Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

Oracle External Table

External tables are created using the sql create table.organization table statement.
 To load the bulk data using sql loader

Step 1:To create a file in os level
oracle@db ~]$ mkdir d1
oracle@db ~]$ cd d1
oracle@db d1]$ vi f1
oracle@db ~]$ export ORACLE_SID=<databasename>
oracle@db ~]$ sqlplus / as sysdba
First we connect the ‘SYS’ user then we can create a new directory

Syntax:
 Sql> conn sqlplus / as sysdba
 You must have any directory system privilege to create directories.
Syntax:
 Sql> grant create any directory to username;
Example:
 Sql> grant create any directory to sai;
Syntax:
 Sql> create directory <directory_name> as  <path_name>;
Example:
 Sql> create directory d1 as  ‘/home/oracle/db/’;
Syntax:
 Sql> grant read,write on directory directoryname to username;
Example:
 Sql> grant read,write on directory d1 to nijam;

Create table:
 Organization external table:using type oracle loader
 Default directory:
 Access parameters( :
 Records delimited by newline
 Fields terminated by
 Location :specifies the location of the external date,the location is specified as a list of directory objects and filenames.if the directory objects is not specified,then the default directory objects is used as the file location.

Example program:
 Create table filename(study) (id number(5),name varchar2(15))
 Organization external (type oracle_loader
 Default directory directoryname
 Access parameters(
 Records delimited by newline
 Fields terminated by ‘ , ‘)
 Location (‘filename’));

SQL > select * from tablename;


Comments

Popular posts from this blog

PostgreSQL Pgbadger Installation On Linux

PostgreSQL pgBadger

How to configure Replication Manager (repmgr) ?

Migrating From Oracle to PostgreSQL using ora2pg open source tools

How To Schedule PostgreSQL Jobs using pgAgent on Linux plateform