How To View the Dropped Tables in Your Recycle Bin?
SQL tutorial | Interview questions | Oracle :: Interview questions :: Oracle DBA Interview questions
Page 1 of 1
How To View the Dropped Tables in Your Recycle Bin?
You can look what's in your recycle bin through the predefined view called RECYCLEBIN. You
can use the SELECT statement to list the dropped tables as shown in the following script:
SQL> connect HR/fyicenter
Connected.
SQL> CREATE TABLE emp_dept_90
2 AS SELECT * FROM employees WHERE department_id=90;
Table created.
SQL> SELECT COUNT(*) FROM emp_dept_90;
COUNT(*)
----------
3
SQL> DROP TABLE emp_dept_90;
Table dropped.
SQL> COL original_name FORMAT A14
SQL> SELECT object_name, original_name, droptime FROM recyclebin;
OBJECT_NAME ORIGINAL_NAME DROPTIME
------------------------------ -------------- -------------------
BIN$uaSS/heeQuys53HgXRhEEQ==$0 EMP_DEPT_10 2006-04-01:18:57:37
BIN$gSt95r7ATKGUPuALIHy4dw==$0 EMP_DEPT_10 2006-04-01:19:59:11
BIN$bLukbcgSQ6mK1P2QVRf+fQ==$0 EMP_DEPT_90 2006-04-01:20:47:22
As you can use the EMP_DEPT_10 was dropped twice. If the same table was dropped multiple
times, you need to restore by using the object name in the recycle bin with FLASHBACK
statement.
Note that RECYCLEBIN is just an alias of USER_RECYCLEBIN.
can use the SELECT statement to list the dropped tables as shown in the following script:
SQL> connect HR/fyicenter
Connected.
SQL> CREATE TABLE emp_dept_90
2 AS SELECT * FROM employees WHERE department_id=90;
Table created.
SQL> SELECT COUNT(*) FROM emp_dept_90;
COUNT(*)
----------
3
SQL> DROP TABLE emp_dept_90;
Table dropped.
SQL> COL original_name FORMAT A14
SQL> SELECT object_name, original_name, droptime FROM recyclebin;
OBJECT_NAME ORIGINAL_NAME DROPTIME
------------------------------ -------------- -------------------
BIN$uaSS/heeQuys53HgXRhEEQ==$0 EMP_DEPT_10 2006-04-01:18:57:37
BIN$gSt95r7ATKGUPuALIHy4dw==$0 EMP_DEPT_10 2006-04-01:19:59:11
BIN$bLukbcgSQ6mK1P2QVRf+fQ==$0 EMP_DEPT_90 2006-04-01:20:47:22
As you can use the EMP_DEPT_10 was dropped twice. If the same table was dropped multiple
times, you need to restore by using the object name in the recycle bin with FLASHBACK
statement.
Note that RECYCLEBIN is just an alias of USER_RECYCLEBIN.
Similar topics
» What Is a Recycle Bin?
» What Is a Recycle Bin?
» How To Recover a Dropped Table?
» How To Recover a Dropped Index?
» How To Empty Your Recycle Bin?
» What Is a Recycle Bin?
» How To Recover a Dropped Table?
» How To Recover a Dropped Index?
» How To Empty Your Recycle Bin?
SQL tutorial | Interview questions | Oracle :: Interview questions :: Oracle DBA Interview questions
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
Sun Jan 31, 2010 12:35 am by Micheal
» Accessing tables of other users in SQL
Sun Jan 31, 2010 12:34 am by Micheal
» Grouping commands in SQL
Sun Jan 31, 2010 12:32 am by Micheal
» Operations on Result Sets in SQL
Sun Jan 31, 2010 12:31 am by Micheal
» Subqueries in SQL
Sun Jan 31, 2010 12:30 am by Micheal
» Joining Relations in SQL
Sun Jan 31, 2010 12:28 am by Micheal
» Delete comand in SQL
Sun Jan 31, 2010 12:27 am by Micheal
» Update command in SQL
Sun Jan 31, 2010 12:26 am by Micheal
» Insert command in SQL
Sun Jan 31, 2010 12:23 am by Micheal