This can be used to find queries that use CPU heavily.  It starts by determining a baseline and then identifies those queries that use more CPU than the baseline * an arbitrary multiplier.  If you want to use this in a balanced RAC environment change the references to v$sqlarea to gv$sqlarea.  Functions are used to return topCPU and user to the mainline.

SET SERVEROUTPUT ON SIZE 1000000

DECLARE

topCPUkount                 NUMBER;
multiplier                  NUMBER     := .60;

CURSOR cpuCursor (cKo...
Continue reading ...

A handy script to check for locked objects in a database.

SET SERVEROUTPUT ON SIZE 1000000

DECLARE

kount           NUMBER   := 0;
objectName dba_objects.object_name%TYPE;
objectType dba_objects.object_type%TYPE;
schemaOwner dba_objects.owner%TYPE;

CURSOR lock_cursor IS
  SELECT *
    FROM gv$locked_object;

BEGIN
  FOR i IN lock_cursor LOOP
    kount := kount + 1;
    SELECT owner,object_name,object_type
      INTO schemaOwner,objectName,objectType
      FROM dba_objects
     WHERE object_id = i.object_id;
   ...
Continue reading ...

A useful script for checking the size of tablespaces

SET PAGESIZE 100

COL "Tablespace" FOR a22
COL "Used MB" FOR 99,999,999
COL "Free MB" FOR 99,999,999
COL "Total MB" FOR 99,999,999

SELECT df.tablespace_name "Tablespace",
       totalusedspace "Used MB",
       (df.totalspace - tu.totalusedspace) "Free MB",
        df.totalspace "Total MB",
        round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace)) "Pct. Free"
  FROM
    (SELECT tablespace_name,
            round(sum(bytes) / 1048576) To...
Continue reading ...