oracle sql and dba tutorial logo

Oracle DBA

Learn Oracle 11g / 12c Database Admin step by step



Oracle SQL

Tutorial for Oracle SQL

 

Oracle DBA Interview Questions

Most asked Oracle DBA Interview Questions.

60 Technical Questions
42 Backup & Recovery Questions

Unix For Oracle DBA 20 Questions


Download Oracle 11g / 12 c Software

Links to Download Oracle 12c / 11g for Linux, Windows etc.



Oracle Flash Back Query

From Oracle Ver. 9i Oracle has introduced Flashback Query feature.  It is useful to recover from accidental statement failures. For example, suppose a user accidently deletes rows from a table and commits it also then, using flash back query he can get back the rows.

Flashback feature depends upon on how much undo retention time you have specified. If you have set the UNDO_RETENTION parameter to 2 hours then, Oracle will not overwrite the data in undo tablespace even after committing until 2 Hours have passed. Users can recover from their mistakes made since last 2 hours only.

For example, suppose John gives a delete statement at 10 AM and commits it. After 1 hour he realizes that delete statement is mistakenly performed. Now he can give a flashback  AS.. OF query to get back the deleted rows like this.

Flashback Query

SQL>select * from emp as of timestamp sysdate-1/24;

Or

SQL> SELECT * FROM emp AS OF TIMESTAMP
      TO_TIMESTAMP('2007-06-07 10:00:00', 'YYYY-MM-DD HH:MI:SS')

To insert the accidently deleted rows again in the table he can type

SQL> insert into emp (select * from emp as of timestamp sysdate-1/24)

Using Flashback Version Query

You use a Flashback Version Query to retrieve the different versions of specific rows that existed during a given time interval. A new row version is created whenever a COMMIT statement is executed.

The Flashback Version Query returns a table with a row for each version of the row that existed at any time during the time interval you specify. Each row in the table includes pseudocolumns of metadata about the row version. The pseudocolumns available are

VERSIONS_XID                         :Identifier of the transaction that created the row version
VERSIONS_OPERATION          :Operation Performed. I for Insert, U for Update, D for Delete
VERSIONS_STARTSCN            :Starting System Change Number when the row version was created
VERSIONS_STARTTIME          :Starting System Change Time when the row version was created
VERSIONS_ENDSCN                :SCN when the row version expired.
VERSIONS_ENDTIME              :Timestamp when the row version expired

To understand let’s see the following example

Before Starting this example let’s us collect the Timestamp

SQL> select to_char(SYSTIMESTAMP,’YYYY-MM-DD HH:MI:SS’) from dual;

