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;

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

Gather Schema Statistics - Oracle Applications

Gather  Schema  Statistics :

Gather Schema Statistics program generates statistics that quantify the data distribution and storage characteristics of tables, columns, indexes, and partitions.

The cost-based optimization (CBO)  uses these statistics to calculate the selectivity of prediction and to estimate the cost of each execution plan.

As a general rule, run Gather Schema Statistics under the following circumstances:

1.  After there has been a significant change in data in either content or volume.

2.  After importing data.

3.  Any time end-users notice deterioration in performance in routine day-to-day business transactions or when running concurrent programs.

4.  Run on a regular basis (weekly at a minimum) and anytime after application of patch, conversion, etc.

Estimate Percentage / Modification threshold defines the percentage which should be used to initiate gather stats for those objects which have actually changed beyond the threshold.

 The default is 10% (i.e. meaning any table which has changed via DML more than 10%, stats will be collected, otherwise it will be skipped).

How to run Gather Schema Statistics concurrent program:

1. Log on to Oracle Applications with

    Responsibility = System Administrator

2. Submit Request Window

    Navigate to: Concurrent > Requests

3. Query for the Gather Schema Statistics

4. Enter the appropriate parameters. This can be run for specific schemas by specifying the schema name or entering  ‘ALL’  to gather statistics for every schema in the database

5. Submit the Gather Schema Statistics program

Parameters :

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

Schema Name:  Schema for which statistics are to be gathered. Specify ALL for all Oracle Applications schemas

Percent:  The sampling percentage. If left blank, the default value of 10 is used. The valid range is from 0 to 100

Degree:  The degree of parallelism to be used for gathering statistics. If a Degree is not provided, it defaults to the minimum ofparallel_max_servers and cpu_count.

Backup Flag:  NOBACKUP is used, then the GATHER_SCHEMA_STATS procedure will not backup the current statistics. This way the GATHER_SCHEMA_STATS procedure will run faster.

Restart Request ID:  In the case where the Gather Schema Statistics run fails due to whatever reasons, the concurrent request can be re-submitted and it will pick up where the failed run left off, if you provide the concurrent request_id of the failed run.

History Mode:  Last Run – History records for each object are maintained only for the last gather statistics run. Each subsequent run will overwrite the previous history record for the object. This is the default behavior

Gather Options:  GATHER: All tables and indexes of the schema schema name are selected for stats gathering. This is the default

Modifications Threshold:  Applicable only to GATHER AUTO and LIST AUTO Options

Invalidate Dependent Cursors:  This flag indicates whether cursors dependent on the table being analyzed should be invalidated or not. By default, dependent cursors are invalidated.

How to Gather the Statistics of Custom Schema when we submit the concurrent request called Gather Schema Statistics :

When we submit Gather Schema Stats with Parameter  ALL, concurrent request will complete successfully, and DBAs will not realize that custom schemas are not analyzed.

SQL> exec DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'SYS',estimate_percent => 50,cascade => TRUE ,options => 'GATHER AUTO');

Sql > select count(table_name)  from  dba_tables  where  last_analyzed  is  not null  and  owner= <custom_schema_name>;

Here you realize none of the tables in custom schema are analyzed.

Gather Schema Statistics program gathers statistics for all schemas , however it skips custom schemas registered in Oracle Applications.

Reason:  

Whenever Custom schemas are registerd in Oracle Applications , the entries are done in 2 tables

ie  FND_ORACLE_USERID  and  FND_APPLICATIONS_TL
However , when Gather schema statistics is submitted it uses the below query to get schema information
Sql > select distinct upper(oracle_username) sname
          from fnd_oracle_userid a,
         fnd_product_installations b
         where a.oracle_id = b.oracle_id
         order by sname;
Note : When custom schemas are created the entry is not made in  FND_PRODUCT_INSTALLATIONS  and hence it is not picked up in the above query.
Solution :
How can we make an entry in fnd_product_installations so that it is picked up by Gather Schema Stats. Follow below steps
Go to the Responsibility called Alert Manager and Navigate to the form -> Installations under Systems Menu.
Define custom application in this form. Go to the last record and make entry for custom applications. Once this is done , it will insert an entry in fnd_product_installations.
Submit Gather Schema stats and then query dba_tables and you will realize , stats are being gathered for custom schemas as well.
******************
Check out MetaLink Docs for more information:
- 141532.1 “How to Gather Schema Statistics for Oracle Applications 11i”
- 163208.1 “bde_last_analyzed.sql - Verifies Statistics for all installed Apps modules 11.5”
- 156968.1 “coe_stats.sql - Automates CBO Stats Gathering using FND_STATS and Table sizes”

