SWEEP_TRX_QUERY

SELECT   distinct  hou.name,
                  a.org_id,
                  a.invoice_id,
                  a.INVOICE_NUM,
                  a.PAYMENT_CURRENCY_CODE,
                  a.AMOUNT_PAID,
                  pov.VENDOR_NAME,
                  a.INVOICE_TYPE_LOOKUP_CODE,
                  a.DOC_SEQUENCE_VALUE,
                  a.VOUCHER_NUM,
                  a.POSTING_STATUS,
                  a.PAYMENT_STATUS_FLAG,
                  a.SOURCE,
                  pov.SEGMENT1 VENDOR_NUM,
                  a.INVOICE_DATE,
                  a.INVOICE_AMOUNT,
                  a.gl_date
                  --,b.ACCOUNTING_DATE
  FROM   ap_invoices_all a,
        -- ap_invoice_distributions_all b,
         po_vendors pov,
         hr_operating_units hou
 WHERE   a.vendor_id = pov.vendor_id    
         --AND a.INVOICE_ID = b.INVOICE_ID        
         AND a.org_id = hou.organization_id
         AND exists (  select 1 from ap_invoice_distributions_all B
                    WHERE b.ACCOUNTING_DATE BETWEEN '01-APR-2011' AND '13-AUG-2011'
                    AND   UPPER(b.LINE_TYPE_LOOKUP_CODe) NOT LIKE '%PRE%PAY%'
                    and   b.invoice_id = a.invoice_id
                    )
--                    AND  a.gl_date <= '31-MAR-2011'
         AND a.SET_OF_BOOKS_ID = 5

---------------------   THANK TO MANOJ SIR  ---------------------------

AR INVOICE QUERY

exec mo_global.init('AR');
/* Formatted on 2011/12/26 23:23 (Formatter Plus v4.8.8) */
SELECT rct.org_id, hou.NAME ou_name, rct.customer_trx_id, rct.trx_number,
       rct.old_trx_number, TRUNC (rct.trx_date) trx_date, ctl.line_number,
       ctl.customer_trx_line_id, lgd.amount, lgd.account_class,
       lgd.acctd_amount, al_class.meaning, lgd.al_class_meaning, gcc.concatenated_segments,
       ffv.description acct_desc, rctt.TYPE, rctt.NAME trx_type_name,
       rctt.description, rctt.post_to_gl, rctt.accounting_affect_flag,
       lgd.gl_date, rct.complete_flag, rctt.status, gcc.segment3 account_no,
       ffv.description account_des, rctt.end_date
  FROM ra_cust_trx_line_gl_dist_v lgd,
       gl_code_combinations_kfv gcc,
       fnd_flex_values_vl ffv,
       ra_customer_trx_all rct,
       apps.ra_cust_trx_types_all rctt,
       hr_operating_units hou,
       ra_customer_trx_lines_all ctl,
       ra_customer_trx_lines_all ctl_line,
       ar_lookups al_class
 WHERE lgd.code_combination_id = gcc.code_combination_id
   AND ffv.flex_value = gcc.segment3
   AND rct.customer_trx_id = lgd.customer_trx_id
   AND rctt.cust_trx_type_id(+) = rct.cust_trx_type_id
   AND hou.organization_id(+) = rct.org_id
   AND lgd.customer_trx_line_id = ctl.customer_trx_line_id(+)
   AND ctl.link_to_cust_trx_line_id = ctl_line.customer_trx_line_id(+)
   AND al_class.lookup_type = 'AUTOGL_TYPE'
     AND al_class.lookup_code = lgd.account_class
   AND TRUNC (rct.trx_date) BETWEEN :p_from_date AND :p_to_date
   --AND rct.customer_trx_id = 1082
   order by rct.org_id, hou.NAME , rct.customer_trx_ID


--------------------------  NEXT ------------------------------


