database scripts

active.sql
===========
/* module.sql
show info about user sessions */
col sid format 99999
col username format a5 trunc
col osuser format a10 trunc
col logged_on format a13 head LOGON_TIME
col umachine format a10 trunc
col program format a20 trunc
col proginfo format a15 trunc
col LastCallET format 99,999
set verify off
accept trgtmod char default ALL prompt 'Restrict to which module : '
accept trgtmach char default ALL prompt 'Restrict to which originating server : '
accept trgtstatus char default Y prompt 'Limit to active sessions only : '
select s.sid,s.status,s.username,s.osuser,
replace(s.machine,'GEIPS-AMER\',NULL) umachine,
s.sql_hash_value,s.command,s.process, p.spid,
to_char(logon_time,'mm/dd hh:miAM') logged_on,
floor(last_call_et/60) "LastCallET",
s.module proginfo
from v$session s, v$process p
where s.username is not null
and s.type = 'USER'
and s.paddr = p.addr
and ((s.status = 'ACTIVE' and upper('&trgtstatus') = 'Y') or upper('&trgtstatus') != 'Y')
and (upper(s.module) like upper('%&trgtmod%') or upper('&trgtmod') = 'ALL')
and (upper(s.machine) like upper('%&trgtmach%') or upper('&trgtmach') = 'ALL')
/




archive logs per hour per day basis.sql
==========================================
/* archlogs.sql
find the number of archivelogs each hour of the day
*/
col MidN format 999
col 1AM format 999
col 2AM format 999
col 3AM format 999
col 4AM format 999
col 5AM format 999
col 6AM format 999
col 7AM format 999
col 8AM format 999
col 9AM format 999
col 10AM format 999
col 11AM format 999
col Noon format 999
col 1PM format 999
col 2PM format 999
col 3PM format 999
col 4PM format 999
col 5PM format 999
col 6PM format 999
col 7PM format 999
col 8PM format 999
col 9PM format 999
col 10PM format 999
col 11PM format 999
select to_char(first_time,'mm/dd/yy') logdate,
sum(decode(to_char(first_time,'hh24'),'00',1,0)) "MidN",
sum(decode(to_char(first_time,'hh24'),'01',1,0)) "1AM",
sum(decode(to_char(first_time,'hh24'),'02',1,0)) "2AM",
sum(decode(to_char(first_time,'hh24'),'03',1,0)) "3AM",
sum(decode(to_char(first_time,'hh24'),'04',1,0)) "4AM",
sum(decode(to_char(first_time,'hh24'),'05',1,0)) "5AM",
sum(decode(to_char(first_time,'hh24'),'06',1,0)) "6AM",
sum(decode(to_char(first_time,'hh24'),'07',1,0)) "7AM",
sum(decode(to_char(first_time,'hh24'),'08',1,0)) "8AM",
sum(decode(to_char(first_time,'hh24'),'09',1,0)) "9AM",
sum(decode(to_char(first_time,'hh24'),'10',1,0)) "10AM",
sum(decode(to_char(first_time,'hh24'),'11',1,0)) "11AM",
sum(decode(to_char(first_time,'hh24'),'12',1,0)) "Noon",
sum(decode(to_char(first_time,'hh24'),'13',1,0)) "1PM",
sum(decode(to_char(first_time,'hh24'),'14',1,0)) "2PM",
sum(decode(to_char(first_time,'hh24'),'15',1,0)) "3PM",
sum(decode(to_char(first_time,'hh24'),'16',1,0)) "4PM",
sum(decode(to_char(first_time,'hh24'),'17',1,0)) "5PM",
sum(decode(to_char(first_time,'hh24'),'18',1,0)) "6PM",
sum(decode(to_char(first_time,'hh24'),'19',1,0)) "7PM",
sum(decode(to_char(first_time,'hh24'),'20',1,0)) "8PM",
sum(decode(to_char(first_time,'hh24'),'21',1,0)) "9PM",
sum(decode(to_char(first_time,'hh24'),'22',1,0)) "10PM",
sum(decode(to_char(first_time,'hh24'),'23',1,0)) "11PM"
from v$log_history
group by to_char(first_time,'mm/dd/yy')
order by 1
/



ckobjstats.sql
==================
/* ckobjectstat.sql
check stats info on objects of interet
SDR-Oracle 7/5/02
*/
set verify off
col objtype format a8
col stats_status format a15
col owner format a15
col pctanal format 999 head PctAnalyzed
col objorder noprint
col table_name format a30
accept objname char default NONE prompt 'What is the table name : '
accept objowner char default ALL prompt 'Who is the table owner : '
select 'Table' objtype, owner, table_name , table_name objorder,
decode(last_analyzed,null,'No Stats',to_char(last_analyzed,'mm/dd/yy hh24:mi')) stats_status,
nvl(sample_size,0)/decode(nvl(num_rows,1),0,1,nvl(num_rows,1)) * 100 pctanal
from dba_tables
where table_name like upper('&objname%') and upper('&objname') != 'NONE'
and (owner = upper('&objowner') or upper('&objowner') = 'ALL')
union
select ' Index' objtype,owner, index_name, table_name objorder,
decode(last_analyzed,null,'No Stats',to_char(last_analyzed,'mm/dd/yy hh24:mi')) stats_status,
nvl(sample_size,0)/decode(nvl(num_rows,1),0,1,nvl(num_rows,1)) * 100 pctanal
from dba_indexes
where (owner = upper('&objowner') or upper('&objowner') = 'ALL')
and (table_name like upper('&objname%') and upper('&objname') != 'NONE')
union
select ' Hist' objtype, owner, column_name, table_name objorder,
'Buckets' status_status,
count(*) pctanal
from dba_tab_histograms
where (table_name like upper('&objname%') and upper('&objname') != 'NONE')
and (owner = upper('&objowner') or upper('&objowner') = 'ALL')
and endpoint_number > 1
group by owner, column_name, table_name
union
select ' PartInd' objtype, p.index_owner, i.index_name, i.table_name objorder,
decode(p.last_analyzed,null,'No Stats',to_char(p.last_analyzed,'mm/dd/yy hh24:mi')),
nvl(p.sample_size,0)/decode(nvl(p.num_rows,1),0,1,nvl(p.num_rows,1)) * 100 pctanal
from dba_ind_partitions p, dba_indexes i
where p.index_name = i.index_name
and p.index_owner = i.owner
and (i.table_name like upper('&objname%') and upper('&objname') != 'NONE')
and p.partition_position = 1
order by 4, 1 desc,3
/


cktempts.sql
=============
select tablespace_name,contents,extent_management,initial_extent,next_extent from dba_tablespaces where tablespace_name like 'TEMP%'
/
col file_name format a45 trunc
col mbavail format 99999.90
col mbused format 99999.90
col db_block_size format a15 trunc
select a.file_name,
a.bytes/1024/1024 mbavail,
((nvl(b.blocks_used,1)*c.db_block_size) + c.db_block_size)/1024/1024 mbused,
lpad(c.db_block_size,15)
from dba_temp_files a, v$temp_space_header b,
(select value db_block_size
from v$parameter
where name='db_block_size') c
where a.tablespace_name = 'TEMP'
and a.file_id = b.file_id(+)
order by a.file_name;



ckurbs.sql
============

/* ckuserrbs.sql
check rollback segments for users and active transactions
*/
set pause off
set pause 'Mash Enter...'
set feedback off
set verify off
col name format a12 heading RollbackSeg
col username format a12 heading DBUser
col sid format 99999 heading DBSID
col serial# format 99999 heading SRL#
col osuser format a8 heading OSUser
col program format a30 heading Program trunc
col spid format a6 heading BOSPID
col bpgm format a20 heading "BackgroundPgm" trunc
col module format a15 trunc
col status format a1 trunc
col machine format a15 trunc
col lastcallet format a10 trunc
col tablespace_name format a15 trunc
accept trgtrbs char default ALL prompt 'Limit to which rollback segment : '
accept trgttbs char default ALL prompt 'Limit to which Tablespace : '
accept trgtmod char default ALL prompt 'Limit to which module : '
accept trgtstatus char default N prompt 'Limit to active sessions only : '
prompt Checking rollback segment info...
select /*+ rule */
e.tablespace_name, c.name,
b.username ,
b.sid , b.status , b.osuser, b.module, b.machine,
floor(b.last_call_et/3600)||':'||
floor(mod(b.last_call_et,3600)/60)||':'||
mod(mod(b.last_call_et,3600),60) "LastCallET",
d.spid, b.command
from v$lock a, v$session b, v$rollname c, v$process d, dba_rollback_segs e
where a.type='TX'
and a.sid=b.sid
and d.addr = b.paddr
and c.usn=trunc(a.id1/1048596)*16+trunc((mod(a.id1,1048596))/65536)
and e.segment_name = c.name
and (c.name = upper('&trgtrbs') or upper('&trgtrbs') = 'ALL')
and (b.module = upper('&trgtmod') or upper('&trgtmod') = 'ALL')
and (b.status = 'ACTIVE' and upper('&trgtstatus') = 'Y' or upper('&trgtstatus') = 'N')
and (e.tablespace_name = upper('&trgttbs') or upper('&trgttbs') = 'ALL')
order by 1,2,5 desc;


Count the number of rows for ALL tables in current schema.txt
================================================================
rem -----------------------------------------------------------------------
rem Filename: countall.sql
rem Purpose: Count the number of rows for ALL tables in current schema
rem using PL/SQL
rem Date: 15-Apr-2000
rem Author: Eberhardt, Roberto (Bolton) (reberhar@husky.ca)
rem -----------------------------------------------------------------------

set serveroutput on size 1000000

DECLARE
t_c1_tname user_tables.table_name%TYPE;
t_command varchar2(200);
t_cid integer;
t_total_records number(10);
stat integer;
row_count integer;
t_limit integer := 0; -- Only show tables with more rows
cursor c1 is select table_name from user_tables order by table_name;
BEGIN
t_limit := 0;
open c1;
loop
fetch c1 into t_c1_tname;
exit when c1%NOTFOUND;
t_command := 'SELECT COUNT(0) FROM '||t_c1_tname;
t_cid := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(t_cid,t_command,dbms_sql.native);
DBMS_SQL.DEFINE_COLUMN(t_cid,1,t_total_records);
stat := DBMS_SQL.EXECUTE(t_cid);
row_count := DBMS_SQL.FETCH_ROWS(t_cid);
DBMS_SQL.COLUMN_VALUE(t_cid,1,t_total_records);
if t_total_records > t_limit then
DBMS_OUTPUT.PUT_LINE(rpad(t_c1_tname,55,' ')||
to_char(t_total_records,'99999999')||' record(s)');

end if;
DBMS_SQL.CLOSE_CURSOR(t_cid);
end loop;
close c1;
END;
/


create user like existing one.txt
====================================
rem -----------------------------------------------------------------------
rem Filename: cr8like.sql
rem Purpose: Script to create a new user (with privs) like an existing
rem database user. User data will not be copied.
rem Date: 02-Nov-1998
rem Author: Frank Naude, Oracle FAQ
rem Updated: Konstantin Krivosheyev - 7 Dec 2002
rem Updated: Frank Naude - 18 Dec 2003
rem -----------------------------------------------------------------------

set pages 0 feed off veri off lines 500

accept oldname prompt "Enter user to model new user to: "
accept newname prompt "Enter new user name: "
-- accept psw prompt "Enter new user's password: "

-- Create user...
select 'create user &&newname identified by values '''||password||''''||
-- select 'create user &&newname identified by &psw'||
' default tablespace '||default_tablespace||
' temporary tablespace '||temporary_tablespace||' profile '||
profile||';'
from sys.dba_users
where username = upper('&&oldname');

-- Grant Roles...
select 'grant '||granted_role||' to &&newname'||
decode(ADMIN_OPTION, 'YES', ' WITH ADMIN OPTION')||';'
from sys.dba_role_privs
where grantee = upper('&&oldname');

-- Grant System Privs...
select 'grant '||privilege||' to &&newname'||
decode(ADMIN_OPTION, 'YES', ' WITH ADMIN OPTION')||';'
from sys.dba_sys_privs
where grantee = upper('&&oldname');

-- Grant Table Privs...
select 'grant '||privilege||' on '||owner||'.'||table_name||' to &&newname;'
from sys.dba_tab_privs
where grantee = upper('&&oldname');

-- Grant Column Privs...
select 'grant '||privilege||' on '||owner||'.'||table_name||
'('||column_name||') to &&newname;'
from sys.dba_col_privs
where grantee = upper('&&oldname');

-- Set Default Role...
set serveroutput on
declare
defroles varchar2(4000);
begin
for c1 in (select * from sys.dba_role_privs
where grantee = upper('&&oldname')
and default_role = 'YES'
) loop
if length(defroles) > 0 then
defroles := defroles||','||c1.granted_role;
else
defroles := defroles||c1.granted_role;
end if;
end loop;
dbms_output.put_line('alter user &&newname default role '||defroles||';');
end;
/


Display database uptime in days and hours.TXT
==============================================
select SYSDATE-logon_time "Days", (SYSDATE-logon_time)*24 "Hours"
from sys.v_$session
where sid=1 /* this is PMON */
/


dynamically zip a trace file.txt
================================

column trc_file new_value trc_file noprint
column zip_file new_value zip_file noprint

select p.value || '/ora_' || u.spid || '.trc' trc_file,
p.value || '/ora_' || u.spid || '.trc.gz' zip_file
from sys.v_$session s,
sys.v_$process u,
sys.v_$parameter p
where s.audsid = userenv('SESSIONID')
and u.addr = s.paddr
and p.name = 'user_dump_dest'
/

set define :
host mknod :trc_file p && nohup gzip < :trc_file > :zip_file &
set define &

alter session set max_dump_file_size = unlimited
/




Dynamically zip a process's trace file .TXT
===============================================
column trc_file new_value trc_file noprint
column zip_file new_value zip_file noprint

select p.value || '/ora_' || u.spid || '.trc' trc_file,
p.value || '/ora_' || u.spid || '.trc.gz' zip_file
from sys.v_$session s,
sys.v_$process u,
sys.v_$parameter p
where s.audsid = userenv('SESSIONID')
and u.addr = s.paddr
and p.name = 'user_dump_dest'
/

set define :
host mknod :trc_file p && nohup gzip < :trc_file > :zip_file &
set define &

alter session set max_dump_file_size = unlimited
/


files on mount point.sql
===========================
col file_name format a55
accept trgtmp char default u00 prompt 'What is the mount point : '
select t.tablespace_name,f.file_name
from dba_tablespaces t, dba_data_files f
where f.tablespace_name = t.tablespace_name
and f.file_name like '/&trgtmp/%'
order by 1,2
/



find last ET from sid.sql
===========================
/* sidtime.sql
check sid for last call et
*/
col osuser format a10 trunc
col LastCallET format a11
col sid format 9999
col username format a10 trunc
col uprogram format a25 trunc
set linesize 132
set verify off
accept trgtsid number default 0 prompt 'What is the SID : '
select to_char(s.logon_time, 'mm/dd hh:mi:ssAM') loggedon,
s.sid, s.status,
floor(last_call_et/3600)||':'||
floor(mod(last_call_et,3600)/60)||':'||
mod(mod(last_call_et,3600),60) "LastCallET",
s.username, s.osuser,
p.spid, s.module || ' - ' || s.program uprogram, s.sql_hash_value
from v$session s, v$process p
where p.addr = s.paddr
and s.sid = &trgtsid
and &trgtsid > 0;





find oracle sid for given os process.sql
=========================================
/* osproc.sql
find the Oracle session info for a given OS process id
*/
col machine format a15 trunc
col program format a25 trunc
col LastCallET format a11
col status format a1 trunc
col module format a12 trunc
set verify off
accept trgtproc char default 0 prompt 'What is the OS process id : '
select s.sid,s.status,
floor(last_call_et/3600)||':'||
floor(mod(last_call_et,3600)/60)||':'||
mod(mod(last_call_et,3600),60) "LastCallET",
s.machine,s.module,s.process,s.sql_hash_value
from v$session s, v$process p
where s.paddr = p.addr
and p.spid = '&trgtproc';


find_hogger.txt
=================
#!/bin/ksh
#
# Find Highest CPU used Oracle processes and get the Username
# and SID from oracle
# Only 3 character SIDNAME is displayed - Adjust the script according to your need.
#
# Biju Thomas - 4/22/1999
#
date
echo " Top 20 CPU Utilized Session from `hostname`"
echo " ============================================"
echo "O/S Oracle Session Session Serial UNIX Login Ora CPU Time"
echo "ID User ID Status ID No ID MMDD:HHMISS SID Used"
echo "-------- ----------- -------- ------- ------- ------- ----------- --- --------"
ps -ef|grep LOCAL|cut -c1-15,42-79|sort -rn +2 | head -20 | while read LINE
do
SIDNAME=`echo $LINE | awk '{ print $4 }' | cut -c7-14`
CPUTIME=`echo $LINE | awk '{ print $3 }'`
UNIXPID=`echo $LINE | awk '{ print $2 }'`
#echo $SIDNAME $CPUTIME $UNIXPID
export ORACLE_SID=$SIDNAME
SIDNAME=`echo $ORACLE_SID | cut -c4-6`
export ORACLE_HOME=`/dba_script/bin/find_ohome.sh ${ORACLE_SID}`
export SHLIB_PATH=$ORACLE_HOME/lib:/usr/lib
export TMPDIR=/tmp
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export SQLPLUS="$ORACLE_HOME/bin/sqlplus -s / "
$SQLPLUS >> $wlogfile < set pages 0 lines 80 trims on echo off verify off pau off
column pu format a8 heading 'O/S|ID' justify left
column su format a11 heading 'Oracle|User ID' justify left
column stat format a8 heading 'Session|Status' justify left
column ssid format 999999 heading 'Session|ID' justify right
column sser format 999999 heading 'Serial|No' justify right
column spid format 999999 heading 'UNIX|ID' justify right
column ltime format a11 heading 'Login|Time' justify right
select p.username pu,
s.username su,
s.status stat,
s.sid ssid,
s.serial# sser,
lpad(p.spid,7) spid,
to_char(s.logon_time, 'MMDD:HH24MISS') ltime,
'$SIDNAME $CPUTIME'
from v\$process p,
v\$session s
where p.addr=s.paddr
and p.spid=$UNIXPID
union all
select a.username, 'Kill Me', 'NoOracle', a.pid, a.serial#,
lpad(a.spid,7) spid, 'KILL UNIXID', '$SIDNAME $CPUTIME'
from v\$process a
where a.spid = $UNIXPID
and not exists (select 1 from v\$session s
where a.addr=s.paddr);
EOF
done
echo "-------- ----------- -------- ------- ------- ------- ----------- --- --------"
date
#
# End of Script


get sql fm sid.sql
=====================
col sid for 999999
set long 100000000
select sid, sql_text from v$session a, v$sqltext b
where a.sql_address = b.address
and a.sid=&SID_NUMBERR
/


getplsqldef.sql
===============
set lines 200
set pages 0
accept objowner char default APPS prompt 'Who is the object owner : '
accept objname char prompt 'What is the object name : '
accept objtype char prompt 'What is the object type : '
accept spoolfile char default plsql.prev prompt 'What is the spool file name : '
set verify off
set feedback off
spool &spoolfile
select text from dba_source
where owner = upper('&objowner')
and name = upper('&objname')
and type = upper('&objtype')
order by line;
spool off


httpget.txt
=============
rem -----------------------------------------------------------------------
rem Filename: httpget.sql
rem Purpose: Access Internet Web pages from SQL or PL/SQL
rem Notes: From Oracle 8.0 one can retrieve web pages directly
rem from SQL or PL/SQL. Note you need to run utlhttp.sql as
rem SYS before this procedure will work.
rem Date: 27-Mar-2000
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

set pages 50000

select utl_http.request('http://www.orafaq.net/') from dual;




inactive user sessions.sql
============================
/* inactive.sql
shows the inactive user sessions
where lastcallet > 90 minutes
and session is a user session
SDR-Oracle 6/21/02 - sourced from my top10inactive.sql
SDR-Oracle 7/9/02 - added app process to ouput
*/
set serveroutput on size 100000
set verify off
set feedback off
declare
v_sidctr number := 0;
v_spidctr number := 0;

v_inact_threshold number := 90;
v_uctr number:= 0;
v_trgtsid number := 0;
v_shortname varchar(30);
v_longname varchar(45);
v_prevspid varchar(9) := '0';
v_usertype varchar(8);
v_umachine varchar(10);
v_lastcallet number;
v_loggedon varchar(16);

-- cursor for all inactive users over the threshold
cursor aiu is
select 'TOAD' usertype,
s.sid,
floor(last_call_et/60) lastcallet,
p.spid,
s.module uprogram,
substr(replace(s.machine,'GEIPS-AMER\',null),1,10) umachine,
s.osuser,
s.username,
to_char(s.logon_time, 'mm/dd hh:mi:ssAM') loggedon,
s.process appproc
from v$session s, v$process p
where p.addr = s.paddr
and s.type = 'USER'
and module is not null
and s.status = 'INACTIVE'
and floor(last_call_et/60) > v_inact_threshold
and (machine not like 'gps%' and upper(module) like 'T%O%A%D%')
union
select 'FORMS' usertype,
s.sid,
floor(last_call_et/60) lastcallet,
p.spid,
nvl(s.module,'UNKnown') uprogram,
s.machine umachine,
s.osuser,
s.username,
to_char(s.logon_time, 'mm/dd hh:mi:ssAM') loggedon,
s.process appproc
from v$session s, v$process p
where p.addr = s.paddr
and s.type = 'USER'
and s.status = 'INACTIVE'
and floor(last_call_et/60) > v_inact_threshold
and machine in ('gpsd011','gpsd012')
and nvl(module,'UNKnown') not like '%JAVA%'
union
select 'SQLPLUS' usertype,
s.sid,
floor(last_call_et/60) lastcallet,
p.spid,
s.module uprogram,
substr(replace(s.machine,'GEIPS-AMER\',null),1,10) umachine,
s.osuser,
s.username,
to_char(s.logon_time, 'mm/dd hh:mi:ssAM') loggedon,
s.process appproc
from v$session s, v$process p
where p.addr = s.paddr
and s.type = 'USER'
and module is not null
and s.status = 'INACTIVE'
and floor(last_call_et/60) > v_inact_threshold
and upper(module) like 'SQL%'
and ((s.machine = 'gpsd002a' and s.username != 'APPS') or (s.machine like 'GE%'))
order by 4,3 desc;

begin
-- do it
<>
for eu in aiu
loop
v_usertype := eu.usertype;
v_umachine := eu.umachine;
v_lastcallet := eu.lastcallet;
v_loggedon := eu.loggedon;

if v_prevspid != eu.spid then
dbms_output.put_line('OSProc:' || eu.spid || chr(9) || v_usertype || ' user from ' || v_umachine ||
' (' || eu.appproc || ') logged on at ' || v_loggedon);
v_prevspid := eu.spid;
v_spidctr := v_spidctr + 1;

begin
-- determine the apps forms user
if eu.usertype = 'FORMS' then
v_trgtsid := eu.sid;
select count(*),
u.user_name, u.description
into v_uctr,
v_shortname, v_longname
from applsys.fnd_logins l,
applsys.fnd_user u,
v$process p,
v$session s
where s.sid = v_trgtsid
and s.paddr = p.addr
and p.pid = l.pid
and l.end_time is null
and l.spid = s.process
and l.start_time is not null
and l.user_id = u.user_id
and l.start_time = (select max(l2.start_time)
from applsys.fnd_logins l2
where l2.pid = l.pid)
group by u.user_name, u.description;

else v_shortname := eu.username;

end if; -- end of if a forms user

exception
when others then
v_shortname := 'UNKnown';
v_longname := null;
end;

end if; -- end of if a new spid

-- provide the individual SID info
dbms_output.put_line(chr(9) || 'SID:' || eu.sid || ', User ' || v_shortname || ' - ' || eu.uprogram ||
' ' || v_lastcallet || ' minutes inactive');
v_sidctr := v_sidctr + 1;

end loop each_sid;

dbms_output.put_line(v_spidctr || ' total OSProcs to kill');
dbms_output.put_line(v_sidctr || ' total SIDs affected');
end;
/



initora.sql
=============
/* init73params.sql
get all the parameters for v7.3
*/
set feedback off
set pause off
set pagesize 66
set linesize 132
col ksppinm format a30 heading Parameter trunc
col ksppdesc format a60 heading Description trunc
col ksppstvl format a30 heading SessionValue trunc
spool initoraparams.lst
select a.ksppinm,
a.ksppdesc,
b.ksppstvl
from sys.x$ksppi a, sys.x$ksppcv b, sys.x$ksppsv c
where a.indx = b.indx
and a.indx = c.indx
order by 1;
spool off





killprocesses.sh
==================
select 'kill -9 ' || p.spid from v$session s, v$process p where s.paddr=p.addr and s.status='SNIPED'


List control file structures with usage limits.TXT
===================================================
rem -----------------------------------------------------------------------
rem Filename: ctlimits.sql
rem Purpose: List control file structures with usage limits
rem Date: 21-Sep-2000
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

set pages 50000
col PCT_USED format 990.09

-- Controlfile creation parameters:
-- Type DATAFILE is for MAXDATAFILES
-- Type REDO LOG is for MAXLOGFILES
-- Type LOG HISTORY is for MAXLOGHISTORY
-- Type REDO THREAD is for MAXINSTANCES
-- No entry for MAXLOGMEMBERS (?)

select type, records_used, records_total,
records_used/records_total*100 "PCT_USED"
from sys.v_$controlfile_record_section
/


Log all database errors to a table.TXT
========================================
rem -----------------------------------------------------------------------
rem Filename: db-error.sql
rem Purpose: Log all database errors to a table
rem Oracle8i or above/ DBA or CREATE ANY TRIGGER privs/ and
rem GRANT SELECT ON SYS.V_$SESSION required
rem Date: 21-Mar-2000
rem Author: Nico Booyse (booysen@saps.org.za)
rem -----------------------------------------------------------------------

drop trigger log_errors_trig;
drop table log_errors_tab;

create table log_errors_tab (
error varchar2(30),
timestamp date,
username varchar2(30),
osuser varchar2(30),
machine varchar2(64),
process varchar2(8),
program varchar2(48));

create or replace trigger log_errors_trig
after servererror on database
declare
var_user varchar2(30);
var_osuser varchar2(30);
var_machine varchar2(64);
var_process varchar2(8);
var_program varchar2(48);
begin
select username, osuser, machine, process, program
into var_user, var_osuser, var_machine, var_process, var_program
from sys.v_$session
where audsid = userenv('sessionid');

insert into log_errors_tab
values(dbms_standard.server_error(1),sysdate,var_user,
var_osuser,var_machine,var_process,var_program);
end;
/



new sessions which have recently started.sql
============================================
/* newses.sql
show the new user sessions based on logon time < 30 secs ago
*/
col LastCallET format a10
col sid format 9999
col sessprog format a25 trunc head PROGRAM
col username format a10 trunc
col osuser format a10 trunc
col lcet_hrs noprint
col lcet_min noprint
col lcet_sec noprint
set linesize 132
set verify off
col machine format a15 trunc
select
to_number(floor(last_call_et/3600)) lcet_hrs,
to_number(floor(mod(last_call_et,3600)/60)) lcet_min,
to_number(mod(mod(last_call_et,3600),60)) lcet_sec,
to_char(s.logon_time, 'mm/dd hh:mi:ssAM') loggedon,
s.sid, s.status,
floor(last_call_et/3600)||':'||
floor(mod(last_call_et,3600)/60)||':'||
mod(mod(last_call_et,3600),60) "LastCallET",
s.machine, s.osuser,
p.spid, nvl(s.module,p.program) sessprog,
s.sql_hash_value
from v$session s, v$process p
where p.addr = s.paddr
and type = 'USER'
and logon_time > (sysdate - 1/2880)
and nvl(s.username,'AQADMIN') != 'AQADMIN'
order by 1,2,3;


obects which are kept.sql
===========================
col type format a15 trunc
select kept,type,count(*),sum(loads),sum(sharable_mem),sum(executions)
from v$db_object_cache
where type in ('SEQUENCE','PACKAGE','PACKAGE BODY','PROCEDURE','FUNCTION','TRIGGER')
group by kept,type
/



objects which are not kept.sql
=============================
col owner format a15
col name format a30
col exec2load format 99,999,999
set lines 132
set pause off
set feedback off
select owner, name, type, loads, executions,
executions/loads exec2load,
sharable_mem
from v$db_object_cache
where type in ('SEQUENCE','PROCEDURE','PACKAGE','PACKAGE BODY','FUNCTION','TRIGGER')
and kept = 'NO'
and loads > 1
order by loads, owner, type;





objs_in_cache.sql
===================
/* objs_in_cache.sql
see the objects in the buffer cache
optionally limit by owner, object name
*/
set verify off
set lines 132
col filename format a35 trunc
col ownername format a10 head owner
col objname format a30 head object_name
col blksincache format 999,999 head footprint
accept trgtown char default ALL prompt 'Limit to owner : '
accept trgtobj char default ALL prompt 'Limit to object name : '
select u.name ownername,
o.name objname,
count(bc.block#) blksincache,
bc.status,
f.name filename
from sys.obj$ o, v$bh bc, v$datafile f, sys.user$ u
where o.obj# = bc.objd
and f.file# = bc.file#
and u.user# = o.owner#
and (u.name = upper('&trgtown') or upper('&trgtown') = 'ALL')
and (o.name = upper('&trgtobj') or upper('&trgtobj') = 'ALL')
group by u.name,o.name,bc.status,f.name
order by 3;


partition table info.sql
=========================
col column_name format a30 trunc
col owner format a15 trunc
col column_position format 999 head Order
set verify off
accept tabname char default NONE prompt 'What is the partitioned table name : '
prompt Getting partitioning info for &tabname....
select t.owner,t.table_name,t.partitioning_type,t.SUBPARTITIONING_TYPE ,
t.PARTITION_COUNT, k.column_name,k.column_position
from dba_part_tables t, dba_part_key_columns k
where t.table_name = upper('&tabname')
and upper('&tabname') != 'NONE'
and k.name = t.table_name
order by 1,2,7;

prompt Getting partioning info on indexes for &tabname....
select i.owner,i.index_name,i.partitioning_type,i.locality,i.SUBPARTITIONING_TYPE ,
i.PARTITION_COUNT, k.column_name,k.column_position
from dba_part_indexes i, dba_part_key_columns k
where i.table_name = upper('&tabname')
and upper('&tabname') != 'NONE'
and k.name = i.index_name
order by 1,2,7;



redo.sql
==========
col member format a40 trunc
col logsize format 9999 head MBSize
select l.group#,l.status,l.bytes/1024/1024 logsize,f.member
from v$log l, v$logfile f
where f.group# = l.group#
order by 1,3
/



redolog status.sql
=====================
col member format a40 trunc
select to_char(l.first_time,'mm/dd hh24:mi'),l.group#, l.sequence#, l.bytes,l.status,l.archived,f.member
from v$log l, v$logfile f
where l.group# = f.group#
order by sequence#
/



redostats.sql
================
/* redostats.sql
see the critical stats for redo and archive logs
SDR-Oracle
Note: this script is the v8-compatible version
*/
set pages 0
set lines 132
set pause off
set feedback off
set verify off
col dsnbr noprint

/* is the archiver falling behind, count should be 1 or 0 */
select decode(count(*),0,'Archiver is current',
1,'Archiver is archiving ' || count(*) || ' log...',
'Archiver has fallen behind by ' || count(*) || ' logs!')
from v$log
where status != 'CURRENT'
and archived = 'NO';

/* what is the current log COL::::<# members> */
select 'Current Online Log:' || chr(9) ||
substr(f.member,1,(instr(f.member,'/',1,2) -1))
|| '/.../' ||
substr(f.member,(instr(f.member,'/',-1,1)+1))
|| chr(9) || 'Sequence #' ||
l.sequence#
from v$logfile f, v$log l
where l.group# = f.group#
and l.status = 'CURRENT'
and l.archived = 'NO'
and rownum = 1;

/* what are the previous online logs */
select lc_1.sequence# dsnbr,
'Previous Online Logs:' || chr(9) ||
substr(f.member,1,(instr(f.member,'/',1,2) -1))
|| '/.../' ||
substr(f.member,(instr(f.member,'/',-1,1)+1))
|| chr(9) || 'Sequence #' ||
lc_1.sequence#
from v$log lc, v$log lc_1, v$logfile f
where lc.sequence# = (select max(l.sequence#) from v$log l)
and lc_1.sequence# = lc.sequence# -1
and f.group# = lc_1.group#
union
select lc_2.sequence# dsnbr,
' ' || chr(9) ||
substr(f.member,1,(instr(f.member,'/',1,2) -1))
|| '/.../' ||
substr(f.member,(instr(f.member,'/',-1,1)+1))
|| chr(9) || 'Sequence #' ||
lc_2.sequence#
from v$log lc, v$log lc_2, v$logfile f
where lc.sequence# = (select max(l.sequence#) from v$log l)
and lc_2.sequence# = lc.sequence# -2
and f.group# = lc_2.group#
union
select lc_3.sequence# dsnbr,
' ' || chr(9) ||
substr(f.member,1,(instr(f.member,'/',1,2) -1))
|| '/.../' ||
substr(f.member,(instr(f.member,'/',-1,1)+1))
|| chr(9) || 'Sequence #' ||
lc_3.sequence#
from v$log lc, v$log lc_3, v$logfile f
where lc.sequence# = (select max(l.sequence#) from v$log l)
and lc_3.sequence# = lc.sequence# -3
and f.group# = lc_3.group#
order by 1 desc;

/* what is the latest archived log LAL:: */
select 'Latest Archived Log:' || chr(9) ||
substr(h.name,(instr(h.name,'/',-1,1)+1))
|| chr(9) || 'Sequence #' ||
max(h.sequence#)
from v$archived_log h
where h.sequence# = (select max(l.sequence#)
from v$log l
where l.archived = 'YES'
and l.status = 'INACTIVE')
group by 'Latest Archived Log:' || chr(9) ||
substr(h.name,(instr(h.name,'/',-1,1)+1));

/* how many minutes since the last log switch MSL: */
select 'Elapsed Minutes Since Last Log Switch:' || chr(9) ||
trunc((sysdate - first_time) * 24 * 60)
from v$log
where status = 'CURRENT'
and sequence# = (select max(sequence#) + 1
from v$log_history);

/* what are the last 3 individual switch intervals L3I:<1>:<2>:<3> */
select 'Prior 3 Actual Switch Intervals:' || chr(9) || '[' ||
round((lc.first_time - lc_1.first_time) * 24 * 60,1)
|| ']' || chr(9) || '[' ||
round((lc_1.first_time - lc_2.first_time) * 24 * 60,1)
|| ']' || chr(9) || '[' ||
round((lc_2.first_time - lc_3.first_time) * 24 * 60,1)
|| ']'
from v$log lc, v$log_history lc_1, v$log_history lc_2, v$log_history lc_3
where lc.sequence# = (select max(l.sequence#) from v$log l)
and lc_1.sequence# = lc.sequence# -1
and lc_2.sequence# = lc.sequence# -2
and lc_3.sequence# = lc.sequence# -3;

/* what is the avg interval for last 3 log switches L3S: */
select 'Prior 3 Average Switch Interval:' || chr(9) ||
round( (
round((lc.first_time - lc_1.first_time) * 24 * 60,2) +
round((lc_1.first_time - lc_2.first_time) * 24 * 60,2) +
round((lc_2.first_time - lc_3.first_time) * 24 * 60,2) ) / 3, 1)
from v$log lc, v$log_history lc_1, v$log_history lc_2, v$log_history lc_3
where lc.sequence# = (select max(l.sequence#) from v$log l)
and lc_1.sequence# = lc.sequence# -1
and lc_2.sequence# = lc.sequence# -2
and lc_3.sequence# = lc.sequence# -3;

set pages 24





script to recreate the database minus data .TXT
====================================================
#!/bin/sh
set -x
export ORACLE_SID=$1
sqlplus -s internal <Rem Filename : Generate_createdb.sh
Rem Purpose : To reverse engineer the createdb.sql from a running database
Rem Syntax : Generate_createdb.sh
Rem produces an output called createdb_$ORACLE_SID.sql
Rem
Rem Notes : Developed and tested on Oracle 7.3.3 for Solaris
Rem (some cursory tests on Oracle 8.1.7 for Solaris and Linux done
Rem by A I Rehberg, but this script is not certified in any way).
Rem Runs ONLY catproc.sql, catalog.sql, catdbsyn.sql and pubbld.sql
Rem Non UNIX databases may need small modifications to the paths
Rem of called scripts i.e. catalog.sql, catproc.sql ...
Rem You will have to review the resulting script and apply some
Rem corrections/additions/changes to it before it will actually
Rem do what you expect from it - but it at least gives you a good
Rem basis to start with!
Rem
Rem Not done : - resource consumer groups are not catered for
Rem - account locks not catered for
Rem - "Create Role" statements are not created
Rem
Rem History : 20-01-2000 E Augustine Created
Rem 14-02-2000 E Augustine
Rem - Database is created in NOARCHIVELOG and then altered
Rem to ARCHIVELOG if originally configured in ARCHIVELOG.
Rem 09-04-2002 A I Rehberg Additions on Oracle 8i
Rem - AutoExtending datafiles recognized now
Rem - TableSpaces for SYS and SYSTEM read from DB
Rem (instead of being hardcoded)
Rem - User Accounts now also extracted (passwords are set to the
Rem user name since they can't be read from DB)
Rem - Grants are extracted now for users as well as roles
Rem - Profiles are catered for
Rem - Dropped the hardcoded creation of user OPS$ORACLE
Rem 12-04-2002 D Kapfer Shell Scripting & more
Rem - Embedded the generator in a bash script to support
Rem Environment variables
Rem - created work-around for "MaxExtents Unlimited" problem
Rem (Oracle just stores this value as a high integer)
Rem - location of executed scripts now based on $ORACLE_HOME
Rem - added execution of catdbsyn.sql and pubbld.sql
Rem 14-04-2002 A I Rehberg Small fixes
Rem - catproc.sql was not executed (added it now)
Rem - some statements had wrong syntax (fixed)
Rem 23-04-2002 A I Rehberg Small additions
Rem - added "Extent Management", "[No]Logging" and "Minimum Extent"
Rem clauses to "Create TableSpace" statements
Rem
Set TERMOUT OFF
Set SERVEROUTPUT On Size 1000000
Set LINESIZE 300
Set TRIMSPOOL On
Set FEEDBACK OFF
Set Echo Off
Spool createdb_${ORACLE_SID}.sql
Declare
-- Non SYSTEM Tablespaces are created explicitly.
-- The Min(FILE_ID) is used to select the order in which Tablespaces
-- were created.
Cursor C_Tablespaces Is
Select TABLESPACE_NAME, Min(FILE_ID) From DBA_DATA_FILES
Where TABLESPACE_NAME Not Like 'SYSTEM%'
Group By TABLESPACE_NAME
Order By 2;
-- All files for a tablespace.
Cursor C_Datafiles ( P_TS Varchar2 ) Is
Select * From DBA_DATA_FILES
Where TABLESPACE_NAME = P_TS
Order By FILE_ID;
-- Profile information
Cursor C_Profiles Is
Select Distinct PROFILE
From DBA_PROFILES
Where PROFILE <> 'DEFAULT';
Cursor C_Profile_Info (PROF In Varchar2) Is
Select PROF,RESOURCE_NAME,LIMIT
From DBA_PROFILES
Where PROFILE=PROF;

-- User information
Cursor C_User_Info Is
Select USERNAME,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE,PROFILE,ACCOUNT_STATUS
From DBA_USERS
Where lower(USERNAME) Not In ('sys','system','outln','dbsnmp')
Order By USER_ID;
-- Administrative User information
Cursor C_Admin_Info Is
Select USERNAME,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE
From DBA_USERS
Where lower(USERNAME) In ('sys','system')
Order By USER_ID;
-- Tablespace Quota information
Cursor C_Quota_Info Is
Select * From DBA_TS_QUOTAS
Order By USERNAME;
-- Role information
Cursor C_Role_Info Is
Select * From DBA_ROLE_PRIVS
Where lower(GRANTEE) Not In ('sys','system','outln','dbsnmp')
Order By GRANTEE;
-- System Privileges
Cursor C_SysPriv_Info Is
Select * from DBA_SYS_PRIVS
Where lower(GRANTEE) Not In ('sys','system','outln','dbsnmp','connect','dba','exp_full_database','imp_full_database','recovery_catalog_owner','resource','snmpagent','aq_administrator_role','aq_user_role','hs_admin_role','plustrace','tkprofer','ck_oracle_repos_owner')
Order By GRANTEE;
-- Object Privileges (only those on sys/systems objects; all others
-- are provided by exp/imp the schemes)
Cursor C_TabPriv_Info Is
Select * From DBA_TAB_PRIVS
Where lower(GRANTOR) In ('public','sys','system')
And GRANTEE Not In (Select ROLE From DBA_ROLES)
And lower(GRANTEE) Not In ('sys','system')
Order By GRANTEE,TABLE_NAME;

-- Tablespace information
Cursor C_Tablespace_Info ( P_TS Varchar2 ) Is
Select * From DBA_TABLESPACES
Where TABLESPACE_NAME = P_TS;
-- SYSTEM rollback segment is created implicitly hence not selected.
Cursor C_Rollback_Segs ( P_TS Varchar2 ) Is
Select * From DBA_ROLLBACK_SEGS
Where TABLESPACE_NAME = P_TS
And SEGMENT_NAME <> 'SYSTEM'
Order By SEGMENT_ID;
Cursor C_Rollback_Online Is
Select SEGMENT_NAME
From dba_rollback_segs
Where SEGMENT_NAME <> 'SYSTEM'
Order By SEGMENT_ID;
Cursor C_Logfile ( L_GROUP_NUM In Varchar2 ) Is
Select A.Group#, A.Bytes, A.Members, B.Member
From V\$LOG A, V\$LOGFILE B
Where A.Group# = B.Group#
And A.Group# = L_GROUP_NUM
Order By A.Group#;
FirstTime Boolean;
L_LINE Varchar2(2000); -- A line to output
L_SID Varchar2(20); -- Oracle Database Name
L_LOGMODE Varchar2(50); -- Archivelog or not
L_CHRSET Varchar2(50); -- NLS character set
L_GROUPS Number(9); -- Nos of Log Groups
L_LOGSIZE Number(9); -- Logfile size
L_MEMBERS Number(9); -- Nos Members per Group
L6 Varchar2(9); -- 6 spaces
L4 Varchar2(9); -- 4 spaces
L2 Varchar2(9); -- 2 spaces
L_MAXEXTENTS Varchar2(20); -- DKapfer required for 'Unlimited'
Begin
L2 := ' ';
L4 := L2||L2;
L6 := L2||L4;

L_LINE := 'Connect Internal'||Chr(10)||
'Set TERMOUT On ECHO On'||Chr(10)||
'Spool createdb_${ORACLE_SID}.log'||Chr(10)||
'Startup nomount'||Chr(10);

dbms_output.put_line(L_LINE);
Select NAME, LOG_MODE
Into L_SID, L_LOGMODE
From V\$DATABASE;
Select VALUE Into L_CHRSET
From V\$NLS_PARAMETERS
Where Upper(PARAMETER) = 'NLS_CHARACTERSET';
Select Max(GROUP#), Max(MEMBERS), Max(BYTES)/1024
Into L_GROUPS, L_MEMBERS, L_LOGSIZE
From V\$LOG;
L_LINE := 'Create Database "'|| L_SID ||'"'|| Chr(10)||
L4||'Maxlogfiles '||To_Char(L_GROUPS*L_MEMBERS*4)||Chr(10)||
L4||'Maxlogmembers '||To_Char(L_MEMBERS*2)||Chr(10)||
L4||'Maxloghistory 160'||Chr(10)||
L4||'Maxdatafiles 255'||Chr(10)||
L4||'Maxinstances 1'||Chr(10)||
L4||'NOARCHIVELOG'||Chr(10)||
L4||'Character Set "'||L_CHRSET||'"'||Chr(10)||
L4||'Datafile';
dbms_output.put_line(L_LINE);
--
-- Get the datafiles for the SYSTEM tablespace
--
FirstTime := TRUE;
For Rec_Datafiles In C_Datafiles ( 'SYSTEM' ) Loop
If FirstTime Then
FirstTime := FALSE;
L_LINE := ' ';
Else
L_LINE := L_LINE||' ,'||Chr(10)||' ';
End If;
L_LINE := L_LINE||
''''||Rec_Datafiles.FILE_NAME||''''||' Size '||
To_Char(Rec_Datafiles.BYTES/1024)||' K';
If Rec_Datafiles.AUTOEXTENSIBLE = 'YES' Then
L_LINE := L_LINE||' Autoextend On';
End If;
End Loop;
L_LINE := L_LINE || Chr(10) || L4 || 'Logfile ';
dbms_output.put_line(L_LINE);
--
-- Create the LOGFILE bits ...
--
FirstTime := TRUE; -- For groups
For L_INDEX In 1.. L_GROUPS Loop
If FirstTime Then
FirstTime := FALSE; -- For groups
L_LINE := ' Group '|| To_Char(L_INDEX) || ' (';
Else
L_LINE := ' ,Group '|| To_Char(L_INDEX) || ' (';
End If;
FirstTime := TRUE; -- For members
For Rec_Logfile In C_Logfile ( L_INDEX ) Loop
If FirstTime Then
FirstTime := FALSE; -- For members
Else
L_LINE := L_LINE ||Chr(10)||L6||L2||' ,';
End If;
L_LINE := L_LINE||''''||Rec_Logfile.MEMBER||'''';
End Loop;
L_LINE := L_LINE || Chr(10)||L6||L2||
' ) Size '||To_Char(L_LOGSIZE)||' K ';
dbms_output.put_line(L_LINE);
End Loop;
--
-- The bootstrapping stuff ...
--
L_LINE := '/'||Chr(10)||Chr(10)||
'Set TERMOUT Off ECHO Off'||Chr(10)||
'@${ORACLE_HOME}/rdbms/admin/catalog.sql'||Chr(10)||
'@${ORACLE_HOME}/rdbms/admin/catproc.sql'||Chr(10)||
'Set TERMOUT On ECHO On'||Chr(10)||Chr(10);
dbms_output.put_line(L_LINE);
--
-- The Rollback segments in the SYSTEM tablespace
--
For Rec_RBS In C_Rollback_Segs ('SYSTEM') Loop
L_LINE := 'Create Rollback Segment '||Rec_RBS.SEGMENT_NAME||
Chr(10)||' Tablespace '||
Rec_RBS.TABLESPACE_NAME||
Chr(10)||' '||'Storage (';

if Rec_RBS.MAX_EXTENTS > 2000000000 or Rec_RBS.MAX_EXTENTS is null then /* DKapfer */
L_MAXEXTENTS := 'Unlimited';
else
L_MAXEXTENTS := To_Char(Rec_RBS.MAX_EXTENTS);
end if;

L_LINE := L_LINE||Chr(10)||' Initial '||
To_Char(Rec_RBS.INITIAL_EXTENT/1024)||' K'||
Chr(10)||' Next '||
To_Char(Rec_RBS.NEXT_EXTENT/1024)||' K'||
Chr(10)||' Minextents '||
To_Char(Rec_RBS.MIN_EXTENTS)||
Chr(10)||' Maxextents '||
L_MAXEXTENTS || /* DKapfer */
-- Rec_RBS.MAX_EXTENTS|| /* DKapfer */
Chr(10)||' Optimal '||
To_Char(Rec_RBS.MIN_EXTENTS *
Rec_RBS.NEXT_EXTENT/1024)||' K'||Chr(10)
||' )'||Chr(10)||'/'||Chr(10)
;
dbms_output.put_line(L_LINE);
End Loop;

--
-- Create all other tablespaces ...
--
For Rec_Tablespaces In C_Tablespaces Loop
L_LINE := 'Create Tablespace '||Rec_Tablespaces.TABLESPACE_NAME;
dbms_output.put_line(L_LINE);
FirstTime := TRUE;
For Rec_Datafiles In
C_Datafiles ( Rec_Tablespaces.TABLESPACE_NAME ) Loop
If FirstTime Then
FirstTime := FALSE;
L_LINE := ' Datafile ';
Else
L_LINE := ' ,';
End If;
L_LINE := L_LINE||''''||Rec_Datafiles.FILE_NAME||''''||
' Size '||
To_Char(Rec_Datafiles.BYTES/1024)||' K';
If Rec_Datafiles.AUTOEXTENSIBLE = 'YES' Then
L_LINE := L_LINE||' Autoextend On';
End If;
dbms_output.put_line(L_LINE);
End Loop;

For Rec_TS_Info In
C_Tablespace_Info( Rec_Tablespaces.TABLESPACE_NAME ) Loop
if Rec_TS_Info.MAX_EXTENTS > 2000000000 or Rec_TS_Info.MAX_EXTENTS is null then /* DKapfer */
L_MAXEXTENTS := 'Unlimited';
else
L_MAXEXTENTS := To_Char(Rec_TS_Info.MAX_EXTENTS);
end if;

L_LINE :=' Default Storage ( '||
Chr(10)||' Initial '||
To_Char(Rec_TS_Info.INITIAL_EXTENT/1024)||' K'||
Chr(10)||' Next '||
To_Char(Rec_TS_Info.NEXT_EXTENT/1024)||' K'||
Chr(10)||' Minextents '||
To_Char(Rec_TS_Info.MIN_EXTENTS)||
Chr(10)||' Maxextents '||
L_MAXEXTENTS || /* DKapfer */
-- Rec_TS_Info.MAX_EXTENTS|| /*DKapfer*/
Chr(10)||' Pctincrease '||
To_Char(Rec_TS_Info.PCT_INCREASE)||
Chr(10)||
' )'
;
L_LINE := L_LINE||Chr(10)||' '||
'Extent Management '||Rec_TS_Info.EXTENT_MANAGEMENT||chr(10);
L_LINE := L_LINE||' '||Rec_TS_Info.LOGGING||Chr(10);
L_LINE := L_LINE||' Minimum Extent '||Rec_TS_Info.MIN_EXTLEN;
L_LINE := L_LINE||Chr(10)||' '||
Rec_TS_Info.CONTENTS||Chr(10)||'/'||Chr(10)||Chr(10);
dbms_output.put_line(L_LINE);
End Loop;

--
-- Create all Rollback segments in the tablespace being created ...
--
For Rec_RBS In C_Rollback_Segs (Rec_Tablespaces.TABLESPACE_NAME) Loop
L_LINE := 'Create Rollback Segment '||Rec_RBS.SEGMENT_NAME||
Chr(10)||' Tablespace '||
Rec_RBS.TABLESPACE_NAME||
Chr(10)||' '||'Storage (';
if Rec_RBS.MAX_EXTENTS > 2000000000 or Rec_RBS.MAX_EXTENTS is null then /* DKapfer */
L_MAXEXTENTS := 'Unlimited';
else
L_MAXEXTENTS := To_Char(Rec_RBS.MAX_EXTENTS);
end if;

L_LINE := L_LINE||Chr(10)||' Initial '||
To_Char(Rec_RBS.INITIAL_EXTENT/1024)||' K'||
Chr(10)||' Next '||
To_Char(Rec_RBS.NEXT_EXTENT/1024)||' K'||
Chr(10)||' Minextents '||
To_Char(Rec_RBS.MIN_EXTENTS)||
Chr(10)||' Maxextents '||
L_MAXEXTENTS || /* DKapfer */
-- Rec_RBS.MAX_EXTENTS|| /* DKapfer */
Chr(10)||' Optimal '||
To_Char(Rec_RBS.MIN_EXTENTS *
Rec_RBS.NEXT_EXTENT/1024)||' K'||Chr(10)
||' )'||Chr(10)||'/'||Chr(10)
;
dbms_output.put_line(L_LINE);
End Loop;
End Loop;
--
-- Alter all Rollback segments Online ...
--
For Rec_RBSO In C_Rollback_Online Loop
L_LINE := 'Alter Rollback Segment '||Rec_RBSO.SEGMENT_NAME||' Online;';
dbms_output.put_line(L_LINE);
End Loop;
L_LINE := Chr(10);
dbms_output.put_line(L_LINE);

--
-- Get the profiles information
--

For Rec_Prof In C_Profiles Loop
L_LINE := 'Create Profile '||Rec_Prof.PROFILE
||' Limit SESSIONS_PER_USER Default;';
dbms_output.put_line(L_LINE);
For Rec_ProfData In C_Profile_Info (Rec_Prof.PROFILE) Loop
L_LINE :=
'Alter Profile '||Rec_ProfData.PROF||' LIMIT '
||Rec_ProfData.RESOURCE_NAME||' '
||Rec_ProfData.LIMIT||';';
dbms_output.put_line(L_LINE);
End Loop;
L_LINE := Chr(10);
dbms_output.put_line(L_LINE);
End Loop;

--
-- Get the data for the Admins
--
FirstTime := TRUE;
For Rec_AdminData In C_Admin_Info Loop
If FirstTime Then
FirstTime := FALSE;
L_LINE := '';
Else
L_LINE := L_LINE||Chr(10);
End If;
L_LINE := L_LINE||
'Alter User '||Rec_AdminData.USERNAME||Chr(10)||L4
||'Default Tablespace '||Rec_AdminData.DEFAULT_TABLESPACE||Chr(10)||L4
||'Temporary Tablespace '||Rec_AdminData.TEMPORARY_TABLESPACE||';'
||Chr(10)||'/'||Chr(10);
End Loop;

L_LINE := L_LINE||Chr(10);
dbms_output.put_line(L_LINE);
--
-- Get the data for the other users
--
For Rec_UserData In C_User_Info Loop
L_LINE :=
'Create User '||Rec_UserData.USERNAME
||' Identified By '||Rec_UserData.USERNAME||Chr(10)||L4
||'Default Tablespace '||Rec_UserData.DEFAULT_TABLESPACE||Chr(10)||L4
||'Temporary Tablespace '||Rec_UserData.TEMPORARY_TABLESPACE||Chr(10)||L4
||'Profile '||Rec_UserData.PROFILE
||';'||Chr(10)||'/'||Chr(10);
dbms_output.put_line(L_LINE);
End Loop;

For Rec_Quota In C_Quota_Info Loop
L_LINE := 'Alter User '||Rec_Quota.USERNAME||' Quota ';
If Rec_Quota.MAX_BYTES = -1 Then
L_LINE := L_LINE||'Unlimited On ';
Else
L_LINE := L_LINE||Rec_Quota.MAX_BYTES||' On ';
End If;
L_LINE := L_LINE||Rec_Quota.TABLESPACE_NAME||';';
dbms_output.put_line(L_LINE);
End Loop;
L_LINE := '/'||Chr(10)||Chr(10);
dbms_output.put_line(L_LINE);

For Rec_Role In C_Role_Info Loop
L_LINE := 'GRANT '||Rec_Role.GRANTED_ROLE
||' To '||Rec_Role.GRANTEE;
If Rec_Role.ADMIN_OPTION = 'YES' Then
L_LINE := L_LINE||' With Admin Option';
End If;
L_LINE := L_LINE||';';
dbms_output.put_line(L_LINE);
End Loop;
L_LINE := '/'||Chr(10)||Chr(10);
dbms_output.put_line(L_LINE);
For Rec_SysPriv In C_SysPriv_Info Loop
L_LINE := 'GRANT '||Rec_SysPriv.Privilege
||' To '||Rec_SysPriv.GRANTEE;
If Rec_SysPriv.ADMIN_OPTION = 'YES' Then
L_LINE := L_LINE||' With Admin Option';
End If;
L_LINE := L_LINE||';';
dbms_output.put_line(L_LINE);
End Loop;
L_LINE := '/'||Chr(10)||Chr(10);
dbms_output.put_line(L_LINE);
For Rec_TabPriv In C_TabPriv_Info Loop
L_LINE := 'GRANT '||Rec_TabPriv.PRIVILEGE
||' On '||Rec_TabPriv.GRANTOR||'.'||Rec_TabPriv.TABLE_NAME
||' To '||Rec_TabPriv.GRANTEE;
If Rec_TabPriv.GRANTABLE = 'YES' Then
L_LINE := L_LINE||' With Grant Option';
End If;
L_LINE := L_LINE||';';
dbms_output.put_line(L_LINE);
End Loop;
L_LINE := '/'||Chr(10)||Chr(10);
dbms_output.put_line(L_LINE);
L_LINE := 'Connect System/Manager'||Chr(10)||
'Set TERMOUT Off ECHO Off'||Chr(10)||
'@${ORACLE_HOME}/rdbms/admin/catdbsyn'||Chr(10)|| /*DKapfer*/
'@${ORACLE_HOME}/sqlplus/admin/pupbld'||Chr(10)|| /*DKapfer*/
'Set TERMOUT On ECHO On'||Chr(10);
dbms_output.put_line(L_LINE);
L_LINE := 'Connect Internal'||Chr(10)||
'Shutdown'||Chr(10)||
'Startup'||Chr(10)||Chr(10);
dbms_output.put_line(L_LINE);
If L_LOGMODE = 'ARCHIVELOG' Then
L_LINE := 'Shutdown immediate'||Chr(10)||
'Startup Mount'||Chr(10)||
'Alter Database ARCHIVELOG;'||Chr(10)||
'Alter Database Open;'||Chr(10);
dbms_output.put_line(L_LINE);
End If;
L_LINE := 'Spool Off'||Chr(10)||'Exit'||Chr(10);
dbms_output.put_line(L_LINE);
End;
/
Spool Off
Exit
EOF
echo $ORACLE_SID
exit 0


Shows current statements for active sessions.TXT
==================================================
rem *********************************************************************
rem * Copyright © Oracle-Consultant.co.uk 2001, all rights reserved *
rem * *
rem * Name : cursta.sql *
rem * Synopsis : Shows current statements for active sessions *
rem * Source : http://www.oracle-consultant.co.uk *
rem * *
rem * Oracle-Consultant.co.uk are not responsible for any liability *
rem * that may arise from the use of this code. Support can be obtained *
rem * by emailing script_support@oracle-consultant.co.uk *
rem * Note: This script is best viewed in a fixed-width font. *
rem *********************************************************************

column pu format a8 heading 'O/S|Login|ID'
column su format a8 heading 'Oracle|User ID'
column stat format a8 heading 'Session|Status'
column ssid format 999999 heading 'Oracle|Session|ID'
column sser format 999999 heading 'Oracle|Serial|No'
column spid format 99999999 heading 'O/S|Process|ID'
column txt format a2000 heading 'Current Statement' wrapped

set linesize 1000
set pagesize 32000
set trimspool on

select p.username pu
, s.username su
, s.status stat
, s.sid ssid
, s.serial# sser
, substr(p.spid,1,8) spid
, substr(sa.sql_text,1,2000) txt
from v$process p
, v$session s
, v$sqlarea sa
where p.addr = s.paddr
and s.username is not null
and s.sql_address = sa.address(+)
and s.sql_hash_value = sa.hash_value(+)
and s.status = 'ACTIVE'
order by 1,2,7
/

column pu clear
column su clear
column stat clear
column ssid clear
column sser clear
column spid clear
column txt clear


Shows status of DBMS jobs .TXT
===============================
rem *********************************************************************
rem * Copyright © Oracle-Consultant.co.uk 2001, all rights reserved *
rem * *
rem * Name : jobs.sql *
rem * Synopsis : Shows status of DBMS jobs *
rem * Source : http://www.oracle-consultant.co.uk *
rem * *
rem * Oracle-Consultant.co.uk are not responsible for any liability *
rem * that may arise from the use of this code. Support can be obtained *
rem * by emailing script_support@oracle-consultant.co.uk *
rem * Note: This script is best viewed in a fixed-width font. *
rem *********************************************************************

column this_date format a15
column next_date format a15
column last_date format a15
column what format a60
column failures format 999
column job format 999
column hidden_last_date noprint

set linesize 1000
set pagesize 32000

break on hidden_last_date skip 1

select job
, to_char(last_date, 'DD-MON-YY HH24:MI') last_date
, trunc(last_date) hidden_last_date
, to_char(this_date, 'DD-MON-YY HH24:MI') this_date
, to_char(next_date, 'DD-MON-YY HH24:MI') next_date
, broken
, failures
, what
from sys.dba_jobs
order by 2
/

column this_date clear
column next_date clear
column last_date clear
column what clear
column failures clear
column job clear
column hidden_last_date clear


space occupied by each user .TXT
==================================
select owner, sum(blocks) Totalblocks, sum(bytes)TotalBytes from DBA_SEGMENTS group by owner



tablespace usage.sql
======================

column file_name format a45
column tablespace_name format a10

set verify off
accept tbs prompt 'What is the Tablespace Name: '


SELECT dts.tablespace_name,
NVL(ddf.bytes / 1024 / 1024, 0) avail,
NVL(ddf.bytes - NVL(dfs.bytes, 0), 0)/1024/1024 used,
NVL(dfs.bytes / 1024 / 1024, 0) free,
TO_CHAR(NVL((ddf.bytes - NVL(dfs.bytes, 0)) / ddf.bytes * 100, 0), '990.00')
"Used %" ,
TO_CHAR(NVL((ddf.bytes - NVL(ddf.bytes - NVL(dfs.bytes, 0), 0)) / ddf.bytes
* 100, 0), '990.00') free_pct,
decode(sign(
(NVL(ddf.bytes - NVL(dfs.bytes, 0), 0)/1024/1024)/0.85 - NVL(ddf.bytes / 1024 /
1024, 0)),-1,0,(NVL(ddf.bytes - NVL(dfs.bytes, 0), 0)/1024/1024)/0.85 - NVL(ddf.bytes / 1024 / 1024, 0)) "Required MB"
FROM
sys.dba_tablespaces dts,
(select tablespace_name, sum(bytes) bytes
from dba_data_files group by tablespace_name) ddf,
(select tablespace_name, sum(bytes) bytes
from dba_free_space group by tablespace_name) dfs
WHERE
dts.tablespace_name = ddf.tablespace_name(+)
AND dts.tablespace_name = dfs.tablespace_name(+)
and dts.tablespace_name = '&tbs';

select file_name,bytes/1024/1024 from dba_data_files
where tablespace_name = '&tbs';


Tabular display of redo-log archiving history (logs hour).TXT
================================================================
rem -----------------------------------------------------------------------
rem Filename: archdist.sql
rem Purpose: Tabular display of redo-log archiving history (logs/hour)
rem - Can only run from sqlplus
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

set pagesize 50000
set veri off
set colsep ""

set termout off
def time="time" -- Oracle7
col time new_value time
select 'to_char(first_time,''DD/MM/YY HH24:MI:SS'')' time
from dual
where &&_O_RELEASE like '8%' -- Oracle8
/
set termout on

select substr(&&time, 1, 5) day,
to_char(sum(decode(substr(&&time,10,2),'00',1,0)),'99') "00",
to_char(sum(decode(substr(&&time,10,2),'01',1,0)),'99') "01",
to_char(sum(decode(substr(&&time,10,2),'02',1,0)),'99') "02",
to_char(sum(decode(substr(&&time,10,2),'03',1,0)),'99') "03",
to_char(sum(decode(substr(&&time,10,2),'04',1,0)),'99') "04",
to_char(sum(decode(substr(&&time,10,2),'05',1,0)),'99') "05",
to_char(sum(decode(substr(&&time,10,2),'06',1,0)),'99') "06",
to_char(sum(decode(substr(&&time,10,2),'07',1,0)),'99') "07",
to_char(sum(decode(substr(&&time,10,2),'08',1,0)),'99') "08",
to_char(sum(decode(substr(&&time,10,2),'09',1,0)),'99') "09",
to_char(sum(decode(substr(&&time,10,2),'10',1,0)),'99') "10",
to_char(sum(decode(substr(&&time,10,2),'11',1,0)),'99') "11",
to_char(sum(decode(substr(&&time,10,2),'12',1,0)),'99') "12",
to_char(sum(decode(substr(&&time,10,2),'13',1,0)),'99') "13",
to_char(sum(decode(substr(&&time,10,2),'14',1,0)),'99') "14",
to_char(sum(decode(substr(&&time,10,2),'15',1,0)),'99') "15",
to_char(sum(decode(substr(&&time,10,2),'16',1,0)),'99') "16",
to_char(sum(decode(substr(&&time,10,2),'17',1,0)),'99') "17",
to_char(sum(decode(substr(&&time,10,2),'18',1,0)),'99') "18",
to_char(sum(decode(substr(&&time,10,2),'19',1,0)),'99') "19",
to_char(sum(decode(substr(&&time,10,2),'20',1,0)),'99') "20",
to_char(sum(decode(substr(&&time,10,2),'21',1,0)),'99') "21",
to_char(sum(decode(substr(&&time,10,2),'22',1,0)),'99') "22",
to_char(sum(decode(substr(&&time,10,2),'23',1,0)),'99') "23"
from sys.v_$log_history
group by substr(&&time,1,5)
/

set colsep " "



to find when was the last reset logs done.txt
================================================
select STATUS,ERROR,RECOVER,FUZZY,CREATION_TIME,RESETLOGS_TIME from v$datafile_header where rownum<10;

to find when was the resetlogs for a particular datafile

select STATUS,ERROR,RECOVER,FUZZY,CREATION_TIME,RESETLOGS_TIME from v$datafile_header where file#=397;


utlfile.txt
=============
CREATE DIRECTORY test_dir AS 'c:\';
-- CREATE DIRECTORY test_dir AS '/tmp';

DECLARE
fileHandler UTL_FILE.FILE_TYPE;
BEGIN
fileHandler := UTL_FILE.FOPEN('test_dir', 'test_file.txt', 'W');
UTL_FILE.PUTF(fileHandler, 'Look ma, I''m writing to a file!!!\n');
UTL_FILE.FCLOSE(fileHandler);
EXCEPTION
WHEN utl_file.invalid_path THEN
raise_application_error(-20000, 'ERROR: Invalid path for file.');
END;
/




whereami.sql
==============
/* whereami.sql
provides info on db instance, server etc for your session
*/
set pause off
set pages 24
set lines 132
col SERVER format a10 trunc
col program format a30 trunc
col username format a10 trunc
col sid format 9999
col serial# format 99999 head SRL#
col value format a6 head THREAD
col spid head OSProc
col thread# head THREAD format 999
col open_mode head DBSTAT
col startedon format a13
select p.machine SERVER, d.name DBNAME, i.thread#, i.status ISTAT,
to_char(i.startup_time,'mm/dd hh:miAM') startedon,
u.username, u.sid, u.serial#, u.program, o.spid
from v$database d, v$instance i, v$session p, v$session u, v$process o
where p.sid = 1
and u.sid = (select sid from v$mystat where rownum = 1)
and o.addr = u.paddr;
clear columns




worked performed by sesison.sql
==============================
/* sesswork.sql
show the work performed by a session
*/
accept trgtsid number prompt 'What is the SID : '
select n.name,s.value from v$sesstat s, v$statname n
where sid = &trgtsid
and s.statistic# = n.statistic#
and s.value > 0
and n.name not in ('session connect time','process last non-idle time')
order by 2;

prompt Specific Buffer Cache Hit ratio values.....
select n.name,s.value from v$sesstat s, v$statname n
where sid = &trgtsid
and s.statistic# = n.statistic#
and n.name in ('consistent gets','db block gets','physical reads');

prompt Specific Sorting values.....
select n.name,s.value from v$sesstat s, v$statname n
where sid = &trgtsid
and s.statistic# = n.statistic#
and n.name like '%sort%';

space db scripts

Check whether a tablespace is fragmented and show fragmentation type.txt
=========================================================================
Check whether a tablespace is fragmented and show fragmentation type

TTI "Tablespace Fragmentation Details"

SELECT dfsc.tablespace_name tablespace_name,
DECODE (
dfsc.percent_extents_coalesced,
100,
(DECODE (
GREATEST ((SELECT COUNT (1)
FROM dba_free_space dfs
WHERE dfs.tablespace_name =
dfsc.tablespace_name), 1),
1,
'No Frag',
'Bubble Frag'
)
),
'Possible Honey Comb Frag'
)
fragmentation_status
FROM dba_free_space_coalesced dfsc
ORDER BY dfsc.tablespace_name;

TTI off



List segments that can not extend (tablespace full.txt
=======================================================

prompt Objects that cannot extend (no space in TS)

column Sname form a40 heading 'Object Name'
column Stype form a15 heading 'Type'
column Size form 9,999 heading 'Size'
column Next form 99,999 heading 'Next'
column Tname form a15 heading 'TsName'

select a.owner||'.'||a.segment_name "Sname",
a.segment_type "Stype",
a.bytes/1024/1024 "Size",
a.next_extent/1024/1024 "Next",
a.tablespace_name "TName"
from sys.dba_segments a
where a.tablespace_name not like 'T%MP%' -- Exclude TEMP tablespaces
and next_extent * 1 > ( -- Cannot extend 1x, can
change to 2x...
select max(b.bytes)
from dba_free_space b
where a.tablespace_name =
b.tablespace_name)
order by 3 desc
/

-- THIS QUERY GIVES THE SAME RESULTS, BUT IS WAY TOO SLOW
--
-- 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
-- /


List tables with high water mark not equal to used blocks.txt
============================================================
set verify off
column owner format a10
column alcblks heading 'Allocated|Blocks' just c
column usdblks heading 'Used|Blocks' just c
column hgwtr heading 'High|Water' just c
break on owner skip page

select
a.owner,
a.table_name,
b.blocks alcblks,
a.blocks usdblks,
(b.blocks-a.empty_blocks-1) hgwtr
from
dba_tables a,
dba_segments b
where
a.table_name=b.segment_name
and a.owner=b.owner
and a.owner not in('SYS','SYSTEM')
and a.blocks <> (b.blocks-a.empty_blocks-1)
and a.owner like upper('&owner')||'%'
and a.table_name like upper('&table_name')||'%'
order by 1,2
/

set verify on
clear columns
clear breaks



Show database growth in Meg per month for the last year.txt
===========================================================
set pagesize 50000
tti "Database growth per month for last year"

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')
/

tti off


Show used &free space per segment type.txt
===========================================
-----------------------------------------------------------------------

---------------------------------------------------------------------------
-- Create history table...
---------------------------------------------------------------------------
drop table db_space_hist;
create table db_space_hist (
timestamp date,
total_space number(8),
used_space number(8),
free_space number(8),
pct_inuse number(5,2),
num_db_files number(5)
);

---------------------------------------------------------------------------
-- Stored proc to populate table...
---------------------------------------------------------------------------
create or replace procedure db_space_hist_proc as
begin
-- Delete old records...
delete from db_space_hist where timestamp > SYSDATE + 364;
-- Insert current utilization values...
insert into db_space_hist
select sysdate, total_space,
total_space-nvl(free_space,0) used_space,
nvl(free_space,0) free_space,
((total_space - nvl(free_space,0)) / total_space)*100
pct_inuse,
num_db_files
from ( select sum(bytes)/1024/1024 free_space
from sys.DBA_FREE_SPACE ) FREE,
( select sum(bytes)/1024/1024 total_space,
count(*) num_db_files
from sys.DBA_DATA_FILES) FULL;
commit;
end;
/
show errors

---------------------------------------------------------------------------
-- Schedule the job using the DB Job System. This section can be
removed if
-- the job is sceduled via an external scheduler.
---------------------------------------------------------------------------
declare
v_job number;
begin
select job into v_job from user_jobs where what like
'db_space_hist_proc%';
dbms_job.remove(v_job);
dbms_job.submit(v_job, 'db_space_hist_proc;', sysdate,
'sysdate+7'); -- Run every 7 days
dbms_job.run(v_job);
dbms_output.put_line('Job '||v_job||' re-submitted.');
exception
when NO_DATA_FOUND then
dbms_job.submit(v_job, 'db_space_hist_proc;', sysdate,
'sysdate+7'); -- Run every 7 days
dbms_job.run(v_job);
dbms_output.put_line('Job '||v_job||' submitted.');
end;
/

---------------------------------------------------------------------------
-- Generate a space history report...
---------------------------------------------------------------------------
select to_char(timestamp, 'DD Mon RRRR HH24:MI') "Timestamp",
total_space "DBSize (Meg)",
used_space "Free (Meg)",
free_space "Used (Meg)",
pct_inuse "% Used",
num_db_files "Num DB Files"
from db_space_hist
order by timestamp;




Show used &free space per tablespace.txt
===========================================
tti "Space Usage for Database in Meg"

SELECT Total.name "Tablespace Name",
nvl(Free_space, 0) Free_space,
nvl(total_space-Free_space, 0) Used_space,
total_space
FROM
(select tablespace_name, sum(bytes/1024/1024) Free_Space
from sys.dba_free_space
group by tablespace_name
) Free,
(select b.name, sum(bytes/1024/1024) TOTAL_SPACE
from sys.v_$datafile a, sys.v_$tablespace B
where a.ts# = b.ts#
group by b.name
) Total
WHERE Free.Tablespace_name(+) = Total.name
ORDER BY Total.name
/

tti off


Show used&free space per datafile.txt
=====================================
TTI "Allocated, Used & Free space within datafiles"

COLUMN free_space_mb format 999999.90
COLUMN allocated_mb format 999999.90
COLUMN used_mb format 999999.90

SELECT SUBSTR (df.NAME, 1, 40) file_name, df.bytes / 1024 / 1024
allocated_mb,
((df.bytes / 1024 / 1024) - NVL (SUM (dfs.bytes) / 1024 /
1024, 0))
used_mb,
NVL (SUM (dfs.bytes) / 1024 / 1024, 0) free_space_mb
FROM v$datafile df, dba_free_space dfs
WHERE df.file# = dfs.file_id(+)
GROUP BY dfs.file_id, df.NAME, df.file#, df.bytes
ORDER BY file_name;

TTI off



tablespaces with used space more than 90 percent.txt
==================================================
SELECT Total.name "Tablespace Name",
nvl(Free_space, 0) Free_space,
nvl(total_space-Free_space, 0) Used_space,
total_space,nvl(total_space-Free_space, 0)/total_space*100 "PERCENTAGE USED"
FROM
(select tablespace_name, sum(bytes/1024/1024) Free_Space
from sys.dba_free_space
group by tablespace_name) Free,
(select b.name, sum(bytes/1024/1024) TOTAL_SPACE
from sys.v_$datafile a, sys.v_$tablespace B
where a.ts# = b.ts# and b.name <> 'RBS'
group by b.name
) Total
WHERE Free.Tablespace_name(+) = Total.name
and nvl(Free_space, 0)/total_space<.1
order by Total.name;


temp space total.txt
=======================
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;



used&free space per segment type.txt
=====================================
set pagesize 50000
set line 80

col "Total Used Meg" format 999,999,990
col "Data part" format 999,999,990
col "Index part" format 999,999,990
col "LOB part" format 999,999,990
col "RBS part" format 999,999,990

tti 'Used space in Meg by segment type'

select sum(bytes)/1024/1024 "Total Used",
sum( decode( substr(segment_type,1,5), 'TABLE',
bytes/1024/1024, 0))
"Data part",
sum( decode( substr(segment_type,1,5), 'INDEX',
bytes/1024/1024, 0))
"Index part",
sum( decode( substr(segment_type,1,3), 'LOB',
bytes/1024/1024, 0))
"LOB part",
sum( decode(segment_type, 'ROLLBACK',
bytes/1024/1024, 0))
"RBS part",
sum( decode(segment_type, 'TEMPORARY',
bytes/1024/1024, 0))
"TEMP part"
from sys.dba_segments
/
tti off

tti "Total database size"

select sum(bytes)/1024/1024 "Total DB size in Meg"
from sys.v_$datafile
/
tti off

Small Activities

all ad config files.txt
=========================

The file adconfig.sh and all of its supporting scripts are located in the
$AD_TOP/bin directory.

Patches to the Rapid Install product, also known as
ADX, update the templates and the parameters in the XML file. All application
processes should be shut down prior to executing adconfig.sh.

As of version 11.5.10, Oracle introduced the
adautocfg.sh script in the $COMMON_TOP/admin/scripts/$CONTEXT_NAME directory
to serve as a wrapper to adconfig.sh. When executing adautocfg.sh, the
location of the context file is not required.

The log file for the execution of adconfig.sh on the application tier is
located here, where MM is the month, DD is the day, hh is the hour, and mm is the
minute when adconfig.sh was executed:

$APPL_TOP/admin/$CONTEXT_NAME/log/MMDDhhmm/adconfig.log

The log file for the execution of adconfig.sh on the database tier located
here, with MMDDhhmm having the same meaning:

$ORACLE_HOME/appsutil/log/$CONTEXT_NAME/MMDDhhmm/adconfig.log

Location of adconfig.sh Backup Files:
$APPL_TOP/admin/$CONTEXT_NAME/out/MMDDhhmm
$ORACLE_HOME/appsutil/log/$CONTEXT_NAME/out/MMDDhhmm


If you want to restore configuration files from the backup of an adconfig.sh run,
you can execute the $APPL_TOP/admin/$CONTEXT_NAME/out/MMDDhhmm/restore.sh
script. On the database server, this script will be found in the $ORACLE_HOME/
appsutil/out/$CONTEXT_NAME/MMDDhhmm directory.


In order to migrate any customization tags from the manual
configuration files to the custom template, the adcustomizer.sh script
should be run on the node where the customizations have been made. The
AD Configuration utility will then apply customizations that are in the custom
template upon subsequent executions.


apachejserv logs from frontend.txt
===================================
http://xxxx.us.oracle.com:8010/apachelogs .

http://xxxx.us.oracle.com:8010/jservlogs .

building xml file.txt
=====================
If the application context file does not exist, it can be created by executing the adbldxml.sh script:
$ ./$AD_TOP/bin/adbldxml.sh

Change a charset set on Oracle 8i.txt
=====================================
uppose you have Oracle 8.1.x installed on Unix.
1. Check the database NLS settings:

select * from nls_database_parameters;

Note: The character set changing does not affect an existing data, it changes the character set declaration. If the new character set is a binary subset of the old one, then you do not need to worry about the data conversion. Otherwise, use export/import utilities.

In our case the old character sets is US7ASCII, the new one is WE8ISO8859P1.
2. Set the PARALLEL_SERVER parameter in the init.ora file to false. Or do not set it at all.
3. Shutdown the database:

$ svrmgrl
SVRMGR> connect internal
SVRMGR> shutdown

4. Mount the database:

startup mount

5. Issue the following commands:

alter system enable restricted session;

alter system set job_queue_processes=0;

alter system set aq_tm_processes=0;

6. Open the database:

alter database open;

7. Change the database character set

alter database character set WE8ISO8859P1;

Also you might change the national character set:

alter database national character set WE8ISO8859P1;

8. Shutdown and startup the database


check aol jdbc pool status.txt
=================================
http://xxxx.us.oracle.com:8010/OA_HTML/jsp/fnd/AoljDbcPoolStatus.jsp

chk if jtf page is working.txt
==============================
Please run http://machine.name:port/OA_HTML/jtfqalgn.htm

Copy an open database.txt
=========================
Suppose you have Oracle 7 or Oracle 8 installed on Unix.

To make the copy of a existing database PROD_DB without shutting it down, follow these steps:
1. Create the init.ora file for your new database PROD_DB_COPY by making the copy of the parameter file of the PROD_DB database.
2. Make the following modifications in this copy of the init.ora file. Replace all references of PROD_DB on PROD_DB_COPY. Change all directories to point to the new locations.
3. Prepare the SQL script to recreate the control files for your new database. Make the backup of the control file on the PROD_DB database. Run the following command using SQL*Plus or Server Manager:

$ svrmgrl
SVRMGR> connect internal
SVRMGR> alter database backup controlfile to trace resetlogs;
SVRMGR> exit

This command generates the trace file in the user dump directory, see the USER_DUMP_DEST parameter in the init.ora file. Modify this trace file:

- remove header

- replace the old database name by the new one

- replace the next line:

CREATE CONTROLFILE REUSE ...

on

CREATE CONTROLFILE SET DATABASE ... RESETLOGS ...

- replace the contents of the LOGFILE section on the list of the redo logs with the new locations

- replace the contents of the DATAFILE section on the list of the datafiles with the new locations

- comment out the other commands, except CREATE CONTROLFILE.

4. Make the online backup of all tablespaces on PROD_DB. For each tablespace, bring it to the backup mode:

ALTER TABLESPACE tablespace_name BEGIN BACKUP;

Copy all datafiles that constitute the tablespace to the new location.

Bring the tablespace back to the normal mode:

ALTER TABLESPACE tablespace_name END BACKUP;
5. On PROD_DB run the following command:

ALTER SYSTEM ARCHIVE LOG CURRENT;

Copy all archived redo logs to the new location.
6. Change your ORACLE_SID to PROD_DB_COPY, start Server Manager and issue the following command:

STARTUP NOMOUNT;

After the database is mounted run the script to recreate the control files using the trace file that you modified before.
7. To begin the recovery your database, issue the following command:

RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE;

reapply all archived redo logs.
8. Open the database:

ALTER DATABASE OPEN RESETLOGS
Copy database;
Suppose that you have Oracle 7 installed on Unix and you don't use remote logins.
The name of the existing database is PROD_DB.
You want to create the new database TEST_DB - the exact copy of PROD_DB.

This is the short description of your actions:
· Make cold backup of PROD_DB database,
· shutdown it,
· copy its data and log files to a new location,
· create a new database based on the just copied files.
Here is the datailed explanation:
1. Create list (df_prod_db.lst) of the data files on PROD_DB. You can get it from DBA_DATA_FILES system view.
2. Create new list (df_test_db.lst) for TEST_DB database using file that you got at previous step (df_prod_db.lst).
3. Create script (cr_control.sql) to compose control files for the new database TEST_DB.
o Set environment variable ORACLE_SID to PROD_DB;
o Run svrmgrl and execute the next command:

$ svrmgrl
SVRMGR> connect internal
SVRMGR> alter database backup controlfile to trace resetlogs;
SVRMGR> exit
o Find our trace file ora_*.trc, it should be the latest at USER_DUMP_DEST directory (see parameter file).
o Copy ora_*.trc to cr_control.sql
o Edit cr_control.sql file:

- remove header

- replace old sid on a new one: PROD_DB on TEST_DB

- replace the next line:

CREATE CONTROLFILE REUSE ...

on

CREATE CONTROLFILE SET ...

- replace contents of LOGFILE section on the new (set new location, check space)

- replace content of DATAFILE section on the list the datafiles with new a locations, which was created earlier

- comment the next line:

RECOVER DATABASE USING BACKUP CONTROLFILE
4. Make a cold backup of the source database - PROD_DB.
5. Shutdown source database (PROD_DB).

$ svrmgrl
SVRMGR> connect internal
SVRMGR> shutdown immediate
SVRMGR> exit
6. Copy data files (df_prod_db.lst) of PROD_DB to a new location (df_test_db.lst), for TEST_DB database.
7. Create parameter file for TEST_DB, you may copy it from PROD_DB or create a new one.
8. Set environment variable ORACLE_SID to TEST_DB;

Attention: if you set a wrong value, you may corrupt existing database.
9. Create new database TEST_DB.

$ svrmgrl
SVRMGR> connect internal
SVRMGR> shutdown immediate
SVRMGR> @cr_control.sql

ATTENTION!
If the database was not created:
- make shutdown abort
$ shutdown abort
- fix errors
- depending on the error you may have to remove just created control and log files
- try to create a new database (TEST_DB) again


In case of success:
10. Change the GLOBAL_NAME of the database.

$ svrmgrl
SVRMGR> connect internal
SVRMGR> ALTER DATABASE RENAME GLOBAL_NAME TO TEST_DB;
SVRMGR> exit





current patchset level of applications.txt
=============================================
Technology Stack Binaries inclusive Database :

1. Source the 9i ORACLE_HOME Environment
2. Execute the script $ORACLE_HOME/appsutil/install/adlnkoh.sh
3. Source the iAS ORACLE_HOME Envrionment
4. Execute the script $ORACLE_HOME/bin/adlnkiAS.sh
5. Source the APPS Environment
6. Execute the script $ORACLE_HOME/bin/adlnk806.sh

Oracle E-Business-Suite Binaries :

1. Source the APPS Environment
2. Execute following command : adrelink.sh force=y "ad adadmin"
3. Execute adadmin
select --> '2. Maintain Applications Files menu'
select --> '1. Relink Applications programs'
--> select all modules to relink




database is 32 bit or 64 bit.txt
==================================
execute "file $ORACLE_HOME/bin/oracle" , you should see output like
/u01/db/bin/oracle: ELF 64-bit MSB executable SPARCV9 Version 1
which means you are on 64 bit oracle
If your oracle is 32 bit you should see output like
oracle: ELF 32-bit MSB executable SPARC Version 1
Now you know what should be bit of patch to download

db in restrict mode.txt
========================
. When the database is in restricted mode, only users, with CREATE SESSION and RESTRICTED SESSION system privileges, can connect to it.

To bring an instance in restricted mode, use the next command in SQLPlus:
ALTER SYSTEM ENABLE RESTRICTED SESSION;
If someone (user without RESTRICTED SESSION system privilege) was connected to the database before, when restricted mode was activated, all his/her session will stay alive, but all attempts to establish a new connection will fail.

To bring the database back in normal mode:
ALTER SYSTEM DISABLE RESTRICTED SESSION;
To bring the databas in restricted mode during startup, issue the next command from SVRMGRL:
STARTUP RESTRICT
The instance starts up in normal mode, by default.


disable maintainace mode.txt
=============================
sqlplus -s &un_apps/***** @/dmdf1i/applmgr/11510/ad/11.5.0/patch/115/sql/adsetmmd.sql ENABLE

enabling f60cgi login access.txt
==================================
PURPOSE
-------

How to enable the ability to login directly via the f60cgi.

SCOPE & APPLICATION
-------------------

The distribution of this note is set to 'Limited.' These steps are
intended for to enable DBAs to login to forms directly only for
troubleshooting purposes with guidance from Oracle Support.


Accessing f60cgi is disabled in 11.5.10
----------------------------------------

With increased security in Oracle EBusiness Suite 11.5.10, the ability to
connect directly to forms via f60cgi has been disabled. By default, a user will
see the following error after entering their username and password:

APP-FND-01542: This Applications Server is not authorized to access this database.

This is expected functionality.


Enabling f60cgi direct login
------------------------------

It is possible to login however this method should only be used when
debugging problems.

1. Backup and open $APPL_TOP/admin/_.xml context file

2. Update the context variable:
s_appserverid_authentication

By default in 11.5.10, this is set to SECURE.
In previous 11i versions, this was set to OFF.
For debug purposes, you can use ON or OFF.

Modes:
- ON : Partial
- SECURE : activates full server security (SECURE mode)
- OFF : deactivates server security

3. Run Autoconfig to instantiate the change.

You should now be able to access forms directly again using the f60cgi call.

4. After you have finished your Forms debugging, please reset
s_appserverid_authentication to SECURE and re-run Autoconfig.


Alternative option
---------------------

Running Autoconfig is the preferred method of updating
s_appserverid_authentication.

If you are unable to run Autoconfig during troubleshooting, you can run the
the following commands instead from $FND_TOP/secure directory:

Disable:

java oracle.apps.fnd.security.AdminAppServer apps/apps \
AUTHENTICATION OFF DBC=host_visdemo1.dbc

Enable:

To activate basic server security, from the command line, enter:

jre oracle.apps.fnd.security.AdminAppServer apps/apps \
AUTHENTICATION ON DBC=

To activate full server security (SECURE mode), from the command
line, enter:

jre oracle.apps.fnd.security.AdminAppServer apps/apps \
AUTHENTICATION SECURE DBC=

Check the status:

java oracle.apps.fnd.security.AdminAppServer apps/apps \
STATUS DBC=host_visdemo1.dbc



enabling jtf trace level.txt
=============================
Set up a standalone Apache listener on a standalone port
• Add the following line to jserv.properties
wrapper.bin.parameters=-DJTF_TRACE_LEVEL=7
• Shutdown listener after performing activity,
so that the session gets closed

JTF Trace Level Tracing Mode
3 Off
7 Normal Sql Tracing
11 Sql Tracing with bind
19 Sql Tracing with wait
35 Sql Tracing with binds and waits



for jtf issue chec.txt
=======================
+ In $OA_HTML/bin/appsweb.cfg and check the heartbeat parameter in minutes,
Search for the section that starts with
"3) Values for the Forms applet parameters:"
here look in for parameter heartbeat and share its value

3.In $APACHE_TOP/Apache/Jserv/etc/zone.properties and share the session.timeout
in miliseconds):

4. Login into Oracle Applications as SYSADMIN and search for profile options
- ICX:Session Timeout
- ICX: Session Timeout
- ICX:Limit Time
- ICX:Limit connect



imp factors for ICM tuning.txt
==============================
Sleep Time — The sleep time parameter indicates the seconds that the ICM should wait between checking for requests that are waiting to run. The default sleep time is 60, but you can lower this number if you see you have a lot of request waiting (Pending/Normal). However, reducing this number to a very low value many cause excessive cpu utilization.

PMON cycle — This is the number of sleep cycles that the ICM waits between the time it checks for concurrent managers failures, which defaults to 20. You should change the PMON cycle to a number lower than 20 if your concurrent managers are having problems with abnormal terminations.

Queue Size — The queue size is the number of PMON cycles that the ICM waits between checking for disabled or new concurrent managers. The default for queue size of 1 PMON cycle should be used

All of the concurrent managers, with the exception of the ICM and CRM, can be configured to run as many processes as needed, as well as the time and days a manager can process requests.

When the space in FND_CONCURRENT_PROCESSES and FND_CONCURRENT_REQUESTS exceed 50K, you can start to experience serious performance problems within your Oracle Applications. When you experience these space problems, a specific request called "Purge Concurrent Requests And/Or Manager Data" should be scheduled to run on a regular basis. This request can be configured to purge the request data from the FND tables as well as the log files and output files on accumulate on disk.

Cache Size
Concurrent manager performance can also be enhanced by increasing the manager cache size to be at lease twice the number of target processes. The cache size specifies the number of requests that will be cached each time the concurrent manager reads from the FND_CONCURRENT_REQUESTS table. Increasing the cache size will boost the throughput of the managers by attempting to avoid sleep time.

Analyzing Oracle Apps Dictionary Tables for High Performance
It is also very important to run the request Gather Table Statistics on these tables:
FND_CONCURRENT_PROCESSES
FND_CONCURRENT_PROGRAMS
FND_CONCURRENT_REQUESTS
FND_CONCURRENT_QUEUES.
Run the request "Analyze All Index Column Statistics" on the indexes of these tables. Since the APPLSYS user is the owner of these tables, so you can also just run the request Analyze Schema Statistics for APPLSYS.

Another popular way to troubleshoot the Concurrent Managers is to generate a trace file. This is done by setting the OS environment variable FNDSQLCHK to FULL, and running the request from the command line.

When you get a backlog of pending requests, you can first allocate more processes to the manager that is having the problem in order to allow most of the requests to process

Increase the number in the Processes column. Also, you may not need all the concurrent managers that Oracle supplies with an Oracle Applications install, so you can save resources by identifying the uneeded managers and disabling them
If the request remains in a phase of RUNNING and a status of TERMINATING after allocating more processes to the manager, then shutdown the concurrent managers, kill any processes from the operating system that won't terminate, and execute the following sqlplus statement as the APPLSYS user to reset the managers in the FND_CONCURRENT_REQUESTS table:
update fnd_concurrent_requests
set status_code='X', phase_code='C'
where status_code='T';

Changing Dispatching Priority within the Concurrent Manager
If there are requests that have a higher priority to run over other requests, you can navigate to Concurrent --> Program --> Define to change the priority of a request. If a priority is not set for a request, it will have the same priority as all other requests, or it will be set to the value specified in the user's profile option Concurrent:Priority.
Also, you can specify that a request run using an SQL optimizer mode of FIRST_ROWS, ALL_ROWS, RULE, or CHOOSE, and this can radically effect the performance of the SQL inside the Concurrent request. If several long running requests are submitted together, they can cause fast running requests to have to wait unnecessarily. If this is occurring, try to schedule as many long running requests to run after peak business hours. Additionally, a concurrent manager can be created to run only fast running requests
afcmstat.sql Displays all the defined managers, their maximum capacity, pids, and their status.
afimchk.sql Displays the status of ICM and PMON method in effect, the ICM's log file, and determines if the concurrent manger monitor is running.
afcmcreq.sql Displays the concurrent manager and the name of its log file that processed a request.
afrqwait.sql Displays the requests that are pending, held, and scheduled. afrqstat.sql Displays of summary of concurrent request execution time and status since a particular date.
afqpmrid.sql Displays the operating system process id of the FNDLIBR process based on a concurrent request id. The process id can then be used with the ORADEBUG utility.
afimlock.sql Displays the process id, terminal, and process id that may be causing locks that the ICM and CRM are waiting to get. You should run this script if there are long delays when submitting jobs, or if you suspect the ICM is in a gridlock with another oracle process.


JDK USED BY UR APACHE.txt
===========================
$MY_JAVA=`awk -F= '$1 ~ /^JSERVJAVA.*$/ {print $2}' \ $IAS_ORACLE_HOME/Apache/Apache/bin/java.sh`; \ $MY_JAVA -version


jvm pool monitor.txt
===================
Pool Monitor can be used to monitor the resource utilization

Application Module Pool
Memory Utilization
JVM JVM Properties
Pre 11i10: https:///servlet/OAAppModPoolMonitor
11i10: Login->Diagnostics->Show Pool Monitor.

obtain formatted explain plan.txt
===================================
1. Create a Plan Table
----------------------------
Use the utlxplan.sql script to create the table as instructed below.
SQL> @?/rdbms/admin/utlxplan
Note that the plan table format can change between versions so ensure that you create it using the utlxplan script from the current version.
2. To Populate the Plan Table
--------------------------------------
SQL> explain plan for
Explained.
3. Displaying The Execution Plan
------------------------------------------
Version 9.2
--------------
With Oracle 9i version 9.2, Oracle supplies a utility called dbms_xplan. It is created by dbmsutil.sql which is called by catproc.sql. As such it should already be installed on most 9.2 databases.
To generate a formatted explain plan of the query that has just been 'explained':
SQL> set lines 130
SQL> set head off
SQL> spool
SQL> alter session set cursor_sharing=EXACT;
SQL> select plan_table_output from
table(dbms_xplan.display(‘PLAN_TABLE’, Null,’ALL’));
SQL> spool off
Version 10.2
----------------
In addition to the standard explain plan option, you can pull execution plans from the library cache if the SQL has already been executed.
a) To get the plan of the last executed SQL issue the following:
SQL> set linesize 150
SQL> set pagesize 2000
SQL> select * from table(dbms_xplan.display_cursor(null,null,
'ALL'));
b) If you know the hash value of the SQL, you can use
dbms_xplan.display_cursor as follows:
SQL> set linesize 150
SQL> set pagesize 2000
SQL> select * from TABLE(dbms_xplan.display_cursor('&SQL_ID',
&CHILD));
c) We can also get run time statistics with some additional
options and if we use the gather_plan_statistics hint.
e.g:
SQL> select /*+ gather_plan_statistics */ col1, col2 etc.....
SQL> set linesize 150
SQL> set pagesize 2000
SQL> select * from TABLE(dbms_xplan.display_cursor('&SQL_ID',
&CHILD,'ALL IOSTATS LAST'))
sql_id: specifies the sql_id value for a specific SQL statement, as shown in V$SQL.SQL_ID, V$SESSION.SQL_ID or V$SESSION.PREV_SQL_ID. If no sql_id is specified, the last executed statement of the current session is shown.

cursor_child_no: specifies the child number for a specific sql cursor, as in V$SQL.CHILD_NUMBER or in V$SESSION.SQL_CHILD_NUMBER, V$SESSION.PREV_CHILD_NUMBER. If not specified, all child cursors for the specified sql_id are displayed.
Note that to use the DISPLAY_CURSOR functionality, the calling user must have SELECT privilege on V$SQL_PLAN_STATISTICS_ALL, V$SQL, and V$SQL_PLAN, otherwise it will show an appropriate error message.




orainventory.txt
===================
What is oraInventory ?
oraInventory is repository (directory) which store/records oracle software products & their oracle_homes location on a machine. This Inventory now a days in XML format and called as XML Inventory where as in past it used to be in binary format & called as binary Inventory.
There are basically two kind of Inventory Global Inventory (also called as Central Inventory) and Local Inventory also called as Oracle Home Inventory.

Global Inventory ?
Global Inventory holds information about Oracle Products on a Machine. These products can be various oracle components like database, oracle application server, collaboration suite, soa suite, forms & reports or discoverer server . This global Inventory location will be determined by file oraInst.loc in /etc (on Linux) or /var/opt/oracle (solaris). If you want to see list of oracle products on machine check for file inventory.xml under ContentsXML in oraInventory (Please note if you have multiple global Inventory on machine check all oraInventory directories)

You will see entry like
HOME NAME="ORA10g_HOME" LOC="/u01/oracle/10.2.0/db" TYPE="O" IDX="1"/
...
...

Local Inventory
Inventory inside each Oracle Home is called as local Inventory or oracle_home Inventory. This Inventory holds information to that oracle_home only.

Can I have multiple Global Inventory on a machine ?
Quite common questions is that can you have multiple global Inventory and answer is YES you can have multiple global Inventory but if your upgrading or applying patch then change Inventory Pointer oraInst.loc to respective location. If you are following single global Inventory and if you wish to uninstall any software then remove it from Global Inventory as well.

What to do if my Global Inventory is corrupted ?
No need to worry if your global Inventory is corrupted, you can recreate global Inventory on machine using Universal Installer and attach already Installed oracle home by option
-attachHome

./runInstaller -silent -attachHome -invPtrLoc $location_to_oraInst.loc
ORACLE_HOME="Oracle_Home_Location" ORACLE_HOME_NAME="Oracle_Home_Name"
CLUSTER_NODES="{}"

Do I need to worry about oraInventory during oracle Apps 11i cloning ?
No, Rapid Clone will update both Global & Local Inventory with required information , you don't have to worry about Inventory during Oracle Apps 11i cloning.


patches applied on non shared appltop.txt
=========================================
select d.name, apps_system_name, f.bug_number, d.SERVER_TYPE_ADMIN_FLAG, d.SERVER_TYPE_FORMS_FLAG, d.SERVER_TYPE_NODE_FLAG,
d.SERVER_TYPE_WEB_FLAG from ad_patch_runs a, ad_patch_drivers b, ad_appl_tops d, AD_PATCH_RUN_BUGS e, ad_bugs f
where a.PATCH_DRIVER_ID = b.PATCH_DRIVER_ID
and f.bug_number in ('5479643','5483388','5486407','5500118','5518587','5521476','5571208','3748835')
and a.appl_top_id=d.appl_top_id
and a.PATCH_RUN_ID = e.PATCH_RUN_ID
and e.bug_id = f.bug_id
/



select a.name as "NODE NAME", b.driver_file_name as "FILE NAME",b.orig_patch_name as "PATCH NAME",b.platform as "PLATFORM", done.language as "LANGUAGE", c.success_flag as "SUCCESS",to_char(c.start_date,'dd-MON-yyyy hh24:mi:ss') as "START DATE",to_char(c.end_date,'dd-MON-yyyy hh24:mi:ss') as "END DATE" from ad_appl_tops a,ad_patch_drivers b,ad_patch_runs c,ad_patch_driver_langs done where b.patch_driver_id=c.patch_driver_id and c.appl_top_id=a.appl_top_id and done.patch_driver_id=c.patch_driver_id and b.orig_patch_name like '%4444880%' order by 1;



recovery using Transportable tablespaces.txt
============================================
here the target instance on which we are gong to do recovery is DFOMEI
and the instance from which are going to bring the data is DFOMEI
here the issue is '/dfomei/oradata/data01/a_nolog01.dbf' datafile has an issue with it nad we do not have any copy of it from theDFOMEI instance to recoverer.
so we shall be dropping the tablespace on the DEV instance and we shall be exporting the corr tablespace from the Test instance(using transportable tablespace) and we shall be then importing to the Dev instance again


Check on the Test instance for Integrity and constarints voilation
=============================================
SQL>EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('APPS_TS_NOLOGGING', TRUE);
PL/SQL procedure successfully completed.
SQL> SELECT * FROM transport_set_violations;
VIOLATIONS
--------------------------------------------------------------------------------
Constraint PAY_TEMP_BALANCE_ADJUST_FK1 between table HR.PAY_BALANCE_BATCH_LINES in tablespace APPS_TS_INTERFACE and table HR.PAY_TEMP_BALANCE_ADJUSTMENTS in tab lespace APPS_TS_NOLOGGING
Constraint SYS_C0030620 between table HR.PAY_BALANCE_BATCH_LINES in tablespace A PPS_TS_INTERFACE and table HR.PAY_TEMP_BALANCE_ADJUSTMENTS in tablespace APPS_TS _NOLOGGING
even when 2 violations are shown, there will be not problem since there is not data.
SQL> select count(*) from HR.PAY_BALANCE_BATCH_LINES;
COUNT(*)
----------
0
SQL> select count(*) from HR.PAY_BALANCE_BATCH_LINES;
COUNT(*)
----------
0
SQL> select owner, CONSTRAINT_TYPE, TABLE_NAME, R_CONSTRAINT_NAME, STATUS
2 from dba_constraints
3 where CONSTRAINT_NAME = '&2';
Enter value for 2: PAY_TEMP_BALANCE_ADJUST_FK1
old 3: where CONSTRAINT_NAME = '&2'
new 3:
where CONSTRAINT_NAME = 'PAY_TEMP_BALANCE_ADJUST_FK1'
OWNER C TABLE_NAME R_CONSTRAINT_NAME
---------- - ------------------------------ ------------------------------
STATUS
--------
HR R PAY_TEMP_BALANCE_ADJUSTMENTS PAY_BALANCE_BATCH_LINES_PK ENABLED
the above constraints needs to be disable to avoid the violations mentioned.
SQL> / Enter value for 2: SYS_C0030620 old 3: where CONSTRAINT_NAME = '&2' new 3: where CONSTRAINT_NAME = 'SYS_C0030620'
OWNER C TABLE_NAME R_CONSTRAINT_NAME
---------- - ------------------------------ ------------------------------
STATUS
--------
HR C PAY_TEMP_BALANCE_ADJUSTMENTS ENABLED



On DFOMEI:
1-. Take a cold backup of DFOMEI.
2. Offline drop the datafile. SQL> ALTER DATABASE DATAFILE '/dfomei/oradata/data01/a_nolog01.dbf' OFFLINE DROP;
3. If the database is at mount point, open it. SQL> ALTER DATABASE OPEN;
4. Drop the user tablespace. SQL> DROP TABLESPACE APPS_TS_NOLOGGING INCLUDING CONTENTS;

ON TFOMEI
------------------

5-. Disable Following constraints:
SQL> alter table HR.PAY_TEMP_BALANCE_ADJUSTMENTS disable constraints PAY_TEMP_BALANCE_ADJUST_FK1;
Table altered.
SQL> alter table HR.PAY_TEMP_BALANCE_ADJUSTMENTS disable constraints SYS_C0030620;
Table altered.
and run SQL> EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('APPS_TS_NOLOGGING', TRUE);
SQL> PL/SQL procedure successfully completed.
SQL> SELECT * FROM transport_set_violations;
SQL>
SQL> no rows selected make sure no rows selected.
6. Tablespaces. ------------
a. The tablespaces need to be in READ ONLY mode in order to successfully run a transport tablespace export.
SQL> ALTER TABLESPACE APPS_TS_NOLOGGING READ ONLY;
Export APPS_TS_NOLOGGING TS using the exp command. exp "'/ as sysdba'" file=exp_tts.dmp log=exp_tts.log transport_tablespace=y \ tablespaces=APPS_TS_NOLOGGING

On DFOMEI:
Copy the datafiles with the desired name in the desired directory.
from TFOMEI Server: auohsfome01 to DFOMEI Server: auohsfome01
cp /tfomei/oradata/data01/a_nolog01.dbf /dfomei/oradata/data01/a_nolog01.dbf -- Transfer the export dumpfile and all datafiles of the tranportable -- tablespace set in binary mode to the target $ORACLE_HOME (TFOMEI)
-- Copy the datafiles with the desired name in the desired directory. imp "'/ as sysdba'" file=exp_tts.dmp log=imp_tts.log \ transport_tablespace=y datafiles=/dfomei/oradata/data01/a_nolog01.dbf

TFOMEI
-----------
7. After the transport tablespace export and the copy of the datafiles, and after the plug-in of the tablespaces during the import),
the tablespaces can be changed back to READ WRITE mode.
SQL> ALTER TABLESPACE APPS_TS_NOLOGGING READ WRITE;
8. Enable constraints.
SQL> alter table HR.PAY_TEMP_BALANCE_ADJUSTMENTS enable constraints PAY_TEMP_BALANCE_ADJUST_FK1;
SQL> alter table HR.PAY_TEMP_BALANCE_ADJUSTMENTS enable constraints SYS_C0030620;

DFOMEI
-----------
9. After the transport tablespace export and the copy of the datafiles, and after the plug-in of the tablespaces during the import), the tablespaces can be changed back to READ WRITE mode.
SQL> ALTER TABLESPACE APPS_TS_NOLOGGING READ WRITE;



recreating con manager views.txt
====================================
goal: Re-create Concurrent Manager Views
fact: Oracle Application Object Library



fix:

For UNIX & Windows NT servers enter from the command prompt:

FNDLIBR FND FNDCPBWV apps/ SYSADMIN 'System Administrator'
SYSADMIN


For VMS servers enter from the command prompt:

FNDLIBR "FND" "FNDCPBWV" apps/ "
SYSADMIN" "System Administrator" "SYSADMIN"



relink full db and apps.txt
===========================
Technology Stack Binaries inclusive Database :

1. Source the 9i ORACLE_HOME Environment
2. Execute the script $ORACLE_HOME/appsutil/install/adlnkoh.sh
3. Source the iAS ORACLE_HOME Envrionment
4. Execute the script $ORACLE_HOME/bin/adlnkiAS.sh
5. Source the APPS Environment
6. Execute the script $ORACLE_HOME/bin/adlnk806.sh

Oracle E-Business-Suite Binaries :

1. Source the APPS Environment
2. Execute following command : adrelink.sh force=y "ad adadmin"
3. Execute adadmin
select --> '2. Maintain Applications Files menu'
select --> '1. Relink Applications programs'
--> select all modules to relink




responsibility User Management not visible.txt
============================================
After assigning the responsibility User Management or Approval Management to a user, the user
cannot use the same because there is an application error preventing so.


The issue is caused by the following setup: no grants were activated.

This is a requirement for use of User Management.


.
Solution
To implement the solution, please execute the following steps:

1. Change the setup for the associated user adding the Security Administrator Role to
them using the system administrator responsibility.

Using the system administrator responsibility, add the Security Administrator Role to the
intended user. The Security Administrator Role is a role available within User Management.

2. Also add the Functional Administrator administrator responsibility as you must assign
the functional and security administrator and then you should have menu choices.
Using the Functional Administrator responsibility:

a. Chose Core Services tab then Menus
b. Under Navigation Menus
c. Select Menu
d. Click Update
e. -= Menu Manager =-
f. Check "Grants" to activate

3. Please retest the issue.

4. If the issue is resolved, please migrate the solution as appropriate to other environments.



restart con mnager in unix.txt
=================================
TIP: Click help for a detailed explanation of this page.
Bookmark Go to End

Subject: How to Restart The Concurrent Manager in Unix.
Doc ID: Note:147449.1 Type: HOWTO
Last Revision Date: 21-NOV-2007 Status: PUBLISHED

Checked for relevance on 21-Nov-2007
Applications Install 11.0.3 to 11.5.10


goal: How to restart the concurrent manager in Unix
goal: How to bounce the concurrent manager in Unix
fact: Oracle Application Object Library



fix:

1. At the command line issue the following shutdown command:

CONCSUB apps/[APPS password] sysadmin 'System Administrator' SYSADMIN
CONCURRENT FND SHUTDOWN

Note: There are 3 options which can be used for controlling the Internal
Manager from the OS as follows:

SHUTDOWN = Standard, non-invasive shutdown.

DEACTIVATE = Will not allow further Requests to Process once Issued.

ABORT = Terminate Requests and Deactivate the Internal Manager.

2. At the command line, issue the following Start Up command:

startmgr sysmgr="[fnd_usernamd/fnd_password]" mgrname="[mgrname]" printer="[printername]"
mailto="[userid userid2...]" restart="[N|minutes]" logfile="[log_file_name]" sleep="
[new_check]" pmon="[manager_check]" quesiz="[number_check]" diag="[Y|N]"

Ex: startmgr sysmgr="applsys/apps" mgrname="std" printer="localprint"
mailto="jdoe jsmith" restart="N" logfile="mgrlog" sleep="90"
pmon="5" quesiz="10" diag="N"

Note: a space is required between arguments and the "diag" parameter
may be omitted if desired.




running Autoconfig manually.txt
================================
run adchkcfg.sh and the outputs will be generated at location " /ptagpi/applmgr/1158/admin/PTAGPI_auohstagp03/out/11260738/"
This location will have files in the form which reflects in case the autoconfig was run
So now manually copy the future files to the expected lcoations
cd /ptagpi/applmgr/1158/admin/PTAGPI_auohstagp03/out/11260738/
cp aplogon.html /ptagpi/applmgr/common/portal/PTAGPI_auohstagp03/
cp afadmprf.sql /ptagpi/applmgr/common/admin/install/PTAGPI_auohstagp03/
cp wdbsvr.app /ptagpi/product/iAS/Apache/modplsql/cfg/
cp zone.properties /ptagpi/product/iAS/Apache/Jserv/etc/



session timeout.txt
=====================

In order for session timeout to function properly, the session.timeout setting
in the zone.properties file must match the application profile option ICX: Session
Timeout. The session timeout value should not exceed 30 minutes. Values greater than
30 could result in JVM heap memory problems.

Submitting conc request from unix.txt
==========================================CONCSUB orauser/pwd RESP_APPL_SHRTNM responsibility USERNAME
[WAIT=check request status] CONCURRENT PROG_APPL_SHRTNM PROGRAM
[PROGRAM_NAME="program description"]
[REPEAT_DAYS=repeat interval]
[REPEAT_END="request re-submission end date"]
[START="date"]
[parameter 1] [parameter 2] . . .

Note: The tokens delimited by [ ] are optional.

Example
---------------
CONCSUB APPLSYS/FND SYSADMIN 'System Administrator' SYSADMIN WAIT=Y
CONCURRENT FND FNDMNRMT
START='"01-JAN-1999 23:00:00"' REPEAT_DAYS=1
REPEAT_END='"01-JAN-2001 23:59:00"' Y 0 0


supress apps password prompt.txt
=================================
Set profile option Utilities:Diagnostics to Yes in order to supress Apps password prompt.

surpass rapid install and directly reach login page.txt
=========================================================
1. Edit index.html and add at the top of the file :


content="0;url=http://./OA_HTML/AppsLocalLogin.jsp">
...


or
2. Add the following line in the url_fw.conf file
(should be under $IAS_CONFIG_HOME/Apache/Apache/conf directory):
RewriteRule ^/$ /OA_HTML/AppsLocalLogin.jsp [R,L]
then check that the following line exists in the httpd.conf file:
include "%s_iASconfig_home%/Apache/Apache/conf/url_fw.conf"
3. Bounce apache and clear browser caches


to delete wrongly named file.txt
================================

for i in `ls -q|grep F1`
do
rm $i
done


to use edit context.txt
=========================
To use the editcontext utility, execute the following commands:
$ export DISPLAY=myclient:0.0
$ cd $COMMON_TOP/util/editcontext
$ ./editcontext

when request in RUNNING + TERMINATING.txt
==========================================
If the request remains in a phase of RUNNING and a status of TERMINATING after allocating more processes to the manager, then shutdown the concurrent managers, kill any processes from the operating system that won't terminate, and execute the following sqlplus statement as the APPLSYS user to reset the managers in the FND_CONCURRENT_REQUESTS table:
update fnd_concurrent_requests
set status_code='X', phase_code='C'
where status_code='T';

Finding versions

Database version.txt
======================

SQL> select * from v$version;
The command returns the release information, such as the following:
Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production
PL/SQL Release 9.2.0.7.0 - Production
CORE 9.2.0.7.0 Production
TNS for 32-bit Windows: Version 9.2.0.7.0 - Production
NLSRTL Version 9.2.0.7.0 - Production

Discoverer Version.txt
========================
Discoverer with Apps installed in ORACLE_HOME same as 806 is usually 3i or 4i. To find Version login to Application Tier & go to $ORACLE_HOME/discwb4/bin and execute
strings dis4ws | grep -i 'discoverer version'
You should see output like
Discoverer Version:Session 4.1.47.09.00
Which means you are on discoverer 4i version 4.1.47.09


Forms Version in Apps from command Line.txt
==========================================
Enter "f60gen" on Forms Server and check for first line in output like
Forms 6.0 (Form Compiler) Version 6.0.8.26.0 (Production)
This confirms that you are on forms server version 6.0.8.26.0 and patch set 17. ( Patch Set = Fourth Digit - 9)



Forms Version.txt
==================
Login to forms from frontend , on top menu bar of forms click on "Help" & Select "About Oracle Applications" go to "Forms Server " section. You should see entry like below depending on your forms version
Oracle Forms Version : 6.0.8.26.0
Which mean you are on forms version 6.0.8.26 . If you want to know whats your forms patchset level then subtract 9 from fourth digit which means for above case form patchset 17 is applied.


JDK Installed.txt
=================
There might be multiple JDK installed on Operating System . Like JDK 1.3.1, 1.4.2 or 1.5 but in order to find which Version of JDK your Apps is using
Open your Context File $SID_$HOSTNAME.xml under $APPL_TOP/admin and look for variable
JDK_TOP oa_var="s_jdktop" what so ever value assigned against that parameter go to that directory & cd bin & execute command
./java -version so lets assume entry above is /usr/jdk then cd /usr/jdk/bin & ./java -version , you will see output like

java version "1.4.2_10"
Java(TM) 2 Runtime Environment, Standard Edition (build 1.4.2_10-b03)
Java HotSpot(TM) Client VM (build 1.4.2_10-b03, mixed mode)
Which means you are using JDK 1.4.2 in Oracle Applications 11i.

Jinitiator Version.txt
=======================

Check for file like appsweb_SID_HOSTNAME.cfg under $OA_HTML/bin defined by environment variable FORMS60_WEB_CONFIG_FILE & search for entry like jinit_ver_name , you will see entry like
jinit_ver_name=Version=1,3,1,23
which means Jinitiator version is 1.3.1.23 ; if your version is 1.3.1.18 you will see entry like 1,3,1,18


opatch Version.txt
=====================
opatch is utility to apply database patch , In order to find opatch version execute
"$ORACLE_HOME/OPatch/opatch version"
You should see output like OPatch Version: 1.0.0.0.52 which means your opatch version is 1.0.0.0.52


OUI version.txt
================
OUI stands for Oracle Universal Installer. In order to find Installer version you have to execute ./runInstaller -help ( From OUI location)
You will get output like
Oracle Universal Installer, Version 10.1.0.4.0 Production Copyright (C) 1999, 2005, Oracle. All rights reserved.
That means OUI version in above case is 10.1.0.4
OUI location is $ORACLE_HOME/oui/bin

Version of any file in Oracle Apps.txt
========================================
In Oracle Applications under ad utilities there is utility called as adident Used for Identification purpose or to find out file version use
adident Header
for ex. inorder to to find file version of one AR form i.e. ARXGLCOR.fmx
adident Header ARXGLCOR.fmx
You should see output like
$Header APPSTAND.fmb 115.33 2002/04/04 11:13:40 pkm ship
$ $Header ARXGLCOR.fmb 115.15 2005/01/31 13:48 mraymond ship
Which means above form executable consist of two forms whose version is 115.33 & 115.15 resp. Similarly you can use adident to find version of any report in 11i.


Version of Apps.txt
=====================
Run following SQL from apps user ;
SQL> select RELEASE_NAME from fnd_product_groups;
You should see output like
RELEASE_NAME

version of java class.txt
============================
ident $HEADER $COMMON_TOP/java/oracle/apps/jtf/base/session/ServletSessionManager.class

Web Server Version.txt
=======================
og to Application tier as Operating System Usually called as APPLMGR ; go to location $IAS_ORACLE_HOME/Apache/Apache/bin and run command
./httpd -version
You will see output like

Server version: Oracle HTTP Server Powered by Apache/1.3.19 (Unix)
Server built: Dec 6 2005 14:59:13 (iAS 1.0.2.2.2 rollup 5)
Which means you are on iAS Version 1.0.2.2.2 with patchset rollup 5 with Apache Version 1.3.19


Server version: Oracle-Application-Server-10g/10.1.2.0.2 Oracle-HTTP-Server

Above is output If you have installed 10g Application Server with 11i

Workflow Cartridge Release Version.txt
========================================
Log in to the database as the owf_mgr user and issue
select wf_core.translate('WF_VERSION') from dual;


Workflow Version embedded.txt
==============================
Run following SQL from apps user ;
SQL>select TEXT from WF_RESOURCES where NAME='WF_VERSION';

You should see output like
TEXT
-----------------------
2.6.0
Which means you are on Workflow Version 2.6.0


You can also use script wfver.sql in FND_TOP/sql to find version of workflow in Apps.