Oracle DBMS_SPACE_ADMIN
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.
Purpose Provides tablespace/segment space administration not available through the standard sql such as support for locally managed tablespaces.
AUTHID DEFINER
Constants
Name Data Type Value
SEGMENT_REPAIR_METADATA POSITIVE 1
SEGMENT_VERIFY_EXTENTS POSITIVE 1
SEGMENT_VERIFY_EXTENTS_GLOBAL POSITIVE 2
SEGMENT_MARK_CORRUPT POSITIVE 3
SEGMENT_MARK_VALID POSITIVE 4
SEGMENT_DUMP_EXTENT_MAP POSITIVE 5
TABLESPACE_VERIFY_BITMAP POSITIVE 6
TABLESPACE_EXTENT_MAKE_FREE POSITIVE 7
TABLESPACE_EXTENT_MAKE_USED POSITIVE 8
SEGMENT_VERIFY_BASIC POSITIVE 9
SEGMENT_VERIFY_DEEP POSITIVE 10
SEGMENT_VERIFY_SPECIFIC POSITIVE 11
HWM_CHECK POSITIVE 12
BMB_CHECK POSITIVE 13
SEG_DICT_CHECK POSITIVE 14
EXTENT_TS_BITMAP_CHECK POSITIVE 15
DB_BACKPOINTER_CHECK POSITIVE 16
EXTENT_SEGMENT_BITMAP_CHECK POSITIVE 17
BITMAPS_CHECK POSITIVE 18
TS_VERIFY_BITMAPS POSITIVE 19
TS_VERIFY_DEEP POSITIVE 20
TS_VERIFY_SEGMENTS POSITIVE 21
SEGMENT_DUMP_BITMAP_SUMMARY POSITIVE 27
 
NGLOB_HBB_CHECK POSITIVE 12
NGLOB_FSB_CHECK POSITIVE 13
NGLOB_PUA_CHECK POSITIVE 14
NGLOB_CFS_CHECK POSITIVE 15
HEAT_MAP_MAXSIZE_UNLIMITED POSITIVE 16
Dependencies
DBA_LOBS DBMS_STANDARD USER$
DBA_LOB_PARTITIONS DBMS_STATS_INTERNAL USER_SEGMENTS
DBA_LOB_SUBPARTITIONS OBJ$ USER_TS_QUOTAS
DBA_SEGMENTS PRVT_ILM UTL_ALL_IND_COMPS
DBA_SEGMENTS_OLD SM$TS_USED _utl$_gnp_ind
DBA_TAB_COLUMNS SYS_UNCOMPRESSED_SEGS _utl$_gp_ind_parts
DBA_TS_QUOTAS TBS_SPACE_USAGE _utl$_lc_ind_subs
DBMS_SPACE_ADMIN_LIB TS_PITR_OBJECTS_TO_BE_DROPPED _utl$_lnc_ind_parts
Documented Yes
First Available 8.1.5
Security Model Owned by SYS with no privileges granted
Source {ORACLE_HOME}/rdbms/admin/dbmsspc.sql
Subprograms
 
ASSM_SEGMENT_SYNCHWM
Synchronize HWMs of the ASSM segment

Returns 1 if the segment requires HWM synchronization: Otherwise returns 0
dbms_space_admin.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;
conn / as sysdba

SELECT tablespace_name, segment_space_management
FROM dba_tablespaces;

SELECT DISTINCT owner
FROM dba_tables
WHERE tablespace_name = 'EXAMPLE';

SELECT dbms_space_admin.assm_segment_synchwm('SH', 'SALES', 'TABLE', NULL, 1)
FROM dual;

SELECT partition_name
FROM dba_tab_partitions
WHERE table_owner = 'SH'
AND table_name = 'SALES';

SELECT DISTINCT segment_type
FROM dba_segments
ORDER BY 1;

SELECT dbms_space_admin.assm_segment_synchwm('SH', 'SALES', 'TABLE PARTITION', 'SALES_Q3_2000', 1)
FROM dual;
 
