Querys for TableSpace info & Temp Usage

+++++++++++++++To know Tablespace Strecture on backend ++++++++++++++

SELECT dbms_metadata.get_ddl('TABLESPACE',tablespace_name )
FROM dba_tablespaces
WHERE tablespace_name IN ( '&your_tablespace' );

@@@@@@@@@---To Find Temp Usage on particular User---@@@@@@@@@
set pages 999 lines 100
col username format a15
col mb format 999,999
select  su.username
,       ses.sid
,       ses.serial#
,       su.tablespace
,       ceil((su.blocks * dt.block_size) / 1048576) MB
from    v$sort_usage    su
,       dba_tablespaces dt
,       v$session ses
where   su.tablespace = dt.tablespace_name
and     su.session_addr = ses.saddr
/

+++++++++++
 SQL> select tablespace_name, file_id, bytes_used, bytes_free  from v$temp_space_header ;
SQL> select tablespace_name,extent_size,total_extents,total_blocks,max_size,max_blocks from v$sort_segment;
SQL>  select tablespace_name, file_id, bytes_used, bytes_free  from v$temp_space_header;
*******************To Know Application Version:**************************

SQL> select product_group_id, product_group_name, release_name,
        product_group_type, argument1
  from fnd_product_groups;

+++++++++++++& Product Version &++++++++++++++++++

select decode(nvl(a.APPLICATION_short_name,'Not Found'),

        'SQLAP','AP','SQLGL','GL','OFA','FA',

        'Not Found','id '||to_char(fpi.application_id),

        a.APPLICATION_short_name) apps,

        decode(fpi.status,'I','Installed','S','Shared',

               'N','Inactive',fpi.status) status,

        fpi.product_version,

        nvl(fpi.patch_level,'-- Not Available --') Patchset,

        to_char(fpi.last_update_date,'dd-Mon-RRRR') "Update Date"

from fnd_oracle_userid o, fnd_application a, fnd_product_installations fpi

where fpi.application_id = a.application_id(+)

  and fpi.oracle_id = o.oracle_id(+)

order by 1,2

/

(OR)

SQL> SELECT TABLESPACE_NAME TBSP_NAME, USED_SPACE, TABLESPACE_SIZE TBSP_SIZE, USED_PERCENT FROM SYS.DBA_TABLESPACE_USAGE_METRICS;

--------------------To Find TableSpace Usage------------------------

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

*********Undo TableSpace :

SQL> select a.sid, a.username, b.xidusn, b.used_urec, b.used_ublk from v$session a, v$transaction b where a.saddr=b.ses_addr;

==================To know Workflow Info===================
wfver.sql script
• Required Init Parameters
• Files and Packages versions
• Queues status
• Workflow Services and Agent Listeners status
SQL>spool wfver.lst
SQL>@$FND_TOP/sql/wfver.sql
…
SQL>spool off

==================================================
++++++++Workflow

column corrid format A50
column state format A15
select NVL(substr(wfe.corrid,1,50),'NULL - No Value') corrid,
decode(wfe.state,0,' 0 = Ready',1,'1 = Delayed',2,'2 = Retained',
3,'3 = Exception',to_char(substr(wfe.state,1,12))) State,
count(*) COUNT from applsys.wf_deferred wfe group by wfe.corrid, wfe.state;

=============================================================

To Analyze the DISK I/o's :

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

prompt SESSIONS PERFORMING HIGH I/O > 50000
select p.spid, s.sid,s.process cli_process, s.status,t.disk_reads, s.last_call_et/3600 last_call_et_Hrs,
s.action,s.program,lpad(t.sql_text,30) "Last SQL"
from v$session s, v$sqlarea t,v$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
p.addr=s.paddr and
t.disk_reads > 10000
order by t.disk_reads desc;

----------->>>>>>>>>>>><<<<<<<<<<<<----------------