CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 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 [89 users online]    © 2010 psoug.org