Impact of Row Length on actual PCTFREE

The PCTFREE storage parameter is essential to how the database manages free space. This SQL parameter sets the minimum percentage of a data reserved as free space for updates to existing rows. Oracle will not insert new rows into existing blocks if INSERTs would cause free space to fall below PCTFREE threshold.

It's important to note that Row Length has impact on actual PCTFREE and quite serious impact on actual PCTFREE for very large tables. If you have a table with row length comparable to the space reserved by PCTFREE, then your actual PCTFREE may be twice as  much and you could end up wasting lots of space, and impact performance of full table scans.

For example, let's say that you have a table with row length of 600 bytes. Block size is 8K. PCTFREE is 10. You may end up with approximately 800 + 600 = 1,400 bytes of free space within the data blocks. You would have expected 800 bytes of free space.

Let's prove the point with a table rebuild. In this example, table takes up more space after the rebuilds due to actual PCTFREE larger than designated PCTFREE.

Case I - Before Rebuild

JMEHTA HRP> SELECT PCT_FREE, BLOCKS, EMPTY_BLOCKS, AVG_ROW_LEN

  2  FROM DBA_TABLES

  3  WHERE TABLE_NAME = 'TAB1';

  PCT_FREE     BLOCKS EMPTY_BLOCKS AVG_ROW_LEN 

---------- ---------- ------------ -----------

        10      93997          378         613      

Case II - After the Rebuild with the same PCTFREE

JMEHTA HRP> SELECT PCT_FREE, BLOCKS, EMPTY_BLOCKS, AVG_ROW_LEN

  2  FROM DBA_TABLES

  3  WHERE TABLE_NAME = TAB1';

  PCT_FREE     BLOCKS EMPTY_BLOCKS AVG_ROW_LEN  

 ---------- ---------- ------------ ----------- 

        10     102589         1090         613       

Case II - After the Rebuild with the PCTFREE = 2

JMEHTA HRP> SELECT PCT_FREE, BLOCKS, EMPTY_BLOCKS, AVG_ROW_LEN

  2  FROM DBA_TABLES

  3  WHERE TABLE_NAME = TAB1';

  PCT_FREE     BLOCKS EMPTY_BLOCKS AVG_ROW_LEN 

---------- ---------- ------------ -----------

         2      93924            0         613         

As you could see, the same table with same PCTFREE after the rebuild took approximately 8,000 more blocks! You would have expected the table to take up less space.

It's advisable to use lower values of PCTFREE for tables with larger row lengths.

Flashback Database

 

Oracle Flashback database is one of the jazziest features of Oracle10g database. As a starter, it has a cool name - Flashback. In addition, Flashback is a powerful word. It sounds like a Hollywood or Bollywood flick theme. It implies turning back the time or looking in the rearview mirror. Thanks to Oracle for lightening up tedious work that goes into database administration. And thanks to Oracle for providing a feature as powerful as the word itself.

Oracle flashback database feature lets you unwind the database easily and quickly to a previous point-in-time. There is no need to restore the backups and perform time-consuming incomplete media recovery. You just need few flashback commands, and commands are simple.

But note that flashback database command, as name suggests, operates at the database level. You will flashback the whole database, not just few schemas or just few tables.

As shown below, You can flashback database with or without FLASHBACK mode which is activated through ALTER DATABASE FLASHBACK ON command when the database is in MOUNT mode.

Flashback database with FLASHBACK mode

• You must turn on FLASHBACK when database is in MOUNT mode.
• Flashback Recovery Window is specified by db_flashback_retention_target init.ora parameter. Please note that this parameter specifies a potential target. Your actual mileage may vary depending upon disk space available in the flash recovery area.
• Flashback to any point-in-time or SCN within the range specified by db_flashback_retention_target.
• No need to create normal restore points, but should be created as aliases to represent SCN or time.

Flashback database without FLASHBACK mode

• Flashback database to a known baseline, also known as pre-defined guaranteed restore point.
• You must explicitly define guaranteed restore points.
• Oracle does generate flashback logs, but you can only flashback to a pre-defined guaranteed restore point. You need to make sure that you have enough disk space to store flashback logs that Oracle would generate.

My databases are not in FLASHBACK mode, so I can't perform media recovery using the flashback feature, but I usually define guaranteed restore points when applications or data upgrade operations have potential of failures, and may require rollback.

Let's look at the examples to understand Oracle Flashback features

Example 1 - Flashback database without FLASHBACK mode

