Audit Files are still generated after setting audit_trail =off ;

If you are hitting this issue, then you are hitting the bug as mentioned in Oracle Support note 21133343.8 Bug 21133343 is there. *.aud File is Generated in Unified Auditing Environment Even When AUDIT_TRAIL=NONE This issue can be encountered in all versions of Oracle Database below 12.2 There is no solution 🙂 but to manually delete… Continue reading Audit Files are still generated after setting audit_trail =off ;


Increase recovery speed with _cleanup_rollback_entries

Issue While upgrading Oracle Apps upgrade project for big telecom giant where upgrade scripts were playing the game 🙂 me and my team mate Sunil Yadav, Apps DBA were playing with mass updation script. Mass update of 400 million records out of 700 million records on big apps table “AP_INVOICE_DISTRIBUTIONS_ALL” after it ran for more… Continue reading Increase recovery speed with _cleanup_rollback_entries


Nested Loop join hiding bad data

Last Friday , got interesting issue where Oracle Applications developer , showed that with same data in tables, it is running fine in one instance while it is giving error on another. As per query design, the developer was doing TO_DATE(PUCIF.VALUE), which expects data in column of date  datatype although PUCIF.VALUE is declared varchar2. SQL> desc… Continue reading Nested Loop join hiding bad data


Missing parenthesis – SQL Tuning

Encountered the issue of slow performance of SQL coming due to missing brackets in old/legacy code. This is second time hitting the same kind of issue. SQL Execution plans are going bad after performing the upgrade. Database is upgraded to from SQL with missing parethesis SELECT APPROVAL_HISTORY_ID,TRX_ID,SITE_ID,TRX_START_DATE,TRX_END_DATE, APPROVER_ID,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN, ORG_ID,ITERATION,RESPONSE,APPROVER_NAME,CREATION_DATE,AMOUNT_APPROVED, APPROVER_COMMENTS FROM apps.XX_INV_APRVL_HIST_V WHERE trx_id=9887773… Continue reading Missing parenthesis – SQL Tuning

SQL Server

SQL Server Profiling

1. Start the profiling with following settings in profiler Start with custom profiling settings ( it will start with all columns and events) Events Not to be included  Brokers CLR Database Depreciation Locks (particularly release and accuired locks) Errors and warnings (moslty) OLEDB Objects Scans( particularly) Security Audit Server Sessions Transactions ( particularly start stop… Continue reading SQL Server Profiling


Debugging ORA-12518 and permissions issue for Oracle Listener

As per documentation, it means that TNS Listener process is not able to hand off client connection.  Function of Listener can be think as 2 step process: 1. It acts as ear on the Database side ( hey I am here ) and then.. 2. Pass the incoming request ( after hearing) to the particular… Continue reading Debugging ORA-12518 and permissions issue for Oracle Listener