oracle 11g expdb autoextend tablespace and create directory error;

ORA-39006: internal error
ORA-39068: invalid master table data in row with PROCESS_ORDER=-3
ORA-01658: unable to create INITIAL extent for segment in tablespace PANFENG
ORA-39097: Data Pump job encountered unexpected error -1658


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation
以下是關於上述錯誤的解釋以及相關過程：

---

SQL> create directory dump_dir1 as '/u01/dump_dir1'; --建立預設目錄
Directory created.

oracle@Ray:/u01>sqlplus / as sysdba
SQL> grant read,write on directory dump_dir1 to u1;--授予許可權
Grant succeeded.

匯出u1使用者中一張名為u1TI的表。
oracle@Ray:/home/oracle>expdp u1/u1 directory=dump_dir1 dumpfile=u1TI.dmp logfile=u1TI.log tables=u1TI;--匯出u1TI的一張表，預設目錄為dump_dir1 dumpfile=u1TI.log
Export: Release 11.2.0.3.0 - Production on Sun Nov 17 02:22:39 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39006: internal error
ORA-39068: invalid master table data in row with PROCESS_ORDER=-3
ORA-01658: unable to create INITIAL extent for segment in tablespace PANFENG
ORA-39097: Data Pump job encountered unexpected error -1658

上述報錯貌似和表空間為panfeng的有關係，所以我們可以檢視panfeng的表空間。


--根據上述資料，我們可以看到名為panfeng的表空閒空間為0%。所以我們需要開啟預設增加表空間。

SQL> alter database datafile '/u01/app/oracle/oradata/ora11g/panfeng01.dbf' autoextend on;
Database altered.

oracle@Ray:/home/oracle>expdp u1/u1 directory=dump_dir1 dumpfile=u1TI.dmp logfile=u1TI.log tables=u1TI;
Export: Release 11.2.0.3.0 - Production on Sun Nov 17 03:16:35 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation

上述的“ORA-39070”提到的“Unable to open the log file.”初看非常的“詭異”，到底無法開啟什麼日誌檔案呢？難道是沒有許可權在這個目錄下寫檔案？經過“touch”測試排除了這種可能性。 
不管怎麼說，這個問題與檔案操作相關。順著這個思路繼續前行，終於發現原來資料庫中的directory資料庫物件所指向的目錄為dump_dir1,而在該作業系統中根本沒有這個目錄，因目錄不存在，日誌檔案也就理所當然的無處可寫,所以，必須要在作業系統上建立相關目錄。

oracle@Ray:/u01>mkdir dump_dir1;

那麼怎麼知道資料庫中的directory資料庫物件所指向的目錄呢？我們可以檢視下面檢視：

導致該問題的潛在原因 
在11g環境中即使在建立directory資料庫物件的過程中即使所引用的目錄不存在，該命令也是可以正常建立的，這就是容易誤操作的根本原因。

oracle@Ray:/home/oracle>expdp u1/u1 directory=dump_dir1 dumpfile=u1TI.dmp logfile=u1TI.log tables=u1TI;
Export: Release 11.2.0.3.0 - Production on Sun Nov 17 03:22:07 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "U1"."SYS_EXPORT_TABLE_01":  u1/******** dumpfile=u1TI.dmp directory=dump_dir6 tables=u1TI 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "U1"."U1TI"                                 5.421 KB       1 rows
Master table "U1"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for U1.SYS_EXPORT_TABLE_01 is:
  /u01/u1TI.dmp
Job "U1"."SYS_EXPORT_TABLE_01" successfully completed at 03:24:07

如此，我們的表為u1TI就匯出來了。

總結：在運算元據庫時，一定要注意想清楚為什麼？關於處理問題，首先要考慮為什麼造成這樣的錯誤，然後在分析定位。

歡迎大家批評指正：
QQ交流群：300392987
論    壇：http://www.oraclefreebase.com