Use of Oracle Sequences in PL/SQL Expressions

In this post, I will be discussing yet another new feature of Oracle database 11g. Well, this new feature is related to Oracle Sequences. With Oracle Database 11g, we can use SEQUENCE.NEXTVAL in PL/SQL expression instead of writing a SELECT statement. Here is an example:




Using SEQUENCE.NEXTVAL in PL/SQL assignment simply enhances readability, but, under the covers, Oracle rewrites the assignment into a SELECT statement. To verify the same, I traced a small PL/SQL block using 10046 trace as shown below:

Following is an extract from the trace file:

As you may see, Oracle has re-written the expression into equivalent SQL statement. Performance wise, this new feature does not give any benefit but the advantage we get is readability.

Thanks for reading :)

Configure Controlfile Autobackup’s

Hi,

You can configure automatic backup of your control file and server parameter file (SPFILE) using RMAN. RMAN backs up control file and SPFILE (if used to start the database) in one of two situations:

1) when a successful backup must be recorded in the RMAN repository, and 2) when a structural change to the database affects the contents of the control file which therefore must be backed up. Structural changes to database include: adding a new tablespace, altering the state of a tablespace or datafile, renaming a file, and so on.

The server process is responsible for performing control file autobackup when there is any structural change.

By default, control file autobackups are turned off. Control file autobackups can be turned on using the following command in RMAN:

C:\> rman target /

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;

old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP OFF;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored

We may set the directory and format of backup files using the following RMAN command:

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'C:\db10g\cfbackup\db10g_%F';

new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'C:\db10g\cfbackup\db10g_%F';
new RMAN configuration parameters are successfully stored

All autobackup formats must include %F variable. %F expands to “C-XXXXXXXXX-YYYYMMDD-NN”, where:

XXXXXXXXX – database id

YYYYMMDD – day, when backed up

NN – change number during day, starts with 00, and represented in hexadecimal

At this point, my “C:\db10g\cfbackup”directory is empty:

C:\db10g\cfbackup>dir
Volume in drive C is SYSTEM
Volume Serial Number is AC21-0462

Directory of C:\db10g\cfbackup

18/09/2007 11:02 AM .
18/09/2007 11:02 AM ..
0 File(s) 0 bytes
2 Dir(s) 4,451,540,992 bytes free

To test the control file autobackup, I will add a new tablespace:

SQL> create tablespace dropme datafile 'C:\db10g\cfbackup\dropme.dbf' size 10m;

Tablespace created.

The above command should trigger an autobackup of controlfile, let me see if any file is created in “C:\db10g\cfbackup” folder:

C:\db10g\cfbackup>dir
Volume in drive C is SYSTEM
Volume Serial Number is AC21-0462

Directory of C:\db10g\cfbackup

18/09/2007 12:19 PM.
18/09/2007 12:19 PM..
18/09/2007 12:19 PM 7,340,032 DB10G_C-15597741-20070918-02
18/09/2007 12:19 PM 10,493,952 DROPME.DBF
2 File(s) 17,833,984 bytes
2 Dir(s) 4,433,707,008 bytes free

As we may see, a file "DB10G_C-15597741-20070918-02" is created. This is the autobackup of control file and SPFILE performed by the server process. At the same time, the database writes a message containing the complete path of the backup piece and device type to the alert log.

Tue Sep 18 12:19:37 2007
create tablespace dropme datafile 'C:\db10g\cfbackup\dropme.dbf' size 10m
Tue Sep 18 12:19:37 2007
Starting control autobackup
Control autobackup written to DISK device
handle 'C:\DB10G\CFBACKUP\DB10G_C-15597741-20070918-02'
Completed: create tablespace dropme datafile 'C:\db10g\cfbackup\dropme.dbf' size 10m

Control file autobackups can be turned off anytime using “CONFIGURE CONTROLFILE AUTOBACKUP OFF” command in RMAN.

Conclusion: With a control file autobackup, RMAN can recover database even if the current control file, recovery catalog, and server parameter file are inaccessible.

Regards

An Unusual Export Error

Hi,

A full database export was completing with the following errors in one of our UAT database (Oracle 10g Release 2):

EXP-00008: ORACLE error 1031 encountered
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_EXPORT_EXTENSION", line 257
ORA-06512: at line 1
EXP-00078: Error exporting metadata for index TB_SDN_EXT_IDX. Index creation will be skipped

Strange thing to note is the error message “ORA-01031: insufficient privileges” and the error is raised only for indexes of type “DOMIAN”. The user executing export has “EXP_FULL_DATABASE” and “IMP_FULL_DATABASE” roles granted.

We have been encountering these errors only after installing “Oracle Text” option, before installing “Oracle Text”, the exports were running successfully.

To resolve the problem we have to explicitly grant “SELECT ANY TABLE” privilege to the exporting user.

Well, it’s logical that when a user has “EXP_FULL_DATABASE” role granted there is no need to grant “SELECT ANY TABLE” privilege. But still, that solved our problem.

Regards

Read-Only Tables in Oracle 11g

In pre-Oracle 11g, one option to place a table in read-only mode is to create a trigger on that table that raises an exception on INSERT, UPDATE, and DELETE. In Oracle Database 11g, you can place a table in read-only mode by merely executing ALTER TABLE…READ ONLY statement.

Following is a simple test:

I create a test table “TEST1” and insert a dummy record. Using the ALTER TABLE …READ ONLY statement, I place “TEST1” table in read-only mode: Once a table is marked as read-only, all DML transactions are protected against that table. Any attempt to modify the “TEST1” table results in ORA-12081 error. You can find out whether a table is in read-only mode by querying “READ_ONLY” column of “USER_TABLES” view. A value of “YES” indicates that a table is read-only while a value of “NO” indicates a table is in read-write mode. At any time, you may place the table back in read-write mode using ALTER TABLE…READ WRITE statement. Read Oracle Database 11g Administrators Guide, for a complete list of allowed and barred operations on read-only tables.

Birla Institute of Technology and Science (BITS), Pilani

Dear all, Recently, Outlook Magazine has ranked BITS Pilani as number ONE engineering college in India. BITS Pilani has two campuses in India and one in Dubai.

I am one of the student of the most prestigious engineering college of India. I did my Master of Science (MS) degree in "Software Systems" from BITS-Pilani.

Read what Wikipedia’s has to say about BITS-Pilani.

Regards