Managing Tablespaces

Managing Tablespaces

Obtaining Tablespace Information
-- tablespace size usage report (for large db (>100GB), it takes long time)
BREAK ON REPORT
COMPUTE SUM OF tbsp_size ON REPORT
compute SUM OF used ON REPORT
compute SUM OF free ON REPORT
COL tbspname FORMAT a20 HEADING 'Tablespace Name'
COL tbsp_size FORMAT 999,999 HEADING 'Size|(MB)'
COL used FORMAT 999,999 HEADING 'Used|(MB)'
COL free FORMAT 999,999 HEADING 'Free|(MB)'
COL pct_used FORMAT 999 HEADING'% Used'
SELECT df.tablespace_name tbspname,
sum(df.bytes)/1024/1024 tbsp_size,
nvl(sum(e.used_bytes)/1024/1024,0) used,
nvl(sum(f.free_bytes)/1024/1024,0) free,
nvl((sum(e.used_bytes)*100)/sum(df.bytes),0) pct_used
FROM DBA_DATA_FILES df,
Page 80 Oracle DBA Code Examples
(SELECT file_id,
SUM(nvl(bytes,0)) used_bytes
FROM dba_extents
GROUP BY file_id) e,
(SELECT MAX(bytes) free_bytes, file_id
FROM dba_free_space
GROUP BY file_id) f
WHERE e.file_id(+) = df.file_id
AND df.file_id = f.file_id(+)
GROUP BY df.tablespace_name
ORDER BY 5 DESC
-- for a specific tablespace:
SELECT df.tablespace_name tbspname,
sum(df.bytes)/1024/1024 tbsp_size,
nvl(sum(e.used_bytes)/1024/1024,0) used,
nvl(sum(f.free_bytes)/1024/1024,0) free,
nvl((sum(e.used_bytes)*100)/sum(df.bytes),0) pct_used
FROM DBA_DATA_FILES df,
(SELECT file_id,
SUM(nvl(bytes,0)) used_bytes
FROM dba_extents
WHERE TABLESPACE_NAME='REC_DATA'
GROUP BY file_id) e,
(SELECT MAX(bytes) free_bytes, file_id
FROM dba_free_space
WHERE TABLESPACE_NAME='REC_DATA'
GROUP BY file_id) f
WHERE e.file_id(+) = df.file_id
AND df.file_id = f.file_id(+)
AND TABLESPACE_NAME='REC_DATA'
GROUP BY df.tablespace_name;
-- *************************************************************
-- tablespace sizes (without getting used and free space) (quick response):
SELECT DF.TABLESPACE_NAME TBSPNAME,
ROUND(SUM(DF.BYTES)/1024/1024/1024,2) GB,
COUNT(FILE_NAME) DATAFILES
FROM DBA_DATA_FILES DF
GROUP BY ROLLUP(DF.TABLESPACE_NAME )
ORDER BY 1
-- free space in temp tablespaces:
select sum(free_blocks)
from gv$sort_segment
where tablespace_name = 'USER_TEMP' ;
-- tablespace info from control file
select TS#,NAME,INCLUDED_IN_DATABASE_BACKUP,
BIGFILE,FLASHBACK_ON,ENCRYPT_IN_BACKUP
from V$TABLESPACE;
-- descriptions of tablespaces
select TABLESPACE_NAME,BLOCK_SIZE,INITIAL_EXTENT,
NEXT_EXTENT NEXT_EXTENT_SIZE,MIN_EXTENTS,MAX_EXTENTS,

