General Database Admin

General Database Admin

Managing Oracle Database Physical Structure
Managing Control Files
Obtaining Control File information
select value from v$parameter where upper(name)= 'CONTROL_FILES'
select * from v$controlfile;
select * from v$controlfile_record_section
Creating Additional Copies, Renaming, and Relocating Control Files
1. Shut down the database.
2. Copy an existing control file to a new location, using operating system commands.
3. Edit the CONTROL_FILES parameter
CONTROL_FILES="D:\ORACLE\ORADATA\ORA11G2\CONTROL01.CTL","D:\ORACLE\ORADATA\ORA1
1G2\CONTROL02.CTL";
4. Restart the database.
Note: if you are using SPFILE, STARTUP NOMOUNT then use ALTER SYSTEM SET ..
SCOPE=SPFILE command.
Creating New Control Files
1. Make a list of all datafiles and redo log files of the database.
2. Shut down the database.
3. Back up all datafiles and redo log files of the database.
4. STARTUP NOMOUNT
5. Use the CREATE CONTROLFILE statement:
CREATE CONTROLFILE
SET DATABASE prod
LOGFILE GROUP 1 ('/u01/oracle/prod/redo01_01.log',
'/u01/oracle/prod/redo01_02.log'),
GROUP 2 ('/u01/oracle/prod/redo02_01.log',
'/u01/oracle/prod/redo02_02.log'),
GROUP 3 ('/u01/oracle/prod/redo03_01.log',
'/u01/oracle/prod/redo03_02.log')
RESETLOGS | NORESETLOGS
DATAFILE '/u01/oracle/prod/system01.dbf' SIZE 3M,
'/u01/oracle/prod/rbs01.dbs' SIZE 5M,
'/u01/oracle/prod/users01.dbs' SIZE 5M,
'/u01/oracle/prod/temp01.dbs' SIZE 5M
MAXLOGFILES 50
MAXLOGMEMBERS 3
MAXLOGHISTORY 400
MAXDATAFILES 200
MAXINSTANCES 6
ARCHIVELOG;
Specify the RESETLOGS clause if you have lost any redo log groups in addition to control files.
In this case, you will need to recover from the loss of the redo logs (step 8). You must specify
the RESETLOGS clause if you have renamed the database. Otherwise, select the
NORESETLOGS clause.
Page 75 Oracle DBA Code Examples
Caution: The CREATE CONTROLFILE statement can potentially damage specified datafiles and
redo log files. Omitting a filename can cause loss of the data in that file, or loss of access to
the entire database.
6. Store a backup of the new control file on an offline storage device.
7. Edit the CONTROL_FILES initialization parameter
8. If you are renaming the database, edit the DB_NAME parameter in your instance parameter
file.
9. Recover the database if necessary.
• If the new control file was created using the NORESETLOGS clause, you can recover the
database with complete, closed database recovery.
• If the new control file was created using the RESETLOGS clause, you must specify USING
BACKUP CONTROL FILE in your RECOVER command.
10. If you did not perform recovery, open the database normally.
ALTER DATABASE OPEN;
If you specified RESETLOGS when creating the control file:
ALTER DATABASE OPEN RESETLOGS;
Handling Errors During CREATE CONTROLFILE
If Oracle Database sends you an error (usually error ORA-01173, ORA-01176, ORA-01177,
ORA-01215, or ORA-01216) when you attempt to mount and open the database after creating
a new control file, the most likely cause is that you omitted a file from the CREATE
CONTROLFILE statement or included one that should not have been listed.
Checking for Missing Files after Creating Control Files
Check the alert log to see if the database has detected inconsistencies between the data
dictionary and the control file.
• If a datafile exists in the data dictionary but not in the new control file, the database creates
a placeholder entry in the control file under the name MISSINGnnnn, where nnnn is the file
number in decimal. MISSINGnnnn is flagged in the control file as being offline and requiring
media recovery.
o If the actual datafile corresponding to MISSINGnnnn is read-only or offline normal,
then you can make the datafile accessible by renaming MISSINGnnnn to the name
of the actual datafile.
o If MISSINGnnnn corresponds to a datafile that was not read-only or offline normal,
you must drop the tablespace containing the datafile.
Backing Up Control Files
-- copy of the control file:
ALTER DATABASE BACKUP CONTROLFILE TO '/oracle/backup/control.bkp';
-- commands to re-create the control file:
ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
Show parameter user_dump_dest

Manage the Size of Control Files
It is affected by MAXDATAFILES, MAXLOGFILES, MAXLOGMEMBERS, MAXLOGHISTORY, and
MAXINSTANCES parameters in the CREATE DATABASE statement. Also it is affected by
CONTROL_FILE_RECORD_KEEP_TIME
Page 76 Oracle DBA Code Examples
Multiplexing the Control File
1. Alter the SPFILE:
 ALTER SYSTEM SET control_files
 ='$HOME/ORADATA/u01/ctrl01.ctl','$HOME/ORADATA/u02/ctrl02.ctl' SCOPE=SPFILE;
