Oracle 12c pluggable and “common” directory recreation issue.

Recently I did a stupid thing

DROP DIRECTORY DATA_PUMP_DIR; -- issues from CDB$ROOT

before that my PDBs was able to see this standard directory:

SELECT directory_name FROM dba_directories WHERE directory_name='DATA_PUMP_DIR';

I’ve almost immediate recreate the directory as:

CREATE DIRECTORY DATA_PUMP_DIR as '/tmp';

but

GRANT READ,WRITE ON DIRECTORY DATA_PUMP_DIR TO IMP_FULL_DATABASE CONTAINER=ALL;

ERROR at line 1:
ORA-65033: a common privilege may not be granted or revoked on a local object

I even not been able to recreate it as a “local” PDB object:

DROP DIRECTORY DATA_PUMP_DIR; -- on CDB$ROOT

on pluggable database

CREATE DIRECTORY DATA_PUMP_DIR as '/u01/data/data_pump'
 *
ERROR at line 1:
ORA-00955: name is already used by an existing object

as metadata link object is still here (I believe that is Oracle’s bug).

After a bit googling I found an excellent blog:
http://blog.dbi-services.com/oracle-12c-cdb-metadata-a-object-links-internals/
that gave me an idea how to fix the issue (as usual ALTER SESSION SET “_oracle_script”=true  is our best frient with container database).

So on CDB$ROOT:

ALTER SESSION SET "_oracle_script"=true;
CREATE DIRECTORY DATA_PUMP_DIR sharing=metadata  as '/tmp';
GRANT READ,WRITE ON DIRECTORY DATA_PUMP_DIR TO IMP_FULL_DATABASE CONTAINER=ALL;

And can see again my DATA_PUMP_DIR in the pluggable database

SELECT directory_name FROM dba_directories WHERE directory_name='DATA_PUMP_DIR'; --in pluggable

 

 

 

 

 

 

 

 

Advertisements

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