Oracle | DBMS

Oracle DBMS_SPACE Version 21c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

Purpose This package provides segment space information not currently available through the standard views

Note For the object growth trend functions the following must be kept in mind to understand the output..

QUALITY: A value indicating how well the requested reporting interval matches the actual recording of statistics. This information is useful because there is no guaranteed reporting interval for object size use statistics, and the actual reporting interval varies over time and from object to object.

The values of the QUALITY column are:
GOOD: The value whenever the value of TIME is based on recorded statistics with a recorded timestamp within 10% of the INTERVAL specified in the input parameters.

INTERPOLATED: The value did not meet the criteria for GOOD, but was based on recorded statistics before and after the value of TIME. Current in-memory statistics can be collected across all instances in a cluster and treated as the "recorded" value for the present time.

PROJECTION: The value of TIME is in the future as of the time the table was produced. In an Oracle Real Application Clusters environment, the rules for recording statistics allow each instance to choose independently which objects will be selected.

The output returned by these functions is an aggregation of values recorded across all instances in an Oracle RAC environment. Each value can be computed from a combination of GOOD and INTERPOLATED values. The aggregate value returned is marked GOOD if at least 80% of that value was derived from GOOD instance values.

AUTHID CURRENT_USER

Constants

Name	Data Type	Value
OBJECT_TYPE_TABLE	POSITIVE	1
OBJECT_TYPE_NESTED_TABLE	POSITIVE	2
OBJECT_TYPE_INDEX	POSITIVE	3
OBJECT_TYPE_CLUSTER	POSITIVE	4
OBJECT_TYPE_LOB_INDEX	POSITIVE	5
OBJECT_TYPE_LOBSEGMENT	POSITIVE	6
OBJECT_TYPE_TABLE_PARTITION	POSITIVE	7
OBJECT_TYPE_INDEX_PARTITION	POSITIVE	8
OBJECT_TYPE_TABLE_SUBPARTITION	POSITIVE	9
OBJECT_TYPE_INDEX_SUBPARTITION	POSITIVE	10
OBJECT_TYPE_LOB_PARTITION	POSITIVE	11
OBJECT_TYPE_LOB_SUBPARTITION	POSITIVE	12
OBJECT_TYPE_MV	POSITIVE	13
OBJECT_TYPE_MVLOG	POSITIVE	14
OBJECT_TYPE_ROLLBACK_SEGMENT	POSITIVE	15
SPACEUSAGE_EXACT (Computes space usage exhaustively)	POSITIVE	16
SPACEUSAGE_FAST (Retrieves values from in-memory statistics)	POSITIVE	17

Data Types TYPE asa_reco_row IS RECORD ( tablespace_name VARCHAR2(30), segment_owner VARCHAR2(30), segment_name VARCHAR2(30), segment_type VARCHAR2(18), partition_name VARCHAR(30), allocated_space NUMBER, used_space NUMBER, reclaimable_space NUMBER, chain_rowexcess NUMBER, ioreqpm NUMBER, iowaitpm NUMBER, iowaitpr NUMBER, recommendations VARCHAR2(1000), c1 VARCHAR2(1000), c2 VARCHAR2(1000), c3 VARCHAR2(1000), task_id NUMBER, mesg_id NUMBER); TYPE asa_reco_row_tb IS RECORD ( tablespace_name VARCHAR2(30), segment_owner VARCHAR2(30), segment_name VARCHAR2(30), segment_type VARCHAR2(18), partition_name VARCHAR2(30), allocated_space NUMBER, used_space NUMBER, reclaimable_space NUMBER, chain_rowexcess NUMBER, recommendations VARCHAR2(1000), c1 VARCHAR2(1000), c2 VARCHAR2(1000), c3 VARCHAR2(1000), task_id NUMBER, mesg_id NUMBER)); -- content of one row in dependent_segments table TYPE object_dependent_segment IS RECORD ( segment_owner VARCHAR2(100), segment_name VARCHAR2(100), segment_type VARCHAR2(100), tablespace_name VARCHAR2(100), partition_name VARCHAR2(100), lob_column_name VARCHAR2(100)); -- a table of dependent_segment records TYPE dependent_segments_table IS TABLE OF object_dependent_segment; -- object_growth_trend_row and object_growth_trend_table are used -- by the object_growth_trend table function to describe its output TYPE object_growth_trend_row IS RECORD ( timepoint TIMESTAMP, space_usage NUMBER, space_alloc NUMBER, quality VARCHAR2(20)); TYPE object_growth_trend_table IS TABLE OF object_growth_trend_row; /* object_growth_swrf_row, object_growth_swrf_table, object_growth_swrf_cursor, object_growth_trend_curtab, and object_growth_trend_test_swrf are internal to the implementation of object_growth_trend but need to be declared here instead of in the private package body. These internal types and procedures do not expose any internal information to the user. */ TYPE object_growth_swrf_cursor IS REF CURSOR RETURN object_growth_swrf_row; TYPE object_growth_swrf_row IS RECORD ( timepoint TIMESTAMP, delta_space_usage NUMBER, delta_space_alloc NUMBER, total_space_usage NUMBER, total_space_alloc NUMBER, instance_number NUMBER, objn NUMBER); TYPE object_growth_swrf_table IS TABLE OF object_growth_swrf_row; -- type owned by SYS CREATE TYPE create_table_cost_colinfo IS OBJECT ( col_type VARCHAR(200), col_size NUMBER); / -- type owned by SYS CREATE TYPE create_table_cost_columns IS VARRAY(50000) OF create_table_cost_colinfo; / -- type owned by SYS CREATE TYPE tablespace_list IS VARRAY (64000) OF NUMBER; / TYPE verify_shrink_row IS RECORD(status NUMBER); TYPE verify_shrink_table IS TABLE OF of verify_shrink_row; TYPE object_space_usage_row IS RECORD ( space_used NUMBER, space_allocated NUMBER, chain_pcent NUMBER); TYPE object_space_usage_table IS TABLE OF object_space_usage_row;

