在锁表情况下expdp导出数据
在使用expdp导出应用表数据时，有时会有这种期望：该表在准备导出时数据就不会被修改，直到导出结束。针对这种情况，一般会在导出前对需要导出的表加锁，保证数据不能被修改。那么什么锁可以满足这种情况呢？没错，share锁：
SQL>lock table linc.CRITIC in share mode;
该会话不要退出。另开一窗口执行expdp导出表操作，执行好后，该会话关闭事务。
SQL>rollback;
share mode可以允许别的会话select或select for update以及lock table table_name in share mode，不允许insert/update/delete。
那么排他锁可以吗？答案是不行的，测试如下：
SQL> lock table linc.CRITIC in exclusive mode;
Table(s) Locked.
如果这时候你要导出数据，expdp会开始等待。
SQL> select sid,username,program,event,sql_id from v$session where username is not null;
SID USERN PROGRAM EVENT SQL_ID
---------- ----- ------------------------------ -------------------------------------------------- -------------
846 LINC oracle@ibmvs_a (DM00) wait for unread message on broadcast channel
845 LINC ude@ibmvs_a (TNS V1-V3) wait for unread message on broadcast channel 7wn3wubg7gjds
840 LINC oracle@ibmvs_a (DW01) enq: TM - contention
836 SYS sqlplus@ibmvs_a (TNS V1-V3) SQL*Net message to client 8779q92b78vg0
848 LINC HTDB@ibmvs_a (TNS V1-V3) SQL*Net message from client
849 LINC HTDB@ibmvs_a (TNS V1-V3) SQL*Net message from client
851 LINC olcp@ibmvs_a (TNS V1-V3) SQL*Net message from client
852 LINC HTDB@ibmvs_a (TNS V1-V3) SQL*Net message from client
854 LINC HTDB@ibmvs_a (TNS V1-V3) SQL*Net message from client
856 LINC HTDB@ibmvs_a (TNS V1-V3) SQL*Net message from client
857 LINC HTDB@ibmvs_a (TNS V1-V3) SQL*Net message from client
859 LINC HTDB@ibmvs_a (TNS V1-V3) SQL*Net message from client
864 SYS sqlplus@ibmvs_a (TNS V1-V3) SQL*Net message from client
837 LINC HTDB@ibmvs_a (TNS V1-V3) SQL*Net message from client
834 LINC HTDB@ibmvs_a (TNS V1-V3) SQL*Net message from client
874 LINC HTDB@ibmvs_a (TNS V1-V3) SQL*Net message from client
822 LINC HTDB@ibmvs_a (TNS V1-V3) SQL*Net message from client
832 LINC HTDB@ibmvs_a (TNS V1-V3) SQL*Net message from client
18 rows selected.
SQL> select sql_text from v$sql where sql_id='7wn3wubg7gjds';
SQL_TEXT
----------------------------------------------------------------------------------------------------
BEGIN :1 := sys.kupc$que_int.get_status(:2, :3); END;
原因是expdp在开始导出表数
据时，会先对表进行lock。很显然该操作会失败。
那么把表空间设置为read only可以吗？
alter tablespace datatb read only;
oracle@ibmvs_a@/other/dumpdir $ expdp test/test dumpfile=t.dmp logfile=t.log tables=test.t directory=dumpdir
Export: Release10.2.0.3.0 - 64bit Production on Monday, 23 May, 2011 11:31:14
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database10gEnterprise Edition Release10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
ORA-31626: job does not exist
ORA-31633: unable to create master table "TEST.SYS_EXPORT_TABLE_05"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 863
ORA-01647: tablespace 'DATATB' is read only, cannot allocate space in it
此时我们发现，expdp直接报错：
unable to create master table "TEST.SYS_EXPORT_TABLE_05
原来expdp导出时会创建master table，该表空间被我们置为read only状态，自然该操作会失败。
恢复表空间为可读可写状态：
alter tablespace datatb read write;