Session Info .....................

col sid format 9999
col username format a10
col osuser format a10
col program format a25
col process format 9999999
col spid format 999999
col logon_time format a13
set lines 150
set heading off
set verify off
set feedback off
undefine sid_number
undefine spid_number
rem accept sid_number number prompt "pl_enter_sid:"
col sid NEW_VALUE sid_number noprint
col spid NEW_VALUE spid_number noprint

         select  s.sid   sid,
                p.spid  spid
--              ,decode(count(*), 1,'null','No Session Found with this info') " "
         FROM v$session s,
              v$process p
         WHERE s.sid LIKE NVL('&sid', '%')
         AND p.spid LIKE NVL ('&OS_ProcessID', '%')
         AND s.process LIKE NVL('&Client_Process', '%')
         AND s.paddr = p.addr
--       group by s.sid, p.spid;
PROMPT Session and Process Information
PROMPT -------------------------------
col event for a30
select '    SID                         : '||v.sid      || chr(10)||
       '    Serial Number               : '||v.serial#  || chr(10) ||
       '    Oracle User Name            : '||v.username         || chr(10) ||
       '    Client OS user name         : '||v.osuser   || chr(10) ||
       '    Client Process ID           : '||v.process  || chr(10) ||
       '    Client machine Name         : '||v.machine  || chr(10) ||
       '    Oracle PID                  : '||p.pid      || chr(10) ||
       '    OS Process ID(spid)         : '||p.spid     || chr(10) ||
       '    Session''s Status           : '||v.status   || chr(10) ||
       '    Logon Time                  : '||to_char(v.logon_time, 'MM/DD HH24:MIpm')   || chr(10) ||
       '    Program Name                : '||v.program  || chr(10)
from v$session v, v$process p
where v.paddr = p.addr
and v.serial# > 1
and p.background is null
and p.username is not null
and sid = &sid_number
order by logon_time, v.status, 1
/

PROMPT Sql Statement
PROMPT --------------
select sql_text
from v$sqltext , v$session
where v$sqltext.address = v$session.sql_address
and sid = &sid_number
order by piece
/
PROMPT
PROMPT Event Wait Information
PROMPT ----------------------
select '   SID '|| &sid_number ||' is waiting on event  : ' || x.event || chr(10) ||
       '   P1 Text                      : ' || x.p1text || chr(10) ||
       '   P1 Value                     : ' || x.p1 || chr(10) ||
       '   P2 Text                      : ' || x.p2text || chr(10) ||
       '   P2 Value                     : ' || x.p2 || chr(10) ||
       '   P3 Text                      : ' || x.p3text || chr(10) ||
       '   P3 Value                     : ' || x.p3
from v$session_wait x
where x.sid= &sid_number
/
PROMPT
PROMPT Session Statistics
PROMPT ------------------
select        '     '|| b.name  ||'             : '||decode(b.name, 'redo size', round(a.value/1024/1024,2)||' M', a.value)
from v$session s, v$sesstat a, v$statname b
where a.statistic# = b.statistic#
and name in ('redo size', 'parse count (total)', 'parse count (hard)', 'user commits')
and s.sid = &sid_number
and a.sid = &sid_number
--order by b.name
order by decode(b.name, 'redo size', 1, 2), b.name
/
COLUMN USERNAME FORMAT a10
COLUMN status FORMAT a8
column RBS_NAME format a10
PROMPT
PROMPT Transaction and Rollback Information
PROMPT ------------------------------------
select        '    Rollback Used                : '||t.used_ublk*8192/1024/1024 ||' M'          || chr(10) ||
              '    Rollback Records             : '||t.used_urec        || chr(10)||
              '    Rollback Segment Number      : '||t.xidusn           || chr(10)||
              '    Rollback Segment Name        : '||r.name             || chr(10)||
              '    Logical IOs                  : '||t.log_io           || chr(10)||
              '    Physical IOs                 : '||t.phy_io           || chr(10)||
              '    RBS Startng Extent ID        : '||t.start_uext       || chr(10)||
              '    Transaction Start Time       : '||t.start_time       || chr(10)||
              '    Transaction_Status           : '||t.status           
FROM v$transaction t, v$session s, v$rollname r
WHERE t.addr = s.taddr
and r.usn = t.xidusn
and s.sid = &sid_number
/
PROMPT
PROMPT Sort Information
PROMPT ----------------
column username format a20
column user format a20
column tablespace format a20
SELECT        '    Sort Space Used(8k block size is asssumed    : '||u.blocks/1024*8 ||' M'             || chr(10) ||
              '    Sorting Tablespace                           : '||u.tablespace       || chr(10)||
              '    Sort Tablespace Type                 : '||u.contents || chr(10)||
              '    Total Extents Used for Sorting               : '||u.extents 
FROM v$session s, v$sort_usage u
WHERE s.saddr = u.session_addr
AND s.sid = &sid_number
/

set heading on
set verify on
clear column

DB Upgrade Note ID's

