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;