SHOW BLOCKERS

SHOW BLOCKERS
================
set lines 132
set pages 66
column “SER”          format 99999
column “Table”        format A10
column “SPID”         format A5
column “CPID”         format A5
column “OS User”      format A7
column “Table”        format A10
column “SQL Text”     format A40
column “Mode”         format A20
column “Node”      format A10
column “Terminal”     format A8
spool /tmp/locks.lst
select
  s.sid “SID”,
  s.serial# “SER”,
  o.object_name “Table”,
  s.osuser “OS User”,
  s.machine “Node”,
  s.terminal “Terminal”,
  –p.spid “SPID”,
  –s.process “CPID”,
  decode (s.lockwait, null, ‘Have Lock(s)’, ‘Waiting for <‘ || b.sid || ‘>’) “Mode”,
  substr (c.sql_text, 1, 150) “SQL Text”
from v$lock l,
  v$lock d,
  v$session s,
  v$session b,
  v$process p,
  v$transaction t,
  sys.dba_objects o,
  v$open_cursor c
where l.sid = s.sid
  and o.object_id (+) = l.id1
  and c.hash_value (+) = s.sql_hash_value
  and c.address (+) = s.sql_address
  and s.paddr = p.addr
  and d.kaddr (+) = s.lockwait
  and d.id2 = t.xidsqn (+)
  and b.taddr (+) = t.addr
  and l.type = ‘TM’
group by
  o.object_name,
  s.osuser,
  s.machine,
  s.terminal,
  p.spid,
  s.process,
  s.sid,
  s.serial#,
  decode (s.lockwait, null, ‘Have Lock(s)’, ‘Waiting for <‘ || b.sid || ‘>’),
  substr (c.sql_text, 1, 150)
order by
  decode (s.lockwait, null, ‘Have Lock(s)’, ‘Waiting for <‘ || b.sid || ‘>’) desc,
  o.object_name asc,
  s.sid asc;
spool off;

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter: https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

Simple Performance tuning for freshers – Easy steps

 

1. To list long-running forms user sessions

select s.sid,s.process,p.spid,s.status ,s.action,s.module, (s.last_call_et/3600) from
v$session s, v$process p where round(last_call_et/3600) >4 and action like ‘%FRM%’ and
p.addr=s.paddr ;

2. To list inactive Sessions respective username

SELECT username,count(*) num_inv_sess
FROM v$session
where last_call_et > 3600
and username is not null
AND STATUS=’INACTIVE’
group by username
order by num_inv_sess DESC;
SELECT count(*) FROM v$session where last_call_et > 43200 and username is not null AND
STATUS=’INACTIVE’;
SELECT count(*) FROM v$session where last_call_et > 3600 and username is not null AND
STATUS=’INACTIVE’;

3. To find session-id with a set of SPIDs

select sid from v$session, v$process where addr=paddr and spid in (‘11555′,’26265′,’11533’);

4. To find SQL Text given SQLHASH & SQLADDR

select piece,sql_text from v$sqltext where HASH_VALUE = &hash and ADDRESS =’&addr’ order by piece;
select piece,sql_text from v$sqltext where ADDRESS =’&addr’ order by piece;

5.Checking for Active Transactions SID

select username,t.used_ublk,t.used_urec from v$transaction t,v$session s where t.addr=s.taddr;

6. Session details from Session longops

select inst_id,SID,SERIAL#,OPNAME,SOFAR,TOTALWORK,START_TIME,LAST_UPDATE_TIME, username from gv$session_longops;

7. Session details with SPID

select sid, serial#, USERNAME, STATUS, OSUSER, PROCESS,
MACHINE, MODULE, ACTION, to_char(LOGON_TIME,’yyyy-mm-dd hh24:mi:ss’)
from v$session where paddr in (select addr from v$process where spid = ‘&spid’)
/

8. To find Undo Generated For a given session

select username,
t.used_ublk ,t.used_urec
from gv$transaction t,gv$session s
where t.addr=s.taddr and
s.sid=’&sid’;

9. To list the count of connections from other machines

select count(1),machine from gv$session where inst_id=’&inst_id’ group by machine;

10. To get the total count of sessions and processes

select count(*) from v$session;
select count(*) from v$process;
select (select count() from v$session) sessions, (select count() from v$process) processes from dual;

11. To find SQL text through SQL address

select sql_address from v$session where sid=1999;
select sql_text from v$sqltext where ADDRESS=’C00000027FF00AF0′ order by PIECE;

12. To find SQL text for different SQL hash value

select hash_value,sql_text from v$sql where hash_value in (1937378691,1564286875,
248741712,2235840973,2787402785)

13. Session details associated with SID and Event waiting for