Dependencies

CREATE_TABLE_COST_COLINFO	DBA_TAB_PARTITIONS	SYS_DBA_SEGS
CREATE_TABLE_COST_COLUMNS	DBA_TS_QUOTAS	SYS_UNCOMPRESSED_SEGS
DBA_ADVISOR_ACTIONS	DBMS_ADVISOR	TABLESPACE_LIST
DBA_ADVISOR_OBJECTS	DBMS_AQ_IND_MON	TS$
DBA_ADVISOR_OBJECT_TYPES	DBMS_ASSERT	USER$
DBA_ADVISOR_RECOMMENDATIONS	DBMS_DBFS_SFS_ADMIN	USER_INDEXES
DBA_ADVISOR_TASKS	DBMS_HEAT_MAP	V$ASM_DISKGROUP_STAT
DBA_DATA_FILES	DBMS_IREFSTATS	V$DATAFILE
DBA_FREE_SPACE	DBMS_LOCK	V$PARAMETER
DBA_HIST_ACTIVE_SESS_HISTORY	DBMS_OUTPUT	WRH$_SEG_STAT
DBA_INDEXES	DBMS_SODA_ADMIN	WRI$_ADV_ACTIONS
DBA_IND_PARTITIONS	DBMS_SPACE_ADMIN	WRI$_ADV_FINDINGS
DBA_LOBS	DBMS_STANDARD	WRI$_ADV_MESSAGE_GROUPS
DBA_LOB_PARTITIONS	DBMS_STATS_INTERNAL	WRI$_ADV_OBJECTS
DBA_LOB_SUBPARTITIONS	DUAL	WRI$_ADV_OBJSPACE_TREND_T
DBA_NESTED_TABLES	NLS_SESSION_PARAMETERS	WRI$_ADV_RECOMMENDATIONS
DBA_OBJECTS	OBJ$	WRI$_ALERT_OUTSTANDING
DBA_OUTSTANDING_ALERTS	PLITBLM	WRI$_SEGADV_CNTRLTAB
DBA_SCHEDULER_GLOBAL_ATTRIBUTE	PRVT_COMPRESSION	WRI$_SEGADV_OBJLIST
DBA_SCHEDULER_RUNNING_JOBS	PRVT_DBMS_INMEMORY_ADMIN	X$KCFISTSA
DBA_SCHEDULER_WINDOWS	RECYCLEBIN$	X$KSPPCV
DBA_TABLES	SEG$	X$KSPPI
DBA_TABLESPACE_USAGE_METRICS

Documented Yes

Exceptions

Error Code	Reason
ORA-03213	Invalid Lob Segment Name for DBMS_SPACE package

First Available 7.3.4

Security Model Owned by SYS with EXECUTE granted to PUBLIC

Source {ORACLE_HOME}/rdbms/admin/dbmsspu.sql

Subprograms

ASA_RECOMMENDATIONS	OBJECT_DEPENDENT_SEGMENTS	OBJECT_SPACE_USAGE
AUTO_SPACE_ADVISOR_JOB_PROC	OBJECT_GROWTH_TREND	OBJECT_SPACE_USAGE_TBF
CREATE_INDEX_COST	OBJECT_GROWTH_TREND_CUR	PARSE_SPACE_ADV_INFO
CREATE_TABLE_COST	OBJECT_GROWTH_TREND_CURTAB	SPACE_USAGE
DBFS_DF	OBJECT_GROWTH_TREND_I_TO_S	UNUSED_SPACE
FREE_BLOCKS	OBJECT_GROWTH_TREND_SWRF	VERIFY_SHRINK_CANDIDATE
GET_COMPRESSION_RATIO	OBJECT_GROWTH_TREND_S_TO_I	VERIFY_SHRINK_CANDIDATE_TBF
ISDATAFILEDROPPABLE_NAME

