iSelfSchooling.com  Since 1999     References  |  Search more  | Oracle Syntax  | Free Online Oracle Problem Solver

    .Home      .Subscribe     .Login      .Start Learning      .Certification      .Cancel      .Password Reminder      .Password Change     .Sign out

 

. Online Accounting        .Copyright & User Agreement   |
    .Vision      .Biography     .Acknowledgement

.Contact Us      .Comments/Suggestions       .Email2aFriend    |

 

Online Oracle Training for beginners and advanced - The most comprehensive Oracle tutorial

The authors do not guarantee or take any responsibility for the accuracy, or completeness of the information.

DBAs - Fundamentals

 

Lesson 01 | Lesson 02 | Lesson 03 | Lesson 04 | Lesson 05 | Lesson 06 | Lesson 07 | Lesson 08 | Lesson 09 | Lesson 10 | Lesson 11 | Lesson 12 | Lesson 13 | Lesson 14 | Lesson 15 | Lesson 16 | Lesson 17 | Lesson 18 | Lesson 19 | Lesson 20 | Lesson 21 | Lesson 22 | Lesson 23 | Lesson 24 | Lesson 25 | Lesson 26 |

 

Lesson 15

"Democracy does not guarantee equality of conditions - it only guarantees equality of opportunity." - Irving Kristol

 

Read first then play the video:

   DBA-VIDEO -Maintaining and Configuring UNDO tablespace

   

Maintaining and Configuring UNDO tablespace

 

Introduction

As a DBA, you are responsible for maintaining UNDO tablespaces due to an usersí database transactions, thanks to Oracle and their handy UNDO_MANAGEMENT parameter. You will indeed find this feature extremely handy. Your jobís responsibilities dictate that you should be at least informed of the following basic fundamental subjects:

 

Creating an UNDO tablespace automatically

Configuring an UNDO tablespace

Displaying the UNDO MANAGEMENT parameter

Setting the UNDO MANAGEMENT parameter

Using the DBA_ROLLBACK_SEGS view

Creating an UNDO tablespace using OMF

Displaying the OMF created file destination

Setting a tablespace status to ONLINE

Setting the UNDO segments to ONLINE

Changing the UNDO tablespace

Setting the UNDO retention time

Dropping the UNDO tablespace

Deactivate the UNDO tablespace

Commands:

SHOW PARAMETER

ALTER SYSTEM SET db_create_file_dest='c:'

CREATE UNDO TABLESPACE DATAFILE

ALTER TABLESPACE ONLINE

ALTER SYSTEM SET undo_tablespace=

ALTER SYSTEM SET undo_retention=

DROP TABLESPACE

 

Hands-on

In this exercise you will learn how to create and configure an UNDO tablespace automatically.

Let's first connect to SQL*Plus as the system/manager user.
SQL> CONNECT system/manager AS SYSDBA


View undo parameters
To create and configure an UNDO tablespace automatically, let's first use the SHOW command to check whether the UNDO management is set to AUTO or MANUAL.
SQL> SHOW PARAMETER undo
The default is set to automatic. If your UNDO management is not set to AUTO, then change it. Open the parameter file and add the UNDO_MANAGEMENT=AUTO line to it. Then shutdown and startup the database.

Query the UNDO tablespace information.
SQL> SELECT segment_name, tablespace_name, status
              FROM dba_rollback_segs
/


Create an UNDO tablespace (OMF)
Now, let's create an UNDO tablespace using Oracle-Managed Files. Before creating a tablespace, make sure that the DB_CREATE_FILE_DEST parameter set to the c:directory. Then, create an UNDO tablespace using Oracle-Managed Files (OMF) with a size of 100k.
SQL> ALTER SYSTEM SET db_create_file_dest='c:'
/
SQL> CREATE UNDO TABLESPACE my_undo_tablespace
              DATAFILE SIZE 100K
/
Remember that by default, the tablespace size using Oracle-Managed Files is 100Megabytes.


