AIX topas explained.

http://www.tablespace.net/papers/topas_intro.html

Linux top command explained.

http://www.linuxnix.com/2011/08/linux-top-command-explained-2.html

What is ASM:

Automatic Storage Management (ASM) simplifies administration of Oracle related files by allowing the administrator to reference disk groups rather than individual disks and files, which are managed by ASM. The ASM functionality is an extention of the Oracle Managed Files (OMF) functionality that also includes striping and mirroring to provide balanced and secure storage. The new ASM functionality can be used in combination with existing raw and cooked file systems, along with OMF and manually managed files.

The ASM functionality is controlled by an ASM instance. This is not a full database instance, just the memory structures and as such is very small and lightweight.

The main components of ASM are disk groups, each of which comprise of several physical disks that are controlled as a single unit. The physical disks are known as ASM disks, while the files that reside on the disks are know as ASM files. The locations and names for the files are controlled by ASM, but user-friendly aliases and directory structures can be defined for ease of reference.

The level of redundancy and the granularity of the striping can be controlled using templates. Default templates are provided for each file type stored by ASM, but additional templates can be defined as needed.

Failure groups are defined within a disk group to support the required level of redundancy. For two-way mirroring you would expect a disk group to contain two failure groups so individual files are written to two locations.

In summary ASM provides the following functionality:

1. Manages groups of disks, called disk groups.
2. Manages disk redundancy within a disk group.
3. Provides near-optimal I/O balancing without any manual tuning.
4. Enables management of database objects without specifying mount points and filenames.
5. Supports large files.

Configuration of ASM:

Use the following command to identify the kernel version

[oracle@node1 u01]$ uname -r

2.6.18-53.el5

Download Oracle ASMLib from following site ==>

http://www.oracle.com/technetwork/server-storage/linux/downloads/rhel5-084877.html

[oracle@node1 u01]$ rpm -ivh --force --nodeps *

oracleasm-support-2.1.7-1.el5.x86_64.rpm

oracleasm-2.6.18-274.el5-2.0.5-1.el5.x86_64.rpm

oracleasmlib-2.0.4-1.el5.x86_64.rpm

oracleasm-2.6.18-53.1.19.el5xen-2.0.4-1.el5.i686.rpm

oracleasm-2.6.18-53.1.19.el5debug-2.0.4-1.el5.i686.rpm

oracleasm-2.6.18-53.1.19.el5PAE-2.0.4-1.el5.i686.rpm

oracleasm-2.6.18-53.1.19.el5-2.0.4-1.el5.i686.rpm

Configure Oracle ASM Library driver

[oracle@node1 init.d]# /etc/init.d/oracleasm configure

Configuring the Oracle ASM library driver.

This will configure the on-boot properties of the Oracle ASM library

driver.  The following questions will determine whether the driver is

loaded on boot and what permissions it will have.  The current values

will be shown in brackets ('[]').  Hitting <ENTER> without typing an

answer will keep that current value.  Ctrl-C will abort.

Default user to own the driver interface []: oracle

Default group to own the driver interface []: dba

Start Oracle ASM library driver on boot (y/n) [n]: y

Scan for Oracle ASM disks on boot (y/n) [y]: y

Writing Oracle ASM library driver configuration: done

Initializing the Oracle ASMLib driver: [OK ]

Scanning the system for Oracle ASMLib disks: [OK]

You can check the Oracle ASM status

[oracle@node1 init.d]# oracleasm status

Checking if ASM is loaded: yes

Checking if /dev/oracleasm is mounted: yes

Creating Raw disks in VM:

You can check the Oracle ASM status

[oracle@node1 init.d]# oracleasm status

Checking if ASM is loaded: yes

Checking if /dev/oracleasm is mounted: yes

Creating a new Partition for ASM disk

[oracle@node1 ~]# fdisk /dev/sdb

Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel

Building a new DOS disklabel. Changes will remain in memory only,

until you decide to write them. After that, of course, the previous

content won't be recoverable.

Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)

Command (m for help): p

Disk /dev/sdb: 5368 MB, 5368709120 bytes

255 heads, 63 sectors/track, 652 cylinders

Units = cylinders of 16065 * 512 = 8225280 bytes

