Category Archives: scripts

Find a session which are generating more archive logs

To find sessions generating lots of redo, you can use either of the following methods. Both methods examine the amount of undo generated. When a transaction generates undo, it will automatically generate redo as well. The methods are: 1) Query … Continue reading

Posted in Performance and Tuning, scripts | Leave a comment

archive log history per day of last 30 days

  SQL> SELECT A.*, Round(A.Count#*B.AVG#/1024/1024) Daily_Avg_Mb FROM ( SELECT To_Char(First_Time,’YYYY-MM-DD’) DAY, Count(1) Count#, Min(RECID) Min#, Max(RECID) Max# FROM v$log_history GROUP BY To_Char(First_Time,’YYYY-MM-DD’) ORDER BY 1 DESC ) A, ( SELECT Avg(BYTES) AVG#, Count(1) Count#, Max(BYTES) Max_Bytes, Min(BYTES) Min_Bytes FROM v$log … Continue reading

Posted in scripts | 2 Comments

How to get Oracle SQL query elapsed time

How to get a Oracle SQL query¬†elapsed¬†time SQL> sho user USER is “SCOTT” SQL> set timing on SQL> select * from emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ———- ———- ——— ———- —— 7369 SMITH CLERK 7902 17/12/80 … Continue reading

Posted in scripts | 6 Comments

CPU usage per session

SQL> conn /as sysdba Connected. SQL> select ss.username, se.SID, VALUE/100 cpu_usage_seconds 5 from v$session ss, v$sesstat se, v$statname sn where se.STATISTIC# = sn.STATISTIC# and NAME like ‘%CPU used by this session%’ and se.SID = ss.SID and ss.status=’ACTIVE’ ss.username is not … Continue reading

Posted in scripts | Leave a comment

Script to Show All Users’ System Privileges

CREATE OR REPLACE VIEW DBA_USER_PRIVS (USERNAME, ROLENAME, PRIVILEGE) AS SELECT DECODE(SA1.GRANTEE#, 1, ‘PUBLIC’, U1.NAME), SUBSTR(U2.NAME,1,20), SUBSTR(SPM.NAME,1,27) FROM SYS.SYSAUTH$ SA1, SYS.SYSAUTH$ SA2, SYS.USER$ U1, SYS.USER$ U2, SYS.SYSTEM_PRIVILEGE_MAP SPM WHERE SA1.GRANTEE# = U1.USER# AND SA1.PRIVILEGE# = U2.USER# AND U2.USER# = SA2.GRANTEE# AND … Continue reading

Posted in scripts | Leave a comment

Tablespace usage in Oracle

Tablespace usage -THIS SCRIPT WILL SHOW USAGE OF TABLESPACE clear columns column tablespace format a20 column total_mb format 999,999,999,999.99 column used_mb format 999,999,999,999.99 column free_mb format 999,999,999.99 column pct_used format 999.99 column graph format a25 heading “GRAPH (X=5%)” column status … Continue reading

Posted in scripts | Leave a comment

Largest objects in Oracle database

Top 10 largest objects in Oracle database col owner format a15 col segment_name format a30 col segment_type format a15 col mb format 999,999,999 select owner , segment_name , segment_type , mb from ( select owner , segment_name , segment_type , … Continue reading

Posted in scripts | Leave a comment