Step 1: Define initialization Parameters

SQL> show parameter db_recovery_file

NAME                                 TYPE        VALUE                         
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      e:\oraflash                   
db_recovery_file_dest_size           big integer 19531250K                     

SQL> show parameter db_flashback_retention_target

NAME                                 TYPE        VALUE                         
------------------------------------ ----------- ------------------------------
db_flashback_retention_target        integer     1440                          

Step 2: Create Guaranteed Restore Points

One important point to note is that the first guarantee restore point must be created when the database is in MOUNT mode. Subsequent mount points can be created while DB is open.

SQL> create restore point test guarantee flashback database ;
create restore point test guarantee flashback database
*
ERROR at line 1:
ORA-38784: Cannot create restore point 'TEST'.
ORA-38787: Creating the first guaranteed restore point requires mount mode when
flashback database is off.

SQL> shutdown immediate ;

SQL> startup mount ;

SQL> create restore point test guarantee flashback database ;

Restore point created.

SQL> alter database open ;

Database altered.

SQL> create restore point test1 guarantee flashback database ;

Restore point created.

SQL> select * from v$restore_point ;

       SCN DATABASE_INCARNATION# GUA STORAGE_SIZE                              
---------- --------------------- --- ------------                              
TIME                                                                           
---------------------------------------------------------------------------    
NAME                                                                           
--------------------------------------------------------------------------------
 114645657                     2 YES            0                              
13-FEB-11 12.16.58.000000000 PM                                                
TEST                                                                           
                                                                               
 114645903                     2 YES      8192000                              
13-FEB-11 12.17.39.000000000 PM                                                
TEST1                                                                          

                                                                            
Step 3: Flashback Database to a Guaranteed Restore Point

Two noteworthy points are that database must be in MOUNT mode in order to flashback and database must be opened withh RESETLOGS.

SQL> flashback database to restore point test1 ;
flashback database to restore point test1
*
ERROR at line 1:
ORA-38757: Database must be mounted and not open to FLASHBACK.

SQL> shutdown immediate ;

SQL> startup mount ;

SQL> flashback database to restore point test1 ;

Flashback complete.

SQL> alter database open ;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open resetlogs ;

Database altered.

Example 2 - Flashback database with FLASHBACK mode

Step 1: Define initialization Parameters

SQL> show parameter db_recovery_file

NAME                                 TYPE        VALUE                         
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      e:\oraflash                   
db_recovery_file_dest_size           big integer 19531250K                     

SQL> show parameter db_flashback_retention_target

NAME                                 TYPE        VALUE                         
------------------------------------ ----------- ------------------------------
db_flashback_retention_target        integer     1440                          

Step 2: Create Normal Restore Points

SQL> connect / as sysdba

SQL> create restore point example_normal_restore_point ;

Restore point created.

SQL> shutdown immediate ;

SQL> startup mount ;

SQL> flashback database to restore point example_normal_restore_point ;
flashback database to restore point example_normal_restore_point
*
ERROR at line 1:
ORA-38782: cannot flashback database to non-guaranteed restore point
'EXAMPLE_NORMAL_RESTORE_POINT'

SQL> drop restore point example_normal_restore_point ;

SQL> shutdown immediate ;

SQL> startup mount ;

SQL> alter database flashback on ;

Database altered.

SQL> alter database open ;

Database altered.

SQL> create restore point example_normal_restore_point ;

Restore point created.

Step 3: Flashback Database

SQL> flashback database to restore point example_normal_restore_point ;
flashback database to restore point example_normal_restore_point
*
ERROR at line 1:
ORA-38757: Database must be mounted and not open to FLASHBACK.

SQL> shutdown immediate ;

SQL> startup mount ;

SQL> flashback database to restore point example_normal_restore_point ;

Flashback complete.

SQL> alter database open ;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open resetlogs ;

Database altered.

Learn It Yourself: Oracle Flashback Features

The script below is the one I had used to learn more about Oracle Flashback features and then to teach development teams how to use flashabck features. I have embeded some comments within the script to explain few points. Just copy and run the script to get better understanding of Oracle Flashback features. Enjoy!

