使用dbms_schema_copy 進行不同使用者間資料複製

dbms_schema_copy 這個包出現在10G，消失在11G，實際上是一個undocumnet 的功能。只是大家在偶爾維護系統使用，切不可在實際程式中使用。

SQL> conn / as sysdba
已連線
SQL> create user abc identified by abc default tablespace users ;

使用者已建立。

SQL> create user def identified by def default tablespace users ;

使用者已建立。

SQL> grant dba to abc ,def
  2  ;

授權成功。

SQL> conn abc/abc
已連線。

 

SQL> create or replace view abc_view
  2  as
  3  select * from all_tables ;

檢視已建立。

SQL> conn / as sysdba
已連線。
SQL> exec dbms_schema_copy.clone('ABC','DEF'); -- 複製function ,package,procedure, synonym, type and view
PL/SQL 過程已成功完成。

 

SQL> exec dbms_schema_copy.clean_up('ABC','DEF');

PL/SQL 過程已成功完成。

SQL> conn def/def
已連線。

SQL> desc abc_view
 名稱                                      是否為空? 型別
 ----------------------------------------- -------- ----------------------

 OWNER                                     NOT NULL VARCHAR2(30)
 TABLE_NAME                                NOT NULL VARCHAR2(30)
 TABLESPACE_NAME                                    VARCHAR2(30)
 CLUSTER_NAME                                       VARCHAR2(30)
 IOT_NAME                                           VARCHAR2(30)
 STATUS                                             VARCHAR2(8)
 PCT_FREE                                           NUMBER
 PCT_USED                                           NUMBER
 INI_TRANS                                          NUMBER
 MAX_TRANS                                          NUMBER
 INITIAL_EXTENT                                     NUMBER
 NEXT_EXTENT                                        NUMBER
 MIN_EXTENTS                                        NUMBER
 MAX_EXTENTS                                        NUMBER
 PCT_INCREASE                                       NUMBER
 FREELISTS                                          NUMBER
 FREELIST_GROUPS                                    NUMBER
 LOGGING                                            VARCHAR2(3)
 BACKED_UP                                          VARCHAR2(1)
 NUM_ROWS                                           NUMBER
 BLOCKS                                             NUMBER
 EMPTY_BLOCKS                                       NUMBER
 AVG_SPACE                                          NUMBER
 CHAIN_CNT                                          NUMBER
 AVG_ROW_LEN                                        NUMBER
 AVG_SPACE_FREELIST_BLOCKS                          NUMBER
 NUM_FREELIST_BLOCKS                                NUMBER
 DEGREE                                             VARCHAR2(20)
 INSTANCES                                          VARCHAR2(20)
 CACHE                                              VARCHAR2(10)
 TABLE_LOCK                                         VARCHAR2(8)
 SAMPLE_SIZE                                        NUMBER
 LAST_ANALYZED                                      DATE
 PARTITIONED                                        VARCHAR2(3)
 IOT_TYPE                                           VARCHAR2(12)
 TEMPORARY                                          VARCHAR2(1)
 SECONDARY                                          VARCHAR2(1)
 NESTED                                             VARCHAR2(3)
 BUFFER_POOL                                        VARCHAR2(7)
 ROW_MOVEMENT                                       VARCHAR2(8)
 GLOBAL_STATS                                       VARCHAR2(3)
 USER_STATS                                         VARCHAR2(3)
 DURATION                                           VARCHAR2(15)
 SKIP_CORRUPT                                       VARCHAR2(8)
 MONITORING                                         VARCHAR2(3)
 CLUSTER_OWNER                                      VARCHAR2(30)
 DEPENDENCIES                                       VARCHAR2(8)
 COMPRESSION                                        VARCHAR2(8)
 DROPPED                                            VARCHAR2(3)

 

SQL> drop view abc_view ;

檢視已刪除。

 

---- 交換物件

SQL> conn / as sysdba
已連線。
SQL> exec dbms_schema_copy.swap('ABC','DEF',true,true);

PL/SQL 過程已成功完成。

SQL> conn abc/abc
已連線。
SQL> desc abc_view
ERROR:
ORA-04043: 物件 abc_view 不存在

SQL> conn def/def
已連線。
SQL> desc abc_view
 名稱                                      是否為空? 型別
 ----------------------------------------- -------- ---------------------

 OWNER                                     NOT NULL VARCHAR2(30)
 TABLE_NAME                                NOT NULL VARCHAR2(30)
 TABLESPACE_NAME                                    VARCHAR2(30)
 CLUSTER_NAME                                       VARCHAR2(30)
 IOT_NAME                                           VARCHAR2(30)
 STATUS                                             VARCHAR2(8)
 PCT_FREE                                           NUMBER
 PCT_USED                                           NUMBER
 INI_TRANS                                          NUMBER
 MAX_TRANS                                          NUMBER
 INITIAL_EXTENT                                     NUMBER
 NEXT_EXTENT                                        NUMBER
 MIN_EXTENTS                                        NUMBER
 MAX_EXTENTS                                        NUMBER
 PCT_INCREASE                                       NUMBER
 FREELISTS                                          NUMBER
 FREELIST_GROUPS                                    NUMBER
 LOGGING                                            VARCHAR2(3)
 BACKED_UP                                          VARCHAR2(1)
 NUM_ROWS                                           NUMBER
 BLOCKS                                             NUMBER
 EMPTY_BLOCKS                                       NUMBER
 AVG_SPACE                                          NUMBER
 CHAIN_CNT                                          NUMBER
 AVG_ROW_LEN                                        NUMBER
 AVG_SPACE_FREELIST_BLOCKS                          NUMBER
 NUM_FREELIST_BLOCKS                                NUMBER
 DEGREE                                             VARCHAR2(20)
 INSTANCES                                          VARCHAR2(20)
 CACHE                                              VARCHAR2(10)
 TABLE_LOCK                                         VARCHAR2(8)
 SAMPLE_SIZE                                        NUMBER
 LAST_ANALYZED                                      DATE
 PARTITIONED                                        VARCHAR2(3)
 IOT_TYPE                                           VARCHAR2(12)
 TEMPORARY                                          VARCHAR2(1)
 SECONDARY                                          VARCHAR2(1)
 NESTED                                             VARCHAR2(3)
 BUFFER_POOL                                        VARCHAR2(7)
 ROW_MOVEMENT                                       VARCHAR2(8)
 GLOBAL_STATS                                       VARCHAR2(3)
 USER_STATS                                         VARCHAR2(3)
 DURATION                                           VARCHAR2(15)
 SKIP_CORRUPT                                       VARCHAR2(8)
 MONITORING                                         VARCHAR2(3)
 CLUSTER_OWNER                                      VARCHAR2(30)
 DEPENDENCIES                                       VARCHAR2(8)
 COMPRESSION                                        VARCHAR2(8)
 DROPPED                                            VARCHAR2(3)

 

 

其實，在11G中使用 impdp network_link 的方式複製不同schema 的資料，是很方便的，例如：

impdp system/oracle  network_link=gpo schemas=(DEV_GIS2_BJDPTJY) REMAP_SCHEMA=DEV_GIS2_BJDPTJY:TRADE