UNDO表空间管理的一个测试：
1、为了测试我设置的UNDO表空间为1M
SQL> create undo tablespace undo datafile 'E:\app\Administrator\oradata\HHN/hh_undo.dbf' size 1m ;
Tablespace created
SQL> alter system set undo_tablespace=undo;
System altered
我创建了一张表，进行了测试
SQL> create table t1(tid number,tname varchar2(2));
Table created
SQL> begin
2 for i in 1 ..1000000 loop
3 insert into t1(tid) values(i);
4 end loop;
5 end;
6 /
begin
*
第 1 行出现错误:
ORA-30036: 无法按 8 扩展段 (在还原表空间 'UNDO' 中)
ORA-06512: 在 line 3
2、查看表空间情况
SQL> select tablespace_name, status, sum(bytes) / 1024 / 1024 / 1024 as space_GB
2 from dba_undo_extents
3 group by tablespace_name, status
4 order by 1;
TABLESPACE_NAME STATUS SPACE_GB
------------------------------ --------- ----------
UNDO EXPIRED 0.00018310
UNDO UNEXPIRED 0.00073242
UNDOTBS1 EXPIRED 0.01446533
UNDOTBS1 UNEXPIRED 0.00610351
解决方法：
切换表空间
SQL> alter system set undo_tablespace=UNDOTBS1 scope=both;
System altered
SQL> alter tablespace UNDO offline;
Tablespace altered

SQL> select tablespace_name, status, sum(bytes) / 1024 / 1024 / 1024 as space_GB
2 from dba_undo_extents
3 group by tablespace_name, status
4 order by 1;
TABLESPACE_NAME STATUS SPACE_GB
------------------------------ --------- ----------
UNDOTBS1 EXPIRED 0.01446533
UNDOTBS1 UNEXPIRED 0.00610351
SQL> drop tablespace UNDO including contents AND DATAFILES CASCADE CONSTRAINTS ;
Tablespace dropped

ITPub博客

UNDO表空间管理的一个测试，切换表空间的测试

最新文章