Device Boot Start End Blocks Id System

Command (m for help): n

Command action

e extended

p primary partition (1-4)

p

Partition number (1-4): 1

First cylinder (1-652, default 1):

Using default value 1

Last cylinder or +size or +sizeM or +sizeK (1-652, default 652): +5000M

Command (m for help): w

The partition table has been altered!

Calling ioctl() to re-read partition table.

Syncing disks.

[oracle@node1 ~]# fdisk -l

Disk /dev/sda: 32.2 GB, 32212254720 bytes

255 heads, 63 sectors/track, 3916 cylinders

Units = cylinders of 16065 * 512 = 8225280 bytes

Device Boot Start End Blocks Id System

/dev/sda1 * 1 38 305203+ 83 Linux

/dev/sda2 39 3405 27045427+ 83 Linux

/dev/sda3 3406 3916 4104607+ 82 Linux swap / Solaris

Disk /dev/sdb: 5368 MB, 5368709120 bytes

255 heads, 63 sectors/track, 652 cylinders

Units = cylinders of 16065 * 512 = 8225280 bytes

Device Boot Start End Blocks Id System

/dev/sdb1 1 609 4891761 83 Linux

To create a ASM disk using Oracle ASMLib

[oracle@node1 ~]# oracleasm createdisk DISK1 /dev/sdb1

Writing disk header: done

Instantiating disk: done

[oracle@node1 ~]# oracleasm createdisk DISK2 /dev/sdc1

Writing disk header: done

Instantiating disk: done

[oracle@node1 ~]# oracleasm createdisk DISK3 /dev/sdd1

Writing disk header: done

Instantiating disk: done

To List and check the Oracle ASM disks

[oracle@node1 ~]# oracleasm listdisks

[oracle@node1 ~]# oracleasm querydisk DISK1

Disk "DISK1" is a valid ASM disk

Creating password file for ASM instance:

orapwd file=$ORACLE_HOME/dbs/orapwasm password=oracle

Creating directories:

+ASM,bdump,cdump,udump

Creating pfile for ASM instance:

db_unique_name = 'ASM'

instance_type = 'asm'

background_dump_dest = '/u01/app/oracle/admin/+ASM/bdump'

core_dump_dest = '/u01/app/oracle/admin/+ASM/cdump'

user_dump_dest = '/u01/app/oracle/admin/+ASM/udump'

export ORACLE_SID=ASM

sqlplus / as sysdba

SQL>startup nomount pfile='/u01/initasm.ora';

ASM instance started

Total System Global Area 79691776 bytes

Fixed Size 1217812 bytes

Variable Size 53308140 bytes

ASM Cache 25165824 bytes

Now creating database through DBCA and use the ASM disks:

Why should we perform consistent export?

As per TomKyte....

it makes all of the queries run by exp be "as of the same point in time-- consistent with
regards to eachother"

imagine if you started an export at 9am of the EMP and DEPT tables.

EMP started exporting at 9am and DEPT at 9:15am.

Now, the EMP data would be "as of 9am", but the DEPT data would be as of 9:15am.  What if
you import that data now -- will it work?  maybe, maybe not -- perhaps at 9:10am, someone
fired the last employee in department 50 and deleted department 50.  Your EMP export
would have them in this deptno, your DEPT export would not have this deptno.  The import
would fail.

If you use consistent=y, Oracle will export all of the data "as of 9am", so deptno=50
will be included in all tables and the import will succeed.

RMAN BACKUP VALIDATION AND RECOVERY FROM BLOCK CORRUPTION:

The main purpose of RMAN validation is to check for corrupt blocks and missing files

Corruption in block:

Block corruption is while the data is being written to the data blocks, if the write to the block fails abruptly, I mean that there is a partial write in the block, may be because of power disruption or I/O problem, leaving no time for header to be updated, or row data to be populated, oracle leaves the block corrupt.In case of block corruption you can normally use the database unless you try to read that particular block, against which it shoots up the block corruption error.Generally block corruption occurs if write fails on the block, when the transaction is being committed

Physical(media corrupt) and Logical(software corrupt) Block Corruption:

In a physical corruption, which is also called a media corruption, the database does not recognize the block at all: the checksum is invalid.

Checksum:

