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檔案分析