08/15/10
Author: Manju Narem Introduction to Flashback Technology Before Oracle 9i release 2, the only way to use the flashback query feature was through the use of package DBMS_FLASHBACK. To use this package, the user had to specify the intention to enter the flashback mode by supplying the time to which the user wished to go back to. To enter the flashback mode use the ENABLE_AT_TIME function of the DBMS_FLASHBACK package. To resume normal operation use DISABLE function. Eg: 1 DECLARE 2 CURSOR emp_cur IS 3 SELECT * FROM EMPLOYEE; 4 e_rec emp_cur%rowtype; 5 BEGIN 6 DBMS_FLASHBACK.ENABLE_AT_TIME (’20-AUG-07 9:40:50’); 7 open emp_cur; 8 DBMS_FLASHBACK.DISABLE; 9 LOOP 10 fetch emp_cur into e_rec; 11 EXIT WHEN emp_cur%NOTFOUND; 12 INSERT INTO Employee VALUES 13 (e_rec.emp_id, 14 e_rec.first_name, 15 e_rec.last_name, 16 e_rec.dept_id); 17 END LOOP; 18 CLOSE emp_cur; 19 COMMIT; 20 END;
To recover data into out Employee table using the SCN number instead of time as shown above, replace line 6 with the line below. DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER (10500401199); Until we exit flashback query mode by issuing DBMS_FLASHBACK.DISABLE DML statements INSERT, UPDATE, or DELETE are not allowed. If a DML statement has been issued, it must be committed before we can enter the flashback mode. FLASHBACK mode can be entered only at the beginning of a transaction. Flashback Table Flashback Table provides the ability to recover a table or a set of tables to a specified point in time easily when online. Flashback Table restores the tables with its associated attributes, current indexes, triggers and constraints. It does not require to find and restore application specific properties. Flashback Table
EG: FLASHBACK TABLE Employee TO TIMESTAMP (’20-AUG-07 9:40:50’,'DD-MON-YY HH24: MI: SS');
Before discussing FLASHBACK DROP, I will discuss about RECYCLE BIN
RECYCLE BIN
The Recycle Bin is a virtual container where all dropped objects reside. The dropped objects occupy the same space as when they were created. Once the tables are dropped any associated objects such as indexes, constraints, nested tables, and other dependant objects are prefixed with BIN$, they are not moved. You can continue to access the data in a dropped table or even use Flashback Query against it. Each user has the same rights and privileges on Recycle Bin objects before they were dropped. You can view your dropped tables by querying the new RECYCLEBIN view.
EG: SELECT Object_Name, Original_Name, Type FROM User_Recyclebin;
Objects in the Recycle Bin will remain in the database until the owner of the dropped objects decides to permanently remove them using the new PURGE command. You can delete a single item in Recycle Bin or empty all items using PURGE
EG: PURGE TABLE Employee; --Single Item Purge PURGE recyclebin; --All Items Purge
The Recycle Bin objects are counted against a user's quota. Objects in the Recycle Bin will be automatically purged by the space reclamation process if
Flashback Drop Flashback Drop provides a safety when dropping objects in Oracle Database 10g. When a user drops a table, Oracle automatically places it into the Recycle Bin. If you drop any thing, you can undrop it with Flashback Drop. EG: FLASHBACK TABLE Employee TO BEFORE DROP; Flashback Database Flashback Database is faster than traditional point-in-time recovery. The traditional recovery method uses backups and redo log files; Flashback Database uses a new type of log file called the Flashback Database log. The Oracle database server periodically logs before images of data blocks in the Flashback Database logs. The data block images are used to quickly back out changes to the database during Flashback Database. Flashback Database reduces the time required to recover the database to a point in time. The time to restore a database is proportional to the number of changes that need to be backed out, not the size of the database. Once Flashback Database is enabled, a new RVWR background process is started. This process is similar to the LGWR (log writer) process. The new process writes Flashback Database data to the Flashback Database logs. You can enable Flashback Database using the following steps: 1. Make sure the database is in archive mode. 2. Configure the recovery area by setting the two parameters:
3. Open the database in MOUNT EXCLUSIVE mode and turn on the flashback feature: SQL> STARTUP MOUNT EXCLUSIVE; 4. Set the Flashback Database retention target:
To enable Flashback Database, Issue the following command: SQL> select flashback_on from v$database; To disable disable Flashback Database, issue the following command SQL> ALTER DATABASE FLASHBACK OFF; Monitor logging in the Flashback Database logs:
SQL> select begin_time, flashback_data, db_data, redo_data, ESTIMATED_FLASHBACK_SIZE from v$flashback_database_stat;
Monitor the Flashback Database retention target: SQL> select * from v$flashback_database_log;
Note: The default value for flashback retention time is 1400 minutes.
The destination and the size of the recovery area are setup using the db_recovery_file_dest and b_recovery_file_dest_size initializatin parameters. SQL> select estimated_flashback_size from v$flashback_database_log;
Flashback Versions Query Flashback Versions Query provides a way to audit the rows of a table and retrieve information about the transactions that changed the rows. It retrieves all committed versions of the rows that existed between the time the query was issued and a point in time in the past. It is achived by using Automatic Undo Management. For any table, a new row version is created every time the COMMIT statement is executed. The Flashback Versions Query returns a row for each version of the row that existed in the time interval you specify The Flashback Versions Query is invoked by using the VERSIONS BETWEEN clause of the SELECT statement. It takes two forms as follows:
The lower and upper boundaries can either be specific timestamps/SCNs or the keywords MINVALUE and MAXVALUE. These keywords instruct Oracle to retrieve all available data versions. The age of the data available is determined by the undo_retention parameter. Oracle provides a variety of metadata with each version of our data. The metadata is exposed via a number of pseudo-columns that we can use with our flashback version queries. These pseudo-columns are as follows:
Eg: SQL> SELECT z, VERSIONS_STARTTIME, VERSIONS_ENDTIME, VERSIONS_XID, VERSIONS_OPERATION FROM EMPLOYEE VERSIONS BETWEEN TIMESTAMP MINVALUE AND MAXVALUE ORDER BY VERSIONS_ENDTIME; Flashback Transaction Query Flashback Transaction Query is used to identify other changes that were done by the transaction and to request the undo SQL to reverse those changes, once you identify an erroneous transaction. The FLASHBACK_TRANSACTION_QUERY view gives transaction history and undo SQL. This includes the SQL required to reverse each change. Queries against this view are documented as "flashback transaction queries" and require the SELECT ANY TRANSACTION system privilege. Eg: SQL> SELECT xid,operation, undo_sql FROM flashback_transaction_query WHERE table_owner = USER AND table_name = 'EMPLOYEE' ORDER BY start_timestamp;
The VERSIONS_XID pseudo-column gives us the key to this view (the XID column) for specific versions of data. The UNDO_SQL column shows us the reversal of every change we made.
Note: Oracle 10g provides two conversion functions, TIMESTAMP_TO_SCN and SCN_TO_TIMESTAMP to convert between timestamp and SCN. Timestamps are mapped to SCNs with a precision of approximately 3 seconds (in 9i this was 5 minutes). SYSTIMESTAMP and LOCALTIMESTAMP functions are used to capture timestamps, we can capture SCNs using the DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER function.
Summary
Flashback revolutionizes recovery by operating on just the changed data and on other hand, there will be no down time needed. Flashback technology removes the complexity of recovery while decreasing the time it takes to recover from unpredictable human errors
05/01/10
REF CURSOR is nothing but a dynamic curosr which points a select statement at run time.So we can have multiple select statements defined for a particular cursor unlike static cursors. There are basically two types of REF cursors: 1 STRONG REF CURSOR 2 WEAK REF CURSOR STRONG REF CURSOR STRONG REF CURSORS are those ref cursors that have a return type defined. The structure of the SELECT statement (the number and datatypes of the columns) must match or be compatible with the structure specified in the RETURN clause of the curosr type declaration statement. DECLARE OPEN c1 FOR SELECT * FROM EMPLOYEES; OPEN c1 FOR SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID=101; We can defined multiple select statements here for cursor c1 but of only employees rowtype as we have mentioned the return type to be of employees type. For any type of select statements we can use weak ref cursors. WEAK REF CURSOR Weak ref cursors can be used for any type of select statments unlike strong ref cursors. This is because we don't have a return type declared for the type statements. DECLARE So just a simple rule for coding with ref cursor is to declare a type ref cursor. Assign it to a cursor variable.While opening the cursor attach a for select statement with it. Thats it . And everything else remains same as it is for a code developed using static cursors. Now it depends on you how to use them. Use strong type ref cursor if you need records to be fetched from a particular type of table. Use weak ref cursor if you are going to fetch record from different tables with different datatypes and columns.
04/17/10
In this article i will be explaining you the meaning and use of associative arrays. Associative arrays are one of the types of the collections present in pl/sql. An associative array is a type of collection in Oracle. Associative arrays are sets of key-value pairs where each key is unique and is used to locate a corresponding value in the array.They are similar to the index-by tables present in pl/sql 8. What i believe is that we, human beings learns and remember more when we do a particular work. So instead of giving you a one page lecture let me explain this to you with the help of a small example. Now i suggest you after reading this please practice the program once in the HR schema of your oracle database. CREATE OR REPLACE PROCEDURE emp_arr Now let me explain each line one by one ,so that it will be easier for you all to understand. LINE-1 - The "type-name" is a user-defined data type which holds a set or in a better way,you can say table of values. It is keyed with a PLS_INTEGER(it is a subtype of data type integer) index . Always remember that the index should be based on PLS_INTEGER,BINARY_INTEGER OR VARCHAR2. LINE-2 This is a composite variable(associative array) of type type-name. LINE-3 This is a FOR loop to assign values to the associative array.You can see the index as well as the values are updated altogether. LINE-4 The statement gives the first key available within the associative array. LINE-5 This is loop which always checks whether the current element is less than the last key. LINE-6 The statement searches for the next key available after the current key stored by the variable "i".The new "key" is assigned to the same variable and loop gets treated accordingly. Now i hope you might have got a brief idea about how to use the associative arrays. Very soon i will come up with my next article on collections. If you have any doubts you can post your question in our oraclepassport forum. Any of our team member will be fast enough to clear your doubt or you can write to me at sibanjan_das@yahoo.com . 04/10/10
Collections are used to define our own composite data types in pl/sql.These are like the same data structures used in third generation languages. They are group of elements of the same type. Collections can be passed as parameters. So, you can usethem to move columns of data into and out of database tables, or between client-side applications and stored subprograms. You can define collection types in a PL/SQL package and then use the same types across many applications. The different types of collections are 1. NESTED TABLE 2. ASSOCIATIVE ARRAYS 3. VARRAYS. In my next article I will explain the types one by one
03/20/10
A transaction is a logical unit of work in which, all the steps must be performed or none. ACID stands for Atomicity, Consistency, Isolation, Durability. These are the properties of a transaction.
Read more about ACID from our Wiki : http://www.oraclepassport.com/wiki/index.php?title=ACID Tag cloud«all_source table»,«alter data type of column having data»,«ascii for newline and tab»,«b-tree indexes»,«bitmap indexes»,«bulk collect»,cardinality,«column constraints»,«constraints on a table»,«control files»,cube,«ddl commands inside pl/sql proc»,«debugging a procedure»,«delete duplicates»,«describing a package body in sql plus»,«duplicate records»,«dynamic table creation in stored procedure»,«export saved database connections in toad»,«grants of an object»,«mutliline data in oracle»,«number of columns in a table»,«oracle aggregate functions»,«oracle data loader»,«oracle product version»,«oracle version»,«pragma autonomous_transaction»,rollup,«source code of a package»,«special characters in varchar2 column»,sql*loader,«toad database connections file»,user_tab_columns,user_tab_privs |
Hit Counter |
03:01:43 pm, by 
XML Feeds