1;复制ups和USC的dmp文件到D:\database\oracle\admin\orcl\dpdump 2:清空用户下的对象 3:在cmd内执行: impdp 用户名/密码@115.159.112.175:1521/orcl schemas=dmp内的用户 dumpfile=usc.dmp REMAP_TABLESPACE=TBS_INDX_SPC:XH-ASC-U remap_schema=dmp内的用户:用户名 impdp 用户名/密码@115.159.112.175:1521/orcl schemas=dmp内的用户 dumpfile=ups.dmp REMAP_TABLESPACE=TBS_INDX_SPC:XH-ASC-P remap_schema=dmp内的用户:用户名
之后,再清空job dblink和同义词
SQL> --chr(13) ASCII中=换行 chr(10) ASCII中=回车 SQL> --delete tables SQL> select 'drop table ' || table_name ||';'||chr(13)||chr(10) from user_tables; drop table TB_UPS_SALES_RETURN_MST; drop table TB_UPS_STATEMENT_DTL; drop table TB_UPS_SPARES_RETURNS_MST; drop table TB_UPS_CONTRACT_TEMPLATE; drop table TB_UPS_MATERIAL; drop table TB_UPS_SPARES_INVOICE_DTL; drop table COM_LOG; drop table TB_UPS_ACC_MST; drop table TB_UPS_DEVICE_INVOICE_DTL; drop table TB_UPS_SPARES_DELIVERY_DTL; drop table TB_UPS_SERVICE_EQUIPMENT; drop table TB_UPS_EMT_ORDER_MST; drop table TB_UPS_SERVICE_INVOICE_DTL; drop table TB_UPS_EMT_DEL_MST; drop table TB_UPS_SPARES_ORDER_DTL; drop table TB_UPS_SERVICE_EVALUATE; drop table TB_UPS_PO_DTL; drop table TB_UPS_NOTICE; drop table TB_UPS_VENDOR_MATERIAL; drop table TB_UPS_VENDOR_CERTIFICATE; drop table TB_UPS_VENDOR_MATERIAL_APPLY; drop table TB_UPS_AUTHORIZATION; drop table TB_UPS_SERVICE_ORDER_MST; drop table TB_UPS_SERVICE_ORDER_DTL; drop table TB_UPS_VENDOR_MATERIAL_HIS; drop table TB_UPS_REGISTER; drop table TB_UPS_CONTRACT; drop table COM_MENU; drop table TB_UPS_SPARES_DELIVERY_MST; drop table TB_UPS_SPARES_INVOICE_MST; drop table TB_UPS_INVOICE_MST; drop table TB_UPS_DISPATCH_MST; drop table TB_UPS_SPARES_RETURNS_DTL; drop table TB_UPS_DEVICE_INVOICE_MST; drop table TB_UPS_INVOICE_DTL; drop table TB_UPS_CONTRACT_MATERIAL; drop table TB_UPS_MANUFACTURE; drop table COM_MENU_PERMISSION; drop table TB_UPS_PO_MST; drop table TB_UPS_SALES_RETURN_DTL; drop table TB_UPS_ACC_DTL; drop table TB_UPS_SERVICE_RECEIVE_REPORT; drop table TB_UPS_STATEMENT_MST; drop table TB_UPS_VENDOR_CERT_APPLY; drop table TB_UPS_VENDOR; drop table TB_UPS_EMT_ORDER_DTL; drop table TB_UPS_VENDOR_CATEGORY; drop table TB_UPS_DISPATCH_DTL; drop table TB_UPS_SERVICE_INVOICE_MST; drop table TB_UPS_EMT_DEL_DTL; drop table TB_UPS_SERVICE_COLLECE_REPORT; drop table TB_UPS_VENDOR_APPLY; drop table TB_UPS_SERVICE_ORDER_RECORD; drop table TB_UPS_SPARES_ORDER_MST; drop table TB_UPS_MATERIAL_CATEGORY10; drop table TB_UPS_FUNCTION_LOCATION; drop table TB_TMP_VENDOR_MATERIAL_APPLY; drop table TB_TMP_MANUFACTURE; drop table TB_UPS_CONTRACT_PO; drop table TB_UPS_MATERIAL_CATEGORY; drop table TB_UPS_MATERIAL_UNITS_MAPPING; drop table TB_UPS_UNITS; drop table TB_UPS_DOC_OP_LOG; drop table TB_UPS_DOC_TYPE; drop table TB_UPS_ORG; drop table COM_NATIONCODE; drop table TB_UPS_HOSPITAL; drop table COM_FILE; drop table COM_DICT; drop table COM_USER; drop table COM_ROLE_USER; drop table COM_ROLE_MENU; drop table COM_ROLE; 已选择73行。 SQL> --delete views SQL> select 'drop view ' || view_name||';'||chr(13)||chr(10) from user_views; 未选定行 SQL> SQL> --delete seqs SQL> select 'drop sequence ' || sequence_name||';'||chr(13)||chr(10) from user_sequences; drop sequence SEQ_COM_DICT; drop sequence SEQ_COM_FILE; drop sequence SEQ_COM_MENU; drop sequence SEQ_COM_MENU_PERMISSION; drop sequence SEQ_COM_ROLE; drop sequence SEQ_COM_USER; drop sequence SEQ_UPS_APPROVE_DETAIL; drop sequence SEQ_UPS_APPROVE_MANGEMENT; drop sequence SEQ_UPS_APPROVE_MASTER; drop sequence SEQ_UPS_AUTHORIZATION; drop sequence SEQ_UPS_BARCODE_E10; drop sequence SEQ_UPS_BARCODE_S3; drop sequence SEQ_UPS_CONTRACT; drop sequence SEQ_UPS_CONTRACT_MATERIAL; drop sequence SEQ_UPS_CONTRACT_PO; drop sequence SEQ_UPS_CONTRACT_TEMPLATE; drop sequence SEQ_UPS_DEVICE_INVOICE_DTL; drop sequence SEQ_UPS_DEVICE_INVOICE_MST; drop sequence SEQ_UPS_DISPATCH_DTL; drop sequence SEQ_UPS_DISPATCH_MST; drop sequence SEQ_UPS_DOC_OP_LOG; drop sequence SEQ_UPS_EMT_DEL_DTL; drop sequence SEQ_UPS_EMT_DEL_MST; drop sequence SEQ_UPS_HOSPITAL; drop sequence SEQ_UPS_INVOICE_DTL; drop sequence SEQ_UPS_INVOICE_MST; drop sequence SEQ_UPS_MANUFACTURE; drop sequence SEQ_UPS_MATERIAL; drop sequence SEQ_UPS_MATERIAL_UNITS_MAPPING; drop sequence SEQ_UPS_NOTICE; drop sequence SEQ_UPS_ORG; drop sequence SEQ_UPS_REGISTER; drop sequence SEQ_UPS_SERVICE_COLLECE_REPORT; drop sequence SEQ_UPS_SERVICE_INVOICE_DTL; drop sequence SEQ_UPS_SERVICE_INVOICE_MST; drop sequence SEQ_UPS_SERVICE_ORDER_RECORD; drop sequence SEQ_UPS_SERVICE_RECEIVE_REPORT; drop sequence SEQ_UPS_SPARES_DELIVERY_DTL; drop sequence SEQ_UPS_SPARES_DELIVERY_MST; drop sequence SEQ_UPS_SPARES_INVOICE_DTL; drop sequence SEQ_UPS_SPARES_INVOICE_MST; drop sequence SEQ_UPS_SPLIT; drop sequence SEQ_UPS_STATEMENT_DTL; drop sequence SEQ_UPS_STATEMENT_MST; drop sequence SEQ_UPS_UNITS; drop sequence SEQ_UPS_VENDOR; drop sequence SEQ_UPS_VENDOR_APPLY; drop sequence SEQ_UPS_VENDOR_CATEGORY; drop sequence SEQ_UPS_VENDOR_CERT_APPLY; drop sequence SEQ_UPS_VENDOR_MATERIAL; drop sequence SEQ_UPS_VENDOR_MATERIAL_APP; drop sequence SEQ_UPS_VENDOR_MATERIAL_HIS; drop sequence SEQ_UPS_XBARCODE; 已选择53行。 SQL> --delete functions SQL> select 'drop function ' || object_name||';'||chr(13)||chr(10) from user_objects where object_type='FUNCTION'; drop function F_GET_VENDOR_CATEGORY_NAMES; drop function F_GET_COM_DICT_NAME; SQL> --delete procedure SQL> select 'drop procedure ' || object_name||';'||chr(13)||chr(10) from user_objects where object_type='ROCEDURE'; 未选定行 SQL> SQL> --delete package SQL> select 'drop package ' || object_name||';'||chr(13)||chr(10) from user_objects where object_type='ACKAGE'; 未选定行 SQL> --delete trigger SQL> SELECT 'drop TRIGGER "' ||SYS_CONTEXT('USERENV','CURRENT_USER')||'"."'|| TRIGGER_NAME ||'";' ||CHR(13) ||CHR(10)FROM USER_TRIGGERS; 未选定行 SQL> spool off; select 'exec '||DECODE(u.name, 'SYS','','UBLIC','',u.name||'.')||'p_execute('||'''' ||'DROP '||DECODE(U.NAME,'UBLIC','public ')||'DATABASE LINK ' || L.NAME||''''||');' as TEXT FROM SYS.LINK$ L, SYS.USER$ U WHERE L.OWNER# = U.USER#;
欢迎光临 临海小憇 (http://czidea.cn/) | Powered by Discuz! X3.3 |