Backing Up Oracle Directories and Grants

Backing up Oracle directory definitions and associated grants is essential for efficient database management. Here are the concise steps to back up directories and their grants using SQL scripts.

1. Backup Oracle Directory Definitions

Generate SQL statements to recreate directories:

spool all_directories_backup.sql
set pages 100 lines 100
select 'create or replace directory ' || DIRECTORY_NAME || ' as ''' || DIRECTORY_PATH || ''';' 
from dba_directories 
order by 1;
spool off;

2. Backup Directory Grants

Generate SQL statements for READ and WRITE grants on directories:

spool all_directory_grants_backup.sql
set pages 1000 lines 1000
select 'grant ' || PRIVILEGE || ' on ' || OWNER || '.' || TABLE_NAME || ' to ' || GRANTEE || ';'
from dba_tab_privs
where PRIVILEGE IN ('READ', 'WRITE') 
and TABLE_NAME IN (select DIRECTORY_NAME from dba_directories);
spool off;

Restore Directories and Grants:

To restore, execute the generated .sql files in the same order:

@all_directories_backup.sql
@all_directory_grants_backup.sql

Conclusion:

These scripts provide a quick and reliable way to back up and restore Oracle directories and grants, ensuring a streamlined database management process.

Also read: