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