create or replace
package dbms_space_admin is
————
— OVERVIEW
—
— This package provides tablespace/segment space administration
— not available through the standard sql.
— SECURITY
—
— run with SYS privileges. thus any user who has privilege to execute the
— the package gets to manipulate the bitmapes.
— CONSTANTS to be used as OPTIONS for various procedures
— refer comments with procedure(s) for more detail
SEGMENT_VERIFY_EXTENTS constant positive := 1;
— used to verify that the space owned by segment is appropriately reflected
— in the bitmap as used
SEGMENT_VERIFY_EXTENTS_GLOBAL constant positive := 2;
— used to verify that the space owned bu segment is appropriately reflected
— in the bitmap as used and that no other segment claims any of this space
— to be used by it
SEGMENT_MARK_CORRUPT constant positive := 3;
— used to mark a temp segment as corrupt whereby facilitating its
— elimination from the dictionary (without space reclaim)
SEGMENT_MARK_VALID constant positive := 4;
— used to mark a corrupt temp segment as valid. Useful when the corruption
— in the segment extent map or elsewhere has been resolved and the segment
— can be dropped normally
SEGMENT_DUMP_EXTENT_MAP constant positive := 5;
— dump the extent map for a given segment
TABLESPACE_VERIFY_BITMAP constant positive := 6;
— verifies the bitmap of the tablespace with extent maps of the segments
— in that tablespace to make sure everything is consistent
TABLESPACE_EXTENT_MAKE_FREE constant positive := 7;
— makes this range (extent) of space free in the bitmaps
TABLESPACE_EXTENT_MAKE_USED constant positive := 8;
— makes this range (extent) of space used in the bitmaps
SEGMENT_VERIFY_BASIC constant positive := 9;
SEGMENT_VERIFY_DEEP constant positive := 10;
SEGMENT_VERIFY_SPECIFIC constant positive := 11;
HWM_CHECK constant positive := 12;
BMB_CHECK constant positive := 13;
SEG_DICT_CHECK constant positive := 14;
EXTENT_TS_BITMAP_CHECK constant positive := 15;
DB_BACKPOINTER_CHECK constant positive := 16;
EXTENT_SEGMENT_BITMAP_CHECK constant positive := 17;
BITMAPS_CHECK constant positive := 18;
TS_VERIFY_BITMAPS constant positive := 19;
TS_VERIFY_DEEP constant positive := 20;
TS_VERIFY_SEGMENTS constant positive := 21;
SEGMENT_DUMP_BITMAP_SUMMARY constant positive := 27;
NGLOB_HBB_CHECK constant positive := 12;
NGLOB_FSB_CHECK constant positive := 13;
NGLOB_PUA_CHECK constant positive := 14;
NGLOB_CFS_CHECK constant positive := 15;
—————————-
— PROCEDURES AND FUNCTIONS
procedure segment_verify(
tablespace_name in varchar2 ,
header_relative_file in positive ,
header_block in positive ,
verify_option in positive DEFAULT SEGMENT_VERIFY_EXTENTS
);
—
— Verifies the consistency of the extent map of the segment
— Input arguments:
— tablespace_name – name of tablespace in which segment resides
— header_relative_file – relative file number of segment segment header
— header_block – block number of segment segment header
— verify_option – SEGMENT_VERIFY_EXTENTS or
— SEGMENT_VERIFY_EXTENTS_GLOBAL
—
procedure segment_corrupt(
tablespace_name in varchar2 ,
header_relative_file in positive ,
header_block in positive ,
corrupt_option in positive DEFAULT SEGMENT_MARK_CORRUPT
);
—
— Marks the segment corrupt/valid so that appropriate error recovery
— can be done/skipped. Only for segments already temporary.
— Input arguments:
— tablespace_name – name of tablespace in which segment resides
— header_relative_file – relative file number of segment segment header
— header_block – block number of segment segment header
— corrupt_option – SEGMENT_MARK_CORRUPT or
— SEGMENT_MARK_VALID
—
procedure segment_drop_corrupt(
tablespace_name in varchar2 ,
header_relative_file in positive ,
header_block in positive
);
—
— Drops a segment currently marked corrupt (without reclaiming space)
— Input arguments:
— tablespace_name – name of tablespace in which segment resides
— header_relative_file – relative file number of segment segment header
— header_block – block number of segment segment header
—
procedure segment_dump(
tablespace_name in varchar2 ,
header_relative_file in positive ,
header_block in positive ,
dump_option in positive DEFAULT SEGMENT_DUMP_EXTENT_MAP
);
—
— Dumps the extent map of a given segment
— Input arguments:
— tablespace_name – name of tablespace in which segment resides
— header_relative_file – relative file number of segment segment header
— header_block – block number of segment segment header
— dump_option – SEGMENT_DUMP_EXTENT_MAP
—
procedure tablespace_verify(
tablespace_name in varchar2 ,
verify_option in positive DEFAULT TABLESPACE_VERIFY_BITMAP
);
—
— Verifies that the extent maps and the bitmaps are in sync.
— Input arguments:
— tablespace_name – name of tablespace
— verify_option – TABLESPACE_VERIFY_BITMAP
—
procedure tablespace_fix_bitmaps(
tablespace_name in varchar2 ,
dbarange_relative_file in positive ,
dbarange_begin_block in positive ,
dbarange_end_block in positive ,
fix_option in positive
);
—
— Marks the appropriate dba range (extent) as free/used in bitmap
— Input arguments:
— tablespace_name – name of tablespace
— dbarange_relative_file – relative fileno of dba range (extent)
— dbarange_begin_block – block number of beginning of extent
— dbarange_end_block – block number (inclusive) of end of extent
— fix_option – TABLESPACE_EXTENT_MAKE_FREE or
— TABLESPACE_EXTENT_MAKE_USED
—
procedure tablespace_rebuild_bitmaps(
tablespace_name in varchar2 ,
bitmap_relative_file in positive DEFAULT NULL,
bitmap_block in positive DEFAULT NULL
);
—
— Rebuilds the appropriate bitmap(s). If no bitmap block dba specified then
— rebuilds all bitmaps for the given tablespace
— Input arguments:
— tablespace_name – name of tablespace
— bitmap_relative_file – relative file number of bitmap block to rebuild
— bitmap_block – block number of bitmap block to rebuild
—
—
procedure tablespace_rebuild_quotas(
tablespace_name in varchar2
);
—
— Rebuilds quotas for given tablespace
— Input arguments:
— tablespace_name – name of tablespace
—
—
procedure tablespace_migrate_from_local(
tablespace_name in varchar2
);
—
— Migrates a locally-managed tablespace to dictionary managed
— Input arguments:
— tablespace_name – name of tablespace
—
procedure tablespace_migrate_to_local(
tablespace_name in varchar2,
unit_size in positive DEFAULT NULL,
rfno in positive DEFAULT NULL
);
—
— Migrates a dictionary-managed tablespace to locally managed
— Input arguments:
— tablespace_name – name of tablespace
— unit_size – bitmap unit size for the tablespace
—
—
procedure tablespace_relocate_bitmaps(
tablespace_name in varchar2,
filno in positive,
blkno in positive);
—
— Tablespace relocate bitmaps to a different location
— Input arguments:
— tablespace_name – name of tablespace
—
—
procedure tablespace_fix_segment_states(
tablespace_name in varchar2);
—
— Tablespace fix segment states. During migration of tablespaces
— the segments are put in a transition state. If migration fails,
— the state of the segments can be corrected by calling this
— procedure. This procedure should be called if migration failed
— and the user tries to run in an incompatible mode.
—
procedure tablespace_fix_segment_extblks(
tablespace_name in varchar2);
—
— Tablespace fix segment extents and blocks based on segment
— header entries
—
procedure tablespace_dump_bitmaps(
tablespace_name in varchar2);
—
— Tablespace space header of files
function segment_number_extents(
header_tablespace_id in natural ,
header_relative_file in positive ,
header_block in positive ,
segment_type in positive ,
buffer_pool_id in natural ,
dictionary_flags in natural ,
data_object_id in number,
dictionary_extents in number
) return pls_integer;
pragma RESTRICT_REFERENCES(segment_number_extents,WNDS,WNPS,RNPS);
—
— Returns the number of extents which belong to the segment. Will return
— NULL if segment has disappeared. IS NOT to be used for any other
— purposes but by the views which need it and are sure that there info
— is correct. Else internal errors will abound
—
function segment_number_blocks(
header_tablespace_id in natural ,
header_relative_file in positive ,
header_block in positive ,
segment_type in positive ,
buffer_pool_id in natural ,
dictionary_flags in natural ,
data_object_id in number,
dictionary_blocks in number
) return pls_integer;
pragma RESTRICT_REFERENCES(segment_number_blocks,WNDS,WNPS,RNPS);
—
— Returns the number of blocks which belong to the segment. Will return
— NULL if segment has disappeared. IS NOT to be used for any other
— purposes but by the views which need it and are sure that there info
— is correct. Else internal errors will abound
—
procedure segment_moveblocks(
segment_owner in varchar2,
segment_name in varchar2,
partition_name in varchar2,
segment_type in varchar2,
group_id in natural,
minimum_size in positive,
move_count in positive,
pause_time in natural,
iterations in positive
);
—
— Moves blocks from the segment header to the process freelist
— It only moves blocks if the list is shorter than the minimum
— size for the move_count blocks
— segment_owner – name of the object’s owner
— segment_name – name of the object
— partition_name – name of the partition (NULL if not partitioned)
— segment_type – object type (TABLE, INDEX, etc – see DBMS_SPACE)
— group_id – freelist group (0 for the segment header)
— minimum_size – do not move if process free list is longer
— move_count – move up to this number of blocks
— pause_time – pause between loop iterations
— iterations – number of iterations (infinite if NULL)
—
procedure assm_segment_verify(
segment_owner in varchar2,
segment_name in varchar2,
segment_type in varchar2,
partition_name in varchar2,
verify_option in positive default SEGMENT_VERIFY_BASIC ,
attrib in positive default NULL);
—
— Verifies the consistency of the segment
— Input arguments:
— segment_owner – owner
— segment_name – name of the segment
— segment_type – type of segment
— partition_name – name of partition default NULL
— verify_option – one of SEGMENT_VERIFY_BASIC, DEEP , SPECIFIC
— attrib – used when option SEGMENT_VERIFY_SPECIFIC
procedure nglob_segment_verify(
segment_owner in varchar2,
segment_name in varchar2,
segment_type in varchar2,
partition_name in varchar2,
verify_option in positive default SEGMENT_VERIFY_BASIC ,
attrib in positive default NULL);
—
— Verifies the consistency of the segment
— Input arguments:
— segment_owner – owner
— segment_name – name of the segment
— segment_type – type of segment
— partition_name – name of partition default NULL
— verify_option – one of SEGMENT_VERIFY_BASIC, DEEP , SPECIFIC
— attrib – used when option SEGMENT_VERIFY_SPECIFIC
procedure assm_tablespace_verify(
tablespace_name in varchar2,
ts_option in positive,
segment_option in positive default NULL);
—
— Verifies that the tablespace consistency.
— Input arguments:
— tablespace_name – name of tablespace
— ts_option – TS_VERIFY_BITMAPS, TS_VERIFY_DEEP, TS_VERIFY_SEGMENTS
— segment option – used when TS_VERIFY_SEGMENTS, one of SEGMENT_VERIFY_DEEP, SPECIFIC
—
function assm_segment_synchwm(
segment_owner in varchar2,
segment_name in varchar2,
segment_type in varchar2,
partition_name in varchar2 default NULL,
check_only in number default 1
) return pls_integer;
—
— Synchronize HWMs of the ASSM segment
— Input arguments:
— segment_owner – owner
— segment_name – name of the segment
— segment_type – type of segment
— partition_name – name of partition default NULL
— check_only – whether it is check only default YES
— Output:
— Return TRUE if the segment requires HWM synchronization
— Return FALSE otherwise
procedure flush_lobsegment_stats;
procedure purge_lobsegment_stats;
————————————————————————-
— PROCEDURE securefile_segment_repair
————————————————————————-
— segment repair option: repair metadata blocks
SEGMENT_REPAIR_METADATA constant positive := 1;
procedure segment_repair(
segment_owner in varchar2,
segment_name in varchar2,
segment_type in varchar2,
partition_name in varchar2 default NULL,
repair_option in number default SEGMENT_REPAIR_METADATA
);
—
— Description:
— Repair SecureFile segment
— Input parameters:
— segment_owner – owner
— segment_name – name of the LOB segment
— segment_type – type of segment
— values: ‘LOB’, ‘LOB PARTITION’, ‘LOB SUBPARTITION’
— partition_name – name of the LON partition segment
— default: NULL
— repair_option – segment repair option,
— values: see SEGMENT_REPAIR_*** definitions
— default: SEGMENT_REPAIR_METADATA
— Note:
— This function is only used internally and does not require
— documentation.
————————————————————————-
— PROCEDURE segment_extend
————————————————————————-
procedure segment_extend(
segment_owner in varchar2,
segment_name in varchar2,
segment_type in varchar2,
partition_name in varchar2 default NULL,
target_size in number default 1
);
—
— Description:
— SecureFile segment extend in background
— Input parameters:
— segment_owner – owner
— segment_name – name of the LOB segment
— segment_type – type of segment
— values: ‘LOB’, ‘LOB PARTITION’, ‘LOB SUBPARTITION’
— partition_name – name of the LOB partition segment
— default: NULL
— target_size – segment target size in GB
— Note:
— This function is only used internally and does not require
— documentation.
————————————————————————-
— PROCEDURE drop_empty_segments
————————————————————————-
procedure drop_empty_segments(
schema_name in varchar2 default NULL,
table_name in varchar2 default NULL,
partition_name in varchar2 default NULL
);
—
— Description:
— Drop segments from empty table(s)/table fragments and dependent
— objects.
— Input parameters:
— schema_name – schema name, default: NULL
— table_name – table name, default: NULL
— partition_name – partition name, default: NULL
— Note:
— Given a schema name, this procedure scans all tables in the schema
— For each table, if the table or any of its fragments are found to be
— empty, and the table satisfies certain criteria [restrictions being
— the same as those imposed by segment creation on demand], the empty
— fragments and associated index segments are dropped. A subsequent
— insert will create segments with the same properties.
— Optionally,
— a. no schema name may be specified in which case we would scan
— tables belonging to all schemas
— b. both schema_name and table_name may be specified to do this
— operation on one particular table
— c. all three arguments may be supplied, in which case we will
— restrict this operation to that partition and its dependent
— objects.
————————————————————————-
— PROCEDURE materialize_deferred_segments
————————————————————————-
procedure materialize_deferred_segments(
schema_name in varchar2 default NULL,
table_name in varchar2 default NULL,
partition_name in varchar2 default NULL
);
—
— Description:
— Materialize segments for tables/table fragments with deferred
— segment creation (and their dependent objects)
— Input parameters:
— schema_name – schema name, default: NULL
— table_name – table name, default: NULL
— partition_name – partition name, default: NULL
— Note:
— Given a schema name, this procedure scans all tables in the schema.
— For each table, if the deferred/delayed segment property is set for
— the table or any of its fragments, a new segment is created for
— those fragments and their dependent objects.
— Optionally,
— a. no schema name may be specified in which case we would scan tables
— belonging to all schemas
— b. both schema_name and table_name may be specified to do this
— operation on one particular table
— c. all three arguments may be supplied, in which case we will
— restrict this operation to that partition and its dependent
— objects
————————————————————————-
— PROCEDURE materialize_deferred_with_opt
————————————————————————-
procedure materialize_deferred_with_opt(
schema_name in varchar2 default NULL,
table_name in varchar2 default NULL,
partition_name in varchar2 default NULL,
partitioned_only in boolean default FALSE
);
—
— Description:
— Materialize segments for tables/table fragments with deferred
— segment creation (and their dependent objects), with an additional
— option.
— Input parameters:
— schema_name – schema name, default: NULL
— table_name – table name, default: NULL
— partition_name – partition name, default: NULL
— partitioned_only – apply materialize procedure on partitioned
— tables only, default: FALSE
— Note:
— The materialize_deferred_segments procedure is a wrapper around
— this. This procedure is required for downgrading (from 11.2.0.2)
— to materialize segments for partitioned tables only. The
— partitioned_only argument supports this limited behavior.
————————————————————————-
— PROCEDURE tablespace_fix_affinity
————————————————————————-
procedure tablespace_fix_affinity(
tablespace_name in varchar2,
block_num in binary_integer,
instance_id in binary_integer DEFAULT NULL
);
—
— Description:
— This procedure sets instance affinity of a File Bitmap Block
— in bigfile tablespace. The block can be either a Level 1 bitmap
— block (FFB) or a Level 2 bitmap block (FSB).
— The report will be generated in the corresponding trace file of
— the session that runs this procedure.
—
— Input parameters:
— tablespace_name – name of tablespace
— block_num – block number
— instance_id – id of instance to be affined, by default
— it is the current instance
—
— Note:
— (1) If the block is Second-level File Bitmap block (FSB), and if
— there is another FSB that has already been affined to the instance
— specified, this FSB will be freed.
— (2) This function is only used internally and does not require
— documentation.
end;
Comment