REM *****************************************************************
REM Name: OraFlash.sql
REM Author: Jay Mehta
REM Description: A script to demonstrate Oracle flashback features
REM *****************************************************************
REM
REM **** drop Scripts
DROP TABLE TEST PURGE;
DROP SEQUENCE TEST_SEQ;
DROP RESTORE POINT before_insert ;
DROP RESTORE POINT before_upper_update ;
DROP RESTORE POINT before_lower_update ;
DROP RESTORE POINT before_delete ;
REM *****************************************************************
REM
REM *****************************************************************
ALTER SESSION SET NLS_DATE_FORMAT='MM/DD/YYYY:HH24:MI:SS';
SET FEEDBACK ON
SET TERM ON
SET ECHO ON
REM
REM **** Create a test table
REM
REM This table will be used in test cases
CREATE TABLE TEST
(   ID NUMBER(9),
    NAME VARCHAR2(30),
    TIME DATE ) ;
REM
REM **** You must enable row movement to perform FLASHBACK .. TO TIMESTAMP/SCN/RESTORE POINT
REM **** FLASHBACK ..... BEFORE DROP doesn't require Row Movement Enabled.
REN
ALTER TABLE TEST ENABLE ROW MOVEMENT;
REM To show that index and trigger are also flashed back by FLASHBACK ..... BEFORE DROP   
CREATE UNIQUE INDEX TEST_PK
ON TEST (ID);
ALTER TABLE TEST
ADD CONSTRAINT TEST_PK
PRIMARY KEY(ID);
CREATE SEQUENCE TEST_SEQ;
REM **** Create a trigger on Test Table
CREATE OR REPLACE TRIGGER TEST_BRI_TRG
BEFORE INSERT
ON TEST
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
  IF :new.id IS NULL THEN
     SELECT TEST_SEQ.NEXTVAL
     INTO :new.id
     FROM dual;
     IF SQL%notfound THEN
       raise_application_error(-20031,
           'The sequence generator IS NOT wo;king');
     END  IF;
  END IF;
END;
/

REM Create Restore Point
REM Get System Change Number and Timestamp so we can use it to flashback to this SCN
REM You can, of course, use SCN and TIMESTAMP to falshback to, but
REM Restore POint offers a better way to flashback
CREATE RESTORE POINT before_insert ;
SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER SCN FROM DUAL;
SELECT SYSDATE FROM DUAL;
REM **** Insert Data
INSERT INTO TEST(ID,NAME,TIME)
VALUES(1,'Jay',SYSDATE);
INSERT INTO TEST(ID,NAME,TIME)
VALUES(2,'Jim',SYSDATE);
INSERT INTO TEST(ID,NAME,TIME)
VALUES(3,'John',SYSDATE);
COMMIT ;
SELECT * FROM TEST ;
REM Create Restore Point
REM Get System Change Number and Timestamp so we can use it to flashback to this SCN
REM You can, of course, use SCN and TIMESTAMP to falshback to, but
REM Restore POint offers a better way to flashback
CREATE RESTORE POINT before_upper_update ;
SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER SCN FROM DUAL;
SELECT SYSDATE FROM DUAL;
UPDATE TEST
SET NAME = UPPER(NAME);
COMMIT;
SELECT * FROM TEST ;
REM
CREATE RESTORE POINT before_lower_update ;
SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER SCN FROM DUAL;
SELECT SYSDATE FROM DUAL;

Prompt ******* Update to Lower Case
UPDATE TEST
SET NAME = LOWER(NAME);
COMMIT;
SELECT * FROM TEST ;

CREATE RESTORE POINT before_delete ;
SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER SCN FROM DUAL;
SELECT SYSDATE FROM DUAL;
Prompt ******* Delete Data
DELETE FROM TEST ;
COMMIT;
Prompt ***** Data in Test Table at this moment
SELECT *
FROM TEST ;
Prompt ************  Update Timestamp as appropriate
REM Flashback Querty Examples with TIMESTAMP clause
REM ***** Data in Test Table 2 seconds ago
SELECT *
FROM TEST
AS OF TIMESTAMP ( SYSDATE - 2/84600) ;
Prompt ***** Data in Test Table 3 seconds ago
SELECT *
FROM TEST
AS OF TIMESTAMP ( SYSDATE - 3/84600) ;
Prompt ***** Data in Test Table 4 seconds ago
SELECT *
FROM TEST
AS OF TIMESTAMP ( SYSDATE - 4/84600) ;
Prompt ***** Data in Test Table 5 seconds ago
SELECT *
FROM TEST
AS OF TIMESTAMP ( SYSDATE - 5/84600) ;
Prompt ***** Data in Test Table 6 seconds ago
SELECT *
FROM TEST
AS OF TIMESTAMP ( SYSDATE - 6/84600) ;
Prompt ***** Data in Test Table 7 seconds ago
SELECT *
FROM TEST
AS OF TIMESTAMP ( SYSDATE - 7/84600) ;
Prompt ***** Data in Test Table 1 hour ago
SELECT *
FROM TEST
AS OF TIMESTAMP ( SYSDATE - 1/24) ;
SELECT *
FROM V$RESTORE_POINT ;
REM
REM Flashback Row History - Retrieve Version History
REM Update Timestamp appropeiately
REM
SELECT VERSIONS_STARTTIME, VERSIONS_ENDTIME,
       DECODE( VERSIONS_OPERATION
         , 'I', 'Insert'
         , 'U', 'Update'
         , 'D', 'Delete'
              , 'Original'
             ) "Operation" ,
             ID, NAME, TIME,
       VERSIONS_STARTSCN, VERSIONS_ENDSCN, VERSIONS_XID
