Using Flashback Drop and Managing the Recycle Bin
A user can view his objects in the recycle bin using the following statement:
SELECT * FROM RECYCLEBIN;
When you drop a tablespace including its contents, the objects in the tablespace are not placed in the recycle bin and the database purges any entries in the recycle bin for objects located in the tablespace. The database also purges any recycle bin entries for objects in a tablespace when you drop the tablespace, not including contents, and the tablespace is otherwise empty. Likewise:When you drop a user, any objects belonging to the user are not placed in the recycle bin and any objects in the recycle bin are purged.When you drop a cluster, its member tables are not placed in the recycle bin and any former member tables in the recycle bin are purged.When you drop a type, any dependent objects such as subtypes are not placed in the recycle bin and any former dependent objects in the recycle bin are purged.
When a dropped table is moved to the recycle bin, the table and its associated objects are given system-generated names.
The renaming convention is as follows:BIN$unique_id$version
where:unique_id is a 26-character globally unique identifier for this object, which makes the recycle bin name unique across all databasesversion is a version number assigned by the database
You can query objects that are in the recycle bin, just as you can query other objects. However, you must specify the name of the object as it is identified in the recycle bin. For example:
SQL> create table hr ( name varchar2(20));
Table created.
SQL> drop table hr;
Table dropped.
SQL> select * from recyclebin;
OBJECT_NAME ORIGINAL_NAME
OPERATION
------------------------------ --------------------------------
---------
TYPE TS_NAME CREATETIME
------------------------- ------------------------------ -------------------
DROPTIME DROPSCN PARTITION_NAME CAN CAN
------------------- ---------- -------------------------------- --- ---
RELATED BASE_OBJECT PURGE_OBJECT SPACE
---------- ----------- ------------ ----------
BIN$74ANPU11QhWw15ah+JM2gQ==$0 HR
DROPTABLE USERS 2006-11-16:17:56:252006-11-16:17:56:34
445402 YES YES
50424 50424 50424 8
SQL> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE
DROP TIME
---------------- ------------------------------ ------------ -------------------
HR BIN$74ANPU11QhWw15ah+JM2gQ==$0 TABLE
2006-11-16:17:56:34
SQL> select * from "BIN$74ANPU11QhWw15ah+JM2gQ==$0";
no rows selected
Restoring Tables from the Recycle Bin
SQL> flashback table hr to before drop rename to scott;
Flashback complete.
You can directly drop table using "purge" cluase.
if use "purge" clause then object not goes in recyclebin. it will delete parmanently.
SQL> drop table scott purge;
Table dropped.
You can also "purge" recyclebin
SQL> show recyclebin;ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME---------------- ------------------------------ ------------ -------------------
SCOTT BIN$QFb9kDMkRq2RpWJjjW9+ug==$0 TABLE 2006-11-16:18:14:26
SQL> purge recyclebin;
Recyclebin purged.
SQL> show recyclebin;
****************************************
Oracle Flashback Table: Returning Individual Tables to Past States
Row movement must be enabled on the table for which you are issuing the FLASHBACK TABLE statement. You can enable row movement with the following SQL statement
SQL> conn hr/hrConnected.
SQL> create table big_table as select * from all_objects;
Table created.
SQL> alter table big_table enable row movement;
Table altered.
SQL> alter session set nls_date_format = 'hh24:mi:ss';
Session altered.
SQL> select sysdate from dual;
SYSDATE
--------
18:43:49
SQL> select count(*) from big_table;
COUNT(*)
----------
46335
SQL> delete from big_table where rownum <= 445; 445 rows deleted. SQL> commit;
Commit complete.
SQL> flashback table big_table to TIMESTAMP 2 to_timestamp ( '2006-11-16 18:44:00','YYYY-MM-DD HH24:MI:SS');
Flashback complete.
SQL> select count(*) from big_table;
COUNT(*)
----------
46335
Flashback Query
you must choose an undo retention interval that is long enough to enable users to construct a snapshot of the database for the oldest version of the database that they are interested in.
SQL> conn sys as sysdba
Enter password:
Connected.
SQL> alter system set undo_retention = 3600;
System altered.
SQL> conn hr/hr
Connected.
SQL> alter session set nls_date_format = 'hh24:mi:ss';
Session altered.
SQL> select sysdate from dual;
SYSDATE
--------
19:04:49
SQL> create table hrtest ( no number, name varchar2(20));
Table created.
SQL> begin
2 insert into hrtest values ( 1, 'aaa');
3 insert into hrtest values ( 2, 'bbb');
4 insert into hrtest values ( 3, 'ccc');
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
SQL> select sysdate from dual;
SYSDATE
--------
19:06:23
SQL> delete from hrtest where name = 'ccc';
1 row deleted.
SQL> commit;
Commit complete.
SQL> select * from hrtest;
NO NAME
---------- --------------------
1 aaa
2 bbb
SQL> edWrote file afiedt.buf
1 select * from hrtest AS OF TIMESTAMP 2 to_timestamp ('2006-11-16 19:06:23','YYYY-MM-DD HH24:MI:SS')
3* where name = 'ccc'
4 /
NO NAME
---------- --------------------
3 ccc
SQL> insert into hrtest 2 ( select * from hrtest AS OF TIMESTAMP 3 to_timestamp ('2006-11-16 19:06:23','YYYY-MM-DD HH24:MI:SS')
4 where name = 'ccc');
1 row created.
SQL> select * from hrtest;
NO NAME
---------- --------------------
1 aaa
2 bbb
3 ccc
x-x-x-x-x-x-x-x-x-x-x-x-x-x-x-x-x-x-x-x-x-x-x-x-x-x-x-x-x-x-x-x-x-x-x
SQL> create table SCN_TEST (no number, name varchar2(20));
Table created.
SQL> insert into scn_test values (1,'a');
1 row created.
SQL> insert into scn_test values (2,'b');
1 row created.
SQL> insert into scn_test values (3,'c');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from scn_test;
NO NAME
---------- --------------------
1 a
2 b
3 c
SQL> show parameter undo_retention
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_retention integer 22719
SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
15893109
SQL> delete from scn_test where no = 2;
1 row deleted.
SQL> commit;
Commit complete.
SQL> select * from scn_test AS OF SCN 15893109 where no = 2;
NO NAME
---------- --------------------
2 b
SQL> insert into scn_test ( select * from scn_test AS OF SCN 15893109
2 where no = 2);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from scn_test;
NO NAME
---------- --------------------
1 a
3 c
2 b
************************
Using Flashback Version Query
SQL> select * from hrtest;
NO NAME
---------- --------------------
1 aaa
2 bbb
3 ccc
SQL> begin
2 insert into hrtest values ( 4, 'ddd');
3 insert into hrtest values ( 5, 'eee');
4 commit;
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> select sysdate from dual;
SYSDATE
--------
19:35:26
SQL> select sysdate from dual;
SYSDATE
--------
19:38:57
SQL> delete from hrtest where rownum <= 3;
3 rows deleted.
SQL> commit;
Commit complete.
SQL> begin
2 update hrtest set no = 0;
3 commit;
4 end;
5 /
PL/SQL procedure successfully completed.
SQL> select sysdate from dual;
SYSDATE
--------
19:48:42
SQL> select * from hrtest;
NO NAME
---------- --------------------
0 ddd
0 eee
SQL> ed
Wrote file afiedt.buf
1 SELECT versions_startscn, versions_starttime,
2 versions_endscn, versions_endtime,
3 versions_xid, versions_operation,
4 hrtest.*
5 FROM hrtest
6 VERSIONS BETWEEN TIMESTAMP
7 TO_TIMESTAMP('2006-11-16 19:35:00', 'YYYY-MM-DD HH24:MI:SS')
8 AND TO_TIMESTAMP('2006-11-16 19:48:00', 'YYYY-MM-DD HH24:MI:SS')
9* WHERE name in ('aaa','bbb','ccc');
SQL> column versions_starttime format a25
SQL> column versions_endtime format a25
SQL> /
VERSIONS_STARTSCN VERSIONS_STARTTIME
VERSIONS_ENDSCN
----------------- -------------------------
---------------
VERSIONS_ENDTIME VERSIONS_XID V NO NAME
------------------------- ---------------- - ---------- -------------------- 449718 16-NOV-06 07.39.10 PM
04000E0028000000 D 3 ccc
449718 16-NOV-06 07.39.10 PM
04000E0028000000 D 2 bbb
449718 16-NOV-06 07.39.10 PM
04000E0028000000 D 1 aaa
44971816-NOV-06 07.39.10 PM
1 aaa
44971816-NOV-06 07.39.10 PM
2 bbb
44971816-NOV-06 07.39.10 PM
3 ccc
6 rows selected.
No comments:
Post a Comment