DataBase Info & Helth Check....!!

******Total Size of The Database******

select
( select sum(bytes)/1024/1024/1024 data_size from dba_data_files ) +
( select nvl(sum(bytes),0)/1024/1024/1024 temp_size from dba_temp_files ) +
( select sum(bytes)/1024/1024/1024 redo_size from sys.v_$log ) +
( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 controlfile_size from v$controlfile) "Size in GB"
from
dual ;

==============================================================
++++++++++++++++++++++++++++Total Size of The Database
col "Database Size" format a20
col "Free space" format a20
col "Used space" format a20
select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size"
, round(sum(used.bytes) / 1024 / 1024 / 1024 ) -
round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space"
, round(free.p / 1024 / 1024 / 1024) || ' GB' "Free space"
from (select bytes
from v$datafile
union all
select bytes
from v$tempfile
union all
select bytes
from v$log) used
, (select sum(bytes) as p
from dba_free_space) free
group by free.p
/
=========================================================================
+++++++++++++++++++++++++++++++largest objects in the database
SELECT * FROM ( select  SEGMENT_NAME, SEGMENT_TYPE, BYTES/1024/1024/1024 GB, TABLESPACE_NAME  from   dba_segments order by 3 desc  )  WHERE ROWNUM <= 10 ;

col owner format a15
col segment_name format a30
col segment_type format a15
col mb format 999,999,999
select owner
, segment_name
, segment_type
, mb
from (
select owner
, segment_name
, segment_type
, bytes / 1024 / 1024 "MB"
from dba_segments
order by bytes desc
)
where rownum < 11
/
=====================================
Show all connected users
set lines 100 pages 999
col ID format a15
select username
,      sid || ',' || serial# "ID"
,      status
,      last_call_et "Last Activity"
from   v$session
where  username is not null
order by status desc
,        last_call_et desc
/
============================
Time since last user activity
set lines 100 pages 999
select username
,      floor(last_call_et / 60) "Minutes"
,      status
from   v$session
where  username is not null
order by last_call_et
/
=======================
Sessions sorted by logon time
set lines 100 pages 999
col ID  format a15
col osuser format a15
col login_time format a14
select  username
, osuser
, sid || ',' || serial# "ID"
, status
, to_char(logon_time, 'hh24:mi dd/mm/yy') login_time
, last_call_et
from v$session
where username is not null
order by login_time
/
===========================
Show user info including os pid
col "SID/SERIAL" format a10
col username format a15
col osuser format a15
col program format a40
select s.sid || ',' || s.serial# "SID/SERIAL"
, s.username
, s.osuser
, p.spid "OS PID"
, s.program
from v$session s
, v$process p
Where s.paddr = p.addr
order  by to_number(p.spid)
/
============================
Show a users current sql
Select sql_text
from   v$sqlarea
where  (address, hash_value) in
(select sql_address, sql_hash_value
        from v$session
        where username like '&username')
/
==============================
Session status associated with the specified os process id
select s.username
, s.sid
, s.serial#
, p.spid
, last_call_et
, status
from  V$SESSION s
, V$PROCESS p
where s.PADDR = p.ADDR
and p.spid='&pid'
/
=====================================
All active sql
set feedback off
set serveroutput on size 9999
column username format a20
column sql_text format a55 word_wrapped
begin
  for x in
   (select username||'('||sid||','||serial#||') ospid = '|| process ||
    ' program = ' || program username,
    to_char(LOGON_TIME,' Day HH24:MI') logon_time,
    to_char(sysdate,' Day HH24:MI') current_time,
    sql_address,
    sql_hash_value
   from v$session
   where status = 'ACTIVE'
   and rawtohex(sql_address) <> '00'
   and username is not null ) loop
   for y in (select sql_text
   from v$sqlarea
   where address = x.sql_address ) loop
   if ( y.sql_text not like '%listener.get_cmd%' and
    y.sql_text not like '%RAWTOHEX(SQL_ADDRESS)%' ) then
    dbms_output.put_line( '--------------------' );
    dbms_output.put_line( x.username );
    dbms_output.put_line( x.logon_time || ' ' || x.current_time || ' SQL#=' || x.sql_hash_value);
    dbms_output.put_line( substr( y.sql_text, 1, 250 ) );
   end if;
  end loop;
 end loop;
