学步园

SQL> create undo tablespace undo_temp1 datafile 'D:\oracle\product\10.2.0\oradata\ordb10\undo_temp1.dbf' size 2M autoextend OFF;
表空间已创建。

SQL> alter system set undo_tablespace='undo_temp1' scope=both;
系统已更改。

SQL> alter tablespace undo_temp offline;
表空间已更改。

SQL> drop tablespace undo_temp including contents and datafiles;
表空间已删除。

SQL> show parameter undo
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     1
undo_tablespace                      string      undo_temp1

SQL> create table test as select * from all_objects;
SQL> variable x refcursor
SQL> exec open for select * from test;
PL/SQL 过程已成功完成。

SQL> begin
    for i in 1 .. 10
    loop
            loop
                    delete from test where rownum <= 500;
                    exit when sql%rowcount = 0;
                    commit;
            end loop;
            commit;
          insert into test select * from all_objects;
   commit;
  end loop;
   end;
   /

PL/SQL 过程已成功完成。

SQL> print x
ERROR:
ORA-01555: 快照过旧: 回退段号 12 (名称为 "_SYSSMU12$") 过小