Tuesday, January 22, 2019

How to check table size including their indexes? What are objects relate of table?

/* table size including their indexes*/


accept TABLE_NAME PROMPT 'Enter the TABLE_NAME >'
SET FEEDBACK OFF
SET HEAD ON
SET ARRAY 1
SET LINESIZE 10000
SET PAGESIZE 50000
SET LONG 10000
SET ECHO OFF
SET COLSEP ';'
SET VERIFY OFF
SET NUMFORMAT 9999999999999999.999
SET SERVEROUTPUT ON SIZE 1000000
  SELECT OWNER, TABLE_NAME, TRUNC (SUM (BYTES) / 1024 / 1024 / 1024) "SIZE(GB)"
    FROM (SELECT SEGMENT_NAME TABLE_NAME, OWNER, BYTES
            FROM DBA_SEGMENTS
           WHERE SEGMENT_TYPE = 'TABLE' AND SEGMENT_NAME = '&TABLE_NAME'
          UNION ALL
          SELECT I.TABLE_NAME, I.OWNER, S.BYTES
            FROM DBA_INDEXES I, DBA_SEGMENTS S
           WHERE     S.SEGMENT_NAME = I.INDEX_NAME
                 AND S.OWNER = I.OWNER
                 AND S.SEGMENT_TYPE = 'INDEX'
                 AND I.TABLE_NAME = '&TABLE_NAME'
          UNION ALL
          SELECT L.TABLE_NAME, L.OWNER, S.BYTES
            FROM DBA_LOBS L, DBA_SEGMENTS S
           WHERE     S.SEGMENT_NAME = L.SEGMENT_NAME
                 AND L.TABLE_NAME = '&TABLE_NAME'
                 AND S.OWNER = L.OWNER
                 AND S.SEGMENT_TYPE = 'LOBSEGMENT'
          UNION ALL
          SELECT L.TABLE_NAME, L.OWNER, S.BYTES
            FROM DBA_LOBS L, DBA_SEGMENTS S
           WHERE     S.SEGMENT_NAME = L.INDEX_NAME
                 AND L.TABLE_NAME = '&TABLE_NAME'
                 AND S.OWNER = L.OWNER
                 AND S.SEGMENT_TYPE = 'LOBINDEX')
GROUP BY TABLE_NAME, OWNER
  HAVING SUM (BYTES) / 1024 / 1024 / 1024 > 1
ORDER BY SUM (BYTES) DESC;

/

/* Objects relate table*/

accept TABLE_NAME PROMPT 'Enter the TABLE_NAME >'
SET FEEDBACK OFF
SET HEAD ON
SET ARRAY 1
SET LINESIZE 10000
SET PAGESIZE 50000
SET LONG 10000
SET ECHO OFF
SET COLSEP ';'
SET VERIFY OFF
SET NUMFORMAT 9999999999999999.999
SET SERVEROUTPUT ON SIZE 1000000
  SELECT OBJECT_NAME, OWNER, TRUNC (SUM (BYTES) / 1024 / 1024) "SIZE(MB)"
    FROM (SELECT SEGMENT_NAME OBJECT_NAME, OWNER, BYTES
            FROM DBA_SEGMENTS
           WHERE SEGMENT_TYPE = 'TABLE' AND SEGMENT_NAME = '&TABLE_NAME'
          UNION ALL
          SELECT I.INDEX_NAME OBJECT_NAME, I.OWNER, S.BYTES
            FROM DBA_INDEXES I, DBA_SEGMENTS S
           WHERE     S.SEGMENT_NAME = I.INDEX_NAME
                 AND S.SEGMENT_TYPE = 'INDEX'
                 AND I.TABLE_NAME = '&TABLE_NAME'
          UNION ALL
          SELECT L.SEGMENT_NAME OBJECT_NAME, L.OWNER, S.BYTES
            FROM DBA_LOBS L, DBA_SEGMENTS S
           WHERE     S.SEGMENT_NAME = L.SEGMENT_NAME
                 AND L.TABLE_NAME = '&TABLE_NAME'
                 AND S.OWNER = L.OWNER
                 AND S.SEGMENT_TYPE = 'LOBSEGMENT'
          UNION ALL
          SELECT s.SEGMENT_NAME OBJECT_NAME, L.OWNER, S.BYTES
            FROM DBA_LOBS L, DBA_SEGMENTS S
           WHERE     S.SEGMENT_NAME = L.INDEX_NAME
                 AND L.TABLE_NAME = '&TABLE_NAME'
                 AND S.OWNER = L.OWNER
                 AND S.SEGMENT_TYPE = 'LOBINDEX')
GROUP BY OBJECT_NAME, OWNER
ORDER BY OBJECT_NAME;
/

No comments:

Post a Comment