背景:undo表空间如果属性是自动增长的,从而表空间过大,那么就重新创建一个新的UNDO表空间。
思路:
1:查看当前UNDO表空间
2:查看数据文件创建地方
3:创建UNDO表空间
4:切换undo表空间
5:将原来的UNDO表空间OFFLINE
6:drop 原来表空间。
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
E:ORACLEPRODUCT10.2.0ORADATAFOCUSSYSTEM01.DBF
E:ORACLEPRODUCT10.2.0ORADATAFOCUSUNDOTBS01.DBF
E:ORACLEPRODUCT10.2.0ORADATAFOCUSSYSAUX01.DBF
E:ORACLEPRODUCT10.2.0ORADATAFOCUSUSERS01.DBF
E:ORACLEPRODUCT10.2.0ORADATAFOCUSAZ_SALES_FOCUS_TS01.DBF
SQL> create undo tablepsace undotbs2 datafile
2 'E:ORACLEPRODUCT10.2.0ORADATAFOCUSUNDOTBS02.DBF' size 100m;
create undo tablepsace undotbs2 datafile
'E:ORACLEPRODUCT10.2.0ORADATAFOCUSUNDOTBS02.DBF' size 100m
ORA-00901: ÎÞЧ CREATE ÃüÁî
SQL> create undo tablespace undotbs2 datafile
2 'E:ORACLEPRODUCT10.2.0ORADATAFOCUSUNDOTBS02.DBF' size 100m;
Tablespace created
SQL> alter system set undo_tablespace=undotbs2;
System altered
SQL> show parameter undo_tablespace;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace string UNDOTBS2
SQL> alter tablespace undotbs1 offline;
Tablespace altered
SQL> drop tablesapce undotbs1;
drop tablesapce undotbs1
ORA-00950: ÎÞЧ DROP Ñ¡Ïî
SQL> drop tablespace undotbs1;
Tablespace dropped
[@more@]来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22934571/viewspace-1051244/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/22934571/viewspace-1051244/