A number calculated by the database from all the bytes stored in a data or redo block. If the DB_BLOCK_CHECKSUM initialization parameter is enabled, then the database calculates the checksum for every datafile or online redo log block and stores it in the block header when writing to disk. The database can use the checksum value to check consistency.

In a logical corruption, the contents of the block are logically inconsistent.

The logical corruption happens within the blocks , for eg. some index entry pointing towards a null rowid.

Validating Database Files with BACKUP VALIDATE:

You can use the BACKUP VALIDATE command to do the following:

-Check datafiles for physical and logical block corruption.

-Confirm that all database files exist and are in the correct locations.

Validating only physical corruption:

BACKUP VALIDATE DATABASE ARCHIVELOG ALL;

Validating both physical and logical corruption:

BACKUP VALIDATE CHECK LOGICAL DATABASE ARCHIVELOG ALL;

Validating Backups Before Restoring Them:

You can run RESTORE ... VALIDATE to test whether RMAN can restore a specific file or set of files from a backup.

RESTORE DATABASE VALIDATE;

RESTORE ARCHIVELOG ALL VALIDATE;

RECOVRING A BLOCK CORRUPTION:

If it finds corrupted blocks(after checking with BACKUP VALIDATE CHECK LOGICAL DATABASE ARCHIVELOG ALL;) it will place the information about the corruption into a view:

v$database_block_corruption

SQL>select * from v$database_block_corruption;

FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO

———- ———- ———- —————— ———

5 81 4 0 CORRUPT

this is what we find in the alert_.log:

Corrupt block relative dba: 0x014000b1 (file 5, block 177)

Bad header found during backing up datafile

Data in bad block:

type: 67 format: 7 rdba: 0x0a545055

last change scn: 0×0000.0007bc77 seq: 0×3 flg: 0×04

spare1: 0×52 spare2: 0×52 spare3: 0×0

consistency value in tail: 0xbc772003

check value in block header: 0xb32

computed block checksum: 0xe4c1

Reread of blocknum=177, file=/u01/app/oracle/oradata/orcl/

example01.dbf.

found same corrupt data 

Now we can tell RMAN to recover all the blocks which it has found as being corrupt:

RMAN> blockrecover corruption list;

# (all blocks from v$database_block_corruption)

Starting blockrecover at 05-04-2006:10:09:15

using channel ORA_DISK_1

channel ORA_DISK_1: restoring block(s) from datafile copy /u01/app/

oracle/flash_recovery_area/ORCL/datafile/o1_mf_example_236tmb1c_.dbf

starting media recovery

archive log thread 1 sequence 2 is already on disk as file /u01/app/oracle/

flash_recovery_area/ORCL/archivelog/2006_04_05/o1_mf_1_2_236wxbsp_.arc

archive log thread 1 sequence 1 is already on disk as file

/u01/app/oracle/oradata/

orcl/redo01.log

media recovery complete, elapsed time: 00:00:01

Finished blockrecover at 05-04-2006:10:09:24

Also read:


Segment checking using DBV


DBMS_REPAIR




Enjoy:-)

Difference between WITH GRANT and WITH ADMIN

Sometimes people are puzzled to find out the difference between WITH GRANT and WITH ADMIN.

Here is the simple explanation:

WITH GRANT OPTION:

We can grant object privileges only with grant option like (select,update,insert)

 A--------->B--------->C

If A want to revoke privileges from C, it is not possible. The user who granted the privilege can only revoke. we have to revoke the privilege from B ,that automatically revoke the privileges from C.

WITH ADMIN OPTION:

We can give the system privileges onlywith admin option(CREATE TABLE,CREATE INDEX,CREATE SESSION)

 A------>B-------->C

In admin option,it is possible to revoke the privileges from both B and C by A.Here suppose if we revoke the privileges from B means it didn’t revoke the privileges of C.


Examples:

WITH ADMIN OPTION  When a user is granted a system privilege, the grantor (i.e., the person granting the privilege, typically the DBA) also has the option to allow the grantee (the person receiving the privilege, typically the user) to grant the same privilege to other users. If this is the result desired, the grantor can grant the privilege using the WITH ADMIN OPTION. When privileges are granted WITH ADMIN OPTION,this means that the grantor has decided that the grantee can be fully trusted by him as well as by the user that granted him the system privilege in the first place. In essence 
all users holding a system privilege


