Encounters of an Enterprise DB Architect

Yesterday, there was a requirement to increase the retention period of WF_DEFERRED and WF_BPEL_Q so that the developers could troubleshoot issues involving business events. This can be done this way: 1. Check the retention period of existing workqueues. SQL> SELECT owner, name, retention FROM all_queues WHERE name LIKE 'WF%'; OWNER           NAME                                                RETENTION --------------- --------------------------------------------- ------------------------------ APPS                 WF_BPEL_Q                                                0 APPLSYS         WF_INBOUND_QUEUE              ...

Yesterday, while troubleshooting a vexing ORA-29280 error, I came across 2 useful PL/SQL anonymous blocks that can be used to test FND_FILE and UTL_FILE to find out whether they are indeed working fine. UTL_FILE set serveroutput on DECLARE file_location VARCHAR2(256) := '<first entry on utl_file_dir>'; file_name VARCHAR2(256) := 'utlfile1.lst'; file_text VARCHAR2(256) := 'THIS IS A TEST'; file_id UTL_FILE.file_type; BEGIN file_id := UTL_FILE.fopen(file_Location, file_name, 'W'); UTL_FILE.put_line(file_id, file_text); UTL_FILE.fclose(file_id); EXCEPTION WHEN UTL_FILE.INVALID_PATH THEN dbms_output.put_line('Invalid path ' || SQLERRM); WHEN OTHERS THEN dbms_output.put_line('Others '|| SQLCODE || ' ' || SQLERRM); END; / References: Metalink (MOS) Note ID: 261693.1 FND_FILE set serveroutput on exec FND_FILE.PUT_LINE(FND_FILE.LOG, 'THIS IS A TEST'); HTH....

I'm back after a long hiatus! Hopefully, with stuff that you will all appreciate and use, in your day to day activities. So, here goes. Objective To find the maximum number of datafiles for a given instance (instance = database, both are interchangeable, unless we are talking of RAC). Options 1. Using init file: Check the parameter db_files. e.g: SQL> show parameter db_files 2. Using control file: Check the parameter MAXDATAFILES after generating the controlfile trace. eg: CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS ARCHIVELOG MAXLOGFILES 64 MAXLOGMEMBERS 5 MAXDATAFILES 512 MAXINSTANCES 8 MAXLOGHISTORY 14607 3. Execute the below query in sqlplus: SQL> select records_total from v$controlfile_record_section where type='DATAFILE';