2. Shut down the database
3. Create additional control files:
 cp $HOME/ORADATA/u01/ctrl01.ctl $HOME/ORADATA/u02/ctrl02.ctl
4. Start the database:
startup
Maintaining Online Redo Log Files
Forcing Log Switches and Checkpoints
# Forcing a log switch
ALTER SYSTEM SWITCH LOGFILE;
# forcing checkpoints in seconds
FAST_START_MTTR_TARGET = 600
ALTER SYSTEM CHECKPOINT;
Adding Online Redo Log File Groups
# GROUP n is optional
ALTER DATABASE ADD LOGFILE GROUP 3
('/ORADATA/u01/log3a.rdo', '/ORADATA/u02/log3b.rdo')
SIZE 1M;
Adding Online Redo Log File Members
ALTER DATABASE ADD LOGFILE MEMBER
'/ORADATA/u04/log1c.rdo' TO GROUP 1,
'/ORADATA/u04/log2c.rdo' TO GROUP 2,
'/ORADATA/u04/log3c.rdo' TO GROUP 3;
Dropping Online Redo Log File Groups
SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG;
ALTER SYSTEM SWITCH LOGFILE;
ALTER DATABASE DROP LOGFILE GROUP 3;
Dropping Online Redo Log File Members
ALTER DATABASE DROP LOGFILE MEMBER '$HOME/ORADATA/u04/log3c.rdo';
Relocating and Renaming Redo Log Members
SHUTDOWN
Copy the redo log files to the new location.
STARTUP MOUNT
Page 77 Oracle DBA Code Examples
ALTER DATABASE
RENAME FILE '/diska/logs/log1a.rdo', '/diska/logs/log2a.rdo'
TO '/diskc/logs/log1c.rdo', '/diskc/logs/log2c.rdo';
ALTER DATABASE OPEN;
Verifying Blocks in Redo Log Files
# it defaults to TURE
alter system set DB_BLOCK_CHECKSUM=true ;
Clearing a Redo Log File
# if DB stops becuase log file is corrupted
ALTER DATABASE CLEAR LOGFILE GROUP 2;
# the redo log file will be available even if not archived
ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 2;
Viewing Redo Log Information
SELECT * FROM V$LOG;
# STATUS: INVALID (inaccessible), STALE (incomplete), DELETED, Blank (in use)
SELECT * FROM V$LOGFILE;
SELECT * FROM V$LOG G, V$LOGFILE M where G.GROUP#=M.GROUP# order by M.GROUP#
Managing Archived Redo Logs
Obtaining Information about Archive Log
-- in SQL*Plus
ARCHIVE LOG LIST
-- to know archivelog mode of the database
select LOG_MODE from V$DATABASE
-- historical archived log information from the control file
select
 RECID,
 NAME,
 DEST_ID,
 THREAD#,
 SEQUENCE#,
 RESETLOGS_CHANGE#,
 RESETLOGS_TIME,
 RESETLOGS_ID,
 FIRST_CHANGE#,
 FIRST_TIME,
 NEXT_CHANGE#,
 NEXT_TIME,
 BLOCKS * BLOCK_SIZE/1024/1024 MB ,
 CREATOR,
 ARCHIVED,
 DELETED,
 STATUS,
 COMPLETION_TIME,
Page 78 Oracle DBA Code Examples
 END_OF_REDO,
 BACKUP_COUNT,
 ARCHIVAL_THREAD#,
 IS_RECOVERY_DEST_FILE,
 COMPRESSED,
 FAL,
 BACKED_BY_VSS
