Undotablespace Manual creation in Oracle 10g

   The following scripts below undotablespace creation in oracle 10g


Step 1:

SQL> startup mount
ORACLE instance started.

Total System Global Area 4294967296 bytes
Fixed Size                  2078264 bytes
Variable Size            4160752072 bytes
Database Buffers          117440512 bytes
Redo Buffers               14696448 bytes
Database mounted.

Step 2:

  SQL> show parameter undo

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

undo_management                      string      AUTO

undo_retention                       integer     900

undo_tablespace                      string      UNDOTBS1

Step 3:

  Change the UNDO_MANAGEMENT to ‘MANUAL’

 

   alter system set undo_management='MANUAL' scope=spfile;

Step 4 :

As, the above parameter is a static one, bounce the database

   

SQL> shu immediate;

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 4294967296 bytes

Fixed Size                  2078264 bytes

Variable Size            4160752072 bytes

Database Buffers          117440512 bytes

Redo Buffers               14696448 bytes

Database mounted.

Database opened.

Step 5 :

 CREATE UNDO TABLESPACE "UNDOTBS2" DATAFILE

 '/u01/TEST/undotbs21.dbf' SIZE 4294967296,

 '/u04/TEST/undotbs22.dbf' SIZE 4294967296,

 '/u02/TEST/undotbs23.dbf' SIZE 6442450944,

 '/u03/TEST/undotbs24.dbf' SIZE 6442450944

 BLOCKSIZE 8192

 EXTENT MANAGEMENT LOCAL AUTOALLOCATE 

Step 6:

SQL> show parameter undo

 

 NAME                                 TYPE        VALUE

 ------------------------------------ ----------- ------------------------------

 undo_management                      string      MANUAL

 undo_retention                       integer     900

 undo_tablespace                      string      UNDOTBS1

Set the undo_tablespace to the new Undo Tablespace name and undo_management back to ‘AUTO’

 

 

 

 SQL> alter system set undo_tablespace='UNDOTBS' scope=spfile;

 

 System altered.

 

 SQL> alter system set undo_management='AUTO' scope=spfile;

 

System altered.

Step 7:

Bounce the database 

SQL> shu immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 4294967296 bytes

Fixed Size                  2078264 bytes

Variable Size            4160752072 bytes

Database Buffers          117440512 bytes

Redo Buffers               14696448 bytes

Database mounted.

Database opened.

 Check the Alert log for if any errors and check the undo parameters 

Step 8 :

SQL> show parameter undo

 

 NAME                                 TYPE        VALUE

 ------------------------------------ ----------- ------------------------------

 undo_management                      string      AUTO

 undo_retention                       integer     900

undo_tablespace                      string      UNDOTBS