--
-- Show the High Water Mark for a given table, or all tables if ALL is specified for Table_Name.
--

SET LINESIZE 300
SET SERVEROUTPUT ON
SET VERIFY OFF

DECLARE
CURSOR cu_tables IS
SELECT a.owner,
a.table_name
FROM all_tables a
WHERE a.table_name = Decode(Upper('&&Table_Name'),'ALL',a.table_name,Upper('&&Table_Name'))
AND a.owner = Upper('&&Table_Owner')
AND a.partitioned='NO'
AND a.logging='YES'
order by table_name;

op1 NUMBER;
op2 NUMBER;
op3 NUMBER;
op4 NUMBER;
op5 NUMBER;
op6 NUMBER;
op7 NUMBER;
BEGIN

Dbms_Output.Disable;
Dbms_Output.Enable(1000000);
Dbms_Output.Put_Line('TABLE UNUSED BLOCKS TOTAL BLOCKS HIGH WATER MARK');
Dbms_Output.Put_Line('------------------------------ --------------- --------------- ---------------');
FOR cur_rec IN cu_tables LOOP
Dbms_Space.Unused_Space(cur_rec.owner,cur_rec.table_name,'TABLE',op1,op2,op3,op4,op5,op6,op7);
Dbms_Output.Put_Line(RPad(cur_rec.table_name,30,' ') ||
LPad(op3,15,' ') ||
LPad(op1,15,' ') ||
LPad(Trunc(op1-op3-1),15,' '));
END LOOP;

END;

The post Find High water mark of a table appeared first on DBACLASS.

  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
--------------------------------------------------------------------------------
      1993 SYS                            ALL_ROWS   3727742172 000000041AE77AD8
    817728       817753
  select sesion.sid,        sesion.username,        optimizer_mode,        hash_
value,        address,        cpu_time,        elapsed_time,        sql_text   f
rom v$sqlarea sqlarea, v$session sesion  where sesion.sql_hash_value = sqlarea.h
ash_value    and sesion.sql_address    = sqlarea.address    and sesion.username
is not null

The post How to find current running sqls in oracle appeared first on DBACLASS.

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 <= 5

The post Top 5 cached sql statements by elapsed time in oracle appeared first on DBACLASS.