Technical troubleshooting and more

Hi All, You may think that in order to query external table you should have only read permission on the Directory Object which sounds very reasonable.... but in some external table the definition include this part of code: ACCESS PARAMETERS         ( records delimited by newline           LOGFILE   "EXTERNAL_FILES" : 'faults.txt'       FIELDS TERMINATED BY ';'             missing FIELD values are null this definition force a write permission on the directory object.  else you will run into this error: ORA-29913: error in executing ODCIEXTTABLEOPEN callout ORA-29400: data cartridge error KUP-04074: no write access to directory object EXTERNAL_FILES 29913. 00000 -  "error in executing %s callout" *Cause:    The execution of the specified callout caused an error. *Action:   Examine the error messages take appropriate action this will do the work: GRANT READ, WRITE ON DIRECTORY  EXTERNAL_FILES TO USER;

if you have a process that run over your DB daily, or any other use cases that you need to recover your users after it you should simply run this query below to generate a simple script using DBMS_METADATA functions and run it over the instance when you desired to: SQL> set head off set pages 0 set long 9999999 spool create_users.sql SELECT DBMS_METADATA.GET_DDL('USER', USERNAME) || '/' DDL FROM DBA_USERS UNION ALL SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', USERNAME) || '/' DDL FROM  DBA_USERS UNION ALL SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT', USERNAME) || '/' DDL FROM DBA_USERS UNION ALL SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', USERNAME) || '/' DDL FROM DBA_USERS; spool off; enjoy :-)