ASSM_SEGMENT_VERIFY
Verifies the basic consistency of the space metadata blocks as well as consistency between space metadata and segment data blocks dbms_space_admin.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);
exec dbms_space_admin.assm_segment_verify('SH', 'TIMES', 'TABLE', NULL, 10);
 
ASSM_TABLESPACE_VERIFY
Verifies all the segments created in an ASSM tablespace dbms_space_admin.assm_tablespace_verify(
tablespace_name IN VARCHAR2,
ts_option       IN POSITIVE,
segment_option  IN POSITIVE DEFAULT NULL);
exec dbms_space_admin.assm_tablespace_verify('EXAMPLE', 20, 10);
 
DROP_EMPTY_SEGMENTS
Drops segments from empty tables or table fragments and dependent segments dbms_space_admin.drop_empty_segments(
schema_name    IN VARCHAR2 DEFAULT NULL,
table_name     IN VARCHAR2 DEFAULT NULL,
partition_name IN VARCHAR2 DEFAULT NULL);
exec dbms_space_admin.drop_empty_segments;
 
FLUSH_LOBSEGMENT_STAT
Undocumented dbms_space_admin.flush_lobsegment_stat;
exec dbms_space_admin.flush_lobsegment_stat;
 
HEAT_MAP_SEGMENT_DROP (new 12.1)
Drops the HEATMAP segment created in the SYSAUX tablespace dbms_space_admin.heat_map_segment_drop;
exec dbms_space_admin.heat_map_segment_drop;
 
HEAT_MAP_SEGMENT_SETMAXSIZE (new 12.1)
Set the MAXSIZE, specified in datafile bocks, for the storage clause for HEATMAP the segment to limit its growth dbms_space_admin.heat_map_segment_setmaxsize(
maxsize IN BINARY_INTEGER DEFAULT HEAT_MAP_MAXSIZE_UNLIMITED);
exec dbms_space_admin.heat_map_segment_setmaxsize(1000);
 
MATERIALIZE_DEFERRED_SEGMENTS
Creates a segment for a table or index previously created and for which a segment does not currently exist dbms_space_admin.materialize_deferred_segments(
schema_name    IN VARCHAR2 DEFAULT NULL,
table_name     IN VARCHAR2 DEFAULT NULL,
partition_name IN VARCHAR2 DEFAULT NULL);
CREATE TABLE t (
testcol VARCHAR2(20));

CREATE INDEX ix_t
ON t(testcol);

SELECT segment_name
FROM user_segments
WHERE segment_name IN ('T', 'IX_T');

exec dbms_space_admin.materialize_deferred_segments;

SELECT segment_name
FROM user_segments
WHERE segment_name IN ('T', 'IX_T');
 
MATERIALIZE_DEFERRED_WITH_OPT
Materialize segments for tables/table fragments with deferred segment creation (and their dependent objects), with an additional option dbms_space_admin.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);
exec dbms_space_admin.materialize_deferred_with_opt('UWCLASS', 'SERVERS');
 
NGLOB_SEGMENT_VERIFY
Verifies the consistency of the segment dbms_space_admin.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); -- used only with the verify_option default

Verify Options
DEEP
SEGMENT_VERIFY_BASIC
SPECIFIC
TBD
 
PURGE_LOBSEGMENT_STAT
Undocumented dbms_space_admin.purge_lobsegment_stat;
exec dbms_space_admin.purge_lobsegment_stat;
 
SEGMENT_CORRUPT
Marks the segment corrupt or not corrupt dbms_space_admin.assm_tablespace_verify(
tablespace_name      IN VARCHAR2,
header_relative_file IN POSITIVE,
header_block         IN POSITIVE,
corrupt_option       IN POSITIVE DEFAULT SEGMENT_MARK_CORRUPT);
SELECT relative_fno
FROM dba_data_files
WHERE tablespace_ name = 'EXAMPLE';

SELECT header_block
FROM dba_segments
WHERE tablespace_name = 'EXAMPLE';

-- mark corrupt

exec dbms_space_admin.segment_corrupt('EXAMPLE', 10, 41090, dbms_space_admin.segment_mark_corrupt);

