Script to check the database size

SQL> select (a.data_size+b.temp_size+c.redo_size+d.controlfile_size)/1024 "total_size in GB"

from ( select sum(bytes)/1024/1024 data_size from dba_data_files) a,

( select nvl(sum(bytes),0)/1024/1024 temp_size from dba_temp_files ) b,

(select sum(bytes)/1024/1024 redo_size from sys.v_$log ) c,

( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024 controlfile_size from v$controlfile) d;

total_size in GB

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

      133.515869

Script to find the Workflow and Service component status

set linesize 150

col Component format a40

set pagesize 9999

col COMPONENT_NAME format a50

col COMPONENT_STATUS format a50

col Component format a40

select SC.COMPONENT_TYPE, SC.COMPONENT_NAME, FND_SVC_COMPONENT.Get_Component_Status(SC.COMPONENT_NAME) COMPONENT_STATUS from FND_SVC_COMPONENTS SC order by 1, 2;

For Specific Component status.

     SELECT component_name as Component, component_status as Status          FROM fnd_svc_components WHERE component_type = 'WF_MAILER';

Script to find the tablespace usage

select df.tablespace_name "Tablespace",
 totalusedspace "Used MB",
 (df.totalspace - tu.totalusedspace) "Free MB",
 df.totalspace "Total MB",
 round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace))
 "Pct. Free"
 from
 (select tablespace_name,
 round(sum(bytes) / 1048576) TotalSpace
 from dba_data_files
 group by tablespace_name) df,
 (select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name
 from dba_segments
 group by tablespace_name) tu
 where df.tablespace_name = tu.tablespace_name ;