Security Hole:  WITH ADMIN OPTION are considered equal and can grant and revoke that privilege from anyone, including the person who granted it to them in the first place.



For example, if you give the key to your car to a friend and tell him that it is alright to make copies of the key, when you ask for the key back from your friend, you cannot, at the same time, get back all copies that were made by him and given to others. In order to get the other copies of the key back, you need to find out who has them. Similarly, in Oracle you need to query the data dictionary to determine which other users were granted the permission being revoked by the user from which it is being revoked.



WITH GRANT OPTION on object privileges allows a user granted 
the privilege to grant it to someone else. The reason for doing this is to minimize the administrative burden of granting object privileges.if an object privilege isrevoked from a user to whom it was granted WITH GRANT OPTION, that privilege would also be removed from anyone that user granted the privilege to. For example,if Damir granted John the SELECT privilege on the DAMIR.JOHN1 table WITH GRANT OPTION, and John then granted the SELECT privilege to Tim, then if Damir issued the command REVOKE SELECT ON DAMIR.JOHN1 FROM JOHN, Tim would also no longer have the privilege. This is because when object privileges are revoked,the revoke also cascades to anyone that the privilege was granted to by the user from whom it is being revoked.



Enjoy:-)

Manually create database in Oracle 10g Linux

Hello,

It is very exciting to create database manually on Linux platform.

Play this link

Enjoy:-) 

Who are you? Development DBA or Production DBA.

Development Oracle DBAs:

Generally speaking, Oracle Development DBAs are concerned with developing applications from a database perspective. Here are some of their tasks:

• ·         Know the source code very well, and the database structure
• ·         Look mainly after one or a set of applications
• ·         Involved in SQL tuning, and re-writing of code
• ·         Development of new functionality
• ·         Liaise with developers regularly in development issues

Production Oracle DBAs

On the whole, the Oracle Production DBA is all about maintaining a reliable, secure and performant database. Here are some common tasks:

• ·         Patching
• ·         Storage and capacity planning
• ·         Backup and recovery
• ·         Rollout of new releases
• ·         Performance tuning of instance as a whole
• ·         Troubleshooting expert

It’s also essential to bear in mind that there will certainly be overlap in any job you go to; just because you don’t work on production doesn’t mean you shouldn’t know how to patch or backup and recover a database. These are just an idea of the main functions of the different types of roles and what you can expect to spend most of your time doing.

It is very commong for you to be both the Development and Production DBA. I would say that at much larger organisations where there are many Oracle DBAs you are more likely to get the distinction between Development and Production DBAs. In smaller companies, you are more likely to be working both as the Development and Production DBA.

Enjoy:-)

Wishing you all a very Happy New year 2012

Secrets about SGA sizing

Hello everyone,

Please read the SGA sizing secrets by Oracle expert Tom:

Enjoy:-)

Checking redo's applied on standby or not

Here is the OTN discussion link that helps us to recognize the problem and solution.

https://forums.oracle.com/forums/thread.jspa?threadID=2303669&tstart=0

Enjoy:-)

Very good summary of DELETE TRUNCATE and DROP commands

Hello everyone,

Today i came across a very good explanation of  DELETE TRUNCATE and DROP commands.

Check here: -

Enjoy:-)

What is LOGGING,NOLOGGING and FORCE LOGGING?

Oracle gives us the ability to limit redo generation on tables and indexes by setting them in NOLOGGING mode.

NOLOGGING affect the recoverability. Before going into how to limit the redo generation, it is important to clear the misunderstanding that NOLOGGING is the way out of redo generation, there are some points regarding it:

NOLOGGING is designed to handle bulk inserts of data which can be easy reproduced.

Regardless of LOGGING status, writing to undo blocks causes generation of redo.

LOGGING should not be disabled on a primary database if it has one or more standby databases. For this reason oracle introduced the ALTER DATABASE FORCE LOGGING command in Oracle 9i R2. (Means that the NOLOGGING attribute will not have any effect on the segments) If the database is in FORCE LOGGING MODE. NOLOGGING can be also override at tablespace level using ALTER TABLESPACE … FORCE LOGGING.

