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

Calculate OS Size with Python

by

df.py #! /usr/bin/python # my first python attempt. # Mark Young 14 July 2016 import sys import os import subprocess def reportUsage(label, total, free): used = total – free print “%s=%dG, used=%dG (%d%%)” % (label, free, used, used*100/total) p = subprocess.Popen([“df”, “-k”, “.”], stdout=subprocess.PIPE) print p.stdout.read() rc = p.wait() stat = os.statvfs(“.”) total = (stat.f_bsize … 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