from V$ARCHIVED_LOG;
-- information about archive log destinations and their status
select * from V$ARCHIVE_DEST
-- information about ARCn processes
select * from V$ARCHIVE_PROCESSES
-- information about any backup made on archived log files
select * from V$BACKUP_REDOLOG
-- online groups and which one to be archived
select * from V$LOG
-- log history information
select * from V$LOG_HISTORY
Changing the Database Archiving Mode
select log_mode from v$database ;
CONN / AS SYSDBA
SHUTDOWN
Back up the database
see Specifying Archive Destinations ( next section )
STARTUP MOUNT
ALTER DATABASE ARCHIVELOG; -- or NOARCHIVELOG
ALTER DATABASE OPEN;
SHUTDOWN IMMEDIATE
Back up the database
Specifying Archive Destinations and their Options
-- to local destinations
LOG_ARCHIVE_DEST_1 = 'LOCATION=/disk1/archive'
LOG_ARCHIVE_DEST_1 = 'LOCATION=USE_DB_RECOVERY_FILE_DEST'
-- default is OPTIONAL, REOPEN in seconds (default 300)
-- if REOPEN is omitted, ARCn will never open a destination after a failure
LOG_ARCHIVE_DEST_2 = 'LOCATION=/disk2/archive MANDATORY REOPEN=600'
LOG_ARCHIVE_DEST_3 = 'LOCATION=/disk3/archive OPTIONAL'
-- to a standby db
LOG_ARCHIVE_DEST_4 = 'SERVICE = standby1'
-- control file format
Page 79 Oracle DBA Code Examples
LOG_ARCHIVE_FORMAT=t%t_s%s_r%r.arc
Specifying the Minimum Number of Successful Destinations
alter system set LOG_ARCHIVE_MIN_SUCCEED_DEST=1
Controlling Archiving to a Destination
alter system set LOG_ARCHIVE_DEST_STATE_2 = DEFER
alter system set LOG_ARCHIVE_DEST_STATE_2 = ENABLE
Controlling Trace Output Generated by the Archivelog Process
LOG_ARCHIVE_TRACE takes combination of:
0 Disable archivelog tracing. This is the default.
1 Track archival of redo log file.
2 Track archival status for each archivelog destination.
4 Track archival operational phase.
8 Track archivelog destination activity.
16 Track detailed archivelog destination activity.
32 Track archivelog destination parameter modifications.
64 Track ARCn process state activity.
128 Track FAL (fetch archived log) server related activities.
256 Supported in a future release.
512 Tracks asynchronous LGWR activity.
1024 RFS physical client tracking.
2048 ARCn/RFS heartbeat tracking.
4096 Track real-time apply
-- LOG_ARCHIVE_TRACE defaults to 0
select value from v$parameter where upper(name)='LOG_ARCHIVE_TRACE';
-- database must be mounted but not open.
ALTER SYSTEM SET LOG_ARCHIVE_TRACE=12;
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,
Page 81 Oracle DBA Code Examples
 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
Page 82 Oracle DBA Code Examples
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')
Page 83 Oracle DBA Code Examples
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;
Page 84 Oracle DBA Code Examples
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;
Page 85 Oracle DBA Code Examples
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
Page 86 Oracle DBA Code Examples
 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
Page 89 Oracle DBA Code Examples
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 ;
Page 90 Oracle DBA Code Examples
 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
Page 91 Oracle DBA Code Examples
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.
Page 93 Oracle DBA Code Examples
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.
Page 94 Oracle DBA Code Examples
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.
Managing Alert Thresholds
Getting the Current Threshold Setting
 List of supported metrics can be found in the documentation "PL/SQL Packages and Types
Reference": 10g, 11g or link or from the query below:
SELECT METRIC_ID, METRIC_NAME, METRIC_UNIT,
GROUP_ID, GROUP_NAME
FROM V$METRICNAME
ORDER BY METRIC_NAME
-- current threshold settings
select * from DBA_THRESHOLDS;
set serveroutput on
DECLARE
 V_WOPERATOR BINARY_INTEGER;
 V_WVALUE VARCHAR2(50);
Page 95 Oracle DBA Code Examples
 V_COPERATOR BINARY_INTEGER;
 V_CVALUE VARCHAR2(50);
 V_OBS_PERIOD BINARY_INTEGER;
 V_CON_PERIOD BINARY_INTEGER;
 FUNCTION GET_OPERATOR_NAME( P_OPER IN BINARY_INTEGER) RETURN VARCHAR2
 IS
 BEGIN
 IF P_OPER =0 THEN
 RETURN 'GT';
 ELSIF P_OPER =1 THEN
 RETURN 'EQ';
 ELSIF P_OPER =2 THEN
 RETURN 'LT';
 ELSIF P_OPER =3 THEN
 RETURN 'LE';
 ELSIF P_OPER =4 THEN
 RETURN 'GE';
 ELSIF P_OPER =5 THEN
 RETURN 'OPERATOR_CONTAINS';
 ELSIF P_OPER =6 THEN
 RETURN 'NE';
 ELSIF P_OPER =7 THEN
 RETURN 'OPERATOR_DO_NOT_CHECK';
 END IF;
 END GET_OPERATOR_NAME;
BEGIN
DBMS_SERVER_ALERT.GET_THRESHOLD(
 metrics_id =>DBMS_SERVER_ALERT.TABLESPACE_PCT_FULL,
 warning_operator =>V_WOPERATOR ,
 warning_value =>V_WVALUE,
 critical_operator =>V_COPERATOR,
 critical_value =>V_CVALUE,
 observation_period =>V_OBS_PERIOD,
 consecutive_occurrences =>V_CON_PERIOD,
 instance_name =>NULL,
 object_type => DBMS_SERVER_ALERT.OBJECT_TYPE_TABLESPACE,
 object_name =>NULL);
 DBMS_OUTPUT.PUT_LINE('Warning Op.: '|| GET_OPERATOR_NAME(V_WOPERATOR));
 DBMS_OUTPUT.PUT_LINE('Warning Val: '||V_WVALUE);
 DBMS_OUTPUT.PUT_LINE('Critical Op.: '|| GET_OPERATOR_NAME(V_COPERATOR));
 DBMS_OUTPUT.PUT_LINE('Critical Val: '||V_CVALUE);
