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. *Acti...

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 :-)

or : ORA-21000: error number argument to raise_application_error of -65035 is out of range Cause: this error caused because of an unrecoverable transaction in the source DB. Solution: 1. either stop and start your pluggable source DB 2. identify the open transaction and kill it if you can. use this query to do so: select * from v$transaction t,v$session  s where t.ses_addr = s.saddr;

Hi there if you face this nagging thing that the output of shell_exec command is appering in one long line you must keep reading this super easy solution you will thank me after that let say the output is saved in $output variable <php     $output = shell_exec($cmd);     echo $output;  # will pring a long line with the output    # in order to fix the output to get line breaks  use this code   foreach(preg_split("/((\r?\n)|(\r\n?))/", $output_including_status) as $line){   {          echo "<br/>";          echo $line;    } ?> have fun. support me.

when working with EXTERNAL tables you should be aware of LIMIT FLAG REJECT LIMIT 0 which means that any null values in the external file will be rejected and this error will be raised when trying to access this table ORA-29913: error in executing ODCIEXTTABLEFETCH callout ORA-30653: reject limit reached if you want to see empty fields as NULL values row add this line to your table definition: MISSING FIELD VALUES ARE NULL if you want to ignore empty lines from being showed as rows add these two lines to your table definition: MISSING FIELD VALUES ARE NULL REJECT ROWS WITH ALL NULL FIELDS

First look for you session status: select * from v$session where status = 'ACTIVE'; then check the long operation table to try to estimate the progress pace SELECT ( sofar / totalwork) * 100 as percent , FROM v $ session_longops WHERE sofar <> totalwork If you encounter this wait event : " wait for unread message on broadcast channel"  probably your session is still alive and running

When trying to copy PDB using SQL> create pluggable database TARGET_PDB from SOURCE_PDB; you may face this error the solution is very simple, you need to increase the parmarter  DB_FILES SQL> show parameter db_files; NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ db_files                             integer     200 key points: when PDB is closed - the files still exists but you won't see them when counting from  CDB_DATA_FILES view. to  drop a pluggabe database including his datafiles use this command: SQL> drop pluggable database PDB_NAME including datafiles;