Oracle 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 

Oracle IExpenses Setups In R12

:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:

I Expenses:
iExpenses is basically an extension Oracle Payables. Employee and Contingent Worker expense reports become supplier invoices and get paid from Payables. You will need following responsibilties to set up Internet Expenses: Payables Manager, Internet Expenses Setup and Administration, System Administration, Application Developer, and AX Developer. If you are also planning on charging expense reports to projects, you will also need Project Billing Super User and General Ledger Super User responsibilities. You will also need access to Oracle Workflow Builder to customize the Expenses workflow and Project Expense Reports Account Generator.

Setups for IExpenses : 

1.Define MOAC

2.Define Job

3.Define Position.

4.Define Employee.

5.Define Financial Options.

6.Define Expenses Template.

7.Define Payable Options.  

8.Assign Cost Center Flexfield qualifier to Department segment or Cost center segment.

9.Define Signing Limits.

10.Assign Profile Option to IExpenses Responsibility.

 1.Define MOAC

·                     Define Responsibilities for GL, AP,PO,HRMS,IEXPENSES

·                     Define Business Group.

·                     Define Ledger.

·                     Define Operating Unit.

·                     Define Security Profile.

·                     Run Security List Maintenance Program. 

·                     Assign Security Profile to Responsibilities. 

·                     Run Replicate Seed Data Program.  

2.Define Job
Navigation:HRMS --> Work structures --> Job --> Description.

Click on New button. 

Enter the Job Name and Code.

 Save.

 3.Define Position.

Click on New button. 

Enter Position number and name ,type, Organization,job,and Status of the position.

Save.

Note: If you want create more position please follow same as above procedure.

  4.Define Employee.

Navigation: HRMS --> People --> Enter and Maintain.

Click on New. 

 Enter Employee Last name,gender,action and birth date.

Save and click on Assignments. 

 Enter HR Organization name,job name and Position name.

Save.

Click left lov button and select the purchase order information.

 Enter the primary ledger name and default expenses account.

Save. 

5.Define Financial Options.

Use the Financials Options window to define the options and defaults that you use for your Oracle Financial Application(s). Values you enter in this window are shared by Oracle Payables, Oracle Purchasing, and Oracle Assets. You can define defaults in this window to simplify supplier entry, requisition entry, purchase order entry, invoice entry, and automatic payments. Depending on your application, you may not be required to enter all fields.
Although you only need to define these options and defaults once, you can update them at any time. If you change an option and it is used as a default value elsewhere in the system, it will only be used as a default for subsequent transactions. For example, if you change the Payment Terms from Immediate to Net 30, Net 30 will be used as a default for any new suppliers you enter, but the change will not affect the Payment Terms of existing suppliers.

Navigation: Payables --> Setup --> Options --> Financial.

 
 Click on New button.

Accounting Tab:
You are required to enter defaults for the Accounting Financials Options in the Accounting region. 

Accounts Like:
Liability, Prepayment, Discount Taken.  

Supplier-Purchasing Tab:
If you do not also have Oracle Purchasing installed, you do not need to enter defaults in the Supplier- Purchasing region.

 Encumbrance Tab:
If you do not use encumbrance accounting or budgetary control, you do not need to enter defaults in the Encumbrance region. 

Tax Tab:
If your enterprise does not need to record a VAT registration number, you don't need to enter defaults in the Tax region.

Human Resources Tab:
If you do not have Oracle Human Resources installed, you are not required to enter defaults in the Human Resources region.

 Save.

 6.Define Expenses Template.

Navigation: Payables --> Setup --> Invoice --> Expenses Report Templates.

Enter operating unit name,template name and enable the enable for internet expenses. 

And finally enter your expenses.

Save. 

7.Define Payable Options.  

Navigation: Payables --> Setup --> Options --> Payable Options.

Click on Find. 

  

Click on Expense Report Tab. 

Default Template. The default expense report template that you want to use in the Payables Expense Reports window. You can override this value in the Expense Reports window. A default expense report template appears in the Expense Reports window only if the expense report template is active.

