Skip to Main Content

Breadcrumb

Dev Live Dev Intro

This month we are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

Question and Answer

Connor McDonald

Thanks for the question, Rajeshwaran.

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

Answered by: Connor McDonald - Last updated: August 05, 2019 - 9:07 am UTC

Category: Database Development - 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



and you rated our response

  (1 rating)

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

Reviews

Top 19 things about 19c

August 02, 2019 - 7:50 am UTC

Reviewer: Rajeshwaran Jeyabal

Team,

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

Connor McDonald

Followup  

August 05, 2019 - 9:07 am UTC

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

More to Explore

PL/SQL

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