Showing posts from February, 2024

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