A reader, January 25, 2005 - 2:24 pm UTC
Excellent
Sandeep, January 25, 2005 - 5:41 pm UTC
Thank you very much Tom.
Thanks for the prompt answer & waiting for the next chance to ask you another question..
Flashback Purge
Jeff, March 04, 2005 - 6:52 pm UTC
For the vast majority of the interactions on Ask Tom the information provided is very useful - THANK YOU for the column.
On the use of PURGE I am very perplexed. I believe that I have followed all of the provided instructions and have gone
so far as to sign-in as sys/as sysdba and have had no success at using "purge dba_recyclebin;", as an example.
The flashback privilege is set. The drop any ... is set.
The error message that is returned is . . .
SP2-0734: unknown command beginning "purge dba_..."
was something overlooked during the install?
It is 10g R1 on a Windows Server 2003 Enterprise Edition.
The v$version is . . .
BANNER
================================================================
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bi
PL/SQL Release 10.1.0.2.0 - Production
CORE 10.1.0.2.0 Production
TNS for 64-bit Windows: Version 10.1.0.2.0 - Production
NLSRTL Version 10.1.0.2.0 - Production
Thank you in advance.
March 04, 2005 - 7:59 pm UTC
show us the cut and paste please.
and -- you are using the 10g sqlplus right? otherwise, since purge did not exist in 9i -- sqlplus will definitely "not accept it"
Why I cant see it in recyclebin?
NK, June 29, 2005 - 8:00 am UTC
I have done the following but still cant get that in sys schema,
what i did was the following:
SQL> SHOW USER
USER is "SYS"
SQL> CREATE TABLE E_SCOTT AS SELECT * FROM SCOTT.EMP;
Table created.
SQL> DESC E_SCOTT;
Name Null? Type
----------------------------------------------------------------------------------- -------- ------
EMPNO NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
SQL> SELECT COUNT(*) FROM E_SCOTT;
COUNT(*)
----------
14
SQL> DROP TABLE E_SCOTT;
Table dropped.
SQL> SELECT OBJECT_NAME FROM RECYCLEBIN;
no rows selected
SQL> DESC E_SCOTT;
ERROR:
ORA-04043: object E_SCOTT does not exist
SQL> FLASHBACK TABLE E_SCOTT TO BEFORE DROP;
FLASHBACK TABLE E_SCOTT TO BEFORE DROP
*
ERROR AT LINE 1:
ORA-38305: OBJECT NOT IN RECYCLE BIN
Am I doing something wrong?
Yeah one more thing I would like to know about the hidden parameters in 10g which starts from "_" underscore, How can i see/check the value of those? for instance there is a parameter _recyclebin, I want to check the value of this parameter and know what it does, any useful link to help me in that?
Thank you in advance for your valueable time.
June 29, 2005 - 9:19 am UTC
create or replace view all_parameters
as
select x.ksppinm name, y.ksppstvl value
from x$ksppi x , x$ksppcv y
where x.indx = y.indx
order by x.ksppinm
/
grant select on all_parameters to public;
create public synonym all_parameters for all_parameters;
that is a view I create in my TEST DATABASES. you can see them all. I would suspect that someone has disabled this via the _ parameter.
Satheesh Babu.S, June 29, 2005 - 11:36 am UTC
That is because the table is created in SYSTEM tablespace. You can't flashback the object in SYSTEM tablespace.
SQL> conn sys as sysdba
Enter password:
Connected.
SQL> create table t ( no number ) tablespace users;
Table created.
SQL> drop table t;
Table dropped.
SQL> flashback table t to before drop;
Flashback complete.
SQL> drop table t purge;
Table dropped.
SQL> create table t ( no number ) tablespace SYSTEM;
Table created.
SQL> drop table t;
Table dropped.
SQL> flashback table t to before drop;
flashback table t to before drop
*
ERROR at line 1:
ORA-38305: object not in RECYCLE BIN
Thanks and Regards,
Satheesh Babu.S
June 29, 2005 - 11:41 am UTC
that is another possible reason.
a third is that the space was used by something else in the background and hence the object really wasn't in the recycle bin.
or that the dba has a job to purge it.
How to purge a Trigger
pasko, June 29, 2005 - 3:30 pm UTC
Hi Tom ,
i was looking for a similar syntax to drop purge a Trigger :
what's the syntax for dropping a standalone trigger and purge it ?
i guess if i drop purge a table , the triggers would also disappear, but i would like to drop purge only the Trigger.
June 29, 2005 - 8:03 pm UTC
there is no need to "purge" a trigger, there is no "undrop" of a trigger, there is just "drop" and it is gone
Flashback and purge the recyclebin
Sonali, August 15, 2005 - 10:39 pm UTC
Article has been very helpful.
Unable to purge the Trigger after restore the DUMP
Sajid Hafeez, October 19, 2005 - 6:16 am UTC
Hi Tom,
I restored a dump and it created extra triggers starting with "BIN$" for each trigger associated with tables. These triggers are only visible through object pan, as when I use SQL to view recyclebin it returns no row
SQL> select object_name from recyclebin;
OBJECT_NAME
------------------------------
SQL>
These triggers remain there even I have tried following commands
purge recyclebin;
purge dba_recyclebin;
purge user_recyclebin;
Thanking you in advance.
kind regards
Sajid
October 19, 2005 - 7:18 am UTC
I restored a "dump" - I've no idea what a "dump" is.
show us a row (all columns) from the table you are querying that has these "bin$" triggers.
use this
</code>
http://asktom.oracle.com/~tkyte/print_table <code>
so we can read it.
PURGE
Laxman Kondal, December 05, 2005 - 4:53 pm UTC
Hi Tom
What privilege needed to use PURGE?
I tried as user and sysdba it did'nt work, something I am missing here please.
ops@ORCL10G> select COUNT(*) from RECYCLEBIN;
COUNT(*)
----------
2
1 row selected.
ops@ORCL10G> PURGE RECYCLEBIN;
SP2-0734: unknown command beginning "PURGE RECY..." - rest of line ignored.
ops@ORCL10G> exec print_table('select object_name from recyclebin')
OBJECT_NAME : BIN$BzND4uzy8YbgQBCsLmRNHg==$0
-----------------
OBJECT_NAME : BIN$BzND4uzz8YbgQBCsLmRNHg==$0
-----------------
PL/SQL procedure successfully completed.
ops@ORCL10G>
----------------------------------------------------
and from another session as sysdba:
sys@ORCL10G> SELECT COUNT(*) FROM DBA_RECYCLEBIN;
COUNT(*)
----------
6
1 row selected.
sys@ORCL10G> PURGE DBA_RECYCLEBIN;
SP2-0734: unknown command beginning "PURGE DBA_..." - rest of line ignored.
sys@ORCL10G>
-------------------------------------
I am not sure what I am missing. Could you please point me to the mistake/misunderstanding I am in - please.
Thanks and regards
December 06, 2005 - 5:29 am UTC
you are using a version of sqlplus that does not know what "purge" means.
say release 11 of the database introduces the bogus command:
sys@ORA9IR2> bogus this;
SP2-0042: unknown command "bogus this" - rest of line ignored.
SP2 - sqlplus is generating the message. sqlplus looks at the beginning of the command to figure out
a) is this something for me, sqlplus, to do OR
b) is this something for it, database, to do OR
c) is this something I've no clue what it is (purge in this case falls into
that category)
you can temporarily use:
exec execute immediate 'purge ....';
PURGE
Laxman Kondal, December 06, 2005 - 9:13 am UTC
Hi Tom
Thanks for reply.
I understand in sqlplus only execution is done in database and if its release is lower then db release then use execute immediate as you explained and it worked.
ops@ORCL10G> exec execute immediate 'purge recyclebin';
PL/SQL procedure successfully completed.
ops@ORCL10G> select count(*) from recyclebin;
COUNT(*)
----------
0
1 row selected.
ops@ORCL10G>
Thanks and regards.
Recyclebin
Prasad Mynumpati, December 19, 2005 - 9:24 am UTC
Hello Tom,
Thanks a lot for helping ORACLE community to understand ORACLE better. Every time we drop a table it is going to recycle bin. When we check dba or user_extents table it is not showing up. Could you please tell us which tablespace it is using to store renamed table(recycle bin) object. Am I wasting lot of space in a tablespace becausae I am not purging my recycle bin?
Thanks a lot
Prasad Mynumpati.
December 19, 2005 - 9:32 am UTC
When you drop a table in 10g - we rename it (and the triggers, the constraints, the indexes, everything) and 'hide it'.
The extents stay exactly where they were, no data in touched or moved.
However, the extents appear to the system to be "free", we can and will reuse them as needed. Oracle will first attempt to use "truly unallocated space" - space in the tablespace no associated with any dropped object. Then, after all of this is used, Oracle will use extents from the oldest objects in the recycle bin - it will NOT autoextend a datafile unless it needs to.
You are not wasting any space.
What happens with drop user cascade?
A reader, December 19, 2005 - 11:45 am UTC
Hi Tom,
What happens when you drop a user cascade? I suppose all objects are "purged" rather than hidden (dropped) ?
Thanks,
Raj
December 19, 2005 - 12:20 pm UTC
they are purged.
sateesh babu ' view's
JASDEEP, December 19, 2005 - 4:11 pm UTC
I have executed following sql's and found a peculier problem
SQL> create table t (x number ) tablespace system;
Table created.
SQL> drop table t;
Table dropped.
SQL> flashback table t to before drop;
Flashback complete.
SQL> select object_name from recyclebin;
no rows selected
IT HAPPENED FOR THE FIRST TIME.
AND NOW............
SQL> create table d ( x number) tablespace system;
Table created.
SQL> drop table d;
Table dropped.
SQL> flashback table t to before drop;
flashback table t to before drop
*
ERROR at line 1:
ORA-38305: object not in RECYCLE BIN
why so ???????
December 19, 2005 - 4:35 pm UTC
table t is not in the recycle bin the second time.
table D however, is.
typing mistake
A reader, December 19, 2005 - 4:43 pm UTC
sorry for typing wrong.
SQL> create table d ( x number) tablespace system;
Table created.
SQL> drop table d;
Table dropped.
SQL> flashback table t to before drop;
flashback table t to before drop
*
ERROR at line 1:
ORA-38305: object not in RECYCLE BIN
SQL> flashback table d to before drop;
flashback table d to before drop
*
ERROR at line 1:
ORA-38305: object not in RECYCLE BIN
SQL> select object_name from recyclebin;
no rows selected
December 19, 2005 - 4:49 pm UTC
you put it into system, objects in system are not recycled.
most segments in locally managed tablespaces that are not system are recycled.
I've got a feeling your other table T was "some other" table T - not the one you just created in system.
Like this:
ops$tkyte@ORA10GR2> desc t;
Name Null? Type
---------------------------------------- -------- ----------------------------
DT DATE
X NUMBER(38)
Y VARCHAR2(30)
ops$tkyte@ORA10GR2> drop table t;
Table dropped.
<b>that was not in system, but this one is:</b>
ops$tkyte@ORA10GR2> create table t ( x int ) tablespace system;
Table created.
ops$tkyte@ORA10GR2> drop table t;
Table dropped.
ops$tkyte@ORA10GR2> flashback table t to before drop;
Flashback complete.
ops$tkyte@ORA10GR2> desc t;
Name Null? Type
---------------------------------------- -------- ----------------------------
DT DATE
X NUMBER(38)
Y VARCHAR2(30)
<b>see the one that came back? not the "newest one", but the older one. the newer one in system - not recycled.</b>
by chance
A reader, December 19, 2005 - 4:53 pm UTC
That means if it is recycled it will work like in first case.
December 19, 2005 - 5:00 pm UTC
did not understand.
A reader, December 19, 2005 - 4:56 pm UTC
That means if it is not recycled it will work like in first case
undo segments
A reader, December 19, 2005 - 5:01 pm UTC
Ok
correct me if I am wrong
IS THAT flashback query in system/sys schema uses system rollback segments by default which are reused/overwritten, while in other tablespaces it uses undo tablespace and If undo_retention is less for undo tablespace , then over here we will also get the same error.
December 19, 2005 - 5:03 pm UTC
undo is not used to UNDROP a table.
undo is not involved in the flashback drop command.
Excellent..very clear explanation
P.S.Narendra Nath, February 23, 2006 - 10:52 pm UTC
warekekXP, July 31, 2006 - 2:23 am UTC
Hi Tom,
Its not really hide the table. I can see the table when select * from tab. I need to run a script for public synonym for all user and using the statement select * from tab to select all the table in schema.
Anyway its not a problem since i can drop and create it back. Just want to this is an excelent explaination and example.
Where is wrong ?
Suvendu, September 28, 2006 - 3:49 am UTC
Hi Tom,
Could you please, look into below statements and provide your input on it?
Question: As all statements says there is NO dropped INDEX inside RECYCLEBIN, but why the following error shows an index is inside recyclebin?
Thanking you a lot for answering me!!
Regards,
Suvendu
----------
D:\oracle\Oracle10gR2\BIN>sqlplus "scott/tiger"
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Sep 28 12:55:04 2006
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> show recyclebin
SQL> select count(1) from user_recyclebin;
COUNT(1)
----------
0
SQL> select index_name from ind where status='UNUSABLE';
no rows selected
SQL> analyze table emp compute statistics;
analyze table emp compute statistics
*
ERROR at line 1:
ORA-01502: index 'SCOTT.BIN$SJtCc6nDQYKBaBnRdrVQsw==$1' or partition of such
index is in unusable state
SQL> alter session set skip_unusable_indexes=true;
Session altered.
SQL> analyze table emp compute statistics;
analyze table emp compute statistics
*
ERROR at line 1:
ORA-01502: index 'SCOTT.BIN$SJtCc6nDQYKBaBnRdrVQsw==$1' or partition of such
index is in unusable state
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
D:\oracle\Oracle10gR2\BIN>
September 28, 2006 - 4:23 am UTC
give me step by steps to reproduce this please.
Reproduced...
Suvendu, September 28, 2006 - 10:52 am UTC
Sorry, for making you confuse.
Simple, I have to collect the statistics on the table. But, while ANALYZING it was giving the error that there is an index which is dropped NOT purged (referring to recyclebin) and is in UNUSABLE state.
<Oracle>
D:\oracle\Oracle10gR2\BIN>sqlplus "scott/tiger"
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Sep 28 12:55:04 2006
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
-- I confirm that there is NO dropped object in recyclebin
SQL> show recyclebin
SQL> select count(1) from user_recyclebin;
COUNT(1)
----------
0
-- Confirmed there is NO index in UNUSABLE state.
SQL> select index_name from ind where status='UNUSABLE';
no rows selected
SQL> analyze table emp compute statistics;
analyze table emp compute statistics
*
ERROR at line 1:
ORA-01502: index 'SCOTT.BIN$SJtCc6nDQYKBaBnRdrVQsw==$1' or partition of such
index is in unusable state
-- Above error clearly says there is INDEX inside recyclebin. And, try out to ignore the unusable indexes setting parameter.
SQL> alter session set skip_unusable_indexes=true;
Session altered.
SQL> analyze table emp compute statistics;
analyze table emp compute statistics
*
ERROR at line 1:
ORA-01502: index 'SCOTT.BIN$SJtCc6nDQYKBaBnRdrVQsw==$1' or partition of such
index is in unusable state
-- But, still I got the same error. So, my question here though there is NOT any dropped index associated with EMP table, why its saying there is one.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 -
Production
With the Partitioning, OLAP and Data Mining options
D:\oracle\Oracle10gR2\BIN>
</Oracle>
Hope IÂ’m clear to you. Please, correct me if IÂ’m wrong.
Thanks,
Suvendu
September 29, 2006 - 7:42 am UTC
ahh, i can do this (not that your example helped, i was hoping you would show me HOW to get into this state). best I can do is show you one way to GET INTO this state.
ops$tkyte%ORA10GR2> create table t ( x int );
Table created.
ops$tkyte%ORA10GR2> create index t_idx on t(x) unusable;
Index created.
ops$tkyte%ORA10GR2> drop table t;
Table dropped.
ops$tkyte%ORA10GR2> flashback table t to before drop;
Flashback complete.
ops$tkyte%ORA10GR2> analyze table t compute statistics;
analyze table t compute statistics
*
ERROR at line 1:
ORA-01502: index 'OPS$TKYTE.BIN$HpX7inAjuH7gQKjA/AAwJg==$0' or partition of
such index is in unusable state
ops$tkyte%ORA10GR2> select * from user_recyclebin;
no rows selected
ops$tkyte%ORA10GR2> column index_name new_val I
ops$tkyte%ORA10GR2> select table_name, index_name from user_indexes;
TABLE_NAME INDEX_NAME
------------------------------ ------------------------------
T BIN$HpX7inAjuH7gQKjA/AAwJg==$0
ops$tkyte%ORA10GR2> drop index "&I";
old 1: drop index "&I"
new 1: drop index "BIN$HpX7inAjuH7gQKjA/AAwJg==$0"
Index dropped.
ops$tkyte%ORA10GR2> analyze table t compute statistics;
Table analyzed.
<b>the index is NOT in the recyclebin, but it was once, you undropped the table, that does not rename the index.
you have a real index there</b>
FLashback Table.
Santosh, December 22, 2006 - 5:29 am UTC
Hi Tom,
Thanks a lot for your wonderful advises. I have got 2 queries reg the 'Flashback table...' feature in 10g:
1. Problem while dropping a table before doing imp:
We have got a script(which reads user_objects to find table_names) which generates a 'drop script' to drop all tables in a schema.If the schema contains any already dropped table, the 'drop script ' will have something like "drop table BIN$JS9TB8GwAsXgRADgAMTu2Q==$0". And this command fails giving:
ERROR at line 1:
ORA-00933: SQL command not properly ended
Any workaround for this( apart from adding " where object_name not like 'BIN%'"in my create 'drop script' ?
2. Space occupied:
I understand that if a table is dropped, it still sits there at the same place but gets renamed to something like BIN%.
Suppose we create few very large tables and drop them all.Obviously this will occupy lot of space. If the tablespace needs any space to get a new table created, it will claim space from these dropped tables in 'FIFO' way. Thats fine. But suppose, I have set an alert that if the tablespace is 80% FULL, I get a message. Now after dropping the large tables, I want to add one table and in this process, the size grows more than 80%. Will I get an alert in this case?
Please excuse me if I am not clear in my questions. I will try to explain more if required.
Thanks a lot.
Santosh.
December 22, 2006 - 6:36 am UTC
1) use user_tables
2) dba_free_space will show the "free space" - including space occupied by dropped tables - they will appear to "not be there" from a space management perspective.
raajesh.sr@gmail.com
Raajesh, December 22, 2006 - 9:36 am UTC
Hi Tom,
In the Oracle documentation, on the flashback technology there is a statement like this
"Dropped objects still appear in the views USER_TABLES, ALL_TABLES, DBA_TABLES, USER_INDEX, ALL_INDEX and DBA_INDEX.A new column, DROPPED, is set to YES for these objects. You can use the DROPPED column in queries against these views to view only objects that are not dropped"
I tried the same in my DB but it did not work. I queried ALL_TABLES but I did not get any hits for a dropped object. Am I doing something wrong?
---------------------------------------------------
SQL> create table temp_scr as select * from all_objects where rownum < 10;
Table created
SQL> drop table temp_scr;
Table dropped
SQL> select object_name,original_name from recyclebin
2 /
OBJECT_NAME ORIGINAL_NAME
------------------------------ --------------------------------
BIN$JTLl9twdM5/gRAADugrUzQ==$0 TEMP_SCR
SQL> select count(*) from all_tables where table_name='TEMP_SCR'
2 /
COUNT(*)
----------
0
-------------------------------------------------------
Regards
Raajesh
December 24, 2006 - 8:48 am UTC
they were going to at one point have the dropped objects appear in those views but decided against it.
by the way - the table_name would NOT have been temp-scr in all tables of course, it would have been the BIN$ name (that is the current table name...)
Getting the name of the object
A reader, December 22, 2006 - 9:11 pm UTC
Is there a way to get the "original" name of the object out of the BIN$HpX7inAjuH7gQKjA/AAwJg==$0 one ?, I ask you this because there are some objects (indexes) like that in one of my db and they are the real names of those.. looks like someone undropped them without changing the name.
Thanks!
December 24, 2006 - 9:05 am UTC
you can use flashback query.
ops$tkyte%ORA10GR2> create table t ( x int );
Table created.
ops$tkyte%ORA10GR2> create index i on t(x);
Index created.
ops$tkyte%ORA10GR2> drop table t;
Table dropped.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> column scn new_val S
ops$tkyte%ORA10GR2> select dbms_flashback.get_system_change_number scn from dual;
SCN
----------
6453097
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select object_name, original_name from user_recyclebin;
OBJECT_NAME ORIGINAL_NAME
------------------------------ --------------------------------
BIN$JVsoEOVAOYfgQAB/AQAYJQ==$0 T
BIN$JVsoEOU/OYfgQAB/AQAYJQ==$0 I
ops$tkyte%ORA10GR2> flashback table t to before drop;
Flashback complete.
ops$tkyte%ORA10GR2> select object_name, original_name from user_recyclebin;
no rows selected
ops$tkyte%ORA10GR2> select object_name, original_name from user_recyclebin as of scn &s;
old 1: select object_name, original_name from user_recyclebin as of scn &s
new 1: select object_name, original_name from user_recyclebin as of scn 6453097
OBJECT_NAME ORIGINAL_NAME
------------------------------ --------------------------------
BIN$JVsoEOVAOYfgQAB/AQAYJQ==$0 T
BIN$JVsoEOU/OYfgQAB/AQAYJQ==$0 I
what kind of privileges is it required for the last step in your test case?
A reader, December 26, 2006 - 12:19 am UTC
select object_name, original_name from user_recyclebin as of scn &s;
old 1: select object_name, original_name from user_recyclebin as of scn &s
new 1: select object_name, original_name from user_recyclebin as of scn 2006396139
select object_name, original_name from user_recyclebin as of scn 2006396139
*
ERROR at line 1:
ORA-01031: insufficient privileges
Elapsed: 00:00:00.00
December 26, 2006 - 8:02 am UTC
grant flashback on user_recyclebin to ops$tkyte;
Wonderful
Prem, January 04, 2007 - 12:19 pm UTC
Thanks for helping all of us out.
Question :
I have a table with same name in two schemas which are not using SYSTEM tablespace but different structures. If I drop both of these tables (without Purge) and would like to restore both of them will this work ?. P.S I could not try it because I do not have access to 10g yet.
Thanks,
Prem
January 05, 2007 - 9:14 am UTC
you do not restore tables, so not sure what you mean by restore in this context.
if you drop the tables and recreate them and populate them with data - of course that will "work" if that is what you want to do.
Purge everything for a specific user
Magnus, January 08, 2007 - 5:42 am UTC
Hi Tom,
In the top of the question you write (in bold):
or you could use the purge command to purge all dropped objects in the database, a tablespace, owned by a user, whatever...
Fact:
Logged on as sysdba, one can write
PURGE TABLESPACE example USER oe;
to purge a users objects from a tablespace.
Question:
Now, is there a way to purge everything for a specific user, no matter witch tablespace the object is stored in?
PURGE TABLESPACE example USER oe; without TABLESPACE?
Flashback dropped user
Serge Shmygelsky, May 15, 2007 - 5:37 am UTC
Hello Tom,
I'm wondering how can I flashback a dropped schema? As I understand, all the schema's objects are purged when it is dropped. So I cannot use flashback table. What should be used? Flashback database? RMAN recovery? And how can I find the moment (SCN or timestamp) to restore to? Is it approximate time before the schema was dropped?
I cannot find this type of solution in the docs so hope on your help.
Thanks in advance.
Serge
May 15, 2007 - 4:29 pm UTC
to get back a dropped user, you would use
a) flashback DATABASE
or
b) good old recovery (restore system, rollback/undo and tablespaces needed elsewhere, point in time recover them)
with a), you can flashback to right before the drop user, open read only, export the user, flash forward and open read/write and import
or
flashback to right before the drop user and open read write, losing anything that happened after the drop user.
with b) you just do b)
you can use logminer, you can use flashback query on the dictionary, you can use your audit trail (if you have one) to find the approximate drop time.
if you know "about when" the user was dropped....
select * from all_users as of scn/timestamp .... where username = 'xxxx';
do sort of a binary search (eg: user was dropped between 9am and 11am sometime - query as of 9am, if there, query as of 10am - if not there, try 9:30, else if there try 10:30 and so on...)
Flashback dropped user - ctd
Serge Shmygelsky, May 15, 2007 - 4:48 pm UTC
Hello Tom,
variant a) with flash forward is just what I needed. Stunning.
Flashback rocks :)
Thank you.
recover dropped column in 10g
Paul, June 02, 2007 - 1:42 am UTC
Tom,
Is there any flashback mechanism in 10g to undo dropped column (done via alter table .. drop column).
Can we use logminer or any other options/methods restore the dropped column ?
Thanks.
June 03, 2007 - 5:32 pm UTC
You can flashback database.
If you open read only, you can export the table
if you open read write, you'll have it back the way it was but will of course lose anything that happened after the drop columns.
other than that, any traditional recovery mechanism would work as well
A reader, August 27, 2007 - 6:07 pm UTC
Is there any difference between "purge recyclebin" and "purge user_recyclebin"?
How does a sys user purge the recycle bin of a user?
September 04, 2007 - 12:31 pm UTC
do not do anything as SYS, just don't.
user_recyclebin is undocumented, shouldn't be used, purge recyclebin is correct.
ops$tkyte%ORA10GR2> purge tablespace users user scott;
Tablespace purged.
is how you might do another user
OK
A reader, October 16, 2007 - 8:38 am UTC
Hello,
To which category(DDL or DML or TCL)
Flashback and Purge statements belong to?
In Oracle documentation it is not mentioned.
They simply say as "cannot be rolled back".
Any idea regarding this?
Performing DML/DDL operations over object in bin
Pradeep, June 30, 2008 - 10:15 am UTC
Tom,
I see these "performing DML/DDL operations over object in bin" errors in the alert logs. How can I identify the object in question?
July 06, 2008 - 6:53 pm UTC
simple example, no error checking (l_sql could be bigger than 255 for example, code it as you need it, not as I supply it...)
ops$tkyte%ORA11GR1> create or replace trigger ORA_38301
2 after servererror on database
3 declare
4 l_n number;
5 l_sql ora_name_list_t;
6 begin
7 if ( ora_server_error(1) = 38301 )
8 then
9 l_n := ora_sql_txt( l_sql );
10 insert into t values ( l_sql(1) );
11 commit;
12 end if;
13 end;
14 /
Trigger created.
ops$tkyte%ORA11GR1> show errors
No errors.
ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> UPDATE "BIN$UWNdXwi4YcHgQKjA/AEOkQ==$0" SET X = X;
UPDATE "BIN$UWNdXwi4YcHgQKjA/AEOkQ==$0" SET X = X
*
ERROR at line 1:
ORA-38301: can not perform DDL/DML over objects in Recycle Bin
ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> drop trigger Ora_38301;
Trigger dropped.
ops$tkyte%ORA11GR1> select * from t;
MSG
-------------------------------------------------------------------------------
UPDATE "BIN$UWNdXwi4YcHgQKjA/AEOkQ==$0" SET X = X
Performing DML/DDL operations over object in bin
Pradeep, June 30, 2008 - 10:48 am UTC
Tom,
Sorry, I submitted my question before I thought it through.
My original question still stands but in addition I wanted to ask what it is that causes these alerts?
Thanks
Pradeep
July 06, 2008 - 6:54 pm UTC
well, the error message is very telling and my example demonstrates one cause...
Guru, September 29, 2009 - 2:49 pm UTC
Hi Tom,
Recently I dropped a table from and it created a recycle bin object but after purging the recycle bin I found new object of the format "44.7".
I need your help to understand what happened and what are these numbered objects. My database version is 10.2.0.2.
Here is the sequence of steps I performed.
SQL> select segment_name,owner from dba_segments where tablespace_name='TEMP_VALIDATED';
SEGMENT_NAME OWNER
----------------- ------------
TEMP_IDX_DW_XXXXX xxx_dba
DW_XXXXX_TEMP xxx_dba
SQL> drop index xxx_dba.TEMP_IDX_DW_XXXXX;
Index dropped.
SQL> drop table xxx_dba.DW_XXXXX_TEMP;
Table dropped.
SQL> select segment_name,owner from dba_segments where tablespace_name='TEMP_VALIDATED';
SEGMENT_NAME OWNER
--------------- ---------------
BIN$dL0P/LpkMGTgQwoVBE8wZA==$0 xxx_dba
44.450591 xxx_dba
SQL> conn xxx_dba/xxx_dba
Connected.
SQL> purge recyclebin;
Recyclebin purged.
SQL> select segment_name,owner from dba_segments where tablespace_name='TEMP_VALIDATED';
SEGMENT_NAME OWNER
--------------- ---------------
44.7 xxx_dba
44.450591 xxx_dba
Any help would help me understand what happened better. Thanks much!!!
October 07, 2009 - 7:33 am UTC
look at the segment type - that number scheme represents a temporary segment. SMON cleans them up if they are orphaned. They are not related to the recycle bin.
use dba_recyclebin in the future, so you can actually see what is in the recyclebin and what it is 'protecting', not dba_segments.
Index in recycle bin
Jay, April 21, 2010 - 5:40 am UTC
Hi Tom,
One of our queries in a stored procedure was causing the procedure to hang. This was not happening alyways, may be once out of every 5 executions. We pinpointed the query and tried it in SQL Developer. The explain plan told us that it was using 2 indexes in the recycle bin. We are not sure if this was causing the issue. We dropped on of the indexes. We could not find the other index (the query was using a view on top of synonyms on top of MVs etc. So we did not have access to all recycle bins of all schemas). Anyway, we created an idex based on the columns in the explain plan and the problem seems to have gone away. The question is - is Oracle expected to use objects in the recycle bin for query plans? Here is the plan we got. Please see BIN$gv6xnPQMOkXgRAAkgXYWOg and BIN$gv6xnPQFOkXgRAAkgXYWOg in the plan
Plan hash value: 1124324120
--------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 331 | | 139 (3)| 00:00:02 |
| 1 | HASH GROUP BY | | 1 | 331 | | 139 (3)| 00:00:02 |
|* 2 | HASH JOIN OUTER | | 1 | 331 | | 138 (3)| 00:00:02 |
|* 3 | FILTER | | | | | | |
| 4 | NESTED LOOPS OUTER | | 1 | 305 | | 135 (3)| 00:00:02 |
| 5 | NESTED LOOPS OUTER | | 1 | 281 | | 133 (3)| 00:00:02 |
| 6 | NESTED LOOPS OUTER | | 1 | 247 | | 130 (3)| 00:00:02 |
| 7 | NESTED LOOPS OUTER | | 1 | 205 | | 127 (3)| 00:00:02 |
| 8 | NESTED LOOPS OUTER | | 1 | 178 | | 125 (3)| 00:00:02 |
|* 9 | HASH JOIN | | 1 | 159 | | 124 (3)| 00:00:02 |
| 10 | NESTED LOOPS | | 1 | 126 | | 121 (2)| 00:00:02 |
| 11 | NESTED LOOPS | | 2 | 210 | | 120 (2)| 00:00:02 |
| 12 | TABLE ACCESS BY INDEX ROWID | MQ_DPC_BOES | 2 | 166 | | 118 (2)| 00:00:02 |
| 13 | BITMAP CONVERSION TO ROWIDS | | | | | | |
| 14 | BITMAP AND | | | | | | |
| 15 | BITMAP CONVERSION FROM ROWIDS| | | | | | |
|* 16 | INDEX RANGE SCAN | BIN$gv6xnPQMOkXgRAAkgXYWOg==$0 | 426 | | | 4 (0)| 00:00:01 |
| 17 | BITMAP CONVERSION FROM ROWIDS| | | | | | |
| 18 | SORT ORDER BY | | | | 440K| | |
|* 19 | INDEX RANGE SCAN | BIN$gv6xnPQFOkXgRAAkgXYWOg==$0 | 426 | | | 54 (0)| 00:00:01 |
| 20 | TABLE ACCESS BY INDEX ROWID | BOE_TYPE_MAP | 1 | 22 | | 1 (0)| 00:00:01 |
|* 21 | INDEX RANGE SCAN | BOE_TYPE_MAP_IDX1 | 1 | | | 0 (0)| 00:00:01 |
|* 22 | TABLE ACCESS BY INDEX ROWID | EGP_BOE_GP_MAPPING | 1 | 21 | | 1 (0)| 00:00:01 |
|* 23 | INDEX UNIQUE SCAN | EGP_BOE_GP_PK | 1 | | | 0 (0)| 00:00:01 |
| 24 | VIEW | IMPORTERS | 4 | 132 | | 2 (0)| 00:00:01 |
| 25 | UNION-ALL | | | | | | |
| 26 | TABLE ACCESS BY INDEX ROWID | DPC_IMPORTER | 1 | 34 | | 2 (0)| 00:00:01 |
|* 27 | INDEX UNIQUE SCAN | SYS_C008097 | 1 | | | 1 (0)| 00:00:01 |
|* 28 | FILTER | | | | | | |
|* 29 | MAT_VIEW ACCESS FULL | MV_AGENTS | 23 | 713 | | 17 (0)| 00:00:01 |
|* 30 | FILTER | | | | | | |
| 31 | TABLE ACCESS BY INDEX ROWID | DPC_FTZ_LICENCEE | 1 | 29 | | 2 (0)| 00:00:01 |
|* 32 | INDEX UNIQUE SCAN | SYS_C008095 | 1 | | | 1 (0)| 00:00:01 |
|* 33 | FILTER | | | | | | |
| 34 | TABLE ACCESS BY INDEX ROWID | DPC_CLR_AGENTS | 1 | 32 | | 2 (0)| 00:00:01 |
|* 35 | INDEX UNIQUE SCAN | PK_NEW_DPC_CLR_AGENTS_01 | 1 | | | 1 (0)| 00:00:01 |
| 36 | TABLE ACCESS BY INDEX ROWID | EGP_BOE_HEADER | 1 | 19 | | 1 (0)| 00:00:01 |
|* 37 | INDEX UNIQUE SCAN | EGP_BOE_HEADER_PK | 1 | | | 0 (0)| 00:00:01 |
| 38 | TABLE ACCESS BY INDEX ROWID | MQ_DPC_CONTRS | 3 | 81 | | 2 (0)| 00:00:01 |
|* 39 | INDEX RANGE SCAN | N_MQ_DPC_CONTRS_2 | 3 | | | 1 (0)| 00:00:01 |
| 40 | TABLE ACCESS BY INDEX ROWID | BOE_HOLD_HDR | 1 | 42 | | 3 (0)| 00:00:01 |
|* 41 | INDEX RANGE SCAN | PK_BOE_HOLD_HDR | 1 | | | 2 (0)| 00:00:01 |
| 42 | TABLE ACCESS BY INDEX ROWID | MQ_DPC_BOLS | 1 | 34 | | 3 (0)| 00:00:01 |
|* 43 | INDEX RANGE SCAN | N_MQ_DPC_BOLS_1 | 1 | | | 2 (0)| 00:00:01 |
| 44 | TABLE ACCESS BY INDEX ROWID | BOL | 1 | 24 | | 2 (0)| 00:00:01 |
|* 45 | INDEX UNIQUE SCAN | PK_BOL_01 | 1 | | | 1 (0)| 00:00:01 |
|* 46 | TABLE ACCESS FULL | EGP_CONTR_CARGO | 4 | 104 | | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."BILL_NO"="H"."BOENO"(+))
3 - filter(DECODE("J"."CARGO_CODE",'R','R','Q','Q','G')='G')
9 - access("A"."BL_CONSG_CODE"="D"."IMP_CODE" AND "A"."BL_CONSG_TYPE"="D"."IMP_TYPE")
16 - access("A"."BL_CONSG_TYPE"='I' AND "A"."BL_CONSG_CODE"='O0832')
19 - access("A"."BILL_DATE">=TO_DATE(' 2008-04-27 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "A"."BILL_DATE"<=TO_DATE('
2008-04-29 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
21 - access("A"."BOE_CODE"="B"."BOE_CODE")
22 - filter(DECODE("K"."GP_TYPE",'BOTH','IN',"K"."GP_TYPE")='IN')
23 - access("B"."BOE_CODE"="K"."BOE_TYPE" AND "K"."USER_TYPE"='I')
27 - access("IMPORTER_CODE"='O0832')
28 - filter(NULL IS NOT NULL)
29 - filter(SUBSTR("AGENT_CODE",1,5)='O0832')
30 - filter(NULL IS NOT NULL)
32 - access("LICENCEE_CODE"='O0832')
33 - filter(NULL IS NOT NULL)
35 - access("CLR_AGENT_CODE"='O0832')
37 - access("A"."BILL_NO"="F"."BOENO"(+))
39 - access("A"."BILL_NO"="E"."BILL_NO"(+))
41 - access("A"."BILL_NO"="C"."BOE_NO"(+))
43 - access("A"."BILL_NO"="I"."BILL_NO"(+))
45 - access("I"."BOL_NO"="J"."BOL_NO"(+) AND "I"."ROTN"="J"."ROTN"(+) AND "I"."BOL_UNIQUE_IND"="J"."BOL_UNIQUE_IND"(+) AND
"I"."BOL_SPLIT"="J"."BOL_SPLIT"(+))
46 - filter("H"."IS_FROM_MDS"(+)='N')
Thanks
April 21, 2010 - 8:58 am UTC
the indexes were probably NOT in the recycle bin, rather this happened:
a) someone dropped the table
b) someone flashback undropped the table
c) that someone did NOT rename the constraints, indexes, etc back.
ops$tkyte%ORA11GR2> create table t ( x int, y int );
Table created.
ops$tkyte%ORA11GR2> alter table t add constraint t_pk primary key(x);
Table altered.
ops$tkyte%ORA11GR2> alter table t add constraint y_check check (y>0);
Table altered.
ops$tkyte%ORA11GR2> create trigger t before insert on t
2 begin
3 dbms_output.put_line( 'hello world' );
4 end;
5 /
Trigger created.
ops$tkyte%ORA11GR2> create index t_idx on t(y,x);
Index created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> select object_name, object_type from user_objects
2 union all
3 select constraint_name, 'constraint' from user_constraints
4 order by 2;
OBJECT_NAME OBJECT_TYPE
------------------------------ -------------------
T_IDX INDEX
T_PK INDEX
T TABLE
T TRIGGER
Y_CHECK constraint
T_PK constraint
6 rows selected.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> select object_name, original_name, type from user_recyclebin;
no rows selected
ops$tkyte%ORA11GR2> drop table t;
Table dropped.
ops$tkyte%ORA11GR2> select object_name, original_name, type from user_recyclebin;
OBJECT_NAME ORIGINAL_NAME TYPE
------------------------------ -------------------------------- -------------------------
BIN$hL+QXu/HeUDgQKjA/AFUwg==$0 T_IDX INDEX
BIN$hL+QXu/IeUDgQKjA/AFUwg==$0 T_PK INDEX
BIN$hL+QXu/JeUDgQKjA/AFUwg==$0 T TRIGGER
BIN$hL+QXu/KeUDgQKjA/AFUwg==$0 T TABLE
ops$tkyte%ORA11GR2> flashback table t to before drop;
Flashback complete.
ops$tkyte%ORA11GR2> select object_name, original_name, type from user_recyclebin;
no rows selected
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> select object_name, object_type from user_objects
2 union all
3 select constraint_name, 'constraint' from user_constraints
4 order by 2;
OBJECT_NAME OBJECT_TYPE
------------------------------ -------------------
BIN$hL+QXu/IeUDgQKjA/AFUwg==$0 INDEX
BIN$hL+QXu/HeUDgQKjA/AFUwg==$0 INDEX
T TABLE
BIN$hL+QXu/JeUDgQKjA/AFUwg==$0 TRIGGER
BIN$hL+QXu/GeUDgQKjA/AFUwg==$0 constraint
BIN$hL+QXu/FeUDgQKjA/AFUwg==$0 constraint
6 rows selected.
unfortunately, the undrop does NOT rename the underlying supporting schema objects back - you have to do that.
And that I believe is what you are seeing here, the indexes were NOT dropped - the table was and then was subsequently undropped.
Recyclebin
Jayadevan, April 22, 2010 - 2:00 am UTC
Hi Tom,
I was using SQL Developer. I saw that index (out of the 2, I could see 1) in the recyclebin in the left tabe of SQL developer window. I deleted that from the recyclebin and it promptly disappeared from the explain plan output. I couldn't locate the other index, though. As I mentioned, there were too many views and synonyms involved, I didn't go hunting for the schema from where it was getting the base table. I could see one index in the recycle bin of the schema I used to login and I deleted that.
Regards,
Jayadevan
April 22, 2010 - 8:07 am UTC
unless you query a table in the recyclebin - you will not use an index in the recyclebin.
Tell you what - do you have any steps to reproduce this - for you see, indexes IN the recyclebin can only be on tables IN the recyclebin.
... As I mentioned, there were too many
views and synonyms involved, I didn't go hunting for the schema from where it
was getting the base table. ...
and had you - you would have found out where and how you got yourself confused :)
show me an example of an index in the recyclebin on a table that is not in the recyclebin.
Drop table in system table space
senthil, October 22, 2010 - 3:06 am UTC
Hey tom ,
if we drop a table in ssytem tablespace we cant flashback it,, ... the reason i came to know is it will not use recylebin....
my question is why this concept is not used for system table space... if suppose this concept is used for system tablespace what wud be the effect??
October 25, 2010 - 3:27 pm UTC
because system is our tablespace and we do not need that feature. system is special, we should be the only thing with objects in it.
Can You Recover the DBA_Recyclebin From a Full Export
Jim Cox, March 22, 2011 - 10:30 am UTC
Hi Tom
have a situation here where a full export of a database had been taken, but the next day the dba_recyclebin was purged.
Is there any way to recover the dba_recyclebin from that full export of the database ?
Thanks
March 22, 2011 - 11:25 am UTC
the recyclebin is not exported.
remember please: export is NOT A BACKUP TOOL IN ANY SENSE OF THE WORD. There is only one type of backup: a backup, of the datafiles - be it done by rman, cp, whatever - but a backup of a datafile.
export is a logical data copy tool.
Thanks
Jim Cox, March 22, 2011 - 11:53 am UTC
Hi Tom
Understand that very well. Was just inquiring if the DBA_RECYCLEBIN was exported.
Thanks
March 22, 2011 - 11:58 am UTC
I wasn't saying it just to you - but to anyone reading, far far too many people talk of export like it is a 'backup', and it just isn't. It is a logical data copying tool.
Great thread
A reader, April 12, 2012 - 12:18 am UTC
Tom,
Lots of good info in hear...
From Oracle database 11G the complete Reference
"The default behavior for the drop of a table is for the table to retain its space allocation; you can see its space usage via the RECYCLEBIN data dictionary view.
If you create and drop a table twice, there will be two copies of the table in the recycle bin.
Although this architecture greatly simplifies recoveries of accidentally dropped tables, it may
considerably increase the space used in your database"
I don't see that this is relevant.
The table may still retain it's extents and be simply renamed however as I understand it the space is marked as free by Oracle and will be used if required.
I don't imagine Oracle shrinks datafiles if a table was dropped and purged and I doubt the dropped tables get backed up..
Can you see any need to have to run a regular job to empty the recycle bin?
April 12, 2012 - 7:51 am UTC
Although this architecture greatly simplifies recoveries of accidentally
dropped tables, it may
considerably increase the space used in your database"
umm, no - it won't. that isn't right.
The table may still retain it's extents and be simply renamed however as I
understand it the space is marked as free by Oracle and will be used if
required.
that is correct.
the dropped tables will get backed up however - the goal of a backup is to be able to restore completely. If you backup a database with tables in the recyclebin, when you restore that database - those tables will still be in the recyclebin.
Can you see any need to have to run a regular job to empty the recycle bin?
No, not really. There is one extreme case - and that would be an application that uses real tables as temporary tables (does a create table, insert, select, drop table) and does that thousands and thousands of times in a "large" tablespace. You would end up with a lot of junk in the data dictionary. If your application queries the data dictionary - that could negatively impact it. I've seen it happen once with an APEX application.
who is connected to Oracle 10g
AJ, April 18, 2012 - 11:51 pm UTC
can we see the user ip who is connected to our oracle database?
who is connected to Oracle 10g
AJ, April 18, 2012 - 11:51 pm UTC
can we see the user ip who is connected to our oracle database?
who is connected to Oracle 10g
AJ, April 18, 2012 - 11:51 pm UTC
can we see the user ip who is connected to our oracle database?
who is connected to Oracle 10g
AJ, April 18, 2012 - 11:51 pm UTC
can we see the user ip who is connected to our oracle database?
April 19, 2012 - 5:49 am UTC
v$session.machine should have the remote machine - including domain if available - for you.
Dropping a Table
Santosh, July 11, 2012 - 8:40 am UTC
Hi,
I have problem with my Sql*plus when I am dropping a Table and table is dropping succesfully. After Viewing from Select * from tab; in the table name which I deleted it is renaming with BIN$UVJP8H#V@PGIFBSW&%kfd format. Table is not deleting but it is renaming with BIN$UVJP8H#V@PGIFBSW&%kfd. Request you to help in this regard
A reader, July 17, 2012 - 7:42 am UTC
How does a bit tree and bit map index works in oracle 10g
pls tel us with example
July 17, 2012 - 9:20 am UTC