EXCEPTION
 WHEN OTHERS THEN
 IF SQLCODE='-13799' THEN
 DBMS_OUTPUT.PUT_LINE('No threshold was found with the specified threshold
key.(ORA-13799)');
 ELSE
 RAISE;
 END IF;
END;
/
Setting Tablespace Alert Thresholds
 warning_operator takes one of the following (not all applicable for all metrics):
o OPERATOR_EQ GE GT LE LT NE
o OPERATOR_CONTAINS
Page 96 Oracle DBA Code Examples
o OPERATOR_DO_NOT_CHECK (disables the alert for the specified metric)
-- set the free-space-remaining thresholds in the USERS tablespace to 10 MB
(warning)
-- and 2 MB (critical), and disable the percent-full thresholds.
BEGIN
DBMS_SERVER_ALERT.SET_THRESHOLD(
 metrics_id => DBMS_SERVER_ALERT.TABLESPACE_BYT_FREE, -- Tablespace FREE
space in KB
 warning_operator => DBMS_SERVER_ALERT.OPERATOR_LT, -- GT is not applicable
here
 warning_value => '10240',
 critical_operator => DBMS_SERVER_ALERT.OPERATOR_LT,
 critical_value => '2048',
 observation_period => 1, -- computation period (1-60 min)
 consecutive_occurrences => 1, -- violation times before alert
 instance_name => NULL, -- NULL= 'database_wide'. Passed value is not checked
 object_type => DBMS_SERVER_ALERT.OBJECT_TYPE_TABLESPACE,
 object_name => 'USERS'); -- if NULL -> All Tablespaces
DBMS_SERVER_ALERT.SET_THRESHOLD(
 metrics_id => DBMS_SERVER_ALERT.TABLESPACE_PCT_FULL, -- tablespace USAGE by
%
 warning_operator => DBMS_SERVER_ALERT.OPERATOR_DO_NOT_CHECK,
 warning_value => '0',
 critical_operator => DBMS_SERVER_ALERT.OPERATOR_DO_NOT_CHECK,
 critical_value => '0',
 observation_period => 1,
 consecutive_occurrences => 1,
 instance_name => NULL,
 object_type => DBMS_SERVER_ALERT.OBJECT_TYPE_TABLESPACE,
 object_name => 'USERS');
END;
/
SELECT *
FROM dba_thresholds
where object_name = 'USERS' and object_type='TABLESPACE';
Restoring a Tablespace to Database Default Thresholds
 You can restore the metric threshold values to revert to the database defaults by setting
them to NULL in the DBMS_SERVER_ALERT.SET_THRESHOLD.
BEGIN
DBMS_SERVER_ALERT.SET_THRESHOLD(
 metrics_id => DBMS_SERVER_ALERT.TABLESPACE_PCT_FULL, -- tablespace USAGE by
%
 warning_operator => NULL, -- do not use ''
 warning_value => NULL,
 critical_operator => NULL,
 critical_value => NULL,
 observation_period => 1,
 consecutive_occurrences => 1,
 instance_name => NULL,
 object_type => DBMS_SERVER_ALERT.OBJECT_TYPE_TABLESPACE,
 object_name => 'USERS');
END;
/
Page 97 Oracle DBA Code Examples
SELECT *
FROM dba_thresholds
where object_name = 'USERS' and object_type='TABLESPACE';
Modifying Database Default Thresholds
 set the object_name to NULL
BEGIN
DBMS_SERVER_ALERT.SET_THRESHOLD(
 metrics_id => DBMS_SERVER_ALERT.TABLESPACE_PCT_FULL, -- redo with
TABLESPACE_BYT_FREE
 warning_operator => DBMS_SERVER_ALERT.OPERATOR_GT,
 warning_value => '80',
 critical_operator => DBMS_SERVER_ALERT.OPERATOR_GT,
 critical_value => '92',
 observation_period => 1,
 consecutive_occurrences => 1,
 instance_name => NULL,
 object_type => DBMS_SERVER_ALERT.OBJECT_TYPE_TABLESPACE,
 object_name => NULL);
END;
/
SELECT *
FROM dba_thresholds
where object_name = 'USERS' and object_type='TABLESPACE';
Viewing Alerts
-- outstanding alerts (to be cleared)
SELECT
 SEQUENCE_ID,OWNER,
 OBJECT_NAME,OBJECT_TYPE,SUBOBJECT_NAME,
 REASON_ID, REASON,TIME_SUGGESTED,
 SUGGESTED_ACTION,ADVISOR_NAME,METRIC_VALUE,
 MESSAGE_TYPE,MESSAGE_GROUP,MESSAGE_LEVEL,
 HOSTING_CLIENT_ID,MODULE_ID,PROCESS_ID,
 HOST_ID,HOST_NW_ADDR,INSTANCE_NAME,
 INSTANCE_NUMBER,USER_ID,EXECUTION_CONTEXT_ID,CREATION_TIME
FROM DBA_OUTSTANDING_ALERTS
-- history of alerts that have been cleared
SELECT
 SEQUENCE_ID,OWNER,
 OBJECT_NAME,SUBOBJECT_NAME,OBJECT_TYPE,
 REASON_ID, REASON,TIME_SUGGESTED,
 SUGGESTED_ACTION,ADVISOR_NAME,METRIC_VALUE,
 MESSAGE_TYPE,MESSAGE_GROUP,MESSAGE_LEVEL,
 HOSTING_CLIENT_ID,MODULE_ID,PROCESS_ID,
 HOST_ID,HOST_NW_ADDR,INSTANCE_NAME,INSTANCE_NUMBER,
 USER_ID,EXECUTION_CONTEXT_ID,CREATION_TIME
FROM DBA_ALERT_HISTORY
ORDER BY SEQUENCE_ID
-- list of all metrics
SELECT METRIC_ID, METRIC_NAME, METRIC_UNIT,GROUP_ID, GROUP_NAME
FROM V$METRICNAME
Page 98 Oracle DBA Code Examples
ORDER BY METRIC_NAME
-- system-level metric values in memory
SELECT BEGIN_TIME,END_TIME,INTSIZE_CSEC,
 GROUP_ID,ENTITY_ID,ENTITY_SEQUENCE,
 METRIC_ID,METRIC_NAME,VALUE,METRIC_UNIT
FROM V$METRIC –- also V$METRIC_HISTORY
ORDER BY BEGIN_TIME, VALUE DESC
-- alert types
select
INST_ID,REASON_ID,OBJECT_TYPE,TYPE,GROUP_NAME,SCOPE,INTERNAL_METRIC_CATEGORY,
INTERNAL_METRIC_NAME
from GV$ALERT_TYPES
order by OBJECT_TYPE,TYPE
Managing Datafiles and Tempfiles
Creating Datafiles
CREATE TABLESPACE
CREATE TEMPORARY TABLESPACE ge 8-9
ALTER TABLESPACE ... ADD DATAFILE
ALTER TABLESPACE ... ADD TEMPFILE ge 8-9
CREATE DATABASE
ALTER DATABASE ... CREATE DATAFILE
Enabling and Disabling Automatic Extension for a Datafile
ALTER TABLESPACE users
ADD DATAFILE '/u02/oracle/rbdb1/users03.dbf' SIZE 10M
AUTOEXTEND ON
NEXT 512K
MAXSIZE 250M;
Manually Resizing a Datafile
-- if there is space in the datafile
ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/stuff01.dbf' RESIZE 100M;
Bringing Datafiles Online or Taking Offline in ARCHIVELOG Mode
ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/stuff01.dbf' ONLINE;
ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/stuff01.dbf' OFFLINE;
Bringing Datafiles Online or Taking Offline in ARCHIVELOG Mode
ALTER DATABASE DATAFILE ... {ONLINE|OFFLINE}
-- all datafiles will be affected in the following code
-- this is different from ALTER TABLESPACE...ONLINE|OFFLINE which controls
tablespace availability
ALTER TABLESPACE ... DATAFILE {ONLINE|OFFLINE}
ALTER TABLESPACE ... TEMPFILE {ONLINE|OFFLINE}
Taking Datafiles Offline in NOARCHIVELOG Mode
 Use it when you want to drop the datafile.
Page 99 Oracle DBA Code Examples
-- datafile cannot be brought ONLINE again
ALTER DATABASE DATAFILE ... OFFLINE FOR DROP;
-- the datafile MUST then be dropped
-- for dictionary managed tablespace
ALTER TABLESPACE ... DROP DATAFILE
DROP TABLESPACE ... INCLUDING CONTENTS AND DATAFILES
Renaming and Relocating Datafiles in a Single Tablespace
ALTER TABLESPACE users OFFLINE NORMAL;
Copy the datafiles to their new locations and rename them using the operating
system.
ALTER TABLESPACE users RENAME DATAFILE
 '/u02/oracle/rbdb1/user1.dbf',
 '/u02/oracle/rbdb1/user2.dbf'
 TO
 '/u02/oracle/rbdb1/users01.dbf',
 '/u02/oracle/rbdb1/users02.dbf';
-- for system, default temporary, or undo
-- ALTER TABLESPACE cannot be used because you cannot take them OFFLINE
mount the database
ALTER DATABASE RENAME FILE '/u02/oracle/rbdb1/sort01.dbf',
 '/u02/oracle/rbdb1/user3.dbf'
TO '/u02/oracle/rbdb1/temp01.dbf', '/u02/oracle/rbdb1/users03.dbf;'
Back up the database.
Dropping Datafiles
 The following restrictions apply:
o The database must be open.
o The datafile must be empty, otherwise use drop the tablespace.
o You cannot drop datafiles in a read-only tablespace.
o You cannot drop datafiles in the SYSTEM tablespace.
o If a datafile in a locally managed tablespace is offline, it cannot be dropped.
ALTER TABLESPACE example DROP DATAFILE ...
ALTER TABLESPACE lmtemp DROP TEMPFILE
ALTER DATABASE TEMPFILE .. DROP INCLUDING DATAFILES
Copying a File on a Local File System
 The copied file must meet the following requirements:
o The size must be a multiple of 512 bytes.
o The size must be less than or equal to two terabytes.
 Be aware not to coy a file that is being used by a process.
 If you are copying a database datafile, make it READ ONLY before you start to copy.

CREATE DIRECTORY SOURCE_DIR AS '/usr/admin/source';
CREATE DIRECTORY DEST_DIR AS '/usr/admin/destination';
GRANT READ ON DIRECTORY source_dir TO strmadmin;
GRANT WRITE ON DIRECTORY dest_dir TO strmadmin;
CONNECT strmadmin/strmadminpw
BEGIN
DBMS_FILE_TRANSFER.COPY_FILE(
 source_directory_object => 'SOURCE_DIR',
 source_file_name => 'db1.dat',
Page 100 Oracle DBA Code Examples
 destination_directory_object => 'DEST_DIR',
 destination_file_name => 'db1_copy.dat');
END;
Transferring a File to a Different Database
 In order to transfer a file the other way around, you must replace the PUT_FILE
procedure with the GET_FILE procedure.
 If you are copying a database datafile, make it READ ONLY before you start to copy.
 You can monitor copying progress using V$SESSION_LONGOPS view.
CREATE DATABASE LINK ODB
 CONNECT TO system IDENTIFIED BY system_passwd USING 'prod1';
BEGIN
DBMS_FILE_TRANSFER.PUT_FILE(
SOURCE_DIRECTORY_OBJECT => 'SOURCE_DIR',
SOURCE_FILE_NAME => 'mydata1.dbf',
DESTINATION_DIRECTORY_OBJECT => 'DEST_DIR',
DESTINATION_FILE_NAME => 'mydata2.dbf'
DESTINATION_DATABASE => 'ODB.ACME.COM');
END;
/
BEGIN
DBMS_FILE_TRANSFER.GET_FILE(
 SOURCE_DIRECTORY_OBJECT => 'SOURCE_DIR',
 SOURCE_FILE_NAME => 'TEST01.DBF',
 SOURCE_DATABASE => 'ODB.ACME.COM',
 DESTINATION_DIRECTORY_OBJECT => 'DEST_DIR',
 DESTINATION_FILE_NAME => 'TEST01.DBF');
END;
/
Dumping a Data Block
ALTER SYSTEM DUMP DATAFILE 3 BLOCK 3281;
-- To dump a number of consecutive blocks::
ALTER SYSTEM DUMP DATAFILE 5 BLOCK MIN 42 BLOCK MAX 50;
select value from v$parameter where name='user_dump_dest';
/u01/app/oracle/admin/pasu/udump/pasu_ora_29673.trc
/* to dump index blocks */
-- get object id of the index:
SELECT object_id FROM dba_objects WHERE object_name = 'MYINDEX';
-- do a treedump of the index:
ALTER SESSION SET EVENTS 'immediate trace name treedump level 106315';
 index height
 distinct index blocks in the lower level |
 RBA block at position zero | |
 | | | |
 branch: 0×1c3588a 29579402 (0: nrow: 222, level: 1)
 number of entries
 lead block number (starts from -1) | non-deleted entries
 | | |
Page 101 Oracle DBA Code Examples
 leaf: 0×1c3588b 29579403 (-1: nrow: 485 rrow: 485)
 leaf: 0×1c3588c 29579404 (0: nrow: 479 rrow: 479)
 leaf: 0×1c3588d 29579405 (1: nrow: 479 rrow: 479)
 leaf: 0×1c3588e 29579406 (2: nrow: 479 rrow: 479)
-- define the RBA of the block to dump then
-- get its file# and block#:
SELECT DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(223456765),
 DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(223456765)
 FROM dual;
-- dump the header block
ALTER SYSTEM DUMP DATAFILE 7 BLOCK 328745;
-- also, you can find the root block ( it is the block following the header bloc):
SELECT header_file, header_block+1 FROM dba_segments WHERE segment_name='MYINDEX';
Managing Undo Tablespaces
Obtaining Information on Undo
SHOW PARAMETER UNDO
NAME TYPE VALUE
----------------- ------- -------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS01
-- undo usage in 10-min-intervals for last 7 days
select TO_CHAR(BEGIN_TIME, 'MM/DD/YYYY HH24:MI:SS') BEGIN_TIME,
 TO_CHAR(END_TIME, 'MM/DD/YYYY HH24:MI:SS') END_TIME,
 UNDOBLKS UNDO_BLOCKS,
 MAXQUERYLEN MAX_QUERY_LENGTH_INSEC,
 MAXQUERYID LONGEST_QUERY_ID,
 TXNCOUNT TOTAL_TRANSACTIONS,
 MAXCONCURRENCY
 from V$UNDOSTAT
order by BEGIN_TIME DESC
-- stats history of V$UNDOSTAT
select
 TO_CHAR(BEGIN_TIME, 'MM/DD/YYYY HH24:MI:SS') BEGIN_TIME,
 TO_CHAR(END_TIME, 'MM/DD/YYYY HH24:MI:SS') END_TIME,
 INSTANCE_NUMBER,SNAP_ID,
 UNDOBLKS,
 TXNCOUNT TOTAL_TRANSACTIONS,
 MAXQUERYLEN MAX_QUERY_LENGTH_INSEC,
 MAXQUERYSQLID,
 MAXCONCURRENCY,
 SSOLDERRCNT ORA01555_CNT,
 NOSPACEERRCNT NOSPACE_CNT,
 ACTIVEBLKS,UNEXPIREDBLKS
from DBA_HIST_UNDOSTAT
-- undo segments
select SEGMENT_NAME,OWNER,STATUS from DBA_ROLLBACK_SEGS;
Page 102 Oracle DBA Code Examples
-- undo extents
select
 e.SEGMENT_NAME,
 e.TABLESPACE_NAME,
 e.EXTENT_ID,
 e.FILE_ID,
 e.BLOCK_ID,
 e.BYTES/1024 SIZE_KB,
 e.RELATIVE_FNO,
 e.STATUS -- ACTIVE EXPIRED UNEXPIRED
from DBA_UNDO_EXTENTS e
order by e.STATUS ASC
-- undo sizes by STATUS
select
 e.TABLESPACE_NAME,
 e.STATUS,
 to_char(sum(e.BYTES/1024),'999,999,999,999') SIZE_KB
from DBA_UNDO_EXTENTS e
group by e.TABLESPACE_NAME, e.STATUS
order by e.STATUS
-- undo sizes consumed by active transactions by username
SELECT s.username, sum(t.used_ublk) used_undo_blocks
from v$session s, v$transaction t
where s.saddr = t.ses_addr
 and t.status='ACTIVE'
group by s.username
order by s.username
Enabling Automatic Undo Management
select value from v$parameter where upper(name)='UNDO_MANAGEMENT';
alter system set UNDO_MANAGEMENT=AUTO scope=spfile ;
Creating an Undo Tablespace
CREATE UNDO TABLESPACE undotbs2
DATAFILE '/u01/oracle/rbdb1/undo0201.dbf' SIZE 200M REUSE AUTOEXTEND ON;
Setting Startup Undo Tablespace
select value from v$parameter where upper(name)='UNDO_TABLESPACE';
-- if there are multiple undo tablespaces
-- after all active transactions have committed, the undo tablespace automatically
-- goes from the PENDING OFFLINE mode to the OFFLINE mode.
alter system set UNDO_TABLESPACE='UNDOTBS1';
-- the following switches out current undo tablespace to the next available one
alter system set UNDO_TABLESPACE = '';
Tuning Undo Retention
 If the undo tablespace is configured with the AUTOEXTEND option, undo retention tuning
is slightly different. In this case, the database tunes the undo retention period to be
slightly longer than the longest-running query on the system at that time.
 For a fixed size undo tablespace, the database tunes the undo retention period based on
85% of the tablespace size, or on the warning alert threshold percentage for space used,
whichever is lower.
 If you want a fixed undo size, use the Undo Advisor to specify the proper undo size for
your requirement. You should estimate:
Page 103 Oracle DBA Code Examples
o The length of your expected longest running query
o The longest interval that you will require for flashback operations
-- determine the current retention period
select to_char(begin_time, 'DD-MON-RR HH24:MI') begin_time,
to_char(end_time, 'DD-MON-RR HH24:MI') end_time,
tuned_undoretention
from v$undostat order by end_time;
-- maximum query time
SELECT round(MAX(maxquerylen)/60) Minutes FROM v$undostat;
Using Undo Advisor
 Use OEM or PL/SQL
-- retreive available AWR snaps
select SNAP_ID, STARTUP_TIME, BEGIN_INTERVAL_TIME, END_INTERVAL_TIME
from DBA_HIST_SNAPSHOT
order by SNAP_ID;
set serveroutput on
DECLARE
 tid NUMBER; -- task ID
 tname VARCHAR2(30); -- task name
 oid NUMBER;
BEGIN
 DBMS_ADVISOR.CREATE_TASK('Undo Advisor', tid, tname, 'Undo Advisor Task');
 DBMS_ADVISOR.CREATE_OBJECT(tname, 'UNDO_TBS', null, null, null, null, oid);
 DBMS_ADVISOR.SET_TASK_PARAMETER(tname, 'TARGET_OBJECTS', oid);
 DBMS_ADVISOR.SET_TASK_PARAMETER(tname, 'START_SNAPSHOT', 52);
 DBMS_ADVISOR.SET_TASK_PARAMETER(tname, 'END_SNAPSHOT', 56);
 DBMS_ADVISOR.SET_TASK_PARAMETER(tname, 'INSTANCE', 1);
 DBMS_ADVISOR.execute_task(tname);
 DBMS_OUTPUT.PUT_LINE(tname);
end;
/
SELECT DBA_ADVISOR_RECOMMENDATIONS.GET_TASK_REPORT('TASKNAME')
FROM DUAL;
Setting the Undo Retention Period
 You must set UNDO_RETENTION parameter when:
o The undo tablespace has the AUTOEXTEND option enabled
o You want to set undo retention for LOBs
o You want retention guarantee
select value from v$parameter where upper(name)='UNDO_RETENTION';
alter system set UNDO_RETENTION = 2400;
Enabling Retention Guarantee
 Enabling retention guarantee can cause multiple DML operations to fail. Use with caution.
select RETENTION from DBA_TABLESPACES where TABLESPACE_NAME='UNDOTBS1';
create undo tablespace undotbs01 .. RETENTION GUARANTEE;
alter tablespace undotbs1 RETENTION GUARANTEE;
alter tablespace undotbs1 RETENTION NOGUARANTEE;
Dropping an Undo Tablespace
DROP TABLESPACE undotbs_01;
Page 104 Oracle DBA Code Examples
To Drop a Corrupt UNDO Tablespace
An Undo tablespace containing a corrupted undo rollback segment may lead to one or more of
the following:
 ORA-00376: file xx cannot be read at this time
 SIMON process being hang waiting for the event "wait for a undo record" forever!
Identify the bad rollback segment(s)(Oracle would report it anyway):
-- looking for NEEDS RECOVERY or PARTIALLY AVAILABLE
select
segment_name, status
from dba_rollback_segs
where tablespace_name='undotbs_corrupt'
and status not in ('OFFLINE', 'ONLINE');
-- Let's say it's _SYSSMU9$
-- (optionally) create a new undo tablespace
-- and replace it with the current one
CREATE UNDO TABLESPACE undotbs2
DATAFILE '/u01/oracle/rbdb1/undo0201.dbf' SIZE 200M REUSE AUTOEXTEND ON;
alter system set UNDO_TABLESPACE='UNDOTBS2';
-- Next, create pfile and in it:
undo_management = MANUAL
_offline_rollback_segments=_SYSSMU9$
-- shutdown the db
-- open it using the pfile
-- drop the offending rollback segment
-- (and the undo tablespace altogether if the other one was created)
-- Note: datafiles might not actually be deleted, check them
drop tablespace .. including contents and datafiles
-- bounce the db (make it start using the spfile)
-- this means it will go back to using the automatic undo
Using Oracle Managed Files (OMF)
 OMF datafiles have to be created in one directory
set DB_CREATE_FILE_DEST, DB_CREATE_ONLINE_LOG_DEST_n, and DB_RECOVERY_FILE_DEST
-- when OMF enabled
CREATE TABLESPACE finance01;
ALTER TABLESPACE finance01 ADD DATAFILE 500M;
DROP TABLESPACE finance01; -- dbf auto deleted
-- create db with OMF
db_name=mydb
DB_CREATE_FILE_DEST = '/u01/app/oracle/oradata'
DB_RECOVERY_FILE_DEST_SIZE = 100M
DB_RECOVERY_FILE_DEST = '/u04/app/oracle/oradata'
LOG_ARCHIVE_DEST_1 = 'LOCATION = USE_DB_RECOVERY_FILE_DEST'
SQL> connect sys/sys_passwd as sysdba
Connected to an idle instance.
Page 105 Oracle DBA Code Examples
SQL> STARTUP NOMOUNT PFILE='initmydb.ora';
SQL> CREATE DATABASE mydb;
Page 106 Oracle DBA Code Examples
Managing Schema Objects