ERROR OGG-01028 Formatting error on: table name + Error converting timestamp with timezone from Oracle to ASCII format for column

2015-11-16 17:51:18  INFO    OGG-00993  Oracle GoldenGate Capture for Oracle, EXTRACT1.prm:  EXTRACT EXTRACT1 started.
2015-11-16 17:51:18  INFO    OGG-01055  Oracle GoldenGate Capture for Oracle, EXTRACT1.prm:  Recovery initialization completed for target file /usr/app/datab1/ggs/trails/a1000007, at RBA 1144.
2015-11-16 17:51:18  INFO    OGG-01478  Oracle GoldenGate Capture for Oracle, EXTRACT1.prm:  Output file /usr/app/datab1/ggs/trails/a1 is using format RELEASE 11.2.
2015-11-16 17:51:18  INFO    OGG-01026  Oracle GoldenGate Capture for Oracle, EXTRACT1.prm:  Rolling over remote file /usr/app/datab1/ggs/trails/a1000007.
2015-11-16 17:51:18  INFO    OGG-01053  Oracle GoldenGate Capture for Oracle, EXTRACT1.prm:  Recovery completed for target file /usr/app/datab1/ggs/trails/a1000008, at RBA 1144.
2015-11-16 17:51:18  INFO    OGG-01057  Oracle GoldenGate Capture for Oracle, EXTRACT1.prm:  Recovery completed for all targets.
2015-11-16 17:51:18  INFO    OGG-01517  Oracle GoldenGate Capture for Oracle, EXTRACT1.prm:  Position of first record processed Sequence 17507, RBA 21008, SCN 3332.2582756465, Nov 16, 2015 5:32:16 PM.
2015-11-16 17:51:19  INFO    OGG-01020  Oracle GoldenGate Delivery for Oracle, rccp004a.prm:  Processed extract process RESTART_ABEND record at seq 8, rba 1081 (aborted 0 records).
2015-11-16 17:51:21  ERROR   OGG-01028  Oracle GoldenGate Capture for Oracle, EXTRACT1.prm:  Formatting error on: table name CSI_CAET.ERROR_HIT, rowid AAAYnTAAGAAACM/AAA, XID 1.1.2642, position (Seqno 17507, RBA 21616). Error converting timestamp with timezone from Oracle to ASCII format for column LAST_UPDATE_DATE.
2015-11-16 17:51:21  ERROR   OGG-01668  Oracle GoldenGate Capture for Oracle, EXTRACT1.prm:  PROCESS ABENDING.


GGSCI (Sever2) 1> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
EXTRACT     ABENDED     EXTRACT1    00:14:10      00:58:53    


GGSCI (Sever2) 2> edit params EXTRACT1

Solution :
Oracle reported bug 14053498 for the above error {GoldenGate Extract Abends: OGG-01028 Formatting Error on: table name xxxx.xxxx rowid , XID nnnn.nn.nnnn, Position (Seqno n, RBA n) (Doc ID 1558791.1)}

Another Solution

Check the database characterset

SQL> select * from nls_database_parameters;

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_NCHAR_CHARACTERSET         AL16UTF16
NLS_LANGUAGE                   AMERICAN
NLS_TERRITORY                  AMERICA
NLS_CURRENCY                   $
NLS_ISO_CURRENCY               AMERICA
NLS_NUMERIC_CHARACTERS         .,
NLS_CHARACTERSET               WE8ISO8859P1
NLS_CALENDAR                   GREGORIAN
NLS_DATE_FORMAT                DD-MON-RR
NLS_DATE_LANGUAGE              AMERICAN
NLS_SORT                       BINARY


Add below two parameters to the extract prm file

SETENV (NLS_LANG="AMERICAN_AMERICA.WE8ISO8859P1") -- Please make sure you have the correct characterset from the above output
TRANLOGOPTIONS INCLUDEREGIONID

GGSCI (Sever2) 4> start EXTRACT1

Sending START request to MANAGER ...
EXTRACT EXTRACT1 starting


GGSCI (Sever2) 5> info EXTRACT1

EXTRACT    EXTRACT1  Last Started 2015-11-16 18:50   Status RUNNING
Checkpoint Lag       01:13:36 (updated 00:00:01 ago)
Log Read Checkpoint  Oracle Redo Logs
                     2015-11-16 17:37:08  Seqno 17507, RBA 176876032
                     SCN 3332.2582759675 (14313413789947)

OGG-00717 : Found unsupported in-memory undo record + Minimum supplemental logging must be enabled to prevent data loss

2015-11-09 16:23:54  INFO    OGG-00993  Oracle GoldenGate Capture for Oracle, EXTRACT3.prm:  EXTRACT EXTRACT3 started.
2015-11-09 16:23:54  INFO    OGG-01052  Oracle GoldenGate Capture for Oracle, EXTRACT3.prm:  No recovery is required for target file /usr/app/db1/ggs/trails/x1000000, at RBA 0 (file not opened).
2015-11-09 16:23:54  INFO    OGG-01478  Oracle GoldenGate Capture for Oracle, EXTRACT3.prm:  Output file /usr/app/db1/ggs/trails/x1 is using format RELEASE 11.2.
2015-11-09 16:23:54  ERROR   OGG-00717  Oracle GoldenGate Capture for Oracle, EXTRACT3.prm:  Found unsupported in-memory undo record in sequence 11566, at RBA 1473040, with SCN 3330.2812655651 (14305053751331) ... Minimum supplemental logging must be enabled to prevent data loss.
2015-11-09 16:23:54  ERROR   OGG-01668  Oracle GoldenGate Capture for Oracle, EXTRACT3.prm:  PROCESS ABENDING.


Possible Cause:
Supplemental logging is not enabled on the database level or the extract is positioned to a point where the supplemental logging has not been enabled i.e an attempt was made to start the extract when the supplemental logging was not enabled on the database.

Solution:

Check if Supplemental logging is enabled

SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;

SUPPLEME
--------
NO

Enable Supplemental Logging

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

Database altered.

Switch Logfile

SQL> ALTER SYSTEM SWITCH LOGFILE;

System altered.

And attempt to start the extract with begin now option

GGSCI (sev274) 2> alter extract EXTRACT3, begin now
EXTRACT altered.


GGSCI (sev274) 3> start EXTRACT3

Sending START request to MANAGER ...
EXTRACT EXTRACT3 starting


GGSCI (sev274) 4> info EXTRACT3

EXTRACT    EXTRACT3  Last Started 2015-11-09 16:29   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:05 ago)
Log Read Checkpoint  Oracle Redo Logs
                     2015-11-09 16:29:20  Seqno 0, RBA 0
                     SCN 0.0 (0)


GGSCI (sev274) 5> !
info EXTRACT3

EXTRACT    EXTRACT3  Last Started 2015-11-09 16:29   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:09 ago)
Log Read Checkpoint  Oracle Redo Logs
                     2015-11-09 16:29:20  Seqno 0, RBA 0
                     SCN 0.0 (0)


GGSCI (sev274) 6> !
info EXTRACT3

EXTRACT    EXTRACT3  Last Started 2015-11-09 16:29   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:03 ago)
Log Read Checkpoint  Oracle Redo Logs
                     2015-11-09 16:55:07  Seqno 11567, RBA 281600
                     SCN 3330.2812657068 (14305053752748)

