This
practice focuses on the type of undo used in the database.
Instructions
1.
Use the SHOW PARAMETER command to determine if you are using manual or
automatic undo segments. If you
are using automatic undo segments, determine the name of the undo tablespace
being used.
|
SQL>
show parameter rollback_segments NAME
TYPE
VALUE -------------------------
----------- --------------------------- max_rollback_segments
integer 37 rollback_segments
string SQL>
show parameter undo NAME
TYPE
VALUE -------------------------
----------- --------------------------- undo_management
string
AUTO undo_retention
integer 900 undo_suppress_errors
boolean
FALSE undo_tablespace
string
UNDOTBS |
2.
Connect
as user SYSTEM/MANAGER, and list the undo segments in your undo tablespace.
Note, the undo tablespace here is
UNDOTBS
|
SQL>
CONNECT SYSTEM/MANAGER Connected. SQL>
SELECT segment_name
2 FROM
dba_rollback_segs
3 WHERE
tablespace_name = 'UNDOTBS'; SEGMENT_NAME -------------------- _SYSSMU1$ _SYSSMU2$ _SYSSMU3$ _SYSSMU4$ _SYSSMU5$ _SYSSMU6$ _SYSSMU7$ _SYSSMU8$ 8
rows selected. |
This
practice covers implementing Automatic Undo Management in an Oracle database.
Instructions
1.
Create undo tablespace UNDO2, size 15M, in $HOME/oradata/u03. List the
undo segments in tablespace UNDO2.
|
SQL>
CREATE UNDO TABLESPACE undo2
2 DATAFILE
'$HOME/ORADATA/u03/undo2.dbf' size 15M; Tablespace
created. SQL>
SELECT segment_name
2 FROM
dba_rollback_segs
3 WHERE
tablespace_name = 'UNDO2'; SEGMENT_NAME -------------------- _SYSSMU9$ _SYSSMU10$ _SYSSMU11$ _SYSSMU12$ _SYSSMU13$ _SYSSMU14$ _SYSSMU15$ _SYSSMU16$ 8
rows selected. |
2.
In a new telnet session start SQL*Plus and connect as user HR and
insert a row into DEPARTMENTS table. Do
not commit, roll back, or exit the session.
|
SQL>
CONNECT hr/hr Connected. SQL>
INSERT INTO departments
2
(department_id,department_name)
3 VALUES
(9999,'x'); 1
row created. |
3.
In the session in which you are connected as SYS, using the ALTER
SYSTEM command, switch the UNDO tablespace from UNDOTBS to UNDO2 for the
instance
|
SQL>
ALTER SYSTEM SET
undo_tablespace='UNDO2'; System
altered. |
4.
As SYS drop tablespace UNDOTBS. What happened?
|
SQL>
DROP TABLESPACE undotbs
2
INCLUDING CONTENTS AND DATAFILES; DROP
TABLESPACE undotbs INCLUDING CONTENTS AND DATAFILES * ERROR
at line 1: ORA-30013:
undo tablespace 'UNDOTBS' is currently in use |
5.
List the undo segments in tablespace UNDOTBS and their status. Compare
this list to the list in step 1.
|
SQL>
SELECT segment_name
2 FROM
dba_rollback_segs
3 WHERE
tablespace_name = 'UNDOTBS'; SEGMENT_NAME -------------------- _SYSSMU1$ _SYSSMU2$ _SYSSMU3$ _SYSSMU4$ _SYSSMU5$ _SYSSMU6$ _SYSSMU7$ _SYSSMU8$ 8
rows selected. |
6.
Check the status of the undo segments in the UNDOTBS tablespace using
V$ROLLNAME and V$ROLLSTAT.
|
SQL>
SELECT a.usn,a.name,b.status
2 FROM
v$rollname a, v$rollstat b
3 WHERE
a.name
4
IN ( SELECT segment_name
5
FROM
dba_rollback_segs
6
WHERE
tablespace_name = 'UNDOTBS‘ )
7 AND
a.usn = b.usn;
USN NAME
STATUS -----
------------ ---------------
2 _SYSSMU2$ PENDING
OFFLINE |