TO_CHAR(SYSTIMESTAMP,’YYYYY
---------------------------
2007-06-19 20:30:43

Suppose a user creates a emp table and inserts a row into it and commits the row.

SQL> Create table emp (empno number(5),name varchar2(20),sal  
                                number(10,2));

SQL> insert into emp values (101,’Sami’,5000);
SQL>commit;

At this time emp table has one version of one row.

Now a user sitting at another machine erroneously changes the Salary from 5000 to 2000 using Update statement

SQL> update emp set sal=sal-3000 where empno=101;
SQL> commit;

Subsequently, a  new transaction updates the name of the employee from Sami to Smith.

SQL>update emp set name=’Smith’ where empno=101;
SQL> commit;

At this point, the DBA detects the application error and needs to diagnose the problem. The DBA issues the following query to retrieve versions of the rows in the emp table that correspond to empno 101. The query uses Flashback Version Query pseudocolumns

SQL> Connect / as sysdba
SQL> column versions_starttime format a16
SQL> column versions_endtime format a16
SQL> set linesize 120;


SQL> select versions_xid,versions_starttime,versions_endtime,
   versions_operation,empno,name,sal from emp versions between
   timestamp to_timestamp(‘2007-06-19 20:30:00’,’yyyy-mm-dd hh:mi:ss’)
    and to_timestamp(‘2007-06-19 21:00:00’,’yyyy-mm-dd hh:mi:ss’);

VERSION_XID  V STARTSCN  ENDSCN  EMPNO  NAME       SAL
-----------  - --------  ------  -----  --------   ----
0200100020D  U 11323                101  SMITH     2000      
02001003C02  U 11345                101  SAMI      2000
0002302C03A  I 12320                101  SAMI      5000

 

The Output should be read from bottom to top, from the output we can see that an Insert has taken place and then  erroneous update has taken place and then again update has taken place to change the name.

The DBA identifies the transaction 02001003C02 as erroneous and issues the following query to get the SQL command to undo the change

SQL> select operation,logon_user,undo_sql
      from flashback_transaction_query
           where xid=HEXTORAW(’02001003C02’);

OPERATION  LOGON_USER UNDO_SQL
---------  ---------- ---------------------------------------

U           SCOTT       update emp set sal=5000 where ROWID = 
                                                   'AAAKD2AABAAAJ29AAA'

Now DBA can execute the command to undo the changes made by the user

SQL> update emp set sal=5000 where ROWID ='AAAKD2AABAAAJ29AAA'

1 row updated

Using Flashback Table to return Table to Past States.

Oracle Flashback Table provides the DBA the ability to recover a table or set of tables to a specified point in time in the past very quickly, easily, and without taking any part of the database offline. In many cases, Flashback Table eliminates the need to perform more complicated point-in-time recovery operations.

Flashback Table uses information in the undo tablespace to restore the table. Therefore, UNDO_RETENTION parameter is significant in Flashing Back Tables to a past state. You can only flash back tables up to the retention time you specified.

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:

ALTER TABLE table ENABLE ROW MOVEMENT;

The following example performs a FLASHBACK TABLE operation the table emp

FLASHBACK TABLE emp TO TIMESTAMP
   TO_TIMESTAMP('2007-06-19 09:30:00', `YYYY-MM-DD HH24:MI:SS');

The emp table is restored to its state when the database was at the time specified by the timestamp.

Example:-
At 17:00 an HR administrator discovers that an employee "JOHN" is missing from the EMPLOYEE table. This employee was present at 14:00, the last time she ran a report. Someone accidentally deleted the record for "JOHN" between 14:00 and the present time. She uses Flashback Table to return the table to its state at 14:00, as shown in this example:

FLASHBACK TABLE EMPLOYEES TO TIMESTAMP
      TO_TIMESTAMP('2007-06-21 14:00:00','YYYY-MM-DD HH:MI:SS')
      ENABLE TRIGGERS;

You have to give ENABLE TRIGGERS option otherwise, by default all database triggers on the table will be disabled.

Recovering Drop Tables (Undo Drop Table)

In Oracle Ver. 10g  Oracle introduced the concept of Recycle Bin i.e. whatever tables you drop the database does not immediately remove the space used by table. Instead, the table is renamed and placed in Recycle Bin. The FLASHBACK TABLE…BEFORE DROP command will restore the table.

This feature is not dependent on UNDO TABLESPACE so UNDO_RETENTION parameter has no impact on this feature.

To Recover Drop tables the RECYCLEBIN parameter must be turned ON, otherwise you will not be able to recover drop tables

 For Example, suppose a user accidently drops emp table

SQL>drop table emp;

Table Dropped

Now for user it appears that table is dropped but it is actually renamed and placed in Recycle Bin. To recover this dropped table a user can type the command

SQL> Flashback table emp to before drop;

You can also restore the dropped table by giving it a different name like this

SQL> Flashback table emp to before drop rename to emp2;

Purging Objects from Recycle Bin

If you want to recover the space used by a dropped table give the following command

SQL> purge table emp;

If you want  to purge objects of  logon user  give the following command

SQL> purge recycle bin;

If you want to recover space for dropped objects of a particular tablespace give the command

SQL> purge tablespace hr;

You can also purge only objects from a tablespace belonging to a specific user, using the following form of the command:

SQL> PURGE TABLESPACE hr USER scott;

If you have the SYSDBA privilege, then you can purge all objects from the recycle bin, regardless of which user owns the objects, using this command:

SQL> PURGE DBA_RECYCLEBIN;

To view the contents of Recycle Bin give the following command

SQL> show recycle bin;

Permanently Dropping Tables

If you want to permanently drop tables without putting it into Recycle Bin, drop tables with purge command like this

SQL> drop table emp purge;

This will drop the table permanently and it cannot be restored.

Flashback Drop of Multiple Objects With the Same Original Name

You can create, and then drop, several objects with the same original name, and they will all be stored in the recycle bin. For example, consider these SQL statements:

CREATE TABLE EMP ( ...columns ); # EMP version 1

DROP TABLE EMP;

CREATE TABLE EMP ( ...columns ); # EMP version 2

DROP TABLE EMP;

CREATE TABLE EMP ( ...columns ); # EMP version 3

DROP TABLE EMP;

In such a case, each table EMP is assigned a unique name in the recycle bin when it is dropped. You can use a FLASHBACK TABLE... TO BEFORE DROP statement with the original name of the table, as shown in this example:

FLASHBACK TABLE EMP TO BEFORE DROP;

The most recently dropped table with that original name is retrieved from the recycle bin, with its original name. You can retrieve it and assign it a new name using a RENAME TO clause. The following example shows the retrieval from the recycle bin of all three dropped EMP tables from the previous example, with each assigned a new name:

FLASHBACK TABLE EMP TO BEFORE DROP RENAME TO EMP_VER_3;

FLASHBACK TABLE EMP TO BEFORE DROP RENAME TO EMP_VER_2;

FLASHBACK TABLE EMP TO BEFORE DROP RENAME TO EMP_VER_1;

Important Points:

1.  There is no guarantee that objects will remain in Recycle Bin. Oracle might empty recycle bin whenever Space Pressure occurs i.e. whenever tablespace becomes full and transaction requires new extents then, oracle will delete objects from recycle bin

2.   A table and all of its dependent objects (indexes, LOB segments, nested tables, triggers, constraints and so on) go into the recycle bin together, when you drop the table. Likewise, when you perform Flashback Drop, the objects are generally all retrieved together.

3.   There is no fixed amount of space allocated to the recycle bin, and no guarantee as to how long dropped objects remain in the recycle bin. Depending upon system activity, a dropped object may remain in the recycle bin for seconds, or for months

 

 


HomeContact Us

Data Loader

Data Loader is a simple yet powerful tool to
export and import Data between many common database formats


Forms Data Loader

Tool to load data into Oracle E-Business Suite R12 / Oracle Apps using Macros and Forms Record and Playback

Interface Computers Academy © 2007-2017 All Rights Reserved