Follow us: Connect on YouTube Connect on YouTube Connect on YouTube

Pages

Saturday, 2 April 2016

SQL External Tables


External tables allow Oracle to query data that is stored in flat files outside the database
ORACLE_LOADER driver is used to access any data stored in any format that can be loaded by SQL*Loader.
No DML can be performed on external tables but they can be used for query, join and sort operations.
Views and synonyms can be created against external tables.
Used in the ETL process of data warehouses since the data doesn't need to be staged and can be queried in parallel.
Should not be used for frequently queried tables

Create directory object to data location
Create directory EXT_TABLES as 'C:\Oracle\External_Tables';

Create the external table Example

CREATE TABLE emp_ext (
empno NUMBER(8),
first_name VARCHAR2(30),
last_name VARCHAR2(30))
ORGANIZATION EXTERNAL
( TYPE oracle_loader DEFAULT DIRECTORY ext_tables
ACCESS PARAMETERS
( RECORDS DELIMITED BY NEWLINE FIELDS TERMINATED BY ',' ) LOCATION
('emp1.txt','emp2.txt') )
PARALLEL 5
REJECT LIMIT 200;

You can use external table feature to access external files as if they are tables inside the database.
When you create an external table, you define its structure and location with in oracle.
When you query the table, oracle reads the external table and returns the results just as if the data had been stored with in the database.

ACCESSING EXTERNAL TABLE DATA

To access external files from within oracle, you must first use the create directory command to define a directory object pointing to the external file location
Users who will access the external files must have the read and write privilege on the directory.

Ex:

CREATING DIRECTORY AND OS LEVEL FILE

    SQL> Sqlplus system/manager
    SQL> Create directory saketh_dir as ‘/Visdb/visdb/9.2.0/external’;
     SQL> Grant all on directory saketh_dir to saketh;
     SQL> Conn saketh/saketh
     SQL> Spool dept.lst
     SQL> Select deptno || ‘,’ || dname || ‘,’ || loc from dept;
     SQL> Spool off

CREATING EXTERNAL TABLE

 SQL> Create table dept_ext
         (deptno number(2),
         Dname varchar(14),
         Loc varchar(13))
         Organization external  ( type oracle_loader
                                                 Default directory saketh_dir
                                                 Access parameters
                                                 ( records delimited by newline
                                                    Fields terminated by “,”
                                                    ( deptno number(2),
                                                      Dname varchar(14),
                                                      Loc varchar(13)))
         Location (‘/Visdb/visdb/9.2.0/dept.lst’));

SELECTING DATA FROM EXTERNAL TABLE

SQL> select * from dept_ext;
This will read from dept.lst which is a operating system level file.

LIMITATIONS ON EXTERNAL TABLES

a)     You can not perform insert, update, and delete operations
a)     Indexing not possible
b)     Constraints not possible

BENEFITS OF EXTERNAL TABLES

a)   Queries of external tables complete very quickly even though a full table scan id required with each access

b)   You can join external tables to each other or to standard tables
Share this article :

4 comments:

  1. Hi Dayakar,

    Love it absolutely! So crystalline. No mumbo jumbo. No non-sense. Straight and simple. You guys need a standing ovation for your good work.

    I tried to to move database files from one location to another while database was offline.
    steps followed
    ALTER DATABASE sample1 SET offline
    GO

    alter database sample1
    MODIFY FILE ( NAME = sample1, FILENAME = "C:\myfolder\sample1.mdf")
    GO

    alter database sample1
    MODIFY FILE ( NAME = secondfile, FILENAME = "C:\myfolder\secondfile.ndf")
    GO

    alter database sample1
    MODIFY FILE ( NAME = sample1_log, FILENAME = "C:\myfolder\sample1_log.ldf")
    GO

    ALTER DATABASE sample1 SET ONLINE
    GO
    later when I tried to bring back online it gave error :can not read from path .I colud see any files that were moved to
    myfolder location also.
    What steps are missing???

    Super likes !!! for this amazing post. I thinks everyone should bookmark this.




    MuchasGracias,
    Irene hynes

    ReplyDelete
  2. Hello There,


    I learnt so much in such little time about SQL External Tables. Even a toddler could become smart reading of your amazing articles.

    1) We had requirement that we need to alter table which contains huge data nearly 25 millions records.
    Its range partitioned table and having 60 Partitions .
    We want to add some extra columns to the same table .
    Obviously it will take more time .What is the efficient way of doing this ?
    Do I need to disable all the indexes or Table gathering
    I know that when we have bulk insertions into the table, disabling the indexes will help .

    By the way do you have any YouTube videos, would love to watch it. I would like to connect you on LinkedIn, great to have experts like you in my connection (In case, if you don’t have any issues).


    Gracias,
    Samson

    ReplyDelete
  3. Marhaba,,


    Fully agree on #topic. We’re seeing a lot of projects tackle big complex problems but few seem to have taken into consideration and in particular reasons to adopt.


    I want to execute the different files at the run time in the SQL PLUS

    Ex: I have two different files like ABC.SQL, XYZ.SQL,

    I had declared one bind variable to hold file_name .

    var file_name VARCHAR2(200);
    DECLARE
    sr VARCHAR2(20) :='ABC';
    BEGIN
    IF sr ='ABC' THEN
    :file_name :='ABC.SQL';
    ELSE
    :file_name :='XYZ.SQL';
    END IF;
    END;
    /
    PRINT file_name


    Now i want to execute the file which is there in :file_name bind variable

    SQL>@:file_name



    It was cool to see your article pop up in my google search for the process yesterday. Great Guide.
    Keep up the good work!


    morgan

    ReplyDelete
  4. Hello There,

    Fully agree on SQL External Tables . We’re seeing a lot of projects tackle big complex problems but few seem to have taken into consideration and in particular reasons to adopt.

    My table column type is clob. Field value contain double quotes

    id,text_clob,date

    1,"\"data1\",0,1,1,1,\"data2\"","2018-03-03 09:08:45"

    can you kindly help loader query. i cant use replace in clob field.
    It was cool to see your article pop up in my google search for the process yesterday. Great Guide.
    Keep up the good work!

    Thank you,
    Preethi.

    ReplyDelete