How to Collect Standard Diagnostic Information Using SQLT for SQL Issues

  Gather Diagnostics with SQLT In order to gather SQLT output for the query, you will need to download and install SQLT. Download SQLT To do this, download and run the SQLT tool. You can  download SQLT here .  Install SQLT Install SQL by executing the installation script sqlt/install/sqcreate.sql connected as SYS: # cd sqlt/install # sqlplus / as sysdba SQL> START sqcreate.sql During the installation you will be asked to enter values for these parameters: Optional Connect Identifier  - In some restricted-access systems you may need to specify a connect identifier like @PROD. If a connect identifier is not needed, enter nothing and just hit the "Enter" key. SQLTXPLAIN password  - Case sensitive in most systems. SQLTXPLAIN Default Tablespace  - Select from a list of available permanent tablespaces which one should be used by SQLTXPLAIN for the SQLT repository. It must have more than 50MB of free space. SQLTXPLAIN Temporary Tablespace  - Select from a list of available tempor

How to Disable Auto-Login Keystore

 Disable Auto-Login At times, It is required to perform some operations directly using an open password-protected keystore without auto-login keystore. To close AUTOLOGIN temporarily, we can use OPEN FORCE KEYSTORE to make password-protected open and implicitly close AUTOLOGIN keystore. SQL> administer key management set keystore open force keystore identified by "welcome1" container=all; keystore altered. Except to disable it temporarily, we can remove the auto-login keystore permanently. First of all, Let's see the location of WALLET_ROOT. SQL> show parameter wallet_root NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ wallet_root                          string      /u01/app/oracle/product/19.0.0                                                  /db_1/admin/ORCLCDB/wallet We should go to the location of WALLET_ROOT and check  Remove Auto-Login from Wallet [oracle@node01 ~]$ cd /u

How To Get CPU/Memory Usage for each session

  Show CPU Usage for Active Sessions SET PAUSE ON SET PAUSE 'Press Return to Continue' SET PAGESIZE 60 SET LINESIZE 300 COLUMN username FORMAT A30 COLUMN sid FORMAT 999,999,999 COLUMN serial# FORMAT 999,999,999 COLUMN "cpu usage (seconds)" FORMAT 999,999,999.0000 SELECT s.username, t.sid, s.serial#, SUM(VALUE/100) as "cpu usage (seconds)" FROM v$session s, v$sesstat t, v$statname n WHERE t.STATISTIC# = n.STATISTIC# AND NAME like '%CPU used by this session%' AND t.SID = s.SID AND s.status='ACTIVE' AND s.username is not null GROUP BY username,t.sid,s.serial# / Show Memory  Usage for Active Sessions SELECT to_char(ssn.sid, '9999') || ' - ' || nvl(ssn.username, nvl(, 'background')) ||' - '|| nvl(lower(ssn.machine), ins.host_name) "SESSION", to_char(prc.spid, '999999999') "PID/THREAD", to_char((se1.value/1024)/1024, '999G999G990D00') || ' MB' " CURRENT SIZE&qu

Important Date for Oracle Critical Patch Updates

Critical Patch Updates provide security patches for supported Oracle on-premises products. They are available to customers with valid support contracts. Starting in April 2022, Critical Patch Updates are released on the third Tuesday of January, April, July, and October (They were previously published on the Tuesday closest to the 17th day of January, April, July, and October). The next four dates are: 16 April 2024 16 July 2024 15 October 2024 21 January 2025 A pre-release announcement will be published on the Thursday preceding each Critical Patch Update release. For reference please check Oracle Site: -

Opatch is getting faild for GI patch with error code 2 while doing prereq

OPatch failed with error code 2  When checked the patch conflict for a patch, got below error. ]$opatch prereq CheckConflictAgainstOHWithDetail -ph ./ Oracle Interim Patch Installer version Copyright (c) 2020, Oracle Corporation.  All rights reserved. PREREQ session Oracle Home       : /u01/oracle/product/ Central Inventory : /u01/oracle/oraInventory    from           : /u01/oracle/product/ OPatch version    : OUI version       : Log file location : /oragrid/orahome/product/19.3.0/cfgtoollogs/opatchauto/core/opatch/opatch2023-12-22_09-37-15AM_1.log Invoking prereq "checkconflictagainstohwithdetail" Prereq "checkConflictAgainstOHWithDetail" is not executed. The details are: Unable to create Patch Object. Exception occured : /orastage/OCT23_GRID_CPU/35642822/33575402/etc/config/actions.xml with Version field of the component "delete" in actions file cannot be <null> or empty. Please check p

How to check UTL_FILE and FND_FILE are working fine or not.

Below are the scripts to check whether UTL_FILE is working or breoken. In order to fix the issue need to run the utilSunc ad DB node. set serveroutput on DECLARE file_location VARCHAR2(256) := ' '; 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');

Oracle Value Chain Planning (VCP) Instance Setup

VCP modules can be implemented on the same instance where other modules like Order Management, Purchasing, Field Service etc are implemented. But, the regular practice is to use a distributed system. In a distributed environment we implement VCP on a separate instance (called the destination instance) and bring in the data required for VCP from another instance called the Source Instance. VCP Instance setup steps Create the source instance entry into VCP. A single VCP instance can plan for many source instances. Every source instance has a unique 'Instance Code' which we give as the entry while creating the 'Application Instances'. Functional Setup: Navigation - Service Supply Chain Administrator --> Admin --> Instances Important Fields: Instance Code -- Unique Source Instance Code Version -- Version of Source Instance. (Ex: R12.1) From Source to APS -- Database Link to get data from source