Note.420146.1 - What is the Best Way to Upgrade?
Note 316889.1 - Complete checklist for manual upgrades to 10gR2
Note:316889.1 for 10g upgrades.
ID 429825.1----Complete checklist for manual upgrades to 11gR1
419550.1

If you are upgrading to any 10.2.0.x version on AIX5L, review the following note before upgrading:
Note 557242.1 "Upgrade Gives Ora-29558 Error Despite of JAccelerator Has Been Installed"

TO take Procedure Backup

REM procedure, package or package body.
REM This SCRIPT is provided as a guide and is not supported by
REM ORACLE Corporation.
set verify off
set feedback off
set lines 132
set pages 0
set heading off
set space 0
column text format a79
column line noprint
select DECODE(line,1,'create or replace ','')||text, line
from dba_source
where
owner = upper('TRUTK') and
type = upper('PROCEDURE')
and name = upper('SYS')
and line >= 1;
set verify on
set feedback on

To check the Partition Info

 SELECT A.TABLE_NAME,A.PARTITIONING_TYPE,B.PARTITION_NAME,B.TABLESPACE_NAME,D.FILE_NAME,D.BYTES/1024/1024/1024 "BYTES" FROM
 DBA_TAB_PARTITIONS B,DBA_PART_TABLES A,DBA_TABLESPACES C,DBA_DATA_FILES D WHERE A.TABLE_NAME=B.TABLE_NAME  AND
 D.TABLESPACE_NAME=C.TABLESPACE_NAME AND C.TABLESPACE_NAME=B.TABLESPACE_NAME  AND A.TABLE_NAME IN(B_FORECAST_MSPN_DEMAND_TREND)

To Take the User Syntax..............

Create user systax
==========================================
select 'create user ' || '&usr' ||
       ' identified by ' || '&password' ||
       ' default tablespace ' || du.default_tablespace ||
       ' temporary tablespace ' || du.temporary_tablespace ||
       ' account unlock profile ' || du.profile
from dba_users du
where du.username = upper('&&usr') ;

quota for a user
==========================
select 'quota ' || decode(dtq.max_bytes,-1,'UNLIMITED',dtq.max_bytes) ||
       ' on ' || dtq.tablespace_name
from   dba_ts_quotas dtq
where  dtq.username = upper('&&usr') ;

/* GRANT NON-DEFAULT ROLES */
select 'grant ' || drp.granted_role || ' to ' || drp.grantee ||
       decode(drp.admin_option,'YES', ' with admin option ;', 'NO', ' ;')
from  dba_role_privs drp
where drp.grantee = upper('&&usr')
and   drp.default_role = 'NO' ;
/* GRANT DEFAULT ROLES */
select 'alter user ' || drp.grantee || ' default role ' || granted_role || ' ;'
from dba_role_privs drp
where drp.grantee = upper('&&usr')
and   drp.default_role = 'YES' ;
/* GRANT SYSTEM PRIVS */
SELECT 'grant ' || dsp.privilege || ' to ' || dsp.grantee ||
       decode(dsp.admin_option,'YES',' with admin option ;','NO',' ;')
from dba_sys_privs dsp
where dsp.grantee = upper('&&usr') ;
/* GRANT OBJECT PRIVS (NEED TO ADD CONNECT STATEMENTS) */
SELECT 'grant ' || dtp.privilege || ' on ' ||
       dtp.owner || '.' || dtp.table_name ||
       ' to ' || dtp.grantee ||
       decode(dtp.grantable, 'YES',' with grant option ;','NO',' ;')
from dba_tab_privs dtp
where dtp.grantee = upper('&&usr') ;

TO Connect to DB with out tns entry .............

Using the following command one can pass hostname, port, servicename and SID and connect to a database without having an entry in tnsnames.ora
$ sqlplus username/password@hostname:port/SERVICENAME
OR
$ sqlplus username
Enter password: password@//hostname:port/SERVICENAME
OR
$ sqlplus /nolog
SQL> connect username/password@hostname:port/SERVICENAME

Get Temporary Tablespace Usage

