asked on

object not in RECYCLE BIN

Oracle 10g, Windows XP

I tried to use the new Oracle feature FLASHBACK but got an erorr message:

SQL> create table X (id number);

Table created.

SQL> insert into x values(1234);

1 row created.

SQL> commit;

Commit complete.

SQL> drop table x;

Table dropped.

SQL> flashback table "X" to before drop;
flashback table "X" to before drop
*
ERROR at line 1:
ORA-38305: object not in RECYCLE BIN

SQL> select * from recyclebin;

no rows selected

How can I enable the recycle bin?

Thanks

schwertner

SELECT object_name,original_name,operation,type,dropscn,droptime
FROM user_recyclebin;

schwertner

Pay attention to 10.1.0.4.0!!!! Buuuuuuugs!

SQL*Plus: Release 10.1.0.4.0 - Production on Fri Apr 15 14:32:36 2005

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

SQL> connect lsmedical/lsmedical@test
Connected.
SQL> create table X (id number);

Table created.

SQL> insert into x values(1234);

1 row created.

SQL> commit
2 ;

Commit complete.

SQL> drop table x;

Table dropped.

SQL> flashback table "X" to before drop;

Flashback complete.

SQL> select * from x;

ID
----------
1234

SQL>

ASKER CERTIFIED SOLUTION

helpneed

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

schwertner

How the schwma was created:

CREATE TABLESPACE "TSDMEDICAL"
LOGGING
DATAFILE 'E:\ORACLE_DB_MAIN\ORADATA\MAIN\TSDMEDICAL00.DBF' SIZE 100M
AUTOEXTEND ON NEXT 100M
MAXSIZE 32768M
PERMANENT ONLINE
EXTENT MANAGEMENT LOCAL
SEGMENTSPACE MANAGEMENT MANUAL;

CREATE USER LSMEDICAL IDENTIFIED BY LSMEDICAL
DEFAULT TABLESPACE TSDMEDICAL
QUOTA UNLIMITED ON TSDMEDICAL;

Mehul Shah

Have you configured the database for flashback mode? See the below commands if you have not done soo.

SQL> startup mount;
ORACLE instance started.
Total System Global Area 184549376 bytes
Fixed Size 1300928 bytes
Variable Size 157820480 bytes
Database Buffers 25165824 bytes
Redo Buffers 262144 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter system set DB_FLASHBACK_RETENTION_TARGET=4320;
System altered.
SQL> alter system set DB_RECOVERY_FILE_DEST_SIZE=536870912;
System altered.
SQL> alter system set DB_RECOVERY_FILE_DEST=’/u02/fra’;
System altered.
SQL> alter database flashback on;
Database altered.
SQL> alter database open;
Database altered.

schwertner

I use the default installation, default parameters and NOARCHIVE log mode.
Do not use FLASHBACK settings.
In fact RECYCLE BIN do not uses flashback. It only uses the notion "flashback":

The table and its associated objects are placed in a logical container known as
the "RECYCLE BIN," which is similar to the one in your PC. However, the objects
are not moved from the tablespace they were in earlier; they still occupy the
space there. The RECYCLE BIN is merely a logical structure that catalogs the
dropped objects. Use the following command from the SQL*Plus prompt to see its
content (you'll need SQL*Plus 10.1 to do this):

SQL> show recyclebin

ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ ------------------
TESTFLASH BIN$04LhcpndanfgMAAAAAANPw==$0 TABLE 2004-02-16:21:13:31

This shows the original name of the table, TESTFLASH, as well as the new name in
the recycle bin (BIN$04LhcpndanfgMAAAAAANPw==$0). Use the FLASHBACK TABLE
command to reinstate the table.