How to find unusable indexes in oracle database and how to rebuild the unusable indexes

This document describes how to find unusable indexes in oracle database and how to rebuild unusable indexes to make it use.

In some scenarios we get unusable indexes in the database. We have to find which indexes are unusable and we have to rebuild them to make it use.

Use below command to find unusable indexes in the database.

col owner for a15
col index_name for a35
col table_name for a40 
select owner,index_name,status,table_name from dba_indexes where status='UNUSABLE';

Use below command to rebuild indexes.

alter index <owner>.<index name> rebuild online;

SQL> alter index XXEMPLOYEES_EMPNO_T1 rebuild online;

Thanks for going through the post......................

How to kill all unix processes (PIDs) with single command in Linux

This document describes how to kill all unix processes related to one user with single command.

Some times we have to kill all the processes related to one unix user. It is a time consuming process if  we find the PID of each process to kill it. Instead of each unix process to kill, we can kill all the unix processes of a particular user with single command. It saves our time.

Please use below command to find PIDs and kill the processes.

ps auxww | grep applprod | awk '{print $2}' | xargs kill

Thanks for going through the post......................

How to find pending concurrent requests in 11i/R12

This document describes how to find total pending concurrent requests in the system. 

Some times our concurrent requests queue fill up. We find lot of requests in pending status. In that scenarios we need to find the total number of pending requests. 

Please use below query to find pending concurrent requests details.

SELECT  DECODE(phase_Code,'P','Pending','R','Running')
                 phase
        , meaning status
        , count(*) numreqs
  FROM apps.fnd_concurrent_requests, apps.fnd_lookups
 WHERE LOOKUP_TYPE = 'CP_STATUS_CODE' AND lookup_code = status_code AND phase_code in ( 'P','R') and status_code!='D'
and requested_Start_Date < sysdate
group by   DECODE(phase_Code,'P','Pending','R','Running') , meaning
/

Thanks for going through the post.................

Top 10 space consuming files in Linux

 

This document describes how to find top 10 space consuming files in Linux. 

Some times we have to purge files to cleanup file system in the server.  We can utilize below command to find top 10 space consuming files in a given directory. We can purge the files if the files are older and are not required.

Top 10 space consuming files in linux server.

$ du -a * | sort -n -r | head -n 10

Thanks for going through the post.....................

How to prepare script to compile all invalid objects in oracle database

This document describes how to prepare script to compile all invalid objects in the database.

Some times we may get hundreds of invalid objects in the database. We need to compile to fix those objects. We can manually compile one by one if the invalid objects are less. But incase if we have hundreds of invalids, we have to prepare a script and compile all invalids by executing the prepared script.

Use below steps to prepare script of all invalid objects and compile.

Step 1:

connect to database as sysdba and execute below query. 

SQL>col owner  for a15
SQL>col object_name for a35
SQL>col object_type for a25
SQL>col status for a15

SQL>select owner,object_name,object_type,status from dba_objects where status='INVALID';

SQL>spool compile_invalids.sql
SQL>select 'alter '||object_type|| ' ' || owner ||'.'||object_name || ' compile;' from dba_objects where status='INVALID';

SQL>spool off;

Step 2:

execute above prepared script as sysdba to compile all invalid objects.

SQL>compile_invalids.log
SQL>@compile_invalids.sql
SQL>spool off;

Verify compile_invalids.log file if any errors occurred and check the invalid objects count in the database.

Thanks for going through the post......

Query to find the session details of running concurrent request using request ID

 

Use below query to find the session details of running concurrent request using concurrent request ID.

SELECT a.request_id, d.sid, d.serial# , c.SPID
 FROM apps.fnd_concurrent_requests a,
 apps.fnd_concurrent_processes b,
 gv$process c,
 gv$session d
 WHERE a.controlling_manager = b.concurrent_process_id
 AND c.pid = b.oracle_process_id
 AND b.session_id=d.audsid
 AND a.request_id = &Request_ID
 AND a.phase_code = 'R';

Pass the concurrent request ID when it prompts.