FROM TEST VERSIONS BETWEEN TIMESTAMP MINVALUE AND MAXVALUE
ORDER BY VERSIONS_XID;
REM Update Timestamp appropeiately
SELECT VERSIONS_STARTTIME, VERSIONS_ENDTIME, VERSIONS_OPERATION, ID, NAME, TIME,
       VERSIONS_STARTSCN, VERSIONS_ENDSCN, VERSIONS_XID
FROM TEST VERSIONS BETWEEN TIMESTAMP TO_DATE('09/08/2010:10:30:20','MM/DD/YYYY:HH24:MI:SS')  AND SYSTIMESTAMP
ORDER BY ID, VERSIONS_STARTSCN ;
REM Update Timestamp appropeiately
SELECT VERSIONS_STARTTIME, VERSIONS_ENDTIME, VERSIONS_OPERATION, ID, NAME, TIME,
       VERSIONS_STARTSCN, VERSIONS_ENDSCN, VERSIONS_XID
FROM TEST VERSIONS BETWEEN TIMESTAMP SYSTIMESTAMP - 1/1440 AND SYSTIMESTAMP
ORDER BY ID, VERSIONS_STARTSCN ;
REM Update Timestamp appropeiately
SELECT VERSIONS_STARTTIME, VERSIONS_ENDTIME, VERSIONS_OPERATION, ID, NAME, TIME,
       VERSIONS_STARTSCN, VERSIONS_ENDSCN, VERSIONS_XID
FROM TEST
VERSIONS BETWEEN TIMESTAMP SYSTIMESTAMP - 1 AND SYSTIMESTAMP
ORDER BY ID, VERSIONS_STARTSCN ;

REM ***** Flashback Row History - Undo Mistakes
REM
-- Get the UNDO Statement to undelete row
SELECT start_timestamp, commit_timestamp, operation, undo_sql
FROM FLASHBACK_TRANSACTION_QUERY
WHERE xid in ( SELECT  hextoraw(VERSIONS_XID)
        FROM TEST VERSIONS BETWEEN TIMESTAMP MINVALUE AND MAXVALUE )
order by start_scn;

SELECT *
FROM FLASHBACK_TRANSACTION_QUERY
WHERE
TABLE_NAME='TEST'
ORDER BY START_SCN ;

SELECT *
FROM FLASHBACK_TRANSACTION_QUERY
WHERE
XID = '0009002F0001CB51'
-- XID = HEXTORAW('0009002F0001CB51')
ORDER BY START_SCN ;

REM Flashback Table - Undo Mistakes
REM

SELECT * FROM TEST ;
REM Flashback Before Delete
REM User SCN, TIMESTAMP or RESTORE POINT
REM
REM FLASHBACK TABLE TEST TO TIMESTAMP ( TO_TIMESTAMP('09/15/2010:14:37:08', 'MM/DD/YYYY:HH24:MI:SS' )) ;
REM FLASHBACK TABLE TEST TO SCN 2342345234 ;
FLASHBACK TABLE TEST TO RESTORE POINT before_delete;
SELECT * FROM TEST ;

-- Flashback Before Last Update
REM FLASHBACK TABLE TEST TO TIMESTAMP ( TO_TIMESTAMP('09/15/2010:14:37:05', 'MM/DD/YYYY:HH24:MI:SS' )) ;
FLASHBACK TABLE TEST TO RESTORE POINT before_upper_update;
SELECT * FROM TEST ;

