SQL-(FLASHBACK QUERY,EXTERNAL TABLES )

FLASHBACK QUERY

Used to retrieve the data which has been already committed with out going for recovery.

Flashbacks are of two types

Ø  Time base flashback

Ø  SCN based flashback (SCN stands for System Change Number)

Ex:

1) Using time based flashback

     a) SQL> Select *from student;

          -- This will display all the rows

     b) SQL> Delete student;

     c) SQL> Commit;              -- this will commit the work.

     d) SQL> Select *from student;

         -- Here it will display nothing

     e) Then execute the following procedures

         SQL> Exec dbms_flashback.enable_at_time(sysdate-2/1440)

     f) SQL> Select *from student;

         -- Here it will display the lost data

         -- The lost data will come but the current system time was used

     g) SQL> Exec dbms_flashback.disable

          -- Here we have to disable the flashback to enable it again

2) Using SCN based flashback

     a) Declare a variable to store SCN

          SQL> Variable s number

     b) Get the SCN

          SQL> Exec :s := exec dbms_flashback.get_system_change_number

     c) To see the SCN

         SQL> Print s

     d) Then execute the following procedures

          SQL> Exec dbms_flashback.enable_at_system_change_number(:s)

          SQL> Exec dbms_flashback.disable

EXTERNAL TABLES

You can user external table feature to access external files as if they are tables inside the database.

When you create an external table, you define its structure and location with in oracle.

When you query the table, oracle reads the external table and returns the results just as if the data had been stored with in the database.

ACCESSING EXTERNAL TABLE DATA

To access external files from within oracle, you must first use the create directory command to define a directory object pointing to the external file location

Users who will access the external files must have the read and write privilege on the directory.

Ex:

CREATING DIRECTORY AND OS LEVEL FILE

    SQL> Sqlplus system/manager

    SQL> Create directory saketh_dir as ‘/Visdb/visdb/9.2.0/external’;

     SQL> Grant all on directory saketh_dir to saketh;

     SQL> Conn saketh/saketh

     SQL> Spool dept.lst

     SQL> Select deptno || ‘,’ || dname || ‘,’ || loc from dept;

     SQL> Spool off

CREATING EXTERNAL TABLE

 SQL> Create table dept_ext

         (deptno number(2),

         Dname varchar(14),

         Loc varchar(13))

         Organization external  ( type oracle_loader

                                                 Default directory saketh_dir

                                                 Access parameters

                                                 ( records delimited by newline

                                                    Fields terminated by “,”

                                                    ( deptno number(2),

                                                      Dname varchar(14),

                                                      Loc varchar(13)))

         Location (‘/Visdb/visdb/9.2.0/dept.lst’));

SELECTING DATA FROM EXTERNAL TABLE

SQL> select * from dept_ext;

This will read from dept.lst which is a operating system level file.

LIMITATIONS ON EXTERNAL TABLES

a)   You can not perform insert, update, and delete operations

a)   Indexing not possible

b)   Constraints not possible

BENEFITS OF EXTERNAL TABLES

a)   Queries of external tables complete very quickly even though a full table scan id required with each access

b)   You can join external tables to each other or to standard tables