清空oracle内用户下的对象
<pre class="brush:sql;toolbar:false">1;复制ups和USC的dmp文件到D:\database\oracle\admin\orcl\dpdump2:清空用户下的对象
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内的用户:用户名</pre><p><br/></p><p>之后,再清空job dblink和同义词</p> <pre class="brush:sql;toolbar:false">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='PROCEDURE';
未选定行
SQL>
SQL> --delete package
SQL> select 'drop package ' || object_name||';'||chr(13)||chr(10) from user_objects where object_type='PACKAGE';
未选定行
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','','PUBLIC','',u.name||'.')||'p_execute('||''''
||'DROP '||DECODE(U.NAME,'PUBLIC','public ')||'DATABASE LINK '
|| L.NAME||''''||');' as TEXT
FROM SYS.LINK$ L, SYS.USER$ U
WHERE L.OWNER# = U.USER#;</pre><p><br/></p>
页:
[1]