ASA_RECOMMENDATIONS

Returns recommendations / findings of segment advisor run automatically by the system or manually invoked by the user dbms_space.asa_recommendations ( all_runs IN VARCHAR2 DEFAULT := TRUE, show_manual IN VARCHAR2 DEFAULT := TRUE, show_findings IN VARCHAR2 DEFAULT := 'FALSE') RETURN asa_reco_row_tb PIPELINED;

set linesize 121

      col tablespace_name format a20

      col segment_owner format a20

      col segment_name format a20

      col segment_type format a15

      col recommendations format a60

      

      SELECT tablespace_name, segment_owner, segment_name, segment_type,

      allocated_space, used_space, reclaimable_space

      FROM (

        SELECT *

        FROM TABLE(dbms_space.asa_recommendations('TRUE','TRUE','TRUE')));

      

      SELECT segment_owner, segment_name, recommendations

      FROM (

        SELECT *

        FROM TABLE(dbms_space.asa_recommendations('TRUE','TRUE','TRUE')));

AUTO_SPACE_ADVISOR_JOB_PROC

Undocumented dbms_space.auto_space_advisor_job_proc;

exec dbms_space.auto_space_advisor_job_proc;

CREATE_INDEX_COST

Determines the cost of creating an index on an existing table dbms_space.create_index_cost( ddl IN VARCHAR2, used_bytes OUT NUMBER, alloc_bytes OUT NUMBER, plan_table IN VARCHAR2 DEFAULT NULL);

CREATE TABLE t (

      person_id  NUMBER(5),

      first_name VARCHAR2(30),

      last_name  VARCHAR2(30));

      

      set serveroutput on

      

      DECLARE

       ub  NUMBER;

       ab  NUMBER;

      BEGIN

        dbms_space.create_index_cost('CREATE INDEX t_pid ON t(person_id)', ub, ab);

      

        dbms_output.put_line('Used Bytes: ' || TO_CHAR(ub));

        dbms_output.put_line('Alloc Bytes: ' || TO_CHAR(ab));

      END;

      /

CREATE_TABLE_COST

Determines the size of the table given various attributes

Overload 1 dbms_space.create_table_cost( tablespace_name IN VARCHAR2, avg_row_size IN NUMBER, row_count IN NUMBER, pct_free IN NUMBER, used_bytes OUT NUMBER, alloc_bytes OUT NUMBER);

set serveroutput on

      

      DECLARE

       ub NUMBER;

       ab NUMBER;

      BEGIN

        dbms_space.create_table_cost('UWDATA',28,250000,0,ub,ab);

      

        dbms_output.put_line('Used Bytes: ' || TO_CHAR(ub));

        dbms_output.put_line('Alloc Bytes: ' || TO_CHAR(ab));

      END;

      /

Overload 2 dbms_space.create_table_cost( tablespace_name IN VARCHAR2, colinfos IN CREATE_TABLE_COST_COLUMNS, row_count IN NUMBER, pct_free IN NUMBER, used_bytes OUT NUMBER, alloc_bytes OUT NUMBER);

set linesize 121

      col type_owner format a20

      col data_type format a20

      

      SELECT argument_name, data_type, type_owner, type_name

      FROM all_arguments

      WHERE object_name = 'CREATE_TABLE_COST'

      AND overload = 2;

      

      SELECT text

      FROM dba_source

      WHERE name = 'CREATE_TABLE_COST_COLUMNS';

      

      SELECT text

      FROM dba_source

      WHERE name = 'CREATE_TABLE_COST_COLINFO';

      

      set serveroutput on

      

      DECLARE

       ub NUMBER;

       ab NUMBER;

       cl sys.create_table_cost_columns;

      BEGIN

        cl := sys.create_table_cost_columns(
      sys.create_table_cost_colinfo('NUMBER',10),

      sys.create_table_cost_colinfo('VARCHAR2',30),

      sys.create_table_cost_colinfo('VARCHAR2',30),

      sys.create_table_cost_colinfo('DATE',NULL));

      

        dbms_space.create_table_cost('UWDATA',cl,100000,0,ub,ab);

      

        dbms_output.put_line('Used Bytes: ' || TO_CHAR(ub));

        dbms_output.put_line('Alloc Bytes: ' || TO_CHAR(ab));

      END;

      /

DBFS_DF