end;
/
==============================
Display any long operations
set lines 100 pages 999
col username format a15
col message format a40
col remaining format 9999
select username
, to_char(start_time, 'hh24:mi:ss dd/mm/yy') started
, time_remaining remaining
, message
from v$session_longops
where time_remaining = 0
order by time_remaining desc
/
==============================
List open cursors per user
set pages 999
select  sess.username
, sess.sid
, sess.serial#
, stat.value cursors
from v$sesstat stat
, v$statname sn
, v$session sess
where sess.username is not null
and sess.sid = stat.sid
and stat.statistic# = sn.statistic#
and sn.name = 'opened cursors current'
order by value
/
(or)
set lines 100 pages 999
select count(hash_value) cursors
,      sid
,      user_name
from   v$open_cursor
group by
       sid
,      user_name
order by
       cursors
/
======================================

Customizing an AutoConfig Environment & Rolling back an AutoConfig session

1. $AD_TOP/bin/adtmplreport.sh contextfile=$CONTEXT_FILE target=Config file
Use above command to find out template for your configuration file.
2. Once you get the template eg : $AD_TOP/admin/template
create a "custom" directory in $AD_TOP/admin/template
copy  the template to custom directory
do your changes
3. Run autoconfig.
=====================================================================
Customizing an AutoConfig template file delivered by Oracle
If you want to customize an existing AutoConfig template file, perform the following steps (in the order listed):
Determine the AutoConfig template file you want to customize
Execute the following command to find out the corresponding AutoConfig template file for the configuration file you want to customize:  
 Application <AD_TOP>/bin/adtmplreport.sh contextfile=<CONTEXT> target=<configurationfile>
 Database <RDBMS ORACLE_HOME>/appsutil/bin/adtmplreport.sh contextfile=<CONTEXT> target=<configurationfile>
For example, if you want to customize

$COMMON_TOP/html/bin/appsweb_$CONTEXT_NAME.cfg, execute:
On UNIX
$AD_TOP/bin/adtmplreport.sh contextfile=$CONTEXT_FILE \
target=$COMMON_TOP/html/bin/appsweb_$CONTEXT_NAME.cfg
The adtmplreport utility returns the name and location of the AutoConfig template file. For the above UNIX example it would return:
$FND_TOP/admin/template/appsweb.cfg.
You cannot customize all AutoConfig template files. The AutoConfig template file cannot be customized if the "LOCK" keyword appears in the template file's entry in the product driver file. AutoConfig ignores custom template files that are marked with "LOCK". For example, the following entry in  AD_TOP/admin/driver/adtmpl.drv would prevent customization of the file adconfig.txt:
ad admin/template adconfig.txt INSTE8 <s_at>/admin adconfig.txt 600 LOCK

Create the custom template directory
Create a directory named "custom" at the location where the AutoConfig template file resides.
For example, if you want to customize <FND_TOP>/admin/template/appsweb.cfg, execute the following command as the applmgr user:

On UNIX
mkdir $FND_TOP/admin/template/custom

Copy the AutoConfig template file
Copy the AutoConfig template file to the custom template file.
Execute the following command as the applmgr user:

On UNIX
cp -i <AutoConfig template file> <custom template file>
For example:
cp -i $FND_TOP/admin/template/appsweb.cfg \
      $FND_TOP/admin/template/custom/appsweb.cfg


****************>Rolling back an AutoConfig session<******************

Each execution of AutoConfig creates a rollback script in case you need to revert to the previous configuration settings. The script and all backup configuration files from each AutoConfig session are stored in:
Tier  Directory 
Application <APPL_TOP>/admin/<CONTEXT_NAME>/out/<MMDDhhmm>
Database <RDBMS ORACLE_HOME>/appsutil/out/<CONTEXT_NAME>/<MMDDhhmm>

where:<MMDDhhmm> = (month, day, hour, minute of AutoConfig run)
To roll back an AutoConfig session:
On UNIX
restore.sh
-------
Customizing an AutoConfig Environment [ID 270519.1]
Note 461326.1 - 'Unable to Create a Custom Product TOP'
Using AutoConfig to Manage System Configurations with Oracle Applications 11i [ID 165195.1]
Oracle E-Business Suite Release 11i with 9i RAC: Installation and Configuration using AutoConfig [ID 279956.1]
----------This is one of the best prectess---------