-- Flashback Before First Update
REM FLASHBACK TABLE TEST TO TIMESTAMP ( TO_TIMESTAMP('09/09/2010:07:00:00', 'MM/DD/YYYY:HH24:MI:SS' )) ;
FLASHBACK TABLE TEST TO RESTORE POINT before_lower_update;
SELECT * FROM TEST ;
REM
REM Flashback Drop - Recover Dropped Tables
REM
DROP TABLE TEST;
SELECT TABLE_NAME FROM USER_TABLES ;
SELECT * FROM RECYCLEBIN ;
FLASHBACK TABLE TEST TO BEFORE DROP  RENAME TO TEST1 ;
SELECT * FROM RECYCLEBIN ;
SELECT TABLE_NAME FROM USER_TABLES ORDER BY 1;
SELECT * FROM TEST;

-- permanent drop
DROP TABLE TEST PURGE ;
SELECT TABLE_NAME FROM USER_TABLES ;
SELECT * FROM RECYCLEBIN ;

How to Configure UTL_MAIL in Oracle DB

To install UTL_MAIL:

sqlplus / as SYSDBA

SQL> @$ORACLE_HOME/rdbms/admin/utlmail.sql

SQL> @$ORACLE_HOME/rdbms/admin/prvtmail.plb

When you run the above mentioned two scripts, Oracle creates UTL_MAIL package. Oracle also creates a public synonym on UTL_MAIL package, but doesn't grant privileges to any user, DBA role or PUBLIC. You need to explicitly grant EXECUTE privilege on this package.

SQL> GRANT EXECUTE ON UTL_MAIL TO APPOWNER, JMEHTA;

Grant succeeded.

In addition, define int.ora parameter as shown below.

SQL> alter system set smtp_out_server= 'mysmtpserver.mydomain.com' scope both;

If you are in Oracle11g, configure network access control lists.

That's all you need to setup UTL_MAIL package. Send a test  email

exec UTL_MAIL.SEND (sender=>'me@mydomain.com', recipients=>'you@google.com', subject=>'Test Message', Message=>'test'); 

If you are receiving the following error, then double-check that SMTP server that you have defined is accessible.

ORA-06502: PL/SQL: numeric or value error

ORA-06512: at "SYS.UTL_MAIL", line 654

ORA-06512: at "SYS.UTL_MAIL", line 671

Customize SQL*Plus

SQL*Plus is one the most commonly used tool to access Oracle databases. Although I do use Oracle Grid/Database Controls and few other tools extensively, SQL*Plus is the tool of choice on the servers to perform variety of database administration activities such as ad-hoc queries, monitoring, upgrades, database maintenance, user management, space management, etc. etc. etc. Given its wide-spread usage on day-to-day basis, I have customized SQL*Plus to improve and enhance my user experience!

In this blog, I will show you few productivity boosting tips on how to customize SQL*Plus. Its quite easy. These tips are quite useful to me, and I believe will be useful to you as wll.

SQLPATH Variable

SQLPATH environment variable is equivalent to PATH variable. What PATH variable is to executables, SQLPATH is to SQL scripts. As a DBA, I have my own repository of scripts that I execute quite often, and I am sure you do as well. Simply add your script folders to SQLPATH. SQL*Plus searches for SQL scripts in all directories specified by SQLPATH variable. Quite useful. You don't need to specify the full path of the script. Or you don't need to change directory to the folder where your scripts are stored.

On Windows platform, you also have a choice to add SQLPATH registry variable. Use either environment variable or registry variable.

Login.sql

LOGIN.SQL is equivalent to AUTOEXEC.BAT on Windows or .profile on Unix platforms. SQL*Plus executes login.sql at the startup. At startup, SQL*Plus starts a search for login.sql in your current directory and then it moves on to directories specified by SQLPATH variable. Create login.sql and include it in a directory that is pointed to by SQLPATH variable. You can add any SQL*Plus commands that you like. Another useful customization. The following is my truncated login.sql file.

set termout off

-- formatiing

set pagesize 1000

set linesize 132

alter session set nls_date_foramt = 'MM/DD/YYYY:HH24:MI:SS' ;

-- SQL Prompt

column new_dbid new_value old_dbid noprint

column usr new_value ousr noprint

select upper (substr (global_name, 1, (instr (global_name, '.') -1))) new_dbid

from global_name ;

select user usr from dual ;

set sqlprompt '&ousr &old_dbid> '

column new_dbid print

column usr print

-- Automatic Spool

column tstamp new_value otstamp noprint

