Use below script to get the current running sql details.   select sesion.sid, sesion.username, optimizer_mode, hash_value, address, cpu_time, elapsed_time, sql_text from v$sqlarea sqlarea, v$session sesion where sesion.sql_hash_value = sqlarea.hash_value and sesion.sql_address = sqlarea.address and sesion.username is not null;   Output: SID USERNAME OPTIMIZER_ HASH_VALUE ADDRESS ———- —————————— ———- ———- —————- CPU_TIME ELAPSED_TIME ———- ———— SQL_TEXT […]

           You can use this script to check tablespace space details in 12c Multitenant database. It will provide information of both root CDB and PDB. SET LINES 132 PAGES 100 COL con_name FORM A15 HEAD “Container|Name” COL tablespace_name FORM A15 COL fsm FORM 999,999,999,999 HEAD “Free|Space Meg.” COL apm FORM 999,999,999,999 […]

            V$SQL_PLAN_MONITOR displays real time plan level monitoring statistics for the currently running sql queries.. Each row in V$SQL_PLAN_MONITOR corresponds to an operation of the execution plan being monitored.   — sql_is from v$sql_monitor SELECT sql_id FROM v$sql_monitor; SQL_ID ——- 00tr6c6tngp4x   Use below query to get the plan  details:   […]

Get the Top 10 cached sql statements details SELECT sql_id,child_number,sql_text, elapsed_time FROM (SELECT sql_id, child_number, sql_text, elapsed_time, cpu_time, disk_reads, RANK () OVER (ORDER BY elapsed_time DESC) AS elapsed_rank FROM v$sql) WHERE elapsed_rank

Use below query to get the sql text of a particular sid. When the below script asks for input, enter the SID. col sql_text form a80 set lines 120 select sql_text from gv$sqltext where hash_value= (select sql_hash_value from gv$session where sid=&1) order by piece / SQL> SQL> 2 3 4 Enter value for 1: 285 […]

Use below script to find active sessions in oracle database. set echo off set linesize 95 set head on set feedback on col sid head “Sid” form 9999 trunc col serial# form 99999 trunc head “Ser#” col username form a8 trunc col osuser form a7 trunc col machine form a20 trunc head “Client|Machine” col program […]

Use below query to get the size of a database. col “Database Size” format a20 col “Free space” format a20 col “Used space” format a20 select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ‘ GB’ “Database Size” , round(sum(used.bytes) / 1024 / 1024 / 1024 ) – round(free.p / 1024 / 1024 / […]

Use below query to get child key and foreign key status:   col owner form a10 col FK_column form a15 col table_name form a30 select b.owner, b.table_name child_table,b.status, c.column_name FK_column, b.constraint_name from dba_constraints a, dba_constraints b, dba_cons_columns c where a.owner=b.r_owner and b.owner=c.owner and b.table_name=c.table_name and b.constraint_name=c.constraint_name and a.constraint_name=b.r_constraint_name and b.constraint_type=’R’ and a.owner=’&OWNER’ and a.table_name=’&TABLE_NAME’ and […]

FIND THE ARCHIVE LAG BETWEEN PRIMARY AND STANDBY:   select LOG_ARCHIVED-LOG_APPLIED “LOG_GAP” from (SELECT MAX(SEQUENCE#) LOG_ARCHIVED FROM V$ARCHIVED_LOG WHERE DEST_ID=1 AND ARCHIVED=’YES’), (SELECT MAX(SEQUENCE#) LOG_APPLIED FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED=’YES’);   CHECK THE STATUS OF DIFFERENT PROCESS:     SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY ; PROCESS STATUS THREAD# SEQUENCE# […]

 Below script will generate the DDLs of all the tablespaces in oracle. SQL>set heading off; SQL>set echo off; SQL>Set pages 999; SQL>set long 90000; SQL>spool ddl_list.sql SQL>select dbms_metadata.get_ddl(‘TABLESPACE’,tb.tablespace_name) from dba_tablespaces tb; SQL>spool off