PCT_INCREASE,MIN_EXTLEN,STATUS,
CONTENTS,LOGGING,FORCE_LOGGING,
EXTENT_MANAGEMENT,ALLOCATION_TYPE,PLUGGED_IN,
SEGMENT_SPACE_MANAGEMENT,DEF_TAB_COMPRESSION,RETENTION
from DBA_TABLESPACES
-- segments contained in tablespaces
select OWNER,SEGMENT_NAME,PARTITION_NAME,SEGMENT_TYPE,S.TABLESPACE_NAME,
HEADER_FILE,HEADER_BLOCK,S.BYTES/1024/1024 SEGMENT_SIZE_MB,
D.BLOCKS SEGMENT_BLOCKS,EXTENTS,S.INITIAL_EXTENT,S.NEXT_EXTENT
NEXT_EXTENT_SIZE, S.MIN_EXTENTS,S.MAX_EXTENTS,S.PCT_INCREASE,
FREELISTS,FREELIST_GROUPS,D.RELATIVE_FNO, D.FILE_NAME
from DBA_SEGMENTS S, DBA_TABLESPACES T, DBA_DATA_FILES D
where S.TABLESPACE_NAME = T.TABLESPACE_NAME AND T.TABLESPACE_NAME =
D.TABLESPACE_NAME
AND S.RELATIVE_FNO = D.RELATIVE_FNO
AND T.TABLESPACE_NAME NOT IN ('SYSAUX','SYSTEM')
order BY S.TABLESPACE_NAME
-- extents contained by tablespaces
SELECT EXTENT_ID, E.BLOCK_ID,E.BYTES/1024 EXTENT_SIZE_KB,
E.OWNER,E.SEGMENT_NAME,E.PARTITION_NAME,
E.SEGMENT_TYPE,D.FILE_NAME, S.TABLESPACE_NAME,HEADER_FILE,
HEADER_BLOCK SEG_HEADER_BLOCK,S.BYTES/1024/1024 SEGMENT_SIZE_MB,D.BLOCKS
SEGMENT_BLOCKS, EXTENTS SEG_EXTENTS
FROM DBA_EXTENTS E, DBA_SEGMENTS S, DBA_DATA_FILES D
WHERE E.OWNER=S.OWNER AND E.SEGMENT_NAME = S.SEGMENT_NAME
AND NVL(E.PARTITION_NAME,'0') = NVL(S.PARTITION_NAME,'0')
AND E.SEGMENT_TYPE = S.SEGMENT_TYPE
AND E.FILE_ID = D.FILE_ID
AND S.TABLESPACE_NAME NOT IN ('SYSAUX','SYSTEM')
ORDER BY E.SEGMENT_NAME, E.OWNER, E.PARTITION_NAME,E.EXTENT_ID
-- free extents within tablespaces
SELECT F.TABLESPACE_NAME,F.FILE_ID,F.BLOCK_ID,
F.BYTES/1024/1024 FREE_MB, D.FILE_NAME
FROM DBA_FREE_SPACE F, DBA_DATA_FILES D
WHERE F.FILE_ID = D.FILE_ID
UNION
SELECT F.TABLESPACE_NAME, TO_NUMBER('') AS FILE_ID, TO_NUMBER('') AS BLOCK_ID,
SUM(F.BYTES/1024/1024) FREE_MB, TO_CHAR('') AS FILE_NAME
FROM DBA_FREE_SPACE F
GROUP BY F.TABLESPACE_NAME, TO_NUMBER('') , TO_NUMBER('') ,TO_CHAR('')
ORDER BY TABLESPACE_NAME
-- data files (from control file)
SELECT FILE#,T.NAME TABLESPACE_NAME,D.NAME FILENAME,
CREATION_CHANGE#,CREATION_TIME, RFILE#,STATUS,
ENABLED,CHECKPOINT_CHANGE#,CHECKPOINT_TIME,
UNRECOVERABLE_CHANGE#,UNRECOVERABLE_TIME,LAST_CHANGE#,
LAST_TIME,OFFLINE_CHANGE#,ONLINE_CHANGE#,
ONLINE_TIME,BYTES/1024/1024 FILESIZE_MB,BLOCKS,
CREATE_BYTES,BLOCK_SIZE,
PLUGGED_IN,BLOCK1_OFFSET,AUX_NAME
FROM V$DATAFILE D, V$TABLESPACE T
WHERE D.TS# = T.TS#
ORDER BY TABLESPACE_NAME, D.RFILE#
-- data files

