--------过行如下导出脚本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/