Returns the free space in the storage used by the tablespaces by specifying their data files

For reasons not readily apparent the function appears to always return zero dbms_space.dbfs_df( userid IN NUMBER, ntbs IN NUMBER, ints_list IN tablespace_list) RETURN NUMBER;

SELECT tablespace_name, file_id

      FROM dba_data_files

      WHERE tablespace_name LIKE ('%SYS%');

      

      set serveroutput on

      

      DECLARE

       l_tbsList sys.tablespace_list := sys.tablespace_list(NULL);

       RetVal    NUMBER;

      BEGIN

        l_tbsList(1) := 1;

        l_tbsList.EXTEND(2);

        l_tbsList(2) := 5;

        l_tbsList(3) := 6;

      

        RetVal := dbms_space.dbfs_df(sys_context('USERENV', 'SESSION_USERID'), l_tbsList.COUNT, l_tbsList);

        dbms_output.put_line(TO_CHAR(RetVal));

      END;

      /

FREE_BLOCKS

Information about free blocks in a table, index, or cluster. Only work on tablespaces w/o ASSM. With ASSM use dbms_space.space_usage

dbms_space.free_blocks (

      segment_owner     IN  VARCHAR2,

      segment_name      IN  VARCHAR2,

      segment_type      IN  VARCHAR2,

      freelist_group_id IN  NUMBER,

      free_blks         OUT NUMBER,

      scan_limit        IN  NUMBER   DEFAULT NULL,

      partition_name    IN  VARCHAR2 DEFAULT NULL);

Segment Types

CLUSTER

INDEX

INDEX PARTITION

INDEX SUBPARTITION

LOB

LOB PARTITION

LOB SUBPARTITION

TABLE

TABLE PARTITION

TABLE SUBPARTITION

SELECT tablespace_name, segment_space_management

      FROM dba_tablespaces;

      

      set serveroutput on

      

      DECLARE

       free_blks NUMBER;

      BEGIN

        dbms_space.free_blocks('SYS', 'TAB$', 'TABLE', 0, free_blks);

        dbms_output.put_line('Free Blocks: ' || TO_CHAR(free_blks));

      END;

      /

GET_COMPRESSION_RATIO

Returns information to guide the decision as to whether to compress a heap table dbms_space.get_compression_ratio( scratchtbsname IN VARCHAR2, ownname IN VARCHAR2, objname IN VARCHAR2, subobjname IN VARCHAR2, comptype IN NUMBER, blkcnt_cmp OUT PLS_INTEGER, blkcnt_uncmp OUT PLS_INTEGER, row_cmp OUT PLS_INTEGER, row_uncmp OUT PLS_INTEGER, cmp_ratio OUT NUMBER, comptype_str OUT VARCHAR2);

TBD

ISDATAFILEDROPPABLE_NAME

Checks whether datafile is droppable dbms_space.isdatafiledroppable_name( filename IN VARCHAR2, value OUT NUMBER);

-- 0 = not droppable, 1 = droppable

SELECT file_name FROM dba_data_files;

      

      DECLARE

       fname  VARCHAR2(100);

       retval VARCHAR2(100);

      BEGIN

        SELECT file_name

        INTO fname

        FROM dba_data_files

        WHERE rownum = 1;

      

        dbms_space.isdatafiledroppable_name(fname, retval);

        dbms_output.put_line(retval);

      END;

      /

OBJECT_DEPENDENT_SEGMENTS

Returns the list of segments that are associated with an object dbms_space.object_dependent_segments( objowner IN VARCHAR2, objname IN VARCHAR2, partname IN VARCHAR2, objtype IN NUMBER) RETURN dependent_segments_table PIPELINED;

set linesize 121

      col segment_owner   format a20

      col segment_name    format a30

      col segment_type    format a15

      col tablespace_name format a15

      col partition_name  format a15

      col lob_column_name format a10

      

      set serveroutput on

      

      SELECT segment_owner, segment_name, segment_type, tablespace_name

      FROM (TABLE(dbms_space.object_dependent_segments('UWCLASS', 'SERV_INST', NULL, 1)));

OBJECT_GROWTH_TREND

Pipelined table function where each row describes the space usage of the object at a specific point in time dbms_space.object_growth_trend ( object_owner IN VARCHAR2, object_name IN VARCHAR2, object_type IN VARCHAR2, partition_name IN VARCHAR2 DEFAULT NULL, start_time IN TIMESTAMP DEFAULT NULL, end_time IN TIMESTAMP DEFAULT NULL, interval IN DSINTERVAL_UNCONSTRAINED DEFAULT NULL, skip_interpolated IN VARCHAR2 DEFAULT 'FALSE', timeout_seconds IN NUMBER DEFAULT NULL, single_datapoint_flag IN VARCHAR2 DEFAULT 'TRUE') RETURN object_growth_trend_table PIPELINED;