Apply Advances. Default value for the Apply Advances option in the Expense Reports window in Payables. If you enable this option, Payables applies advances to employee expense reports if the employee has any outstanding, available advances. You can override this default during expense report entry.
If you use Internet Expenses and you enable this option, then Expense Report Export applies all outstanding, available advances, starting with the oldest, up to the amount of the Internet expense report.

Automatically Create Employee as Supplier. If you enable this option, when you import Payables expense reports, Payables automatically creates a supplier for any expense report where an employee does not already exist as a supplier. If the supplier site you are paying (HOME or OFFICE) does not yet exist, Payables adds the supplier site to an existing supplier. Payables creates a HOME or OFFICE supplier site with the appropriate address, depending on where you are paying the expense report. The Home address is from the PER_ADDRESSES table, and the Office address is from the HR_LOCATIONS table. Payables creates suppliers based on the defaults you select in this region and employee information from the Enter Person window. You can review suppliers and adjust any defaults in the Suppliers window.

If you do not enable this option, enter an employee as a supplier in the Suppliers window and link the Employee Name/Number to the supplier before you use Expense Report Export. Payables cannot export expense reports without corresponding suppliers, and lists them on Export Results page.
Payment Terms. Payment terms you want to assign to any suppliers that you create from employees during Expense Report Export.

Suggestion: Define and assign immediate payment terms for your employee suppliers.

Pay Group. Pay Group you want to assign to any suppliers that you create from employees during Expense Report Export. You can define additional values for Pay Group in the Purchasing Lookups window.
Payment Priority. Payment priority you want to assign to any suppliers that you create from employees during Expense Report Export. A number, between 1 (high) and 99 (low), which represents the priority of payment for a supplier.

Hold Unmatched Expense Reports. This option defaults to the Hold Unmatched Invoices option for the supplier and supplier site for any suppliers Payables creates during Expense Report Export.
When Hold Unmatched Invoices for a supplier site is enabled, Payables requires that you match each invoice for the supplier site to either a purchase order or receipt. If you enable this option for a site, then Payables applies a Matching Required hold to an invoice if it has Item type distributions that are not matched to a purchase order or receipt. Payables applies the hold to the invoice during Invoice Validation. You cannot pay the invoice until you release the hold. You can release this hold by matching the invoice to a purchase order or receipt and resubmitting Invoice Validation, or you can manually release the hold in the Holds tab of the Invoice Workbench. Payables will not apply a hold if the sum of the invoice distributions by accounting code combination is zero.
 

Save. 

8.Assign Cost Center Flexfiedl qualifier to Department segment or Costcenter segment. 

Navigation: Payables --> Setup --> Flexfield --> Key --> Segments.

Query your Coa.

Un Freeze Flexfield Definition and click on segments.

 select Cost Center segment and then click on the Flexfield Qualifiers.

Enable cost center segment. 

Save.

 9.Define Signing Limits.

Navigation: Payables --> Employees --> Signing Limits.

 10.Assign Profile Option to IExpenses Responsibility.

Navigation: System Administrator --> Profile --> Systems.

Please set the following Profile options in Site level as well as Responsibility level.   

:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:

To Identify the Descriptive Flexfield present in a window(form)

             1. Navigate to the window and block, and set the cursor at the

                field for which you want to set up the descriptive flexfield.

           

             2. Select Help->Tools->Examine from the menu bar.  You may be

                asked to enter a password.

             3. The Examine Field and Variable Values window initially

                displays the hidden block and field names of the field

                your cursor was in.  Note the block name and field name

                displayed to assist you later selecting the correct flexfield. 

             4. Click the down arrow symbol in the block field.  Select

                $DESCRIPTIVE_FLEXFIELD$ in Choose a block window. Click OK.

             5. Click the down arrow symbol in the Field field.  Select

                the descriptive flexfield you want.  The LOV displays

                the block names and field names for all descriptive

                flexfields on that form.

             6. The flexfield title that appears in the Value field is

                the descriptive flexfield name with application enclosed in

                a pair of parenthesis.  Note the descriptive flexfield name.

                This is the title to choose in the Descriptive Flexfield

                Segments form.