在使用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 PROGRAMEVENTSQL_ID

---------- ----- ------------------------------ -------------------------------------------------- -------------

846 LINCoracle@ibmvs_a (DM00)wait for unread message on broadcast channel

845 LINCude@ibmvs_a (TNS V1-V3)wait for unread message on broadcast channel7wn3wubg7gjds

840 LINCoracle@ibmvs_a (DW01)enq: TM - contention

836 SYSsqlplus@ibmvs_a (TNS V1-V3)SQL*Net message to client8779q92b78vg0

848 LINCHTDB@ibmvs_a (TNS V1-V3)SQL*Net message from client

849 LINCHTDB@ibmvs_a (TNS V1-V3)SQL*Net message from client

851 LINColcp@ibmvs_a (TNS V1-V3)SQL*Net message from client

852 LINCHTDB@ibmvs_a (TNS V1-V3)SQL*Net message from client

854 LINCHTDB@ibmvs_a (TNS V1-V3)SQL*Net message from client

856 LINCHTDB@ibmvs_a (TNS V1-V3)SQL*Net message from client

857 LINCHTDB@ibmvs_a (TNS V1-V3)SQL*Net message from client

859 LINCHTDB@ibmvs_a (TNS V1-V3)SQL*Net message from client

864 SYSsqlplus@ibmvs_a (TNS V1-V3)SQL*Net message from client

837 LINCHTDB@ibmvs_a (TNS V1-V3)SQL*Net message from client

834 LINCHTDB@ibmvs_a (TNS V1-V3)SQL*Net message from client

874 LINCHTDB@ibmvs_a (TNS V1-V3)SQL*Net message from client

822 LINCHTDB@ibmvs_a (TNS V1-V3)SQL*Net message from client

832 LINCHTDB@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: Release 10.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 Database 10g Enterprise Edition Release 10.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;