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
Hi Dayakar,
ReplyDeleteLove 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
Hello There,
ReplyDeleteI 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
Marhaba,,
ReplyDeleteFully 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
Hello There,
ReplyDeleteFully 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.
Oracle apps and Fusion Self Paced Training Videos by Industry Experts. Please Check oracleappstechnical.com
ReplyDeleteOracle Apps R12 and Oracle Fusion Cloud Self Paced Online Training Videos Published on Udemy with Life Time Access & Live Meeting Support to Clear your Queries. Avail 25% to 80% discount. Please Check https://www.oracleappstechnical.com for Never Before Offers and Discount Coupon Codes.
ReplyDelete