OGG-01004 + Database error 26945 (OCI Error ORA-26945: unsupported hint RESTRICT_ALL_REF_CONS

While using delete cascade on the database, the replicat needs below parameter set in the prm file

DBOPTIONS DEFERREFCONST

After setting this, the replicat was getting abended with the below error

2015-10-30 09:21:34  WARNING OGG-00869  Oracle GoldenGate Delivery for Oracle, rapi8602.prm:  OCI Error ORA-26945: unsupported hint RESTRICT_ALL_REF_CONS (status = 26945). DELETE /*+ RESTRICT_ALL_REF_CONS */ FROM "user5"."transactions"  WHERE "ID" = :b0.
2015-10-30 09:21:34  WARNING OGG-01004  Oracle GoldenGate Delivery for Oracle, rapi8602.prm:  Aborted grouped transaction on 'user5.transactions', Database error 26945 (OCI Error ORA-26945: unsupported hint RESTRICT_ALL_REF_CONS (status = 26945). DELETE /*+ RESTRICT_ALL_REF_CONS */ FROM "user5"."transactions"  WHERE "ID" = :b0).
2015-10-30 09:21:34  WARNING OGG-01003  Oracle GoldenGate Delivery for Oracle, rapi8602.prm:  Repositioning to rba 1679 in seqno 8.
2015-10-30 09:21:34  WARNING OGG-01154  Oracle GoldenGate Delivery for Oracle, rapi8602.prm:  SQL error 26945 mapping user5.transactions to user5.transactions OCI Error ORA-26945: unsupported hint RESTRICT_ALL_REF_CONS (status = 26945). DELETE /*+ RESTRICT_ALL_REF_CONS */ FROM "user5"."transactions"  WHERE "ID" = :b0.

Solution :

exec dbms_goldengate_auth.grant_admin_privilege('<GGSUSER>');
ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION = TRUE SCOPE = BOTH;

Oracle : Recycle Bin ORA-02096

10g

On Database level

11:15:22 DB2 @ sev1 > show parameter recyclebin

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
recyclebin                           string      on

11:15:45 DB2 @ sev1 > ALTER SYSTEM SET recyclebin = OFF ;

System altered.

11:17:45 DB2 @ sev1 > ALTER SYSTEM SET recyclebin = ON ;

System altered.

11g

We need to specify DEFFERED to turn off recyclebin on 11g databases, else you will see the below error. The change will take place from the new sessions

11:15:26 DB2 @ sev1 > ALTER SYSTEM SET recyclebin = OFF ;        
ALTER SYSTEM SET recyclebin = OFF
                                 *
ERROR at line 1:
ORA-02096: specified initialization parameter is not modifiable with this option

Fix :

11:15:45 DB2 @ sev1 > ALTER SYSTEM SET recyclebin = OFF DEFERRED;

System altered.

11:15:58 DB2 @ sev1 > ALTER SYSTEM SET recyclebin = ON DEFERRED;

System altered.

Turning off recyclebin on session level

11:18:23 DB2 @ sev1 > ALTER SESSION SET recyclebin = OFF ;

11:19:17 DB2 @ sev1 > ALTER SESSION SET recyclebin = ON ;

ERROR OGG-00446 Oracle GoldenGate Delivery for Oracle, replicat3.prm: Unable to lock file "/usr/app/db1/ggs/11.2.1.0.28/dirchk/REPLICAT3.cpr" (error 11, Resource temporarily unavailable). Lock currently held by process id (PID) 68732

ERROR   OGG-00446  Oracle GoldenGate Delivery for Oracle, replicat3.prm:  Unable to lock file "/usr/app/db1/ggs/11.2.1.0.28/dirchk/REPLICAT3.cpr" (error 11, Resource temporarily unavailable).  Lock currently held by process id (PID) 68732

Replicat was getting abended with above error, checked to see what the process PID 68732 is, but nothing was showing up with

ps -ef | grep 68732

and no processes showed as running with the replicat REPLICAT3 name.

Solution :

Copied the original  REPLICAT3.cpr file as REPLICAT3.cpr.tmp , removed the original file REPLICAT3.cpr and renamed REPLICAT3.cpr.tmp as REPLICAT3.cpr.

This released the lock and replicat process started running

Steps :
cp REPLICAT3.cpr REPLICAT3.cpr.tmp
rm REPLICAT3.cpr
mv REPLICAT3.cpr.tmp REPLICAT3.cpr

ERROR OGG-01032 There is a problem in network communication, a remote file problem, encryption keys for target and source do not match (if using ENCRYPT) or an unknown error. Length is 3 - 000000: 46 45 00 |FE. |.

After the Server 2 crashed and brought up the pumps on Server 1 sending trails to Server2 was abending with below error

ERROR   OGG-01032  There is a problem in network communication, a remote file problem, encryption keys for target and source do not match (if using ENCRYPT) or an unknown error. Length is 3 - 000000: 46 45 00                                        |FE.             |.

Cause

Trail file system on target goes out of space causing the pump  to hang. The issue is ultimately due to an  interrupted rmt trail writing caused by connection issues because the target file system cannot be written to.

Note that this is a single instance of a broader issue. Anytime the collector server fails for any reason, it will look like a TCP or network issue to the pump. This can be space or security or path issues or anything else.

Solution

Fix any target space issue.

1) Go the source server and login into ggsci

- Execute the below command
alter <extract pump> etrollover

start <extract pump>

(ETROLLOVER will force the pump to write on a fresh trail)

3) Go the target server and check if the new trail files are being generated.

4) Verify if the first record in the new trail file is indeed the next record in the local extract trail. (It can be done by looking at the RBA value through the logdump utility)

5) Alter the replicat to the new trail file
alter <replicat> extseqno <new trail file> extrba 0

6) Start the Replicat process

7) Verify if all the processes are running fine. 

Oracle : Determine Backups/Restore Time

col OPNAME for a30

select OPNAME,SOFAR/TOTALWORK*100 PERCENT_SOFAR, trunc(TIME_REMAINING/60) TIME_REMAINING,

trunc(ELAPSED_SECONDS/60) TIME_CONSUMED

from v$session_longops where TOTALWORK>0 and OPNAME like '%RMAN%';

Output for above query

OPNAME                         PERCENT_SOFAR TIME_REMAINING TIME_CONSUMED

------------------------------ ------------- -------------- -------------

RMAN: incremental datafile bac           100              0             0

kup

RMAN: full datafile backup               100              0             0

RMAN: incremental datafile bac           100              0             0

kup

RMAN: full datafile backup               100              0             0

RMAN: full datafile backup               100              0             0

RMAN: full datafile backup               100              0             0

RMAN: archived log backup                100              0             0

RMAN: archived log backup                100              0             0

RMAN: archived log backup                100              0             0

RMAN: archived log backup                100              0             0

Understanding the Output

About v$session_longops : Please click here 

OPNAME : Shows the Brief description of the operation

PERCENT_SOFAR : Percentage completed sofar

TIME_REMAINING : Estimated remaining time to complete the job

TIME_CONSUMED : Elapsed Time for the job

You can alter the above query to find the time remaining for 'restore' work as well by substituting 'RMAN' with 'RESTORE'

Oracle : ORA-06570: shared pool object does not exist, cannot be pinned/purged

SQL> select ADDRESS, HASH_VALUE from GV$SQLAREA where SQL_ID like '8xzc%';

ADDRESS HASH_VALUE
---------------- ----------
00000067B31EB568  1105770

Running from first node

SQL> execute dbms_shared_pool.purge('00000067B31EB568,1105770','C');
BEGIN dbms_shared_pool.purge('00000067B31EB568,1105770','C'); END;

*
ERROR at line 1:
ORA-06570: shared pool object does not exist, cannot be pinned/purged
ORA-06512: at "SYS.DBMS_SHARED_POOL", line 48
ORA-06512: at "SYS.DBMS_SHARED_POOL", line 115
ORA-06512: at line 1

As the details were obtained from GV$SQLAREA and it is RAC database, the purge should be done on another node as it might not be on the library cache on the instance you are connected.

Now, running it from another node

SQL> execute dbms_shared_pool.purge('00000067B31EB568,1105770','C');

PL/SQL procedure successfully completed.

Oracle : Script which shows who owns which objects and where

select
  substrb(decode(grouping(owner),1,'{all}',owner),1,10) OWNER,
  substrb(decode(grouping(tablespace_name),1,'{all}',tablespace_name),1,13) TS_NAME,
  substrb(decode(floor(sum(bytes)/1073741824),0,
    decode(floor(sum(bytes)/1048576),0,
      to_char(sum(bytes)/1024,'9999')||'K',
      to_char(sum(bytes)/1048576,'9999')||'M'),
      to_char(sum(bytes)/1073741824,'99.9')||'G'),2,6) "SIZE",
  to_char(sum(decode(segment_type,'TABLE',1,0)),'999999') TAB,
  to_char(sum(decode(segment_type,'INDEX',1,0)),'999999') IDX,
  substrb(to_char(sum(decode(segment_type,'ROLLBACK',1,0)),'999'),2,3) ROL,
  substrb(to_char(sum(decode(segment_type,'TEMPORARY',1,0)),'999'),2,3) TMP,
  substrb(to_char(sum(decode(segment_type,'CACHE',1,0)),'999'),2,3) CAC,
  substrb(to_char(sum(decode(segment_type,'CLUSTER',1,0)),'999'),2,3) CLU,
  substrb(to_char(sum(decode(substrb(segment_type,1,3),'LOB',1,0)),'999'),2,3) LOB,
  to_char(count(*),'9999999') TOTAL
