OCI : Create DIRECTORIES for expdp impdp on autonomous database

 OCI : Create DIRECTORIES for expdp impdp on autonomous database  and uploading  files.

• We can not drop directory from sql prompt if bucket having data stored on particular directory .

dbms_cloud package : 

• dbms_cloud.get_object : Copy object from storage bucket to autonomous DB
• dbms_cloud.put_object : Copy object from autonomous Db to object bucket storage

• Check the current status of database directories

SELECT * FROM DBA_DIRECTORIES;

• Create new directory  and provide  privilege's to test user

GRANT CREATE ANY DIRECTORY TO TEST;

GRANT DROP ANY DIRECTORY TO TEST;

• Create a directory that points to the root directory of the user. 

CREATE OR REPLACE DIRECTORY ROOT_DIR AS '';

• Check the location of root directories

SELECT * FROM DBA_DIRECTORIES WHERE DIRECTORY_NAME='ROOT_DIR';

CREATE OR REPLACE DIRECTORY ROOT_DIR AS '';

• Create a directory object to points to a sub-directory named as stage

CREATE DIRECTORY staging AS 'stage';

• Check the location of staging directories

SELECT * FROM dba_directories where directory_name='STAGING';

• The LIST_FILES function returns no row because there is no file under any directory. 

SELECT * FROM DBMS_CLOUD.LIST_FILES('ROOT_DIR');

• Copying Files from bucket to object storage.
• Upload object into bucket  storage and check the details. 

• Copy object profiles 
  
  
  

• Generate token 

• Copy the authentication  token clipboard

• Authentication token obtained from the previous step

SET DEFINE OFF

BEGIN

DBMS_CLOUD.CREATE_CREDENTIAL (

 CREDENTIAL_NAME => 'ADMIN_CRED_NAME',

 USERNAME => 'varunyadav@gmail.com',

 PASSWORD => '.ypEHus4L01bAYeZd+H0' 

);

END;

/ 

• Verify the staging directory 

SELECT CREDENTIAL_NAME FROM USER_CREDENTIALS;

• Uploaded file from the object storage to the directory objects.

BEGIN

DBMS_CLOUD.GET_OBJECT(

CREDENTIAL_NAME => 'ADMIN_CRED_NAME',

OBJECT_URI => 'https://objectstorage.ap.xxxxxx.oraclecloud.com/n/bmdsoh48o0kf/b/bucket-20210415-1806/o/wallet%20zip%20fileWallet_ORCLADB.zip',

DIRECTORY_NAME => 'STAGING');

END;

/ 

• Check the staging file directory status

 SELECT OBJECT_NAME FROM DBMS_CLOUD.LIST_FILES('STAGING');

• Check files in the staging directory status from root directory

SELECT OBJECT_NAME FROM DBMS_CLOUD.LIST_FILES('ROOT_DIR');