闪回表 时报ORA-25153: 临时表空间为空

C:\Users\user>sqlplus zyh/zyh@orcl147

SQL*Plus: Release 11.1.0.6.0 - Production on 星期一 11月 12 10:49:30 2012

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

连接到:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select count(*) from test1;

  COUNT(*)
----------
      6186

SQL> delete from test1 where rownum<3000;

已删除2999行。

SQL> set time on
10:50:29 SQL> commit;

提交完成。

10:50:34 SQL> select count(*) from test1;

  COUNT(*)
----------
      3187

10:50:45 SQL> select count(*) from test1 as of timestamp  to_timestamp('2012-11-12 10:50:28','YYYY-MM-DD HH24:MI:SS');

  COUNT(*)
----------
      6186

10:52:57 SQL> FLASHBACK TABLE TEST1 TO timestamp  to_timestamp('2012-11-12 10:50:28','YYYY-MM-DD HH24:MI:SS');
FLASHBACK TABLE TEST1 TO timestamp  to_timestamp('2012-11-12 10:50:28','YYYY-MM-DD HH24:MI:SS')
                *
第 1 行出现错误:
ORA-08189: 因为未启用行移动功能, 不能闪回表

10:54:17 SQL> ALTER TABLE TEST1 ENABLE ROW MOVEMENT;

表已更改。

10:54:28 SQL> FLASHBACK TABLE TEST1 TO timestamp  to_timestamp('2012-11-12 10:50:28','YYYY-MM-DD HH24:MI:SS');
FLASHBACK TABLE TEST1 TO timestamp  to_timestamp('2012-11-12 10:50:28','YYYY-MM-DD HH24:MI:SS')
                *
第 1 行出现错误:
ORA-00604: 递归 SQL 级别 1 出现错误
ORA-25153: 临时表空间为空

10:56:05 SQL> select tablespace_name,file_name from dba_temp_files;

未选定行

11:11:23 SQL>  alter tablespace temp add tempfile 'D:\APP\ADMINISTRATOR\ORADATA\MDLCNPRO\TEMP01.DBF'size  100M autoextend off;

表空间已更改。

11:12:09 SQL>  select tablespace_name,file_name from dba_temp_files;

TABLESPACE_NAME
------------------------------------------------------------
FILE_NAME
--------------------------------------------------------------------------------
TEMP
D:\APP\ADMINISTRATOR\ORADATA\MDLCNPRO\TEMP01.DBF

11:12:25 SQL>  FLASHBACK TABLE TEST1 TO timestamp  to_timestamp('2012-11-12 10:50:28','YYYY-MM-DD HH24:MI:SS');

闪回完成。

11:12:40 SQL> select count(*) from test1;

  COUNT(*)
----------
      6186

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/23891491/viewspace-749000/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/23891491/viewspace-749000/