Monday, November 26, 2012

0 TABLESPACE(3) : Undo tablespace

Undo tablespace is to replace the roll back segment before 9i.

  • Rollback the un commit transaction.
  • Provide the consistent read for other user. Oracle use the snapshot isolation transaction by default. undo tablespace store the the un commit data, so other user can access it.
  • Used by flash back query

image

  • undo_management : It is recommend to set to AUTO and let oracle manage the space.
  • undo_retention: The number is in secs. That means how long oracle would keep the data in the extent after it is commit. The higher number would avoid the “Snapshot too old error” and flashback query can query order data. However, the higher number it is set, the more space would be used.
  • undo_tablespace is defined the undo tablespace name.

Create the undo tablespace

Just like create other tablespace but with undo keyword

create undo tablespace myundo1;
 
SELECT
  DT.TABLESPACE_NAME,
  DT.contents,
  DT.BLOCK_SIZE,
  DT.bigfile,
  DT.EXTENT_MANAGEMENT,
  DT2.BYTES,
  DT2.FILE_NAME
FROM
  DBA_TABLESPACES DT,
  DBA_DATA_FILES DT2
WHERE
  DT.TABLESPACE_NAME= DT2.TABLESPACE_NAME
  and DT.contents='UNDO';

image

We can add more data file in the undo tablespace but only one undo table space per database.

Change the undo tablespace

show parameter undo_tablespace
alter system set undo_tablespace = myundo1;
show parameter undo_tablespace

image

Drop the undo tablespace

We can not drop the in used undo tablespace.

-- drop the old undo tablespace
drop tablespace UNDOTBS1 INCLUDING CONTENTS AND DATAFILES;
--- Can not drop in used undo tablespace
drop tablespace myundo1

image

Add data file to the undo tablespace

-- Add data file to the undo tablespace
alter tablespace myundo1 add datafile '/home/oracle/app/oracle/oradata/orcl/ORCL/datafile/myundo2.dbf' size 10M;
 
SELECT
  DT.TABLESPACE_NAME,
  DT.contents,
  DT.BLOCK_SIZE,
  DT.bigfile,
  DT.EXTENT_MANAGEMENT,
  DT2.BYTES,
  DT2.FILE_NAME
FROM
  DBA_TABLESPACES DT,
  DBA_DATA_FILES DT2
WHERE
  DT.TABLESPACE_NAME= DT2.TABLESPACE_NAME
  and DT.contents='UNDO';

image

Since I specify the path after the datafile, the file is not name and manage by OMF, hence the drop tablespace would not remove the file.
We have to manually remove it by using OS command, such as rm.
Alternatively, we can  use below syntax

alter tablespace myundo1 add datafile size 10M;

0 comments:

Post a Comment

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates