برای جداول از نوع External Table، تنها متادیتا و شکل تعریف جدول در درون بانک ذخیره خواهد شد و دیتای اصلی جدول، در خارج از بانک و در سطح سیستم عامل قرار می گیرد.

در مثال زیر قصد داریم برای اطلاعات فایل mytbl.csv، یک جدول از نوع external و با نام mytbl ایجاد کنیم. مراحل انجام کار را در ادامه خواهید دید.

مثال: محتویات فایل mytbl.csv را در قسمت زیر می بینید:

[oracle@myhost ~]$ cat /home/oracle/mytbl.csv

1,vahid,usefzadeh,oracle database

2,reza,hosseini,sqlserver database

3,nima,alavi,mysql database

برای دسترسی به محتویات فایل mytbl.csv از داخل بانک، باید دایرکتوری ای را در این لایه ایجاد کنیم:

SQL> create directory drm as ‘/home/oracle’;

Directory created.

بعد از ایجاد دایرکتوری، باید متناسب با ساختار اطلاعات موجود در فایل mytbl.csv، جدولی را ایجاد کنیم.

در هنگام ساخت جدول، تعیین پارامترهایی زیر، الزامی می باشد:

Default directory: تعیین نام دایرکتوری پیش فرض

TYPE: این پارامتر access driver را مشخص خواهد کرد که ORACLE_LOADER درایور پیش فرض خواهد بود و امکان بارگذاری دیتا، از محیط بیرون به بانک را فراهم خواهد کرد علاوه بر این access driver، دایورهای دیگری نظیر ORACLE_DATAPUMP، ORACLE_HIVE و ORACLE_HDFS هم وجود دارند.

Access parameters: در این قسمت، می توانیم لیستی از پارامترهای که ساختار جدول mytbl و محتویات فایل mytbl.csv را تعریف می کنند، تعیین کنیم برای مثال، دو پارامتر زیر، از این قبیل هستند:

• records delimited by: تعیین کارکتر جداکننده رکورد

• Fields terminated by: تعیین کاراکتر جداکننده فیلد

PARALLEL: تعیین خصیصه همروندی برای جدول

location: تعیین نام فایلی که اطلاعات در ان قرار دارد(در مثال ما، mytbl.csv).

reject limit: چه تعداد خطا قابل قبول است.

در قسمت زیر، جدولی با ساختار دیتای موجود در فایل mytbl.csv را ایجاد می کنیم:

SQL> create table usef.mytbl ( id number, name varchar2(100), last_name varchar2(100), comment_ varchar2(100))

organization external (

  type oracle_loader

  default directory drm

  access parameters (

    records delimited  by newline

    fields  terminated by ‘,’

  )

  location (‘mytbl.csv’)

  )

  reject limit unlimited;

Table created.

با ساخت این جدول، به دیتا دسترسی خواهیم داشت:

select * from mytbl;

همچنین plan اجرایی دستور را در قسمت زیر می بینید:

بعد از ایجاد یک external table، می توان خصیصه های ان(دایرکتوری پیش فرض، پارامتر parallel و …) را تغییر داد، مثالهای زیر را ببینید:

مثال اول: تغییر دایرکتوری پیش فرض مربوط به جدول mytbl:

SQL> create directory ext_dr as ‘/home/oracle’;

Directory created.

SQL> ALTER TABLE usef.mytbl DEFAULT DIRECTORY ext_dr;

Table altered.

مثال دوم: تغییر و یا اضافه کردن فایلی که حاوی دیتای اصلی می باشد:

[oracle@myhost ~]$ cp mytbl.csv mytbl2.txt

SQL> ALTER TABLE usef.mytbl LOCATION (‘mytbl.csv’, ‘mytbl2.txt’);

Table altered.

مثال سوم: تغییر مقدار پارامترهای Reject limit و parallel:

SQL> ALTER TABLE usef.mytbl REJECT LIMIT 145 parallel 4;

Table altered.

دستور table alter، خصوصیتهای جدول را در سطح سیستم تغییر خواهد داد، در صورتی که بخواهیم خصیصه جدول را در سطح یک “دستور” تغییر دهیم، می توانیم از قابلیت جدید اوراکل 12c بهره بگیریم:

