如何获取oracle按表大小排行前50名列表信息?

栏目:eas cloud知识作者:金蝶来源:金蝶云社区发布:2024-09-22浏览:1

如何获取oracle按表大小排行前50名列表信息?


进入sqlplus ,并运行下面脚本,就可以得到

   set lines 200
 set pages 1000
  column OWNER format a25
  column TABLE_NAME format a25

set serveroutput on




DECLARE
  --v_tableowner    varchar(50) := 'LUO';
  t_OWNER         varchar(50);
  t_TABLE_NAME    varchar(50);
  t_BYTES         number(30);
  v_LAST_ANALYZED date;
  v_NUM_ROWS      number(30);
  v_date          char(10);
  v_text          varchar2(300);
 
  v_text2          varchar2(3000);
  CURSOR c1 IS
    select owner, TABLE_NAME, LAST_ANALYZED, NUM_ROWS
      from (select OWNER, TABLE_NAME,  LAST_ANALYZED, NUM_ROWS,  blocks * 8192 / 1024 / 1024
              from dba_tables
             where (LAST_ANALYZED is not null
               and num_rows > 10000
               ) or TABLE_NAME='T_BAS_ATTACHMENT'
             order by 5 desc) inner1
     where rownum < 51;
BEGIN
  select to_char(sysdate, 'yyyy_mm_dd') into v_date from dual;
  v_text := 'create table TABLEINFO_DB_' || v_date || '(OWNER varchar2(40),TABLE_NAME varchar2(60),LAST_ANALYZED DATE,NUM_ROWS number(20),tablesize_MBytes number(20))';

  execute immediate v_text;


  OPEN c1;
  LOOP
    FETCH c1
      INTO t_OWNER, t_TABLE_NAME, v_LAST_ANALYZED, v_NUM_ROWS;
   
    EXIT WHEN c1%NOTFOUND;
    select tabinfo.OWNER,
           tabinfo.TABLE_NAME,
           sum(tabinfo.BYTES) / 1024 / 1024
      into t_OWNER, t_TABLE_NAME, t_BYTES
      from (SELECT A.OWNER,
                   A.TABLE_NAME,
                   A.ALLSEGMENT,
                   B.SEGMENT_TYPE,
                   B.BYTES
              FROM (SELECT OWNER,
                           OWNER || '.' || TABLE_NAME AS TABLE_NAME,
                           TABLE_NAME allsegment
                      FROM DBA_TABLES
                     WHERE OWNER = t_OWNER
                       AND TABLE_NAME = t_TABLE_NAME
                    UNION
                    SELECT OWNER,
                           OWNER || '.' || TABLE_NAME AS TABLE_NAME,
                           INDEX_NAME
                      FROM DBA_INDEXES
                     WHERE OWNER = t_OWNER
                       AND TABLE_NAME = t_TABLE_NAME
                    UNION
                    SELECT OWNER,
                           OWNER || '.' || TABLE_NAME AS TABLE_NAME,
                           SEGMENT_NAME
                      FROM DBA_LOBS
                     WHERE OWNER = t_OWNER
                       AND TABLE_NAME = t_TABLE_NAME) A,
                   DBA_SEGMENTS B
             where a.OWNER = t_OWNER
               and b.owner = t_OWNER
               and a.owner = b.owner
               and allsegment = b.SEGMENT_NAME) tabinfo
     group by tabinfo.OWNER, tabinfo.TABLE_NAME;
 
 
    v_text2 := 'insert into TABLEINFO_DB_' || v_date||'(OWNER,TABLE_NAME ,NUM_ROWS ,tablesize_MBytes,LAST_ANALYZED)' || '  values ('|| ''''||t_OWNER ||''''|| ',' ||    ''''||

t_TABLE_NAME||'''' || ',' || v_NUM_ROWS || ',' ||    t_BYTES ||','||''''|| v_LAST_ANALYZED||''''||')';

     execute immediate v_text2;
--DBMS_OUTPUT.PUT_LINE(v_text2);

  END LOOP;

  commit;
  DBMS_OUTPUT.PUT_LINE('AllUserTableSizeInformation stored in :'||'TABLEINFO_DB_'||v_date);
  DBMS_OUTPUT.PUT_LINE('you can run query to get result :select * from ' ||'TABLEINFO_DB_'||v_date);
  close c1;
END;
/

如何获取oracle按表大小排行前50名列表信息?

进入sqlplus ,并运行下面脚本,就可以得到 set lines 200 set pages 1000 column OWNER format a25 column TABLE_NAME f...
点击下载文档
确认删除?
回到顶部
客服QQ
  • 客服QQ点击这里给我发消息