Object_dependent_segments
is a function which returns a list of all the segments that are
associated with the specified object. This function is useful for
walking the dependency tree of objects related to the object of
interest such that one can figure an object's real overall space
requirements.
|
Argument
|
Type
|
In / Out
|
Default Value
|
|
OBJOWNER
|
VARCHAR2
|
IN
|
|
|
OBJNAME
|
VARCHAR2
|
IN
|
|
|
PARTNAME
|
VARCHAR2
|
IN
|
|
|
OBJTYPE
|
NUMBER
|
IN
|
|
Table 6.122:
Object_dependent_segments Parameters
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)
);
type dependent_segments_table is table of
object_dependent_segment;
Object_growth_trend
is a powerful function that returns the space utilization of a
database object over a time period from the current time or from the
Automatic Workload Repository Facilities (AWRF). However, note that
this requires proper OEM licensing - namely, the Diagnostics Pack. It
returns a table of
dbms_space.growth_trend_table.
|
Argument
|
Type
|
In / Out
|
Default Value
|
|
OBJECT_OWNER
|
VARCHAR2
|
IN
|
|
|
OBJECT_NAME
|
VARCHAR2
|
IN
|
|
|
OBJECT_TYPE
|
VARCHAR2
|
IN
|
|
|
PARTITION_NAME
|
VARCHAR2
|
IN
|
|
|
START_TIME
|
TIMESTAMP
|
IN
|
|
|
END_TIME
|
TIMESTAMP
|
IN
|
|
|
INTERVAL
|
DSINTERVAL_UNCONSTRAINED
|
IN
|
|
|
SKIP_INTERPOLATED
|
VARCHAR2
|
IN
|
|
|
TIMEOUT_SECONDS
|
|
IN
|
|
|
SINGLE_DATAPOINT_FLAG
|
VARCHAR2
|
IN
|
|
Table 6.123:
Dbms_space.growth_trend_table
type object_growth_trend_row is record (
timepoint
timestamp,
space_usage
number,
space_alloc
number,
quality
varchar(20)
);
type object_growth_trend_table is table of
object_growth_trend_row;
Space_usage
is an overloaded procedure that reveals segment space usage under the
high watermark. The first form works for database objects, but only
for tablespaces using auto segment space management. The FS1 through
FS4 parameters stand for the ranges of blocks with free space falling
in the following ranges: FS1=0-25%, FS2=25-50%, FS3=50-75% and
FS4=75-100%.
|
Argument
|
Type
|
In / Out
|
Default Value
|
|
SEGMENT_OWNER
|
VARCHAR2
|
IN
|
|
|
SEGMENT_NAME
|
VARCHAR2
|
IN
|
|
|
SEGMENT_TYPE
|
VARCHAR2
|
IN
|
|
|
UNFORMATTED_BLOCKS
|
NUMBER
|
OUT
|
|
|
UNFORMATTED_BYTES
|
NUMBER
|
OUT
|
|
|
FS1_BLOCKS
|
NUMBER
|
OUT
|
|
|
FS1_BYTES
|
NUMBER
|
OUT
|
|
|
FS2_BLOCKS
|
NUMBER
|
OUT
|
|
|
FS2_BYTES
|
NUMBER
|
OUT
|
|
|
FS3_BLOcKS
|
NUMBER
|
OUT
|
|
|
FS3_BYTES
|
NUMBER
|
OUT
|
|
|
FS4_BLOCKS
|
NUMBER
|
OUT
|
|
|
FS4_BYTES
|
NUMBER
|
OUT
|
|
|
FULL_BLOCKS
|
NUMBER
|
OUT
|
|
|
FULL_BYTES
|
NUMBER
|
OUT
|
|
|
PARTITION_NAME
|
VARCHAR2
|
IN
|
NULL
|
Table 6.124:
Space_usage Parameters
The second form of the
space_usage
procedure returns information about SECUREFILE LOB space usage, i.e.
the Oracle 11g preferred way to store large objects basically not in
the database - essentially replaces LOBs and BFILEs. The expired and
unexpired values are used by the LOB to keep version data.
|
Argument
|
Type
|
In / Out
|
Default Value
|
|
SEGMENT_OWNER
|
VARCHAR2
|
IN
|
|
|
SEGMENT_NAME
|
VARCHAR2
|
IN
|
|
|
SEGMENT_TYPE
|
VARCHAR2
|
IN
|
|
|
PARTITION_NAME
|
VARCHAR2
|
IN
|
NULL
|
|
SEGMENT_SIZE_BLOCKS
|
NUMBER
|
OUT
|
|
|
SEGMENT_SIZE_BYTES
|
NUMBER
|
OUT
|
|
|
USED_BLOCKS
|
NUMBER
|
OUT
|
|
|
USED_BYTES
|
NUMBER
|
OUT
|
|
|
EXPIRED_BLOCKS
|
NUMBER
|
OUT
|
|
|
EXPIRED_BYTES
|
NUMBER
|
OUT
|
|
|
UNEXPIRED_BLOCKS
|
NUMBER
|
OUT
|
|
|
UNEXPIRED_BYTES
|
NUMBER
|
OUT
|
|
Table 6.125:
Space_usage Parameters, 2nd form
Unused_space
is a procedure that reveals information about unused space by a
database object.
|
Argument
|
Type
|
In / Out
|
Default Value
|
|
SEGMENT_OWNER
|
VARCHAR2
|
IN
|
|
|
SEGMENT_NAME
|
VARCHAR2
|
IN
|
|
|
SEGMENT_TYPE
|
VARCHAR2
|
IN
|
|
|
TOTAL_BLOCKS
|
NUMBER
|
OUT
|
|
|
TOTAL_BYTES
|
NUMBER
|
OUT
|
|
|
UNUSED_BLOCKS
|
NUMBER
|
OUT
|
|
|
UNUSED_BYTES
|
NUMBER
|
OUT
|
|
|
LAST_USED_EXTENT_FILE_ID
|
NUMBER
|
OUT
|
|
|
LAST_USED_EXTENT_BLOCK_ID
|
NUMBER
|
OUT
|
|
|
LAST_USED_BLOCK
|
NUMBER
|
OUT
|
|
|
PARTITION_NAME
|
VARCHAR2
|
IN
|
NULL
|
Table 6.126:
Unused_space Parameters
and include the URL for the page.