Thanks for going through the post................

Query to find PID from running concurrent request ID

 

Use below query to find PID of running concurrent request ID.

SELECT request_id,
TO_CHAR (ACTUAL_START_DATE, ‘DDMMYYYY
HH24:MI:SS’),
TO_CHAR (ACTUAL_COMPLETION_DATE, ‘DDMMYYYY
HH24:MI:SS’),
phase_code,
status_code,
os_process_id,
oracle_process_id
FROM apps.fnd_concurrent_requests
WHERE request_id = &req_id;

Thanks for going through the post.............

Query to find the sql plan of running sql in oracle database

 

Use below query to find the sql plan of running sql. This is required to troubleshoot when query is taking long time to finish.

Connect to the database as sysdba(sqlplus '/as sysdba') and execute below queries.

SQL Plan using sql_id:

SQL> select sid,serial#,sql_id from gv$session where sid=&SID;

SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('sql_id',0));

SQL Plan using SID:

SELECT  p.plan_table_output

FROM    v$session s
,  table(dbms_xplan.display_cursor(s.sql_id, s.sql_child_number)) p
where   s.sid = &sid2cplan
/

Thanks for going through the post.................

Query to find long running queries in oracle database

 

Use below query to find the sessions which are running from long time.

set lines 200 pages 200
cle bre
col sid form 99999
col start_time head "Start|Time" form a12 trunc
col opname head "Operation" form a12 trunc
col target head "Object" form a40 trunc
col totalwork head "Total|Work" form 9999999999 trunc
col Sofar head "Sofar" form 9999999999 trunc
col elamin head "Elapsed|Time|(Mins)" form 99999999 trunc
col tre head "Time|Remain|(Min)" form 999999999 trunc
select sid,sql_id,to_char(start_time,'dd-mon:hh24:mi') start_time,
         opname,target,totalwork,sofar,(elapsed_Seconds/60) elamin,
         (time_remaining/60) tre
from v$session_longops
where totalwork <> SOFAR
order by tre, start_time,sid
/

Thanks for going through the post.............

Query to find whether the object(table, package, package body etc....) is in use or not.

 

Some times we may require to find a particular object is in use or not. Use below query to find it.

select sid,object from gv$access where object=<object name>;

Replace the object name with actual object name like table name, package name, package body name etc...

If the query returns "0" rows then object is not in use. If query returns some values then it is under use.

Thanks for going through the post...............

Query to find the SID of running concurrent request

 

Use  below query to find the SID of running concurrent request. It may require to tune the concurrent requests. 

SELECT a.request_id, d.sid, d.serial# ,d.osuser,d.process , c.SPID ,d.inst_id

FROM apps.fnd_concurrent_requests a,

apps.fnd_concurrent_processes b,

gv$process c,

gv$session d

WHERE a.controlling_manager = b.concurrent_process_id

AND c.pid = b.oracle_process_id

AND b.session_id=d.audsid

AND a.request_id =&req_id
AND a.phase_code = 'R';

Pass the concurrent request ID when it prompts.

Thanks for going through the post...............................

No supported authentication methods available (server sent: publickkey,gssapi-keyex,gssapi-with-mic)

This document describes how to resolve the subject lined issue.

We are getting this issue while logging in to putty session with private key because SSH-Key format is mismatch. 

Error:

Solution: We have to convert the SSH-Key to .ppk format and use it to overcome this issue.

Please click here how to convert the SSH-Key to .ppk format.

Thanks for going through the post.............

How to convert SSH Private Key to .ppk format

 

This document describes how to convert SSH Key to .ppk format to connect from Putty client.

Some times we can't connect to instances using Putty client due to SSH key format mismatch. In that scenario we need to convert SSH private key to required format(.ppk). Follow below method to covert it.

Download puttygen software.

Click on "Load" and upload SSH private key which we want to convert.

We can able to see key has been converted successfully.

Click on OK. Click on Save 

Converted SSH Key generated successfully with .ppk format. Use this key to login putty client without password.

Thanks for going through the post......................