数据迁移__经典脚本

 

 

--表空间
select dbms_metadata.get_ddl('TABLESPACE',TS.TABLESPACE_NAME) from DBA_TABLESPACES TS where TS.TABLESPACE_NAME not in ('SYSTEM','SYSAUX','UNDOTBS1','TEMP','UNDOTBS2','USERS'); 
select 'CREATE TABLESPACE '||TABLESPACE_NAME||q'[ DATAFILE '/data/oradata/orcl/test/]'||TABLESPACE_NAME||q'[_01.DBF' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE 30G;]' from DBA_TABLESPACES  where  TABLESPACE_NAME not in ('SYSTEM','SYSAUX','UNDOTBS1','TEMP','UNDOTBS2','USERS'); 
--用户
select dbms_metadata.get_ddl('USER',t.username)||';' from dba_users t where t.user_id >82 and t.username not in ('XS$NULL') order by user_id ;
--注意：用户需要设置密码 删除概要文件
--权限
select 'grant connect,resource to '||t.username||';' from dba_users t where t.user_id >82 and t.username not in ('XS$NULL') order by user_id ;
--建表语句
select dbms_metadata.get_ddl('TABLE',t.table_name,t.owner)||';' from all_tables t;
--字段注释
select 'comment on column ' || t.OWNER || '.' || t.table_name || '.' ||
       t.column_name || ' is ' || '"' || t1.comments || '"' || ';'
  from all_tab_columns t, all_col_comments t1
 where t1.comments is not null
   and t.table_name = t1.table_name
   and t.column_name = t1.column_name(+)
   and t.owner not in
       ('SYSTEM', 'SYS', 'DIP', 'OLAPSYS', 'SI_INFORMTN_SCHEMA', 'MGMT_VIEW',
        'OWBSYS', 'ORDPLUGINS', 'SPATIAL_WFS_ADMIN_USR',
        'SPATIAL_CSW_ADMIN_USR', 'XDB', 'SYSMAN', 'APEX_PUBLIC_USER',
        'OUTLN', 'ANONYMOUS', 'CTXSYS', 'ORDDATA', 'MDDATA', 'OWBSYS_AUDIT',
        'APEX_030200', 'APPQOSSYS', 'ORACLE_OCM', 'WMSYS', 'DBSNMP',
        'EXFSYS', 'ORDSYS', 'MDSYS', 'FLOWS_FILES','XS$NULL');
comment on table BIDPRO.WM_PLAN IS q'[废旧物资网上竞价竞价计划表，单位名称ID、竞价计划名称、竞价事件编号等 ，竞价管理员填写，回收商竞价使用。 ]'		
--表注释
select 'comment on table '||t.OWNER||'.' || table_name || ' IS ''' || comments || ''||' '||''';'
  from all_tab_comments t
 where t.owner not in
       ('SYSTEM', 'SYS', 'DIP', 'OLAPSYS', 'SI_INFORMTN_SCHEMA', 'MGMT_VIEW',
        'OWBSYS', 'ORDPLUGINS', 'SPATIAL_WFS_ADMIN_USR',
        'SPATIAL_CSW_ADMIN_USR', 'XDB', 'SYSMAN', 'APEX_PUBLIC_USER',
        'OUTLN', 'ANONYMOUS', 'CTXSYS', 'ORDDATA', 'MDDATA', 'OWBSYS_AUDIT',
        'APEX_030200', 'APPQOSSYS', 'ORACLE_OCM', 'WMSYS', 'DBSNMP',
        'EXFSYS', 'ORDSYS', 'MDSYS', 'FLOWS_FILES','XS$NULL'') and t.comments is not null;		

		
-------------
--删除步骤---
-------------
--删除用户
select 'drop user ' || t.username || ' cascade ;' from dba_users t where t.user_id >82;		
--删除表空间
select 'DROP TABLESPACE '||t.tablespace_name||' INCLUDING CONTENTS AND DATAFILES;' from DBA_TABLESPACES t where t.tablespace_name not in ('SYSTEM','SYSAUX','UNDOTBS1','TEMP','UNDOTBS2','USERS');
--kettle
select q'[insert into table_list1 values ('BIDUPGRADETS','BIDPRO',']'||t.table_name||q'[','0',null,null,sysdate,sysdate,'0','0');]' from all_tables t where t.OWNER='BIDPRO';