set linesize 121

      col timepoint format a40

      

      SELECT *

      FROM TABLE(dbms_space.object_growth_trend('SYS', 'TAB$', 'TABLE'));

OBJECT_GROWTH_TREND_CUR

Undocumented dbms_space.object_growth_trend_curtab( object_owner IN VARCHAR2, object_name IN VARCHAR2, object_type IN VARCHAR2, partition_name IN VARCHAR2 DEFAULT NULL, start_time IN TIMESTAMP DEFAULT NULL, end_time IN TIMESTAMP DEFAULT NULL, interval IN DSINTERVAL_UNCONSTRAINED DEFAULT NULL, skip_interpolated IN VARCHAR2 DEFAULT 'FALSE', timeout_seconds IN NUMBER DEFAULT NULL) RETURN SYS_REFCURSOR;

TBD

OBJECT_GROWTH_TREND_CURTAB

Undocumented dbms_space.object_growth_trend_curtab RETURN object_growth_trend_table PIPELINED;

TBD

OBJECT_GROWTH_TREND_I_TO_S

Undocumented dbms_space.object_growth_trend_i_to_s(interv IN DSINTERVAL_UNCONSTRAINED) RETURN NUMBER;

TBD

OBJECT_GROWTH_TREND_SWRF

Pipelined table function returning timestamp, change in space usage, change in space allocation, instance number, and object number dbms_space.object_growth_trend_swrf ( object_owner IN VARCHAR2, object_name IN VARCHAR2, object_type IN VARCHAR2, partition_name IN VARCHAR2 DEFAULT NULL) RETURN object_growth_swrf_table PIPELINED;

set linesize 121

      col timepoint format a40

      

      SELECT timepoint,
      delta_space_usage, delta_space_alloc, total_space_usage, total_space_alloc

      FROM TABLE(dbms_space.object_growth_trend_swrf('SYS', 'TAB$', 'TABLE'))

      ORDER BY 1;

OBJECT_GROWTH_TREND_S_TO_I

Undocumented dbms_space.object_growth_trend_s_to_i(secsin IN NUMBER) RETURN DSINTERVAL_UNCONSTRAINED;

TBD

OBJECT_SPACE_USAGE

Returns the space used, space allocated, and percentage of chained rows in a table dbms_space.object_space_usage ( object_owner IN VARCHAR2, object_name IN VARCHAR2, object_type IN VARCHAR2, sample_control IN NUMBER, space_used OUT NUMBER, space_allocated OUT NUMBER, chain_pcent OUT NUMBER, partition_name IN VARCHAR2 DEFAULT NULL, preserve_result IN BOOLEAN DEFAULT TRUE, timeout_seconds IN NUMBER DEFAULT NULL);

set serveroutput on

      

      DECLARE

       su NUMBER;

       sa NUMBER;

       cp NUMBER;

      BEGIN

        dbms_space.object_space_usage('UWCLASS', 'SERVERS', 'TABLE', NULL, su, sa, cp);

      

        dbms_output.put_line('Space Used: ' || TO_CHAR(su));

        dbms_output.put_line('Space Allocated: ' || TO_CHAR(sa));

        dbms_output.put_line('Chained Percentage: ' || TO_CHAR(cp));

      END;

      /

OBJECT_SPACE_USAGE_TBF

Pipelined table function returning space used, space allocated, and percentage of chained rows in a table dbms_space.object_space_usage_tbf( object_owner IN VARCHAR2, object_name IN VARCHAR2, object_type IN VARCHAR2, sample_control IN NUMBER, partition_name IN VARCHAR2 DEFAULT NULL, preserve_result IN VARCHAR2 DEFAULT 'TRUE', timeout_seconds IN NUMBER DEFAULT NULL) RETURN object_space_usage_table pipelined;

SELECT *

      FROM TABLE(dbms_space.object_space_usage_tbf('UWCLASS', 'SERVERS', 'TABLE', NULL));

PARSE_SPACE_ADV_INFO

Undocumented dbms_space.parse_space_adv_info( info IN VARCHAR2, used_space OUT VARCHAR2, allocated_space OUT VARCHAR2, reclaimable_space OUT VARCHAR2);

TBD

SPACE_USAGE

Shows the space usage of data blocks under the segment High Water Mark. The bitmap blocks, segment header, and extent map blocks are not accounted for by this procedure. This procedure can only be used on tablespaces that are created with auto segment space management (ASSM).

