CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle DBMS_SPACE_ADMIN
Version 11.1
 
General
Purpose Provides functionality for locally managed tablespaces
First Available 8i with additions in 10.1
Source {ORACLE_HOME}/rdbms/admin/dbmsspc.sql

Constants
Name Data Type Value
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
SEGMENTS_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
Dependencies
DBA_SEGMENTS USER_SEGMENTS
DBA_SEGMENTS_OLD USER_TS_QUOTAS
DBA_TS_QUOTAS UTL_ALL_IND_COMPS
DBMS_SPACE_ADMIN_LIB _utl$_gnp_ind
DBMS_STATS_INTERNAL _utl$_gp_ind_parts
SM$TS_USED _utl$_lc_ind_subs
TBS_SPACE_USAGE _utl$_lnc_ind_parts
TS_PITR_OBJECTS_TO_BE_DROPPED
Object Privileges GRANT execute on DBMS_SPACE_ADMIN TO <schema_name>
GRANT execute on DBMS_SPACE_ADMIN TO uwclass;
 
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);
 
FLUSH_LOBSEGMENT_STAT
Undocumented dbms_space_admin.flush_lobsegment_stat;
exec dbms_space_admin.flush_lobsegment_stat;
 
NGLOB_SEGMENT_VERIFY (new 11g)

Undocumented. Used to support DBMS_SPACE.SPACE_USAGE
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);
TBD
 
PURGE_LOBSEGMENT_STAT
Undocumented dbms_space_admin.purge_lobsegment_stat;
exec dbms_space_admin.purge_lobsegment_stat;
 
SEGMENT_CORRUPT

Marks the segment 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);
exec dbms_space_admin.assm_tablespace_verify('EXAMPLE', 4, 33, 3); 

Marks the segment as 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);
exec dbms_space_admin.assm_tablespace_verify('EXAMPLE', 4, 33, 4);
 
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 ts$;

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

exec dbms_space_admin.segment_dump('UWDATA', 6, 9676);
 
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 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.
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 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
.
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_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_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 dbms_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 dbms_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 INTGER DEFAULT NULL);
exec dbms_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
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [197 users online]    © 2010 psoug.org