Queries for Applications

To Know the definition of a DDL Commands.
=========================================
select  dbms_metadata.get_ddl ('TABLE', 'TABLE_NAME', 'SCHEMA_NAME') from dual

Tablspace Size Info
==================

select a.TABLESPACE_NAME, a.MAX, b.USED,
round((b.USED/a.MAX)*100,2) PCT  from
(select TABLESPACE_NAME, sum(MAXBYTES/1024/1024)
MAX from dba_Data_files group by TABLESPACE_NAME) a,(select tablespace_name, sum(bytes/1024/1024) USED from dba_segments group by tablespace_name) b where a.TABLESPACE_NAME=b.TABLESPACE_NAME and b.USED > a.MAX *(60/100)
===========================================================================
USER RESPONSIBILITY

select t1.user_name, t1.end_date, t3.responsibility_name, t2.creation_date
from fnd_user t1, fnd_user_resp_groups t2, fnd_responsibility_vl t3
where t1.user_id = t2.user_id
and t2.responsibility_id = t3.responsibility_id
and t3.responsibility_name like '&responsibility_name';
===========================================================================

To Find a Provile Value
=======================
SELECT PROFILE_OPTION_NAME, PROFILE_OPTION_VALUE FROM APPS.FND_PROFILE_OPTIONS
A,APPS.FND_PROFILE_OPTION_VALUES B WHERE A.PROFILE_OPTION_ID=B.PROFILE_OPTION_ID AND
PROFILE_OPTION_NAME LIKE 'SITENAME';
=============================================================================
Find a SQL Query by using SID
============================

select s.username, s.status,s.SQL_ADDRESS,t.SQL_TEXT,s.last_call_et/3600
 from v$session s,v$sqltext t
 where t.ADDRESS=s.SQL_ADDRESS and s.sid=&sid order by piece;
===================================================================
To See the profile values for a particular USER
===============================================
select
n.profile_option_name,
n.user_profile_option_name NAME,
decode(v.level_id,
10001, 'Site',
10002, 'Application',
10003, 'Responsibility',
10004, 'User',
10005, 'Server',
10006, 'Org',
10007, 'SERVRESP',
'UnDef') LEVEL_SET,
decode(to_char(v.level_id),
'10001', '',
'10002', app.application_short_name,
'10003', rsp.responsibility_key,
'10004', usr.user_name,
'10005', svr.node_name,
'10006', org.name,
'10007', 'Serv/resp',
'UnDef') "CONTEXT",
v.profile_option_value VALUE,
usr.user_name
from fnd_profile_options p,
fnd_profile_option_values v,
fnd_profile_options_tl n,
fnd_user usr,
fnd_application app,
fnd_responsibility rsp,
fnd_nodes svr,
hr_operating_units org
where p.profile_option_id = v.profile_option_id (+)
and p.profile_option_name = n.profile_option_name
and usr.user_name in ('&UserName')
and usr.user_id (+) = v.level_value
and rsp.application_id (+) = v.level_value_application_id
and rsp.responsibility_id (+) = v.level_value
and app.application_id (+) = v.level_value
and svr.node_id (+) = v.level_value
and org.organization_id (+) = v.level_value
order by 1,2,3,6;
=====================================================
To Update any profile Values
=====================================================
update /*+parallel (a,8) */ fnd_profile_option_values a
2 set a.profile_option_value= replace(a.profile_option_value,'appsnidcprod-nmcmotor.oracle.com','appsnidcprod.nmcmotor.com')
3 where upper(a.profile_option_value) like upper('%appsnidcprod-nmcmotor.oracle.com%');

========================================
select dbms_metadata.get_ddl('TABLE','FND_LOGINS','APPLSYS') from dual;
select 'alter system kill session ''' || sid||','||serial#||''' IMMEDIATE ' || ';' from v$session where status ='INACTIVE';
select 'alter PACKAGE ''' || OWNER||'.'|| OBJECT_NAME|| '''  '  ||  'compile body ;' from dba_objects where status ='INVALID';
select to_char(STARTUP_TIME,'dd-mon-yyyy hh24:mi:ss') from v$instance;
================================================

PCP CM CHECKS
=============
select distinct node_name,node_name2 from fnd_concurrent_queues;
SELECT COUNT(*) FROM WF_NOTIFICATIONS WHERE MAIL_STATUS IN ('MAIL', 'INVALID', 'OPEN');
Select running_processes,max_processes from apps.fnd_concurrent_queues where running_processes > 0 or max_processes > 0;
select * from fnd_profile_option_values where PROFILE_OPTION_ID=125;

select t.PROFILE_OPTION_ID , z.USER_PROFILE_OPTION_NAME , v.PROFILE_OPTION_VALUE, v.level_id
  from fnd_profile_options t, fnd_profile_option_values v, fnd_profile_options_tl z
  where v.PROFILE_OPTION_ID = t.PROFILE_OPTION_ID
  and z.PROFILE_OPTION_NAME = t.PROFILE_OPTION_NAME
  and t.PROFILE_OPTION_NAME in ('WF_MAIL_WEB_AGENT') ;