Overload 1 dbms_space.space_usage( segment_owner IN VARCHAR2, segment_name IN VARCHAR2, segment_type IN VARCHAR2, unformatted_blocks OUT NUMBER, unformatted_bytes OUT NUMBER, fs1_blocks OUT NUMBER, -- blocks w/ 0-25% free space fs1_bytes OUT NUMBER, -- bytes w/ 0-25% free space fs2_blocks OUT NUMBER, -- blocks w/ 25-50% free space fs2_bytes OUT NUMBER, -- bytes w/ 25-50% free space fs3_blocks OUT NUMBER, -- blocks w/ 50-75% free space fs3_bytes OUT NUMBER, -- bytes w/ 50-75% free space fs4_blocks OUT NUMBER, -- blocks w/ 75-100% free space fs4_bytes OUT NUMBER, -- bytes w/ 75-100% free space full_blocks OUT NUMBER, full_bytes OUT NUMBER, partition_name IN VARCHAR2 DEFAULT NULL);

conn / as sysdba

      

      SELECT blocks

      FROM dba_segments

      WHERE segment_name = 'SERVERS';

      

      set serveroutput on

      

      DECLARE

       uf   NUMBER;

       ub   NUMBER;

       f1   NUMBER;

       f1b  NUMBER;

       f2   NUMBER;

       f2b  NUMBER;

       f3   NUMBER;

       f3b  NUMBER;

       f4   NUMBER;

       f4b  NUMBER;

       fbl  NUMBER;

       fby  NUMBER;

      BEGIN

        dbms_space.space_usage('UWCLASS','SERVERS', 'TABLE', uf, ub,  f1,  f1b,  f2,  f2b,  f3,  f3b,  f4,  f4b, fbl, fby);

      

        dbms_output.put_line('unformatted blocks:  ' || TO_CHAR(uf));

        dbms_output.put_line('unformatted bytes:   ' || TO_CHAR(ub));

        dbms_output.put_line('blocks 0-25% free:   ' || TO_CHAR(f1));

        dbms_output.put_line('bytes 0-25% free:    ' || TO_CHAR(f1b));

        dbms_output.put_line('blocks 25-50% free:  ' || TO_CHAR(f2));

        dbms_output.put_line('bytes 25-50% free:   ' || TO_CHAR(f2b));

        dbms_output.put_line('blocks 50-75% free:  ' || TO_CHAR(f3));

        dbms_output.put_line('bytes 50-75% free:   ' || TO_CHAR(f3b));

        dbms_output.put_line('blocks 75-100% free: ' || TO_CHAR(f4));

        dbms_output.put_line('bytes 75-100% free:  ' || TO_CHAR(f4b));

        dbms_output.put_line('full blocks:         ' || TO_CHAR(fbl));

        dbms_output.put_line('full bytes:          ' || TO_CHAR(fby));

      END;

      /

      

      CREATE TABLE uwclass.servers_bak AS

      SELECT * FROM uwclass.servers

      WHERE srvr_id LIKE '%5%';

      

      DELETE FROM uwclass.servers

      WHERE srvr_id LIKE '%5%';

      

      COMMIT;

      

      INSERT /*+ APPEND */ INTO uwclass.servers

      SELECT * FROM uwclass.servers_bak;

Returns the amount of space in blocks being used by all the SECUREFILE LOBs in the LOB segment. The procedure displays the space actively used by the LOB column, freed space that has retention expired, and freed space that has retention unexpired.

Overload 2

I want to thank Dave Franks for catching the missing description of why the demo at right returns an exception. The exception has now been properly color highlighted and the exception added to the Exceptions section above. dbms_space.space_usage( segment_owner IN VARCHAR2, segment_name IN VARCHAR2, segment_type IN VARCHAR2, segment_size_blocks OUT NUMBER, segment_size_bytes OUT NUMBER, used_blocks OUT NUMBER, used_bytes OUT NUMBER, expired_blocks OUT NUMBER, expired_bytes OUT NUMBER, unexpired_blocks OUT NUMBER, unexpired_bytes OUT NUMBER, partition_name IN VARCHAR2 DEFAULT NULL);

set serveroutput on

      

      DECLARE

       sbl  NUMBER;

       sby  NUMBER;

       ubl  NUMBER;

       uby  NUMBER;

       ebl  NUMBER;

       eby  NUMBER;

       xbl  NUMBER;

       xby  NUMBER;

      BEGIN

        dbms_space.space_usage('UWCLASS','SERVERS', 'TABLE', sbl, sby, ubl, uby, ebl, eby, xbl, xby);

      

        dbms_output.put_line('segment size blocks: ' || TO_CHAR(sbl));

        dbms_output.put_line('segment size bytes:  ' || TO_CHAR(sby));

        dbms_output.put_line('used blocks:         ' || TO_CHAR(ubl));

        dbms_output.put_line('used bytes:          ' || TO_CHAR(uby));

        dbms_output.put_line('expired blocks:      ' || TO_CHAR(ebl));

        dbms_output.put_line('expired bytes:       ' || TO_CHAR(eby));

        dbms_output.put_line('unexpired blocks:    ' || TO_CHAR(xbl));

        dbms_output.put_line('unexpired bytes:     ' || TO_CHAR(xby));

      END;

      /

      

      DECLARE

      *

      ERROR at line 1:

      ORA-03213: Invalid Lob Segment Name for DBMS_SPACE package

      ORA-06512: at "SYS.DBMS_SPACE", line 234

      ORA-06512: at line 11

