Monthly Archives: September 2013

SQL to query table size and DBMS_REDEFINITION progress

Like so many other Oracle DBAs, I need a script to query the total disk space used by an individual table, including the data, indexes and LOBs, that works whether or not the table uses partitioning.  I also wanted a script to monitor the progress of DBMS_REDEFINITION actions.  Here I provide a single script that does both.

Sample output during a DBMS_REDEFINITION run, with my SAP system name redacted:

SQL> @s
Enter value for segment: reposrc

ACTION          TARGET                              REMAINS  PROGRESS
--------------- ----------------------------------- -------- ---------------
Table Scan      SAP***.REPOSRC                      00:08:45 4.89%

SEGTYPE         SEGMENT                               SIZEMB TABLESPACE
--------------- ----------------------------------- -------- ---------------
1-TABLE         SAP***.REPOSRC                          3230 PSAP***702
                SAP***.REPOSRC#$                         160 PSAP***702
***************                                     --------
sum                                                     3390

2-INDEX         SAP***.REPOSRC^0                         136 PSAP***702
                SAP***.REPOSRC^SPM                       136 PSAP***702
***************                                     --------
sum                                                      272

3-LOBDATA       DATA:SAP***.REPOSRC                     3365 PSAP***702
                DATA:SAP***.REPOSRC#$                    192 PSAP***702
***************                                     --------
sum                                                     3557

4-LOBINDEX      DATA:SAP***.REPOSRC                        0 PSAP***702
                DATA:SAP***.REPOSRC#$                      0 PSAP***702
***************                                     --------
sum                                                        0

                                                    --------
sum                                                     7219

The first result block shows the current action (a table scan, in this instance), the name of the table, time remaining in hours:minutes:seconds format and the completion percentage from V$SESSION_LONGOPS.  As a side benefit, if you run this against a table that has some other long operation running against it, you will see that here as well.  It works for more than just table redefinitions.