select to_char(sysdate,'MMDDYYYYHHMISS') tstamp from dual ;

spool 'E:\MyDoc\Spool\&ousr&otstamp'

column tstamp print

set termout on

Please be careful if you have scheduled unattended batch jobs using SQL*Plus. If database is not up and running, login.sql script will prompt for values for Username and Global Name variables used in the above script, and wait forever.

SQL Prompt

Another useful customization is to customize the SQL*Plus prompt. Login.sql displayed above sets up SQL prompt that comprises of Username and a Database Name.

Automatic Spool File

This customization, included as part of above mentioned login.sql, has been quite useful to me on number of occasions. It automatically creates a spool file for every SQL*Plus session at startup. Your SQL*Plus session history is saved in the spool files that you can reference anytime. Quite useful when you need to reconstruct what happened during that late night session!

Spool filename comprises of username and timestamp. Spool file is created in the folder specified as part of spool command in the above mentioned SQL*Plus.

Update Start in Property on MS Windows

Update Start-in property on Windows so all your spool files will be saved into this folder.

If you have recently installed Oracle11g R2 and are getting the following errors, then you need to update Start in SQL*Plus properties.

JMEHTA> ed
SP2-0110: Cannot create save file "afiedt.buf"

 JMEHTA> save temp.lst
SP2-0110: Cannot create save file "temp.lst"

Flashback is not just for humans - Oracle Flashback Features

One of the most critical responsibilities of an Oracle DBA is to protect data against disasters. That includes maintaining data integrity of the database. FIPS Publication 199 “Standards for Security Categorization of Federal Information and Information Systems” defines loss of integrity as unauthorized modification and/or destruction of information. Loss of integrity may be a result of hardware failures, malicious attacks or simply unintentional accidents.

A recent study that cites human errors as the leading cause of loss of data integrity is no surprise. Human errors and accidental mistakes cause more system downtime than all other events combined. We heard the phrase “We are human” quite often. You can’t blame anybody. Accidents do happen. Mistakes do take place. An UPDATE without a WHERE clause runs. DELETE statement may have a bug. Application Code misbehaves. A support staff simply fires the wrong statement in the wrong database. Remember running a script in production which was meant for development database. Ouch! Whatever the cause maybe, an Oracle DBAs are called upon to fix data integrity issues.

It’s Oracle DBA’s responsibility to plan and implement mechanisms to recover from such accidents and disasters. A well-planned and tested backup strategy forms the primary mechanism to protect data. Oracle Flashback is one more weapon in Oracle DBA’s arsenal against such disasters. Oracle Flashback may come to your rescue in some specific scenarios. Oracle Flashback Features may have restrictions, constraints and limitations, but it’s Oracle DBA’s job to maximize the benefits that may be accrued from Oracle Flashback features.

With Oracle Flashback, you can view data as of previous point-in-time, get data update history along with metadata, recover tables to a previous point-in-time, undo transactions, etc.

From an Oracle DBA perspective, Oracle Flashback features are based on three different components:

• Flashback Features that are based on Undo Segments – Flashback Query, Flashback Version Query and Flashback Transaction fall in this category. These features rely upon data stored in undo segments.
• Flashback Features that are based on Recycle Bin – Flashback Drop feature falls in this category.
• Flashback Features that are based on Flashback Archive Logs – Flashback Database feature falls in this category. This feature relies upon flashback logs.

In this blog, I will explain what you need to do to setup flashback features that are based on undo segments.

Init.ora Parameters

Automatic Undo Management (AUM) is a pre-requisite for Oracle Flashback. With AUM, the database manages undo segments in an undo tablespace. Beginning with Release 11g, automatic undo management is the default mode for a newly installed database.

Set Undo_management init.ora parameter to AUTO to enable Automatic Undo Management.

UNDO Tablespace

This parameter is optional. If you have only one undo tablespace in your database, then you don’t need to specify this parameter. Oracle picks up the first available undo tablespace at the database startup. If you have more than one undo tablespaces, then specify the name of the undo tablespace that you want Oracle to use. If you don’t specify undo tablespace parameter and you haven’t pre-created any undo tablespace, then Oracle would start using SYSTEM tablespace as undo tablespace.

Size of Undo Tablespace

Size of undo tablespace is an important parameter that has direct impact on read consistency and flashback. The two primary objectives of the undo segments are (a) to rollback transactions and (2) to provide read consistent view of the database to the users. In addition, undo segments are used during instance recovery to rollback uncommitted transactions. You need to size undo tablespace keeping in mind these objectives. If undo tablespace is too small, then you may get dreaded “snapshot too old” errors. If undo tablespace is too large, then you may waste space.

