|
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.
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:'
/
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
/ |
