--1、显示undo表空间相关参数
SQL> Show Parameters Undo;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
SQL>
undo_management=auto | manual
--2、查询undo表空间
SQL> Select a.TABLESPACE_NAME,a.CONTENTS From dba_tablespaces a Where a.Contents='UNDO' ;
TABLESPACE_NAME CONTENTS
------------------------------ ---------
UNDOTBS1 UNDO
--3、创建undo表空间
SQL> Create Undo Tablespace undotbs2 Datafile 'D:\app\Administrator\oradata\orcl\undotbs2a.dbf' Size 10m Autoextend On Next 10m Maxsize Unlimited;
Tablespace created
SQL> Select a.TABLESPACE_NAME,a.CONTENTS From dba_tablespaces a Where a.Contents='UNDO' ;
TABLESPACE_NAME CONTENTS
------------------------------ ---------
UNDOTBS1 UNDO
UNDOTBS2 UNDO
SQL>
--4、为undo表空间增加数据文件
SQL> Alter Tablespace undotbs2 Add Datafile 'D:\app\Administrator\oradata\orcl\undotbs2b.dbf' Size 10m Autoextend On Next 10m Maxsize Unlimited;
Tablespace altered
SQL>
--5、查询撤销表空间和数据文件
SQL> Select b.file_name,b.tablespace_name
2 From dba_tablespaces a,dba_data_files b Where a.TABLESPACE_NAME=b.TABLESPACE_NAME And a.contents='UNDO'
3 /
FILE_NAME TABLESPACE_NAME
-------------------------------------------------------------------------------- ------------------------------
D:\APP\ADMINISTRATOR\ORADATA\ORCL\UNDOTBS01.DBF UNDOTBS1
D:\APP\ADMINISTRATOR\ORADATA\ORCL\UNDOTBS2A.DBF UNDOTBS2
D:\APP\ADMINISTRATOR\ORADATA\ORCL\UNDOTBS2B.DBF UNDOTBS2
--6、为撤销表空间删除数据文件
SQL> Alter Tablespace undotbs2 Drop Datafile 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\UNDOTBS2B.DBF';
Tablespace altered
SQL> Select b.file_name,b.tablespace_name
2 From dba_tablespaces a,dba_data_files b Where a.TABLESPACE_NAME=b.TABLESPACE_NAME And a.contents='UNDO'
3 /
FILE_NAME TABLESPACE_NAME
-------------------------------------------------------------------------------- ------------------------------
D:\APP\ADMINISTRATOR\ORADATA\ORCL\UNDOTBS01.DBF UNDOTBS1
D:\APP\ADMINISTRATOR\ORADATA\ORCL\UNDOTBS2A.DBF UNDOTBS2
--7、改变undo表空间数据文件的大小
SQL> Alter Database Datafile 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\UNDOTBS2A.DBF' Resize 20m;
Database altered
SQL>
--8、改变undo表空间数据文件的扩展属性
SQL> Alter Database Datafile 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\UNDOTBS2A.DBF' Autoextend On Next 1m Maxsize 1024m;
Database altered
SQL>
--9、切换undo表空间
SQL> alter system set undo_tablespace=undotbs2;
System altered
SQL> show parameters undo_tablespace;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace string UNDOTBS2
--10、修改undo_retention(至少保留时间)
SQL> alter system set undo_retention=1200;
System altered
SQL> show parameter undo_retention;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_retention integer 1200
SQL> 修改retention garentee
SQL> Alter Tablespace undotbs2 Retention Guarantee;
Tablespace altered
SQL> Select a.TABLESPACE_NAME,a.CONTENTS,a.RETENTION From dba_tablespaces a Where a.Contents='UNDO' ;
TABLESPACE_NAME CONTENTS RETENTION
------------------------------ --------- -----------
UNDOTBS1 UNDO NOGUARANTEE
UNDOTBS2 UNDO GUARANTEE
SQL> Alter Tablespace undotbs2 Retention Noguarantee;
Tablespace altered
SQL> Select a.TABLESPACE_NAME,a.CONTENTS,a.RETENTION From dba_tablespaces a Where a.Contents='UNDO' ;
TABLESPACE_NAME CONTENTS RETENTION
------------------------------ --------- -----------
UNDOTBS1 UNDO NOGUARANTEE
UNDOTBS2 UNDO NOGUARANTEE
SQL>
--10、删除undo表空间
SQL> drop tablespace undotbs2 including contents and datafiles;
Tablespace dropped
--11、查看undo表空间状态
SQL> Select to_char(a.begin_time,'yyyymmdd hh24:mi:ss') As begin_time,to_char(a.end_time,'yyyymmdd hh24:mi:ss') As end_time,a.UNDOBLKS,a.TXNCOUNT From v$undostat a;
BEGIN_TIME END_TIME UNDOBLKS TXNCOUNT
----------------- ----------------- ---------- ----------
20150831 00:38:10 20150831 00:39:49 256 483
20150831 00:28:10 20150831 00:38:10 37 265
20150831 00:18:10 20150831 00:28:10 9 152
20150831 00:08:10 20150831 00:18:10 8 84
20150830 23:58:10 20150831 00:08:10 80 334
20150830 23:48:10 20150830 23:58:10 6 76
20150830 23:38:10 20150830 23:48:10 9 130
20150830 23:28:10 20150830 23:38:10 8 93
20150830 23:18:10 20150830 23:28:10 9 76
20150830 23:08:10 20150830 23:18:10 7 204
20150830 22:58:10 20150830 23:08:10 101 186
20150830 22:48:10 20150830 22:58:10 12 113
20150830 22:38:10 20150830 22:48:10 6 80
20150830 22:28:10 20150830 22:38:10 14 192
20150830 22:18:10 20150830 22:28:10 9 80
20150830 22:08:10 20150830 22:18:10 14 365
20150830 21:58:10 20150830 22:08:10 73 160
20150830 21:48:10 20150830 21:58:10 13 283
20150830 21:38:10 20150830 21:48:10 13 117
19 rows selected
SQL>
SQL> Show Parameters Undo;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
SQL>
undo_management=auto | manual
--2、查询undo表空间
SQL> Select a.TABLESPACE_NAME,a.CONTENTS From dba_tablespaces a Where a.Contents='UNDO' ;
TABLESPACE_NAME CONTENTS
------------------------------ ---------
UNDOTBS1 UNDO
--3、创建undo表空间
SQL> Create Undo Tablespace undotbs2 Datafile 'D:\app\Administrator\oradata\orcl\undotbs2a.dbf' Size 10m Autoextend On Next 10m Maxsize Unlimited;
Tablespace created
SQL> Select a.TABLESPACE_NAME,a.CONTENTS From dba_tablespaces a Where a.Contents='UNDO' ;
TABLESPACE_NAME CONTENTS
------------------------------ ---------
UNDOTBS1 UNDO
UNDOTBS2 UNDO
SQL>
--4、为undo表空间增加数据文件
SQL> Alter Tablespace undotbs2 Add Datafile 'D:\app\Administrator\oradata\orcl\undotbs2b.dbf' Size 10m Autoextend On Next 10m Maxsize Unlimited;
Tablespace altered
SQL>
--5、查询撤销表空间和数据文件
SQL> Select b.file_name,b.tablespace_name
2 From dba_tablespaces a,dba_data_files b Where a.TABLESPACE_NAME=b.TABLESPACE_NAME And a.contents='UNDO'
3 /
FILE_NAME TABLESPACE_NAME
-------------------------------------------------------------------------------- ------------------------------
D:\APP\ADMINISTRATOR\ORADATA\ORCL\UNDOTBS01.DBF UNDOTBS1
D:\APP\ADMINISTRATOR\ORADATA\ORCL\UNDOTBS2A.DBF UNDOTBS2
D:\APP\ADMINISTRATOR\ORADATA\ORCL\UNDOTBS2B.DBF UNDOTBS2
--6、为撤销表空间删除数据文件
SQL> Alter Tablespace undotbs2 Drop Datafile 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\UNDOTBS2B.DBF';
Tablespace altered
SQL> Select b.file_name,b.tablespace_name
2 From dba_tablespaces a,dba_data_files b Where a.TABLESPACE_NAME=b.TABLESPACE_NAME And a.contents='UNDO'
3 /
FILE_NAME TABLESPACE_NAME
-------------------------------------------------------------------------------- ------------------------------
D:\APP\ADMINISTRATOR\ORADATA\ORCL\UNDOTBS01.DBF UNDOTBS1
D:\APP\ADMINISTRATOR\ORADATA\ORCL\UNDOTBS2A.DBF UNDOTBS2
--7、改变undo表空间数据文件的大小
SQL> Alter Database Datafile 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\UNDOTBS2A.DBF' Resize 20m;
Database altered
SQL>
--8、改变undo表空间数据文件的扩展属性
SQL> Alter Database Datafile 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\UNDOTBS2A.DBF' Autoextend On Next 1m Maxsize 1024m;
Database altered
SQL>
--9、切换undo表空间
SQL> alter system set undo_tablespace=undotbs2;
System altered
SQL> show parameters undo_tablespace;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace string UNDOTBS2
--10、修改undo_retention(至少保留时间)
SQL> alter system set undo_retention=1200;
System altered
SQL> show parameter undo_retention;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_retention integer 1200
SQL> 修改retention garentee
SQL> Alter Tablespace undotbs2 Retention Guarantee;
Tablespace altered
SQL> Select a.TABLESPACE_NAME,a.CONTENTS,a.RETENTION From dba_tablespaces a Where a.Contents='UNDO' ;
TABLESPACE_NAME CONTENTS RETENTION
------------------------------ --------- -----------
UNDOTBS1 UNDO NOGUARANTEE
UNDOTBS2 UNDO GUARANTEE
SQL> Alter Tablespace undotbs2 Retention Noguarantee;
Tablespace altered
SQL> Select a.TABLESPACE_NAME,a.CONTENTS,a.RETENTION From dba_tablespaces a Where a.Contents='UNDO' ;
TABLESPACE_NAME CONTENTS RETENTION
------------------------------ --------- -----------
UNDOTBS1 UNDO NOGUARANTEE
UNDOTBS2 UNDO NOGUARANTEE
SQL>
--10、删除undo表空间
SQL> drop tablespace undotbs2 including contents and datafiles;
Tablespace dropped
--11、查看undo表空间状态
SQL> Select to_char(a.begin_time,'yyyymmdd hh24:mi:ss') As begin_time,to_char(a.end_time,'yyyymmdd hh24:mi:ss') As end_time,a.UNDOBLKS,a.TXNCOUNT From v$undostat a;
BEGIN_TIME END_TIME UNDOBLKS TXNCOUNT
----------------- ----------------- ---------- ----------
20150831 00:38:10 20150831 00:39:49 256 483
20150831 00:28:10 20150831 00:38:10 37 265
20150831 00:18:10 20150831 00:28:10 9 152
20150831 00:08:10 20150831 00:18:10 8 84
20150830 23:58:10 20150831 00:08:10 80 334
20150830 23:48:10 20150830 23:58:10 6 76
20150830 23:38:10 20150830 23:48:10 9 130
20150830 23:28:10 20150830 23:38:10 8 93
20150830 23:18:10 20150830 23:28:10 9 76
20150830 23:08:10 20150830 23:18:10 7 204
20150830 22:58:10 20150830 23:08:10 101 186
20150830 22:48:10 20150830 22:58:10 12 113
20150830 22:38:10 20150830 22:48:10 6 80
20150830 22:28:10 20150830 22:38:10 14 192
20150830 22:18:10 20150830 22:28:10 9 80
20150830 22:08:10 20150830 22:18:10 14 365
20150830 21:58:10 20150830 22:08:10 73 160
20150830 21:48:10 20150830 21:58:10 13 283
20150830 21:38:10 20150830 21:48:10 13 117
19 rows selected
SQL>