Oracle Database

Active Session History Longest SQL

by

SELECT STAT.INSTANCE_NUMBER, STAT.SQL_ID, SQL_TEXT, PLAN_HASH_VALUE, PARSING_SCHEMA_NAME, ELAPSED_TIME_DELTA/1000000 SECONDS, round((ELAPSED_TIME_DELTA/1000000)/60,2) MINUTES, (ELAPSED_TIME_DELTA/1000000) / EXECUTIONS_DELTA, round((ELAPSED_TIME_DELTA/1000000)/60 / EXECUTIONS_DELTA,2) “TIME PER EXEC”, STAT.SNAP_ID, SS.END_INTERVAL_TIME, EXECUTIONS_DELTA FROM DBA_HIST_SQLSTAT STAT, DBA_HIST_SQLTEXT TXT, DBA_HIST_SNAPSHOT SS WHERE STAT.SQL_ID = TXT.SQL_ID AND STAT.DBID = TXT.DBID AND SS.DBID = STAT.DBID AND SS.INSTANCE_NUMBER = STAT.INSTANCE_NUMBER AND STAT.SNAP_ID = SS.SNAP_ID AND PARSING_SCHEMA_NAME = ‘SCOTT’ AND SS.BEGIN_INTERVAL_TIME … Read more

How Many Inserts In A Day

by

SELECT STAT.SQL_ID, SQL_TEXT, PLAN_HASH_VALUE, PARSING_SCHEMA_NAME, ELAPSED_TIME_DELTA / 1000000, STAT.SNAP_ID, SS.END_INTERVAL_TIME FROM DBA_HIST_SQLSTAT STAT, DBA_HIST_SQLTEXT TXT, DBA_HIST_SNAPSHOT SS WHERE STAT.SQL_ID = TXT.SQL_ID AND STAT.DBID = TXT.DBID AND SS.DBID = STAT.DBID AND SS.INSTANCE_NUMBER = STAT.INSTANCE_NUMBER AND STAT.SNAP_ID = SS.SNAP_ID –AND STAT.DBID = ? — AND STAT.INSTANCE_NUMBER in (1,2,3) AND SS.BEGIN_INTERVAL_TIME >= SYSDATE – 7 AND lower (STAT.SQL_ID) … Read more

Tables With No foreign Keys

by

SELECT table_name, constraint_name, cname1 || NVL2 (cname2, ‘,’ || cname2, NULL) || NVL2 (cname3, ‘,’ || cname3, NULL) || NVL2 (cname4, ‘,’ || cname4, NULL) || NVL2 (cname5, ‘,’ || cname5, NULL) || NVL2 (cname6, ‘,’ || cname6, NULL) || NVL2 (cname7, ‘,’ || cname7, NULL) || NVL2 (cname8, ‘,’ || cname8, NULL) columns FROM … Read more

Table size and Growth

by

select * from (SELECT o.OWNER , o.OBJECT_NAME , o.SUBOBJECT_NAME , o.OBJECT_TYPE , t.NAME “Tablespace”, s.growth/(1024*1024) “Growth in MB”, (SELECT sum(bytes)/(1024*1024*1024) FROM dba_segments WHERE segment_name=o.object_name) “Total Size(GB)” FROM DBA_OBJECTS o, ( SELECT TS#,OBJ#, SUM(SPACE_USED_DELTA) growth FROM DBA_HIST_SEG_STAT GROUP BY TS#,OBJ# HAVING SUM(SPACE_USED_DELTA) > 0 ORDER BY 2 DESC ) s, v$tablespace t WHERE s.OBJ# = o.OBJECT_ID … Read more

Unused Indexes – With constraints

by

SELECT i.owner, a.index_name, a.table_name, c.constraint_name, a.monitoring, a.used, a.start_monitoring, ROUND (SUM (b.bytes) / 1024 / 1024 / 1024) GB, ROUND (SUM (b.bytes) / 1024 / 1024) MB, i.tablespace_name FROM all_object_usage a, dba_segments b, dba_indexes i, dba_constraints c WHERE a.index_name = b.segment_name AND a.used = ‘NO’ AND b.segment_name = i.index_name AND a.table_name = c.table_name(+) GROUP BY i.owner, … Read more

Unused Index Sizes (GB)

by

SELECT a.owner, a.index_name, a.table_name, a.monitoring, a.used, a.start_monitoring, SUM (b.bytes) / 1024 / 1024 / 1024 GB FROM all_object_usage a, dba_segments b WHERE a.index_name = b.segment_name AND a.used = ‘NO’ GROUP BY a.owner, a.index_name, a.table_name, a.monitoring, a.used, a.start_monitoring ORDER BY GB DESC;

Indexes Sizes

by

SELECT idx.index_name, SUM(bytes) FROM dba_segments seg, dba_indexes idx WHERE idx.table_owner = ‘TRANSSENDOPS_PRD’ AND idx.table_name in (select * from all_object_usage) AND idx.owner = seg.owner AND idx.index_name = seg.segment_name GROUP BY idx.index_name;