from dba_segments
group by rollup( owner, tablespace_name )
order by 1,2,3 desc,4 desc
/

Oracle : Script shows object locks currently held in the database

select /*+ rule */
substrb(vlo.oracle_username,1,10) db_user,
substrb(vlo.os_user_name,1,10) os_user,
substrb(to_char(vlo.session_id),1,4) sid,
decode(vs.lockwait,null,substrb(vs.status,1,3),'WAI') "STA",
substrb(dbo.owner,1,10) owner,
substrb(dbo.object_name,1,25) obj_name,
substrb(dbo.object_type,1,3) typ,
substrb(decode(locked_mode,0,'NONE',1,'NULL',2,'ROWS',3,'ROWX',
  4,'SHAR',5,'SRWX',6,'EXCL',to_char(vlo.locked_mode)),1,4) "MODE"
from v$locked_object vlo, v$session vs, dba_objects dbo
where vlo.session_id = vs.sid
and vs.type != 'BACKGROUND'
and vlo.object_id = dbo.object_id
order by decode(vs.lockwait,null,decode(vs.status,'ACTIVE',2,3),1),3,1
/

Oracle : Script shows all latches held in the database

select
substrb(vs.username,1,10) db_user,
substrb(vs.osuser,1,10) os_user,
substrb(vs.terminal,1,8) terminal,
decode(vs.lockwait,null,substrb(vs.status,1,3),'WAI') "STA",
substrb(vs.server,1,1)||substrb(to_char(vs.sid),1,4) sid,
substrb(to_char(vs.serial#),1,4) srl,
substrb(vp.spid,1,8) spid,
substrb(vl.name,1,21) latch
from v$session vs, v$process vp, v$latchholder vl
where vs.sid = vl.sid
and vs.type = 'USER'
and vs.paddr = vp.addr(+)
/

Script to look at session wait to see who is waiting on latches
=======================================================================

select /*+ RULE CURSOR_SHARING_EXACT */
  substrb(vs.username,1,12) db_user,
  decode(vs.lockwait,null,
    decode(vp.latchwait,null,
      decode(vp.latchspin, null,
        substrb(vs.status,1,3),
        'SPN'),
      'LAT'),
    'LCK') STA,
  substrb(to_char(vs.sid),1,4) sid,
  substrb(vp.spid,1,8) spid,
  vsw.p1raw LATCH_ADDR,
  vs.sql_address SQL_ADDR,
  vs.sql_hash_value SQL_HASH_VALUE,
  to_char(vsw.seconds_in_wait,'9999') TIME,
  substrb(vlc.name,1,30) LATCH_NAME,
  substrb(vlc.child#,1,5) CHILD,
  to_char(vlc.gets,'99999999') "GETS",
--  to_char(vlc.misses,'999999') "MISSES",
  to_char(vlc.sleeps,'999999') "SLEEPS"
from v$session_wait vsw, v$latch_children vlc, v$session vs, v$process vp
where vsw.p1raw = vlc.addr
and vsw.sid = vs.sid
and vs.paddr = vp.addr
and vsw.event like 'latch%'
union
select /*+ RULE */
  substrb(vs.username,1,12),
  decode(vs.lockwait,null,
    decode(vp.latchwait,null,
      decode(vp.latchspin, null,
        substrb(vs.status,1,3),
        'SPN'),
      'LAT'),
    'LCK') STA,
  substrb(to_char(vs.sid),1,4),
  substrb(vp.spid,1,8),
  vsw.p1raw,
  vs.sql_address,
  vs.sql_hash_value SQL_HASH_VALUE,
  to_char(vsw.seconds_in_wait,'9999'),
  substrb(vl.name,1,30),
  'N/A',
  to_char(vl.gets,'99999999'),
--  to_char(vl.misses,'999999'),
  to_char(vl.sleeps,'999999')
from v$session_wait vsw, v$latch vl, v$session vs, v$process vp
where vsw.p1raw = vl.addr
and vsw.sid = vs.sid
and vs.paddr = vp.addr
and vsw.event like 'latch%'
order by 5,6
/

Oracle : Script shows the last call for each session

select /*+ RULE CURSOR_SHARING_EXACT */
  substrb(to_char(vss.sid),1,4) sid,
  decode(vss.lockwait,null,substrb(vss.status,1,3),'WAI') "STA",
  lpad(substrb(decode(trunc(vss.last_call_et/86400),0,
  to_char(to_date(vss.last_call_et,'SSSSS'),'HH24:MI:SS'),
  to_char(trunc(vss.last_call_et/86400),'B999999')),1,10),8) "LC-D/HMS",
  vsa.address sql_addr,
  substrb(vsa.sql_text,1,45) sql_text
from v$session vss, v$sqlarea vsa
where vss.type != 'BACKGROUND'
and vss.sql_address = vsa.address(+)
--and to_char(vss.sid) like '%$1'
union all
select
  substrb(to_char(vss.sid),1,4) sid,
  decode(vss.lockwait,null,substrb(vss.status,1,3),'WAI') "STA",
  lpad(substrb(decode(trunc(vss.last_call_et/86400),0,
  to_char(to_date(vss.last_call_et,'SSSSS'),'HH24:MI:SS'),
  to_char(trunc(vss.last_call_et/86400),'B999999')),1,10),8) "LC-D/HMS",
  vsa.address sql_addr,
  substrb(vsa.sql_text,1,45) sql_text
from v$session vss, v$sqlarea vsa
where vss.prev_sql_addr != '00'
and vss.type != 'BACKGROUND'
and vss.prev_sql_addr = vsa.address(+)
--and to_char(vss.sid) like '%$1'
order by 2, 1
/

oracle : Script to show the database hot spots

select
substrb(decode(ascii(substrb(vdf.name,21,1)),NULL,vdf.name,
  substrb(vdf.name,1,6)||'..'||
  substrb(vdf.name,greatest(length(vdf.name)-12,0),13)),1,21) "DB_FILE",
to_char(xbh.dbablk,'999999') "SGABLK",
substrb(dbo.owner,1,10) "OWNER",
substrb(dbo.object_name,1,20) "OBJ_NAME",
substrb(dbo.object_type,1,3) "TYP",
to_char((vlc.gets/greatest(vlc.gets+vlc.misses,1))*100,'99.99')||'%' "HIT_RT"
--,to_char(xbh.tch,'99999') "TOUCH"
from sys.x$bh xbh, v$datafile vdf, dba_objects dbo, v$latch_children vlc
where xbh.file# = vdf.file#
and xbh.obj = dbo.object_id
and xbh.hladdr = vlc.addr
and vlc.name = 'cache buffers chains'
and (vlc.misses/(greatest(vlc.gets+vlc.misses,1))>0.005)
order by 6, 3, 4
/

Oracle : Script shows objects that were read by a full table scan

select /*+ RULE CURSOR_SHARING_EXACT */
  to_char(count(1),'999,999') "BLOCKS",
  substrb(ds.buffer_pool,1,8) "POOL",
  substrb(u.name,1,10) "OWNER",
  substrb(o.name,1,30) "OBJ_NAME",
  decode(o.type#,1,'IND',2,'TAB','OTH') "TYP",
  substr(ds.tablespace_name,1,15) "TS_NAME"
from obj$ o,x$bh x, user$ u, dba_objects dbo, dba_segments ds
where x.obj = o.obj#
--and o.type# = 2
and bitand(x.flag,524288) > 0
and o.owner# = u.user#
and u.name <> 'SYS'
and o.obj# = dbo.object_id
and dbo.owner = ds.owner
and dbo.object_name = ds.segment_name
and dbo.object_type = ds.segment_type
having count(*) > 5
group by u.name, o.name, o.type#, ds.tablespace_name, ds.buffer_pool
order by 1 desc
/

Oracle : Script to check whats executing in the database right now

select /*+ RULE CUSROR_SHARING_EXACT */
  substrb(vs.username,1,10) db_user,
  substrb(to_char(vs.sid),1,4) sid,
  decode(bitand(vt.flag,4194304),0,'LOCAL','REMOTE') "LOCAL",
  decode(bitand(vt.flag,268435456),0,'RDC','SRL') "ISO",
  substrb(to_char(vt.log_io+vt.phy_io,'999,999,999'),2,7) totl_io,
  substrb(to_char(vt.log_io/greatest((vt.log_io+vt.phy_io),1)*100,'999.99')  ||'%',2,7) hit_rt,
  to_char(nvl(vt.used_ublk,0),'999,999') undo,
  lpad(substrb(decode(trunc(sysdate-to_date(vt.start_time,'MM/DD/YY HH24:MI:SS')),0,
    to_char(to_date(trunc(86400*abs(sysdate-to_date(vt.start_time,'MM/DD/YY HH24:MI:SS'))),'SSSSS'),'HH24:MI:SS'),
    to_char(trunc(sysdate-to_date(vt.start_time,'MM/DD/YY HH24:MI:SS')),'B999999')),1,10),8) "ST-D/HMS",
  vs.sql_address sql_addr
from v$session vs, v$transaction vt
where vs.type != 'BACKGROUND'
and vs.taddr = vt.addr
order by 5 desc, 3 desc
/

Oracle : Waitevents

select
substrb(vse.event,1,40) EVENT,
to_char(average_wait,'999,999,999.99') AVG_WAIT_SECS,
to_char(time_waited,'999,999,999') TIME_WAITED
from v$system_event vse
where vse.event != 'Null event'
and vse.event not like '%timer%'
and vse.event not like 'SQL*Net%'
and 15 > (
  select count(*) from v$system_event vse2
  where vse2.time_waited > vse.time_waited
  and vse2.event != 'Null event'
  and vse2.event not like '%timer%'
  and vse2.event not like 'SQL*Net%' )
order by vse.time_waited desc
/

Oracle : Script shows the enqueue waits in the database

select /*+ RULE CURSOR_SHARING_EXACT */
  to_char(inst_id,'999') "INS",
  ksqsttyp "TYP",
  decode(
    ksqsttyp,
    'CI','Cross Instance - initiation of processes on other instances',
    'CF','Controlfile - contention for controlfile',
    'CU','Cursor Binding',
    'HW','High Watermark - manually add extents',
    'RO','Releasable Object - contention during delete/truncate',
    'ST','Space Management - use LMTs or incr. next extent',
    'TM','Trans Mgmt - check FK idx',
    'TX','Trans Enq - incr. init/max trans or more commits',
    null) "ACTION",
  to_char(sum(ksqstwat),'999,999') "WAITS"
--  , to_char(sum(ksqstget),'999,999,999') "GETS"
from X$KSQST
where ksqstwat > 0
group by inst_id, ksqsttyp
order by 4 desc
/

Oracle : Script to give a quick overveiw of the Instance

select /*+ RULE CURSOR_SHARING_EXACT */
   substrb(max(name),1,6) NAME,
   substrb(max(version),1,9) VERSION,
   substrb(to_char(max(startup_time),'DD-MON-YY'),1,9) STARTED,
   substrb(decode(max(log_mode),'NOARCHIVELOG','NOARCH','ARCH'),1,5) LOGMD,
   decode(max(archiver),'STOPPED','NO','YES') ARC,
   decode(max(logins),'ALLOWED','NO','YES') RM,
   substrb(max(parallel),1,3) OPS,
   to_char(max(E4031),'9999') "4031",
   substrb(decode(floor(max(pool_mb)/1073741824),0,
     decode(floor(max(pool_mb)/1048576),0,
       to_char(max(pool_mb)/1024,'9999')||'K',
       to_char(max(pool_mb)/1048576,'9999')||'M'),
     to_char(max(pool_mb)/1073741824,'99.9')||'G'),2,6) POOL,
   substrb(decode(floor(max(dbf_mb)/1073741824),0,
     decode(floor(max(dbf_mb)/1048576),0,
       to_char(max(dbf_mb)/1024,'9999')||'K',
       to_char(max(dbf_mb)/1048576,'9999')||'M'),
     to_char(max(dbf_mb)/1073741824,'99.9')||'G'),2,6) CACH,
   substrb(decode(floor(max(log_kb)/1073741824),0,
     decode(floor(max(log_kb)/1048576),0,
       to_char(max(log_kb)/1024,'9999')||'K',
       to_char(max(log_kb)/1048576,'9999')||'M'),
     to_char(max(log_kb)/1073741824,'99.9')||'G'),2,6) LOG,
   substrb(decode(floor(max(pga_sz)/1073741824),0,
     decode(floor(max(pga_sz)/1048576),0,
       to_char(max(pga_sz)/1024,'9999')||'K',
       to_char(max(pga_sz)/1048576,'9999')||'M'),
     to_char(max(pga_sz)/1073741824,'99.9')||'G'),2,6) PGA,
   substrb(decode(floor(max(jav_sz)/1073741824),0,
     decode(floor(max(jav_sz)/1048576),0,
       to_char(max(jav_sz)/1024,'9999')||'K',
       to_char(max(jav_sz)/1048576,'9999')||'M'),
     to_char(max(jav_sz)/1073741824,'99.9')||'G'),2,6) JAVA from (
   select
     vd.name name,
     vi.version version,
     vi.startup_time startup_time,
     vd.log_mode log_mode,
     vi.archiver archiver,
     vi.logins logins,
     vi.parallel parallel,
     xk.kghlunfu e4031,
     vp1.value POOL_MB,
     to_number(vp2.value*vp3.value) DBF_MB,
     vp4.value LOG_KB,
     vp5.value PGA_SZ,
     '0' JAV_SZ
   from v$database vd, v$instance vi, sys.x$kghlu xk, v$parameter vp1,
     v$parameter vp2, v$parameter vp3, v$parameter vp4, v$parameter vp5
   where vp1.name = 'shared_pool_size'
   and vp2.name = 'db_block_buffers'
   and vp2.value != '0'
   and vp3.name = 'db_block_size'
   and vp4.name = 'log_buffer'
   and vp5.name = 'sort_area_size'
   union all
   select
     vd.name,
     vi.version,
     vi.startup_time,
     vd.log_mode,
     vi.archiver archiver,
     vi.logins logins,
     vi.parallel parallel,
     xk.kghlunfu e4031,
     vp1.value POOL_MB,
     to_number(vp2.value) DBF_MB,
     vp4.value LOG_KB,
     vp5.value PGA_SZ,
     vp6.value JAV_SZ
   from v$database vd, v$instance vi, sys.x$kghlu xk, v$parameter vp1,
     v$parameter vp2, v$parameter vp4, v$parameter vp5, v$parameter vp6
   where vp1.name = 'shared_pool_size'
   and vp2.name = 'db_cache_size'
   and vp4.name = 'log_buffer'
   and vp5.name = 'pga_aggregate_target'
   and vp6.name = 'java_pool_size' )
 /

Oracle : Script which gives the CPU Performance Information

select 'recursive cpu:'||chr(9)||trunc(rcpu.value/tcpu.value*100,2)||'%' "METRIC"
from v$sysstat rcpu, v$statname vn1, v$sysstat tcpu, v$statname vn2
where vn1.name = 'recursive cpu usage' and vn1.statistic# = rcpu.statistic#
and vn2.name = 'CPU used by this session' and vn2.statistic# = tcpu.statistic#
union all
select 'parse cpu:'||chr(9)||trunc(pcpu.value/tcpu.value*100,2)||'%'
from v$sysstat pcpu, v$statname vn1, v$sysstat tcpu, v$statname vn2
where vn1.name = 'parse time cpu' and vn1.statistic# = pcpu.statistic#
and vn2.name = 'CPU used by this session' and vn2.statistic# = tcpu.statistic#
union all
select 'other cpu:'||chr(9)||trunc((tcpu.value-rcpu.value-pcpu.value)/tcpu.value*100,2)||'%'
from v$sysstat rcpu, v$statname vn1, v$sysstat pcpu, v$statname vn2, v$sysstat tcpu, v$statname vn3
where vn1.name = 'recursive cpu usage' and vn1.statistic# = rcpu.statistic#
and vn2.name = 'parse time cpu' and vn2.statistic# = pcpu.statistic#
and vn3.name = 'CPU used by this session' and vn3.statistic# = tcpu.statistic#
union all
select 'cpu/execute:'||chr(9)||trunc(tcpu.value/exct.value,2)||' hsecs'
from v$sysstat exct, v$statname vn1, v$sysstat tcpu, v$statname vn2
where vn1.name = 'execute count' and vn1.statistic# = exct.statistic#
and vn2.name = 'CPU used by this session' and vn2.statistic# = tcpu.statistic#
union all
select 'cpu/call:'||chr(9)||trunc(tcpu.value/ucals.value,2)||' hsecs'
from v$sysstat ucals, v$statname vn1, v$sysstat tcpu, v$statname vn2
where vn1.name = 'user calls' and vn1.statistic# = ucals.statistic#
and vn2.name = 'CPU used by this session' and vn2.statistic# = tcpu.statistic#
union all
select 'cpu/commit:'||chr(9)||trunc(tcpu.value/ucmts.value,2)||' hsecs'
from v$sysstat ucmts, v$statname vn1, v$sysstat tcpu, v$statname vn2
where vn1.name = 'user commits' and vn1.statistic# = ucmts.statistic#
and vn2.name = 'CPU used by this session' and vn2.statistic# = tcpu.statistic#
/

Oracle : Script which shows vital statistics for whole database

select /*+ RULE CURSOR_SHARING_EXACT */
   'DB BLOCK BUFFER (TOAD)' DB_RESOURCE,
   to_char(sum(decode(name,'db block gets',value,0))+sum(decode(name,'consistent gets',value,0)),'999,999,999,999') HIT,
   to_char(sum(decode(name,'physical reads',value,0)),'999,999,999,999') MISS,
   to_char((sum(decode(name,'db block gets',value,0))+sum(decode(name,'consistent gets',value,0)))/(sum(decode(name,'db block gets',value,0))+sum(decode(name,'consistent gets',value,0))+sum(decode(name,'physical reads',value,0)))*100,'999.99')||'%' RATIO
 from v$sysstat
 union all
 select
   'LIBRARY GETS',
   to_char(sum(gets),'999,999,999,999'),
   to_char(sum(gets)-sum(gethits),'999,999,999,999'),
   to_char(sum(gets)/(sum(gets)+(sum(gets)-sum(gethits)))*100,'999.99')||'%'
 from v$librarycache
 union all
 select
   'LIBRARY PINS',
   to_char(sum(pinhits),'999,999,999,999'),
   to_char(sum(pins)-sum(pinhits),'999,999,999,999'),
   to_char(sum(pinhits)/(sum(pinhits)+(sum(pins)-sum(pinhits)))*100,'999.99')||'%'
 from v$librarycache
 union all
 select
   'DICTIONARY',
   to_char(sum(gets)-sum(getmisses),'999,999,999,999'),
   to_char(sum(getmisses),'999,999,999,999'),to_char(sum(gets)/(sum(gets)+sum(getmisses))*100,'999.99')||'%'
 from v$rowcache
 union all
 select
   'INDEX UTILIZATION',
   to_char(sum(decode(name,'table fetch by rowid',value,0)),'999,999,999,999'),
   to_char(sum(decode(name,'table scan rows gotten',value,0)),'999,999,999,999'),
   to_char(sum(decode(name,'table fetch by rowid',value,0))/(sum(decode(name,'table fetch by rowid',value,0))+sum(decode(name,'table scan rows gotten',value,0)))*100,'999.99')||'%'
 from v$sysstat
 union all
   select
   'REDOLOG WRITES',
   to_char(sum(decode(name,'redo writes',value,0)),'999,999,999,999'),
   to_char(sum(decode(name,'redo log space requests',value,0)),'999,999,999,999'),
   to_char(sum(decode(name,'redo writes',value,0))
   /(greatest(1,sum(decode(name,'redo writes',value,0))+sum(decode(name,'redo log space requests',value,0))))*100,'999.99')||'%'
 from v$sysstat
 union all
 select
   'MEMORY SORTS',
   to_char(sum(decode(name,'sorts (memory)',value,0)),'999,999,999,999'),
   to_char(sum(decode(name,'sorts (disk)',value,0)),'999,999,999,999'),
   to_char(sum(decode(name,'sorts (memory)',value,0))/(sum(decode(name,'sorts (memory)',value,0))+sum(decode(name,'sorts (disk)',value,0)))*100,'999.99')||'%'
 from v$sysstat
 union all
 select
   'CURSOR REUSE',
   to_char(count(*),'999,999,999,999'),
   to_char(sum(decode(executions,0,0,1,0,1)),'999,999,999,999'),
   to_char((sum(decode(executions,0,0,1,0,1))/count(*))*100,'999.99')||'%'
 from v$sqlarea
 union all
 select
   'PARSE/EXEC',
   to_char(vss1.value,'999,999,999,999'),
   to_char(vss2.value,'999,999,999,999'),
   to_char(100*(1-vss1.value/vss2.value),'999.99')||'%'
 from v$sysstat vss1, v$sysstat vss2
 where vss1.name = 'parse count (total)'
 and vss2.name = 'execute count'
 union all
 select
   'PARSE HARD/TOT',
   to_char(vss1.value,'999,999,999,999'),
   to_char(vss2.value,'999,999,999,999'),
   to_char((vss1.value/vss2.value)*100,'999.99')||'%'
 from v$sysstat vss1, v$sysstat vss2
 where vss1.name = 'parse count (hard)'
 and vss2.name = 'parse count (total)'
 union all
 select
   'DB BLOCK MISS RATIO',
   to_char(sum(decode(name,'db block gets',value,0))+sum(decode(name,'consistent gets',value,0)),'999,999,999,999'),
   to_char(sum(decode(name,'physical reads',value,0)),'999,999,999,999'),
   to_char(
     (sum(decode(name,'physical reads',value,0))/
     (sum(decode(name,'db block gets',value,0))+sum(decode(name,'consistent gets',value,0))))*100
   ,'999.99')||'%' RATIO
 from v$sysstat
 union all
 select
   'DB BLOCK BUFFER',
   to_char(vss2.value,'999,999,999,999'),
   to_char(vss1.value,'999,999,999,999'),
   to_char(100*(1-vss1.value/vss2.value),'999.99')||'%'
 from v$sysstat vss1, v$sysstat vss2
 where vss1.name = 'physical reads'
 and vss2.name = 'consistent gets'
 order by 4 asc
 /

Oracle : Script which shows blocking sessions and waiters

select /*+ RULE CURSOR_SHARING_EXACT */
   substrb(vs1.username,1,10) "DB_USER_BL",
   decode(vs1.lockwait,null,substrb(vs1.status,1,3),'WAI') "STA",
   substrb(ltrim(to_char(vs1.inst_id,'9')||substrb(vs1.server,1,1)||substrb(to_char(vs1.sid),1,4)),1,6) "I-SID",
   substrb(to_char(vs1.serial#),1,4) "SRL",
   substrb(vp1.spid,1,8) spid,
   substrb(vs2.username,1,10) "DB_USER_WA",
   decode(vs2.lockwait,null,substrb(vs2.status,1,3),'WAI') "STA",
   substrb(ltrim(to_char(vs2.inst_id,'9')||substrb(vs2.server,1,1)||substrb(to_char(vs2.sid),1,4)),1,6) "I-SID",
   substrb(to_char(vs2.serial#),1,4) "SRL",
   substrb(vp2.spid,1,8) spid,
   lpad(substrb(decode(trunc(vl2.ctime/86400),0,
     to_char(to_date(vl2.ctime,'SSSSS'),'HH24:MI:SS'),
     to_char(trunc(vl2.ctime/86400),'B999999')),1,10),8) "WT-D/HMS"
 from gv$session vs1, gv$session vs2, gv$process vp1, gv$lock vl1, gv$lock vl2, gv$process vp2
 where vl1.block != 0
 and vl1.sid = vs1.sid
 and vl1.inst_id = vs1.inst_id
 and vl1.id1 = vl2.id1
 and vs1.paddr = vp1.addr(+)
 and vs1.inst_id = vp1.inst_id(+)
 and vl2.block = 0
 and vl2.sid = vs2.sid
 and vl2.inst_id = vs2.inst_id
 and vs2.paddr = vp2.addr(+)
 and vs2.inst_id = vp2.inst_id(+)
 /

Oracle : Script show which objects have auditing turned on

select
     substrb(owner,1,9) OWNER,
     substrb(object_name,1,14) OBJ_NAME,
     substrb(object_type,1,3) TYP,
     alt,aud,com,del,gra,ind,ins,loc,ren,sel,upd,ref,exe
     from dba_obj_audit_opts
     where
     greatest(alt,greatest(aud,greatest(com,greatest(del,greatest(gra,greatest(
     ind,greatest(ins,greatest(loc,greatest(ren,greatest(sel,greatest(
     upd,greatest(ref,exe)))))))))))) > '-/-'
     order by 1,3,2
     /

Oracle : Script to report the storage parameters of the tablespaces in a database

COL   stat   FORM A7         HEAD   'Status'      JUST c
COL   name   FORM A15        HEAD 'Tablespace'    JUST c
COL   init   FORM 999,990    HEAD 'Initial|(KB)'  JUST c
COL   next   FORM 999,990    HEAD 'Next|(KB)'     JUST c
COL   pcti   FORM 990        HEAD 'Pct|incr'      JUST c
COL   minx   FORM 990        HEAD 'Min|exts'      JUST c
COL   maxe   FORM 9999999990 HEAD 'Max|exts'      JUST c

BREAK ON stat SKIP 1

SELECT status    stat, tablespace_name     name,initial_extent/1024   init,
 next_extent/1024      next, pct_increase       pcti,min_extents  minx,
 max_extents           maxe
FROM dba_tablespaces WHERE status!='INVALID'
ORDER BY 1,2
/ 

Oracle : Script to Report On Space Usage in Tablespaces

set pagesize 300
 set linesize 120
 column sumb format 9,999,999,999,999
 column extents format 999999
 column bytes format 9,999,999,999,999
 column largest format 9,999,999,999,999
 column Tot_Size format 9,999,999,999,999
 column Tot_Free format 9,999,999,999,999
 column Pct_Free format 9,999,999,999,999
 column Chunks_Free format 9,999,999,999,999  
 column Max_Free format 9,999,999,999,999
 set echo off
 spool TFSTSNFO.SQL
       
 PROMPT  SPACE AVAILABLE IN TABLESPACES  
       
 select a.tablespace_name,sum(a.tots) Tot_Size,  
 sum(a.sumb) Tot_Free,
 sum(a.sumb)*100/sum(a.tots) Pct_Free,  
 sum(a.largest) Max_Free,sum(a.chunks) Chunks_Free  
 from  
 (
 select tablespace_name,0 tots,sum(bytes) sumb,  
 max(bytes) largest,count(*) chunks
 from dba_free_space a
 group by tablespace_name
 union
 select tablespace_name,sum(bytes) tots,0,0,0 from
 dba_data_files
 group by tablespace_name) a
 group by a.tablespace_name;
       
 column owner format a15
 column segment_name format a30
       
       
 PROMPT   SEGMENTS WITH MORE THAN 20 EXTENTS  
       
 select owner,segment_name,extents,bytes ,  
 max_extents,next_extent  
 from  dba_segments  
 where segment_type in ('TABLE','INDEX') and extents>20  
 order by owner,segment_name;
       
       
       
 PROMPT SEGMENTS WHERE THERE'S NOT ENOUGH ROOM FOR THE NEXT EXTENT  

 select  a.owner, a.segment_name, b.tablespace_name,
      decode(ext.extents,1,b.next_extent,
      a.bytes*(1+b.pct_increase/100)) nextext,  
      freesp.largest
 from    dba_extents a,
      dba_segments b,
      (select owner, segment_name, max(extent_id) extent_id,
      count(*) extents  
      from dba_extents  
      group by owner, segment_name
      ) ext,
      (select tablespace_name, max(bytes) largest
      from dba_free_space  
      group by tablespace_name
      ) freesp
 where   a.owner=b.owner and
      a.segment_name=b.segment_name and
      a.owner=ext.owner and  
      a.segment_name=ext.segment_name and
      a.extent_id=ext.extent_id and
      b.tablespace_name = freesp.tablespace_name and  
      decode(ext.extents,1,b.next_extent,
      a.bytes*(1+b.pct_increase/100)) > freesp.largest
 /
spool off  

Oracle : Script will report tablespace use by segment type and user

ttitle - center  'Space Usage per Segment Type per Tablespace per User'  skip 2

 col username        formata20 justify c heading 'Username'
 col tablespace_name format            a20 justify c heading 'Tablespace Name'
 col segment_type    format            a17 justify c heading 'Segment Type'
 col mbytes          format 999,999,990.99 justify c heading 'Mb Used'
 
 break -
   on username skip 1
 
 select
   owner                  username,
   segment_type           segment_type,
   sum(bytes)/1048576     mbytes,
   tablespace_name        tablespace_name
 from
   dba_segments
 group by
   owner,
   tablespace_name,
   segment_type
 order by
   owner,
   segment_type,
   tablespace_name
/ 

Oracle : Script will report tablespace free space and fragmentation

Oracle : Script to evaluate space used in a tablespace

set pause off
set feed off
set verify off
set pagesize 55
set linesize 255
clear screen
col tn   format a10 heading 'Name' trunc
col fn   format a50 heading 'Located in file_ID + File Name'
col bts  format a10 heading 'Size'
col used format a10 heading 'Used'

col ex   format        9999 heading 'NrExt'
col rs   format 999,999,999 heading 'RBSsize'
col init format     999,999 heading 'Init'
col next format     999,999 heading 'Next'
col mi   format         999 heading 'Min'
col ma   format  9999999999 heading 'Max'
col pct  format         990 heading '%Inc'
col st   format          a4 heading 'Stat'
col sn   format         a15 heading 'Segm Name'
col ts   format         a15 heading 'In Table Space'

create or replace view free_view
as
select file_id, sum(bytes) free_bytes
from sys.dba_free_space
group by file_id;

clear screen

prompt Tablespace Datafiles
select d.tablespace_name tn,
       f.file_id||' '||file_name fn,
       to_char(f.bytes/1024,'999,999')||'K' bts,
       to_char( (f.bytes - s.free_bytes)/1024,'999,999')||'K' used
from sys.dba_tablespaces d, sys.dba_data_files f, free_view s
where d.tablespace_name = f.tablespace_name
and   f.file_id = s.file_id(+)
order by d.tablespace_name;

prompt
prompt                  Tablespace definitions

define part1="rpad('|',29*(f.bytes-s.free_bytes)/f.bytes,'*')"
col gr format a30 heading 'Percent full'
select d.tablespace_name tn,
       d.initial_extent init,
       d.next_extent next,
       d.pct_increase pct,
       d.min_extents mi, max_extents ma,
       decode(d.status,'ONLINE','OnL','OFFLINE','OffL') st,
       rpad(&part1,29,' ')||'|' gr
from sys.dba_tablespaces d, sys.dba_data_files f, free_view s
where d.tablespace_name = f.tablespace_name
and   f.file_id = s.file_id
order by d.tablespace_name;
drop view free_view;
set feed on

Oracle : Script lists the details of database growth per month

select to_char(creation_time, 'RRRR Month') "Month",
    sum(bytes)/1024/1024 "Growth in Meg"
    from sys.v_$datafile
    where creation_time > SYSDATE-365
    group by to_char(creation_time, 'RRRR Month');

Oracle : Script to Determine Objects Per Tablespace

 

 accept tableSpaceName prompt 'Enter tablespace: '
 
 set linesize 132
 set pagesize 72
 col type format a17 heading "object type"
 col object format a111 heading "schema.Object"

 break on type skip 1 on type
 ttitle left 'Tablespace: ' &tableSpaceName - skip 1 ' '
 
 select segment_type type,
      rtrim(owner)||'.'||rtrim(segment_name) object
 from sys.dba_extents
 where file_id in (
      select file_id
      from sys.dba_data_files
      where tablespace_name = upper('&tableSpaceName'))
 group by owner, segment_name, segment_type
 order by segment_type;

Oracle : Space Management and Object Storage

This script shows the space usage for a database.  This script performs the following functions:
               
  o Show the tablespaces, size, and percent used.            
  o The total free space still available in each tablespace.
  o The free space blocks in each of the tablespaces.      
  o The total free space still available in each tablespace.
  o The usage by each user segment, the inital extent size,
      and the size of the next extents - ordered by segment name.
  o The usage by each user segment, the inital extent size,
      and the size of the next extents - ordered by tablespace name.
  o The usage by each user segment, the inital extent size,
      and the size of the next extents - ordered by segment size.                                        
  o Show the size of the entire database.                   

 
 
 - - - - - - - - - - - - - - - - Code begins here - - - - - - - - - - - - - - - -

  set linesize 132;
  set pagesize 1000;
  set long 50;
  set echo on;
 
  break on "TBL SPACE" on "TOTAL BYTES";
 
  /* *************************************************** */
  /* block-size                                          */
  /* *************************************************** */
  column db_block_size new_value BLOCK_SIZE
  select to_char(value, '9999') db_block_size
         from v$parameter
         where name = 'db_block_size';
 
 
  /* *************************************************** */
  /* date & user_id                                      */
  /* *************************************************** */
  column today new_value dba_date
  select to_char(sysdate, 'mm/dd/yy hh:miam') today
         from dual;
 
  break on instance
  column instance new_value instance_name
  select substr(name,1,4) instance
        from v$database;
 
  clear breaks
  set termout on
 
  set pagesize 60 linesize 132 verify off
  set space 2
  ttitle left 'Date: ' format a18 dba_date -
         center 'Space Report - ' format a4 instance_name -
         right 'Page: ' format 999 sql.pno skip 2
 
  set echo off;
 
  set echo on;
 
 
  /* *************************************************** */
  /* tablespace usage via the dba_data_files             */
  /* *************************************************** */
  set echo off;
 
  select  substr(D.tablespace_name, 1, 12) "TBL SPACE",
     substr(to_char(sum(D.bytes), '999,999,999,999'), 1, 16) "TOTAL BYTES",
     substr(to_char(sum(D.bytes)/1024/1024, '999,999.9'), 1, 10) "TOT MBYTES",
     substr(to_char(sum(D.bytes)/(Z.VALUE), '99,999,999'), 1, 11) "BLOCKS"
  from    sys.dba_data_files D,
          v$parameter        Z
  where Z.name = 'db_block_size'
  group by D.tablespace_name, Z.value;
               
  set echo on;  
 
 
  /* *************************************************** */
  /*  show tablespace usage                              */
  /* *************************************************** */
  set echo off;
 
  select  distinct substr(tablespace_name, 1, 12)      "TBL SPACE",
    substr(to_char(sum(blocks), '999,999,999'), 1, 12)              "TOT BLKS",
       substr(to_char(sum(bytes), '999,999,999,999'), 1, 16)           "TOT BYTES"
  from    sys.dba_free_space
  group by
   tablespace_name;
 
  set echo on;
 
 
  /* *************************************************** */
  /*  show tablespace usage                              */
  /* *************************************************** */
  set echo off;
 
  break on "TBL SPACE";
 
  select  substr(tablespace_name,1,12)                           "TBL SPACE",
        substr(to_char(file_id, '99'), 1, 4)                  "ID",
      substr(to_char(count(*), '9,999'), 1, 6)              "PCS",
     substr(to_char(max(blocks), '9,999,999'), 1, 10)      "MAX BLKS",
        substr(to_char(min(blocks), '9,999,999'), 1, 10)      "MIN BLKS",
        substr(to_char(avg(blocks), '9,999,999.9'), 1, 12)    "AVG BLKS",
        substr(to_char(sum(blocks), '9,999,999'), 1,10)       "SUM BLKS",
        substr(to_char(sum(bytes), '99,999,999,999'), 1, 15)  "SUM BYTES"
  from
    sys.dba_free_space
  group by
      tablespace_name,file_id
  order by 1, 4;
 
  set echo on;
 
 
  /* *************************************************** */
  /*  show tablespace usage                              */
  /* *************************************************** */
  set echo off;
 
  select
       distinct substr(tablespace_name,1,12)   "TBL SPACE",
      substr(to_char(sum(blocks), '999,999,999'), 1, 12)         "SUM BLKS",
   substr(to_char(sum(bytes)/(1024*1024), '999,999.999'), 1, 16) "SUM MBYTES"
  from
       sys.dba_free_space
  group by
       tablespace_name;
 
  set echo on;
  /* *************************************************** */
  /*  show tablespace segments - order by segment        */
  /* *************************************************** */
  set echo off;
 
  break on "SEGMENT" on "TYPE";
 
  select substr(segment_name, 1, 20)                          "SEGMENT",
         substr(segment_type,1,7)                             "TYPE",
         substr(tablespace_name,1,8)                          "TBL SPACE",
         substr(to_char(sum(bytes)/(1024*1024), '999,999.999'), 1, 11)
                                                              "SUM Mb",
        /* substr(to_char(sum(blocks), '999,999'), 1, 8)         "SUM BLKS", */
         substr(to_char(extents, '999'), 1, 4)                 "EXTENTS",
         substr(to_char((initial_extent/(1024*1024)), '99,999.999'), 1, 10)
                                                               "INI (Mb)",
         substr(to_char((next_extent/(1024*1024)), '999.999'), 1, 8) "NXT (Mb)"
  from    user_segments
  group by segment_name,
           segment_type,
           tablespace_name,
           bytes,
           blocks,
           extents,
           initial_extent,
           next_extent
  order by 1, 2;
 
  set echo on;
 
 
  /* *************************************************** */
  /*  show tablespace segments - order by tablespace     */
  /* *************************************************** */
  set echo off;
 
  break on "TBL SPACE" on "SEGMENT" on "TYPE";
 
  select substr(tablespace_name,1,12)                           "TBL SPACE",
         substr(segment_name, 1, 20)                            "SEGMENT",
      /* substr(segment_type,1,7)                               "TYPE",  */
         substr(to_char(sum(bytes)/(1024*1024), '999,999.999'), 1, 12)
                                                                "SUM Mb",      
      /* substr(to_char(sum(blocks), '9,999,999'), 1, 10)       "SUM BLKS", */
         substr(to_char(extents, '999'), 1, 4)                  "EXTS",
         substr(to_char(initial_extent/(1024*1024), '999.999'), 1, 8)    
                                                                "INI Mb",
         substr(to_char(next_extent/(1024*1024), '999.999'), 1, 8)
                                                                "NXT Mb"
  from    user_segments
  group by segment_name,
           segment_type,
           tablespace_name,
           bytes,
           blocks,
           extents,
           initial_extent,
           next_extent
  order by 1, 2;
       
  set echo on;
 
 
  /* *************************************************** */
  /*  show tablespace segments - order by bytes          */
  /* *************************************************** */
  set echo off;
 
  break on "SUM BYTES" on "SEGMENT" on "TYPE";
 
  select substr(to_char(sum(bytes)/(1024*1024), '999,999.999'), 1, 11)
                                                                "SUM Mb",
         substr(segment_name, 1, 20)                            "SEGMENT",
         substr(tablespace_name,1,12)                           "TBL SPACE",
         /*substr(to_char(sum(blocks), '9,999,999'), 1, 10)     "SUM BLKS",*/
         substr(to_char(extents, '999'), 1, 4)                  "EXTS",
         substr(to_char(initial_extent/(1024*1024), '9,999.999'), 1, 10)  
                                                                "INI Mb",
         substr(to_char(next_extent/(1024*1024), '999.999'), 1, 8)          
                                                                "NXT Mb"
  from    user_segments
  group by segment_name,
           segment_type,
           tablespace_name,
           bytes,
           blocks,
           extents,
           initial_extent,
           next_extent
  order by 1, 2;        
 
  set echo on;
 
 
  /* *************************************************** */
  /*  show size of database                              */
  /* *************************************************** */
  set echo off;
 
  select  substr(to_char(sum(bytes)/1024/1024, '999,999,999.99'), 1, 15)  "TOT MBYTES",
          substr(to_char(sum(bytes)/1024/1024/1024, '999,999.99'), 1, 11) "TOT GBYTES"
  from    sys.dba_data_files;
       
  undefine BLOCK_SIZE;
  set echo off;
 
  set long 80;

 - - - - - - - - - - - - - - - -  Code ends here  - - - - - - - - - - - - - - - -

Oracle : Script lists all jobs that are currently running in the local database

set pagesize 80 
title -  center  'List Running Jobs'  skip 2 

col sess format 99   heading 'Ses' 
col jid  format 999  heading 'Id' 
col subu format a10  heading 'Submitter'     trunc 
col secd format a10  heading 'Security'      trunc 
col proc format a20  heading 'Job'           word_wrapped 
col lsd  format a5   heading 'Last|Ok|Date'  
col lst  format a5   heading 'Last|Ok|Time' 
col nrd  format a5   heading 'This|Run|Date' 
col nrt  format a5   heading 'This|Run|Time' 
col fail format 99 heading 'Err' 

select 
  djr.sid                        sess, 
  djr.job                        jid, 
  dj.log_user                    subu, 
  dj.priv_user                   secd, 
  dj.what                        proc, 
  to_char(djr.last_date,'MM/DD') lsd, 
  substr(djr.last_sec,1,5)       lst, 
  to_char(djr.this_date,'MM/DD') nrd, 
  substr(djr.this_sec,1,5)       nrt, 
  djr.failures                   fail 
from 
  sys.dba_jobs dj, 
  sys.dba_jobs_running djr 
where 
  djr.job = dj.job 
/ 

Oracle : Script provides a report showing the type and count of objects by user

SET ECHO OFF 
set newpage 0 
ttitle center 'Object counts by User' - 
right 'Page:' format 999 sql.pno skip skip 
col ow format a18 heading 'Owner' 
col ta format 999,999 heading 'Tables' 
col ind format 999,999 heading 'Indexes' 
col sy format 999,999 heading 'Synonyms' 
col se format 999,999 heading 'Sequences' 
col ve format 999,999 heading 'Views' 
col clu format 999,999 heading 'Clusters' 
set verify off 
compute sum of ta on report 
compute sum of ow on report 
compute sum of sy on report 
compute sum of se on report 
compute sum of ind on report 
compute sum of ve on report 
compute sum of clu on report 
break on report 
spool tfsobnum.lst 
set feedback off 
start time 
set feedback 6 
set heading on 
select owner ow, 
       sum(decode(object_type,'TABLE',1,0)) ta , 
       sum(decode(object_type,'INDEX',1,0)) ind , 
       sum(decode(object_type,'SYNONYM',1,0)) sy , 
       sum(decode(object_type,'SEQUENCE',1,0)) se , 
       sum(decode(object_type,'VIEW',1,0)) ve , 
       sum(decode(object_type,'CLUSTER',1,0)) clu 
from dba_objects 
group by owner 
/ 
col dbl format 999,999 heading 'Database|Links' 
col pkg format 999,999 heading 'Packages' 
col pkb format 999,999 heading 'Package|Bodies' 
col pro format 999,999 heading 'Procedures' 
col ve format 999,999 heading 'Views' 
col clu format 999,999 heading 'Clusters' 
set verify off 
compute sum of dbl on report 
compute sum of ow on report 
compute sum of pkg on report 
compute sum of pkb on report 
compute sum of pro on report 
compute sum of ve on report 
compute sum of clu on report 
break on report 
select owner ow, 
       sum(decode(object_type,'DATABASE LINK',1,0)) dbl , 
       sum(decode(object_type,'PACKAGE',1,0)) pkg , 
       sum(decode(object_type,'PACKAGE BODY',1,0)) pkb , 
       sum(decode(object_type,'PROCEDURE',1,0)) pro  
from dba_objects 
group by owner 
/ 
Prompt 
prompt End of Report 
spool off 
btitle off 
ttitle off 
clear breaks 
clear columns 
clear computes 
set verify on 

Oracle : Table's constraints and their associated tables and constraints

def owner = &&1 
def tab = &&2 

ttitle -  center  'Table &owner..&tab Constraints'  skip 2 

col name format a22 heading 'Name' justify c 
col type format a5  heading 'Type' justify c 
col stat format a4  heading 'Stat' justify c 
col ref_tab format a20 heading 'Reference|Object' justify c 
col ref_con format a22 heading 'Reference|Constraint' justify c 

select 
  a.constraint_name name, 
  decode(a.constraint_type,'C','Check','R','FK','P','PK','U','Uniq', 
    'C','Check','*') type, 
  decode(a.status,'ENABLED','Y','DISABLED','N','*') stat, 
  b.owner||'.'||b.table_name ref_tab, 
  a.r_constraint_name ref_con 
from 
  dba_constraints a, 
  dba_constraints b 
where 
  a.owner = upper('&owner') and 
  a.table_name = upper('&tab') and 
  a.r_constraint_name = b.constraint_name (+) 
order by 
  1 
/ 

undef owner 
undef tab 

Oracle : Script reports the names of all ORACLE usernames that own database objects

col owner format a33 heading 'Owner Name' justify c

select
  distinct owner
from
  dba_objects
where
  owner not in ('SYS')
order by
  1 asc
/ 

Oracle : Script generates a report of the current values of the database initialization parameters and statistics related to the SGA

SET ECHO OFF 
spool tfssgapr.lst 
ttitle cen 'Current SGA Parameters'  - 
right 'Page:' format 999 sql.pno skip skip 
set feedback off 


btitle off 
column nline newline 
set pagesize 54 
set linesize 78 
set heading off 
set embedded off 
set verify off 
accept report_comment char prompt 'Enter a comment to identify system: ' 
select 'Date -  '||to_char(sysdate,'Day Ddth Month YYYY     HH24:MI:SS'), 
'At            -  '||'&&report_comment' nline, 
'Username      -  '||USER  nline 
from sys.dual 
/ 
prompt 
set embedded on 
set heading on 


set feedback 6 
column name format a36 heading 'Parameter Name' wrap 
column val format a36 heading 'Value' wrap  

select name 
,lpad(decode(type,1,'Boolean',2,'Character',3,'Integer',4,'File',null),9) 
 ||' '||value val 
from   v$parameter 
order by 1; 
set embedded off 
set newpage 2 pagesize 16 lines 78 
ttitle cen 'Current SGA Storage Summary' - 
right 'Page:' format 999 sql.pno skip skip 
set newpage 2 pagesize 16  
column name format a20 heading 'SGA Segment' 
column value format 9,999,999,990 heading 'Size|(Bytes)' 
column kbval format 9,999,990.9 heading 'Size|(Kb)' 
break on report 
compute sum of value kbval on report 
set newpage 0 

select name 
      ,value 
      ,round(value/1024,1) kbval 
from   v$sga 
/ 
ttitle cen 'Current SGA Library Summary'  
set newpage 3 
set pagesize 60 
column library format A15 heading 'Library|Name' 
column gets format 9,999,999 heading 'Gets' 
column gethitratio format 990.99 heading 'Get Hit|Ratio' 
column pins format 9,999,999 heading 'Pins' 
column pinhitratio format 990.99 heading 'Pin Hit|Ratio' 
column reloads format 99,999 heading 'Reloads' 
column invalidations format 99,999 heading 'Invalid' 
select initcap(namespace) library, 
       gets, 
       gethitratio, 
       pins, 
       pinhitratio, 
       reloads, invalidations 
  from v$librarycache 
/ 
prompt 
prompt The pin hit rate should be high (close to 1) 
prompt 
prompt End of Report 
spool off; 
set termout off; 
clear break column sql 
ttitle off 
btitle off 
set newpage 0 pagesize 56 lines 78 
set termout on feedback 6