/* Formatted on 12/26/2011 9:20:35 PM (QP5 v5.115.810.9015) */
  SELECT   rct.org_id,
           hou.NAME ou_name,
           rct.customer_trx_id,
           rct.trx_number,
           rct.old_trx_number,
           TRUNC (rct.trx_date) trx_date,
           ctl.line_number,
           ctl.customer_trx_line_id,
           lgd.amount,
           lgd.ACCOUNT_CLASS,
           lgd.acctd_amount,
           --         lgdv.AL_CLASS_MEANING,
           gcc.concatenated_segments,
           ffv.description acct_desc,
           --         rctt.TYPE,
           --         rctt.NAME trx_type_name,
           --         rctt.description,
           rctt.post_to_gl,
           rctt.accounting_affect_flag,
           lgd.GL_DATE,
           rct.COMPLETE_FLAG,
           rctt.status,
           gcc.SEGMENT3 ACCOUNT_NO,
           ffv.DESCRIPTION ACCOUNT_DES,
           rctt.end_date,
           AL_CLASS.MEANING,
            AL_TYPE.MEANING
    FROM   ra_cust_trx_line_gl_dist_all lgd,
           ra_customer_trx_lines_all ctl,
           ra_customer_trx_all rct,
           hr_operating_units hou,
           gl_code_combinations_kfv gcc,
           apps.ra_cust_trx_types_all rctt,
           fnd_flex_values_vl ffv,
           RA_CUSTOMER_TRX_LINES CTL_LINE,
           AR_LOOKUPS AL_CLASS,
           AR_LOOKUPS AL_TYPE,
           RA_RULES RR
   WHERE       lgd.customer_trx_line_id = ctl.customer_trx_line_id(+)
           AND rct.customer_trx_id(+) = ctl.customer_trx_id
           AND hou.organization_id(+) = rct.org_id
           AND gcc.code_combination_id = lgd.code_combination_id
           AND rctt.cust_trx_type_id(+) = rct.cust_trx_type_id
           AND CTL.LINK_TO_CUST_TRX_LINE_ID = CTL_LINE.CUSTOMER_TRX_LINE_ID(+)
           AND AL_CLASS.LOOKUP_TYPE = 'AUTOGL_TYPE'
           AND AL_CLASS.LOOKUP_CODE = LGD.ACCOUNT_CLASS
           AND AL_TYPE.LOOKUP_TYPE(+) = 'STD_LINE_TYPE'
           AND AL_TYPE.LOOKUP_CODE(+) = CTL.LINE_TYPE
           AND CTL.ACCOUNTING_RULE_ID = RR.RULE_ID(+)
           AND ffv.flex_value = gcc.segment3
           AND TRUNC (rct.trx_date) BETWEEN :p_from_date AND :p_to_date
ORDER BY   rct.org_id, hou.NAME,rct.trx_number

CCR_PAYMANT_QUERY

SELECT VENDOR_TYPE_LOOKUP_CODE
,       ORG_ID
,       VENDOR_NUM
,       VENDOR_NAME
,       VENDOR_SITE_CODE
,       CODE_COMBINATION
,       NVL(SUM(DECODE(INVOICE_TYPE_LOOKUP_CODE,'CREDIT',INVOICE_AMOUNT+(PAID_AMOUNT+ADUSTED_AMOUNT),'DEBIT',INVOICE_AMOUNT+(PAID_AMOUNT+ADUSTED_AMOUNT),'STANDARD',INVOICE_AMOUNT+(PAID_AMOUNT+ADUSTED_AMOUNT))),0)+
        NVL(SUM(DECODE(INVOICE_TYPE_LOOKUP_CODE,'PREPAYMENT',(INVOICE_AMOUNT+(PAID_AMOUNT+ADJUSTED_AMOUNT)))),0) "AMOUNT"
