create undo tablespace undotbs datafile size 1024m autoextend on next 100m maxsize 6000m;
--the next statement unassigns the current undo tablespace
ALTER SYSTEM SET UNDO_TABLESPACE ='';
--the next statement assigns the new undo tablespace
ALTER SYSTEM SET UNDO_TABLESPACE =undotbs;
alter system set undo_retention=43200;
drop tablespace undotbs1 including contents and datafiles.
select sum(bytes)/1024/1024,tablespace_name from dba_undo_extents group by tablespace_name;
SUM(BYTES)/1024/1024 TABLESPACE_NAME
-------------------- ------------------------------
266.5 UNDOTBS1
1.25 UNDOTBS
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 43200
undo_tablespace string UNDOTBS
SQL> alter system set undo_retention=900;
System altered.
SQL> select sum(bytes)/1024/1024,tablespace_name from dba_undo_extents group by tablespace_name;
SUM(BYTES)/1024/1024 TABLESPACE_NAME
-------------------- ------------------------------
266.5 UNDOTBS1
1.25 UNDOTBS
SQL> alter system set undo_retention=600;
System altered.
SQL> select sum(bytes)/1024/1024,tablespace_name from dba_undo_extents group by tablespace_name;
SUM(BYTES)/1024/1024 TABLESPACE_NAME
-------------------- ------------------------------
266.5 UNDOTBS1
1.25 UNDOTBS
SQL> alter system set undo_retention=43200;
System altered.
SQL> ALTER TABLESPACE UNDOTBS1 OFFLINE
SQL> select sum(bytes)/1024/1024,tablespace_name from dba_undo_extents group by tablespace_name;
SUM(BYTES)/1024/1024 TABLESPACE_NAME
-------------------- ------------------------------
266.5 UNDOTBS1
1.3125 UNDOTBS
SQL> desc dba_tablespaces
Name Null? Type
----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
TABLESPACE_NAME NOT NULL VARCHAR2(30)
BLOCK_SIZE NOT NULL NUMBER
INITIAL_EXTENT NUMBER
NEXT_EXTENT NUMBER
MIN_EXTENTS NOT NULL NUMBER
MAX_EXTENTS NUMBER
PCT_INCREASE NUMBER
MIN_EXTLEN NUMBER
STATUS VARCHAR2(9)
CONTENTS VARCHAR2(9)
LOGGING VARCHAR2(9)
FORCE_LOGGING VARCHAR2(3)
EXTENT_MANAGEMENT VARCHAR2(10)
ALLOCATION_TYPE VARCHAR2(9)
PLUGGED_IN VARCHAR2(3)
SEGMENT_SPACE_MANAGEMENT VARCHAR2(6)
DEF_TAB_COMPRESSION VARCHAR2(8)
RETENTION VARCHAR2(11)
BIGFILE VARCHAR2(3)
SQL> select tablespace_name,status from dba_tablespaces where tablespace_name like 'UNDO%';
TABLESPACE_NAME STATUS
------------------------------ ---------
UNDOTBS ONLINE
UNDOTBS1 ONLINE
SQL> alter tablespace undotbs1 offline;
alter tablespace undotbs1 offline
*
ERROR at line 1:
ORA-30042: Cannot offline the undo tablespace
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 43200
undo_tablespace string UNDOTBS
SQL> desc v$undostat
Name Null? Type
----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
BEGIN_TIME DATE
END_TIME DATE
UNDOTSN NUMBER
UNDOBLKS NUMBER
TXNCOUNT NUMBER
MAXQUERYLEN NUMBER
MAXQUERYID VARCHAR2(13)
MAXCONCURRENCY NUMBER
UNXPSTEALCNT NUMBER
UNXPBLKRELCNT NUMBER
UNXPBLKREUCNT NUMBER
EXPSTEALCNT NUMBER
EXPBLKRELCNT NUMBER
EXPBLKREUCNT NUMBER
SSOLDERRCNT NUMBER
NOSPACEERRCNT NUMBER
ACTIVEBLKS NUMBER
UNEXPIREDBLKS NUMBER
EXPIREDBLKS NUMBER
TUNED_UNDORETENTION NUMBER
SQL> desc v$rollstat
Name Null? Type
----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
USN NUMBER
LATCH NUMBER
EXTENTS NUMBER
RSSIZE NUMBER
WRITES NUMBER
XACTS NUMBER
GETS NUMBER
WAITS NUMBER
OPTSIZE NUMBER
HWMSIZE NUMBER
SHRINKS NUMBER
WRAPS NUMBER
EXTENDS NUMBER
AVESHRINK NUMBER
AVEACTIVE NUMBER
STATUS VARCHAR2(15)
CUREXT NUMBER
CURBLK NUMBER
SQL> select count(*) from v$rollstat;
COUNT(*)
----------
12
SQL> select USN,RSSIZE,HWMSIZE,STATUS from v$rollstat;
USN RSSIZE HWMSIZE STATUS
---------- ---------- ---------- ---------------
0 385024 385024 ONLINE
1 75620352 1543495680 PENDING OFFLINE
14 253952 253952 ONLINE
15 188416 188416 ONLINE
16 188416 188416 ONLINE
17 188416 188416 ONLINE
18 188416 188416 ONLINE
19 188416 188416 ONLINE
20 188416 188416 ONLINE
21 188416 188416 ONLINE
22 188416 188416 ONLINE
23 188416 188416 ONLINE
12 rows selected.
SQL> select USN,RSSIZE,HWMSIZE,STATUS from v$rollstat;
USN RSSIZE HWMSIZE STATUS
---------- ---------- ---------- ---------------
0 385024 385024 ONLINE
14 253952 253952 ONLINE
15 188416 188416 ONLINE
16 188416 188416 ONLINE
17 253952 253952 ONLINE
18 253952 253952 ONLINE
19 253952 253952 ONLINE
20 188416 188416 ONLINE
21 253952 253952 ONLINE
22 188416 188416 ONLINE
23 188416 188416 ONLINE
11 rows selected.
SQL> alter tablespace undotbs1 offline;
Tablespace altered.
SQL> select sum(bytes)/1024/1024 from dba_data_files;
SUM(BYTES)/1024/1024
--------------------
53830
SQL> drop tablespace undotbs1 including contents and datafiles;
Tablespace dropped.
SQL> select sum(bytes)/1024/1024 from dba_data_files;
SUM(BYTES)/1024/1024
--------------------
53830
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 43200
undo_tablespace string UNDOTBS
SQL> SELECT TO_CHAR(BEGIN_TIME, 'MM/DD/YYYY HH24:MI:SS') BEGIN_TIME,
TO_CHAR(END_TIME, 'MM/DD/YYYY HH24:MI:SS') END_TIME,
UNDOTSN, UNDOBLKS, TXNCOUNT, MAXCONCURRENCY AS "MAXCON"
FROM v$UNDOSTAT WHERE rownum <= 2 3 4 10;
BEGIN_TIME END_TIME UNDOTSN UNDOBLKS TXNCOUNT MAXCON
------------------- ------------------- ---------- ---------- ---------- ----------
07/03/2012 10:23:38 07/03/2012 10:31:58 92 51 738 3
07/03/2012 10:13:38 07/03/2012 10:23:38 92 65 837 5
07/03/2012 10:03:38 07/03/2012 10:13:38 92 100 1402 4
07/03/2012 09:53:38 07/03/2012 10:03:38 92 160 1360 4
07/03/2012 09:43:38 07/03/2012 09:53:38 1 130 1840 5
07/03/2012 09:33:38 07/03/2012 09:43:38 1 50 811 4
07/03/2012 09:23:38 07/03/2012 09:33:38 1 55 942 4
07/03/2012 09:13:38 07/03/2012 09:23:38 1 66 949 4
07/03/2012 09:03:38 07/03/2012 09:13:38 1 86 1471 5
07/03/2012 08:53:38 07/03/2012 09:03:38 1 109 641 4