Q & A

Q: How to get help

For example: ORA-01659

#> oerr ora 1659

For example: IMP-00032

#> oerr imp 32

Q: 使用 OEM UI 新增 datafile 時，不小心加錯 Tablespace，如何移除它？

Ans: 在 OEM UI（Java）操作下無法刪除 datafile，但可以用指令方式來作

SQL> alter tablespace <tablespace-name> drop datafile '/opt/oracle/oradata/win/STDB03.dbf';

Q: 移除 User 時，DB 發現 hang 問題且 CPU 負載非常高

Ans: 解決步驟如下：

1. 嘗試關閉資料庫
2. 資料庫重啟恢復後
3. 先移除 User 所屬的所有 Tabls、View等所有物件
4. 再完整移除整個 User
    

如果連 SQL/PLUS 都無法有效被執行時，如何能強制關閉資料庫

$sqlplus -prelim "/ as sysdba"
SQL> shutdown abort 

移除 User 所屬的物件

set heading off
set pages 0
set feedback off
set echo off
spool ./drop_MYUSER.sql
select 'drop table MYUSER.'||table_name||';' from dba_tables where owner='MYUSER';

spool off

移除 User

drop user USER CASCADE;

Q: IMP-00032: SQL statement exceeded buffer length

Ans: Add a option buffer=1000000 in the import command

#> imp system/oracle buffer=1000000 .....

If it's still not working, try to find out the proper value of the SQL_LENGTH with the following command:

In the source DB you need to run

SQL> select dbms_lob.getlength (dbms_metadata.get_ddl('TABLE','<table-name>','<owner-name>')) "SQL_LENGTH" from dual;
   
    SQL_LENGTH
    ----------
     71203

NOTE: the table name can be one of the tables failed on import.

Q: ORA-01659: unable to allocate MINEXTENTS beyond 1in tablespace XXX

Ans: Solution

1. Extend the datafile in the tablespace
2. Extend the datafile in the temporary tablespace
3. Re-export the dump file from the source DB and enable the option compress=n.