query to check ORA errors
This query is used to check the ORA error in past one hour. query: COLUMN ORIGINATING_TIMESTAMP FORMAT A40 COLUMN message_text FORMAT A100 set linesize 300 SELECT ORIGINATING_TIMESTAMP , message_text FROM…
Read MoreAll About Oracle DB Audit
The auditing mechanism for Oracle is extremely flexible. Oracle stores information that is relevant to auditing in its data dictionary. Every time when a user attempts anything in the database…
Read MoreEBS – API to Create Bank Branch Account creation in single procedure
CREATE OR REPLACE PROCEDURE INSERT_SINGLE_BANKS IS x_response_rec apps.iby_fndcpt_common_pub.result_rec_type; l_init_msg_list VARCHAR2 (2000); o_bank_id NUMBER; x_return_status VARCHAR2 (3000); l_msg_count NUMBER; l_msg_data VARCHAR2 (3000); l_msg_dummy VARCHAR2 (3000); l_output VARCHAR2 (3000); l_extbank_rec apps.iby_ext_bankacct_pub.extbank_rec_type; l_ext_bank_branch_rec…
Read MoreCreate Requisition interface against the blanket purchase agreement for different Locations
AIM: Component is used for Create the Requisition against the Blanket Purchase Agreement For different Locations. SCRIPT: CREATE OR REPLACE PACKAGE BODY APPS.XXTTK_BPO_PKG IS PROCEDURE XXTTK_REQ ( ERRORBUF OUT VARCHAR2…
Read MoreEstimate space required for index creation
SET SERVEROUTPUT ON DECLARE v_used_bytes NUMBER(10); v_Allocated_Bytes NUMBER(10); BEGIN DBMS_SPACE.CREATE_INDEX_COST ( ‘ create index PROD.INDEX1 on PROD.EMP(EMPNO)’, v_used_Bytes, v_Allocated_Bytes ); DBMS_OUTPUT.PUT_LINE(‘Used Bytes MB: ‘ || round(v_used_Bytes/1024/1024)); DBMS_OUTPUT.PUT_LINE(‘Allocated Bytes MB: ‘…
Read MoreGlobal Temporary Table ( GTT) – key facts
The data in a GTT is written to the temporary tablespace, which is not directly protected by redo, so using a GTT improves performance by reducing redo generation. Unfortunately, prior…
Read MoreOracle 12c SQL Plan Directives – Disable | Enable | Use As Hint
What is it? In previous releases the database stored compilation and execution statistics in a shared sql area which is non persistent. Starting in 12c the database can use a…
Read MoreORA-20005: object statistics are locked (stattype = ALL) – Solution
During tuning a query, I found one table has stale statistics. While running gather stats for that table, got below error. Let me demonstrate with a demo table: SQL> execute…
Read MoreIgnore Duplicate Rows (ORA-00001) with the ignore_row_on_dupkey_index Hint
The simplest method is to add a hint to the query to overcome ORA-00001 Added in 11.2, the ignore_row_on_dupkey_index hint silently ignores duplicate values: insert /*+ ignore_row_on_dupkey_index ( acct (…
Read MoreCreate a Virtual Column:
A virtual column applies a function to a column in the table. The database only computes this at runtime. The value is not stored in the table. So instead of…
Read More