Q & A

Q: How to get help

For example: ORA-01659

#> oerr ora 1659

For example: IMP-00032

#> oerr imp 32

Q:[ORA-39083] 使用 impdp 匯入資料庫時，到最後一個步驟時，發現系統 hang 問題

Ans: 當執行到這個步驟時，整個系統出現 hang 問題

ORA-39083: Object type INDEX_STATISTICS failed to create with error

•  https://community.oracle.com/thread/2412962?tstart=0
• https://community.oracle.com/thread/403886
• http://neeraj-dba.blogspot.tw/2011/1...tatistics.html
   

Solution:
方法一：impdp 加上 EXCLUDE=statistics

$ impdp system SCHEMAS=<source-owner> REMAP_SCHEMA=<source-owner>:<target-owner> table_exists_action=replace DIRECTORY=dmp_dir DUMPFILE=expdp_SOURCE_OWNER.dmp LOGFILE=impdp_TARGET_OWNER.log EXCLUDE=statistics

$ sqlplus / as sysdba
SQL> exec dbms_stats.gather_schema_stats('<target-owner>'); 

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.