FLASHBACK QUERY
Used to retrieve the data which has been already committed
with out going for recovery.
Flashbacks are of two types
1
Time base flashback
2
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
b)
Indexing not possible
c)
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
REF DEREF VALUE
REF
1 The ref function allows referencing of
existing row objects.
2 Each of the row objects has an object
id value assigned to it.
3 The object id assigned can be seen by
using ref function.
DEREF
1 The deref function per\ opposite
action.
2 It takes a reference value of object
id and returns the value of the row objects.
VALUE
1 Even though the primary table is
object table, still it displays the rows in general format.
2 To display the entire structure of the
object, this will be used.
Ex:
1) create vendot_adt type
SQL> Create type vendor_adt as object
(vendor_code number(2), vendor_name
varchar(2), vendor_address varchar(10));/
2) create object tables vendors and vendors1
SQL> Create table vendors of
vendor_adt;
SQL> Create table vendors1 of
vendor_adt;
3) insert the data into object tables
SQL> insert into vendors values(1, ‘a’, ‘hyd’);
SQL> insert into vendors values(2, ‘b’,
‘bang’);
SQL> insert into vendors1 values(3,
‘c’, ‘delhi’);
SQL> insert into vendors1 values(4,
‘d’, ‘chennai’);
4) create another table orders which holds
the vendor_adt type also.
SQL> Create table orders (order_no number(2), vendor_info ref
vendor_adt);
Or
SQL> Create table orders (order_no number(2), vendor_info ref
vendor_adt with
rowid);
5) insert the data into orders table
The vendor_info column in the following syntaxes will store
object id of any table
which is referenced by vendor_adt
object ( both vendors and vendors1).
SQL> insert into orders values(11,(select ref(v) from vendors v
where vendor_code
= 1));
SQL> insert into orders values(12,(select ref(v) from vendors v
where vendor_code
= 2));
SQL> insert into orders values(13,(select ref(v1) from vendors1 v1
where
vendor_code = 1));
SQL> insert into orders values(14,(select ref(v1) from vendors1 v1
where
vendor_code = 1));
6) To see the object ids of vendor table
SQL> Select ref(V) from vendors v;
7) If you see the vendor_info of orders it
will show only the object ids not the values,
to see the values
SQL> Select deref(o.vendor_info) from
orders o;
8) Even though the vendors table is object
table it will not show the adt along with
data, to see the data along with the
adt
SQL>Select * from vendors;
This will give the data without adt.
SQL>Select value(v) from vendors v;
This will give the columns data along
wih the type.
REF
CONSTRAINTS
1 Ref can also acts as constraint.
2 Even though vendors1 also holding
vendor_adt, the orders table will store the object ids of vendors only because
it is constrained to that table only.
3 The vendor_info column in the
following syntaxes will store object ids of vendors only.
SQL> Create table orders (order_no
number(2), vendor_info ref vendor_adt scope is
vendors);
Or
SQL> Create table orders (order_no
number(2), vendor_info ref vendor_adt constraint fk
references vendors);
No comments:
Post a Comment