FORCE LOGGING can be used on tablespace or database level to force logging of changes to the redo. This may be required for sites that are mining log data, using Oracle Streams or using Data Guard (standby databases).

Enjoy:-)

How To Check Whether Physical Standby is in Sync with the Primary or Not?

The following tips will help you in monitoring sync between primary and standby databases.

1. Check for GAP on standby

2. Check redo received on standby

3. Check redo applied on standby

Solution:

Execute following queries:

A. On Primary

SQL> SELECT THREAD# "Thread",SEQUENCE# "Last Sequence Generated"

FROM V$ARCHIVED_LOG

WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)

ORDER BY 1;

Thread     Last Sequence Generated

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

1          19

2          13

3          11

B. On Physical Standby

SQL> SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"

FROM

(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,

(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL

WHERE

ARCH.THREAD# = APPL.THREAD#

ORDER BY 1;

Thread     Last Sequence Received Last Sequence Applied Difference

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

1          19                     19                    0

2          13                     13                    0

3          11                     11                    0

C. On Physical Standby

SQL> SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;

no rows selected

Now perform following checks:

1. Check for GAP

If query “C” returns any row then this means there are some archive log missing on standby.

Example:

SQL> SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;

Thread     Low Sequence High Sequence

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

1          8            9

This example shows sequence 8 and 9 from thread 1 are missing on standby, Hence standby is not in sync with the primary.

If query “C” does not returns any row and output is “no row selected” than this means there is no archive gap on standby.

2. Check for redo received on standby

Compare value of “Last Sequence Generated” in query “A” with “Last Sequence Received” in query “B” for all threads.

If both values are same than this means that standby has received the last sequence generated on primary.

If both values are not same then there are some archives missing on standby, Hence standby is not in sync with the primary.

enjoy:-)

Database Switchover

A database can be in one of two mutually exclusive modes (primary or standby). These roles can be altered at runtime without loss of data or resetting of redo logs. This process is known as a Switchover and can be performed using the following statements..

-- Convert primary database to standby

SQL>ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY;

ERROR at line 1:

ORA-01093: ALTER DATABASE CLOSE only permitted with no sessions connected

if the above error occurred, then try to shut the database then startup then try again.

-If still the same error persist,do the following in sequence: -

SQL> SHOW PARAMETER JOB_QUEUE_PROCESSES; (value for this should be 0)

SQL> SELECT SID, PROCESS, PROGRAM FROM V$SESSION;

SQL> alter database commit to switchover to PHYSICAL STANDBY WITH SESSION SHUTDOWN;

-- Shutdown primary database

SHUTDOWN IMMEDIATE;

-- Mount old primary database as standby database

STARTUP NOMOUNT;

ALTER DATABASE MOUNT STANDBY DATABASE;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

On the standby database issue the following commands: -

-- Convert standby database to primary

CONNECT / AS SYSDBA

ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;



SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY
*
ERROR at line 1:
ORA-16139: media recovery required


if the above error occurred then execute the below commands:


SQL> alter database recover managed standby database finish;


SQL> alter database commit to switchover to physical primary with session shutdown;

-- Shutdown standby database

SHUTDOWN IMMEDIATE;

-- Open old standby database as primary

STARTUP;

enjoy:-)

Defining the time interval at which redo log wil apply to standby:

Here in this post we will learn how to set time interval of redo log application to standby.

How to create a physical standby in oracle 11g:

You can set a delay between the arrival of the archived redo log and it being applied on the standby server using the following commands.

On standby server: 

-ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

-ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DELAY 30 DISCONNECT FROM SESSION;

You may also changed the scenario as NODELAY means redo log will immediately applied using the below command:

-ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

-ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY DISCONNECT FROM SESSION;

enjoy:-)

External tables with Datapump

External tables are largely used as a convenient way of moving data into and out of the database.They let you query data in a flat file as though the file were an Oracle table.

Oracle uses SQL*Loader functionality, through the ORACLE_LOADER access driver to move data from the flat file into the database; it uses a Data Pump access driver to move data out of the db into a file in an Oracle-proprietary format, and back into the database from files of that format

Real-Life use of External Tables:

