Oracle常用脚本

1.查看SQL执行计划

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('75n7rjsypwy7w',NULL,'Advanced'))

2.查看会话执行过的sql

select * from v$open_cursor where sid=xxx;

3.查看数据库中表大小

/* Formatted on 2021-08-30 上午 10:00:23 (QP5 v5.163.1008.3004) */
SELECT *
  FROM (  SELECT OWNER,
                 SEGMENT_NAME,
                 ROUND (SUM (BYTES) / 1024 / 1024 / 1024, 2) SIZEG
            FROM DBA_SEGMENTS
           WHERE SEGMENT_TYPE IN ('TABLE PARTITION', 'TABLE')
                 --AND OWNER LIKE '%MONKEY%'
        GROUP BY OWNER, SEGMENT_NAME)
 WHERE SIZEG > 5

4.查看隐含参数

SELECT x.ksppinm name,
         y.ksppstvl VALUE,
         y.ksppstdf isdefault,
         DECODE (BITAND (y.ksppstvf, 7),
                 1, 'MODIFIED',
                 4, 'SYSTEM_MOD',
                 'FALSE')
            ismod,
         DECODE (BITAND (y.ksppstvf, 2), 2, 'TRUE', 'FALSE') isadj
    FROM sys.x$ksppi x, sys.x$ksppcv y
   WHERE     x.inst_id = USERENV ('Instance')
         AND y.inst_id = USERENV ('Instance')
         AND x.indx = y.indx
         and x.ksppinm ='_kgl_hot_object_copies' 
--ORDER BY TRANSLATE (x.ksppinm, ' _', ' ')

5.新增ID栏位并赋值

--使用ROWID进行，表很大的时候使用
DECLARE
   V_COUNT   NUMBER := 0;
   V_ROWID   VARCHAR2 (200);
BEGIN
   FOR r IN (SELECT ROWIDTOCHAR (ROWID) v_rowid, a.*
               FROM MONKEY.TEST01 a)
   LOOP
      UPDATE MONKEY.TEST01
         SET id = MONKEY.TEST01_SEQ.NEXTVAL
       WHERE ROWID = r.v_rowid;

      V_COUNT := V_COUNT + 1;

      IF V_COUNT = 50000
      THEN
         V_COUNT := 0;
         COMMIT;
      END IF;
   END LOOP;

   COMMIT;
END;
/
--小表的时候使用
UPDATE MONKEY.TEST01 SET ID = MONKEY.TEST01_SEQ.NEXTVAL;
COMMIT;