Ferhat's Blog

There will be only one database

External tables with symbolic links (DISABLE_DIRECTORY_LINK_CHECK)

Posted by fsengonul on November 12, 2015

The first question that was popped up in my mind when I started to use external tables in 10g days was: “Why doesn’t oracle let us use symbolic links?”

Everybody was mentioning about security issues. If the DBA is the only one to have access to the oracle machine, there may be a pardon for this restriction.

It seems, I’m not alone. When searching for something else I’ve seen DISABLE_DIRECTORY_LINK_CHECK in the 11.2 documentation.

And of course I have that itchy feeling whether it was always there in 10g, also.ūüôā

Below is the example continuing from the skeleton from psoug.


SYSTEM@L11R 20151008 14:31:47 SQL> sta demo/external_tables/5_change_it_to_symbolic_link.sql

SYSTEM@L11R 20151008 14:32:20 SQL>

SYSTEM@L11R 20151008 14:32:20 SQL> select * from ext_tab;



EMPN ENAME                JOB                  DE

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

1111 MORGAN               DIRECTOR             10

2222 HARDIE               MANAGER              30

3333 HAVEMEYER            VP MKTG              10

4444 LOFSTROM             MANAGER              10

5555 ALLEN                SECURITY             30

7369 SMITH                CLERK                20

7499 ALLEN                SALESMAN             30

7521 WARD                 SALESMAN             30

7566 JONES                MANAGER              20

7654 MARTIN               SALESMAN             30



10 rows selected.



Elapsed: 00:00:00.03

SYSTEM@L11R 20151008 14:32:20 SQL> --Now create sybolic links in OS.

SYSTEM@L11R 20151008 14:32:20 SQL> --

SYSTEM@L11R 20151008 14:32:20 SQL> --mv demo1.dat real_demo1.dat

SYSTEM@L11R 20151008 14:32:20 SQL> --mv demo2.dat real_demo2.dat

SYSTEM@L11R 20151008 14:32:20 SQL> --ln -s real_demo1.dat demo1.dat

SYSTEM@L11R 20151008 14:32:20 SQL> --ln -s real_demo2.dat demo2.dat

SYSTEM@L11R 20151008 14:32:20 SQL>



[oracle@ol6-112-rac1 ext_dir]$ ls -altr

total 16

-rw-r--r--.  1 oracle oinstall  111 Oct  8 13:43 real_demo1.dat

-rw-r--r--.  1 oracle oinstall  121 Oct  8 13:43 real_demo2.dat

drwxr-xr-x. 34 oracle oinstall 4096 Oct  8 13:51 ..

lrwxrwxrwx.  1 oracle oinstall   14 Oct  8 14:33 demo2.dat -> real_demo2.dat

lrwxrwxrwx.  1 oracle oinstall   14 Oct  8 14:33 demo1.dat -> real_demo1.dat

drwxr-xr-x.  2 oracle oinstall 4096 Oct  8 14:37 .

[oracle@ol6-112-rac1 ext_dir]$







SYSTEM@L11R 20151008 14:32:20 SQL> --let's query again to see the error

SYSTEM@L11R 20151008 14:32:20 SQL> pause;



SYSTEM@L11R 20151008 14:33:22 SQL>

SYSTEM@L11R 20151008 14:33:22 SQL> select * from ext_tab;

select * from ext_tab

*

ERROR at line 1:

ORA-12801: error signaled in parallel query server P000, instance ol6-112-rac1.localdomain:RAC1 (1)

ORA-29913: error in executing ODCIEXTTABLEFETCH callout

ORA-29400: data cartridge error

KUP-04027: file name check failed: /home/oracle/ext_dir/demo1.dat





Elapsed: 00:00:00.01

SYSTEM@L11R 20151008 14:34:03 SQL>

SYSTEM@L11R 20151008 14:34:03 SQL> --This is the error

SYSTEM@L11R 20151008 14:34:03 SQL> --now drop and recreate with DISABLE_DIRECTORY_LINK_CHECK

SYSTEM@L11R 20151008 14:34:03 SQL> pause;



SYSTEM@L11R 20151008 14:34:04 SQL>

SYSTEM@L11R 20151008 14:34:04 SQL> drop table ext_tab;