This overload is officially undocumented but note the suoption parameter and review the Space Usage option constants above.

Overload 3 dbms_space.space_usage( segment_owner IN VARCHAR2, segment_name IN VARCHAR2, segment_type IN VARCHAR2, suoption IN NUMBER, -- see Constants: above segment_size_blocks OUT NUMBER, segment_size_bytes OUT NUMBER, used_blocks OUT NUMBER, used_bytes OUT NUMBER, expired_blocks OUT NUMBER, expired_bytes OUT NUMBER, unexpired_blocks OUT NUMBER, unexpired_bytes OUT NUMBER, partition_name IN VARCHAR2 DEFAULT NULL);

set serveroutput on

      

      DECLARE

       sbl  NUMBER;

       sby  NUMBER;

       ubl  NUMBER;

       uby  NUMBER;

       ebl  NUMBER;

       eby  NUMBER;

       xbl  NUMBER;

       xby  NUMBER;

      BEGIN

        dbms_space.space_usage('UWCLASS','SERVERS', 'TABLE', dbms_space.spaceusage_exact, sbl, sby, ubl, uby, ebl, eby, xbl, xby);

      

        dbms_output.put_line('segment size blocks: ' || TO_CHAR(sbl));

        dbms_output.put_line('segment size bytes:  ' || TO_CHAR(sby));

        dbms_output.put_line('used blocks:         ' || TO_CHAR(ubl));

        dbms_output.put_line('used bytes:          ' || TO_CHAR(uby));

        dbms_output.put_line('expired blocks:      ' || TO_CHAR(ebl));

        dbms_output.put_line('expired bytes:       ' || TO_CHAR(eby));

        dbms_output.put_line('unexpired blocks:    ' || TO_CHAR(xbl));

        dbms_output.put_line('unexpired bytes:     ' || TO_CHAR(xby));

      END;

      /

      DECLARE

      *

      ERROR at line 1:

      ORA-03213: Invalid Lob Segment Name for DBMS_SPACE package

      ORA-06512: at "SYS.DBMS_SPACE", line 274

      ORA-06512: at line 11

If you do not wish to use a long list of dbms_output statements try this technique I saw posted by Hazem Ameen in his "Oracle DBA in KSA" blog. I have made minor changes to it bu the essence is Hazem's. CREATE OR REPLACE TYPE space_usage_t AUTHID DEFINER AS OBJECT ( segment_owner VARCHAR2(30), segment_name VARCHAR2(30), segment_type VARCHAR2(30), partition_name VARCHAR2(30), blocks_unformatted NUMBER, bytes_unformatted NUMBER, blocks_0_25 NUMBER, bytes_0_25 NUMBER, blocks_25_50 NUMBER, bytes_25_50 NUMBER, blocks_50_75 NUMBER, bytes_50_75 NUMBER, blocks_75_100 NUMBER, bytes_75_100 NUMBER, blocks_full NUMBER, bytes_full NUMBER); / CREATE TYPE space_usage_tt AS TABLE OF space_usage_t; / -- pipelined function CREATE OR REPLACE FUNCTION space_usage ( in_segment_owner IN dba_segments.owner%TYPE , in_segment_name IN dba_segments.segment_name%TYPE, in_segment_type IN dba_segments.segment_type%TYPE, in_partition_name IN dba_tab_partitions.partition_name%TYPE := NULL) RETURN space_usage_tt PIPELINED AUTHID DEFINER AS unformatted_blocks NUMBER; unformatted_bytes NUMBER; fs1_blocks NUMBER; fs1_bytes NUMBER; fs2_blocks NUMBER; fs2_bytes NUMBER; fs3_blocks NUMBER; fs3_bytes NUMBER; fs4_blocks NUMBER; fs4_bytes NUMBER; full_blocks NUMBER; full_bytes NUMBER; BEGIN dbms_space.space_usage(segment_owner => in_segment_owner, segment_name => in_segment_name, segment_type => in_segment_type, unformatted_blocks => unformatted_blocks, unformatted_bytes => unformatted_bytes, fs1_blocks => fs1_blocks, fs1_bytes => fs1_bytes, fs2_blocks => fs2_blocks, fs2_bytes => fs2_bytes, fs3_blocks => fs3_blocks, fs3_bytes => fs3_bytes, fs4_blocks => fs4_blocks, fs4_bytes => fs4_bytes, full_blocks => full_blocks, full_bytes => full_bytes, partition_name => in_partition_name); PIPE ROW(space_usage_t(in_segment_owner, in_segment_name, in_segment_type, in_partition_name, unformatted_blocks, unformatted_bytes, fs1_blocks, fs1_bytes, fs2_blocks, fs2_bytes, fs3_blocks, fs3_bytes, fs4_blocks, fs4_bytes, full_blocks, full_bytes)); RETURN; END space_usage; / SELECT * FROM TABLE(space_usage('UWCLASS', 'SERVERS', 'TABLE'));

