SELECT   DECODE(ses.username, NULL, 'SYS', ses.username) username,
         TO_CHAR(ses.SID) SID, sn.NAME, sest.VALUE, ins.instance_name,
         DECODE(ses.username, NULL, 'SYS', ses.username) || TO_CHAR(ses.SID) usersid
    FROM gv$session ses, gv$statname sn, gv$sesstat sest, gv$instance ins
   WHERE ses.SID = sest.SID
     AND sn.statistic# = sest.statistic#
     AND sest.VALUE IS NOT NULL
     AND sest.VALUE != 0
     AND ses.INST_ID = sn.INST_ID
     AND ses.INST_ID = sest.INST_ID
     AND ses.INST_ID = ins.INST_ID
ORDER BY ses.username, ses.SID, upper(sn.NAME);

SELECT   (DECODE ((INITCAP (se.username)), NULL, 'SYS', se.username)
          ) username,
          se.SID usersession,
          SUM (DECODE (sn.NAME, 'consistent gets', ss.VALUE, 0)) consisgets,
          SUM (DECODE (sn.NAME, 'db block gets', ss.VALUE, 0)) dbblkgets,
          SUM (DECODE (sn.NAME, 'physical reads', ss.VALUE, 0)) physreads,
          ROUND(  (  SUM (DECODE (sn.NAME, 'consistent gets', ss.VALUE, 0))
             + SUM (DECODE (sn.NAME, 'db block gets', ss.VALUE, 0))
             - SUM (DECODE (sn.NAME, 'physical reads', ss.VALUE, 0))
            )
          / (  SUM (DECODE (sn.NAME, 'consistent gets', ss.VALUE, 0))
             + SUM (DECODE (sn.NAME, 'db block gets', ss.VALUE, 0))
             + 1
            ), 6)
          * 100 hitratio,
          MAX (UPPER(ins.instance_name)) instancename
    FROM v$sesstat ss, v$statname sn, v$session se, v$instance ins
   WHERE ss.SID = se.SID
     AND sn.statistic# = ss.statistic#
     AND ss.VALUE != 0
     AND sn.NAME IN ('db block gets', 'consistent gets', 'physical reads')
GROUP BY se.username, se.SID
ORDER BY 1,2

SELECT owner, index_name, table_owner, table_name, NULL partition_name,
       NULL subpartition_name, TO_CHAR(num_rows) num_rows, last_analyzed
FROM   DBA_INDEXES
WHERE  tablespace_name IS NOT NULL
AND    status = 'UNUSABLE'
UNION ALL
SELECT i.owner, i.index_name, i.table_owner, i.table_name,  p.partition_name,
       NULL subpartition_name, TO_CHAR(p.num_rows), p.last_analyzed
FROM   DBA_IND_PARTITIONS p,DBA_INDEXES i
WHERE  p.index_owner = i.owner
AND    p.index_name = i.index_name
AND    i.partitioned = 'YES'
AND    p.subpartition_count = 0
AND    P.STATUS = 'UNUSABLE'
UNION ALL
SELECT i.owner, i.index_name, i.table_owner, i.table_name,  p.partition_name,
       p.subpartition_name, TO_CHAR(p.num_rows), p.last_analyzed
FROM   DBA_IND_SUBPARTITIONS p, DBA_INDEXES i
WHERE  p.index_owner = i.owner
AND    p.index_name = i.index_name
AND    i.partitioned = 'YES'
AND    P.STATUS = 'UNUSABLE'
ORDER BY 1,2

SELECT l.namespace, l.reloads, UPPER(i.instance_name) instance_name
FROM   v$librarycache l, v$instance i
ORDER BY 1

SELECT   *
    FROM (SELECT c.tablespace_name,
                 ROUND (A.BYTES / 1048576, 2) megs_allocacted,
                 ROUND (b.BYTES / 1048576, 2) megs_free,
                 ROUND ((A.BYTES - b.BYTES) / 1048576, 2) megs_used,
                 ROUND (b.BYTES / A.BYTES * 100, 2) pct_free,
                 ROUND ((A.BYTES - b.BYTES) / A.BYTES, 2) * 100 pct_used
            FROM (SELECT   tablespace_name, SUM (A.BYTES) BYTES,
                           MIN (A.BYTES) minbytes, MAX (A.BYTES) maxbytes
                      FROM SYS.dba_data_files A
                  GROUP BY tablespace_name) A,
                 (SELECT   A.tablespace_name, NVL (SUM (b.BYTES), 0) BYTES
                      FROM SYS.dba_data_files A, SYS.dba_free_space b
                     WHERE A.tablespace_name = b.tablespace_name(+)
                           AND A.file_id = b.file_id(+)
                  GROUP BY A.tablespace_name) b,
                 SYS.dba_tablespaces c
           WHERE A.tablespace_name = b.tablespace_name(+)
             AND A.tablespace_name = c.tablespace_name
             )
   WHERE pct_used >= 90
ORDER BY tablespace_name

SELECT   a.NAME, a.VALUE, upper(i.instance_name) instance_name
FROM     v$parameter a, v$instance i
ORDER BY UPPER(NAME)

SELECT owner, segment_name, partition_name,
       segment_type, ROUND(bytes / 1048576, 2) size_in_mb
FROM   dba_segments
WHERE  owner NOT IN ('SYS', 'SYSTEM')
AND    tablespace_name = 'SYSTEM'
ORDER BY owner, segment_type, segment_name

SELECT   doc.owner, doc.NAME, doc.TYPE, doc.loads, doc.sharable_mem,
         upper(ins.instance_name) instance_name
FROM     v$db_object_cache doc, v$instance ins
WHERE    doc.loads > 2
AND      doc.TYPE IN ('PACKAGE', 'PACKAGE BODY', 'FUNCTION', 'PROCEDURE')
ORDER BY doc.loads DESC

SELECT o.owner, o.object_name, o.object_type, o.LAST_DDL_TIME,
       DECODE(NVL(e.SEQUENCE, 0), 0, 'No', 'Yes') Has_errors
FROM   DBA_OBJECTS o, DBA_errors e
WHERE  o.status <> 'VALID'
AND    o.object_type <> 'SYNONYM'
AND    o.owner = e.owner (+)
AND    o.object_name = e.NAME (+)
AND    o.OBJECT_TYPE = e.TYPE (+)
AND    e.SEQUENCE (+) = 1
ORDER BY 1, 3, 2