Description: There are two methods of creating an undo tablespace. The first method creates the undo tablespace when the CREATE DATABASE statement is issued. This occurs when you are creating a new database, and the instance is started in automatic undo management mode (UNDO_MANAGEMENT = AUTO). The second method is used with an existing database. It uses the CREATE UNDO TABLESPACE statement.
You cannot create database objects in an undo tablespace. It is reserved for system-managed undo data.
Language: PL/SQL Highlight Mode: PLSQL Last Modified: March 12th, 2009
CREATE UNDO TABLESPACE <tablespace_name>
SIZE <integer><K | M | G | T>
AUTOEXTEND <ON | OFF>
RETENTION <GUARANTEE | NOGUARANTEE>;-- The following statement illustrates using the UNDO TABLESPACE -- clause in a CREATE DATABASE statement. The undo tablespace is -- named undotbs_01 and one datafile, /u01/oracle/rbdb1/undo0101.dbf, -- is allocated for it.CREATE DATABASE rbdb1
UNDO TABLESPACE undotbs_01 DATAFILE '/u01/oracle/rbdb1/undo0101.dbf';-- The CREATE UNDO TABLESPACE statement is the same as the -- CREATE TABLESPACE statement, but the UNDO keyword is specified. -- The database determines most of the attributes of the undo -- tablespace, but you can specify the DATAFILE clause.-- This example creates the undotbs_02 undo tablespace:CREATE UNDO TABLESPACE undotbs_02
DATAFILE '/u01/oracle/rbdb1/undo0201.dbf' SIZE 2M
REUSE AUTOEXTEND ON;CREATE UNDO TABLESPACE undotbs02
SIZE 50000M REUSE AUTOEXTEND ON
SELECT segment_name, tablespace_name, status, SUM(bytes)
GROUP BY segment_name, tablespace_name, status;
ALTER SYSTEM SET undo_tablespace='UNDOTBS2' scope=BOTH;
SQL University.net courses meet the most demanding needs of the business world for advanced education
in a cost-effective manner. SQL University.net courses are available immediately for IT professionals
and can be taken without disruption of your workplace schedule or processes.
Compared to traditional travel-based training, SQL University.net saves time and valuable corporate
resources, allowing companies to do more with less. That's our mission, and that's what we deliver.