set pages 50000 lines 32767
col EVENT for a40
select a.sid, a.serial#, a.status, a.program, b.event,to_char(a.logon_time, ‘dd-mon-yy hh24:mi’) LOGON_TIME,to_char(Sysdate, ‘dd-mon-yy-hh24:mi’) CURRENT_TIME, (a.last_call_et/3600) “Hrs connected” from v$session a,v$session_wait b where a.sid in(&SIDs) and a.sid=b.sid order by 8;

14. Active Sessions running for more than 1 hour

set pages 50000 lines 32767
col USERNAME for a10
col MACHINE for a15
col PROGRAM for a40
SELECT USERNAME,machine,inst_id,sid,serial#,PROGRAM,
to_char(logon_time,’dd-mm-yy hh:mi:ss AM’)”Logon Time”,
ROUND((SYSDATE-LOGON_TIME)(2460),1) as MINUTES_LOGGED_ON,
ROUND(LAST_CALL_ET/60,1) as Minutes_FOR_CURRENT_SQL
From gv$session
WHERE STATUS=’ACTIVE’
AND USERNAME IS NOT NULL and ROUND((SYSDATE-LOGON_TIME)(2460),1) > 60
ORDER BY MINUTES_LOGGED_ON DESC;

15.SQLs Running from longtime

alter session set nls_date_format = ‘dd/mm/yyyy hh24:mi’;
set pages 50000 lines 32767
col target format a25
col opname format a40
select sid
,opname
,target
,round(sofar/totalwork*100,2) as percent_done
,start_time
,last_update_time
,time_remaining
from v$session_longops;

16. Last/Latest Running SQL

set pages 50000 lines 32767
select inst_id,sample_time,session_id,session_serial#,sql_id from gv$active_session_history
where sql_id is not null
order by 1 desc;

17. Current Running SQLs

set pages 50000 lines 32767
col program format a20
col sql_text format a50
select b.sid,b.status,b.last_call_et,b.program,c.sql_id,c.sql_text
from v$session b,v$sqlarea c
where b.sql_id=c.sql_id;

18. Current Running SQLs

set pages 50000 lines 32767
col HOST_NAME for a20
col EVENT for a40
col MACHINE for a30
col SQL_TEXT for a50
col USERNAME for a15
select  sid,serial#,a.sql_id,a.SQL_TEXT,S.USERNAME,i.host_name,machine,S.event,S.seconds_in_wait sec_wait,
to_char(logon_time,’DD-MON-RR HH24:MI’) login
from gv$session S,gV$SQLAREA A,gv$instance i
where S.username is not null
— and S.status=’ACTIVE’
AND S.sql_address=A.address
and s.inst_id=a.inst_id and i.inst_id = a.inst_id
and sql_text not like ‘select S.USERNAME,S.seconds_in_wait%’;

Please find out all of our articles send us the Invitation or Follow us:

Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter: https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

How to find the bind variable values of the SQL statement using SQLID?

How to find the bind variable values of the SQL statement using SQLID?

SELECT sql_id,  b.name BIND_NAME,  b.value_string BIND_STRING   from gv$sql t
JOIN gv$sql_bind_capture b USING (sql_id) WHERE b.value_string IS NOT NULL
AND sql_id ='&SQLID';

Additional script which may be helpful at a later point in time:

To know the last executed query in the oracle and its hash value,sqlid

SELECT * FROM v$SQLTEXT_WITH_NEWLINES WHERE address =
  (SELECT prev_sql_addr FROM v$session WHERE audsid = userenv('SESSIONID'))
ORDER BY piece;

To know the SQL plan history of a sql using the sqlid:

select * from TABLE(DBMS_XPLAN.DISPLAY_AWR('&sqlid'));

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter: https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

Check unusable and invalid index in Oracle

 

Check unusable and not valid Index in Oracle

Below Query will convert all the unusable and not valid indexes in Oracle. The query will cover the complete index with partition index and subpartition index.

The result will give you the rebuild command of the invalid or unusable indexes.
You can directly run that and on sqlplus and make them a valid or usable state.

QUERY :

SELECT 'ALTER INDEX ' || OWNER || '.' ||
INDEX_NAME || ' REBUILD ' ||
' TABLESPACE ' || TABLESPACE_NAME || ';'
FROM DBA_INDEXES
WHERE STATUS='UNUSABLE'
UNION
SELECT 'ALTER INDEX ' || INDEX_OWNER || '.' ||
INDEX_NAME ||
' REBUILD PARTITION ' || PARTITION_NAME ||
' TABLESPACE ' || TABLESPACE_NAME || ';'
FROM DBA_IND_PARTITIONS
WHERE STATUS='UNUSABLE'
UNION
SELECT 'ALTER INDEX ' || INDEX_OWNER || '.' ||
INDEX_NAME ||
' REBUILD SUBPARTITION '||SUBPARTITION_NAME||
' TABLESPACE ' || TABLESPACE_NAME || ';'
FROM DBA_IND_SUBPARTITIONS
WHERE STATUS='UNUSABLE';

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter: https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

