Oracle清库脚本

最新推荐文章于 2022-09-16 17:17:58 发布

小非肥

最新推荐文章于 2022-09-16 17:17:58 发布

阅读量492

点赞数

分类专栏：数据库文章标签： oracle

本文链接：https://blog.csdn.net/weixin_42988712/article/details/112146745

版权

数据库专栏收录该内容

13 篇文章 1 订阅

订阅专栏

下面的两个删除都是index,JOB,LOB未删除。

-- 删除当前用户下的这些文件  
BEGIN
  FOR obj IN
  (SELECT object_name,object_type from user_objects
    WHERE object_type='PROCEDURE'
     OR object_type='TABLE'
     OR object_type='VIEW'
     OR object_type='FUNCTION'
     OR object_type='PACKAGE'
     OR object_type='PACKAGE BODY'
     OR object_type='SEQUENCE'
     OR object_type='TYPE'
     OR object_type='TYPE　BODY'
    )
    LOOP
        IF obj.object_type='TABLE' THEN
           EXECUTE IMMEDIATE 'DROP '||obj.object_type||' '||obj.object_NAME||' CASCADE CONSTRAINTS';
        ELSIF obj.object_type='TYPE' THEN
           EXECUTE IMMEDIATE 'DROP '||obj.object_type||' '||obj.object_NAME||' FORCE';
        ELSIF obj.object_type='TYPE BODY' THEN
           EXECUTE IMMEDIATE 'DROP '||obj.object_type||' '||obj.object_NAME||' FORCE';
        ELSE
           EXECUTE IMMEDIATE 'DROP '||obj.object_type||' '||obj.object_NAME;
        END IF;
   END LOOP;  
END;

管理员权限下登录：删除某用户的以下文件 job和lob没有删除

BEGIN
  FOR obj IN
  (SELECT object_name,object_type from dba_objects
    WHERE (object_type='PROCEDURE'
     OR object_type='TABLE'
     OR object_type='VIEW'
     OR object_type='FUNCTION'
     OR object_type='PACKAGE'
     OR object_type='PACKAGE BODY'
     OR object_type='SEQUENCE'
     OR object_type='TYPE'
     OR object_type='TYPE　BODY') AND owner = 'HSCON'
    )
    LOOP
        IF obj.object_type='TABLE' THEN
           EXECUTE IMMEDIATE 'DROP '||obj.object_type||' HSCON.'||obj.object_NAME;
        ELSIF obj.object_type='TYPE' THEN
           EXECUTE IMMEDIATE 'DROP '||obj.object_type||' HSCON.'||obj.object_NAME||' FORCE';
        ELSIF obj.object_type='TYPE BODY' THEN
           EXECUTE IMMEDIATE 'DROP '||obj.object_type||' HSCON.'||obj.object_NAME||' FORCE';
        ELSE
           EXECUTE IMMEDIATE 'DROP '||obj.object_type||' HSCON.'||obj.object_NAME;
        END IF;
   END LOOP;  
END;