工具篇（Oracle）---常用脚本

目录

概述

一键解锁脚本

一键加索引脚本

一键数据库闪回脚本

---

概述

    根据项目需求写的一些脚本，仅作参考，后续新脚本会接着追加

一键解锁脚本

DECLARE

  V_SQT       VARCHAR2(200);
  V_ERRORTEXT VARCHAR2(200);

BEGIN

  FOR ITEM IN (SELECT C.OBJECT_NAME,
                      B.USERNAME,
                      B.SID,
                      B.SERIAL#,
                      LOGON_TIME
                 FROM V$LOCKED_OBJECT A, V$SESSION B, DBA_OBJECTS C
                WHERE A.SESSION_ID = B.SID
                  AND A.OBJECT_ID = C.OBJECT_ID
                GROUP BY C.OBJECT_NAME,
                         B.USERNAME,
                         B.SID,
                         B.SERIAL#,
                         LOGON_TIME) LOOP
  
    DBMS_OUTPUT.PUT_LINE('锁表用户:' || ITEM.USERNAME || '    被锁表名:' ||
                         ITEM.OBJECT_NAME || '    锁表时间:' ||
                         ITEM.LOGON_TIME);
  
    V_SQT := 'ALTER SYSTEM KILL SESSION ' || '''' || ITEM.SID || ',' ||
             ITEM.SERIAL# || '''';
  
    DBMS_OUTPUT.PUT_LINE(V_SQT);
  
    BEGIN
      EXECUTE IMMEDIATE V_SQT;
    
    EXCEPTION
    
      WHEN OTHERS THEN
      
        DBMS_OUTPUT.PUT_LINE('解锁失败');
      
        V_ERRORTEXT := SUBSTR(SQLERRM, 1);
      
        DBMS_OUTPUT.PUT_LINE(V_ERRORTEXT);
    END;
  
  END LOOP;

END;

一键加索引脚本

DECLARE

  INDX VARCHAR2(30);

  SQLT VARCHAR(1000);

  NSQLT VARCHAR(1000);

  ERRORTEXT VARCHAR2(200);

  BUMS NUMBER := 0;

BEGIN

  DBMS_OUTPUT.ENABLE(100000000);

  -- 循环遍历出所需要创建索引的表以及索引字段和索引名
  FOR ITEM IN (SELECT C.TABLE_NAME,
                      CC.COLUMN_NAME,
                      ('ATSI_' || CC.COLUMN_NAME) INDX,
                      CC.POSITION COLUMN_POSITION
                 FROM USER_CONSTRAINTS C, USER_CONS_COLUMNS CC
                WHERE C.CONSTRAINT_NAME = CC.CONSTRAINT_NAME
                  AND C.CONSTRAINT_TYPE = 'R'
               MINUS
               SELECT I.TABLE_NAME,
                      IC.COLUMN_NAME,
                      ('ATSI_' || IC.COLUMN_NAME) INDX,
                      IC.COLUMN_POSITION
                 FROM USER_INDEXES I, USER_IND_COLUMNS IC
                WHERE I.INDEX_NAME = IC.INDEX_NAME) LOOP
    BEGIN
    
      -- 如果索引过长则进行截取
      IF LENGTHB(ITEM.INDX) > 30 THEN
        INDX := SUBSTR(ITEM.INDX, 0, 30);
      ELSE
        INDX := ITEM.INDX;
      END IF;
    
      -- 创建索引语句   
      SQLT := 'CREATE INDEX ' || INDX || ' ON ' || ITEM.TABLE_NAME || '(' ||
              ITEM.COLUMN_NAME || ')';
    
      -- 输出语句脚本，以作记录
      DBMS_OUTPUT.PUT_LINE(SQLT || ';');
    
      -- 执行语句
      EXECUTE IMMEDIATE SQLT;
    
    EXCEPTION
    
      WHEN OTHERS THEN
      
        DBMS_OUTPUT.PUT_LINE('创建索引失败');
      
        ERRORTEXT := SUBSTR(SQLERRM, 1);
      
        DBMS_OUTPUT.PUT_LINE(ERRORTEXT);
      
        DBMS_OUTPUT.PUT_LINE('');
      
        -- 如果名称已被使用则后缀加个累加标志
        IF ERRORTEXT = 'ORA-00955: 名称已由现有对象使用' THEN
          NSQLT := 'CREATE INDEX ' || INDX || BUMS || ' ON ' ||
                   ITEM.TABLE_NAME || '(' || ITEM.COLUMN_NAME || ')';
        
          BUMS := BUMS + 1;
        
          BEGIN
          
            DBMS_OUTPUT.PUT_LINE(NSQLT || ';     --- NEW');
            EXECUTE IMMEDIATE NSQLT;
          
          EXCEPTION
          
            WHEN OTHERS THEN
            
              ERRORTEXT := SUBSTR(SQLERRM, 1);
            
              DBMS_OUTPUT.PUT_LINE(ERRORTEXT);
            
          END;
        
          DBMS_OUTPUT.PUT_LINE(NSQLT);
        END IF;
      
    END;
  
  END LOOP;

END;

一键数据库闪回脚本

DECLARE

  -- 要回闪的表名字
  TABLENAME VARCHAR2(30) := 't_taskparamdef';

  -- 要回闪到的时间节点（2019-05-22 10:37:00）类似这种形式
  ROLLTIME VARCHAR2(30) := '2019-05-22 11:49:00';

  SQLT VARCHAR(1000);

BEGIN

  -- 启动要回闪表的行移动功能
  SQLT := 'ALTER TABLE ' || TABLENAME || ' ENABLE ROW MOVEMENT';

  -- 执行语句
  EXECUTE IMMEDIATE SQLT;

  -- 开始回闪到指定时间的表数据
  SQLT := 'FLASHBACK TABLE ' || TABLENAME || ' TO TIMESTAMP TO_TIMESTAMP(' || '''' ||
          ROLLTIME || '''' || ',' || '''' || 'yyyy-MM-dd HH24:MI:SS' || '''' || ')';

  -- 执行语句
  EXECUTE IMMEDIATE SQLT;
END;