Shrink Undo表空间

UNDOTBS1 已经extend到13.1396484G。 由于磁盘紧张，现在需要Shrink Undo表空间.

[@more@]

SQL> select usn,xacts,rssize/1024/1024/1024,hwmsize/1024/1024/1024,shrinks,extends
2 from v$rollstat order by rssize;

USN XACTS RSSIZE/1024/1024/1024 HWMSIZE/1024/1024/1024 SHRINKS EXTENDS
---------- ---------- --------------------- ---------------------- ---------- ----------
0 0 .000358582 .000358582 0 0
1 0 .005973816 .012809753 45 86
6 0 .005973816 .006950378 30 48
2 0 .005973816 .008903503 37 56
7 0 .005973816 .008903503 37 57
8 0 .005973816 .012809753 46 116
3 0 .010856628 .012809753 43 130
5 0 .010856628 .012809753 44 132
4 0 .011833191 .013786316 47 136
9 0 .070426941 .070426941 28 28
10 0 .070426941 .070426941 28 28

11 rows selected.

SQL> select tablespace_name,sum(bytes)/1024/1024/1024 GB from dba_data_files
2 group by tablespace_name;

TABLESPACE_NAME GB
------------------------------ ----------
BILLING .029296875
BUSINESS 15.625
CWMLITE .0390625
DRSYS .01953125
DUMP .009765625
EXAMPLE .1953125
INDX .493164063
ODM .01953125
PERFSTAT .1953125
SYSTEM .78125
TOOLS .009765625

TABLESPACE_NAME GB
------------------------------ ----------
UNDOTBS1 13.1396484
USERS .588378906
XDB .09765625

14 rows selected.

SQL> CREATE UNDO TABLESPACE UNDOTBS
2 DATAFILE /oas/oradata/starmap/UNDOTBS.dbf size 2g AUTOEXTEND ON;
DATAFILE /oas/oradata/starmap/UNDOTBS.dbf size 2g AUTOEXTEND ON
*
ERROR at line 2:
ORA-02236: invalid file name

SQL> CREATE UNDO TABLESPACE UNDOTBS
2 DATAFILE '/oas/oradata/starmap/UNDOTBS.dbf' size 2000m AUTOEXTEND ON;

Tablespace created.

SQL> ALTER SYSTEM SET UNDO_TABLESPACE = UNDOTBS;

System altered.

SQL> select tablespace_name,sum(bytes)/1024/1024/1024 GB from dba_data_files
2 group by tablespace_name;

TABLESPACE_NAME GB
------------------------------ ----------
BILLING .029296875
BUSINESS 15.625
CWMLITE .0390625
DRSYS .01953125
DUMP .009765625
EXAMPLE .1953125
INDX .493164063
ODM .01953125
PERFSTAT .1953125
SYSTEM .78125
TOOLS .009765625

TABLESPACE_NAME GB
------------------------------ ----------
UNDOTBS 1.953125
UNDOTBS1 13.1396484
USERS .588378906
XDB .09765625

15 rows selected.

SQL> DROP TABLESPACE UNDOTBS1;

Tablespace dropped.

SQL> select tablespace_name,sum(bytes)/1024/1024/1024 GB from dba_data_files
2 group by tablespace_name;

TABLESPACE_NAME GB
------------------------------ ----------
BILLING .029296875
BUSINESS 15.625
CWMLITE .0390625
DRSYS .01953125
DUMP .009765625
EXAMPLE .1953125
INDX .493164063
ODM .01953125
PERFSTAT .1953125
SYSTEM .78125
TOOLS .009765625

TABLESPACE_NAME GB
------------------------------ ----------
UNDOTBS 1.953125
USERS .588378906
XDB .09765625

14 rows selected.

SQL> select usn,xacts,rssize/1024/1024/1024,hwmsize/1024/1024/1024,shrinks,extends
2 from v$rollstat order by rssize;

USN XACTS RSSIZE/1024/1024/1024 HWMSIZE/1024/1024/1024 SHRINKS EXTENDS
---------- ---------- --------------------- ---------------------- ---------- ----------
11 0 .000114441 .000114441 0 0
12 0 .000114441 .000114441 0 0
13 0 .000114441 .000114441 0 0
15 0 .000114441 .000114441 0 0
17 0 .000114441 .000114441 0 0
19 0 .000114441 .000114441 0 0
20 0 .000114441 .000114441 0 0
18 0 .000114441 .000114441 0 0
16 0 .000114441 .000114441 0 0
14 0 .000114441 .000114441 0 0
0 0 .000358582 .000358582 0 0

11 rows selected.

最后同步pfile

SQL> create pfile from spfile;

File created.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/76065/viewspace-823723/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/76065/viewspace-823723/