Proper sizing of undo tablespace is not trivial. You need to know maximum undo generation rate and timings of long running queries. In addition, you need to factor in flashback requirements. Many DBAs don’t have such statistics, and hence start with reasonable undo tablespace size, and then monitor the statistics to fine tune the size if required.

If you have enough disk space, then I would recommend creating an undo tablespace as large as reasonably possible. With larger undo tablespace, you are likely to retain undo data for longer duration, and hence flashback further into past.

Undo_retention

For NON-AUTOEXTEND undo datafiles, you don’t need to specify undo_retention. If you specify, then Oracle ignores it. The database automatically tunes undo_retention parameter for the best possible retention based on system activities and undo tablespace size.

For AUTOEXTEND undo datafiles, the database attempts to maintain undo information for the period specified by this parameter. If necessary, Oracle extends datafiles to ensure that undo information is retained for the duration specified here. Please note that Oracle doesn’t automatically shrink the datafiles when undo tablespace extends and becomes too large.

Enable Row Movement

Enable Row Movement is a pre-requisite for FLASHBACK TABLE <table> TO SCN/TIMESTAMP/RESTORE POINT statement. You must execute ALTER TABLE <table> ENABLE ROW MOVEMENT to enable row movement. This clause grants Oracle permission to move rows within the table. Row movement changes row ids of the rows. FLASHBACK TABLE <table> TO SCN/TIMESTATMP/RESTORE POINT statement deletes and then inserts rows, causing row ids to change from their original values.

Please don’t confuse Row Movement with Row Migration and Row Chaining. Row Chaining occurs when Oracle cannot fit a row in one block and the row is spread across more than one Oracle data blocks. Row migration occurs when Oracle migrates a row to a new block without changing its row id.

By default, row movement is disabled. You need to explicitly enable row movement with CREATE TABLE or ALTER TABLE clauses. Please note that Oracle10g invalidates objects when you enable row movement for a table. I don’t see any reasons why row movement should not be enabled unless

• Your application stores ROWIDs for future queries and DML operations.
• Your application relies upon Oracle exceptions to prevent partition key updates.

Please note that FLASHBACK TABLE <table> TO BEFORE DROP doesn’t require row movement enabled. In addition, ALTER TABLE <table> SHRINK and Partition Key Update operations require row movement to be enabled.

Oracle Recycle Bin

Flashback

In computing world, a concept of recycle bin or trash is not new. A recycle bin, or trash can, is temporary storage for files that have been deleted by the user, but not yet permanently erased by the operating system from the disk drive. Apple first implemented a trash can icon during the development of Lisa. The concept was carried over to the Apple Macintosh, and has existed and proliferated since then.

Although MS-DOS operating systems and some third parties utilities did offer UNDELETE feature for DOS and Windows users, Microsoft didn’t offer an icon similar to the trash can until it introduced a Recycle Bin icon in Windows 95. In fact, this concept of trash was subject to a lawsuit which Apple eventually lost.

Oracle Database Recycle Bin

And, Oracle waited until Oracle10g to introduce a recycle bin concept. Better late than never! Oracle Recycle Bin is actually a data dictionary table containing information about dropped objects. When you drop a table in Oracle, the database does not immediately remove/purge the table from the database. It is just a logical delete. The table is not permanently removed from the database. Instead, it renames the table and places it and any dependant objects such as indexes, constraints and triggers in a recycle bin.

Oops!

In the life of a DBA or an application developer, there are "Oops" moments that they love to hate or hate to love for a long time. Many of us might have accidentally dropped a table that very well be an "Oops" moment. For example,

DROP TABLE US_STATES;

Thanks!

Recycle Bin could very well be a job-saver for “Oops” drops! It has been a life-saver for many who had dropped some important database tables either accidentally and/or unconsciously. You can view the recycle bin by:

JMEHTA > show recyclebin ;

ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
US_STATES BIN$OozJnFttRLa3AntqEdXy/g==$0 TABLE 2009-07-07:10:42:58

What a relief! US_STATES table is peacefully resting in Recycle Bin! Although Oracle has renamed this dropped table with a system generated name that begins with BIN$.

You can continue to access data from the dropped table through its recycled name, as shown below:

JMEHTA > select * from "BIN$OozJnFttRLa3AntqEdXy/g==$0" ;

