Tablespace created.
SQL> alter system set undo_tablespace='undotbs2' scope=both;
System altered.
SQL>
SQL> drop tablespace UNDOTBS1 including contents and datafiles;
Tablespace dropped.
SQL> !df -gt
Filesystem GB blocks Used Free %Used Mounted on
/dev/hd4 5.00 0.66 4.34 14% /
/dev/hd2 10.00 3.25 6.75 33% /usr
/dev/hd9var 5.00 0.65 4.35 13% /var
/dev/hd3 10.00 0.66 9.34 7% /tmp
/dev/hd1 15.00 5.99 9.01 40% /home
/dev/hd11admin 10.00 0.00 10.00 1% /admin
/proc - - - - /proc
/dev/hd10opt 5.00 0.28 4.72 6% /opt
/dev/livedump 2.00 0.00 2.00 1% /var/adm/ras/livedump
/dev/oralv01 150.00 0.37 149.63 1% /740arcnew
/dev/lv01 179.00 17.32 161.68 10% /740bak
/dev/bealv01 99.00 0.90 98.10 1% /740bea
/dev/moracle01 9.00 0.75 8.25 9% /740mhome
/dev/moracle02 90.00 21.67 68.33 25% /740moracle
/dev/oralv02 847.00 700.23 146.77 83% /740ora
SQL> create undo tablespace undotbs1 datafile '/XXX/undotbs1_01.dbf' size 20480M;
Tablespace created.
SQL> alter system set undo_tablespace='undotbs1' scope=both;
System altered.
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_undo_autotune boolean FALSE
undo_management string AUTO
undo_retention integer 10800
undo_tablespace string undotbs1
SQL> alter tablespace undotbs1 add datafile '/XXX/undotbs1_02.dbf' size 20480M;
Tablespace altered.
SQL> select name,value from v$parameter where name in('undo_management','undo_tablespace');
NAME VALUE
-------------------------------------------------------------------------------- ---------------------------------------------------
undo_management AUTO
undo_tablespace undotbs1
SQL> alter tablespace undotbs1 add datafile '/XXX/undotbs1_03.dbf' size 20480M;
Tablespace altered.
SQL> drop tablespace UNDOTBS2 including contents and datafiles;
Tablespace dropped.
SQL> set line 132
SQL> set wrap off
SQL> select t.*
2 from (SELECT D.TABLESPACE_NAME,
3 SPACE "SUM_SPACE(M)",
4 BLOCKS SUM_BLOCKS,
5 SPACE - NVL(FREE_SPACE, 0) "USED_SPACE(M)",
6 ROUND((1 - NVL(FREE_SPACE, 0) / SPACE) * 100, 2) "USED_RATE(%)",
7 FREE_SPACE "FREE_SPACE(M)"
8 FROM (SELECT TABLESPACE_NAME,
9 ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE,
10 SUM(BLOCKS) BLOCKS
11 FROM DBA_DATA_FILES
12 GROUP BY TABLESPACE_NAME) D,
13 (SELECT TABLESPACE_NAME,
14 ROUND(SUM(BYTES) / (1024 * 1024), 2) FREE_SPACE
15 FROM DBA_FREE_SPACE
16 GROUP BY TABLESPACE_NAME) F
17 WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
18 UNION ALL --if have tempfile
19 SELECT D.TABLESPACE_NAME,
20 SPACE "SUM_SPACE(M)",
21 BLOCKS SUM_BLOCKS,
22 USED_SPACE "USED_SPACE(M)",
23 ROUND(NVL(USED_SPACE, 0) / SPACE * 100, 2) "USED_RATE(%)",
24 SPACE - USED_SPACE "FREE_SPACE(M)"
25 FROM (SELECT TABLESPACE_NAME,
26 ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE,
27 SUM(BLOCKS) BLOCKS
28 FROM DBA_TEMP_FILES
29 GROUP BY TABLESPACE_NAME) D,
30 (SELECT TABLESPACE,
31 ROUND(SUM(BLOCKS * 8192) / (1024 * 1024), 2) USED_SPACE
32 FROM V$SORT_USAGE
33 GROUP BY TABLESPACE) F
34 WHERE D.TABLESPACE_NAME = F.TABLESPACE(+)) t
35 order by "USED_RATE(%)" desc;
TABLESPACE_NAME SUM_SPACE(M) SUM_BLOCKS USED_SPACE(M) USED_RATE(%) FREE_SPACE(M)
------------------------------ ------------ ---------- ------------- ------------ -------------
IDR_TS 477184 61079552 359997 75.44 117187
INDEX_TS 8192 1048576 3962.12 48.37 4229.88
IDRERROR_TS 4096 524288 1970 48.1 2126
BILL_TS 61440 7864320 28346.25 46.14 33093.75
USER_TS 2048 262144 823 40.19 1225
SYSTEM 2048 262144 816.25 39.86 1231.75
STATIC_TS 300 38400 103 34.33 197
ORDERHST_TS 8192 1048576 2425 29.6 5767
SYSAUX 3072 393216 822 26.76 2250
INDX 100 12800 25.19 25.19 74.81
ORDER_TS 20 2560 4 20 16
TABLESPACE_NAME SUM_SPACE(M) SUM_BLOCKS USED_SPACE(M) USED_RATE(%) FREE_SPACE(M)
------------------------------ ------------ ---------- ------------- ------------ -------------
USERS 5 640 1 20 4
EXAMPLE 200 25600 12.25 6.13 187.75
TOOLS 20 2560 1 5 19
ODM 50 6400 2.37 4.74 47.63
DRSYS 50 6400 1.87 3.74 48.13
CWMLITE 50 6400 1 2 49
XDB 100 12800 1 1 99
UNDOTBS1 61440 7864320 18.5 .03 61421.5
TEMP 94207 12058496 1 0 94206
20 rows selected.