一些实用SQL脚本

1. 按比例显示数据库文件的使用情况

set serveroutput on size 20000
set linesize 80

declare
  cursor c_map(p_file_id number) is
  (select file_id,block_id,blocks,'X' used
   from dba_extents where file_id=p_file_id
   union
   select file_id,block_id,blocks,'=' used
   from dba_free_space where file_id=p_file_id
   ) order by 1,2,3;
 cursor c_file is
 select file_name,file_id,blocks/400  bsize from dba_data_files;
 j number :=1;
 xsize number;
begin
   for r_file in c_file
   loop
       dbms_output.put_line('Map of '||r_file.file_name);
       for r_map in c_map(r_file.file_id)
       loop
           for i  in 1..r_map.blocks/r_file.bsize
           loop
            dbms_output.put(R_MAP.USED);
            if j>=80 then
        j :=1;dbms_output.new_line;
            else
               j := j+1;
            end if;
           end loop;
       end loop;
       dbms_output.new_line;j:=1;
   end loop;
end;
/

2. 捕获用户登录信息

CREATE OR REPLACE TRIGGER tr_login_record
  AFTER logon ON DATABASE
DECLARE
  miUserSid NUMBER;
  mtSession v$session%ROWTYPE;
  CURSOR cSession(iiUserSid IN NUMBER) IS
    SELECT * FROM v$session WHERE sid = iiUserSid;
BEGIN
  SELECT sid INTO miUserSid FROM v$mystat WHERE rownum <= 1;
  OPEN cSession(miUserSid);
  FETCH cSession
    INTO mtSession;
  --if user exists then insert data
  IF cSession%FOUND THEN
    INSERT INTO log$information
      (login_user,
       login_time,
       ip_adress,
       ausid,
       terminal,
       osuser,
       machine,
       program,
       sid,
       serial#)
    VALUES
      (ora_login_user,
       SYSDATE,
       SYS_CONTEXT('USERENV', 'IP_ADDRESS'),
       userenv('SESSIONID'),
       mtSession.Terminal,
       mtSession.Osuser,
       mtSession.Machine,
       mtSession.Program,
       mtSession.Sid,
       mtSession.Serial#);
  ELSE
    --if user don't exists then return error
    sp_write_log('Session Information Error:' || SQLERRM);
    CLOSE cSession;
    raise_application_error(-20099, 'Login Exception', FALSE);
  END IF;
  CLOSE cSession;
EXCEPTION
  WHEN OTHERS THEN
    sp_write_log('Login Trigger Error:' || SQLERRM);
END tr_login_record;
/

3. 查看隐藏参数

SELECT NAME,
       VALUE,
       decode(isdefault, 'TRUE', 'Y', 'N') as "Default",
       decode(ISEM, 'TRUE', 'Y', 'N') as SesMod,
       decode(ISYM, 'IMMEDIATE', 'I', 'DEFERRED', 'D', 'FALSE', 'N') as SysMod,
       decode(IMOD, 'MODIFIED', 'U', 'SYS_MODIFIED', 'S', 'N') as Modified,
       decode(IADJ, 'TRUE', 'Y', 'N') as Adjusted,
       description
  FROM ( --GV$SYSTEM_PARAMETER 
        SELECT x.inst_id as instance,
                x.indx + 1,
                ksppinm as NAME,
                ksppity,
                ksppstvl as VALUE,
                ksppstdf as isdefault,
                decode(bitand(ksppiflg / 256, 1), 1, 'TRUE', 'FALSE') as ISEM,
                decode(bitand(ksppiflg / 65536, 3),
                       1,
                       'IMMEDIATE',
                       2,
                       'DEFERRED',
                       'FALSE') as ISYM,
                decode(bitand(ksppstvf, 7), 1, 'MODIFIED', 'FALSE') as IMOD,
                decode(bitand(ksppstvf, 2), 2, 'TRUE', 'FALSE') as IADJ,
                ksppdesc as DESCRIPTION
          FROM x$ksppi x, x$ksppsv y
         WHERE x.indx = y.indx
           AND substr(ksppinm, 1, 1) = '_'
           AND x.inst_id = USERENV('Instance'))
 ORDER BY NAME
/

4. 查找隐藏的会话CPU利用

SELECT ROWNUM top, t.*
  FROM (SELECT s.sid,
               x.CPU_TIME / x.executions per_cpu,
               x.hash_value,
               x.sql_text
          FROM v$sqlarea x, v$session s
         WHERE x.executions > 0
           AND x.parsing_user_id <> 0
           and s.sql_hash_value(+) = x.hash_value
         ORDER BY x.CPU_TIME / x.executions DESC) t
 WHERE ROWNUM < 21
/

5. 发现tablespace有fragmentation

SELECT SUBSTR(ts.name, 1, 10) TSPACE,
       tf.blocks BLOCKS,
       SUM(f.length) FREE,
       COUNT(*) PIECES,
       MAX(f.length) BIGGEST,
       MIN(f.length) SMALLEST,
       ROUND(AVG(f.length)) AVERAGE
  FROM sys.fet$ F, sys.file$ TF, sys.ts$ TS
 WHERE ts.ts# = f.ts#
   AND ts.ts# = tf.ts#
 GROUP BY ts.name, tf.blocks
/

6. 发现table有fragmentation

select /*+ ordered */
 u.name || '.' || o.name table_name,
 lpad(decode(t.degree, 32767, 'DEFAULT', nvl(t.degree, 1)), 7) degree,
 substr(to_char(100 * t.rowcnt /
                (floor((p.value - 66 - t.initrans * 24) /
                       greatest(t.avgrln + 2, 11)) * t.blkcnt),
                '999.00'),
        2) || '%' density,
 1 new_free,
 99 - ceil((100 * (p.value - 66 - t.initrans * 24 -
           greatest(floor((p.value - 66 - t.initrans * 24) /
                                  greatest(t.avgrln + 2, 11)) - 1,
                            1) * greatest(t.avgrln + 2, 11)) /
           (p.value - 66 - t.initrans * 24))) new_used,
 ceil((t.blkcnt -
      t.rowcnt /
      floor((p.value - 66 - t.initrans * 24) / greatest(t.avgrln + 2, 11))) /
      m.value) reads_wasted
  from sys.tab$ t,
       (select value
          from sys.v_$parameter
         where name = 'db_file_multiblock_read_count') m,
       sys.obj$ o,
       sys.user$ u,
       (select value from sys.v_$parameter where name = 'db_block_size') p
 where t.tab# is null
   and t.blkcnt > m.value
   and t.chncnt = 0
   and t.avgspc > t.avgrln
   and ceil((t.blkcnt -
            t.rowcnt / floor((p.value - 66 - t.initrans * 24) /
                              greatest(t.avgrln + 2, 11))) / m.value) > 0
   and o.obj# = t.obj#
   and o.owner# != 0
   and u.user# = o.owner#
 order by 5 desc, 2
/
Trackback

no comment untill now

Add your comment now

切换到手机版