,       NVL(SUM(DECODE(INVOICE_TYPE_LOOKUP_CODE,'CREDIT',INVOICE_AMOUNT+(PAID_AMOUNT+ADUSTED_AMOUNT),'DEBIT',INVOICE_AMOUNT+(PAID_AMOUNT+ADUSTED_AMOUNT),'STANDARD',INVOICE_AMOUNT+(PAID_AMOUNT+ADUSTED_AMOUNT))),0) "LIABILITY"
,       NVL(SUM(DECODE(INVOICE_TYPE_LOOKUP_CODE,'PREPAYMENT',(INVOICE_AMOUNT+(PAID_AMOUNT+ADJUSTED_AMOUNT)))),0) "PREPAYMENT"
FROM
(SELECT DISTINCT AI.VENDOR_ID
,       APS.VENDOR_NAME
,       APS.SEGMENT1 "VENDOR_NUM"
,       APS.VENDOR_TYPE_LOOKUP_CODE
,       APSS1.VENDOR_SITE_CODE
,       AI.ORG_ID
,       AI.INVOICE_ID
,       AI.INVOICE_NUM
,       AI.INVOICE_DATE
,       AI.INVOICE_TYPE_LOOKUP_CODE
,       ROUND(DECODE(AI.INVOICE_TYPE_LOOKUP_CODE,'PREPAYMENT',AI.INVOICE_AMOUNT*NVL(AI.EXCHANGE_RATE,1),AI.INVOICE_AMOUNT*NVL(AI.EXCHANGE_RATE,1)*-1),2) INVOICE_AMOUNT
,       AI.PAYMENT_STATUS_FLAG
,       NVL((SELECT ROUND(SUM(NVL(AIP.AMOUNT,0)*NVL(AI.EXCHANGE_RATE,1)),2) AMOUNT_PAID FROM APPS.AP_INVOICE_PAYMENTS_ALL AIP WHERE AIP.INVOICE_ID=AI.INVOICE_ID AND TRUNC(AIP.ACCOUNTING_DATE) <= :P89_DATE
        AND AI.INVOICE_TYPE_LOOKUP_CODE<>'PREPAYMENT' ),0) PAID_AMOUNT
,       NVL(CC.CODE_COMBINATION,(SELECT GK.CONCATENATED_SEGMENTS  FROM APPS.GL_CODE_COMBINATIONS_KFV GK                        WHERE GK.CODE_COMBINATION_ID=AI.ACCTS_PAY_CODE_COMBINATION_ID)) CODE_COMBINATION
,          ROUND((SELECT  NVL(SUM(AMOUNT)*-1,0) FROM   APPS.AP_INVOICE_LINES_ALL AIA WHERE AIA.INVOICE_ID=AI.INVOICE_ID
                AND AIA.ORG_ID=AI.ORG_ID AND  AIA.LINE_TYPE_LOOKUP_CODE='PREPAY' AND TRUNC(AIA.ACCOUNTING_DATE)<= :P89_DATE)*NVL(AI.EXCHANGE_RATE,1),2) ADUSTED_AMOUNT
,        ROUND((SELECT NVL(SUM(AMOUNT),0) FROM   APPS.AP_INVOICE_LINES_ALL AIM WHERE AIM.PREPAY_INVOICE_ID=AI.INVOICE_ID
                AND AIM.ORG_ID=AI.ORG_ID AND TRUNC(AIM.ACCOUNTING_DATE)<= :P89_DATE)*NVL(AI.EXCHANGE_RATE,1),2) ADJUSTED_AMOUNT
FROM APPS.AP_INVOICES_ALL AI
,     APPS.AP_SUPPLIERS APS
,     APPS.AP_SUPPLIER_SITES_ALL APSS1
,     APPS.AP_INVOICE_DISTRIBUTIONS_ALL AD
,     APPS.AP_INVOICE_LINES_ALL AIL
,     (SELECT DISTINCT XD.APPLIED_TO_SOURCE_ID_NUM_1,DECODE(XAL.ACCOUNTING_CLASS_CODE,'PREPAID_EXPENSE','PREPAYMENT',XAL.ACCOUNTING_CLASS_CODE) "CODE",GCC.SEGMENT1||'.'||GCC.SEGMENT2||'.'||GCC.SEGMENT3||'.'||GCC.SEGMENT4||'.'||GCC.SEGMENT5||'.'||GCC.SEGMENT6||'.'||GCC.SEGMENT7 CODE_COMBINATION
                  FROM APPS.XLA_AE_LINES XAL
               ,     APPS.XLA_DISTRIBUTION_LINKS XD
               ,     APPS.GL_CODE_COMBINATIONS GCC
               WHERE XD.AE_HEADER_ID=XAL.AE_HEADER_ID
               AND XD.AE_LINE_NUM=XAL.AE_LINE_NUM
               AND XAL.ACCOUNTING_CLASS_CODE IN('LIABILITY','PREPAID_EXPENSE')
               AND XAL.CODE_COMBINATION_ID=GCC.CODE_COMBINATION_ID)CC
WHERE AI.INVOICE_ID=AD.INVOICE_ID
AND AD.MATCH_STATUS_FLAG='A'
AND AI.VENDOR_ID=APS.VENDOR_ID
AND AI.VENDOR_ID=APSS1.VENDOR_ID
AND AI.VENDOR_SITE_ID=APSS1.VENDOR_SITE_ID
AND AI.ORG_ID=APSS1.ORG_ID
AND AI.INVOICE_ID=CC.APPLIED_TO_SOURCE_ID_NUM_1(+)
AND AI.INVOICE_ID=AIL.INVOICE_ID
AND AI.ORG_ID=AIL.ORG_ID
AND DECODE(AI.INVOICE_TYPE_LOOKUP_CODE,'PREPAYMENT',AI.INVOICE_TYPE_LOOKUP_CODE,'LIABILITY')=CC.CODE(+)
AND AD.LINE_TYPE_LOOKUP_CODE <> 'AWT'
AND AI.ORG_ID= :P89_ORG_ID
and NOT (ai.INVOICE_TYPE_LOOKUP_CODE='PREPAYMENT' AND PAYMENT_STATUS_FLAG='N')
AND TRUNC(AD.ACCOUNTING_DATE) <= :P89_DATE
ORDER BY APS.SEGMENT1)
GROUP BY VENDOR_TYPE_LOOKUP_CODE
,       ORG_ID
,       VENDOR_NUM
,       VENDOR_NAME
,       VENDOR_SITE_CODE
,       CODE_COMBINATION