How to find pending concurrent requests in 11i/R12

How to find pending concurrent requests in 11i/R12

Below 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

 

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

Oracle Zero Downtime Migration 21c

Zero Downtime Migration (ZDM) 21c is available for download! ZDM is Oracle’s premier solution for moving your on-premises Oracle Database workloads to Oracle Cloud, supporting a variety of Oracle Database versions as the source and most Oracle Cloud Database Services as targets. Zero Downtime Migration 21c enhances the existing functionality by adding the long-requested Logical Migration workflow, which provides even more zero downtime migration choices!

For more reference: blogs.oracle.com click the below link

Oracle Zero Downtime Migration 21c

Logical Migration

In addition to the Physical Migration workflow which has been supported since the first version of ZDM (see for example: https://blogs.oracle.com/maa/announcing-oracle-zero-downtime-migration-release-197), ZDM 21c offers a Logical Migration to the Oracle Cloud in eight simple steps, which can be scheduled and monitored as needed. The Logical Migration workflow allows you to migrate to Oracle Autonomous Database leveraging ZDM automation.

You can leverage two logical migration workflows with different methodologies:

  • Logical Offline Migration
    • Data Pump and Database Links based methodology
    • Data Pump and Backup Location based methodology
  • Logical Online Migration
    • GoldenGate, Data Pump and Database Links based methodology
    • GoldenGate, Data Pump and Backup Location based methodology

Exadata On-Premises Support

Oracle ZDM 21c includes Exadata Database Machine as a new target. You can now migrate your current database deployments to an on-premises Exadata Database Machine, leveraging the full automation and power of ZDM. Migration to Exadata On-Premises is only supported via ZDM’s physical migration workflow.

Autonomous Database Support

Oracle ZDM 21c can now migrate databases directly to Autonomous Database using any of the logical migration workflows. Supported targets are:

  • Autonomous Transaction Processing (Shared & Dedicated)
  • Autonomous Data Warehouse (Shared & Dedicated)
  • Autonomous Database on Exadata Cloud at Customer

Non-CDB to CDB Migration Support

Non-CDB to CDB Migration is now supported in both Oracle ZDM’s physical and logical migration workflows, providing you with more migration flexibility via a simplified migration process that includes the Non-CDB to PDB conversion and hence, a wider choice of cloud targets.

Pre-Migration Advisor

A pre-migration advisor that evaluates the source database for feature compatibility and other limits is now included in the logical migration workflow only. You should leverage this advisor in order to better prepare for your database migration journey.

Support for Database Links

Logical Database Migration offers the possibility to establish direct Database Links between the source and the target, thus avoiding the need for a backup location as part of the migration workflow. Support for Database Links is optional, as you can also leverage a backup location such as Object Storage, NFS or the Recovery Appliance for your migration. Database Links based migration is not supported for Autonomous Database Dedicated.

Lighter ZDM Home

The Oracle ZDM software home is now lighter and more compact. Users will notice the difference already when downloading the binaries. ZDM installation and deployment has also been optimized and trimmed, allowing for an even faster instantiation of your first migration.

 

Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

Get ACL details in database

set lines 200
COL ACL_OWNER FOR A12
COL ACL FOR A67
COL HOST FOR A34
col PRINCIPAL for a20
col PRIVILEGE for a13
select ACL_OWNER,ACL,HOST,LOWER_PORT,UPPER_PORT FROM dba_network_acls;
select ACL_OWNER,ACL,PRINCIPAL,PRIVILEGE from dba_network_acl_privileges;

Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

How to Find the optimal undo retention size in database

SELECT d.undo_size / (1024 * 1024) “ACTUAL UNDO SIZE [MByte]”,
SUBSTR(e.value, 1, 25) “UNDO RETENTION [Sec]”,
(TO_NUMBER(e.value) * TO_NUMBER(f.value) * g.undo_block_per_sec) /
(1024 * 1024) “NEEDED UNDO SIZE [MByte]”
FROM (SELECT SUM(a.bytes) undo_size
FROM gv$datafile a, gv$tablespace b, dba_tablespaces c
WHERE c.contents = ‘UNDO’
AND c.status = ‘ONLINE’
AND b.name = c.tablespace_name
AND a.ts# = b.ts#) d,
gv$parameter e,
gv$parameter f,
(SELECT MAX(undoblks / ((end_time – begin_time) * 3600 * 24)) undo_block_per_sec
FROM v$undostat) g
WHERE e.name = ‘undo_retention’
AND f.name = ‘db_block_size’;

Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8