SELECT a.name, b.name

FROM v$tablespace a, v$datafile b

WHERE a.name = (SELECT VALUE

FROM v$parameter

WHERE name = 'undo_tablespace')

AND a.ts# = b.ts#;

SQL> @/soft/query_undo.sql

NAME NAME

------------------------------ ------------------------------

UNDOTBS1 /u01/app/oracle/oradata/xcky/u

ndotbs01.dbf

[root@xckydb soft]# vi create_undo.sql

--编写undo表空间创建脚本

#create middle undo tablespace

Create UNDO TABLESPACE UNDOTBS2 DATAFILE <'/undotbs02.dbf'> SIZE <100M> REUSE AUTOEXTEND ON;

Alter SYSTEM SET UNDO_TABLESPACE=UNDOTBS2;

#delete old undo tablespace

Drop TABLESPACE INCLUDING CONTENTS AND DATAFILES;

Select * FROM DBA_TABLESPACES;

#create new undo tablespace

Create UNDO TABLESPACE DATAFILE <'/undotbs01.dbf'> SIZE <100M> REUSE AUTOEXTEND ON;

Alter SYSTEM SET UNDO_TABLESPACE=UNDOTBS1;

#delete middle undo tablespace

Drop TABLESPACE UNDOTBS2 INCLUDING CONTENTS AND DATAFILES;

Select * FROM DBA_TABLESPACES;

***********************************************声明***********************************************************************

原创作品，出自 “深蓝的blog” 博客，欢迎转载，转载时请务必注明出处，否则追究版权法律责任。

深蓝的blog：http://blog.csdn.net/huangyanlong/article/details/39935157

****************************************************************************************************************************