TRIAL BALANCE QUERY - 11i and R12

SELECT   gb.SET_OF_BOOKS_ID,
         glcc.CONCATENATED_SEGMENTS,
         glcc.SEGMENT3 Accountno,
         gb.PERIOD_NAME,
         NVL (gb.PERIOD_NET_DR, 0) - NVL (gb.PERIOD_NET_CR, 0) PTD,
         (NVL (gb.BEGIN_BALANCE_DR, 0) - NVL (gb.BEGIN_BALANCE_CR, 0))
         + (NVL (gb.PERIOD_NET_DR, 0) - NVL (gb.PERIOD_NET_CR, 0))
            YTD
  FROM   gl_balances gb, gl_code_combinations_kfv glcc
 WHERE       1 = 1
         AND gb.code_combination_id = glcc.code_combination_id
         AND gb.code_combination_id = 267552
         AND gb.period_name = 'JUL-11'
         AND gb.set_of_books_id = 5

table detail query

Tables/Indexes

Here are some scripts related to Tables/Indexes .

Tabs w/ Questionable Inds

TABLES WITH QUESTIONABLE INDEX(ES) NOTES:

Owner - Owner of the table

Table Name - Name of the table

Column - Name of the column in question

The above query shows all tables that have more than one index with the same leading column. These indexes can cause queries to use an inappropriate indexes; in other words, Oracle will use the index that was created most recently if two indexes are of equal ranking. This can cause different indexes to be used from one environment to the next (e.g., from DEV to TEST to PROD).

The information does not automatically indicate that an index is incorrect; however, you may need to justify the existence of each of the indexes above.

select  TABLE_OWNER,
 TABLE_NAME,
 COLUMN_NAME
from   dba_ind_columns 
where   COLUMN_POSITION=1
and   TABLE_OWNER not in ('SYS','SYSTEM')
group   by TABLE_OWNER, TABLE_NAME, COLUMN_NAME
having  count(*) > 1 

Tabs With More Than 5 Inds

TABLES WITH MORE THAN 5 INDEXES NOTES:

Owner - Owner of the table

Table Name - Name of the table

Index Count - Number of indexes

select  OWNER,
 TABLE_NAME,
 COUNT(*) index_count
from   dba_indexes 
where   OWNER not in ('SYS','SYSTEM')
group   by OWNER, TABLE_NAME 
having  COUNT(*) > 5 
order  by COUNT(*) desc, OWNER, TABLE_NAME

Tables With No Indexes

TABLES WITHOUT INDEXES NOTES:

Owner - Owner of the table

Table Name - Name of the table

select  OWNER,
 TABLE_NAME
from 
(
select  OWNER, 
 TABLE_NAME 
from  dba_tables
minus
select  TABLE_OWNER, 
 TABLE_NAME 
from  dba_indexes
)
orasnap_noindex
where OWNER not in ('SYS','SYSTEM')
order  by OWNER,TABLE_NAME

Tables With No PK

NO PRIMARY KEY NOTES:

Table Owner - Owner of the table

Table Name - Name of the table

select  OWNER,
 TABLE_NAME
from    dba_tables dt
where   not exists (
        select  'TRUE'
        from    dba_constraints dc
        where   dc.TABLE_NAME = dt.TABLE_NAME
        and     dc.CONSTRAINT_TYPE='P')
and  OWNER not in ('SYS','SYSTEM')
order by OWNER, TABLE_NAME

Disabled Constraints

DISABLED CONSTRAINT NOTES:

Owner - Owner of the table

Table Name - Name of the table

Constraint Name - Name of the constraint

Constraint Type - Type of constraint

Status - Current status of the constraint

select  OWNER,
        TABLE_NAME,
        CONSTRAINT_NAME,
        decode(CONSTRAINT_TYPE, 'C','Check',
                                'P','Primary Key',
                                'U','Unique',
                                'R','Foreign Key',
                                'V','With Check Option') type,
        STATUS 
from  dba_constraints
where  STATUS = 'DISABLED'
order  by OWNER, TABLE_NAME, CONSTRAINT_NAME

FK Constraints

FOREIGN KEY CONSTRAINTS NOTES:

Table Owner - Owner of the table

Table Name - Name of the table

Constraint Name - Name of the constraint

Column Name - Name of the column

Referenced Table - Name of the referenced table

Reference Column - Name of the referenced column

Position - Position of the column

