ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling
complimentary technical Workshops on Database Security for the first 30
Oracle Database customers located anywhere in North America, EMEA, LATAM, or
APAC that send an email to
asra_us@oracle.com. Request a Workshop for
your organization today.
Purpose
Provides tablespace/segment space administration not available through the standard sql such as support for locally managed tablespaces.
Gets an attribute of space administration ... but do not know which one. The only values accepted are 1 or 2. the default value is 1 but the value can also be set to 0.
dbms_space_admin.get_segadv_attrib(
attribute IN NUMBER, -- must be 1 or 2
value OUT NUMBER);
DECLARE
retVal NUMBER;
BEGIN
dbms_space_admin.get_segadv_attrib(1, retVal);
dbms_output.put_line(retVal);
END;
/
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_all_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;
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);
Trace file C:\APP\ORACLE\diag\rdbms\orabase\orabase\trace\orabase_ora_5684.trc
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Windows NT Version V6.1 Service Pack 1
CPU : 4 - type 8664, 2 Physical Cores
Process Affinity : 0x0x0000000000000000
Memory (Avail/Total): Ph:4970M/8075M, Ph+PgF:13009M/16148M
Instance name: orabase
Redo thread mounted by this instance: 1
Oracle process number: 49
Windows thread id: 5684, image: ORACLE.EXE (SHAD)
Dummy procedure for loading in memory columnar segment
dbms_space_admin.imc_segment_load(
segment_owner IN VARCHAR2,
segment_name IN VARCHAR2,
segment_type IN VARCHAR2,
partition_name IN VARCHAR2,
query IN BINARY_INTEGER);
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
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);
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);
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;
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
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);
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;
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;
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);
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'
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);
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);
dbms_space_admin.tablespace_rebuild_bitmaps(
tablespace_name IN VARCHAR2,
bitmap_relative_file IN POSITIVE DEFAULT NULL,
bitmap_block IN POSITIVE DEFAULT NULL);
