ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 使用dbms_schema_copy 进行不同用户间数据复制

使用dbms_schema_copy 进行不同用户间数据复制

原创 Linux操作系统 作者:guoge 时间:2012-04-05 10:37:05 0 删除 编辑

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 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/9036/viewspace-720341/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2007-12-12

  • 博文量
    72
  • 访问量
    212200