临海小憇

 找回密码
 立即注册
搜索
热搜: 活动 交友 discuz
查看: 949|回复: 1

清空oracle内用户下的对象

[复制链接]

100

主题

121

帖子

8552

积分

论坛元老

Rank: 8Rank: 8

积分
8552
发表于 2017-7-25 16:00:12 | 显示全部楼层 |阅读模式
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和同义词

回复

使用道具 举报

100

主题

121

帖子

8552

积分

论坛元老

Rank: 8Rank: 8

积分
8552
 楼主| 发表于 2017-7-25 16:01:01 | 显示全部楼层
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#;


回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

Archiver|手机版|小黑屋|临海小憇 ( 鲁ICP备16010828号-2

GMT+8, 2019-11-21 04:22 , Processed in 0.044269 second(s), 7 queries , File On.

Powered by Discuz! X3.3

© 2001-2017 Comsenz Inc.

快速回复 返回顶部 返回列表