select FILE_NAME,FILE_ID,T.TABLESPACE_NAME,
F.STATUS FILE_STATUS,
RELATIVE_FNO,
AUTOEXTENSIBLE,
ROUND(BYTES/1024/1024,2) FILESIZE_MB,
ROUND(MAXBYTES/1024/1024,2) MAXSIZE_MB,
ROUND((INCREMENT_BY*T.BLOCK_SIZE)/1024/1024,2) AUTOEXTENSION_SIZE_MB,
ROUND(USER_BYTES/1024/1024,2) AVAILABLE_FOR_DATA_MB -- remaining size used
from DBA_DATA_FILES F, DBA_TABLESPACES T -- for storing metadata
where F.TABLESPACE_NAME = T.TABLESPACE_NAME
order BY TABLESPACE_NAME, F.RELATIVE_FNO
-- tempfiles included in tablespaces
select M.NAME TEMPFILE,FILE#,T.NAME TABLESPACE_NAME,
CREATION_TIME,M.TS#,RFILE#,
STATUS,ENABLED,BYTES,
BLOCKS,CREATE_BYTES,BLOCK_SIZE
from V$TEMPFILE M, V$TABLESPACE T
where M.TS# = T.TS#
order by T.NAME
select FILE_NAME,FILE_ID,T.TABLESPACE_NAME,
F.STATUS FILE_STATUS,
RELATIVE_FNO,
AUTOEXTENSIBLE,
ROUND(BYTES/1024/1024,2) FILESIZE_MB,
ROUND(MAXBYTES/1024/1024,2) MAXSIZE_MB,
ROUND((INCREMENT_BY*T.BLOCK_SIZE)/1024/1024,2) AUTOEXTENSION_SIZE_MB
from DBA_TEMP_FILES F, DBA_TABLESPACES
where F.TABLESPACE_NAME = T.TABLESPACE_NAME
order BY TABLESPACE_NAME, F.RELATIVE_FNO
-- extents in all locally managed temporary tablespaces
SELECT E.TABLESPACE_NAME,E.FILE_ID,
BLOCK_ID BEGIN_BLOCK#,
ROUND(E.BYTES/1024,2) EXTENT_SIZE_KB,
E.BLOCKS,OWNER
FROM V$TEMP_EXTENT_MAP E
-- tablespace groups
select GROUP_NAME, TABLESPACE_NAME
from DBA_TABLESPACE_GROUPS
order BY TABLESPACE_NAME
-- user qoutas
SELECT
USERNAME,TABLESPACE_NAME, BYTES/1024 SIZE_KB,
DECODE(MAX_BYTES,-1,-1,MAX_BYTES/1024/1024) MAX_MB
FROM DBA_TS_QUOTAS
ORDER BY USERNAME
-- diskspace usage by USER
select owner, round(sum(bytes)/1024/1024,2) space_in_mb
from dba_segments
group by owner
WHERE OWNER NOT IN ('SYSTEM', 'SYSMAN',
'SYS','WKSYS','WK_TEST','WMSYS','XDB','OUTLN','PERFSTAT','OLAPSYS','ORDSYS','M
DSYS','EXFSYS','DMSYS','CTXSYS','REPTEST','SCOTT','RMAN')

order by round(sum(bytes)/1024/1024,2) desc;
-- user temporary usage
-- SEGTYPE: SORT,HASH,DATA,INDEX,LOB_DATA,LOB_INDEX
SELECT USERNAME,SESSION_NUM SESSION_SN,
SQLADDR,SQLHASH,SQL_ID,
TABLESPACE,
SEGTYPE,SEGFILE# INIT_EXTENT_FILE#,SEGBLK#
INIT_EXTENT_BLK#,EXTENTS,BLOCKS,SEGRFNO#
FROM V$TEMPSEG_USAGE
Creating a Locally Managed Tablespace
CREATE TABLESPACE lmtbsb DATAFILE '/u02/oracle/data/lmtbsb01.dbf' SIZE 50M
EXTENT MANAGEMENT AUTOALLOCATE;
CREATE TABLESPACE lmtbsb DATAFILE '/u02/oracle/data/lmtbsb01.dbf' SIZE 50M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K; -- default 1MB
-- creating a tablespace in an ASM diskgroup
CREATE TABLESPACE sample DATAFILE '+dgroup1';
CREATE TABLESPACE satbs DATAFILE '+DATA' size 50m;
Specifying Segment Space Management
CREATE TABLESPACE lmtbsb DATAFILE '/u02/oracle/data/lmtbsb01.dbf' SIZE 50M
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO; -- though it's the default
-- the other option is MANUAL
Adding Space to Tablespace
ALTER TABLESPACE test01 ADD DATAFILE '..' SIZE 1000M;
ALTER DATABASE DATAFILE '…' RESIZE 500m;
Specifying Nonstandard Block Sizes for Tablespaces
-- must be 2KB, 4KB, 8KB, 16KB, or 32KB.
-- requirement
set DB_CACHE_SIZE, DB_nK_CACHE_SIZE
select value/1024 KB from v$parameter where name='db_block_size';
CREATE TABLESPACE lmtbsb DATAFILE '/u02/oracle/data/lmtbsb01.dbf' SIZE 50M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K BLOCKSIZE 8K;
Using Bigfile Tablespace (BFT)
 Oracle recommends that you change the extent allocation policy from AUTOALLOCATE,
which is the default, to UNIFORM and set a very high extent size.
select TABLESPACE_NAME, BIGFILE from DBA_TABLESPACES order by BIGFILE;
-- use BigFile tablespaces only with ASM or RAID volume manager.
-- it can take up to 4G blocks. with 8K blocks = 32 terabyte datafile
CREATE BIGFILE TABLESPACE bigtbs -- the other option SMALLFILE
DATAFILE '/u02/oracle/data/bigtbs01.dbf' SIZE 50G -- T is acceptable
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 65536K; -- default 1m
ALTER TABLESPACE bigtbs RESIZE 80G;

ALTER TABLESPACE bigtbs AUTOEXTEND ON NEXT 20G;
-- with this sitting, by default all tbs created later will be BFT
CREATE DATABASE SET DEFAULT BIGFILE tablespace … -- the other option SMALLFILE
ALTER TABLESPACE SET DEFAULT BIGFILE TABLESPACE;
SELECT property_value FROM database_properties
WHERE property_name='DEFAULT_TBS_TYPE';
Using Temporary Tablespace
 Oracle recommends creating temporary tablespaces with multiples-of-64KB extent sizes.
For large data warehousing, make it 1MB.
select * from V$TEMPFILE;
select * from DBA_TEMP_FILES;
CREATE TEMPORARY TABLESPACE lmtemp TEMPFILE '/u02/oracle/data/lmtemp01.dbf'
SIZE 20M REUSE
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16M; -- default is 1M (AUTOALLOCATE not
allowed)
CREATE BIGFILE TABLESPACE bigtbs
TEMPFILE '/u02/oracle/data/bigtbs01.dbf' SIZE 50G
ALTER TABLESPACE lmtemp ADD TEMPFILE '/u02/oracle/data/lmtemp02.dbf' SIZE 18M
REUSE;
-- doesn't apply on default temporary tablespace
ALTER TABLESPACE lmtemp TEMPFILE OFFLINE;
ALTER TABLESPACE lmtemp TEMPFILE ONLINE;
ALTER DATABASE TEMPFILE '/u02/oracle/data/lmtemp02.dbf' OFFLINE;
ALTER DATABASE TEMPFILE '/u02/oracle/data/lmtemp02.dbf' ONLINE;
ALTER DATABASE TEMPFILE '/u02/oracle/data/lmtemp02.dbf' RESIZE 18M;
-- the tablespace isn't dropped
ALTER DATABASE TEMPFILE '/u02/oracle/data/lmtemp02.dbf' DROP INCLUDING
DATAFILES;
Renaming a Tempfile
ALTER DATABASE TEMPFILE 'C:\ORACLE\ORADATA\ORCL\TEMP02.DBF' OFFLINE;
ren C:\ORACLE\ORADATA\ORCL\TEMP02.DBF TEMP03.DBF
ALTER DATABASE RENAME FILE 'C:\ORACLE\ORADATA\ORCL\TEMP02.DBF' TO
'C:\ORACLE\ORADATA\ORCL\TEMP03.DBF';
ALTER DATABASE TEMPFILE 'C:\ORACLE\ORADATA\ORCL\TEMP03.DBF' ONLINE;
Shrinking Temporary Tablespace
ALTER TABLESPACE temp SHRINK SPACE KEEP 1000M;
ALTER TABLESPACE temp SHRINK SPACE TEMPFILE tempfile
'/u01/app/oracle/oradata/prod1/temp02.dbf' KEEP 100m;
SELECT file#, name, bytes/1024/1024 mb FROM v$tempfile;

Using Default Temporary Tablespace
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temptbs02; -- can be temp tbs grp
SELECT PROPERTY_NAME, PROPERTY_VALUE
FROM database_properties
WHERE property_name='DEFAULT_TEMP_TABLESPACE';
Using Temporary Tablespace Groups
CREATE TEMPORARY TABLESPACE lmtemp2 TEMPFILE '/u02/oracle/data/lmtemp201.dbf'
SIZE 50M TABLESPACE GROUP group1;
ALTER TABLESPACE lmtemp TABLESPACE GROUP group2;
-- remove it from a group
ALTER TABLESPACE lmtemp3 TABLESPACE GROUP '';
-- Assigning a Tablespace Group as the Default Temporary Tablespace
ALTER DATABASE mydb DEFAULT TEMPORARY TABLESPACE group2;
select GROUP_NAME, TABLESPACE_NAME
from DBA_TABLESPACE_GROUPS
order BY TABLESPACE_NAME
Suppressing Redo Generation for a Tablespace
CREATE TABLESPACE .. NOLOGGING;
Controlling Tablespaces Availability
-- NORMAL, TEMPORARY, IMMEDIATE (not possible in NOARCHIVELOG)
ALTER TABLESPACE users OFFLINE NORMAL;
-- media recovery required if it was offline using TEMPORARY or IMMEDIATE
ALTER TABLESPACE users ONLINE;
Using Read-Only Tablespaces
-- backup the tablespace after making it read only
-- it waits for all transactions started before
ALTER TABLESPACE flights READ ONLY;
-- to list blocking transactions:
SELECT SQL_TEXT, SADDR
FROM V$SQLAREA,V$SESSION
WHERE V$SQLAREA.ADDRESS = V$SESSION.SQL_ADDRESS
AND SQL_TEXT LIKE 'alter tablespace%';
-- all transactions on top of SADDR retuned by previous statement
-- are blocking transactions
SELECT T.SES_ADDR, T.START_SCNB, S.USERNAME, S.MACHINE
FROM V$TRANSACTION T, V$SESSION S
WHERE T.SES_ADDR = S.SADDR
ORDER BY START_SCNB;
-- back to read write
ALTER TABLESPACE flights READ WRITE;
Renaming Tablespaces
ALTER TABLESPACE users RENAME TO usersts;
Default Permanent Tabelspace
SELECT property_value FROM database_properties WHERE

property_name='DEFAULT_PERMANENT_TABLESPACE';
ALTER DATABASE DEFAULT TABLESPACE users;
Dropping Tablespaces
DROP TABLESPACE users INCLUDING CONTENTS;
DROP TABLESPACE test01 CASCADE CONSTRAINTS;
DROP TABLESPACE users INCLUDING CONTENTS CASCADE CONSTRAINTS;
DROP TABLESPACE users INCLUDING CONTENTS AND DATAFILES;
Managing the SYSAUX Tablespace
A typical system with an average of 30 concurrent active sessions may require approximately
200 to 300 MB of space for its AWR data.
-- to monitor is occupants
-- to know which procedure to use to move an occupant
SELECT OCCUPANT_NAME , OCCUPANT_DESC , SCHEMA_NAME ,MOVE_PROCEDURE
,MOVE_PROCEDURE_DESC ,SPACE_USAGE_KBYTES FROM V$SYSAUX_OCCUPANTS
Diagnosing and Repairing Locally Managed Tablespace Problems
Verifying the Integrity of Segments Created in ASSM Tablespaces.
 Use DBMS_SPACE_ADMIN.ASSM_SEGMENT_VERIFY.
 If ASSM is disabled, use SEGMENT_VERIFY.
 After execution, check sid_ora_process_ID.trc in USER_DUMP_DEST.
 The parameter verify_option takes one of the following constants:
o SEGMENT_VERIFY_DEEP (9)
o SEGMENT_VERIFY_BASIC (10) default
o SEGMENT_VERIFY_SPECIFIC (11) then the attrib parameter is considered.
 attrib takes one of the following constants:
o HWM_CHECK (12) checks whether high water mark information is accurate.
o BMB_CHECK (13) checks whether space bitmap blocks have correct backpointers to
the segment header.
o SEG_DICT_CHECK (14) checks whether dictionary information for segment is
accurate.
o EXTENT_TS_BITMAP_CHECK (15) checks whether extent maps are consistent with file
level bitmaps.
o DB_BACKPOINTER_CHECK (16) checks whether datablocks have correct backpointers
to the space metadata blocks.
o EXTENT_SEGMENT_BITMAP_CHECK (17) checks whether extent map in the segment
match with the bitnaps in the segment.
o BITMAPS_CHECK (18) checks whether space bitmap blocks are accurate.
declare
v_segname varchar2(100) := 'EMPLOYEES';
v_segowner varchar2(100) :='HR';
v_segtype varchar2(100) :='TABLE';
v_tbs varchar2(100);
Page 87 Oracle DBA Code Examples
begin
select tablespace_name
into v_tbs
from dba_segments
where segment_name=v_segname and owner=v_segowner
and segment_type=v_segtype;
DBMS_SPACE_ADMIN.ASSM_SEGMENT_VERIFY (
segment_owner =>v_segowner,
segment_name =>v_segname,
segment_type =>v_segtype,
partition_name =>'',
verify_option => DBMS_SPACE_ADMIN.SEGMENT_VERIFY_DEEP
);
end;
/
-- Check sid_ora_process_ID.trc in USER_DUMP_DEST
select value from v$parameter where upper(name)='USER_DUMP_DEST'
declare
v_segname varchar2(100) := 'EMPLOYEES';
v_segowner varchar2(100) :='HR';
v_segtype varchar2(100) :='TABLE';
v_tbs varchar2(100);
begin
select tablespace_name
into v_tbs
from dba_segments
where segment_name=v_segname and owner=v_segowner
and segment_type=v_segtype;
DBMS_SPACE_ADMIN.ASSM_SEGMENT_VERIFY (
segment_owner =>v_segowner,
segment_name =>v_segname,
segment_type =>v_segtype,
partition_name =>'',
verify_option => DBMS_SPACE_ADMIN.SEGMENT_VERIFY_SPECIFIC,
attrib => DBMS_SPACE_ADMIN.BMB_CHECK
);
end;
/
-- Check sid_ora_process_ID.trc in USER_DUMP_DEST
select value from v$parameter where upper(name)='USER_DUMP_DEST'
Checking Consistency of Segment Extent Map with Tablespace File Bitmaps
 Use ASSM_SEGMENT_VERIFY to segment residing in a tablespace with automatic segment
space management enabled and SEGMENT_VERIFY when it is disabled.
 After execution, check sid_ora_process_ID.trc in USER_DUMP_DEST.
conn sys as sysdba
declare
v_segname varchar2(100) := 'EMPLOYEES';
v_segowner varchar2(100) :='HR';
Page 88 Oracle DBA Code Examples
v_tbs varchar2(100);
v_fno number ;
v_rfno number;
v_hdr number;
begin
-- retreive tablespace name, absolute file number
select tablespace_name, header_file, header_block
into v_tbs, v_fno, v_hdr
from dba_segments
where segment_name=v_segname and owner=v_segowner;
select relative_fno
into v_rfno
from dba_data_files where tablespace_name = v_tbs and file_id=v_fno;
DBMS_SPACE_ADMIN.SEGMENT_VERIFY(
tablespace_name =>v_tbs,
header_relative_file =>v_rfno,
header_block =>v_hdr,
verify_option =>dbms_space_admin.SEGMENT_VERIFY_EXTENTS_GLOBAL --
default SEGMENT_VERIFY_EXTENTS
);
end;
/
-- Check sid_ora_process_ID.trc in USER_DUMP_DEST
select value from v$parameter where upper(name)='USER_DUMP_DEST'
Verifying the Integrity of ASSM Tablespaces
 Use DBMS_SPACE_ADMIN.ASSM_TABLESPACE_VERIFY
 If ASSM is disabled, use TABLESPACE_VERIFY
 After execution, check sid_ora_process_ID.trc in USER_DUMP_DEST.
 The parameter ts_option takes one of the following constants:
o TS_VERIFY_BITMAPS (19) (Default) The bitmaps are verified against the extents. This
will detect bits that are marked used or free wrongly and will also detect multiple
allocation of extents. The file metadata will be validated against file$ and control file.
o TS_VERIFY_DEEP (20) verifies the file bitmaps as well perform checks on all the
segments.
o TS_SEGMENTS (21) This option is used to invoke SEGMENT_VERIFY on all the
segments in the tablespace.
 segment_option: when the TABLESPACE_VERIFY_SEGMENTS or
TABLESPACE_VERIFY_DEEP is selected, the SEGMENT_OPTION can be specified optionally.
When TS_VERIFY_SEGMENTS is specified, segment_option can be one of the following:
o SEGMENT_VERIFY_BASIC (9)
o SEGMENT_VERIFY_DEEP (10)
 The value of segment_option is NULL when TS_VERIFY_DEEP or TS_VERIFY_BASIC is
specified.
 After execution, check sid_ora_process_ID.trc in USER_DUMP_DEST
conn sys as sysdba
select name from v$tablespace order by name;
begin

DBMS_SPACE_ADMIN.ASSM_TABLESPACE_VERIFY(
tablespace_name =>'EXAMPLE',
ts_option => DBMS_SPACE_ADMIN.TS_VERIFY_DEEP,
segment_option =>NULL);
end;
/
-- Check sid_ora_process_ID.trc in USER_DUMP_DEST
select value from v$parameter where upper(name)='USER_DUMP_DEST'
Marking the Segment Corrupt or Valid
 corrupt_option takes one of the following:
o SEGMENT_MARK_CORRUPT (default)
o SEGMENT_MARK_VALID
conn sys as sysdba
declare
v_segname varchar2(100) := 'EMPLOYEES';
v_segowner varchar2(100) :='HR';
v_tbs varchar2(100);
v_fno number ;
v_rfno number;
v_hdr number;
begin
-- retreive tablespace name, absolute file number
select tablespace_name, header_file, header_block
into v_tbs, v_fno, v_hdr
from dba_segments
where segment_name=v_segname and owner=v_segowner;
select relative_fno
into v_rfno
from dba_data_files
where tablespace_name = v_tbs and file_id=v_fno;
DBMS_SPACE_ADMIN.SEGMENT_CORRUPT (
tablespace_name =>v_tbs,
header_relative_file =>v_rfno,
header_block =>v_hdr,
corrupt_option =>DBMS_SPACE_ADMIN.SEGMENT_MARK_CORRUPT);
end;
Dropping a Corrupted Segment
 Use SEGMENT_DROP_CORRUPT to drop a segment currently marked corrupt (without
reclaiming space). The space for the segment is not released, and it must be fixed by
using the TABLESPACE_FIX_BITMAPS Procedure or the TABLESPACE_REBUILD_BITMAPS
Procedure.
 If the segment state is valid, the procedure returns ORA-03211 error.
conn sys as sysdba
declare
v_segname varchar2(100) := 'EMP';
v_segowner varchar2(100) :='HR';
v_tbs varchar2(100);
v_fno number ;

v_rfno number;
v_hdr number;
begin
-- retreive tablespace name, absolute file number
select tablespace_name, header_file, header_block
into v_tbs, v_fno, v_hdr
from dba_segments
where segment_name=v_segname and owner=v_segowner;
select relative_fno
into v_rfno
from dba_data_files
where tablespace_name = v_tbs and file_id=v_fno;
DBMS_SPACE_ADMIN.SEGMENT_DROP_CORRUPT(
tablespace_name =>v_tbs,
header_relative_file =>v_rfno,
header_block => v_hdr);
end;
Dumping a Segment Header and Bitmap Blocks
 Use DBMS_SPACE_ADMIN.SEGMENT_DUMP
 The dump file named sid_ora_process_id.trc is generated in the location specified in the
USER_DUMP_DEST
conn sys as sysdba
declare
v_segname varchar2(100) := 'NAMES';
v_segowner varchar2(100) :='HR';
v_tbs varchar2(100);
v_fno number;
v_rfno number;
v_hdr number;
begin
-- retreive tablespace name, absolute file number
select tablespace_name, header_file, header_block
into v_tbs, v_fno, v_hdr
from dba_segments
where segment_name=v_segname and owner=v_segowner;
select relative_fno
into v_rfno
from dba_data_files
where tablespace_name = v_tbs and file_id=v_fno;
DBMS_SPACE_ADMIN.SEGMENT_DUMP(
tablespace_name =>v_tbs,
header_relative_file =>v_rfno,
header_block =>v_hdr,
dump_option => DBMS_SPACE_ADMIN.SEGMENT_DUMP_EXTENT_MAP);
end;
Marking a DBA Range in Bitmap as Free or Used
 The procedure TABLESPACE_FIX_BITMAPS marks the appropriate DBA range (extent) as
free or used in bitmap.
 The BEGIN and END blocks should be in extent boundary and should be extent multiple.
 fix_option takes one of the following

o TABLESPACE_EXTENT_MAKE_FREE
o TABLESPACE_EXTENT_MAKE_USED
conn sys as sysdba
DBMS_SPACE_ADMIN.TABLESPACE_FIX_BITMAPS (
tablespace_name =>'EXAMPLE',
dbarange_relative_file =>4,
dbarange_begin_block =>33,
dbarange_end_block =>83,
fix_option =>DBMS_SPACE_ADMIN.TABLESPACE_EXTENT_MAKE_FREE);
Rebuilding the Appropriate Bitmap
conn sys
begin
DBMS_SPACE_ADMIN.TABLESPACE_REBUILD_BITMAPS (
tablespace_name =>'EXAMPLE',
bitmap_relative_file =>NULL, -- all files
bitmap_block =>NULL); -- Block number of bitmap block to rebuild
-- NULL = all blocks
end;
Rebuilding Quotas for Given Tablespace
conn sys
exec DBMS_SPACE_ADMIN.TABLESPACE_REBUILD_QUOTAS('USERS');
Migrating from a Dictionary-Managed to a Locally Managed Tablespace
-- This operation is done online, but space management operations are blocked
-- ASSM won't be active on migrated objects
conn sys
EXEC DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL ('USERS');
-- another way requiring table lock (better)
ALTER TABLE emp MOVE TABLESPACE tbsp_new;
ALTER INDEX emp_pk_idx REBUILD TABLESPACE tbsp_idx_new;
Fixing the State of the Segments in A Tablespace
Use TABLESPACE_FIX_SEGMENT_STATES to fix the state of the segments in a tablespace in
which migration was aborted.
conn sys
EXECUTE DBMS_SPACE_ADMIN.TABLESPACE_FIX_SEGMENT_STATES('TS1')
Scenario 1: Fixing Bitmap When Allocated Blocks are Marked Free (No Overlap)
The TABLESPACE_VERIFY procedure discovers that a segment has allocated blocks that are
marked free in the bitmap, but no overlap between segments is reported.
In this scenario, perform the following tasks:
1. Call the SEGMENT_DUMP procedure to dump the ranges that the administrator allocated to
the segment.
2. For each range, call the TABLESPACE_FIX_BITMAPS procedure with the
TABLESPACE_EXTENT_MAKE_USED option to mark the space as used.
Page 92 Oracle DBA Code Examples
3. Call TABLESPACE_REBUILD_QUOTAS to fix up quotas.
Scenario 2: Dropping a Corrupted Segment
You cannot drop a segment because the bitmap has segment blocks marked "free". The
system has automatically marked the segment corrupted. In this scenario, perform the
following tasks:
1. Call the SEGMENT_VERIFY procedure with the SEGMENT_VERIFY_EXTENTS_GLOBAL option.
If no overlaps are reported, then proceed with steps 2 through 5.
2. Call the SEGMENT_DUMP procedure to dump the DBA ranges allocated to the segment.
3. For each range, call TABLESPACE_FIX_BITMAPS with the
TABLESPACE_EXTENT_MAKE_FREE option to mark the space as free.
4. Call SEGMENT_DROP_CORRUPT to drop the SEG$ entry.
5. Call TABLESPACE_REBUILD_QUOTAS to fix up quotas.
Scenario 3: Fixing Bitmap Where Overlap is Reported
The TABLESPACE_VERIFY procedure reports some overlapping. Some of the real data must be
sacrificed based on previous internal errors. After choosing the object to be sacrificed, in this
case say, table t1, perform the following tasks:
1. Make a list of all objects that t1 overlaps.
2. Drop table t1. If necessary, follow up by calling the SEGMENT_DROP_CORRUPT procedure.
3. Call the SEGMENT_VERIFY procedure on all objects that t1 overlapped. If necessary, call
the TABLESPACE_FIX_BITMAPS procedure to mark appropriate bitmap blocks as used.
4. Rerun the TABLESPACE_VERIFY procedure to verify the problem is resolved.
Scenario 4: Correcting Media Corruption of Bitmap Blocks
A set of bitmap blocks has media corruption. In this scenario, perform the following tasks:
1. Call the TABLESPACE_REBUILD_BITMAPS procedure, either on all bitmap
blocks, or on a single block if only one is corrupt.
2. Call the TABLESPACE_REBUILD_QUOTAS procedure to rebuild quotas.
3. Call the TABLESPACE_VERIFY procedure to verify that the bitmaps are consistent.
Transporting Tablespaces Between Databases
Limitations on Transportable Tablespace Use
 The source and target database must use the same character set and national character
set.
 Objects with underlying objects (such as materialized views) or contained objects (such as
partitioned tables) are not transportable unless all of the underlying or contained objects
are in the tablespace set.
 You cannot transport the SYSTEM tablespace or objects owned by the user SYS. This
means that you cannot use TTS for PL/SQL, triggers, or views. These would have to be
moved with export.
 You cannot transport a table with a materialized view unless the mview is in the transport
set you create.
 You cannot transport a partition of a table without transporting the entire table.

1. Check endian format of both platforms.
For cross-platform transport, check the endian format of both platforms by querying the
V$TRANSPORTABLE_PLATFORM view.
You can find out your own platform name:
select platform_name from v$database
2. Pick a self-contained set of tablespaces.
The following statement can be used to determine whether tablespaces sales_1 and sales_2
are self-contained, with referential integrity constraints taken into consideration:
DBMS_TTS.TRANSPORT_SET_CHECK( TS_LIST =>'sales_1,sales_2', INCL_CONSTRAINTS
=>TRUE, FULL_CHECK =>TRUE)
Note: You must have been granted the EXECUTE_CATALOG_ROLE role (initially signed to SYS)
to execute this procedure.
You can see all violations by selecting from the TRANSPORT_SET_VIOLATIONS view. If the set
of tablespaces is self-contained, this view is empty.
3. Generate a transportable tablespace set.
3.1.Make all tablespaces in the set you are copying read-only.
3.2.Export the metadata describing the objects in the tablespace(s)
EXPDP system/password DUMPFILE=expdat.dmp DIRECTORY=dpump_dir
TRANSPORT_TABLESPACES = sales_1,sales_2
TRANSPORT_FULL_CHECK=Y
3.3.If you want to convert the tablespaces in the source database, use the RMAN
RMAN TARGET /
CONVERT TABLESPACE sales_1,sales_2
TO PLATFORM 'Microsoft Windows NT'
FORMAT '/temp/%U'
4. Transport the tablespace set.
Transport both the datafiles and the export file of the tablespaces to a place accessible to
the target database.
5. Convert tablespace set, if required, in the destination database.
Use RMAN as follows:
RMAN> CONVERT DATAFILE
'/hq/finance/work/tru/tbs_31.f',
'/hq/finance/work/tru/tbs_32.f',
'/hq/finance/work/tru/tbs_41.f'
TO PLATFORM="Solaris[tm] OE (32-bit)"
FROM PLATFORM="HP TRu64 UNIX"
DBFILE_NAME_CONVERT=
"/hq/finance/work/tru/", "/hq/finance/dbs/tru"
PARALLELISM=5
Note: The source and destination platforms are optional.
Note: By default, Oracle places the converted files in the Flash Recovery Area, without
changing the datafile names.
Note: If you have CLOB data on a small-endian system in an Oracle database version before
10g and with a varying-width character set and you are transporting to a database in a bigendian
system, the CLOB data must be converted in the destination database. RMAN does
not handle the conversion during the CONVERT phase. However, Oracle database
automatically handles the conversion while accessing the CLOB data.
If you want to eliminate this run-time conversion cost from this automatic conversion, you
can issue the CREATE TABLE AS SELECT command before accessing the data.
6. Plug in the tablespace.

IMPDP system/password DUMPFILE=expdat.dmp DIRECTORY=dpump_dir
TRANSPORT_DATAFILES=
/salesdb/sales_101.dbf,
/salesdb/sales_201.dbf
REMAP_SCHEMA=(dcranney:smith) REMAP_SCHEMA=(jfee:williams)
If required, put the tablespace into READ WRITE mode.
Using Transportable Tablespaces: Scenarios
Transporting and Attaching Partitions for Data Warehousing
1. In a staging database, you create a new tablespace and make it contain the table you
want to transport. It should have the same columns as the destination partitioned table.
2. Create an index on the same columns as the local index in the partitioned table.
3. Transport the tablespace to the data warehouse.
4. In the data warehouse, add a partition to the table.
ALTER TABLE sales ADD PARTITION jul98 VALUES LESS THAN (1998, 8, 1)
5. Attach the transported table to the partitioned table by exchanging it with the new
partition:
ALTER TABLE sales EXCHANGE PARTITION jul98 WITH TABLE jul_sales
INCLUDING INDEXES WITHOUT VALIDATION
Publishing Structured Data on CDs
A data provider can load a tablespace with data to be published, generate the transportable
set, and copy the transportable set to a CD. When customers receive this CD, they can plug
it into an existing database without having to copy the datafiles from the CD to disk storage.
Note: In this case, it is highly recommended to set the READ_ONLY_OPEN_DELAYED
initialization parameter to TRUE.
Moving Databases Across Platforms Using Transportable Tablespaces
You can use the transportable tablespace feature to migrate a database to a different
platform.
However, you cannot transport the SYSTEM tablespace. Therefore, objects such as
sequences, PL/SQL packages, and other objects that depend on the SYSTEM tablespace are
not transported. You must either create these objects manually on the destination database,
or use Data Pump to transport the objects that are not moved by transportable tablespace.