Suppose that you receive a daily .csv file from a reporting department. Instead of writing a SQL*Loader script to import each day's .csv file into your database, you can simply create an external table and write an "insert ... select" SQL query to insert the data directly into your tables. Place the day's CSV file in the location specified in the external table definition, run the query, and you're done.

Lets see how its done:

Choose or create a OS directory where you will put your .CSV(..flat file).

In my case i have taken : 'D:\et\'

My CSV file will look like this having name "emp.CSV"

001,Hutt,Jabba,896743856,jabba@thecompany.com,18

002,Simpson,Homer,382947382,homer@thecompany.com,20

003,Kent,Clark,082736194,superman@thecompany.com,5

004,Kid,Billy,928743627,billythkid@thecompany.com,9

005,Stranger,Perfect,389209831,nobody@thecompany.com,23

The next step is to create this directories in Oracle, and grant read/write access on it to the Oracle user who will be creating the external table

SQL> create directory opump as 'D:\et\';

Directory created.

SQL> grant read,write on directory opump to gg;

Grant succeeded.

Now,create the external table correspond to .CSV file.

SQL> conn gg/gg

Connected.

SQL>  create table xtern_empl_rpt

  2          ( empl_id varchar2(3),

  3            last_name varchar2(50),

  4            first_name varchar2(50),

  5            ssn varchar2(9),

  6           email_addr varchar2(100),

  7            years_of_service number(2,0)

  8          )

  9          organization external

 10         ( default directory opump

 11           access parameters

 12           ( records delimited by newline

 13             fields terminated by ','

 14           )

 15           location ('emp.csv')

 16       );

Table created.

External table is successfully created now.Lets check the data in oracle!

SQL> sho user;

USER is "GG"

SQL> select * from xtern_empl_rpt ;

Note: If u add more records in the csv file i.e. emp.csv then oracle table "xtern_empl_rpt" will automatically updated.

For example i will add a new record in my csv file:

7,Vishu,DBA,123,itsmevishu82@gmail.com,25

Save the file..

Lets see the record if updated in oracle table.

Unloading data into an external file using DATAPUMP.

Oracle 10g lets you create a new external table from data in your database, which goes into a flat file pushed from the database using the ORACLE_DATAPUMP access driver. This flat file is in an Oracle-proprietary format that can be read by DataPump. The syntax is similar to the CREATE TABLE... ORGANIZATION EXTERNAL above, but simpler -- since you can't specify the data format, you can specify very few access_parameters. The key difference is that you must specify the access driver, ORACLE_DATAPUMP, since the access driver defaults to ORACLE_LOADER.

Lets see how its done..

First create a user called "mm" and provide the privileges as below.

SQL> create user mm identified by mm;

User created.

SQL> grant read,write on directory opump to mm;

Grant succeeded.

Now,connect to mm and create the dumpfile using external table.

SQL> conn mm/mm

Connected.

SQL> create table import_empl_info

  2      ( empl_id varchar2(3),

  3        last_name varchar2(50),

  4        first_name varchar2(50),

  5        ssn varchar2(9),

  6        birth_dt date

  7      )

  8      organization external

  9      ( type oracle_datapump

 10       default directory opump

 11       location ('empl_info_rpt.dmp')

 12     ) ;

Table created.

It will create the dumpfile called "empl_info_rpt.dmp" which can be moved and used in any other database or same.

IMPORT THE TABLE BACK IN AGAIN..

We will now again load the data into oracle from the dump being created through external table.

First drop the table "import_empl_info".

SQL> drop table import_empl_info;

Table dropped.

Now load the data again ..

SQL> create table import_empl_info

  2      ( empl_id varchar2(3),

  3        last_name varchar2(50),

  4        first_name varchar2(50),

  5        ssn varchar2(9)

  6        )

  7      organization external

  8      ( type oracle_datapump

  9       default directory opump

 10       location ('empl_info_rpt.dmp')

 11     ) ;

We have seen now how External tables in 9i and 10g provide a convenient, seamless way to move data in and out of the database

Restrictions imposed on External tables:

1.)External are not usable in many ways regular Oracle tables.

2.)You cannot perform any DML operations on external tables other than table creation.

3.)You can't create an index on an external table. 

Enjoy:-)