Oracle DBMS_SPACE
Version 12.1.0.2

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.1 to 12.1.0.2. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.

Interested in Oracle GoldenGate? Check out the IGGOUG,
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;
/
Dependencies
CREATE_TABLE_COST_COLINFO DBA_TABLES TABLESPACE_LIST
CREATE_TABLE_COST_COLUMNS DBA_TABLESPACE_USAGE_METRICS TS$
DBA_ADVISOR_ACTIONS DBA_TAB_PARTITIONS USER$
DBA_ADVISOR_OBJECTS DBA_TS_QUOTAS USER_INDEXES
DBA_ADVISOR_OBJECT_TYPES DBMS_ADVISOR V$ASM_DISKGROUP_STAT
DBA_ADVISOR_RECOMMENDATIONS DBMS_ASSERT V$DATAFILE
DBA_ADVISOR_TASKS DBMS_DBFS_SFS_ADMIN V$PARAMETER
DBA_DATA_FILES DBMS_HEAT_MAP WRH$_SEG_STAT
DBA_FREE_SPACE DBMS_LOCK WRI$_ADV_ACTIONS
DBA_HIST_ACTIVE_SESS_HISTORY DBMS_OUTPUT WRI$_ADV_FINDINGS
DBA_INDEXES DBMS_STANDARD WRI$_ADV_MESSAGE_GROUPS
DBA_IND_PARTITIONS DBMS_STATS_INTERNAL WRI$_ADV_OBJECTS
DBA_LOBS DUAL WRI$_ADV_OBJSPACE_TREND_T
DBA_LOB_PARTITIONS NLS_SESSION_PARAMETERS WRI$_ADV_RECOMMENDATIONS
DBA_LOB_SUBPARTITIONS OBJ$ WRI$_ALERT_OUTSTANDING
DBA_NESTED_TABLES PLITBLM WRI$_SEGADV_CNTRLTAB
DBA_OBJECTS PRVT_COMPRESSION WRI$_SEGADV_OBJLIST
DBA_OUTSTANDING_ALERTS RECYCLEBIN$ X$KCFISTSA
DBA_SCHEDULER_GLOBAL_ATTRIBUTE SEG$ X$KSPPCV
DBA_SCHEDULER_RUNNING_JOBS SYS_DBA_SEGS X$KSPPI
DBA_SCHEDULER_WINDOWS SYS_UNCOMPRESSED_SEGS  
Documented Yes
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
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;
/
 
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.

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;
Overload 2 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
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));

Related Topics
DBMS_RESUMABLE
DBMS_SPACE_ADMIN
Packages
Tablespaces

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved