Oracle DBMS_SPACE

hunterjoy發表於2012-08-27
Oracle

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

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/543979/viewspace-741993/，如需轉載，請註明出處，否則將追究法律責任。

相關文章