--表空间 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';