Oracle - Tablespaces, Datafiles, ...

Contents

• Tablespaces
• Data Files
• Rollback Segments
• Undo and flashback queries
• Temporary Storage
• Miscellaneous Tips

Tablespaces

Locally-managed tablespace (not for SYSTEM tablespace):

CREATE TABLESPACE tablespace-name
  DATAFILE 'file.dbf' SIZE nM  [REUSE]
     [  ,  'file.dbf' SIZE nM]    
      -- If no size then the file exists and is being re-initialized
  EXTENT MANAGEMENT LOCAL
  [  AUTOALLOCATE -- Oracle manages extent size automatically
   | UNIFORM SIZE 128K ] ; -- All extents have the same size

Dictionary-managed tablespace:

CREATE TABLESPACE tablespace-name
  DATAFILE 'file.dbf' SIZE nM  [REUSE]
     [  ,  'file.dbf' SIZE nM]    
      -- If no size then the file exists and is being re-initialized
  [AUTOEXTEND OFF]
  [ MINIMUM EXTENT nM ]
   -- Minimum extent mostly used for large databases, to ensure that extents are large
  [ DEFAULT STORAGE   
         ([ INITIAL nM  ]               -- As large as potentially needed
          [ NEXT nM  ]    
          [ MINEXTENTS 1  ]          -- Best with one extent
          [ MAXEXTENTS n | UNLIMITED ]  -- Put a limit 
          [ PCTINCREASE 0 ]            
         ) ]
  [ PERMANENT | TEMPORARY ]          -- permanent is default
  [ ONLINE | OFFLINE ]  ;               -- Online is default of course

On block is reserved for the header.  For the size, nK can replace nM.