ID CODE NAME
---------- -- ------------------------------
1 VA Virginia
2 MD Maryland

But what about indexes, triggers and grants that were associated with this table? They are also recoverable.

“SELECT * FROM USER_RECYCLEBIN” or “SELECT * FROM RECYCLEBIN” yields the following output.

Note: RECYCLEBIN or USER_RECYCLEBIN are synonyms and can be used interchangeably.

JMEHTA > select OBJECT_NAME, ORIGINAL_NAME, TYPE from user_recyclebin ;

OBJECT_NAME ORIGINAL_NAME TYPE
------------------------------ -------------------------- -------------------------
BIN$fIgPImIyQsi9yL3c3F8+8g==$0 US_STATES_PK INDEX
BIN$s4VUcdg2SDuHuOVLq3Gyyg==$0 US_STATES_BRI_SEQ TRIGGER
BIN$OozJnFttRLa3AntqEdXy/g==$0 US_STATES TABLE

If you are a DBA, you can also view database level recycle bin by:

JMEHTA> select * from dba_recyclebin

Bingo!

You can recover a table and its dependant objects through flashback command, as shown below.

JMEHTA > flashback table us_states to before drop ;

Let's verify table recovery.

JMEHTA > SELECT TABLE_NAME FROM USER_TABLES WHERE TABLE_NAME = 'US_STATES' ;

TABLE_NAME
------------------------------
US_STATES

What!

You may shout “What!” when you realize that Oracle didn’t rename associated indexes and triggers to their original names.

JMEHTA > select index_name from user_indexes where table_name = 'US_STATES' ;

INDEX_NAME
------------------------------
BIN$leIeukwWQL6zfEoR7XKk5g==$0

JMEHTA > select trigger_name from user_triggers where table_name = 'US_STATES' ;

TRIGGER_NAME
------------------------------
BIN$43f91QgqSBe3gG055leeVA==$0

You can rename indexes and triggers as shown below:

JMEHTA > alter index "BIN$leIeukwWQL6zfEoR7XKk5g==$0" rename to US_STATES_PK ;

Index altered.

JMEHTA > alter trigger "BIN$43f91QgqSBe3gG055leeVA==$0" rename to US_STATES_BRI_SEQ ;

Trigger altered.

Managing Recycle Bin

Oracle provides various commands to manage recycle bin.

To Purge a single table from Recycle Bin:

PURGE TABLE TABLE_NAME

To empty current user's recycle bin and release all space associated with objects in the recycle bin:

PURGE RECYCLEBIN or PURGE USER_RECYCLEBIN

To Empty Recycle Bin at Database Level:

PURGE DBA_RECYCLEBIN
SYSDBA privilege is required to purge DBA_RECYCLEBIN

Bypassing Recycle Bin

Specify PURGE if you want to drop the table and release the space associated with it in a single step. If you specify PURGE, then the database does not place the table and its dependent objects into the recycle bin. Using this clause is equivalent to first dropping the table and then purging it from the recycle bin. This clause lets you save one step in the process.

DROP TABLE TABLE_NAME PURGE;

To disable Recycle Bin:

ALTER SESSION SET recyclebin = OFF; (Session Level)

ALTER SYSTEM SET recyclebin = OFF; (system level)

recyclebin=off (system level init.ora parameter)

When the recycle bin is disabled, dropped tables and their dependent objects are not placed in the recycle bin; they are just dropped, and you must use other means to recover them (such as recovering from backup).

To enable Recycle Bin

ALTER SESSION SET recyclebin = ON; (Session Level)

ALTER SYSTEM SET recyclebin = ON; (system level)

recyclebin=on (system level init.ora parameter)

When the recycle bin is enabled, dropped tables and their dependent objects are placed in the recycle bin. The recycle bin is enabled by default.

Space Pressures

Dropped objects continue to stay in the recycle bin until the owner of the dropped objects decides to permanently remove them using the PURGE command. Objects in the Recycle Bin will be automatically purged by the database if

• A user creates a new table or adds data that causes their quota to be exceeded.
• The tablespace needs to extend its file size to accommodate create/insert operations.
• Corresponging tablespace or user is dropped

Misc.

Please note individual index drops or trigger drops are not recoverable through recycle bin. For example, if you drop an index or a trigger, then they are not placed in recycle bin. But if you drop a table, all dependant objects such as indexes and triggers are placed in recycle bin, and are recoverable.


Happy Recycling! Go Green!