select  c.OWNER,
 c.TABLE_NAME,
 c.CONSTRAINT_NAME,
 cc.COLUMN_NAME,
 r.TABLE_NAME,
 rc.COLUMN_NAME,
 cc.POSITION
from  dba_constraints c, 
 dba_constraints r, 
 dba_cons_columns cc, 
 dba_cons_columns rc
where  c.CONSTRAINT_TYPE = 'R'
and  c.OWNER not in ('SYS','SYSTEM')
and  c.R_OWNER = r.OWNER
and  c.R_CONSTRAINT_NAME = r.CONSTRAINT_NAME
and  c.CONSTRAINT_NAME = cc.CONSTRAINT_NAME
and  c.OWNER = cc.OWNER
and  r.CONSTRAINT_NAME = rc.CONSTRAINT_NAME
and  r.OWNER = rc.OWNER
and  cc.POSITION = rc.POSITION
order  by c.OWNER, c.TABLE_NAME, c.CONSTRAINT_NAME, cc.POSITION

FK Index Problems

FK CONSTRAINTS WITHOUT INDEX ON CHILD TABLE NOTES:

Owner - Owner of the table

Constraint Name - Name of the constraint

Column Name - Name of the column

Position - Position of the index

Problem - Nature of the problem

It is highly recommended that an index be created if the Foreign Key column is used in joining, or often used in a WHERE clause. Otherwise a table level lock will be placed on the parent table.

select  acc.OWNER,
 acc.CONSTRAINT_NAME,
 acc.COLUMN_NAME,
 acc.POSITION,
 'No Index' Problem
from    dba_cons_columns acc, 
 dba_constraints ac
where   ac.CONSTRAINT_NAME = acc.CONSTRAINT_NAME
and    ac.CONSTRAINT_TYPE = 'R'
and     acc.OWNER not in ('SYS','SYSTEM')
and     not exists (
        select  'TRUE' 
        from    dba_ind_columns b
        where   b.TABLE_OWNER = acc.OWNER
        and     b.TABLE_NAME = acc.TABLE_NAME
        and     b.COLUMN_NAME = acc.COLUMN_NAME
        and     b.COLUMN_POSITION = acc.POSITION)
order   by acc.OWNER, acc.CONSTRAINT_NAME, acc.COLUMN_NAME, acc.POSITION

Inconsistent Column Names

INCONSISTENT COLUMN DATATYPE NOTES:

Owner - Owner of the table

Column - Name of the column

Table Name - Name of the table

Datatype - Datatype of the column

select  OWNER,
 COLUMN_NAME,
 TABLE_NAME,
 decode(DATA_TYPE, 'NUMBER', DATA_PRECISION, DATA_LENGTH) datatype
from  dba_tab_columns 
where   (COLUMN_NAME, OWNER) in
  (select COLUMN_NAME, 
   OWNER
    from  dba_tab_columns
    group by COLUMN_NAME, OWNER
     having min(decode(DATA_TYPE, 'NUMBER', DATA_PRECISION, DATA_LENGTH)) <
    max(decode(DATA_TYPE, 'NUMBER', DATA_PRECISION, DATA_LENGTH)) )
and  OWNER not in ('SYS', 'SYSTEM')
order by COLUMN_NAME,DATA_TYPE 

Object Extent Warning

TABLES THAT CANNOT EXTEND NOTES:

Owner - Owner of the object

Object Name - Name of the object

Object Type - Type of object

Tablespace - Name of the tablespace

Next Extent - Size of next extent (bytes)

select  OWNER,
 SEGMENT_NAME,
 SEGMENT_TYPE,
 TABLESPACE_NAME,
 NEXT_EXTENT
