| 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'); |