SELECT * FROM usef.mytbl EXTERNAL MODIFY (LOCATION (‘mytbl3.txt’));

با این دستور، دیتافایل مربوط به جدول mytbl به mytbl3.txt تغییر خواهد کرد.

نکته 1: مشابه sqlloader، امکان تعیین سه پارامتر LOGFILE، BADFILE و DISCARDFILE هم در زمان ساخت external table وجود دارد و با کمک دایرکتوری، می توان مسیر هر کدام را تفکیک کرد:

 access parameters (

 records delimited  by newline

 LOGFILE drm:’log_data.log’

 BADFILE drm:’bad_data.bad’

 DISCARDFILE drm:’dis_data.disc’

 fields  terminated by ‘,’

 )

نکته 2: اگر در فایل متنی حاوی دیتا، از جداکننده خاصی استفاده نشده باشد و طول فیلدها ثابت و مشخص باشد، می توان مشابه با sqlLoader، از عبارت position، برای جدا کردن فیلدها از هم استفاده کرد:

access parameters (

     records delimited by newline

     fields (

       field_1 position(1: 7) char( 7),

       field_2 position(7:20) char(20)

    )

  )

نکته مهم دیگری که در مورد external table باید توجه شود، قرار داشتن این نوع از جداول در حالت read only می باشد به همین جهت، امکان اجرای دستورات dmlای بر روی این نوع از جداول وجود ندارد:

SQL> insert into mytbl values(4,’samad’,’marfavi’,’DB2′);

ORA-30657: operation not supported on external organized table

همچنین ایجاد ایندکس و یا constraint، بر روی فیلدهای یک external table امکان پذیر نخواهد بود:

SQL> create index ind on mytbl(name);

ORA-30657: operation not supported on external organized table

SQL> alter table usef.mytbl add constraint ccc primary key (id);

ORA-30657: operation not supported on external organized table

البته امکان ایجاد ویو، بر روی این جدول وجود دارد:

SQL> create view myview as select name,last_name from usef.mytbl;

View created.

نکته: برای مشاهده اسامی external tableهای دیتابیس، می توان از ویوی dba_external_tables استفاده کرد.

درایور ORACLE_DATAPUMP

در مثالهای قبلی با درایور oracle_loader آشنا شدیم علاوه بر این درایور، ORACLE_DATAPUMP هم وجود دارد که می توان از طریق آن، اطلاعات موجود در بانک را در یک فایل باینری ذخیره کرده و از اطلاعات این فایل باینری، در همان بانک و یا بانک دیگر، استفاده نمود.

مثال: با اجرای دستور زیر، یک فایل باینری با نام my_binary_file.dmp در دایرکتوری drm ایجاد خواهد شد که محتوای ان، همان محتوای جدول mytbl خواهد بود:

CREATE TABLE my_binary_file

ORGANIZATION EXTERNAL

(

TYPE ORACLE_DATAPUMP

DEFAULT DIRECTORY drm

LOCATION (‘my_binary_file.dmp’)

)

AS SELECT * FROM mytbl;

در دایرکتوری drm، این فایل را مشاهده خواهیم کرد:

[oracle@myhost ~]$ ls -lh my_binary_file.dmp

-rw-r—– 1 oracle oinstall 12K Jan 19 16:30 my_binary_file.dmp

با انتقال این فایل به سرور دیگر، می توان با کمک ویژگی external table، این اطلاعات را بدون انتقال به جدولی از بانک، مورد دسترسی قرار داد:

SQL> create directory drm_bin as ‘/home/oracle’;

Directory created.

SQL> CREATE TABLE MYTBL_binary (

 id        NUMBER,

  name      VARCHAR2(100),

  last_name VARCHAR2(100),

  comment_  VARCHAR2(100))

ORGANIZATION EXTERNAL (

TYPE ORACLE_DATAPUMP

DEFAULT DIRECTORY drm_bin

LOCATION (‘my_binary_file.dmp’)

);

Table created.

با اجرای select بر روی این external table، خروجی مورد نظر را خواهیم دید:

SQL> select * from  MYTBL_binary;

 

 

 

.