UNUSED_SPACE

Returns information about unused space in a table, index, or cluster dbms_space.unused_space ( segment_owner IN VARCHAR2, segment_name IN VARCHAR2, segment_type IN VARCHAR2, total_blocks OUT NUMBER, total_bytes OUT NUMBER, unused_blocks OUT NUMBER, unused_bytes OUT NUMBER, last_used_extent_file_id OUT NUMBER, last_used_extent_block_id OUT NUMBER, last_used_block OUT NUMBER, partition_name IN VARCHAR2 DEFAULT NULL);

set serveroutput on

      

      DECLARE

       segown   VARCHAR2(30) := 'UWCLASS';

       segname  VARCHAR2(30) := 'AIRPLANES';

       segtype  VARCHAR2(30) := 'TABLE';

       partname VARCHAR2(30);

      

       totblock NUMBER;

       totbytes NUMBER;

       unusedbl NUMBER;

       unusedby NUMBER;

       lu_ef_id NUMBER;

       lu_eb_id NUMBER;

       lu_block NUMBER;
      

      BEGIN

        dbms_space.unused_space(segown, segname, segtype, totblock,

        totbytes, unusedbl, unusedby, lu_ef_id, lu_eb_id,

        lu_block, partname);

      

        dbms_output.put_line('Total Blocks: ' || TO_CHAR(totblock));

        dbms_output.put_line('Total Bytes: ' || TO_CHAR(totbytes));

        dbms_output.put_line('Unused Blocks: ' || TO_CHAR(unusedbl));

        dbms_output.put_line('Unused Bytess: ' || TO_CHAR(unusedby));

        dbms_output.put_line('Last Used Extent File ID: ' || TO_CHAR(lu_ef_id));

        dbms_output.put_line('Last Used Extent Block ID: ' || TO_CHAR(lu_eb_id));

        dbms_output.put_line('Last Used Block: ' || TO_CHAR(lu_block));

      END;

      /

VERIFY_SHRINK_CANDIDATE

Determines whether a segment can be shrunk to the "shrink_target_bytes" value dbms_space.verify_shrink_candidate( segment_owner IN VARCHAR2, segment_name IN VARCHAR2, segment_type IN VARCHAR2, shrink_target_bytes IN NUMBER, partition_name IN VARCHAR2 DEFAULT NULL) RETURN BOOLEAN;

CREATE TABLE t AS

      SELECT *

      FROM dba_objects_ae;

      

      exec dbms_stats.gather_table_stats('UWCLASS', 'T');

      

      col segment_name format a30

      

      SELECT segment_name, bytes

      FROM user_segments;

      

      delete from t;

      commit;

      

      exec dbms_stats.gather_table_stats('UWCLASS', 'T');

      

      SELECT segment_name, bytes

      FROM user_segments;

      

      set serveroutput on

      

      BEGIN

        IF (dbms_space.verify_shrink_candidate('UWCLASS', 'T', 'TABLE', 262144)) THEN

          dbms_output.put_line('Shinkable');

        ELSE

          dbms_output.put_line('Not Shinkable');

        END IF;

      END;

      /

VERIFY_SHRINK_CANDIDATE_TBF

Pipelined Table Function: Returns 1 if shrinkable, 0 if not shrinkable dbms_space.verify_shrink_candidate_tbf( segment_owner IN VARCHAR2, segment_name IN VARCHAR2, segment_type IN VARCHAR2, shrink_target_bytes IN NUMBER, partition_name IN VARCHAR2 DEFAULT NULL) RETURN verify_shrink_table PIPELINED;

SELECT *

      FROM TABLE(dbms_space.verify_shrink_candidate_tbf('UWCLASS', 'T', 'TABLE', 262144));

      

      SELECT *

      FROM TABLE(dbms_space.verify_shrink_candidate_tbf('UWCLASS', 'SERVERS', 'TABLE', 262144));