Table dropped.



Elapsed: 00:00:00.03

SYSTEM@L11R 20151008 14:34:04 SQL>

SYSTEM@L11R 20151008 14:34:04 SQL> CREATE TABLE ext_tab (

2  empno  CHAR(4),

3  ename  CHAR(20),

4  job    CHAR(20),

5  deptno CHAR(2))

6  ORGANIZATION EXTERNAL (

7    TYPE oracle_loader

8    DEFAULT DIRECTORY ext_dir

9      ACCESS PARAMETERS (

10      RECORDS DELIMITED BY NEWLINE

11      DISABLE_DIRECTORY_LINK_CHECK

12      FIELDS TERMINATED BY ','

13      MISSING FIELD VALUES ARE NULL

14      REJECT ROWS WITH ALL NULL FIELDS

15      (empno, ename, job, deptno))

16      LOCATION ('demo1.dat','demo2.dat')

17    )

18  PARALLEL

19  REJECT LIMIT 0

20  NOMONITORING;



Table created.



Elapsed: 00:00:00.01

SYSTEM@L11R 20151008 14:34:04 SQL>

SYSTEM@L11R 20151008 14:34:04 SQL>

SYSTEM@L11R 20151008 14:34:04 SQL> pause;



SYSTEM@L11R 20151008 14:34:05 SQL> --let's query again

SYSTEM@L11R 20151008 14:34:05 SQL>

SYSTEM@L11R 20151008 14:34:05 SQL> SELECT * FROM ext_tab;



EMPN ENAME                JOB                  DE

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

1111 MORGAN               DIRECTOR             10

2222 HARDIE               MANAGER              30

3333 HAVEMEYER            VP MKTG              10

4444 LOFSTROM             MANAGER              10

5555 ALLEN                SECURITY             30

7369 SMITH                CLERK                20

7499 ALLEN                SALESMAN             30

7521 WARD                 SALESMAN             30

7566 JONES                MANAGER              20

7654 MARTIN               SALESMAN             30



10 rows selected.



Elapsed: 00:00:00.05

SYSTEM@L11R 20151008 14:34:05 SQL>

SYSTEM@L11R 20151008 14:34:05 SQL>

SYSTEM@L11R 20151008 14:34:05 SQL>

SYSTEM@L11R 20151008 14:34:05 SQL>

<b> </b>



/* What if we put the files in another folder */



[oracle@ol6-112-rac1 ext_dir]$ ls -altr

total 24

drwxr-xr-x. 35 oracle oinstall  4096 Oct 20 09:11 ..

-rw-r--r--.  1 oracle dba      14504 Oct 20 09:12 log.log

lrwxrwxrwx.  1 oracle oinstall    26 Oct 20 09:13 demo1.dat -> ../real_dir/real_demo1.dat

lrwxrwxrwx.  1 oracle oinstall    26 Oct 20 09:13 demo2.dat -> ../real_dir/real_demo2.dat

drwxr-xr-x.  2 oracle oinstall  4096 Oct 20 09:13 .





SYSTEM@L11R 20151020 09:12:34 SQL> SELECT * FROM EXT_TAB;



EMPN ENAME                JOB                  DE

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

1111 MORGAN               DIRECTOR             10

2222 HARDIE               MANAGER              30

3333 HAVEMEYER            VP MKTG              10

4444 LOFSTROM             MANAGER              10

5555 ALLEN                SECURITY             30

7369 SMITH                CLERK                20

7499 ALLEN                SALESMAN             30

7521 WARD                 SALESMAN             30

7566 JONES                MANAGER              20

7654 MARTIN               SALESMAN             30



10 rows selected.





/* What if we send to /dev/null */



[oracle@ol6-112-rac1 ext_dir]$ ln -s /dev/null demo1.dat

[oracle@ol6-112-rac1 ext_dir]$ ln -s /dev/null demo2.dat

[oracle@ol6-112-rac1 ext_dir]$



SYSTEM@L11R 20151020 09:14:19 SQL> SELECT * FROM EXT_TAB;



no rows selected



Elapsed: 00:00:00.38

SYSTEM@L11R 20151020 09:26:19 SQL>

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: