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.
Pardy DBA 