11g DBA Basic Examples

Example



You can view the dropped objects in the recycle bin from two dictionary views:



USER_RECYCLEBIN: list all dropped user objects


DBA_RECYCLEBIN: list all dropped system-wide objects



SQL> create table test (col_a varcher(4));
Table created.



SQL> select object_name, original_name, type, createtime, droptime from user_recyclebin;
No rows selected.



SQL> drop table test;
Table dropped.



SQL> select object_name, original_name, type, createtime, droptime from user_recyclebin;



OBJECT_NAME                ORIGINAL_NAME TYPE   CREATETIME             DROPTIME


-------------------------------------    --------------------------          ---------------- -----------------------------------


BIN$0+ktoVCgEmXgNAAADiUEHQ = = $0 TEST TABLE 2009 – 06 – 21 : 19 : 04 : 03 2009 – 06 – 21 : 19 : 04 : 41



SQL> create table test (col_b varchar(4));
Table created.




SQL> select object_name, original_name, type, createtime, droptime from user_recyclebin;



OBJECT_NAME           ORIGINAL_NAME TYPE     CREATETIME        DROPTIME


----------------------------- --------------------------------------  -------------------------- -----------------------------------


BIN$0+ktoVCgEmXgNAAADiUEHQ = = $0 TEST TABLE 2009 – 06 – 21 : 19 : 04 : 03 2009 – 06 – 21 : 19 : 04 : 41



SQL> drop table test;
Table dropped.



SQL> select object_name, original_name, type, createtime, droptime from user_recyclebin;



OBJECT_NAME     ORIGINAL_NAME TYPE       CREATETIME         DROPTIME


---------------------------- --------------------------        --------------------------- -----------------------------------


BIN$0+ktoVChEmXgNAAADiUEHQ = = $0 TEST TABLE 2009 – 06 – 21 : 19 : 07 : 33 2009 – 06 – 21 : 19 : 08 : 17


BIN$0+ktoVCgEmXgNAAADiUEHQ = = $0 TEST TABLE


2009 – 06 – 21 : 19 : 04 : 03 2009 – 06 – 21 : 19 : 04 : 41




This example will restore a dropped table test


SQL> flashback table “BIN$0+ktoVChEmXgNAAADiUEHQ = = $0” to before drop;
Flashback complete.



This example removes the table permanently:


SQL> drop table test purge;


This example removes the table in the recycle bin:



SQL> purge table “BIN$0+ktoVChEmXgNAAADiUEHQ = = $0”;
Table purged.



You can only issue this command when the tablespace users is empty. Object in the recycle bin of tablespace users will be purged:



SQL> drop tablespace users;



When you issue this command, objects in the tablespace users are dropped. They are not placed in the recycle bin. Any objects in the recycle bin belonging to the tablespace users are purged.



SQL> drop tablespace users including contents;




This example purges the user recycle bin:


SQL> purge recyclebin;
Recycle bin purged.


This example removes all objects from the recycle bin:



SQL> purge dba_recyclebin;
DBA Recycle bin purged.



This example purges all objects from tablespace users in the recycle bin:


SQL> purge tablespace users;
Tablespace purged.




Keep track the changing of data in table:


Example



SQL> create table emp (name varchar2(10), salary number(8,2));



SQL> insert into emp values (‘AHMAD’, 2000);
1 row inserted.



SQL> commit;
Commit complete.



SQL> select * from emp;



NAME            SALARY


----------------- --------------


AHMAD              2000




SQL> update emp set salary = 3000 where name = ‘AHMAD’;
1 row updated.



SQL> commit;
Commit complete.



SQL> select * from emp;



NAME               SALARY


----------------- --------------


AHMAD            3000



SQL> select * from versions between scn minvalue and maxvalue;



NAME              SALARY


----------------- --------------


AHMAD              3000


AHMAD              2000

Comments

Popular posts from this blog

Create Tables in Oracle Database using TOAD

How to create Oracle stored Procedures using TOAD for Oracle

How to Create Packages in Oracle Database using TOAD for Oracle