| General |
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. |
| 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 |
| SPACEUSAGE_EXACT (Computes space usage exhaustively) |
POSITIVE |
16 |
| SPACEUSAGE_FAST (Retrieves values from in-memory statistics) |
POSITIVE |
17 |
|
| Defined 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 TABLE OF asa_reco_row;
-- a table of dependent_segment records
TYPE dependent_segments_table IS TABLE OF object_dependent_segment;
-- 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));
-- 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));
/* 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 object_growth_trend_table IS TABLE OF object_growth_trend_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 |
DBMS_LOCK |
| CREATE_TABLE_COST_COLUMNS |
DBMS_OUTPUT |
| DBA_ADVISOR_ACTIONS |
DBMS_POSIX_ADMIN |
| DBA_ADVISOR_OBJECTS |
DUAL |
| DBA_ADVISOR_OBJECT_TYPES |
EMD_MAINTENANCE |
| DBA_ADVISOR_RECOMMENDATIONS |
OBJ$ |
| DBA_ADVISOR_TASKS |
PLITBLM |
| DBA_INDEXES |
RECYCLEBIN$ |
| DBA_IND_PARTITIONS |
SYS_DBA_SEGS |
| DBA_LOBS |
SYS_PLSQL_7779_875_1 |
| DBA_LOB_PARTITIONS |
SYS_PLSQL_7779_923_1 |
| DBA_LOB_SUBPARTITIONS |
TS$ |
| DBA_NESTED_TABLES |
WRH$_SEG_STAT |
| DBA_OBJECTS_AE |
WRI$_ADV_ACTIONS |
| DBA_OUTSTANDING_ALERTS |
WRI$_ADV_FINDINGS |
| DBA_SCHEDULER_GLOBAL_ATTRIBUTE |
WRI$_ADV_MESSAGE_GROUPS |
| DBA_SCHEDULER_RUNNING_JOBS |
WRI$_ADV_OBJECTS |
| DBA_SCHEDULER_WINDOWS |
WRI$_ADV_OBJSPACE_TREND_T |
| DBA_TABLES |
WRI$_ADV_RECOMMENDATIONS |
| DBA_TAB_PARTITIONS |
WRI$_ALERT_OUTSTANDING |
| DBMS_ADVISOR |
WRI$_SEGADV_CNTRLTAB |
| DBMS_ASSERT |
WRI$_SEGADV_OBJLIST |
|
| Security Model |
Execute is granted to PUBLIC |
| Subprograms |
| ASA_RECOMMENDATIONS |
OBJECT_DEPENDENT_SEGMENTS |
OBJECT_SPACE_USAGE |
| AUTO_SPACE_ADVISOR_JOB_PROC |
OBJECT_GROWTH_TREND |
OBJECT_SPACE_USAGE_TBF |
| CREATE_INDEX_COST |
OBJECT_GROWTH_TREND_CUR |
PARSE_SPACE_ADV_INFO |
| CREATE_TABLE_COST |
OBJECT_GROWTH_TREND_CURTAB |
SPACE_USAGE |
| DBFS_DF |
OBJECT_GROWTH_TREND_I_TO_S |
UNUSED_SPACE |
| FREE_BLOCKS |
OBJECT_GROWTH_TREND_SWRF |
VERIFY_SHRINK_CANDIDATE |
| ISDATAFILEDROPPABLE_NAME |
OBJECT_GROWTH_TREND_S_TO_I |
VERIFY_SHRINK_CANDIDATE_TBF |
|
| |
| 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;
/ |
| |
| 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 |
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 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;
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'))
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)); |
|
