Oracle Applications Database frequently used scripts

FIND DATABASE SIZE
****************************

select 'DATA_n_INDEX: ' || sum(bytes)/1024/1024/1024 || ' GBytes' DATABASE_SIZE from dba_data_files
union
select 'TEMP: ' || sum(bytes)/1024/1024/1024 || ' GBytes' from dba_temp_files
union
select 'REDO LOGS: ' || sum(bytes)/1024/1024/1024 || ' GBytes' from v$log
union
select 'CONTROLFILE: ' || sum(FILE_SIZE_BLKS*BLOCK_SIZE)/1024/1024 || ' MBytes' from v$controlfile;


CREATE NEW DATABASE USER
*****************************************

create user <username> identified by <password> default tablespace <tablespace_name> temporary tablespace <tablespace_name> profile <profile_name>;

grant create session to <username>;


alter user <username> password expire;

CREATE DYNAMIC SQL SCRIPTS TO COMPILE INVALID OBJECTS
**********************************************************************************

spool recompile_list.sql;
select 'ALTER '||
decode(substr(object_type,1,4),'PACK','PACKAGE ',object_Type||' ')||
owner || '.' || decode(object_type,'JAVA CLASS','"') || object_name ||
decode(object_type,'JAVA CLASS','" RESOLVE ','INDEX','REBUILD',' COMPILE ') ||
DECODE(object_type,'PACKAGE BODY','BODY', NULL)||';' OBJECTS_TO_COMPILE
from dba_objects
where object_type in ('PACKAGE','PROCEDURE','PACKAGE BODY','FUNCTION',
'TRIGGER','VIEW','JAVA CLASS','INDEX')
and status = 'INVALID'
order by object_name, object_type;

spool off;

FIND ALL ACTIVE DATABASE SESSIONS
****************************************************

select event, program, module, sid, SECONDS_IN_WAIT from v$session where status = 'ACTIVE'
and module is not null order by 2 desc;

OR

select sid, serial#, username, osuser, logon_time, program, module 

from v$session where username is not null and status = 'ACTIVE';

FIND EBS USER NAME FROM DATABASE SESSION ID
********************************************************************

column "User Name" format a10
column "OS_PID" format a8

select d.user_name "User Name", 
b.sid SID,b.serial# "Serial#", c.spid "srvPID", a.SPID "OS_PID",
to_char(START_TIME,'DD-MON-YY HH:MM:SS') "STime"
from fnd_logins a, v$session b, v$process c, fnd_user d
where b.paddr = c.addr
and a.pid=c.pid
and a.spid = b.process
and d.user_id = a.user_id
and (d.user_name = 'USER_NAME' OR 1=1)

and b.sid = &sid;

FIND ORPHAN PROCESSES
***********************************

select spid from v$process where not exists (select 1 from v$session where paddr=addr);

FIND DETAILS WHEN CONCURRENT PROGRAM WAS RUN IN LAST 10 DAYS
***********************************************************************************************

select REQUEST_ID,
REQUEST_DATE,
REQUESTED_START_DATE,
RESUBMIT_END_DATE,
ACTUAL_START_DATE,
ACTUAL_COMPLETION_DATE
from fnd_concurrent_requests
where CONCURRENT_PROGRAM_ID = &Concurrent_Prog_ID
and ACTUAL_START_DATE between trunc(sysdate) - 10 and sysdate

order by REQUESTED_START_DATE;

FIND ALL FORMS RELATED SESSIONS
*************************************************

col CLIENT_IDENTIFIER format a10
col MODULE format a25
col MACHINE format a10

select sid, serial#, logon_time, client_identifier, module, status, machine, seconds_in_wait
from gv$session
where program like 'frmweb%'

order by logon_time;

FIND USER_ID FROM USER_NAME
********************************************

select USER_ID, USER_NAME
from apps.FND_USER

where USER_NAME = upper('&1');