在鎖表情況下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: 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;