Query the UNDO segments information.
SQL> SELECT segment_name, tablespace_name, status
              FROM dba_rollback_segs
              WHERE tablespace_name = 'MY_UNDO_TABLESPACE'
/
Notice that Oracle created all of the UNDO segments for you and it will manage the UNDO segments for you automatically as soon as the UNDO tablespace is created. Notice that all of the statuses are set to OFFLINE.


Set the created UNDO tablespace to ONLINE.
SQL> ALTER TABLESPACE my_undo_tablespace ONLINE
/


Query the UNDO segments information only where the UNDO tablespace name is my_undo_tablespace.
SQL> SELECT segment_name, tablespace_name, status
              FROM dba_rollback_segs
              WHERE tablespace_name = 'MY_UNDO_TABLESPACE'
/
Notice that the segment statuses are still OFFLINE. Remember, when you create an UNDO segment always their statues are OFFLINE by default.


Set to use an undo tablespace

Now that you created your own UNDO tablespace, let's tell the Oracle database from now on to use it.
SQL> ALTER SYSTEM SET undo_tablespace=my_undo_tablespace
/

Use the SHOW command to see if the default undo_tablespace was changed.
SQL> SHOW PARAMETER undo

Query the UNDO segments information only where the UNDO tablespace name is my_undo_tablespace.

SQL> SELECT segment_name, tablespace_name, status
              FROM dba_rollback_segs
              WHERE tablespace_name = 'MY_UNDO_TABLESPACE'
/
Notice that this time, all of the segment statuses are ONLINE.


Change undo retention time
Specify 10 minutes where Oracle retains the committed UNDO information in the database.
SQL> ALTER SYSTEM SET undo_retention=600
/


Check the changes.
SQL> SHOW PARAMETER undo
From now on, for 10 minutes, the Oracle database will retain the prechanged, original copy of the data in an UNDO segment for Flashback Query purposes.


Drop undo tablespace

Try to drop the UNDO tablespace.
SQL> DROP TABLESPACE my_undo_tablespace

INCLUDING CONTENTS CASCADE CONSTRAINTS
/
That's right. You can't drop an UNDO tablespace that is currently in use.

Set the default UNDO tablespace back to UNDOTBS.

SQL> ALTER SYSTEM SET undo_tablespace=undotbs
/

Attempt to drop the UNDO tablespace again.
SQL> DROP TABLESPACE my_undo_tablespace

INCLUDING CONTENTS CASCADE CONSTRAINTS
/
No problem this time.

 

"There are only two ways to live your life. One is as though nothing is a miracle. The other is as though everything is a miracle." - Albert Einstein (1879-1955)

 

Questions:

Q: How do you create an UNDO tablespace?

Q: How do you configure an UNDO tablespace?

Q: How do you view the UNDO MANAGEMENT parameter?

Q: How do you set the UNDO MANAGEMENT parameter?

Q: Describe the DBA_ROLLBACK_SEGS view?

Q: Create an UNDO tablespace using OMF.

Q: Create an UNDO tablespace using UMF.

Q: How do you set an UNDO segment status from OFFLINE to ONLINE?

Q: How do you deactivate an UNDO tablespace?

Q: How do you drop an UNDO tablespace?

Q: How do you set an UNDO retention time?

Q: What do the following SQL statements do?

SQL> ALTER SYSTEM SET db_create_file_dest='c:\nnnnnn'
/


SQL> CREATE UNDO TABLESPACE my_undo_tablespace
              DATAFILE SIZE 100K
/


SQL> SELECT segment_name, tablespace_name, status
              FROM dba_rollback_segs
              WHERE tablespace_name = 'MY_UNDO_TABLESPACE'
/


SQL> ALTER TABLESPACE my_undo_tablespace ONLINE
/


SQL> DROP TABLESPACE my_undo_tablespace

              INCLUDING CONTENTS CASCADE CONSTRAINTS
/