from (
 select  seg.OWNER, 
  seg.SEGMENT_NAME,
   seg.SEGMENT_TYPE, 
  seg.TABLESPACE_NAME,
   t.NEXT_EXTENT
 from  dba_segments seg,
   dba_tables t
 where  (seg.SEGMENT_TYPE = 'TABLE'
 and    seg.SEGMENT_NAME = t.TABLE_NAME
 and    seg.owner = t.OWNER
 and    NOT EXISTS (
   select  TABLESPACE_NAME
    from  dba_free_space free
    where  free.TABLESPACE_NAME = t.TABLESPACE_NAME
    and  BYTES >= t.NEXT_EXTENT))
 union
 select  seg.OWNER, 
  seg.SEGMENT_NAME,
   seg.SEGMENT_TYPE, 
  seg.TABLESPACE_NAME,
   c.NEXT_EXTENT
 from  dba_segments seg,
   dba_clusters c 
 where   (seg.SEGMENT_TYPE = 'CLUSTER'
 and      seg.SEGMENT_NAME = c.CLUSTER_NAME
 and      seg.OWNER = c.OWNER
 and     NOT EXISTS (
   select  TABLESPACE_NAME
   from  dba_free_space free
   where  free.TABLESPACE_NAME = c.TABLESPACE_NAME
   and  BYTES >= c.NEXT_EXTENT))
 union
 select  seg.OWNER, 
  seg.SEGMENT_NAME,
   seg.SEGMENT_TYPE, 
  seg.TABLESPACE_NAME,
   i.NEXT_EXTENT
 from  dba_segments seg,
   dba_indexes  i
 where   (seg.SEGMENT_TYPE = 'INDEX'
 and      seg.SEGMENT_NAME = i.INDEX_NAME
 and      seg.OWNER        = i.OWNER
 and      NOT EXISTS (
   select  TABLESPACE_NAME
     from  dba_free_space free
     where  free.TABLESPACE_NAME = i.TABLESPACE_NAME
   and  BYTES >= i.NEXT_EXTENT))
 union
 select  seg.OWNER, 
  seg.SEGMENT_NAME,
   seg.SEGMENT_TYPE, 
  seg.TABLESPACE_NAME,
   r.NEXT_EXTENT
 from  dba_segments seg,
   dba_rollback_segs r
 where   (seg.SEGMENT_TYPE = 'ROLLBACK'
 and      seg.SEGMENT_NAME = r.SEGMENT_NAME
 and      seg.OWNER        = r.OWNER
 and      NOT EXISTS (
   select TABLESPACE_NAME
     from  dba_free_space free
     where  free.TABLESPACE_NAME = r.TABLESPACE_NAME
                and  BYTES >= r.NEXT_EXTENT))
)
orasnap_objext_warn
order  by OWNER,SEGMENT_NAME

Segment Fragmentation

OBJECTS WITH MORE THAN 50% OF MAXEXTENTS NOTES:

Owner - Owner of the object

Tablespace Name - Name of the tablespace

Segment Name - Name of the segment

Segment Type - Type of segment

Size - Size of the object (bytes)

Extents - Current number of extents

Max Extents - Maximum extents for the segment

Percentage - Percentage of extents in use

As of v7.3.4, you can set MAXEXTENTS=UNLIMITED to avoid ORA-01631: max # extents (%s) reached in table $s.%s.

To calculate the MAXEXTENTS value on versions < 7.3.4 use the following equation: DBBLOCKSIZE / 16 - 7

Here are the MAXEXTENTS for common blocksizes: 1K=57, 2K=121, 4K=249, 8K=505, and 16K=1017

Multiple extents in and of themselves aren't bad. However, if you also have chained rows, this can hurt performance.

select  OWNER,
 TABLESPACE_NAME,
 SEGMENT_NAME,
 SEGMENT_TYPE,
 BYTES,
 EXTENTS,
 MAX_EXTENTS,
 (EXTENTS/MAX_EXTENTS)*100 percentage
from  dba_segments
where  SEGMENT_TYPE in ('TABLE','INDEX')
and  EXTENTS > MAX_EXTENTS/2
order  by (EXTENTS/MAX_EXTENTS) desc

Extents reaching maximum

TABLES AND EXTENTS WITHIN 3 EXTENTS OF MAXIMUM :

Owner - Owner of the segment

Segment Name - Name of the segment

select owner "Owner",
       segment_name "Segment Name",
       segment_type "Type",
       tablespace_name "Tablespace",
       extents "Ext",
       max_extents "Max"
from dba_segments
where ((max_extents - extents) <= 3) 
and owner not in ('SYS','SYSTEM')
order by owner, segment_name

Analyzed Tables

ANALYZED TABLE NOTES:

Owner - Owner of the table

Analyzed - Number of analyzed tables

Not Analyzed - Number of tables that have not be analyzed

Total - Total number of tables owned by user

The ANALYZE statement allows you to validate and compute statistics for an index, table, or cluster. These statistics are used by the cost-based optimizer when it calculates the most efficient plan for retrieval. In addition to its role in statement optimization, ANALYZE also helps in validating object structures and in managing space in your system. You can choose the following operations: COMPUTER, ESTIMATE, and DELETE. Early version of Oracle7 produced unpredicatable results when the ESTIMATE operation was used. It is best to compute your statistics.

A COMPUTE will cause a table-level lock to be placed on the table during the operation.

select OWNER,
 sum(decode(nvl(NUM_ROWS,9999), 9999,0,1)) analyzed,
 sum(decode(nvl(NUM_ROWS,9999), 9999,1,0)) not_analyzed,
 count(TABLE_NAME) total
