oracle 11g expdb autoextend tablespace and create directory error;

今天，试了一把Data Pump和original Exopot and Import的实用性，在途中遇到的以下错误：

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的表空间。

点击(此处)折叠或打开

1. SQL> select
   
2.   2 f.tablespace_name,
   
3.   3 a.total,
   
4.   4 f.free,(a.total-f.free)/1024 \"used SIZE(G)\"
   
5.   5 ,round((f.free/a.total)*100) \"% Free\"
   
6.   6 from
   
7.   7 (select tablespace_name, sum(bytes/(1024*1024)) total from dba_data_files group by
   
8.   8 tablespace_name) a,
   
9.   9 (select tablespace_name, round(sum(bytes/(1024*1024))) free from dba_free_space
   
10.  10 group by tablespace_name) f
    
11.  11 WHERE a.tablespace_name = f.tablespace_name(+)
    
12.  12 order by \"% Free\";
    
13. 
    
14. 
    
15. TABLESPACE_NAME TOTAL FREE used SIZE(G) % Free
    
16. ------------------------------ ---------- ---------- ------------ ----------
    
17. PANFENG 30 0 .029296875 0
    
18. SYSTEM 750 9 .723632813 1
    
19. SYSAUX 720 42 .662109375 6
    
20. EXAMPLE 337.5 27 .303222656 8
    
21. UNDO01 50 31 .018554688 62
    
22. UNDOTBS1 165 137 .02734375 83
    
23. USERS 66.25 62 .004150391 94
    
24. PANFENG2 50 49 .000976563 98
    
25. RMAN_TBS 1024 1017 .006835938 99
    
26. 
    
27. 
    
28. 9 rows selected.

--根据上述数据，我们可以看到名为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数据库对象所指向的目录呢？我们可以查看下面视图：

点击(此处)折叠或打开

1. SQL>select directory_name,directory_path from dba_directories
2. DIRECTORY_NAME DIRECTORY_PATH
3. ------------------------------ --------------------------------------------------
4. DUMP_DIR1 /u01/dump_dir1
5. SUBDIR /u01/app/oracle/products/11.2.0/demo/schema/order_
6. entry//2002/Sep
8. SS_OE_XMLDIR /u01/app/oracle/products/11.2.0/demo/schema/order_
9. entry/
11. LOG_FILE_DIR /u01/app/oracle/products/11.2.0/demo/schema/log/
12. MEDIA_DIR /u01/app/oracle/products/11.2.0/demo/schema/produc
13. t_media/
16. DIRECTORY_NAME DIRECTORY_PATH
17. ------------------------------ --------------------------------------------------
18. XMLDIR /u01/app/oracle/products/11.2.0/rdbms/xml
19. DATA_FILE_DIR /u01/app/oracle/products/11.2.0/demo/schema/sales_
20. history/
22. DATA_PUMP_DIR /u01/app/oracle/admin/ora11g/dpdump/
23. ORACLE_OCM_CONFIG_DIR /u01/app/oracle/products/11.2.0/ccr/state

导致该问题的潜在原因 
在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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29210156/viewspace-776797/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29210156/viewspace-776797/