The second result block displays the space used by the original table (REPOSRC) and the intermediate table used during DBMS_REDEFINITION (REPOSRC#), along with all segment types in use by both tables (table data, indexes, LOB data and LOB indexes).  For the LOB data and indexes, the “SEGMENT” column shows the LOB column name followed by the table name.

Another example of output from the same script, this time for a partitioned table with no LOBs and no redefinition running, from my EM12c repository database:

SQL> @s
Enter value for segment: em_metric_values_daily

SEGTYPE         SEGMENT                               SIZEMB TABLESPACE
--------------- ----------------------------------- -------- ---------------
1-TABLE         SYSMAN.EM_METRIC_VALUES_DAILY            327 MGMT_TABLESPACE
***************                                     --------
sum                                                      327

2-INDEX         SYSMAN.EM_METRIC_VALUES_DAILY_PK          48 MGMT_TABLESPACE
***************                                     --------
sum                                                       48

                                                    --------
sum                                                      375

The script:

SET PAGES 30
SET VERIFY OFF
SET FEEDBACK OFF

COLUMN ACTION FORMAT A15
COLUMN TARGET FORMAT A35
COLUMN PROGRESS FORMAT A15
COLUMN REMAINS FORMAT A8

SELECT
  OPNAME ACTION,
  TARGET,
  TO_CHAR(TO_DATE(TIME_REMAINING, 'sssss'), 'hh24:mi:ss') REMAINS,
  TO_CHAR(TRUNC(ELAPSED_SECONDS/(ELAPSED_SECONDS+TIME_REMAINING)*100,2))
  || '%' PROGRESS
FROM
  V$SESSION_LONGOPS
WHERE
  TIME_REMAINING != 0
AND TARGET LIKE UPPER('%&&segment%');

COLUMN SEGTYPE FORMAT A15
COLUMN SEGMENT FORMAT A35
COLUMN SIZEMB FORMAT 9999999
COLUMN TABLESPACE FORMAT A15

BREAK ON SEGTYPE SKIP 1 ON REPORT

COMPUTE SUM OF SIZEMB ON SEGTYPE
COMPUTE SUM OF SIZEMB ON REPORT

SELECT
  SEGTYPE,
  SEG SEGMENT,
  SIZEMB,
  TABLESPACE_NAME TABLESPACE
FROM
  (
    SELECT
      '1-TABLE' SEGTYPE,
      S.OWNER
      || '.'
      || S.SEGMENT_NAME SEG,
      TRUNC(SUM(BYTES)/1024/1024) SIZEMB,
      S.TABLESPACE_NAME
    FROM
      DBA_SEGMENTS S
    WHERE
      (
        S.SEGMENT_NAME = UPPER('&&segment')
      OR S.SEGMENT_NAME LIKE UPPER('&&segment#%')
      )
    AND S.SEGMENT_TYPE LIKE 'TABLE%'
    GROUP BY
      S.OWNER
      || '.'
      || SEGMENT_NAME,
      TABLESPACE_NAME
    UNION
    SELECT
      '2-INDEX' SEGTYPE,
      S.OWNER
      || '.'
      || S.SEGMENT_NAME SEG,
      TRUNC(SUM(S.BYTES)/1024/1024) SIZEMB,
      S.TABLESPACE_NAME
    FROM
      DBA_SEGMENTS S,
      DBA_INDEXES I
    WHERE
      S.SEGMENT_NAME = I.INDEX_NAME
    AND S.SEGMENT_TYPE LIKE 'INDEX%'
    AND S.OWNER = I.OWNER
    AND
      (
        I.TABLE_NAME = UPPER('&&segment')
      OR I.TABLE_NAME LIKE UPPER('&&segment#%')
      )
    GROUP BY
      S.OWNER
      || '.'
      || S.SEGMENT_NAME,
      S.TABLESPACE_NAME
    UNION
    SELECT
      '3-LOBDATA' SEGTYPE,
      L.COLUMN_NAME
      || ':'
      || S.OWNER
      || '.'
      || L.TABLE_NAME SEG,
      TRUNC(SUM(S.BYTES)/1024/1024) SIZEMB,
      S.TABLESPACE_NAME
    FROM
      DBA_SEGMENTS S,
      DBA_LOBS L
    WHERE
      S.SEGMENT_NAME = L.SEGMENT_NAME
    AND
      (
        S.SEGMENT_TYPE = 'LOBSEGMENT'
      OR S.SEGMENT_TYPE LIKE 'LOB %'
      )
    AND S.OWNER = L.OWNER
    AND
      (
        L.TABLE_NAME = UPPER('&&segment')
      OR L.TABLE_NAME LIKE UPPER('&&segment#%')
      )
    GROUP BY
      L.COLUMN_NAME
      || ':'
      || S.OWNER
      || '.'
      || L.TABLE_NAME,
      S.TABLESPACE_NAME
    UNION
    SELECT
      '4-LOBINDEX' SEGTYPE,
      L.COLUMN_NAME
      || ':'
      || S.OWNER
      || '.'
      || L.TABLE_NAME SEG,
      TRUNC(SUM(S.BYTES)/1024/1024) SIZEMB,
      S.TABLESPACE_NAME
    FROM
      DBA_SEGMENTS S,
      DBA_LOBS L
    WHERE
      S.SEGMENT_NAME   = L.INDEX_NAME
    AND S.SEGMENT_TYPE = 'LOBINDEX'
    AND S.OWNER        = L.OWNER
    AND
      (
        L.TABLE_NAME = UPPER('&&segment')
      OR L.TABLE_NAME LIKE UPPER('&&segment#%')
      )
    GROUP BY
      L.COLUMN_NAME
      || ':'
      || S.OWNER
      || '.'
      || L.TABLE_NAME,
      S.TABLESPACE_NAME
  )
ORDER BY
  SEGTYPE,
  SEG ;
UNDEFINE segment;

I based this on a script I initially found at stackoverflow.