Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Rajeshwaran.

Asked: May 21, 2019 - 3:58 pm UTC

Last updated: August 05, 2019 - 9:07 am UTC

Version: 18.0.0

Viewed 1000+ times

You Asked

Team,

was reading this blog post - the demo at the blog was from 19c - how ever it still claims that even in 18c we can't have datapump activities over directory object point to Symbolic links.

http://christian-gohmann.de/2019/05/19/symbolic-links-in-directory-objects-not-permitted-with-oracle-18c-19c/

but i see the other way around. kindly help us to understand if i am missing something in this below demo.

/u02/app/oracle/product/18.0.0.0/dbhome_2
[oracle@local-host dbhome_2]$ echo $ORACLE_HOME
/u02/app/oracle/product/18.0.0.0/dbhome_2
[oracle@local-host dbhome_2]$ mkdir $ORACLE_HOME/hello2
[oracle@local-host dbhome_2]$ ln -s $ORACLE_HOME/hello2 /home/oracle/hello2
[oracle@local-host dbhome_2]$

[oracle@local-host dbhome_2]$ ls -ltr /home/oracle
total 248
-rw-r--r-- 1 oracle asmadmin   1288 May 21 11:17 export.log
lrwxrwxrwx 1 oracle oinstall     48 May 21 11:23 hello2 -> /u02/app/oracle/product/18.0.0.0/dbhome_2/hello2
[oracle@local-host dbhome_2]$



sys@PDB1> create directory DP as '/home/oracle/hello2';

Directory created.

sys@PDB1> grant read,write on directory dp to c##rajesh ;

Grant succeeded.

sys@PDB1> ! expdp c##rajesh/Password-1@localhost/pdb1 directory=DP tables=EMP dumpfile=EXP_EMP_02.dmp nologfile=YES

Export: Release 18.0.0.0.0 - Production on Tue May 21 11:28:35 2019
Version 18.1.0.0.0

Copyright (c) 1982, 2018, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 18c EE Extreme Perf Release 18.0.0.0.0 - Production
Starting "C##RAJESH"."SYS_EXPORT_TABLE_01":  c##rajesh/********@localhost/pdb1 directory=DP tables=EMP dumpfile=EXP_EMP_02.dmp nologfile=YES
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
. . exported "C##RAJESH"."EMP"                           8.781 KB      14 rows
Master table "C##RAJESH"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for C##RAJESH.SYS_EXPORT_TABLE_01 is:
  /home/oracle/hello2/EXP_EMP_02.dmp
Job "C##RAJESH"."SYS_EXPORT_TABLE_01" successfully completed at Tue May 21 11:28:49 2019 elapsed 0 00:00:13





and we said...

I think its a slight error in the blog post. My understanding is that:

18c - we handled UTL_FILE, BFILE, External Tables
19c - we added data pump

eg (using same setup as your demo) from 18c

[oracle@host18 ~]$ expdp scott/tiger@pdb1 directory=DP dumpfile=EXP_EMP_03.dmp nologfile=yes

Export: Release 18.0.0.0.0 - Production on Wed May 22 09:21:44 2019
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Starting "SCOTT"."SYS_EXPORT_SCHEMA_01":  scott/********@pdb1 directory=DP dumpfile=EXP_EMP_03.dmp nologfile=yes 
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
. . exported "SCOTT"."EMP"                               8.773 KB      14 rows
. . exported "SCOTT"."DEPT"                              6.023 KB       4 rows
. . exported "SCOTT"."SALGRADE"                          5.953 KB       5 rows
. . exported "SCOTT"."BONUS"                                 0 KB       0 rows
Master table "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is:
  /home/oracle/hello2/EXP_EMP_03.dmp
Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at Wed May 22 09:22:16 2019 elapsed 0 00:00:32

SQL> declare
  2    f utl_file.file_type;
  3  begin
  4    f := utl_file.fopen('DP','demo.dat','W');
  5  end;
  6  /
declare
*
ERROR at line 1:
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 41
ORA-06512: at "SYS.UTL_FILE", line 478
ORA-06512: at line 4



Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Top 19 things about 19c

Rajeshwaran Jeyabal, August 02, 2019 - 7:50 am UTC

Team,

Anything like "Top 19 things about 19c" by AskTom team available somewhere? like how we do for previous releases?

Connor McDonald
August 05, 2019 - 9:07 am UTC

Check my slideshare http://bit.ly/slideshare-connor

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library