Find the query taking long time to execute in Oracle Select module,parsing_schema_name,inst_id,sql_id,CHILD_NUMBER,sql_plan_baseline,sql_profile,plan_hash_value,sql_fulltext, to_char(last_active_time,’DD/MM/YY HH24:MI:SS’ ),executions, elapsed_time/executions/1000/1000, rows_processed,sql_plan_baseline from gv$sql where executions <> 0 order by elapsed_time/executions desc Find the highest …
-
-
Check the query is triggered from any procedure SELECT s.sql_id, s.sql_text FROM gv$sqlarea s JOIN dba_objects o ON s.program_id = o.object_id and o.object_name = ‘&procedure_name’
-
Sniped Session Sniped Session is occurred when IDLE_TIME is set in users or the default profile exceed then this will kill the sessions in the database then status column value …
-
Find top 10 Queries from high physical read SELECT schema, sql_text, disk_reads, round(cpu,2) FROM (SELECT s.parsing_schema_name schema, t.sql_id, t.sql_text, t.disk_reads, t.sorts, t.cpu_time/1000000 cpu, t.rows_processed, t.elapsed_time FROM v$sqlstats t join v$sql …
-
Check the dataguard status for two Node RAC If you have 3 Node RAC then you can add more union clause and copy paste the select query from union clause …
-
Error: ORA-00018 maximum number of sessions exceeded SQL> drop table test; drop table test * ERROR at line 1: ORA-00018: maximum number of sessions exceeded Cause: Maximum number of session …
-
<aside> 💡 Steps to move the Oracle Grid Home location in Oracle Environment </aside> Login with root user, Stop CRS services. cd /u01/app/12.2.0/grid/bin ./crsctl stop crs ./crsctl stop cluster Login with …
-
Error ORA-04031: unable to allocate nnn bytes of shared memory Solution Need to add more memory to the Shared pool area. Check the size of memory allocate to shared_pool_size, shared_pool_reserved_size …
-
Check the Generated archive log in Primary Database –Run on Primary Server: select thread#, max(sequence#) “Last Primary Seq Generated” from v$archived_log val, v$database vdb where val.resetlogs_change# = vdb.resetlogs_change# group by …
-
Enable DDL logging in Oracle can be done in several ways. Enable the parameter ENABLE_DDL_LOGGING in Oracle 12c Enable the audit of the database and enable DDL auditing on objects …