For locally managed tablespaces, note that certain options are incompatible.  AUTOEXTENT OFF works, but not the DEFAULT STORAGE (but that's logical). Replace the DEFAULT STORAGE with: EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

ALTER TABLESPACE tablespace ADD DATAFILE 'file' SIZE nM;

Add a file

ALTER TABLESPACE tablespace OFFLINE   [ NORMAL | TEMPORARY | IMMEDIATE ] ;

Normal is best

ALTER TABLESPACE tablespace ONLINE;
ALTER TABLESPACE tablespace { READ ONLY | READ WRITE } ;

Cannot create tables in read-only tablespace, but can drop existing tables.

DROP TABLESPACE tablespace [INCLUDING CONTENTS [CASCADE CONSTRAINTS]]; 

Best to put off-line first. After the drop, the datafile still exists

Creating a Read-Only Tablespace on a WORM Device

• Create a normal writable tablespace on a disk. Insert data.
• ALTER TABLESPACE tbsp READ ONLY; (automatic transitional read-only state until all transactions are complete).
• Copy the datafiles onto the WORM device
• ALTER TABLESPACE tbsp OFFLINE NORMAL;
• ALTER TABLESPACE tbsp RENAME DATAFILE '...' TO '...';
• ALTER TABLESPACE tbsp ONLINE;

Automatic free space management in segments:

CREATE TABLESPACE x DATAFILE 'y' SIZE nM
   EXTENT MANAGEMENT LOCAL
   SEGMENT SPACE MANAGEMENT AUTO;

Full Tablespace

When a tablespace is full, then two possibilities
(Tip: Get the current file name with "select tablespace_name, file_name from dba_data_files;"):

• Try to make it bigger (this may not work for the SYSTEM tablespace because it may not even have the temporary space to grow).
ALTER DATABASE [db] DATAFILE 'file name' RESIZE 30M; (see section datafiles below)
• Add a datafile (we don't usually use the autoextend on): 
ALTER TABLESPACE tablespace ADD DATAFILE 'file' SIZE nM [autoextend ON maxsize nM;];

column free_perc format 999
column mb      format 999G999
column used_mb format 999G999
column free_mb format 999G999
select substr(s.tablespace_name, 1, 18) as tblspace
     , decode (f.autoextensible ,
                   'NO', decode ( sign (s.total_blocks / f.blocks - 0.2), -1, '-->', ' '),
                         ' ') w --V8
     , f.autoextensible --V8
     , to_char ( (1 - s.total_blocks / f.blocks) * 100, '999' ) || '%' as used_perc
     , to_char ( s.total_blocks / f.blocks * 100, '999' ) || '%' as free_perc
     , decode ( sign (s.total_blocks / f.blocks - 0.2), -1, 'less than 20% free', ' ') as w2
     , f.bytes/1024/1024 AS MB
     , (f.bytes- s.total_bytes)/1024/1024 as used_mb
     , s.total_bytes/1024/1024 as free_mb
  from (select tablespace_name,
               count(*) as free_spaces, sum(blocks) as total_blocks, sum(bytes) as total_bytes
          from dba_free_space
          group by tablespace_name) s
     , (select tablespace_name
             , autoextensible
             , sum(blocks) as blocks
             , sum(bytes) as bytes
          from dba_data_files
          group by tablespace_name, autoextensible) f
  where s.tablespace_name = f.tablespace_name
  order by s.tablespace_name;

If needed, do ALTER DATABASE DATAFILE 'xyz' RESIZE nM;

Datafiles

DBA_DATA_FILES

View for data files

ALTER DATABASE CREATE DATAFILE ;

Create datafile

 

ALTER DATABASE [db] DATAFILE 'file name' RESIZE 30M;

Make the file larger

Ex: alter database datafile 'r:\oradata\bo01.ora' resize 100M;

 

ALTER DATABASE [db] TEMPFILE 'file name' . . .;

Manage the temporary files

 

 

ALTER TABLESPACE tablespace RENAME DATAFILE 'file1' TO 'file2'; 

Remember to put offline and copy the file first.  When db is mounted, use "alter database rename file". See below for example

ALTER DATABASE RENAME FILE '...a.dbf', '...b.dbf' TO 'new..../a.dbf', 'new..../b.dbf;

Mount database and copy the datafiles with OS commands before issuing statement.

ALTER DATABASE DATAFILE 'file1.dbf' OFFLINE;

Put data file off-line (only for archivelog mode)

ALTER DATABASE DATAFILE 'file' OFFLINE DROP;

Put off line for noarchivelog mode

Alter database datafile 'data file' offline drop;

Drop a datafile.

ALTER DATABASE RENAME FILE 'xyz', 'abc' TO 'wxy', 'bcd';

Rename a file

Example for renaming file (only for archivelog mode) 17 Feb 2005: to be tested:
ALTER DATABASE DATAFILE 'file1.dbf' OFFLINE;
pause copy the file: mv file1.dbf file2.dbf
ALTER TABLESPACE dwh_data1 RENAME DATAFILE 'file1.dbf' TO 'file2.dbf';
ALTER DATABASE DATAFILE 'file2.dbf' ONLINE;

Rollback Segments

Rollback segment cannot extend: see ALTER ROLLBACK SEGMENT segname SHRINK; below.

CREATE [ PUBLIC ] ROLLBACK SEGMENT segname   -- not public --> SYS
  TABLESPACE rbs
  STORAGE       (INITIAL 256K      -- 2M for batch processes
          NEXT 256K           -- Same as initial
          MINEXTENTS 15      -- Or 20 which is optimal.  5 for batch
          --MAXEXTENTS 100      -- Not really necessary
          OPTIMAL 5M);     -- 20 * INITIAL, but in any case > minextents*initial

ALTER ROLLBACK SEGMENT segname ONLINE;

Put online after creation        

ALTER ROLLBACK SEGMENT segname SHRINK; 

Force OPTIMAL size. Use this to free space when there is no longer any activity on a rollback segement.
Use v$rollstat.rssize to find large segments, then check if there is any activity (v$rollstat.xacts > 0)

ALTER ROLLBACK SEGMENT segname ONLINE;

Off line before deleting

DROP ROLLBACK SEGMENT segname ;

Delete a segment

It is recommended that the size of the initial extents be the same as the size of the next extents.

Various rollback segments:

• SYSTEM rollback segment in SYSTEM tablespace, used by SYS
• RB_TEMP rollback segment in SYSTEM (private), used by SYS
• Other rollback segments in the rollback tablespace, used by PUBLIC.

Initialization parameters

UNDO_MANAGEMENT = MANUAL

Use the rollback segments

rollback_segments = (r0, r1, r2, r3)

Specifies the rollback segments to be put online

TRANSACTIONS

Maximum number of concurrent transactions

TRANSACTIONS_PER_ROLLBACK_SEGMENT

Number of concurrent transactions to be handled by each rollback segment

MAX_ROLLBACK_SEGMENTS

Maximum number of online rollback segments at one moment

View dba_rollback_segs

select substr( r.segment_name, 1, 10) as segname
     , substr( r.owner, 1, 10) as owner -- public or private
     , substr( r.tablespace_name, 1, 10) as tblspace
     , r.status -- online even if pending offline
     , r.segment_id as seg_id
     , r.block_id
     , r.initial_extent / 1024 as initial_kb
     , r.next_extent / 1024 as next_kb -- should be = to initial_extent
     , decode ( r.initial_extent - r.next_extent, 0, ' ', 'initial <> next' ) as gg
     , r.min_extents
     , r.max_extents
     , r.pct_increase
     , substr( f.file_name , 1, 100) as file_name
  from dba_rollback_segs r, dba_data_files f
  where r.file_id = f.file_id
  order by tblspace, block_id;

Views rollname, rollstat

select s.usn, -- rollback segment number
       substr(n.name, 1,12) as the_name,
       status, --
       extents, --
       rssize / 1024, -- current segment size
       xacts, -- number of transactions actively using the segment
       decode(xacts, 0, ' ', 'current trans') as actvty,
       optsize, -- / 1024, -- value of OPTIMAL
       hwmsize / 1024, -- max size since startup (high water mark)
       shrinks, -- Number of times the segments shrank
       aveactive / 1024, -- average size of extents
       curext,curblk -- current location of the header of the segement
  from v$rollname n, v$rollstat s
  where n.usn = s.usn;

Current activity

select s.username,
       t.status,
       t.xidusn, -- number of rollback segment begin used
       t.ubafil, -- current writing in the rollback segment
       t.ubablk,
       t.used_ublk -- number of rollback blocks generated by the transaction
  from v$session s, v$transaction t
  where s.saddr(+) = t.ses_addr; -- is outer join necessary?

Space in tablespace

column free_perc format 999
column used_mb format 999G999
column free_mb format 999G999
select substr(s.tablespace_name, 1, 18) as tblspace
     , f.autoextensible --V8
     , s.total_blocks / f.blocks * 100 as free_perc
     , f.bytes/1024/1024 AS MB
     , (f.bytes- s.total_bytes)/1024/1024 as used_mb
     , s.total_bytes/1024/1024 as free_mb
 from (select tablespace_name,
              count(*) as free_spaces, sum(blocks) as total_blocks, sum(bytes) as total_bytes
         from dba_free_space group by tablespace_name) s
     , (select tablespace_name
             , autoextensible
             , sum(blocks) as blocks
             , sum(bytes) as bytes
          from dba_data_files
          group by tablespace_name, autoextensible) f
  where s.tablespace_name = f.tablespace_name
  order by s.tablespace_name ;

Show details datafile by datafile:
select to_char( f.file_id, '99') as id
     , substr(f.file_name , 1, 5) as disk_name
     , substr(s.tablespace_name, 1, 18) as tblspace
     , decode (f.autoextensible ,
          'NO', decode ( sign (s.total_blocks / f.blocks - 0.2), -1, '-->', ' '),
                                        ' ') w --V8
     , f.autoextensible --V8
     , to_char ( (1 - s.total_blocks / f.blocks) * 100, '999' ) || '%' as used_perc
     , to_char ( s.total_blocks / f.blocks * 100, '999' ) || '%' as free_perc
     , decode ( sign (s.total_blocks / f.blocks - 0.2), -1, 'less than 20% free', ' ') as w2
     , to_char( trunc( f.bytes/1024/1024 , 2), '999G999D99') AS MB
     , to_char( trunc( (f.bytes- s.total_bytes)/1024/1024 , 2), '999G999D99') as used_mb
     , to_char( trunc( s.total_bytes/1024/1024 , 2), '999G999D99') as free_mb
     , decode (f.STATUS , 'AVAILABLE', ' ', '-->') w3
     , f.status
     , substr(file_name , 1, 100) as file_name
from (select file_id, tablespace_name,
      count(*) as free_spaces, sum(blocks) as total_blocks, sum(bytes) as total_bytes
      from dba_free_space group by tablespace_name, file_id) s
    , dba_data_files f
where s.file_id = f.file_id
order by s.tablespace_name;

Restrict to tablespaces with rollback segments: add
 where tablespace_name in (select tablespace_name from dba_rollback_segs)

Note on ORA-01594 and ORA-01595

If the rollback segment size is already below optimal size, then the errors ORA-01595 and ORA-01594 are meaningless. It is a bug (984869) that is not feasible to fix. Safely ignore these errors.

Undo Tablespace

Undo records are useful for rolling back transactions (ROLLBACK statement), recovering un-committed data and providing read consistency (e.g. long queries). However, there is always a SYSTEM rollback segment.

UNDO_MANAGEMENT = AUTO

Parameter: use the first undo tablespace encountered. If no undo tablespace is available, then the system rollback segment is used.

UNDO_TABLESPACE = undo_tablespace_name

Parameter: Use a specific undo tablespace. At database creation, the tablespace SYS_UNDOTBS is created by default.

UNDO_RETENTION

Dynamic parameter: length of time to retain undo (default 900 seconds, i.e. 15min), assuming that there is enough space in the tablespace.

UNDO_SUPPRESS_ERRORS

 Parameter.

CREATE DATABASE .... UNDO TABLESPACE undotbs DATAFILE '...';

Create the undo tablespace during database creation.

CREATE UNDO TABLESPACE undotbs_01 DATAFILE '...' SIZE xM REUSE AUTOEXTEND ON;

Explicitely create the undo tablespace.

DROP TABLESPACE undotbs_01;

Drop. Clause "including contents" is not necessary. Oracle does not let you drop an undo tablespace with unrecovered information, but will let you drop an undo tablespace with unexpired information.

ALTER SYSTEM SET UNDO_TABLESPACE = undotbs_02;

Switch undo tablespaces. The old undo tablespace enters "pending offline" mode.

V$UNDOSTAT

View statistics

V$ROLLSTAT

Information on undo segments, if undo_management is automatic

V$TRANSACTION

Undo segment information

DBA_UNDO_EXTENTS

View commit time for each extent.

How to do a flashback query

Necessary permissions: grant FLASHBACK privilege on tables or FLASHBACK ANY TABLE privilege and grant EXECUTE privilege on the DBMS_FLASHBACK package.
For LOBs, do ALTER TABLE with RETENTION option to enable flashback queries because LOBs need extra space.

Get the SCN with: select dbms_flashback.get_system_change_number from dual;
If the SCN is not known, look at the SCN numbers linked to the archived logs. Or define according to the timestamp, using the function to_timestamp('dd/mm/yyyy hh:mm','DD/MM/YYYY HH24:MI'), to_date(), SYSDATE, TRUNC(SYSDATE) or SYSDATE-1.

See data at a given time in the past:

• SELECT ... FROM table_name AS OF TIMESTAMP to_timestamp('dd/mm/yyyy hh:mm','DD/MM/YYYY HH24:MI') WHERE ...;
• SELECT ... FROM table_name AS OF TIMESTAMP TRUNC(SYSDATE) WHERE ...; -- at beginning of day
• SELECT ... FROM table_name AS OF SCN nnnn WHERE ...;

Use DBMS_FLASHBACK package to see data in the past:

• exec dbms_flashback.enable_at_time(to_timestamp (...));
or
exec dbms_flashback.enable_at_system_change_number (the_scn);
• select ...
• exec dbms_flashback.disable;

Reference: Oracle9i Database Administrator's Guide Release 2 (9.2) chapter 13.

Temporary Storage

Temporary segments are used for sorting, grouping, unions, creating indexes and select distinct. The memory used is defined by SORT_AREA_SIZE. If the volume is too large, then intermediate results are stored on disk. (Chapter 11).

The parameter SORT_AREA_SIZE is 64K by default. 128K is best. Always double if increased. If needed, increase SORT_ARE_SIZE, alter the default storage of the tablespace, create another temporary tablespace.

A TEMPORARY tablespace may contain only temporary segments.

To create a temporary tablespace:
CREATE TABLESPACE tablespace-name TEMPORARY DATAFILE file-name . . .; -- dictionary-managed
CREATE TEMPORARY TABLESPACE tablespace-name TEMPFILE file-name . . . SIZE 100M REUSE
  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16M; -- Locally managed

To change a temporary tablespace to permanent and vice-versa:
ALTER TABLESPACE tablespace-name TEMPORARY | PERMANENT;

Define the DEFAULT STORAGE with the following rules:

• Initial extent = next extent
• Extent size = multiple-of-SORT_AREA_SIZE + DB_BLOCK_SIZE (for the header)
• Pctincrease = 0

Temporary segments may be put in permanent tablespaces if a user has been assigned temporary storage in a permanent tablespace (see the TEMPORARY TABLESPACE clause in the creation of users). The segments are freed by SMON.

In temporary tablespaces, the sort segment is created by the first query / command after startup. Space is freed at shutdown. Several transactions may use the same segments, but not the same extents.

ALTER TABLESPACE "TEMPORARY_DATA" ADD TEMPFILE '...' SIZE 500M REUSE;

Add a datafile to the tablespace

ALTER DATABASE TEMPFILE '...' [ONLINE | OFFLINE];
ALTER DATABASE TEMPFILE '...' RESIZE 200M;

Note that locally managed temporary files are managed through ALTER DATABASE statements, not ALTER TABLESPACE.

ALTER DATABASE TEMPFILE '...' DROP;

Drop a datafile (if there are other datafiles). Remember to remove the file from the disk.

select * from dba_temp_files;
select * from v$tempfile;

Views of the temporary files

Locally managed temporary tablespaces have tempfiles, which are like datafiles, except that:

• Always set to NOLOGGING mode.
• A tempfile cannot be made read-only.
• A tempfile cannot be renamed.
• Cannot be created with the ALTER DATABASE statement.
• Media recovery does not recognize tempfiles:
  • BACKUP CONTROLFILE does not generate any information for tempfiles.
  • CREATE CONTROLFILE cannot specify any information about tempfiles.
• Tempfile information is shown in the dictionary view DBA_TEMP_FILES and the dynamic performance view V$TEMPFILE, but not in DBA_DATA_FILES or the V$DATAFILE view.

Miscellaneous Tips

CREATE TABLESPACE tmp_index DATAFILE '...'
  SIZE 500M REUSE EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
ALTER INDEX the_index REBUILD LOGGING TABLESPACE tmp_index ;
ALTER INDEX the_index REBUILD LOGGING TABLESPACE original_tbsp ;
DROP TABLESPACE tmp_index ; -- Wait so that objects are deleted

Do not use "MAXEXTENTS UNLIMITED" or default storage clauses in dictionary managed tablespaces. Define 1000 or 2000 for maxextents.

Never change the storage clause for SYS objects.

Use TEMPFILE based TEMORARY tablespaces.