178 DBMS_SPACE
The DBMS_SPACE
package enables you to analyze segment growth and space requirements.
This chapter contains the following topics:
178.1 DBMS_SPACE Security Model
This package runs with SYS
privileges. The execution privilege is granted to PUBLIC
. Subprograms in this package run under the caller security. The user must have ANALYZE
privilege on the object.
178.2 DBMS_SPACE Data Structures
The DBMS_SPACE
package defines an OBJECT
type, a RECORD
type, and a TABLE
type.
OBJECT Types
RECORD Types
TABLE Types
178.2.1 DBMS_SPACE CREATE_TABLE_COST_COLINFO Object Type
This type describes the datatype and size of a column in the table.
Syntax
TYPE create_table_cost_colinfo IS OBJECT( col_type VARCHAR(200), col_size NUMBER)
Attributes
Table 178-1 CREATE_TABLE_COST_COLINFO Object Type
Attribute | Description |
---|---|
|
Column type |
|
Column size |
178.2.2 DBMS_SPACE ASA_RECO_ROW Record Type
This type contains the column type of individual columns returned by the ASA_RECOMMENDATIONS Function.
Syntax
TYPE asa_reco_row 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);
Attributes
Table 178-2 ASA_RECO_ROW Attributes
Field | Description |
---|---|
|
Name of the tablespace containing the object |
|
Name of the schema |
|
Name of the object |
|
Type of the segment ' |
|
Name of the partition |
|
Space allocated to the segment |
|
Space actually used by the segment |
|
Reclaimable free space in the segment |
|
Percentage of excess chain row pieces that can be eliminated |
|
Recommendation or finding for this segment |
|
Command associated with the recommendation |
|
Command associated with the recommendation |
|
Command associated with the recommendation |
|
Advisor Task that processed this segment |
|
Message ID corresponding to the recommendation |
Related Topics
178.3 Summary of DBMS_SPACE Subprograms
This table lists the DBMS_SPACE
subprograms and briefly describes them.
Table 178-3 DBMS_SPACE Package Subprograms
Subprogram | Description |
---|---|
Returns recommendations/findings of segment advisor run automatically by the system or manually invoked by the user |
|
Determines the cost of creating an index on an existing table |
|
Determines the size of the table given various attributes |
|
Returns information about free blocks in an object (table, index, or cluster) |
|
Checks whether a datafile is droppable |
|
Returns the list of segments that are associated with the object |
|
A table function where each row describes the space usage of the object at a specific point in time |
|
SHRINK_TABLESPACE Procedure | Analyzes a bigfile tablespace before resizing or resizes a bigfile tablespace and optionally returns information about the resize operation |
Returns information about free blocks in an auto segment space managed segment |
|
Returns information about unused space in an object (table, index, or cluster) |
178.3.1 DBMS_SPACE ASA_RECOMMENDATIONS Function
This function returns recommendations using the stored results of the auto segment advisor. This function returns results from the latest run on any given object.
Syntax
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;
Parameters
Table 178-4 ASA_RECOMMENDATIONS Procedure Parameters
Parameter | Description |
---|---|
|
Returns the results of all the auto advisor runs or only the results of the
latest run. The valid values are If |
|
This parameter is used to indicate if the results of manual jobs should be included. If However, the The valid values are |
|
Shows only the findings instead of the recommendations. The valid values are
|
Table 178-5 Parameter Usage
all_runs | show_manual | show_ findings | Outcome |
---|---|---|---|
TRUE |
TRUE |
TRUE |
All findings from auto advisor and manual tasks. |
TRUE |
TRUE |
FALSE |
All recommendations from auto advisor and manual tasks. |
TRUE |
FALSE |
TRUE |
All findings from auto advisor tasks. |
TRUE |
FALSE |
FALSE |
All recommendations from all auto advisor tasks. |
FALSE |
TRUE |
TRUE |
N/A |
FALSE |
TRUE |
FALSE |
N/A |
FALSE |
FALSE |
TRUE |
Findings for the latest auto advisor task. |
FALSE |
FALSE |
FALSE |
Recommendations from the latest auto advisor task. |
178.3.2 CREATE_INDEX_COST Procedure
This procedure determines the cost of creating an index on an existing table. The input is the DDL statement that will be used to create the index. The procedure will output the storage required to create the index.
Syntax
DBMS_SPACE.CREATE_INDEX_COST ( ddl IN VARCHAR2, used_bytes OUT NUMBER, alloc_bytes OUT NUMBER, plan_table IN VARCHAR2 DEFAULT NULL);
Pragmas
pragma restrict_references(create_index_cost,WNDS);
Parameters
Table 178-6 CREATE_INDEX_COST Procedure Parameters
Parameter | Description |
---|---|
|
The create index DDL statement |
|
The number of bytes representing the actual index data |
|
Size of the index when created in the tablespace |
|
Which plan table to use, default |
Usage Notes
-
The table on which the index is created must already exist.
-
The computation of the index size depends on statistics gathered on the segment.
-
It is imperative that the table must have been analyzed recently.
-
In the absence of correct statistics, the results may be inaccurate, although the procedure will not raise any errors.
178.3.3 CREATE_TABLE_COST Procedures
This procedure is used in capacity planning to determine the size of the table given various attributes. The size of the object can vary widely based on the tablespace storage attributes, tablespace block size, and so on. There are two overloads of this procedure.
-
The first version takes the column information of the table as argument and outputs the table size.
-
The second version takes the average row size of the table as argument and outputs the table size.
This procedure can be used on tablespace of dictionary managed and locally managed extent management as well as manual and auto segment space management.
Syntax
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); 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); CREATE TYPE create_table_cost_colinfo IS OBJECT ( COL_TYPE VARCHAR(200), COL_SIZE NUMBER);
Parameters
Table 178-7 CREATE_TABLE_COST Procedure Parameters
Parameter | Description |
---|---|
|
The tablespace in which the object will be created. The default is |
|
The anticipated average row size in the table |
|
The description of the columns |
|
The anticipated number of rows in the table |
|
The percentage of free space in each block for future expansion of existing rows due to updates |
|
The space used by user data |
|
The size of the object taking into account the tablespace extent characteristics |
Usage Notes
-
The
used_bytes
represent the actual bytes used by the data. This includes the overhead due to the block metadata, pctfree etc. -
The
alloc_bytes
represent the size of the table when it is created in the tablespace. This takes into account, the size of the extents in the tablespace and tablespace extent management properties.
Examples
-- review the parameters SELECT argument_name, data_type, type_owner, type_name FROM all_arguments WHERE object_name = 'CREATE_TABLE_COST' AND overload = 2 -- examine the input parameter type SELECT text FROM dba_source WHERE name = 'CREATE_TABLE_COST_COLUMNS'; -- drill down further into the input parameter type 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('SYSTEM',cl,100000,0,ub,ab); DBMS_OUTPUT.PUT_LINE('Used Bytes: ' || TO_CHAR(ub)); DBMS_OUTPUT.PUT_LINE('Alloc Bytes: ' || TO_CHAR(ab)); END; /
178.3.4 FREE_BLOCKS Procedure
This procedure returns information about free blocks in an object (table, index, or cluster).
See SPACE_USAGE Procedures for returning free block information in an auto segment space managed segment.
Syntax
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);
Pragmas
pragma restrict_references(free_blocks,WNDS);
Parameters
Table 178-8 FREE_BLOCKS Procedure Parameters
Parameter | Description |
---|---|
|
Schema name of the segment to be analyzed |
|
Segment name of the segment to be analyzed |
|
Type of the segment to be analyzed (
|
|
Freelist group (instance) whose free list size is to be computed |
|
Returns count of free blocks for the specified group |
|
Maximum number of free list blocks to read (optional). Use a scan limit of X you are interested only in the question, "Do I have X blocks on the free list?" |
|
Partition name of the segment to be analyzed. This is only used for partitioned tables. The name of subpartition should be used when partitioning is composite. |
Examples
The following uses the CLUS
cluster in SCOTT
schema with 4 freelist groups. It returns the number of blocks in freelist group 3 in CLUS
.
DBMS_SPACE.FREE_BLOCKS('SCOTT', 'CLUS', 'CLUSTER', 3, :free_blocks);
Note:
An error is raised if scan_limit
is not a positive number.
178.3.5 ISDATAFILEDROPPABLE_NAME Procedure
This procedure checks whether a datafile is droppable. This procedure may be called before actually dropping the file.
Syntax
DBMS_SPACE.ISDATAFILEDROPPABLE_NAME ( filename IN VARCHAR2, value OUT NUMBER);
Pragmas
pragma restrict_references(free_blocks,WNDS);
Parameters
Table 178-9 ISDATAFILEDROPPABLE_NAME Procedure Parameters
Parameter | Description |
---|---|
|
Name of the file |
|
Values: |
Examples
DECLARE fname VARCHAR2(100); retval NUMBER;BEGIN SELECT file_name INTO fname FROM dba_data_files WHERE file_name like '%empty%';DBMS_SPACE.ISDATAFILEDROPPABLE_NAME(fname, retval);DBMS_OUTPUT.PUT_LINE(retval);END;/
178.3.6 OBJECT_DEPENDENT_SEGMENTS Function
This table function, given an object, returns the list of segments that are associated with the object.
Syntax
DBMS_SPACE.OBJECT_DEPENDENT_SEGMENTS( objowner IN VARCHAR2, objname IN VARCHAR2, partname IN VARCHAR2, objtype IN NUMBER) RETURN dependent_segments_table PIPELINED;
Parameters
Table 178-10 OBJECT_DEPENDENT_SEGMENTS Function Parameters
Parameter | Description |
---|---|
|
The schema containing the object |
|
The name of the object |
|
The name of the partition |
|
Type of the object:
|
Return Values
The content of one row of a 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));
Table 178-11 OBJECT_DEPENDENT_SEGMENT Type Parameters
Parameter | Description |
---|---|
|
The schema containing the segment |
|
The name of the segment |
|
The type of the segment, such as table, index or LOB |
|
The name of the tablespace |
|
The name of the partition, if any |
|
The name of the |
178.3.7 OBJECT_GROWTH_TREND Function
This is a table function. The output is one or more rows where each row describes the space usage of the object at a specific point in time.
Either the space usage totals will be retrieved from Automatic Workload Repository Facilities (AWRF), or the current space usage will be computed and combined with space usage deltas retrieved from AWRF.
Syntax
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;
Parameters
Table 178-12 OBJECT_GROWTH_TREND Function Parameters
Parameter | Description |
---|---|
|
The schema containing the object |
|
The name of the object |
|
The type of the object |
|
The name of the partition |
|
Statistics generated after this time will be used in generating the growth trend |
|
Statistics generated until this time will be used in generating the growth trend |
|
The interval at which to sample |
|
Whether interpolation of missing values should be skipped |
|
The time-out value for the function in seconds |
|
Whether in the absence of statistics the segment should be sampled |
Return Values
The
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 VARCHAR(20));
Table 178-13 OBJECT_GROWTH_TREND_ROW Type Parameters
Parameter | Description |
---|---|
|
The time at which the statistic was recorded |
|
The space used by data |
|
The size of the segment including overhead and unused space |
|
The quality of result: " |
TYPE object_growth_trend_table IS TABLE OF object_growth_trend_row;
178.3.8 SHRINK_TABLESPACE Procedure
This procedure can resize a bigfile tablespace or analyze a bigfile tablespace before resizing.
Syntax
DBMS_SPACE.SHRINK_TABLESPACE (
ts_name IN VARCHAR2,
shrink_mode IN NUMBER,
target_size IN NUMBER,
shrink_result OUT CLOB);
DBMS_SPACE.SHRINK_TABLESPACE (
ts_name IN VARCHAR2,
shrink_mode IN NUMBER,
target_size IN NUMBER);
Parameters
Table 178-14 SHRINK_TABLESPACE Procedure Parameters
Parameter | Description |
---|---|
|
The name of the tablespace to be analyzed or resized |
|
The shrink mode to execute. The values are:
TS_MODE_SHRINK which moves
objects online by default, except for index-organized tables.
TS_MODE_SHRINK_FORCE will move objects online by
default, but if the online move fails, it will attempt to move them
offline.
|
|
The desired tablespace size specified in bytes. The default value is
|
|
Output result of the procedure returned as a CLOB. The output results for
TS_MODE_SHRINK include:
The output results for
TS_MODE_ANALYZE include:
|
Errors
Table 178-15 SHRINK_TABLESPACE Procedure Errors
Error | Description |
---|---|
|
Message: Failed to acquire a lock (Type: "Type", Name: "Name", Description: "Description") because it is currently held by another session. The resource being locked can be identified by ID1_value ("ID1_description") and ID2_value ("ID2_description") Reasons:
|
Examples
set serveroutput on
execute dbms_space.shrink_tablespace('TBS_1', shrink_mode => DBMS_SPACE.TS_MODE_ANALYZE);
------------------------------ANALYZE RESULT--------------------------------------
1. { BG_TEST.SYS_IL0000081422C00004$$ | type: INDEX | blocks: 256 | tablespace_name: TBS_1 }
2. { BG_TEST.SYS_IL0000081422C00005$$ | type: INDEX | blocks: 512 | tablespace_name: TBS_1 }
3. { BG_TEST.T2 | type: TABLE | blocks: 512 | tablespace_name: TBS_1 }
4. { BG_TEST.T2_LOB1 | type: LOBSEGMENT | blocks: 45824 | tablespace_name: TBS_1}
5. { BG_TEST.T2_LOB2 | type: LOBSEGMENT | blocks: 41216 | tablespace_name: TBS_1}
Total Movable Objects: 5
Total Movable Size(GB): .67
Orginal Datafile Size(GB): 10
Suggested Target Size(GB): 2.09
Process Time: +00 00:00:03.94897
set serveroutput on
execute dbms_space.shrink_tablespace('TBS_1');
-------------------SHRINK RESULT-------------------
Total Moved Objects: 5
Total Moved Size(GB): 1.35
Orginal Datafile Size(GB): 10
New Datafile Size(GB): 1.81
Process Time: +00 00:00:50.94897
178.3.9 SPACE_USAGE Procedures
This procedure has two variations to show space usage.
The first form of the procedure shows the space usage of data blocks under the segment High Water Mark. You can calculate usage for LOB
s, LOB
PARTITIONS
and LOB
SUBPARTITIONS
. This procedure can only be used on tablespaces that are created with auto segment space management. The bitmap blocks, segment header, and extent map blocks are not accounted for by this procedure. Note that this overload cannot be used on SECUREFILE
LOB
s.
Note:
For LOB segments, the number of blocks that is returned from full_blocks
and unformatted_blocks
is actually the number of chunks for the LOB segment.
The second form of the procedure returns information about SECUREFILE
LOB
space usage. It will return the amount of space in blocks being used by all the SECUREFILE
LOB
s in the LOB
segment. The procedure displays the space actively used by the LOB column, freed space that has retention expired, and freed space that has retention unexpired. Note that this overload can be used only on SECUREFILE
LOB
s.
Syntax
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);
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);
Parameters
Table 178-16 SPACE_USAGE Procedure Parameters
Parameter | Description |
---|---|
|
Schema name of the segment to be analyzed |
|
Name of the segment to be analyzed |
|
Partition name of the segment to be analyzed |
|
Type of the segment to be analyzed (
|
|
For LOB segments, the number of blocks that is returned from unformatted_blocks is actually the number of chunks for the LOB segment. |
|
Total number of bytes unformatted |
|
Number of blocks having at least 0 to 25% free space |
|
Number of bytes having at least 0 to 25% free space |
|
Number of blocks having at least 25 to 50% free space |
|
Number of bytes having at least 25 to 50% free space |
|
Number of blocks having at least 50 to 75% free space |
|
Number of bytes having at least 50 to 75% free space |
|
Number of blocks having at least 75 to 100% free space |
|
Number of bytes having at least 75 to 100% free space |
|
The number of blocks that is returned from full_blocks is actually the number of chunks for the LOB segment |
|
Total number of bytes full in the segment |
|
Number of blocks allocated to the segment |
|
Number of bytes allocated to the segment |
|
Number blocks allocated to the LOB that contains active data |
|
Number bytes allocated to the LOB that contains active data |
|
Number of expired blocks used by the LOB to keep version data |
|
Number of expired bytes used by the LOB to keep version data |
|
Number of unexpired blocks used by the LOB to keep version data |
|
Number of unexpired bytes used by the LOB to keep version data |
|
Name of the partition ( |
Examples
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('U1','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;
178.3.10 UNUSED_SPACE Procedure
This procedure returns information about unused space in an object (table, index, or cluster).
Syntax
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);
Parameters
Table 178-17 UNUSED_SPACE Procedure Parameters
Parameter | Description |
---|---|
|
Schema name of the segment to be analyzed |
|
Segment name of the segment to be analyzed |
|
Type of the segment to be analyzed (
|
|
Returns total number of blocks in the segment |
|
Returns total number of blocks in the segment, in bytes |
|
Returns number of blocks which are not used |
|
Returns, in bytes, number of blocks which are not used |
|
Returns the file ID of the last extent which contains data |
|
Returns the starting block ID of the last extent which contains data |
|
Returns the last block within this extent which contains data |
|
Partition name of the segment to be analyzed. This is only used for partitioned tables; the name of subpartition should be used when partitioning is compose. |
Examples
The following declares the necessary bind variables and executes.
DBMS_SPACE.UNUSED_SPACE('SCOTT', 'EMP', 'TABLE', :total_blocks, :total_bytes,:unused_blocks, :unused_bytes, :lastextf, :last_extb, :lastusedblock);