oracle11g之expdp产生library cache lock及tablespace autoextend off_alert log

--------过行如下导出脚本hang住了
expdp tbl_bck/system schemas=tbl_bck dumpfile=dir_tbl_bck:expdp_tbl_bck.dmp

---等待事件的会话请求的object handle address
select p1raw from v$session where sid=48;--48为请求锁会话

--定位持锁会话的会话地址
select * from v$libcache_locks where object_handle='000007FF57DB6E40' and mode_held=3;

-- statement suspended, wait error to be cleared --此事件即会话语句出错，导致会话语句暂时挂起，等待处理完毕后即可恢复正常
select sid,serial#,(select sql_text from v$sql where sql_id=v$session.sql_id),event from v$session where saddr='000007FF627AFDB8';---saddr即上述的持锁会话查询出的持锁会话

--杀掉持锁会话
alter system kill session '52,479'---等待事件的会话请求的object handle address
select p1raw from v$session where sid=48;

--定位持锁会话的会话地址
select * from v$libcache_locks where object_handle='000007FF57DB6E40' and mode_held=3;

-- statement suspended, wait error to be cleared
select sid,serial#,(select sql_text from v$sql where sql_id=v$session.sql_id),event from v$session where saddr='000007FF627AFDB8';---saddr即上述的持锁会话查询出的持锁会话

--杀掉持锁会话
alter system kill session '52,479'

C:\Users\123>expdp tbl_bck/system schemas=tbl_bck dumpfile=dir_tbl_bck:expdp_tbl
_bck.dmp

Export: Release 11.2.0.1.0 - Production on Fri May 17 15:54:34 2013

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.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 TBS_NEWLY --好像和表空间有关

ORA-39097: Data Pump job encountered unexpected error -1658

--查看并配置表空间为自动扩展
SQL> select * from dba_data_files;
 
FILE_NAME                                                                           FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS    RELATIVE_FNO AUTOEXTENSIBLE   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS ONLINE_STATUS
-------------------------------------------------------------------------------- ---------- ------------------------------ ---------- ---------- --------- ------------ -------------- ---------- ---------- ------------ ---------- ----------- -------------
D:\ORACLE11G_64BIT\ORADATA\SECOND\SYSTEM01.DBF                                            1 SYSTEM                         1268776960     154880 AVAILABLE            1 YES            3435972198    4194302         1280 1267728384      154752 SYSTEM
D:\ORACLE11G_64BIT\ORADATA\SECOND\SYSAUX01.DBF                                            2 SYSAUX                          629145600      76800 AVAILABLE            2 YES            3435972198    4194302         1280  628097024       76672 ONLINE
D:\ORACLE11G_64BIT\ORADATA\SECOND\UNDOTBS01.DBF                                           3 UNDOTBS1                        408944640      49920 AVAILABLE            3 YES            3435972198    4194302          640  407896064       49792 ONLINE
D:\ORACLE11G_64BIT\ORADATA\SECOND\USERS01.DBF                                             4 USERS                            49807360       6080 AVAILABLE            4 YES            3435972198    4194302          160   48758784        5952 ONLINE
D:\ORACLE11G_64BIT\ORADATA\SECOND\TBS _NEWLY.DBF                                          5 TBS_NEWLY                        10485760       1280 AVAILABLE            5 NO                      0          0            0    9437184        1152 ONLINE
 
SQL> alter database datafile 'D:\ORACLE11G_64BIT\ORADATA\SECOND\TBS _NEWLY.DBF' autoextend on;
 
Database altered
 
SQL> select * from dba_data_files;
 
FILE_NAME                                                                           FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS    RELATIVE_FNO AUTOEXTENSIBLE   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS ONLINE_STATUS
-------------------------------------------------------------------------------- ---------- ------------------------------ ---------- ---------- --------- ------------ -------------- ---------- ---------- ------------ ---------- ----------- -------------
D:\ORACLE11G_64BIT\ORADATA\SECOND\SYSTEM01.DBF                                            1 SYSTEM                         1268776960     154880 AVAILABLE            1 YES            3435972198    4194302         1280 1267728384      154752 SYSTEM
D:\ORACLE11G_64BIT\ORADATA\SECOND\SYSAUX01.DBF                                            2 SYSAUX                          629145600      76800 AVAILABLE            2 YES            3435972198    4194302         1280  628097024       76672 ONLINE
D:\ORACLE11G_64BIT\ORADATA\SECOND\UNDOTBS01.DBF                                           3 UNDOTBS1                        408944640      49920 AVAILABLE            3 YES            3435972198    4194302          640  407896064       49792 ONLINE
D:\ORACLE11G_64BIT\ORADATA\SECOND\USERS01.DBF                                             4 USERS                            49807360       6080 AVAILABLE            4 YES            3435972198    4194302          160   48758784        5952 ONLINE
D:\ORACLE11G_64BIT\ORADATA\SECOND\TBS _NEWLY.DBF                                          5 TBS_NEWLY                        10485760       1280 AVAILABLE            5 YES            3435972198    4194302            1    9437184        1152 ONLINE
 
SQL>

---重置为禁用自动扩展
SQL> alter database datafile 'D:\ORACLE11G_64BIT\ORADATA\SECOND\TBS _NEWLY.DBF' autoextend off;
 
Database altered

--也可以直接查看alert日志分析定位问题：
DM00 started with pid=37, OS id=9568, job TBL_BCK.SYS_SQL_FILE_SCHEMA_01
Fri May 17 17:50:07 2013
DW00 started with pid=38, OS id=18732, wid=1, job TBL_BCK.SYS_SQL_FILE_SCHEMA_01
statement in resumable session 'TBL_BCK.SYS_SQL_FILE_SCHEMA_01' was suspended due to
    ORA-01658: unable to create INITIAL extent for segment in tablespace TBS_NEWLY --很明显与表空间有关
Fri May 17 17:57:17 2013

alter database datafile 'D:\ORACLE11G_64BIT\ORADATA\SECOND\TBS _NEWLY.DBF' autoextend on
Completed:
alter database datafile 'D:\ORACLE11G_64BIT\ORADATA\SECOND\TBS _NEWLY.DBF' autoextend on
Fri May 17 17:57:19 2013
statement in resumable session 'TBL_BCK.SYS_SQL_FILE_SCHEMA_01' was resumed

 

 

---使用sqlfile选项导出create 相关的ddl语句到指定的文件,sqlfile指定分为2部分：1为directory,2为指定的文件
C:\Users\123>impdp tbl_bck/system schemas=tbl_bck dumpfile=dir_tbl_bck:expdp_tbl
_bck.dmp sqlfile=dir_tbl_bck:impdp_ddl.log

小结：
     1,expdp与impdp选项很多
     2,如出错先查alert更快，而后为v$session相关视图，最后才是trace文件分析

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

转载于:http://blog.itpub.net/9240380/viewspace-761389/