from  dba_tables
where  OWNER not in ('SYS', 'SYSTEM')
group  by OWNER

Recently Analyzed Tables

LAST ANALYZED TABLE NOTES:

Owner - Owner of the table

Table Name - Name of the table

Last Analyzed - Last analyzed date/time

select  OWNER,
 TABLE_NAME,
 to_char(LAST_ANALYZED,'MM/DD/YYYY HH24:MI:SS') last_analyzed
from  dba_tab_columns
where  OWNER not in ('SYS','SYSTEM')
and  LAST_ANALYZED is not null
and COLUMN_ID=1
and  (SYSDATE-LAST_ANALYZED) < 30
order by (SYSDATE-LAST_ANALYZED)

Cached Tables

CACHED TABLE NOTES:

Owner - Owner of the table

Table Name - Name of the table

Cache - Cached?

Oracle 7.1+ provides a mechanism for caching table in the buffer cache. Caching tables will speed up data access and improve performance by finding the data in memory and avoiding disk reads.

select  OWNER,
 TABLE_NAME,
 CACHE
from dba_tables
where OWNER not in ('SYS','SYSTEM')
and CACHE like '%Y'
order by OWNER,TABLE_NAME

invoice_validatad or never_validated

exec


SELECT

invoice_num, approval_status_lookup_code,ORG_IDFROM ap_invoices_vWHERE invoice_num in ('RECEIPT/426014245/2730024', MO_GLOBAL.INIT('SQLAP');

invoice_payment

/* Formatted on 9/19/2011 12:23:40 PM (QP5 v5.115.810.9015) */SELECT AI.INVOICE_ID C_INVOICE_ID,AI.LAST_UPDATE_DATE C_LAST_UPDATE_DATE,PV.SEGMENT1 C_SUPPLIER_CODE,DECODE (:h_sort_by_alternate,'Y',PV.VENDOR_NAME_ALT,'N',PV.VENDOR_NAME)C_SUPPLIER_NAME,DECODE (:h_sort_by_alternate,'Y',PVS.VENDOR_SITE_CODE_ALT,'N',PVS.VENDOR_SITE_CODE)C_SUPPLIER_SITE,PV.VENDOR_NAME C_SUPPLIER_NAME_DISP,PVS.VENDOR_SITE_CODE C_SUPPLIER_SITE_CODE_DISP,AI.INVOICE_NUM C_INVOICE_NUMBER,AI.INVOICE_DATE C_INVOICE_DATE,AI.INVOICE_CURRENCY_CODE C_CURRENCY,AI.INVOICE_AMOUNT C_AMOUNT,AI.EXCHANGE_RATE C_EXCHANGE_RATE,ALC1.DISPLAYED_FIELD C_TAX_CALCULATION,gcc
|| .segment1'.'|| gcc
|| .segment2'.'|| gcc
|| .segment3'.'|| gcc
|| .segment4'.'|| gcc
|| .segment5'.'|| gcc
|| .segment6'.'|| gcc
gcc.segment7,ffv1
|| .DESCRIPTION'.'|| ffv2
|| .DESCRIPTION'.'|| ffv3
|| .DESCRIPTION'.'|| ffv4
|| .DESCRIPTION'.'|| ffv5
|| .DESCRIPTION'.'|| ffv6
|| .DESCRIPTION'.'|| ffv7
LIAB_ACCT_DESC.DESCRIPTION,-- RPAD (&H_ACCT_SEGS, 600) C_INVOICE_ACCT_SEGS , /*1683898 , added rpad*/ FU.USER_NAME C_UPDATED_BY,ALC2.DISPLAYED_FIELD C_INVOICE_TYPE,FDS.NAME C_SEQUENCE_NAME,AI.DOC_SEQUENCE_VALUE C_VOUCHER_NUMBER,AI.CREATION_DATE C_CREATION_DATE,AI.CREATED_BY C_CREATED_BY,CFU.USER_NAME C_CREATED_BY_NAME,AI.LAST_UPDATED_BY C_LAST_UPDATED_BY,AI.SET_OF_BOOKs_ID CP_SET_OF_BOOKS,AI.GL_date,T.NAMEFROM AP_INVOICES_all AI,PO_VENDORS PV,PO_VENDOR_SITES_all PVS,FND_USER FU,FND_USER CFU,AP_LOOKUP_CODES ALC1,AP_LOOKUP_CODES ALC2,FND_DOCUMENT_SEQUENCES FDS,GL_CODE_COMBINATIONS GCC,AP_TERMS T,FND_FLEX_VALUES_VL ffv1,FND_FLEX_VALUES_VL ffv2,FND_FLEX_VALUES_VL ffv3,FND_FLEX_VALUES_VL ffv4,FND_FLEX_VALUES_VL ffv5,FND_FLEX_VALUES_VL ffv6,FND_FLEX_VALUES_VL ffv7
WHERE 1 = 1-- &H_INVOICE_WHERE------/*-- trunc(AI.LAST_UPDATE_DATE) between nvl(trunc(:P_START_UPDATE_DATE),AI.LAST_UPDATE_DATE) and -- nvl(trunc(:P_END_UPDATE_DATE),AI.LAST_UPDATE_DATE) and*/AND TRUNC (AI.LAST_UPDATE_DATE) BETWEEN NVL (:P_START_UPDATE_DATE,TRUNC (AI
.LAST_UPDATE_DATE))AND NVL (:P_END_UPDATE_DATE,TRUNC (AI
.LAST_UPDATE_DATE))AND AI.SET_OF_BOOKS_ID = NVL (:P_SET_OF_BOOKS_ID ,AI.SET_OF_BOOKS_ID )




AND AI.VENDOR_ID = PV.VENDOR_IDAND AI.VENDOR_SITE_ID = PVS.VENDOR_SITE_IDAND AI.LAST_UPDATED_BY = FU.USER_IDAND AI.CREATED_BY = CFU.USER_IDAND (AI.AUTO_TAX_CALC_FLAG = ALC1.LOOKUP_CODEOR ALC1.DISPLAYED_FIELD = 'Line')
AND AI.TERMS_ID = T.TERM_IDAND ALC1.LOOKUP_TYPE = 'AP_TAX_CALCULATION_METHOD'
AND AI.INVOICE_TYPE_LOOKUP_CODE = ALC2.LOOKUP_CODEAND ALC2.LOOKUP_TYPE = 'INVOICE TYPE'
AND AI.ACCTS_PAY_CODE_COMBINATION_ID = GCC.CODE_COMBINATION_IDAND AI.DOC_SEQUENCE_ID = FDS.DOC_SEQUENCE_ID(+)and FDS.NAME = nvl(:P_C_SEQUENCE_NAME,FDS.NAME)AND AI.TERMS_ID = NVL (:P_TERMS_ID, AI.TERMS_ID)AND AI.DOC_SEQUENCE_VALUE BETWEEN NVL (:P_C_VOUCHER_NUMBER_FROM,AI.DOC_SEQUENCE_VALUE)AND NVL (:P_C_VOUCHER_NUMBER_TO,AI.DOC_SEQUENCE_VALUE)AND pv.vendor_id =NVL (:P_SUPPLIER_NAME,pv


.vendor_id) and (gcc.segment1 = ffv1.FLEX_VALUEAND ffv1.FLEX_VALUE_SET_ID = 1014875
OR gcc.segment1 = ffv1.FLEX_VALUEAND ffv1.FLEX_VALUE_SET_ID = 1014876
OR gcc.segment1 = ffv1.FLEX_VALUEAND ffv1.FLEX_VALUE_SET_ID = 1014877
OR gcc.segment1 = ffv1.FLEX_VALUEAND ffv1.FLEX_VALUE_SET_ID = 1014878OR
gcc.segment1 = ffv1.FLEX_VALUEAND ffv1.FLEX_VALUE_SET_ID = 1014874)
AND gcc.segment2 = ffv2.FLEX_VALUEAND ffv2.FLEX_VALUE_SET_ID = 1014869
AND (gcc.segment3 = ffv3.FLEX_VALUEAND ffv3.FLEX_VALUE_SET_ID = 1014870
OR gcc.segment8 = ffv3.FLEX_VALUEAND ffv3.FLEX_VALUE_SET_ID = 1014870)
AND gcc.segment4 = ffv4.FLEX_VALUEAND ffv4.FLEX_VALUE_SET_ID = 1014885
AND gcc.segment5 = ffv5.FLEX_VALUEAND ffv5.FLEX_VALUE_SET_ID = 1014886
AND gcc.segment6 = ffv6.FLEX_VALUEAND ffv6.FLEX_VALUE_SET_ID = 1014887
AND gcc.segment7 = ffv7.FLEX_VALUEAND ffv7.FLEX_VALUE_SET_ID = 1014888ORDER BY FDS.NAME,AI.DOC_SEQUENCE_VALUE,AI.LAST_UPDATE_DATE,DECODE (:h_sort_by_alternate,'Y',PV.VENDOR_NAME_ALT,'N',PV.VENDOR_NAME),DECODE (:h_sort_by_alternate,'Y',PVS.VENDOR_SITE_CODE_ALT,'N',PVS.VENDOR_SITE_CODE),AI.INVOICE_NUM