-- mark not corrupt
exec dbms_space_admin.segment_corrupt('EXAMPLE', 10, 41090, dbms_space_admin.segment_mark_valid);
 
SEGMENT_DROP_CORRUPT
Drops a segment currently marked corrupt (without reclaiming space). For this to work, the segment should have been marked temporary. To mark a corrupt segment as temporary, issue a DROP command on the segment.

dbms_space_admin.segment_drop_corrupt(
tablespace_name      IN VARCHAR2,
header_relative_file IN POSITIVE,
header_block         IN POSITIVE);
exec dbms_space_admin.segment_drop_corrupt('EXAMPLE', 4, 33);
 
SEGMENT_DUMP
Dumps the segment header and bitmap blocks of a specific segment to the location specified in the USER_DUMP_DEST initialization parameter dbms_space_admin.segment_dump(
tablespace_name      IN VARCHAR2,
header_relative_file IN POSITIVE,
header_block         IN POSITIVE,
dump_option          IN POSITIVE DEFAULT SEGMENT_DUMP_EXTENT_MAP);
SELECT ts#, name
FROM sys.ts$;

-- header type = 5
SELECT distinct type#, block#
FROM sys.seg$
WHERE ts# = 7
AND type# = 5;

exec dbms_space_admin.segment_dump('UWDATA', 6, 9676);
 
SEGMENT_EXTEND
Extends a SecureFile segment dbms_space_admin.segment_extend(
segment_owner  IN VARCHAR2,
segment_name   IN VARCHAR2, -- name of LOB segment
segment_type   IN VARCHAR2, -- values: 'LOB', 'LOB PARTITION', 'LOB SUBPARTITION'
partition_name IN VARCHAR2 DEFAULT NULL,
target_size    IN NUMBER   DEFAULT 1); -- target size in GB
TBD
 
SEGMENT_MOVEBLOCKS
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. dbms_space_admin.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);
TBD
 
SEGMENT_NUMBER_BLOCKS
Returns the number of blocks that belong to the segment. Will return NULL if segment has disappeared.

IS NOT to be used for any other purposes but by the views that need it and are sure that there info is correct. Else internal errors will abound. Ask me how I know.
dbms_space_admin.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;
Take the warning to the left seriously!
 
SEGMENT_NUMBER_EXTENTS
Returns the number of extents that belong to the segment. Will return NULL if segment has disappeared.

IS NOT to be used for any other
purposes but by the views that need it and are sure that there info is correct. Else internal errors will abound. Ask me how I know.
dbms_space_admin.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;
Take the warning to the left seriously!
 
SEGMENT_REPAIR
Used to corrupt the segment metadata by enabling event 42221 for verification testing purposes

Read this description one more time before thinking it is a good idea to try this.
dbms_space_admin.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);
Take the warning to the left seriously!

CREATE TABLE t AS SELECT * FROM user_objects;

exec dbms_space_admin.segment_repair(USER, 'T', 'TABLE');
 
SEGMENT_VERIFY
Checks the consistency of the segment extent map with the tablespace file bitmaps dbms_space_admin.segment_verify(
tablespace_name      IN VARCHAR2,
header_relative_file IN POSITIVE,
header_block         IN POSITIVE,
verify_option        IN POSITIVE DEFAULT SEGMENT_VERIFY_EXTENTS);
desc dba_data_files

SELECT file_id, tablespace_name
FROM dba_data_files;

SELECT header_block
FROM dba_segments
WHERE tablespace_name = 'UWDATA'

exec dbms_space_admin.segment_verify('UWDATA', 6, 9068, 2);
 
TABLEPACE_DUMP_BITMAPS
Dumps the tablespace space header of files dbms_space_admin.tablespace_dump_bitmaps(tablespace_name IN VARCHAR2);
-- choose a tablespace
SELECT tablespace_name, SUM(bytes)/1024/1024
FROM dba_data_files
GROUP BY tablespace_name;

exec dbms_space_admin.tablespace_dump_bitmaps('USERS');

-- the first few lines of the dump

