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