SELECT A.tablespace_name tablespace,D.mb_total,SUM(A.used_blocks * D.block_size) / 1024 / 1024 mb_used,D.mb_total-SUM (A.used_blocks * D.block_size)/1024/ 1024 mb_free FROM v$sort_segment A,(SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
FROM v$tablespace B, v$tempfile C WHERE B.ts#= C.ts# GROUP BY B.name, C.block_size) D  WHERE A.tablespace_name = D.name GROUP by A.tablespace_name, D.mb_total;


TABLESPACE MB_TOTAL MB_USED MB_FREE
——————————- ———- ———- ———-
TEMP2 27000 80 26920

Get Current TEMP space usage by session id (9i/10g):
SELECT S.sid || ',' || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module,
S.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
COUNT(*) sort_ops
FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P
WHERE T.session_addr = S.saddr
AND S.paddr = P.addr
AND T.tablespace = TBS.tablespace_name
GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,
S.program, TBS.block_size, T.tablespace
ORDER BY sid_serial;

Get All Table Space syntax

SQL>set heading off;
SQL>set echo off;
SQL>Set pages 999;
SQL>set long 90000;
SQL>spool ddl_list.sql
SQL>select dbms_metadata.get_ddl(’TABLESPACE’,tb.tablespace_name) from dba_tablespaces tb;
SQL>spool off

To Check Parent & Child tables..........

select
  child.owner        || '.' ||
  child.table_name   "Child table",
  'is child of'      " ",
  parent.owner       || '.' ||
  parent.table_name  "Parent table"
from
  dba_constraints child join dba_constraints parent on
    child.r_constraint_name = parent.constraint_name and
    child.r_owner           = parent.owner
where
  parent.table_name like 'RI\_%' escape '\' --';

TO Check The Frag % On tables........

select a.owner,a.table_name,round(b.bytes/1024/1024) "currentsize in MB",round((a.num_rows*a.avg_row_len)/1024/1024) "actualsize in MB",round(((b.bytes/1024/1024-((a.num_rows*a.avg_row_len)/1024/1024))/(b.bytes/1024/1024))*(100-a.pct_free),2) "frag percent" from dba_segments b,dba_tables a where a.owner=b.owner
and b.bytes/1024/1024>100 and b.segment_name=a.table_name and a.owner='DSI_DPIPE';

TO enable the TRACE session level .................

column spid format a5
column sid format 9999
column ora_user format a10
column unix_user format a10
column osuser format a10
column server for a12
column machine for a15
col MACHINE for a10
column whenon format a18 heading "WHEN USER LOGGED ON"
column whendo format a18 heading "WHEN LAST ACTIVITY"
set lin 150
select p.pid, p.spid, s.sid, s.machine,s.serial#,lower(s.username) ora_user, p.username unix_user,Status,
s.osuser,
       to_char(s.logon_time,'mm/dd/yy hh24:mi:ss') whenon,
       to_char(sysdate - (s.last_call_et) / 86400,'mm/dd/yy hh24:mi:ss') whendo
from v$process p, v$session s
where s.paddr(+) = p.addr  and s.sid=1764
order by s.logon_time,s.status;


2. Start the debug session with the SPID of the process that needs traced.

SQL> oradebug setospid 26966

3. Select the appropriate trace level. There are four different options when specifying a tracing level:

• Level 1 – provides “base set” tracing information. Bind variables are displayed as variables (:b1).

• Level 4 – provides Level 1 data and the actual data values of bind variables.

• Level 8 – provides Level 1 data and information on wait events when the elapsed time is greater than the CPU time.

• Level 12 – combines levels 1, 4 and 8 tracing information.

A Level 12 trace contains base set, bind variable values and wait events.
The oradebug command below will enable the maximum tracing possible:

SQL> oradebug event 10046 trace name context forever, level 12

4. Turn tracing off.

SQL> oradebug event 10046 trace name context off

5. Obtain the trace file name. The oradebug facility provides an easy way to obtain the file name:

SQL> oradebug tracefile_name
c:\oracle9i\admin\ORCL92\udump\mooracle_ora_2280.trc


How to Trace(Reference)
----------------

Note:75713.1   Important Customer information about using Numeric Events
Note:21235.1   EVENT: 10262 "Do not check for memory leaks"
Note:21154.1   EVENT: 10046 "enable SQL statement tracing (including binds/waits)"
Note:160178.1  How to set EVENTS in the SPFILE
Note:1058210.6 HOW TO ENABLE SQL TRACE FOR ANOTHER SESSION USING ORADEBUG
Note:29062.1   SUPTOOL:  ORAMBX (VMS) - Quick Reference

TO Check the Oracle JOB status

set linesize 250
col log_user for a10
col job for 9999999 head 'Job'
col broken for a1 head 'B'
col failures for 99 head "fail"
col last_date for a18 head 'Last|Date'
col this_date for a18 head 'This|Date'
col next_date for a18 head 'Next|Date'
col interval for 9999.000 head 'Run|Interval'
col what for a60
select j.log_user,
j.job,
j.broken,
j.failures,
j.last_date||':'||j.last_sec last_date,
j.this_date||':'||j.this_sec this_date,
j.next_date||':'||j.next_sec next_date,
j.next_date - j.last_date interval,
j.what
from (select dj.LOG_USER, dj.JOB, dj.BROKEN, dj.FAILURES,
dj.LAST_DATE, dj.LAST_SEC, dj.THIS_DATE, dj.THIS_SEC,
dj.NEXT_DATE, dj.NEXT_SEC, dj.INTERVAL, dj.WHAT
from dba_jobs dj) j;

To check the user Privilages and Roles........

select  privilege||' '||table_name  "Privileges for N514012" from  dba_tab_privs where  grantee='&data'
union
select  privilege from  dba_sys_privs where grantee='&data'
union
select granted_role from dba_role_privs where  grantee='&data';


select 'grant select on  '||owner||'.'||object_name||' to xxint;' from dba_objects where owner='XXDW' and object_type='TABLE';