Oracle DBMS


	CODE Oracle Code Library


	JOBS Find Or Post Oracle Jobs


	FORUM Oracle Discussion & Chat

Search the Reference Library pages:

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle DBMS_SPACE

Version 11.1

General

Purpose

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

Source

{ORACLE_HOME}/rdbms/admin/dbmsspu.sql

First Available

7.3.4

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

Defined Data Types

CREATE TYPE create_table_cost_colinfo IS OBJECT (
col_type VARCHAR(200),
col_size NUMBER);
/

CREATE TYPE create_table_cost_columns IS VARRAY(50000) OF create_table_cost_colinfo;
/

Dependencies

CREATE_TABLE_COST_COLINFO	DBMS_ADVISOR
CREATE_TABLE_COST_COLUMNS	DBMS_ASSERT
DBA_ADVISOR_ACTIONS	DBMS_OUTPUT
DBA_ADVISOR_OBJECTS	DBMS_SPACE
DBA_ADVISOR_OBJECT_TYPES	EMD_MAINTENANCE
DBA_ADVISOR_RECOMMENDATIONS	OBJ$
DBA_ADVISOR_TASKS	PLITBLM
DBA_INDEXES	SYS_DBA_SEGS
DBA_IND_PARTITIONS	TS$
DBA_LOBS	WRH$_SEG_STAT
DBA_LOB_PARTITIONS	WRI$_ADV_ACTIONS
DBA_LOB_SUBPARTITIONS	WRI$_ADV_FINDINGS
DBA_NESTED_TABLES	WRI$_ADV_MESSAGE_GROUPS
DBA_OBJECTS	WRI$_ADV_OBJECTS
DBA_OUTSTANDING_ALERTS	WRI$_ADV_OBJSPACE_TREND_T
DBA_SCHEDULER_GLOBAL_ATTRIBUTE	WRI$_ADV_RECOMMENDATIONS
DBA_SCHEDULER_RUNNING_JOBS	WRI$_ALERT_OUTSTANDING
DBA_SCHEDULER_WINDOWS	WRI$_SEGADV_CNTRLTAB
DBA_TABLES	WRI$_SEGADV_OBJLIST
DBA_TAB_PARTITIONS

Object Privileges

GRANT execute on DBMS_SPACE TO <schema_name>

GRANT execute on DBMS_SPACE TO uwclass;

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;

Return Type:
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));

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;
/

FREE_BLOCKS

Information about free blocks in a table, index, or cluster

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

-- Note: This will only work on tablespaces w/o ASSM.
-- With ASSM use dbms_space.space_usage

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;

Return Type:
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));

set linesize 141
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;

Return Type:
TYPE object_growth_trend_row IS RECORD (
timepoint   TIMESTAMP,
space_usage NUMBER,
space_alloc NUMBER,
quality     VARCHAR(20));

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'));

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

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,
fs1_bytes          OUT NUMBER,
fs2_blocks         OUT NUMBER,
fs2_bytes          OUT NUMBER,
fs3_blocks         OUT NUMBER,
fs3_bytes          OUT NUMBER,
fs4_blocks         OUT NUMBER,
fs4_bytes          OUT NUMBER,
full_blocks        OUT NUMBER,
full_bytes         OUT NUMBER,
partition_name     IN VARCHAR2 DEFAULT NULL);

variable unf   NUMBER;
variable unfb NUMBER;
variable fs1   NUMBER;
variable fs1b NUMBER;
variable fs2   NUMBER;
variable fs2b NUMBER;
variable fs3   NUMBER;
variable fs3b NUMBER;
variable fs4   NUMBER;
variable fs4b NUMBER;
variable full NUMBER;
variable fullb NUMBER;

BEGIN
dbms_space.space_usage('UWCLASS','T', 'TABLE', :unf, :unfb, :fs1, :fs1b, :fs2, :fs2b, :fs3, :fs3b, :fs4, :fs4b, :full, :fullb);
END;
/
print unf ;
print unfb ;
print fs4 ;
print fs4b;
print fs3 ;
print fs3b;
print fs2 ;
print fs2b;
print fs1 ;
print fs1b;
print full;
print fullb;

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;

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

Home : Code Library : Sponsors : Privacy : Terms of Use : Contact Us