*** SERVICE NAME:(SYS$USERS) 2007-03-14 21:39:46.937
*** SESSION ID:(159.3) 2007-03-14 21:39:46.937
Header Control:
RelFno: 4, Unit: 8, Size: 6240, Flag: 9
AutoExtend: YES, Increment: 160, MaxSize: 4194302
Initial Area: 7, Tail: 6240, First: 52, Free: 695
Deallocation scn: 3723938.0
Header Opcode:
Save: No Pending Op
File Space Bitmap Block:
BitMap Control:
RelFno: 4, BeginBlock: 9, Flag: 0, First: 52, Free: 63404
FFFFFFFFFFFFEFFF 1F00FFFF00000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000
 
TABLESPACE_FIX_AFFINITY (new 12.1)
sets instance affinity of a File Bitmap Block a in bigfile tablespace. The block can be either a Level 1 bitmap block (FFB) or a Level 2 bitmap block (FSB) dbms_space_admin.tablespace_fix_affinity(
tablespace_name IN VARCHAR2,
block_num       IN BINARY_INTEGER,
instance_id     IN BINARY_INTEGER DEFAULT NULL);
exec dbms_space_admin.tablespace_fix_affinity('UWDATA', 16464, 1);
 
TABLESPACE_FIX_BITMAPS
Marks the appropriate DBA range (extent) as free or used in bitmap dbms_space_admin.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);
exec dbms_space_admin.tablespace_fix_bitmaps('UWDATA', 4, 27, 67, 8);
 
TABLESPACE_FIX_SEGMENT_EXTBLKS
Tablespace fix segment extents and blocks based on segment header entries dbms_space_admin.segment_number_extblks(tablespace_name IN VARCHAR2);
TBD
 
TABLESPACE_FIX_SEGMENT_STATES
Fixes the state of the segments in a tablespace in which migration was aborted dbms_space_admin.tablespace_fix_segment_states(
tablespace_name IN VARCHAR2);
exec space_admin.tablespace_fix_segment_states('UWDATA');
 
TABLESPACE_MIGRATE_FROM_LOCAL
Migrates a locally-managed tablespace to dictionary-managed tablespace dbms_space_admin.tablespace_migrate_from_local(
tablespace_name IN VARCHAR2);
exec space_admin.tablespace_migrate_from_local('USERS');
 
TABLESPACE_MIGRATE_TO_LOCAL
Migrates a tablespace from dictionary managed format to locally managed format dbms_space_admin.tablespace_migrate_to_local(
tablespace_name IN VARCHAR2,
unit_size       IN POSITIVE DEFAULT NULL,
rfno            IN INTEGER DEFAULT NULL);
exec space_admin.tablespace_migrate_to_local('UWDATA', 512, 4);
 
TABLESPACE_REBUILD_BITMAPS
Rebuilds tablespace bitmaps dbms_space_admin.tablespace_rebuild_bitmaps(
tablespace_name      IN VARCHAR2,
bitmap_relative_file IN POSITIVE DEFAULT NULL,
bitmap_block         IN POSITIVE DEFAULT NULL);
exec dbms_space_admin.tablespace_rebuild_bitmaps('UWDATA');
 
TABLESPACE_REBUILD_QUOTAS
Rebuilds tablespace quotas dbms_space_admin.tablespace_rebuild_quotas(tablespace_name IN VARCHAR2);
exec dbms_space_admin.tablespace_rebuild_quotas('UWDATA');
 
TABLESPACE_RELOCATE_BITMAPS
Relocates the bitmaps to the destination specified dbms_space_admin.tablespace_relocate_bitmaps(
tablespace_name IN VARCHAR2,
filno           IN POSITIVE,
blkno           IN POSITIVE);
exec dbms_space_admin.tablespace_relocate_bitmaps('UWDATA', 4, 3);
 
TABLESPACE_VERIFY
Verifies that the bitmaps and extent maps for the segments in the tablespace are in sync dbms_space_admin.tablespace_verify(
tablespace_name IN VARCHAR2,
verify_option   IN POSITIVE DEFAULT TABLESPACE_VERIFY_BITMAP);
exec dbms_space_admin.tablespace_verify('UWDATA');

Related Topics
DBMS_SPACE
Packages

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved