IN ORACLE MILIEU …

Beyond Knowledge

Virual Index and Invisible Index

Leave a Comment Posted by on March 23, 2011

Oracle has come up with a feature called virtual index in oracle 9i. This feature allow us to test an index on a table without actually adding an index on the table. The table will be visible only in our session and will be used by our queries only (if optimizer decide it to use). So basically the index will be visible to optimizer only in our sessions. Optimizer running query in other sessions won’t be able to see this index.

Virtual Index in Oracle 9i

Utility of using virtual index is that, suppose we have a table having huge number of rows and it is getting joined with other tables. If we see that optimizer is creating a plan which is costly and SQL tuning advisor suggest us to create an index on a column, then in case of production database we cannot simply create an index and test the changes. We need to make sure that creating that index wont have any negative impact on the execution plan of other queries running in this database.

So there is where we can use virtual index. Here is how virtual index works.

1) Creating a table

SQL> create table test as select * from dba_objects;

Table created.

2) Try selecting a value from test table

SQL> select * from test where object_name = 'STANDARD';

Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020

----------------------------------
| Id  | Operation         | Name |
----------------------------------
|   0 | SELECT STATEMENT  |      |
|*  1 |  TABLE ACCESS FULL| TEST |
----------------------------------

3) Create a virtual Index on test table

SQL> create index test_idx_1 on test(object_name) nosegment;

Index created.

In order to create a virtual index, we need to give NOSEGMENT at the end of the create index statement. This will just create a index on the object_name column of test table. But it will not create an index segment in database.

You can check this by querying dba_objects and dba_indexes tables.

SQL> select index_name from dba_indexes where table_name = 'TEST' and index_name = 'TEST_IDX_1';

no rows selected
SQL> col OBJECT_NAME format a30;
SQL> select object_name, object_type from dba_objects where object_name = 'TEST_IDX_1';

OBJECT_NAME                    OBJECT_TYPE
------------------------------ -------------------
TEST_IDX_1                     INDEX

So, object exists in database, but we dont have segment for the same.

Now if you try to run the same select command on test table, still optimizer will NOT use virtual index.

SQL> select * from test where object_name = 'STANDARD';
Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020

----------------------------------
| Id  | Operation         | Name |
----------------------------------
|   0 | SELECT STATEMENT  |      |
|*  1 |  TABLE ACCESS FULL| TEST |
----------------------------------

In order for optimizer to use virtual index, you need to set a parameter called _USE_NOSEGMENT_INDEXES in your session

SQL> alter session set "_USE_NOSEGMENT_INDEXES" = true;

Session altered.

Once you set this hidden parameter, optimizer will start using the virtual index you created on this table

SQL> select * from test where object_name = 'STANDARD';

Execution Plan
----------------------------------------------------------
Plan hash value: 1221747299

------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |     2 |   354 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST       |     2 |   354 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | TEST_IDX_1 |    46 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

If you run this query from some other session, it wont use virtual index.

you can analyze virtual indexes

SQL> analyze index TEST_IDX_1 compute statistics;

Index analyzed.

You CANNOT rebuild a virtual index

SQL> alter index TEST_IDX_1 rebuild;
alter index TEST_IDX_1 rebuild
*
ERROR at line 1:
ORA-08114: can not alter a fake index

Finally, if the index doesn’t seem to satisfy your requirement, then you can drop the virtual index.

SQL> drop index TEST_IDX_1;

Index dropped.

Invisible Index in 11g

We have a similar concept in Oracle database 11g called invisible index.

In case of invisible index, we can check if creating a new index is actually going to improve the performance or not. This index will not be visible to any other session and it will not be used by any other existing query run on same table.

SQL>drop table t;

Table dropped.

SQL>create table t as select * from dba_objects;

Table created.

SQL>create index t_ind1 on t(object_name) invisible;

Index created.

You can also make existing index as invisible using alter index command. You can make existing invisible index as visible. As soon as you make index visible, your existing queries will start using new index.

TDCLTN1>alter index t_ind1 visible;
TDCLTN1>alter index t_ind1 invisible;

We have a new column in USER_INDEXES called VISIBILITY. This tells whether an index is visible or not.

SQL>select index_name,VISIBILITY from user_indexes where index_name='T_IND1';

INDEX_NAME                     VISIBILIT
------------------------------ ---------
T_IND1                         INVISIBLE

So how does INVISIBLE index works ?

Now that we have created an INVISIBLE index, lets try to run a query on new table T and see if it uses the index.

SQL>explain plan for
 2  select * from t where object_name='STANDARD';

Explained.

SQL>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1601196873

----------------------------------
| Id  | Operation         | Name |
----------------------------------
|   0 | SELECT STATEMENT  |      |
|*  1 |  TABLE ACCESS FULL| T    |
----------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

 1 - filter("OBJECT_NAME"='STANDARD')

So we can see its doing a full table scan.

In order to use the invisible index, we have a new parameter introduced in 11g – OPTIMIZER_USE_INVISIBLE_INDEXES

This parameter can be set to either TRUE or FALSE

If set to true, we are asking optimizer to use the invisible index if it can make a better plan using that.

Lets try to set this parameter to TRUE and run the same query.

SQL>select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     2 |   202 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T      |     2 |   202 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T_IND1 |     2 |       |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
1 - filter("OBJECT_NAME"='STANDARD')

Now it has used the index and the cost of the query also reduced.

If the index seems to make positive impact on the other query as well, then you can make this index as visible.

Note that if the index is INVISIBLE, then optimizer will ignore the index and 10053 trace will show index as “UNUSABLE”

Difference:

So based on above examples, we can see that the difference is

- In case of virtual index, we dont actually create the index, its just the place holder. In case of invisible index we actually create the index

- In case of virtual index, we can check if the index is getting used or not using explain plan and performance of the query can be mentioned statistically by explain plan. In case of invisible index, we can actually run the query and check the performance benefit.

- We cannot make existing index as virtual, unless we have to drop it and create a no_segment index. We can make any index invisible.

Hope this helps !!

Oracle Database 10g, Oracle Database 11g, Oracle Database 9i ,

Oracle Index rebuild online – 10g Vs 11g

5 Comments Posted by on March 17, 2011

An index is basically used for faster access to tables. Over a period of time the index gets fragmented because of several DMLs running on table.
When the index gets fragmented, data inside the index is scattered, rows / block reduces, index consumes more space and scanning of index takes more time and more blocks for same set of queries.
To talk in index terminology, we will have a single root block, but as fragmentation increases there will be more number of branch blocks and more leaf blocks. Also the height of index will increase.

To fix the above issue, we go for index rebuild. During index rebuild, the data inside the index is reorganized and compressed to fit in minimum number of blocks, height of the index is reduced to minimum possible level and performance of queries will increase.
Your search becomes faster and your query will read less number of blocks.

There are 2 methods to rebuild the index.

1) Offline index rebuild – alter index <index name> rebuild;
2) Online index rebuild  – alter index <index name> rebuild online;

With offline index rebuild, table and index is locked in exclusive mode preventing any translations on the table. This is most intrusive method and is used rarely in production unless we know for sure that modules are not going to access the table and we have complete downtime.

With online index rebuild, transactions can still access the table and index. Only for very less amount of time the lock is acquired on the table (by index rebuild operation). For rest of the time table and index is available for transactions.
However there is a difference in a way this internal online rebuild mechanism works in 10g and 11g. With 11g things are refined further to minimize the impact.

I will first explain the 10g method of rebuilding the index, after that will see the behavior in 11g

10g behaviour:

The way online index build (OIB) works is by creating an IOT journal table to keep track of changes while the OIB is in progress and merge all the changes from journal table to complete index build operation.

If I have a table with the structure of (A NUMBER, B NUMBER, C CHAR(100), D NUMBER) and to create index on (A, B, C) column, Oracle would create journal table like:

create table “ORACLE”.”SYS_JOURNAL_18155″ (C0 NUMBER, C1 NUMBER, C2 VARCHAR2(100), opcode char(1), partno number, rid rowid, primary key( C0, C1, C2 , rid )) organization index;

Before 11g, OIB will get in the DML queue to lock the table exclusively while preventing the new DML’s to go through, once all the active transactions (ones which were initiated before the OIB) are completed, OIB will create the journal IOT table and release the exclusive table lock (it’ll still keep the share lock on the table to prevent any other DDL’s) for DML’s to continue.

As part of journal table creation, Oracle would create an internal trigger on the primary table to record all the changes to the journal table. Along with using all the index columns, journal table will add “ROWID” to that list to make it as primary key.

“OPCODE” column represents the type of operation like “I” for Insert and “D” for Delete.
“PARTNO” column represents partition number of the underlying table.

Among all the changes to a given row for any of the index key columns, only the most recent change for that record is visible in the journal table. Any update operation of index key columns would be converted to “DELETE” and “INSERT” in the journal table.

While rest of the user sessions populate journal table with the ongoing changes for index key columns, OIB session would be reading the table in consistent mode (as of the time journal table is created) to create the index followed by the final merge operation.

During the merge process, Oracle will read the journal table (IOT) leaf blocks from left to right to merge those changes with the index being built. As the journal table leaf block changes are applied, once a leaf block is fully consumed, it’s reference will be deleted from the branch block.

This process will continue until all leaf blocks are consumed and when it comes to the last leaf block, Oracle would stop all the DML’s again to do the final merge and drop the journal table before releasing the mode 6 exclsuive table lock.

As each leaf block is consumed, Oracle would mark each entry as deleted. If more DML’s happen while Oracle is doing the merge, it’ll do one more pass of walking through the leaf blocks, this process continues until the merge process is all done.

If there is any long running DML before issuing the OIB, Oracle would wait on the long running transaction and it will prevent upcoming DML’s also. This is one of the major problems with OIB. Same thing can happen when it is doing the final merge.

So in the above process lock is acquired 2 times, one during the start of index creation when journal table is created online index rebuild process needs to be take exclusive lock on table to prevent DMLs from changing data. Once journal table is created online index rebuild process will release DML lock and hold a shared lock. Any DMLs happening after this will have entry made into journal table.
Again at the end of the process online index rebuild process will try to take exclusive lock to merge the last block of journal table into the main index.

Following example demonstrate the same:

Create test table and insert huge number of rows

</pre>
SQL>create table test as select * from dba_objects;

Table created.

SQL>insert into test select * from test;

29493 rows created.

SQL>/

58986 rows created.

Like this add more rows till it becomes big

Check the size of table


SQL>select sum(bytes)/1024/1024/1024 from dba_segments where segment_name = 'TEST';

SUM(BYTES)/1024/1024/1024
-------------------------
 .430053711

Create index on the table

SQL>create index I1 on test(OBJECT_NAME);

Index created.

Check the size of index.

SQL>select sum(bytes)/1024/1024/1024 from dba_segments where segment_name = 'I1';

SUM(BYTES)/1024/1024/1024
-------------------------
 .129272461

Now from the session 1, try inserting a record in the TEST table

Session 1 SID: 3204
SQL Text :

SQL>insert into test (owner, object_name) values ('AVDEO','NEW_TEST');
1 row created.
SQL>

From session 2, try rebuilding the index online

Session 2 SID: 3046
SQL text :

alter index I1 rebuild online

From session 3, run another DML command.

Session 3 SID: 3827
SQL Text:

update test set OWNER = ‘DEO2′ where OWNER = ‘DEO’

If we check v$lock table we can see session 2 (online index rebuild) is waiting on session 1 (insert). So unless insert completes, session 2 doing online index rebuild will not get a exclusive lock.


SQL>select
 2      l.SID oracle_id,
 3      decode(TYPE,
 4          'MR', 'Media Recovery',
 5          'RT', 'Redo Thread',
 6          'UN', 'User Name',
 7          'TX', 'Transaction',
 8          'TM', 'DML',
 9          'UL', 'PL/SQL User Lock',
 10          'DX', 'Distributed Xaction',
 11          'CF', 'Control File',
 12          'IS', 'Instance State',
 13          'FS', 'File Set',
 14          'IR', 'Instance Recovery',
 15          'ST', 'Disk Space Transaction',
 16          'TS', 'Temp Segment',
 17          'IV', 'Library Cache Invalidation',
 18          'LS', 'Log Start or Switch',
 19          'RW', 'Row Wait',
 20          'SQ', 'Sequence Number',
 21          'TE', 'Extend Table',
 22          'TT', 'Temp Table', type) lock_type,
 23      decode(LMODE,
 24          0, 'None',
 25          1, 'Null',
 26          2, 'Row-S (SS)',
 27          3, 'Row-X (SX)',
 28          4, 'Share',
 29          5, 'S/Row-X (SSX)',
 30          6, 'Exclusive', lmode) lock_held,
 31      decode(REQUEST,
 32          0, 'None',
 33          1, 'Null',
 34          2, 'Row-S (SS)',
 35          3, 'Row-X (SX)',
 36          4, 'Share',
 37          5, 'S/Row-X (SSX)',
 38          6, 'Exclusive', request) lock_requested,
 39      decode(BLOCK,
 40          0, 'Not Blocking',
 41          1, 'Blocking',
 42          2, 'Global', block) status,
 43      OBJECT_NAME
 44  from    v$locked_object lo,
 45      dba_objects do,
 46      v$lock l
 47  where     lo.OBJECT_ID = do.OBJECT_ID
 48  AND     l.SID = lo.SESSION_ID
 49  /

 ORACLE_ID LOCK_TYPE                  LOCK_HELD                                LOCK_REQUESTED                           STATUS               OBJECT_NAME
---------- -------------------------- ---------------------------------------- ---------------------------------------- -------------------- ------------------------------
 3204 DML                        Row-X (SX)                               None                                     Blocking             TEST
 3204 Transaction                Exclusive                                None                                     Not Blocking         TEST
 3075 DL                         Row-X (SX)                               None                                     Not Blocking         SYS_JOURNAL_1664589
 3075 DL                         Row-X (SX)                               None                                     Not Blocking         SYS_JOURNAL_1664589
 3075 DML                        Share                                    None                                     Not Blocking         SYS_JOURNAL_1664589
 3075 DML                        Row-S (SS)                               Share                                    Not Blocking         SYS_JOURNAL_1664589
 3075 DL                         Row-X (SX)                               None                                     Not Blocking         TEST
 3075 DL                         Row-X (SX)                               None                                     Not Blocking         TEST
 3075 DML                        Share                                    None                                     Not Blocking         TEST
 3075 DML                        Row-S (SS)                               Share                                    Not Blocking         TEST
 3900 DML                        None                                     Row-X (SX)                               Not Blocking         TEST

From the above output we can see that first session is 3204 which is running insert.
Session 2 is 3075 which is running index rebuild and is waiting for “DML share” lock
session 3 is 3900 running update DML and waiting for “DML Row-X” lock

If we see which session is blocking what we see below rows


Logn Ora    SQL/Prev                     OS                                                   Call
><    Sid-Ser-S Time User   Hash       Module            User   Svr-Pgm    Machine      HR Resource           Elap Ctim Locked Object
--------------- ---- ------ ---------- ----------------- ------ ---------- ------------ -- ------------------ ---- ---- --------------------
>  3204,23884-I 0953 ADVAIT 0          SQL*Plus          advait 7924-orac  db-fc-admin- 3  TM:1664558-0       448s 448s 1664558
 < 3075,20427-A 0959 ADVAIT 3645454058 SQL*Plus          advait 15432-orac db-fc-admin-  4                    433s 432s 1664558
 < 3900,30565-A 0959 ADVAIT 4227999514 SQL*Plus          advait 16004-orac db-fc-admin-  3                    408s 408s 1664558

Above output is generated by my custom scripts.

It says that 3204 is parent session and 3204 and 3900 is waiting on 3204.

In case of 10g if we commit session 1 (sid 3204 running insert), it will allow online index rebuild to continue and get the lock.
Session 3 will still continue to wait until session 2 running online index rebuild releases the lock.

After some time session 2 will start with index rebuild, it will take lock for very short period of time and releases the lock.
Session 3 (update DML) will aquire the lock after session 2 (index rebuild) releases the lock.

Session 3 completed while session 2 (index rebuild) does table scan.

SQL>update test set OWNER = 'DEO2' where OWNER = 'DEO'
 2  ;
1 rows updated.
SQL>

Now after session 3 DML completes, we didnt commit session 3. We are waiting for session 2 to complete now.

We can check v$session_longops to see current operation for index rebuild


SQL>select sid, OPNAME, SOFAR, TOTALWORK, TIME_REMAINING, ELAPSED_SECONDS from v$session_longops where sid = 3075;

 SID OPNAME                                                                SOFAR  TOTALWORK TIME_REMAINING ELAPSED_SECONDS
------ ---------------------------------------------------------------- ---------- ---------- -------------- ---------------
 3075 Sort Output                                                           25011      25011              0              15
 3075 Table Scan                                                            56036      56036              0              61

2 rows selected.

So we can see that v$session_longops that table scan for index rebuild completed. But still the session 2 for index rebuild is hanging.

If we run above query to see the locks it gives below output


ORACLE_ID LOCK_TYPE                  LOCK_HELD                                LOCK_REQUESTED                           STATUS               OBJECT_NAME
---------- -------------------------- ---------------------------------------- ---------------------------------------- -------------------- ------------------------------
 3075 DL                         Row-X (SX)                               None                                     Not Blocking         SYS_JOURNAL_1664589
 3075 DL                         Row-X (SX)                               None                                     Not Blocking         SYS_JOURNAL_1664589
 3075 DML                        Share                                    None                                     Not Blocking         SYS_JOURNAL_1664589
 3075 DML                        Row-S (SS)                               Share                                    Not Blocking         SYS_JOURNAL_1664589
 3075 Temp Segment               Exclusive                                None                                     Not Blocking         SYS_JOURNAL_1664589
 3075 Transaction                Exclusive                                None                                     Not Blocking         SYS_JOURNAL_1664589
 3075 DL                         Row-X (SX)                               None                                     Not Blocking         TEST
 3075 DL                         Row-X (SX)                               None                                     Not Blocking         TEST
 3075 DML                        Share                                    None                                     Not Blocking         TEST
 3075 DML                        Row-S (SS)                               Share                                    Not Blocking         TEST
 3075 Temp Segment               Exclusive                                None                                     Not Blocking         TEST
 3075 Transaction                Exclusive                                None                                     Not Blocking         TEST
 3900 DML                        Row-X (SX)                               None                                     Blocking             TEST

13 rows selected.

&nbsp;

So sid 3900 is the blocking session. This is session 3 which has run update query but havent commited.
So online index rebuild is waiting to acquire lock second time at the end. This is the time when it has to do the merging.

So after we commit session 3, session 2 doing online index rebuild will acquire the lock and will complete.

11g behaviour:

With 11g, significant changes were introduced to address all these problems.

Oracle will still wait for long running transactions to complete before starting the OIB process, but it will not block the upcoming DML’s. Oracle will not use exclusive table lock (mode 6) for OIB, which will eliminate any DML hang situations.

As soon as OIB process is initiated, Oracle will create IOT journal table and use internal trigger on the underlying table to keep track of the changes.

Once the journal table is created and the long running transactions are completed, Oracle will create the index by reading the table blocks in “CURRENT” mode, which means any committed changes in a block happened before the current SCN are read instead of the old way of accessing the blocks as of journal table creation time. This will virtually eliminate chance of running into rollback segment too small errors.

Oracle will recreate the internal trigger to directly modify the target index structure once the index build is completed (but the merge job is still pending). This means all user sessions will now directly update the target index instead of updating the journal table. Any change coming through would first be checked in the journal table before consolidating the change for the target index (this is best explanied with the example below).

Record “A” was inserted with rowid “RID” before the merge phase and hence it is tracked in the journal table.
Record “A” was deleted during the merge phase. Now the user session will read the journal table by record “A” with rowid “RID” and apply that change to the target index before deleteing the same record. It will also mark that record as consumed (i.e. deleted) in the IOT journal table leaf block.

While the user sessions continue to update the target index with the ongoing changes, OIB session will do the merge of journal table changes into the final index, as there will not be any ongoing DML changes being tracked in the journal table, this process will complete faster than before (i.e. pre 11g).

Lets take an example. In case of 11g we have MYTAB table and MYTAB_IDX index


SQL>select sum(bytes)/1024/1024 from dba_segments where segment_name = 'MYTAB';

SUM(BYTES)/1024/1024
--------------------
 406.5

SQL>select sum(bytes)/1024/1024 from dba_segments where segment_name = 'MYTAB_IDX';

SUM(BYTES)/1024/1024
--------------------
 152.5

In this case also do the following activity

From session 1 run a DML (insert statement) – SID 2213
From session 2 run index rebuild online command – SID 2222
From session 3 run a DML (update statement) – SID 2177

The current status in v$lock shows following


ORACLE_ID LOCK_TYPE                  LOCK_HELD                                LOCK_REQUESTED                           STATUS               OBJECT_NAME
---------- -------------------------- ---------------------------------------- ---------------------------------------- -------------------- ------------------------------
 2177 AE                         Share                                    None                                     Not Blocking         MYTAB
 2177 DML                        Row-X (SX)                               None                                     Not Blocking         MYTAB
 2177 Transaction                Exclusive                                None                                     Not Blocking         MYTAB
 2213 AE                         Share                                    None                                     Not Blocking         MYTAB
 2213 DML                        Row-X (SX)                               None                                     Not Blocking         MYTAB
 2213 Transaction                Exclusive                                None                                     Blocking             MYTAB
 2222 AE                         Share                                    None                                     Not Blocking         MYTAB
 2222 OD                         Exclusive                                None                                     Not Blocking         MYTAB
 2222 DL                         Row-X (SX)                               None                                     Not Blocking         MYTAB
 2222 DL                         Row-X (SX)                               None                                     Not Blocking         MYTAB
 2222 OD                         Share                                    None                                     Not Blocking         MYTAB
 2222 Transaction                None                                     Share                                    Not Blocking         MYTAB
 2222 DML                        Row-S (SS)                               None                                     Not Blocking         MYTAB
 2222 DML                        Share                                    None                                     Not Blocking         MYTAB
 2222 Transaction                Exclusive                                None                                     Not Blocking         MYTAB
 2222 AE                         Share                                    None                                     Not Blocking         SYS_JOURNAL_98701
 2222 OD                         Exclusive                                None                                     Not Blocking         SYS_JOURNAL_98701
 2222 DL                         Row-X (SX)                               None                                     Not Blocking         SYS_JOURNAL_98701
 2222 DL                         Row-X (SX)                               None                                     Not Blocking         SYS_JOURNAL_98701
 2222 OD                         Share                                    None                                     Not Blocking         SYS_JOURNAL_98701
 2222 Transaction                None                                     Share                                    Not Blocking         SYS_JOURNAL_98701
 2222 DML                        Row-S (SS)                               None                                     Not Blocking         SYS_JOURNAL_98701
 2222 DML                        Share                                    None                                     Not Blocking         SYS_JOURNAL_98701
 2222 Transaction                Exclusive                                None                                     Not Blocking         SYS_JOURNAL_98701

Now if you compare it with 10g we see significant differences.

1) We see lot of extra locks in 11g compared to 10g
2) and the most important differences is that in case of 10g if you see last 2 rows in first output of v$lock we see


ORACLE_ID LOCK_TYPE                  LOCK_HELD                                LOCK_REQUESTED                           STATUS               OBJECT_NAME
---------- -------------------------- ---------------------------------------- ---------------------------------------- -------------------- ------------------------------
 3075 DML                        Row-S (SS)                               Share                                    Not Blocking         TEST
 3900 DML                        None                                     Row-X (SX)                               Not Blocking         TEST

and same in 11g is


ORACLE_ID LOCK_TYPE                  LOCK_HELD                                LOCK_REQUESTED                           STATUS               OBJECT_NAME
---------- -------------------------- ---------------------------------------- ---------------------------------------- -------------------- ------------------------------
 2177 AE                         Share                                    None                                     Not Blocking         MYTAB
 2177 DML                        Row-X (SX)                               None                                     Not Blocking         MYTAB
 2177 Transaction                Exclusive                                None                                     Not Blocking         MYTAB
 2213 AE                         Share                                    None                                     Not Blocking         MYTAB
 2213 DML                        Row-X (SX)                               None                                     Not Blocking         MYTAB
 2213 Transaction                Exclusive                                None                                     Blocking             MYTAB
 .
 .
 2222 Transaction                None                                     Share                                    Not Blocking         MYTAB

In case of 10g if you see 3075 is the one which is doing an index rebuild and is waiting on initial sid 3204. Also 3rd session (3900) running DML is waiting for “Row-X (SX)” lock.
In case of 11g if you see 2222 is the one which is doing an index rebuild and is waiting on initial sid 2213. Also 3rd session (2177) running DML already got “Row-X (SX)” lock and is not waiting for anything.

So in case of 11g if index rebuild is waiting for initial lock to acquire, it does not block incomming DMLs.

After I commit 1st session I see that none of the session is now blocking index rebuild operation and so it can acquire initial lock


ORACLE_ID LOCK_TYPE                  LOCK_HELD                                LOCK_REQUESTED                           STATUS               OBJECT_NAME
---------- -------------------------- ---------------------------------------- ---------------------------------------- -------------------- ------------------------------
 2177 AE                         Share                                    None                                     Not Blocking         MYTAB
 2177 DML                        Row-X (SX)                               None                                     Not Blocking         MYTAB
 2177 Transaction                Exclusive                                None                                     Not Blocking         MYTAB
 2222 AE                         Share                                    None                                     Not Blocking         SYS_JOURNAL_98701
 2222 OD                         Exclusive                                None                                     Not Blocking         SYS_JOURNAL_98701
 2222 DL                         Row-X (SX)                               None                                     Not Blocking         SYS_JOURNAL_98701
 2222 DL                         Row-X (SX)                               None                                     Not Blocking         SYS_JOURNAL_98701
 2222 OD                         Share                                    None                                     Not Blocking         SYS_JOURNAL_98701
 2222 DML                        Row-S (SS)                               None                                     Not Blocking         SYS_JOURNAL_98701
 2222 DML                        Share                                    None                                     Not Blocking         SYS_JOURNAL_98701
 2222 Transaction                Exclusive                                None                                     Not Blocking         SYS_JOURNAL_98701
 2222 AE                         Share                                    None                                     Not Blocking         MYTAB
 2222 OD                         Exclusive                                None                                     Not Blocking         MYTAB
 2222 DL                         Row-X (SX)                               None                                     Not Blocking         MYTAB
 2222 DL                         Row-X (SX)                               None                                     Not Blocking         MYTAB
 2222 OD                         Share                                    None                                     Not Blocking         MYTAB
 2222 DML                        Row-S (SS)                               None                                     Not Blocking         MYTAB
 2222 DML                        Share                                    None                                     Not Blocking         MYTAB
 2222 Transaction                Exclusive                                None                                     Not Blocking         MYTAB

My session 3 is already complete and I commited that session now. session 3 had no dependency on any session.

After index rebuild start (its doing table scan), I run one more DML from session 3, but I dont commit. In this case index rebuild will hang again at the end waiting for exclusive lock on table.
Unless session 3 gives the lock index rebuild cannot proceed.

Important change here between 10g and 11g is that if we start 4th session while index rebuild waits for 2nd time for lock and if I commit session 3, in case of 10g index rebuild will get precedence and it will acquire lock blocking session 4.
In case of 11g session 4 will get presedence and will acquire lock for DML, where as index rebuild will wait further until all DML sessions are complete and lock is available for it to acquire. So online index rebuild will prioritize all other sessions before him to acquire locks.

From v$session_longops we can see that tablescan operation completed


SQL>select sid, OPNAME, SOFAR, TOTALWORK, TIME_REMAINING, ELAPSED_SECONDS from v$session_longops where sid = 2222 and opname not like 'RMAN%';

 SID OPNAME                              SOFAR  TOTALWORK TIME_REMAINING ELAPSED_SECONDS
---------- ------------------------------ ---------- ---------- -------------- ---------------
 2222 Table Scan                          51736      51736              0              12
 2222 Sort Output                         21422      21422              0               7

Now online index rebuild session is waiting for session 3 (SID 2177), which I started which table scan operation was inprogress


ORACLE_ID LOCK_TYPE                  LOCK_HELD                                LOCK_REQUESTED                           STATUS               OBJECT_NAME
---------- -------------------------- ---------------------------------------- ---------------------------------------- -------------------- ------------------------------
 2177 AE                         Share                                    None                                     Not Blocking         MYTAB
 2177 DML                        Row-X (SX)                               None                                     Not Blocking         MYTAB
 2177 Transaction                Exclusive                                None                                     Blocking             MYTAB
 2222 AE                         Share                                    None                                     Not Blocking         SYS_JOURNAL_98701
 2222 OD                         Exclusive                                None                                     Not Blocking         SYS_JOURNAL_98701
 2222 DL                         Row-X (SX)                               None                                     Not Blocking         SYS_JOURNAL_98701
 2222 DL                         Row-X (SX)                               None                                     Not Blocking         SYS_JOURNAL_98701
 2222 OD                         Share                                    None                                     Not Blocking         SYS_JOURNAL_98701
 2222 Transaction                None                                     Share                                    Not Blocking         SYS_JOURNAL_98701
 2222 DML                        Row-S (SS)                               None                                     Not Blocking         SYS_JOURNAL_98701
 2222 DML                        Share                                    None                                     Not Blocking         SYS_JOURNAL_98701
 2222 Transaction                Exclusive                                None                                     Not Blocking         SYS_JOURNAL_98701
 2222 AE                         Share                                    None                                     Not Blocking         MYTAB
 2222 OD                         Exclusive                                None                                     Not Blocking         MYTAB
 2222 DL                         Row-X (SX)                               None                                     Not Blocking         MYTAB
 2222 DL                         Row-X (SX)                               None                                     Not Blocking         MYTAB
 2222 OD                         Share                                    None                                     Not Blocking         MYTAB
 2222 Transaction                None                                     Share                                    Not Blocking         MYTAB
 2222 DML                        Row-S (SS)                               None                                     Not Blocking         MYTAB
 2222 DML                        Share                                    None                                     Not Blocking         MYTAB
 2222 Transaction                Exclusive                                None                                     Not Blocking         MYTAB

Unless I commit that session index rebuild will wait. If I start another session now (session 4) and commit session 3, index rebuild will still wait for session 4 to complete.
Like this it can continue to wait until all transactions are done.

Example I started session 4 (SID 2223 ) and ran DML, commited in session 3

now index rebuild session (SID 2222) is waiting for session 4.


ORACLE_ID LOCK_TYPE                  LOCK_HELD                                LOCK_REQUESTED                           STATUS               OBJECT_NAME
---------- -------------------------- ---------------------------------------- ---------------------------------------- -------------------- ------------------------------
 2223 AE                         Share                                    None                                     Not Blocking         MYTAB
 2223 DML                        Row-X (SX)                               None                                     Not Blocking         MYTAB
 2223 Transaction                Exclusive                                None                                     Blocking             MYTAB
 2222 Transaction                None                                     Share                                    Not Blocking         SYS_JOURNAL_98701
 2222 AE                         Share                                    None                                     Not Blocking         SYS_JOURNAL_98701
 2222 OD                         Exclusive                                None                                     Not Blocking         SYS_JOURNAL_98701
 2222 OD                         Share                                    None                                     Not Blocking         SYS_JOURNAL_98701
 2222 DML                        Row-S (SS)                               None                                     Not Blocking         SYS_JOURNAL_98701
 2222 DML                        Share                                    None                                     Not Blocking         SYS_JOURNAL_98701
 2222 Transaction                Exclusive                                None                                     Not Blocking         SYS_JOURNAL_98701
 2222 Transaction                None                                     Share                                    Not Blocking         MYTAB
 2222 AE                         Share                                    None                                     Not Blocking         MYTAB
 2222 OD                         Exclusive                                None                                     Not Blocking         MYTAB
 2222 OD                         Share                                    None                                     Not Blocking         MYTAB
 2222 DML                        Row-S (SS)                               None                                     Not Blocking         MYTAB
 2222 DML                        Share                                    None                                     Not Blocking         MYTAB
 2222 Transaction                Exclusive                                None                                     Not Blocking         MYTAB

So main difference in case of online index rebuild procedure is online index rebuild process gives precedence to other DML sessions to acquire locks. The process has become less intrusive now.

Hope this helps !!

Oracle Database 10g, Oracle Database 11g , ,

WARNING: inbound connection timed out (ORA-3136)

Leave a Comment Posted by on March 17, 2011

Many times I faced this issue “WARNING: inbound connection timed out (ORA-3136)” in alert log and if you are using data guard, then you can see that dgmgrl status will show failed because standby is not able to sync up with primary.

The “WARNING: inbound connection timed out (ORA-3136)” in the alert log indicates that the client was not able to complete it’s authentication within the period of time specified by parameter SQLNET.INBOUND_CONNECT_TIMEOUT.

From 10.2 onwards the default value of this parameter is 60 seconds, hence if the client is not able authenticate within 60 secs , the warning would appear in the alert log and the client connection will be terminated.

Root cause of this issue could be

1. malicious client is flooding database servers with connect requests that consumes resources

2. The server receives a valid client connection request but the client takes a long time to authenticate more than the default 60 seconds.

3. The DB server is heavily loaded due to which it cannot finish the client logon within the timeout specified

As a workaround to avoid only this warning messages, you can set the parameters SQLNET.INBOUND_CONNECT_TIMEOUT=0 in $ORACLE_HOME/network/admin/sqlnet.ora file

Once you set this parameter the warnings in alert log will go away. If you are using data guard, may be you want to enable the databases on which you were getting warnings.

Hope this helps !!

ORA Errors, Oracle Database 10g , , ,

Oracle Database 11g new feature – Automatic Memory Management

1 Comment Posted by on January 8, 2011

Automatic Memory Management was a new feature introduced in 10g. With 10g release oracle has come up with anew parameter called sga_target which was used to automatically manage the memeory inside SGA.
The components which were managed by sga_target are db_cache_size, shared_pool_size, large_pool_size, java_pool_size and streams_pool_size

With 11g, Oracle went a step further to manage both SGA as well as PGA automatically. Oracle database 11g introduced 2 new parameters – memory_target and memory_max_target

The memory_target Parameter

The memory_target parameter is somewhat a combination of the sga_target parameter value and the pga_aggregate_target parameter, representing the total amount of memory that Oracle has to allocate between the various SGA and PGA structures. The memory_target parameter is dynamic and can be changed up to and including the value of memory_max_target, which we discuss next.

The memory_max_target Parameter

The memory_max_target parameter allows you to dynamically change the value of the parameter memory_target within the confines of memory_max_target. Thus you can adjust the total amount of memory available to the database as a whole at any time.

NOTE: The memory_target and memory_max_target parameters cannot be used when LOCK_SGA has been set. Also memory_target and memory_max_target cannot be used in conjunction with huge pages on Linux.

Following fig show the hierarchy of memory parameters and components that it tunes. This fig is taken from Robert Freeman book – Oracle database 11g: New features for Administrator.

Also below fig from same book show the effect of setting memroy_target, memroy_max_target and sga_target.

While setting these new parameters (memroy_target and max_memory_target), one has to be careful. A general advice would be to set sga_target and pga_aggregate_target to a minimum fixed value and set memrory_target. Oracle will automatically increase sga_target and pga_aggregate_target to the desired level.

If you are upgrading the existing 10g database to 11g and want to keep the current value of sga_target and pga_aggregate_target, than make sure you set the value of memroy_target >= (sga_target + pga_aggregate_target).

With new hirarchy in memeory management, Oracle has also introduced new memroy advisory. You can view V$MEMORY_TARGET_ADVICE view to get advice on the optimal value of memroy_target parameter. This view will show advisory data only after you set memory_target parameter.

Hope this helps !!

Oracle Database 11g , , ,

Checking Database Growth Trend

6 Comments Posted by on January 7, 2011

One of the simple way to find the growth of the database is using v$datafile view. following is the simple query and its output which gives the growth trend in month and year

</code>

SQL>select to_char(CREATION_TIME,'RRRR') year, to_char(CREATION_TIME,'MM') month, round(sum(bytes)/1024/1024/1024) GB
 2  from   v$datafile
 3  group by  to_char(CREATION_TIME,'RRRR'),   to_char(CREATION_TIME,'MM')
 4  order by   1, 2;

YEAR MO         GB
---- -- ----------
2004 10         14
2004 11         66
2004 12         47
2005 01         15
2005 02         14
2005 03         34
2005 04         14
2005 05         30
2005 06          5
2005 07          3
2005 08          9

YEAR MO         GB
---- -- ----------
2005 10          4
2005 12         20
2006 01         11
2006 02         15
2006 03         14
2006 04         16
2006 05         13
2006 06          0
2006 07          4
2006 08         17
2006 09          5

YEAR MO         GB
---- -- ----------
2006 10          6
2006 11          0
2006 12         30
2007 01          4
2007 02          4
2007 03          6
2007 04          6
2007 05         12
2007 06         15
2007 07         62
2007 08         29

YEAR MO         GB
---- -- ----------
2007 09         18
2007 11         20
2007 12          8
2008 03          8
2008 05          4
2008 08         11
2008 09         14
2008 10          4
2008 11         36
2008 12         49
2009 01          8

YEAR MO         GB
---- -- ----------
2009 02         38
2009 03         35
2009 04         54
2009 05         73
2009 08          1
2009 09         12
2009 10         78
2009 11        105
2009 12        113
2010 01        109
2010 02         79

YEAR MO         GB
---- -- ----------
2010 03         84
2010 05        243
2010 06        225
2010 07        244
2010 08        152
2010 09         40
2010 10        176
2010 11         76
2010 12        107

64 rows selected.

<code>

Oracle Database 10g , ,

2010 in review

Leave a Comment Posted by on January 3, 2011

The stats helper monkeys at WordPress.com mulled over how this blog did in 2010, and here’s a high level summary of its overall blog health:

Healthy blog!

The Blog-Health-o-Meter™ reads Wow.

Crunchy numbers

Featured image

The Louvre Museum has 8.5 million visitors per year. This blog was viewed about 260,000 times in 2010. If it were an exhibit at The Louvre Museum, it would take 11 days for that many people to see it.

 

In 2010, there were 23 new posts, growing the total archive of this blog to 158 posts. There was 1 picture uploaded, taking a total of 45kb.

The busiest day of the year was November 15th with 1,174 views. The most popular post that day was Uploading excel sheet using Oracle Application Express (APEX).

Where did they come from?

The top referring sites in 2010 were forums.oracle.com, google.co.in, en.wordpress.com, google.com, and zimbio.com.

Some visitors came searching, mostly for ora-04091, fndcpass, ora-12705: cannot access nls data files or invalid environment specified, oracle 9i architecture diagram, and oracle architecture.

Attractions in 2010

These are the posts and pages that got the most views in 2010.

1

Uploading excel sheet using Oracle Application Express (APEX) May 2008
87 comments

2

Reducing datafile size to recover free space – Oracle Database 10g February 2008
14 comments

3

Setting up Oracle DataGuard for 10g June 2007
28 comments

4

Upgrading to Oracle Database 11g September 2007
23 comments

5

OPATCH Utility (Oracle RDBMS Patching) August 2008
13 comments

Analyze Index Vs. Gather_Index_Stats – Oracle Database 10gp

Leave a Comment Posted by on November 1, 2010

One of the difference I noticed about analyze index command and gather_index_stats procedure is that when we use analyze index command if updates the index statistics with number of leaf blocks equals the number of leaf blocks below HWM.

However if we use gather_index_stats statistics shows number of leaf blocks equals number of leaf blocks that actually has the data.

Here is the illustration for the same.

 

Create a table

SQL> create table t1 pctfree 99 pctused 1  
2  as  
3  select rownum id,  
4  trunc(100 * dbms_random.normal) val,  
5  rpad('X',100) padding  
6  from all_objects where rownum <= 10000;

Table created.

I intentionally created table with pctfree as 99 so that each block will have 1 row and it uses 10000 blocks to store 10000 rows.

Create an index

SQL> create index t1_i on t1(val) ;
Index created.

Check the number of leaf blocks

SQL> select BLEVEL, LEAF_BLOCKS, DISTINCT_KEYS from dba_indexes where index_name = 'T1_I';

 BLEVEL LEAF_BLOCKS DISTINCT_KEYS
---------- ----------- -------------         
1          21           563

We have around 1600 rows whose val > 100

SQL> select count(*) from t1 where val > 100;
 COUNT(*)
----------      
  1598

Delete rows from table, which will also delete entries from index and some of the leaf blocks will get empty.

SQL> delete from t1 where val > 100;
1598 rows deleted.

Analyze index

SQL> analyze index t1_i validate structure;
Index analyzed.

Check again the number of leaf blocks

SQL> select BLEVEL, LEAF_BLOCKS, DISTINCT_KEYS from dba_indexes where index_name = 'T1_I';
 BLEVEL LEAF_BLOCKS DISTINCT_KEYS
---------- ----------- -------------         
 1          21           563

Number of leaf blocks reported are still 21

Gather stats on index

SQL> exec dbms_stats.gather_index_stats('ADVAITD_DBA','T1_I');
PL/SQL procedure successfully completed.

Check the number of leaf blocks again

SQL> select BLEVEL, LEAF_BLOCKS, DISTINCT_KEYS from dba_indexes where index_name = 'T1_I';
 BLEVEL LEAF_BLOCKS DISTINCT_KEYS
---------- ----------- -------------         
 1          18           379

Now if we see the number of leaf blocks reported are 18. This plays quiet a big role for optimizer in creating plans for queries. If we don’t have correct stats for the index, it may lead to expensive explain plans.

So its better to use gather_index_stats rather than analyze index.

Hope this helps !!

Database 10g Performance Tuning, Oracle Database 10g , ,

Oracle E-Business Suite R12 – Profiles

1 Comment Posted by on November 1, 2010

Profile Options in Oracle Application Object Library

This section lists each profile option in Oracle Application Object Library. These profile options are grouped into categories based on their functional area and are available to every product in Oracle Applications. For each profile option, we give a brief overview of how Oracle Application Object Library uses the profile’s setting.

Unless otherwise noted, a profile option uses the Security hierarchy type.

A table is provided for most profile options that lists the access levels for the profile option (at which levels the system administrator can set the profile option). For Security profile options, there are four possible levels at which system administrators can view and update a profile option value: site, application, responsibility, and user. This table lists whether the profile option’s value is visible at each of these levels, and whether it is updatable at each level.

Concurrent Processing Execution

The internal name for this profile category is FND_CP_EXECUTION.

Concurrent:Active Request Limit

You can limit the number of requests that may be run simultaneously by each user. or for every user at a site. If you do not specify a limit, no limit is imposed.

Users cannot see or update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application No No
Responsibility No No
User Yes Yes

The internal name for this profile option is CONC_REQUEST_LIMIT.

Concurrent:Attach URL

Setting this option to “Yes” causes a URL to be attached to request completion notifications. When a user submits a request, and specifies people to be notified in the Defining Completion Options region, everyone specified is sent a notification when the request completes. If this profile option is set to Yes, a URL is appended to the notification that enables them to view the request results online.

Only the System Administrator can update this profile option.

Users can see but not update this profile option.

This profile options is visible at all levels but can only updated at the Site level.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is CONC_ATTACH_URL.

Concurrent:Conflicts Domain

Specify a conflict domain for your data. A conflict domain identifies the data where two incompatible programs cannot run simultaneously.

Users can see but not update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is CONC_CD_ID.

Concurrent:Collect Request Statistics

Set this profile option to “Yes” to have statistics for your runtime concurrent processes collected.

To review the statistics you must run the Purge Concurrent Request and/or Manager Data program to process the raw data and have it write the computed statistics to the FND_CONC_STAT_SUMMARY table. You can then retrieve your data from this table using SQL*PLUS or on a report by report basis using the Diagnostics window from the Requests window.

Users cannot see nor change this profile option.

This profile option is visible at all levels but can only be updated at the Site level.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes No
User Yes No

The internal name for this profile option is CONC_REQUEST_STAT.

Concurrent:Date Parameter Increment Option

Use this profile to control how date parameters are automatically incremented for concurrent requests. In the Standard Request Submission window, the user can specify if to run a request periodically. The user can then specify that the interval be based on the start date of the requests, or specify the interval using a unit of time and number of units.

If this profile is set to “Start Date” then the date parameters for a given request will be incremented according to the difference between the requested start date of the request and the requested start date of the previous request. If this profile is set to “Resubmit” any date parameters are incremented according to the current request’s date parameter and the amount of time represented by the number of units (RESUBMIT_INTERVAL) and the unit of time (RESUBMIT_INTERVAL_UNIT_CODE).

Level Visible Allow Update
Site Yes Yes
Application Yes No
Responsibility Yes No
User Yes No

The internal name for this profile option is CONC_DATE_INCREMENT_OPTION.

Concurrent:Hold Requests

You can automatically place your concurrent requests on hold when you submit them.

The default is “No”. The concurrent managers run your requests according to the priority and start time specified for each.

Changing this value does not affect requests you have already submitted.

“Yes” means your concurrent requests and reports are automatically placed on hold. To take requests off hold, you:

  • Navigate to the Requests window to select a request
  • Select the Request Control tabbed region
  • Uncheck the Hold check box

Users can see and update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is CONC_HOLD.

Concurrent:Multiple Time Zones

“Yes” sets the default value to ‘Sysdate-1′ for the ‘Schedules Start Date’ used by request submissions. Sysdate-1 ensures that you request is scheduled immediately regardless of which time zone your client session is running in. You should use this profile option when the client’s session is running in a different time zone than the concurrent manager’s session.

Users cannot see nor change this profile option.

This profile option is visible at all four levels and updatable at the Site level.

Level Visible Allow Update
Site Yes Yes
Application No No
Responsibility No No
User No No

The internal name for this profile option is CONC_MULTI_TZ.

Concurrent:Print on Warning

Set this profile option to “Yes” if you want concurrent request output to be printed if the requests completes with a status of Warning.

Users can see and update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is CONC_PRINT_WARNING.

Concurrent:Report Copies

You can set the number of output copies that print for each concurrent request. The default is set to 1.

  • Changing this value does not affect requests that you have already submitted.

Users can see and update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is CONC_COPIES.

Concurrent:Request Priority

This displays the default priority number for your concurrent requests. Only a system administrator can change your request priority.

Requests normally run according to start time, on a “first-submitted, first-run” basis. Priority overrides request start time. A higher priority request starts before an earlier request.

Priorities range from 1 (highest) to 99 (lowest). The standard default is 50.

Users can see this profile option, but they cannot update it.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is CONC_PRIORITY.

Concurrent:Save Output

The Concurrent: Save Output profile is used to determine whether the default behavior of certain concurrent programs should be to save or delete their output files. This only affects concurrent programs that were created in the character mode versions of Oracle Applications and that have a null value for “Save Output”.

  • “Yes” saves request outputs.
  • Some concurrent requests do not generate an output file.
  • If your request output is saved, you can reprint a request. This is useful when requests complete with an Error status, for example, the request runs successfully but a printer malfunctions.
  • Changing this value does not affect requests you have already submitted.

Users can see and update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is CONC_SAVE_OUTPUT.

Concurrent:Sequential Requests

You can force your requests to run one at a time (sequentially) according to the requests’ start dates and times, or allow them to run concurrently, when their programs are compatible.

  • Concurrent programs are incompatible if simultaneously accessing the same database tables incorrectly affects the values each program retrieves.
  • When concurrent programs are defined as incompatible with one another, they cannot run at the same time.

“Yes” prevents your requests from running concurrently. Requests run sequentially in the order they are submitted.

“No” means your requests can run concurrently when their concurrent programs are compatible.

Changing this value does not affect requests you have already submitted.

Users can see and update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is CONC_SINGLE_THREAD.

Concurrent:Wait for Available TM

You can specify the maximum number of seconds that the client will wait for a given transaction manager (TM) to become available before moving on to try a different TM.

Users can see and update this profile option.

This profile option is visible and updatable at the site and application levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility No No
User No No

The internal name for this profile option is CONC_TOKEN_TIMEOUT.

Concurrent Processing File Server

The internal name for this profile category is FND_CP_FILE_SERVER.

RRA:Delete Temporary Files

When using a custom editor to view a concurrent output or log file, the Report Review Agent will make a temporary copy of the file on the client. Set this profile to “Yes” to automatically delete these files when the user exits Oracle Applications.

Only the System Administrator can update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is FS_DELETE.

RRA:Enabled

Set this user profile to “Yes” to use the Report Review Agent to access files on concurrent processing nodes.

Only the System Administrator can update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is FS_ENABLED.

RRA: Service Prefix

Using this new profile option allows you to override the default service name prefix (FNDFS_) assigned to the Report Review Agent. By assigning a new prefix to the Report Review Agent you can avoid having multiple instances of the Applications share executables.

Valid values for this option must be nine characters or less and use only alphanumeric characters or the underscore. We recommend using the underscore character as the last character of your value as in the default value “FNDFS_”.

Users cannot see or update this profile option.

This profile option is visible and updatable at the site level only.

Level Visible Allow Update
Site Yes Yes
Application No No
Responsibility No No
User No No

The internal name for this profile option is FS_SVC_PREFIX.

Attention: GLDI will not support the “RRA: Service Prefix” profile until release 4.0 and so uses the default prefix “FNDFS_” regardless of the value entered for the profile option. Consequently, you must ensure that at least one of your Report Review Agents maintains the default prefix in order for GLDI to access the application executables.

RRA:Maximum Transfer Size

Specify, in bytes, the maximum allowable size of files transferred by the Report Review Agent, including those downloaded by a user with the “Copy File…” menu option in the Oracle Applications Report File Viewer and those “temporary” files which are automatically downloaded by custom editors. For example, to set the size to 64K you enter 65536. If this profile is null, there is no size limit.

Only the System Administrator can update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is FS_MAX_TRANS.

Concurrent Processing Manager

The internal name for this profile category is FND_CP_MANAGER.

Concurrent:Debug Flags

Your Oracle support representative may access this profile option to debug Transaction Managers. Otherwise, it should be set to null.

Users cannot see nor change this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is CONC_DEBUG.

Concurrent:GSM Enabled

Use this profile option to enable Generic Service Management.

Level Visible Allow Update
Site Yes Yes
Application No No
Responsibility No No
User No No

The internal name for this profile option is CONC_GSM_ENABLED.

Concurrent:OPP Process Timeout

This profile option specifies the amount of time the manager waits for the OPP to actually process the request.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is CONC_PP_PROCESS_TIMEOUT.

Concurrent:OPP Response Timeout

This profile option specifies the amount of time a manager waits for the OPP to respond to its request for post processing.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is CONC_PP_RESPONSE_TIMEOUT.

Concurrent:PCP Instance Check

This profile option controls whether Parallel Concurrent Processing (PCP) will be sensitive to the state (up or down) of the database instance connected to on each middle-tier node.

When this profile option is set to “OFF”, PCP will not provide database instance failover support; however, it will provide middle-tier node failover support when a node goes down.

Level Visible Allow Update
Site Yes Yes
Application No No
Responsibility No No
User No No

The internal name for this profile option is CP_INSTANCE_CHECK.

Concurrent Processing Submission

The internal name for this profile category is FND_CP_SUBMISSION.

Concurrent:Allow Debugging

This profile option allows debug options to be accessed by the user at submit time.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is FND_CONC_ALLOW_DEBUG.

Concurrent:Enable Request Submission in View Mode

Use this profile option to enable Request Submission in View Requests mode.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is CONC_FNDRSRUN_MODE.

Concurrent:Report Access Level

Determines access privileges to report output files and log files generated by a concurrent program. This profile option can be set by a System Administrator to User or Responsibility.

If your Concurrent:Report Access Level profile option is set to “User” you may:

  • View the completed report output for your requests online
  • View the diagnostic log file for those requests online. (system administrator also has this privilege)
  • Reprint your completed reports, if the Concurrent:Save Output profile option is set to “Yes”.
  • If you change responsibilities, then the reports and log files available for online review do not change.

If your Concurrent:Report Access Level profile option is set to “Responsibility”, access to reports and diagnostic log files is based on the your current responsibility.

  • If you change responsibilities, then the reports and log files available for online review change to match your new responsibility. You can always see the output and log files from reports you personally submit, but you also see reports and log files submitted by any user from the current responsibility.

Users can see this profile option, but they cannot update it.

This profile option is visible and updatable at the site, responsibility, and user levels.

Level Visible Allow Update
Site Yes Yes
Application No No
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is CONC_REPORT_ACCESS_LEVEL.

Concurrent:Request Start Time

You can set the date and time that your requests are available to start running.

  • If the start date and time is at or before the current date and time, requests are available to run immediately.
  • If you want to start a request in the future, for example, at 3:45 pm on June 12, 2002, you enter 2002/06/12 15:45:00 as the profile option value.

Attention: You must ensure that this value is in canonical format (YYYY/MM/DD HH24:MI:SS) to use the Multilingual Concurrent Request feature.

  • You must include both a date and a time.
  • Changing this value does not affect requests that you have already submitted.
  • Users can override the start time when they submit requests. Or, this profile option can be left blank and users will be prompted for a start time when they submit requests.

Users can see and update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is CONC_REQ_START.

Concurrent: Show Requests Summary After Each Request Submission

Using this new profile option, you can choose to either have the Requests Summary displayed each time you submit a request, or retain the request submission screen.

The default is “Yes”. “Yes” means the Requests Summary screen is displayed each time you submit a request.

If you choose “No”, a decision window is opened asking you if you wish to submit another request. When you choose to submit another request you are returned to the submission window and the window is not cleared, allowing you to easily submit copies of the same request with minor changes.

Users can see and update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is CONC_REQ_SUMMARY.

Concurrent:Validate Request Submission

This profile option prompts users in SRS form if no options or parameters have been changed from their defaults.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is CONC_VALIDATE_SUBMISSION.

Printer

You can select the printer which prints your reports. If a printer cannot be selected, contact your system administrator. Printers must be registered with Oracle Applications.

Users can see and update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is PRINTER.

Concurrent Processing View Requests

The internal name for this profile category is FND_CP_VIEW_REQUESTS.

Concurrent:Show Request Set Stages

Set this profile option value to Yes to show request set stages in the concurrent request screens.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is CONC_SHOW_STAGES.

Concurrent:URL Lifetime

The numeric value you enter for this profile option determines the length of time in minutes a URL for a request ouput is maintained. After this time period the URL will be deleted from the system. This profile option only affects URLs created for requests where the user has entered values in the notify field of the Submit Request or Submit Request Set windows.

Attention: All request ouput URLs are deleted when the Purge Concurrent Requests and Manager… program is run even if the URL liftime has not expired.

Users can see and update this profile option.

This profile option is visible and updatable at the all levels.

Level Visible Allow Update
Site Yes Yes
Application Yes No
Responsibility Yes No
User Yes No

The internal name for this profile option is CONC_URL_LIFETIME.

FND: Default Request Days

This profile option specifies the default number of days to view requests.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is FND_DEFAULT_REQUEST_DAYS.

Maximum Page Length

Determines the maximum number of lines per page in a report.

Users can see and update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is MAX_PAGE_LENGTH.

Viewer: Application for HTML, PCL, PDF, Postscript, Text, and XML

These profile options determine the applications a user will use to view reports in the given output formats. For example, you could set Viewer: Application for Text to ‘application/word’ to view a Text report in Microsoft Word.

Valid values are defined by the system administrator in the Viewer Options form.

Users can see and update these profile options.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal names for these profile options are FS_MIME_HTML, FS_MIME_PCL, FS_MIME_PDF, FS_MIME_PS, FS_MIME_TEXT, and FS_MIME_XML.

Viewer:Default Font Size

Using this new profile option, you can set the default font size used when you display report output in the Report Viewer.

The valid values for this option are 6, 8, 10, 12, and 14.

Users can see and update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is FNDCPVWR_FONT_SIZE.

Viewer: Text

The Viewer: Text profile option allows you to send report output directly to a browser window rather than using the default Report Viewer. Enter “Browser” in this profile option to enable this feature.

Users can see and update the Viewer:Text profile option.

This profile option is both visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is EDITOR_CHAR.

Database

The internal name for this profile category is FND_DATABASE.

Database Instance

Entering a valid two_task connect string allows you to override the default two_task. This profile is specifically designed for use with Oracle Parallel Server, to allow different responsibilities and users to connect to different nodes of the server.

Users can see this profile option, but they cannot update it.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is INSTANCE_PATH.

FND: Resource Consumer Group

Resource consumer groups are used by the Oracle8i Database Resource Manager, which allocates CPU resources among database users and applications. Each form session is assigned to a resource consumer group. The system administrator can assign users to a resource consumer group for all of their forms sessions and transactions. If no resource consumer group is found for a process, the system uses the default group “Default_Consumer_Group”.

Users can see this profile option, but they cannot update it.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is FND_RESOURCE_CONSUMER_GROUP.

Two Task

This profile option should be set by AutoConfig. only.

The TWO_TASK for the database. This profile is used in conjunction with the Gateway User ID profile to construct a connect string for use in creating dynamic URLs for the Web Server. This should be set to the SQL*NET. alias for the database.

Note: The TWO_TASK must be valid on the node upon which the WebServer is running

Users can see and but not update this profile option.

This profile option is visible at all levels but may only be updated at site level.

Level Visible Allow Update
Site Yes Yes
Application Yes No
Responsibility Yes No
User Yes No

The internal name for this profile option is TWO_TASK.

Debug

The internal name for this profile category is FND_DEBUG.

Account Generator:Debug Mode

This profile option controls Oracle Workflow process modes for the Account Generator feature in flexfields. This profile option should normally be set to “No” to improve performance. If you are testing your Account Generator implementation and using the Oracle Workflow Monitor to see your results, set this profile option to “Yes”.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is ACCOUNT_GENERATOR:DEBUG_MODE.

BIS/AOL:Debug Log Directory

The directory for BIS debugging log files.

Users can see and change this profile option.

System administrators can see and update this profile option at the site level only.

The internal name for this profile option is BIS_DEBUG_LOG_DIRECTORY.

FND: Override Directory

The FND:Override Directory profile option is used by the Work Directory feature. The value of FND: Override Directory should be the directory containing your alternate files. Typically, this profile option should be set at the User level only.

Using the Work Directory and this profile option should be done for debugging only, as they present a security risk.

Users can see but not update this profile option.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is APPLWRK.

Utilities: Diagnostics

Utilities: Diagnostics determines whether a user can automatically use the Diagnostics features. If Utilities:Diagnostics is set to Yes, then users can automatically use these features. If Utilities:Diagnostics is set to No, then users must enter the password for the APPS schema to use the Diagnostics features.

Users cannot see nor change this profile option.

This profile option is visible and updatable at the all levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is DIAGNOSTICS.

Utilities:SQL Trace

This profile option is used by concurrent processing only. SQL trace files can be generated for individual concurrent programs. The trace can be enabled at the user level by setting the profile “Utilities:SQL Trace” to “Yes”. This profile can be enabled for a user only by System Administrator so that it is not accidentally turned on and disk usage can be monitored.

For more information on SQL trace, see the Oracle database documentation.

Users cannot see nor change this profile option.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is SQL_TRACE.

Deployment

The internal name for this profile category is FND_DEPLOYMENT.

Forms Runtime Parameters

Use this profile to specify certain forms runtime parameters. The profile value must be entered in as parameter=value. Each parameter-value pair must be separated by a single space. For example:

record=collect log=/tmp/frd.log debug_messages=yes

In order for the parameters updated in this profile option to go into effect, you must exit and log back in to Oracle Applications.

Users can see but not update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is FND_MORE_FORM_PARAMS.

Gateway User ID

Oracle login for gateway account. This should be the same as the environment variable GWYUID. For example, applsyspub/pub.

Users cannot see or update this profile option.

This profile option is visible at all levels but can only be updated at the site level.

Level Visible Allow Update
Site Yes Yes
Application Yes No
Responsibility Yes No
User Yes No

The internal name for this profile option is GWYUID.

Site Name

Site Name identifies an installation of Oracle Applications. The value of this profile should be set via AutoConfig.

The Site Name appears in the title of the MDI window. If you want additional information on your installation to appear in the title, for example, “Test” or “Production”, you can add that information here.

Users cannot see nor change this profile option.

This profile option is visible and updatable at the site level.

Level Visible Allow Update
Site Yes Yes
Application No No
Responsibility No No
User No No

The internal name for this profile option is SITENAME.

Socket Listener Port

This profile option defines the port number used by the Forms Client Controller.

The default value for this profile option is ’6945′.

The E-Business Suite Home page uses the Socket Listener Port profile for launching forms from Framework HTML sessions. With this architecture, a user navigating through different forms/responsibilities in a Framework session will reuse the same Oracle Forms session instead of opening multiple ones. So a user will never have more than one Forms session open on his/her PC at any given time, for a given database.

It is possible to have multiple Oracle Forms sessions open where each is connected to a different database, but the Socket Listener Port profile must be set to a different value beforehand on each database. For example, set it to 6945 on database A, 6946 on database B, and 6947 on database C. This profile option must be set at the site level in advance of any users attempting to use this functionality, as it cannot be set on a per-user basis.

Users can see but not update this profile option.

Level Visible Allow Update
Site Yes Yes
Application No No
Responsibility No No
User No No

The internal name for this profile option is SOCKET_LISTENER_PORT.

TCF: HOST

Set this to the name of the host running the TCF Socket Server.

This profile option is visible at all levels and updatable at the site and application level only.

Users can see but not update this profile option.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes No
User Yes No

The internal name for this profile option is TCF:HOST.

TCF: PORT

Set this profile option to the port number at which TCF Socket Server accepts connections.

Users can see and but not update this profile option.

This profile option is visible at all levels and updatable at the site and application level only.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes No
User Yes No

The internal name for this profile option is TCF:PORT.

Discoverer

The internal name for this profile category is FND_DISCOVERER.

ICX: Discoverer Launcher, Forms Launcher, and Report Launcher

These profile options are used by the Oracle Applications Personal Homepage.

Set the site level value of each of these profile options to the base URL for launching each application. The profile option value should be sufficient to launch the application, but should not include any additional parameters which may be supplied by the Personal Homepage.

Users can see these profile options, but they cannot update them.

These profile options are visible and updatable at all levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for these profile options are ICX_DISCOVERER_LAUNCHER, ICX_FORMS_LAUNCHER, and ICX_REPORT_LAUNCHER.

Document Sequencing

The internal name for this profile category is FND_DOC_SEQ.

Sequential Numbering

Sequential Numbering assigns numbers to documents created by forms in Oracle financial products. For example, when you are in a form that creates invoices, each invoice document can be numbered sequentially.

Sequential numbering provides a method of checking whether documents have been posted or lost. Not all forms within an application may be selected to support sequential numbering.

Sequential Numbering has the following profile option settings:

Always Used You may not enter a document if no sequence exists for it.
Not Used You may always enter a document.
Partially Used You will be warned, but not prevented from entering a document, when no sequence exists.

Users can see this profile option, but they cannot update it.

This profile option is visible and updatable at the site, application, and responsibility levels.

Note: If you need to control Sequential Numbering for each of your set of books, use the ‘Responsibility’ level. Otherwise, we recommend that you use either the ‘Site’ or ‘Application’ level to set this option.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User No No

The internal name for this profile option is UNIQUE:SEQ_NUMBERS.

Flexfields

The internal name for this profile category is FND_FLEXFIELDS.

Flexfields:AutoSkip

You can save keystrokes when entering data in your flexfields by automatically skipping to the next segment as soon as you enter a complete valid value into a segment.

  • “Yes” means after entering a valid value in a segment, you automatically move to the next segment.
  • “No” means after entering a valid value in a segment, you must press [Tab] to go to the next segment.

Note: You may still be required to use tab to leave some segments if the valid value for the segment does not have the same number of characters as the segment. For example, if a segment in the flexfield holds values up to 5 characters and a valid value for the segment is 4 characters, AutoSkip will not move you to the next segment.

Users can see and update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is FLEXFIELDS:AUTOSKIP.

Flexfields:BiDi Direction

This profile option controls the appearance of the flexfields window in Applications running in Semitic languages. Possible values are “Left To Right” and “Right To Left”.

Users can see and update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is FLEXFIELDS:BIDI_DIRECTION.

Flexfields:Open Descr Window

You can control whether a descriptive flexfield window automatically opens when you navigate to a customized descriptive flexfield.

  • “Yes” means that the descriptive flexfield window automatically opens when you navigate to a customized descriptive flexfield.
  • “No” means that when you navigate to a customized descriptive flexfield, you must choose Edit Field from the Edit menu or use the List of Values to open the descriptive flexfield window.

Users can see and update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is FLEXFIELDS:OPEN_DESCR_WINDOW.

Note: This profile option does not apply to descriptive flexfields in folders.

Flexfields:Open Key Window

You can control whether a key flexfield window automatically opens when you navigate to a key flexfield.

  • “Yes” means that the key flexfield window automatically opens when you navigate to a key flexfield.
  • “No” means that when you navigate to a key flexfield, you must choose Edit Field from the Edit menu or use the List of Values to open the key flexfield window.

Users can see and update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is FLEXFIELDS:OPEN_KEY_WINDOW.

Flexfields:Shorthand Entry

If shorthand flexfield entry is defined for your flexfield, you can use a shorthand alias to automatically fill in values for some or all of the segments in a flexfield.

Not Enabled Shorthand Entry is not available for any flexfields for this user, regardless of whether shorthand aliases are defined.
New Entries Only Shorthand Entry is available for entering new records in most foreign key forms. It is not available for combinations forms, updating existing records, or entering queries.
Query and New Entry Shorthand Entry is available for entering new records or for entering queries. It is not available for updating existing records.
All Entries Shorthand Entry is available for entering new records or updating old records. It is not available for entering queries.
Always Shorthand Entry is available for inserting, updating, or querying flexfields for which shorthand aliases are defined.

Users can see and update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is FLEXFIELDS:SHORTHAND_ENTRY.

Flexfields:Show Full Value

If an alias defines valid values for all of the segments in a flexfield, and Flexfields: Shorthand Entry is enabled, when you enter the alias the flexfield window does not appear.

“Yes” displays the full flexfield window with the cursor resting on the last segment.

Users can see and update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is FLEXFIELDS:SHOW_FULL_VALUE.

Flexfields:Validate On Server

This profile option is set to “Yes” to enable server side, PL/SQL flexfields validation for Key Flexfields. This improves performance when using Key Flexfields over a wide area network by reducing the number of network round trips needed to validate the entered segment combinations.

You may find, however, that your validation’s performance is better with client side validation. In this case, set this profile option to “No”.

Users can see and update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is FLEXFIELDS:VALIDATE_ON_SERVER.

Folders

The internal name for this profile category is FND_FOLDERS.

Folders:Allow Customization

Your system administrator controls whether you can create or customize a folder definition layout in folder block.

  • “Yes” means that you can create or customize a folder definition, that is, the entire Folder menu is enabled in the folder block.
  • “No” means that you can only open an existing folder definition in a folder block, that is, only the Open option is enabled in the Folder menu.

Users can see this profile option, but they cannot update it.

Level Visible Allow Update
Site No No
Application No No
Responsibility No No
User Yes Yes

The internal name for this profile option is FLEXVIEW:CUSTOMIZATION.

Forms UI

The internal name for this profile category is FND_FORMS_UI.

Flexfields:LOV Warning Limit

Use Flexfields:LOV Warning Limit to improve efficiency when retrieving a list of values.

Sometimes, particularly when no reduction criteria has been specified, an LOV can take a very long time to run if there is a very significant amount of data in it. Set this profile option to the number of rows to be returned before the user is asked whether to continue retrieving the entire list.

Users can see and update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is QUICKPICK_ROWS_BEFORE_WARN.

FND: Enable Cancel Query

Oracle Applications allows end users to cancel certain long-running queries, such as retrieving data in a block. When these operations exceed a threshold of time, approximately ten seconds, a dialog will display that allows the user to cancel the query.

Set the FND: Enable Cancel Query profile option to Yes if you wish to enable the ability to cancel a form query. This profile option may be set at the site, application, responsibility or the user level.

Users can see but not update this profile option.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is FND_ENABLE_CANCEL_QUERY.

FND: Indicator Colors

The default for this profile option is null, which means “Yes.” When this profile option is set to Yes:

  • Required fields are displayed in yellow.
  • Queryable fields are displayed in a different color while in enter-query mode.
  • Fields that cannot be entered (read-only) are rendered in dark gray.

Users can see and update this profile option.

Level Visible Allow Update
Site No No
Application No No
Responsibility No No
User Yes Yes

The internal name for this profile option is FND_INDICATOR_COLORS.

Forms Keyboard Mapping File

Use this profile option to define the path of the Keyboard Mapping File.

The “Keys” window displays the keystrokes to perform standard Forms operations, such as “Next Block” and “Clear Record.” This window can be viewed at anytime by pressing Ctrl+k. The keyboard mappings can be customized as follows:

  • The System Administrator must locate the Oracle Forms resource file on the middle tier, typically called fmrweb.res.
  • Make a copy of the file, name it as desired, and locate it in the same directory as the original
  • Open the new file in any text editor and make the desired keystroke mapping changes. Comments at the top of the file explain how the mappings are performed.
  • To run the new mapping file, specify the complete path and file name in this profile option.

Users can see and update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is FND_FORMS_TERM.

Indicate Attachments

This profile option allows you to turn off indication of attachments when querying records (for performance reasons).

Users can see and update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is ATCHMT_SET_INDICATOR.

Java Color Scheme

If the Java Look and Feel profile option is set to Oracle, the Java Color Scheme can be specified as follows:

  • Swan (default)
  • Teal
  • Titanium
  • Red
  • Khaki
  • Blue
  • Olive
  • Purple

The Java Color Scheme profile has no effect if the Java Look and Feel is set to Generic.

Attention: Setting the Java Color Scheme profile option to a value other than ‘swan’ (the default value) can have a considerable impact on forms user response time performance.

For some users, setting this profile option to a value other than ‘swan’ may be desirable for accessibility reasons. See: Oracle Applications Accessibility Features and “Accessibility in Oracle Forms Applications” at http://www.oracle.com/accessibility/apps02.html.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is FND_COLOR_SCHEME.

Java Look and Feel

Oracle Applications Professional User Interface (Forms-based applications) can be run with either the Oracle Look and Feel or the Generic Look and Feel. The Oracle Look and Feel consists of a new look and feel for each item, and a predefined set of color schemes. The Generic Look and Feel adheres to the native interface and color scheme of the current operating system.

To specify the look and feel set this profile to “generic” or “oracle”.

If the Oracle Look and Feel is used, the profile Java Color Scheme can be set. The Java Color Scheme profile has no effect if the Java Look and Feel is set to Generic.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is FND_LOOK_AND_FEEL.

Framework Logging and Alerting

The internal name for this profile category is FND_FWK_LOGGING_ALERTING.

FND: Log Filename for Middle-Tier

The file name for the file to hold debugging messages used in the Logging Service. If the value of this profile option is null, then the Logging Service is turned off.

Users can see but not update this profile option.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is AFLOG_FILENAME.

FND: Log Level

The Logging Service can filter out debugging messages depending on their priority level.. There are five levels of the Debug/Trace Service:. In order from highest priority to lowest priority, they are: Errors, Exceptions, Events, Procedures, and Statements. The Debug Log Level is the lowest level that the user wants to see messages for.. The possible profile option values are Null (which means off), and the five priority levels above. For instance, if the “FND: Debug Log Level” profile is set to “EVENT”, then the file will get the messages that the programmer had marked as “EVENT”, “EXCEPTION”, or “ERROR”.

Users can see but not update this profile option.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is AFLOG_LEVEL.

FND: Log Module

The Logging Service can filter out debugging messages depending on their module. Module names are unique across applications and coding languages. If a module is specified for this profile option, then only messages for that module will be written to the log file. If this profile option is left blank then messages for all modules will be written to the log file.

Users can see but not update this profile option.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is AFLOG_MODULE.

iHelp

The internal name for this profile category is FND_IHELP.

Applications Help Web Agent

Applications Help Web Agent is optional and should only be used if you want to launch online help on a web server different from the one specified by the Applications Servlet Agent.

Attention: For most installations, this profile should be set to NULL. Only specify a value if you want to use a different web server than that for the Applications Servlet Agent.

Specify the entire online help URL for this profile’s value:

 http://<host name of servlet agent>:<port number of servlet                agent>/OA_HTML/jsp/fnd/fndhelp.jsp?dbc=<DBC file name>

If this profile option is not set, the online help tree navigator will default to starting up at the host name and port number that is specified by the Applications Servlet Agent profile option. The DBC file used will be that of the database where online help was invoked.

Users can see this profile option, but they cannot update it.

This profile option is visible and updatable at all levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is HELP_WEB_AGENT.

Help Localization Code

This code determines which localized context-sensitive help files a user accesses.

Users can see this profile option, but they cannot update it.

This profile option is visible and updatable at the responsibility and user levels.

Level Visible Allow Update
Site No No
Application No No
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is HELP_LOCALIZATION_CODE.

Help Tree Root

This profile option determines which tree is shown in the navigation frame when context-sensitive help is launched.

If Help Tree Root is set to “null” or “NULL” (case insensitive), then the online help is launched in a single frame, without the navigation and search features.

Users can see this profile option, but they cannot update it.

This profile option is visible and updatable at all levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is HELP_TREE_ROOT.

Help Utility Download Path

Use this profile option to define the directory into which the Help Utility downloads help files from the Oracle Applications Help System.

Users can see this profile option, but they cannot update it.

This profile option is visible and updatable at all levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is HELP_UTIL_DL_PATH.

Help Utility Upload Path

Use this profile option to define the directory from which the Help Utility uploads help files to the Oracle Applications Help System.

Users can see this profile option, but they cannot update it.

This profile option is visible and updatable at all levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is HELP_UTIL_UL_PATH.

Multi Organization Setup

The internal name for this profile category is FND_MULTI_ORG.

MO:Operating Unit

In Multiple Organization installations, Oracle Applications uses the profile option MO: Operating Unit to link an operating unit to a responsibility. You must set this profile option for each responsibility. For more information on setting this profile option, see: Multiple Organizations in Oracle Applications.

Users can see but not update this profile option.

This profile option is visible and updatable at the responsibility level only.

Level Visible Allow Update
Site No No
Application No No
Responsibility Yes Yes
User No No

The internal name for this profile option is ORG_ID.

NLS

The internal name for this profile category is FND_NLS.

Currency:Mixed Precision

Use Mixed Currency Precision to specify how many spaces are available to the right of the decimal point when displaying numbers representing different currencies.

  • Normally, currency numbers are right-justified.
  • Each currency has its own precision value that is the number of digits displayed to the right of a decimal point. For U.S. dollars the precision default is 2, so an example display is 345.70.
  • Set Mixed Currency Precision to be equal to or greater than the maximum precision value of the currencies you are displaying.

For example, if you are reporting on rows displaying U.S. dollars (precision=2), Japanese yen (precision=0), and Bahraini dinar (precision=3), set Mixed Currency Precision=3.

Note: The Currency profile options pertain to currency only, not to other numeric fields.

Users can see and update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is CURRENCY:MIXED_PRECISION.

Currency:Negative Format

You can use different formats to identify negative currency. The default identifier is a hyphen ( – ) preceding the currency amount, as in “-xxx”. You can also select:

Angle brackets < > < xxx >

Trailing hyphen – xxx -

Parentheses ( ) ( xxx )

Square Brackets [ ] [ xxx ]

Note: The Currency profile options pertain to currency only, not to other numeric fields.

Users can see and update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is CURRENCY:NEGATIVE_FORMAT.

Note: Currency:Negative Format only affects the display of currency values . Non-currency negative numbers appear with a preceding hyphen regardless of the option selected here.

Currency:Positive Format

You can use different formats to identify positive currency values. The default condition is no special identifier.

Note: The Currency profile options pertain to currency only, not to other numeric fields.

Users can see and update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is CURRENCY:POSITIVE_FORMAT.

Currency:Thousands Separator

You can separate your currency amounts in thousands by placing a thousands separator. For example, one million appears as 1,000,000.

Users can see and update this profile option.

Note: The Currency profile options pertain to currency only, not to other numeric fields.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is CURRENCY:THOUSANDS_SEPARATOR.

Default Country

This is the default source for the Country field for all address zones and is used by the Flexible Address Formats feature, the Flexible Bank Structures feature and the Tax Registration Number and Taxpayer ID validation routines.

The profile can be set to any valid country listed in the Maintain Countries and Territories form and can be set to a different value for each user.

Users can see and update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is DEFAULT_COUNTRY.

FND: Native Client Encoding

FND: Native Client Encoding indicates the character set that a client machine uses as its native character set. The value must be one of the Oracle character sets and should correspond to the client native character set. The character set used in a client machine varies depending on language and platform. For example, if a user uses a Windows machine with Japanese, the value should be JA16SJIS. But if a user uses a Solaris machine with Japanese, the value should be JA16EUC. The value is normally set in the user level since each user uses different machine, but it can be set in every level for a default value.

This profile option is used when storing text data. When a user uploads text files as attachments, the current value of FND: Native Client Encoding is stored along with the text data. With the value of this profile option, the server can then convert the text data to another character set as necessary when the text data is downloaded.

Users can see and update this profile option.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is FND_NATIVE_CLIENT_ENCODING.

ICX: Preferred Currency

This profile determines in which currency a user will see the currency number in the UI.

For example, the source currency number might be stored in database such as 10.00 as US Dollar (USD), but the displayed currency number is based on the currency set in this profile option such as 1,200 as Japanese Yen (JPY). In this multi-currency conversion, USD is source currency and JPY is the profile option value.

This profile option is for currency display purpose especially for self-service type applications.

This profile option is a generic preference that a user can set through the Oracle Application Framework Preferences page. The profile option values is used across the Oracle E-Business Suite so that the user sees currency numbers in all applications based on the currency chosen.

The currencies must be set up through the Oracle General Ledger application properly (the following must be set properly: Enabled/Disabled, Active Date and Exchange ratio between currencies). Proper setup ensures that the currency chosen is available in the system, and the currency number can be converted from the source (functional) currency to the target currency (the currency chosen by a user as this profile option value) with the specified exchange ratio. This profile option is tightly linked to GL currency setup. For more information, see:

Defining Currencies.

Users can see and update this profile option.

Level Visible Allow Update
Site Yes Yes
Application No No
Responsibility No No
User Yes Yes

The internal name for this profile option is ICX_PREFERRED_CURRENCY.

Server Timezone

The time zone of the database server.

Users can see this profile option, but they cannot update it.

Level Visible Allow Update
Site Yes Yes
Application No No
Responsibility No No
User No No

The internal name for this profile option is SERVER_TIMEZONE_ID.

Personalization

The internal name for this profile category is FND_PERSONALIZATION.

Initialization SQL Statement – Custom

This profile option allows you to add site-specific initialization code (such as optimizer settings) that will be executed at database session initialization. The value of this profile option must be a valid SQL statement.

The system administrator may set this profile option at any level.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is FND_INIT_SQL.

Security

The internal name for this profile category is FND_SECURITY.

AuditTrail:Activate

You can turn AuditTrail on or off (Yes or No). The default setting is No (Off).

When you enter or update data in your forms, you change the database tables underlying the forms you see and use.

AuditTrail tracks which rows in a database table(s) were updated at what time and which user was logged in using the form(s).

  • Several updates can be tracked, establishing a trail of audit data that documents the database table changes.
  • AuditTrail is a feature enabled on a form-by-form basis by a developer using Oracle’s Application Object Library.
  • All the forms that support AuditTrail are referred to as an audit set.
  • Not all forms may be enabled to support AuditTrail.
  • To enable or disable AuditTrail for a particular form, you need access to Oracle Application Object Library’s Application Developerresponsibility.

Users cannot see nor change this profile option.

This profile option is visible and updatable at the site and application levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility No No
User No No

The internal name for this profile option is AUDITTRAIL:ACTIVATE.

Enable Security Groups

This profile option is used by the Security Groups feature, which is used by HRMS security only. For more information on Security Groups, see the Oracle HRMS documentation.

The possible values are ‘None’ (N), and ‘Service Bureau’ (Y).

Only the System Administrator can update this profile option.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility No No
User No No

The internal name for this profile option is ENABLE_SECURITY_GROUPS.

Hide Diagnostics Menu Entry

This profile option determines whether users can access the Diagnostics menu entry from the Help menu. The default value is Yes, with the Diagnostics menu entry is hidden. If it is set to No, the Diagnostics menu entry is visible.

Users cannot see nor change this profile option.

This profile option is visible and updatable at the all levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is FND_HIDE_DIAGNOSTICS.

ICX: Limit time

This profile option determines the absolute maximum duration (in hours) of a user’s session, regardless of activity.

Users cannot see or update this profile option.

Level Visible Allow Update
Site Yes Yes
Application No No
Responsibility No No
User Yes Yes

The internal name for this profile option is ICX_LIMIT_TIME.

ICX: Session Timeout

This profile option determines the length of time (in minutes) of inactivity in a user’s session before the session is disabled. If the user does not perform any operation in Oracle Applications for longer than this value, the session is disabled. The user is provided the opportunity to re-authenticate and re-enable a timed-out session. If re-authentication is successful, the session is re-enabled and no work is lost. Otherwise, Oracle Applications exit without saving pending work.

If this profile option to 0 or NULL, then user sessions will never time out due to inactivity.

Users can see this profile option, but they cannot update it.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is ICX_SESSION_TIMEOUT.

Node Trust Level

Determines the level of trust assigned to a Web server. This profile option uses the Server hierarchy type. This profile option is used in conjunction with the profile option Responsibility Trust Level. For more information on using these profile options, see: Restricting Access to Responsibilities Based on User’s Web Server.

Users can see but not update this profile option.

This profile option is visible and updatable at the site and server level only.

Level Visible Allow Update
Site Yes Yes
Server Yes Yes
User No No

The internal name for this profile option is NODE_TRUST_LEVEL.

Responsibility Trust Level

Responsibilities or applications with the specified level of trust can only be accessed by an application server with at least the same level of trust.

This profile option is used in conjunction with the profile option Node Trust Level. For more information on using these profile options, see:Restricting Access to Responsibilities Based on User’s Web Server.

Users can see this profile option, but they cannot update it.

The system administrator access is described in the following table:

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User No No

The internal name for this profile option is APPL_SERVER_TRUST_LEVEL.

Sign-On:Audit Level

Sign-On:Audit Level allows you to select a level at which to audit users who sign on to Oracle Applications. Four audit levels increase in functionality: None, User, Responsibility, and Form.

None is the default value, and means do not audit any users who sign on to Oracle Applications.

Auditing at the User level tracks:

  • who signs on to your system
  • the times users log on and off
  • the terminals in use

Auditing at the Responsibility level performs the User level audit functions and tracks:

  • the responsibilities users choose
  • how much time users spend using each responsibility

Auditing at the Form level performs the Responsibility level audit functions and tracks:

  • the forms users choose
  • how long users spend using each form
  • System Administrator visible, updatable at all levels.

Users cannot see nor change this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is SIGNONAUDIT:LEVEL.

Sign-On:Notification

“Yes” displays a message at login that indicates:

  • If any concurrent requests failed since your last session,
  • How many times someone tried to log on to Oracle Applications with your username but an incorrect password, and
  • When the default printer identified in your user profile is unregistered or not specified.

Users can see and update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is SIGNONAUDIT:NOTIFY.

Signon Password Case

Oracle Applications gives you the ability to control case sensitivity in user passwords through this profile option. This profile has two possible settings:

  • Sensitive – Passwords are stored and compared as they are, with the password case preserved. During validation, the entered password must match the decrypted version otherwise an error message is displayed. With Release 12, this option is the default behavior. All newly created or changed passwords are treated as case sensitive.

Note: Users who have not changed their passwords since the installation of release 12 are not affected until they do change their passwords.

A password expiration utility is available if the System Administrator requires that all users convert to case sensitive passwords upon the next login. This utility expires all passwords in FND_USER, including that of SYSADMIN and default Vision accounts, and can be run as a SQL Script ($FND_TOP/sql/AFCPEXPIRE.sql) or as a Concurrent Program (FNDCPEXPIRE_SQLPLUS).

  • Insensitive (or unset) – Passwords are treated as case insensitive. In Insensitive mode, passwords are stored and compared in uppercase, similar to that in earlier releases. During validation, the entered password and the decrypted password are compared in uppercase. If the passwords do not match, an error is displayed.

Users can see but not update this profile option.

Level Visible Allow Update
Site Yes Yes
Application No No
Responsibility No No
User No No

The internal name for this profile option is SIGNON_PASSWORD_CASE.

Signon Password Failure Limit

The Signon Password Failure Limit profile option determines the maximum number of login attempts before the user’s account is disabled.

Users cannot see or update this profile option.

Level Visible Allow Update
Site Yes Yes
Application No No
Responsibility No No
User Yes Yes

The internal name for this profile option is SIGNON_PASSWORD_FAILURE_LIMIT.

Signon Password Hard to Guess

The Signon Password Hard to Guess profile option sets rules for choosing passwords to ensure that they will be “hard to guess.” A password is considered hard-to-guess if it follows these rules:

  • The password contains at least one letter and at least one number.
  • The password does not contain the username.
  • The password does not contain repeating characters.

Users can see but not update this profile option.

Level Visible Allow Update
Site Yes Yes
Application No No
Responsibility No No
User Yes Yes

The internal name for this profile option is SIGNON_PASSWORD_HARD_TO_GUESS.

Signon Password Length

Signon Password Length sets the minimum length of an Applications signon password. If no value is entered the minimum length defaults to 5.

Users can see but not update this profile option.

Level Visible Allow Update
Site Yes Yes
Application No No
Responsibility No No
User Yes Yes

The internal name for this profile option is SIGNON_PASSWORD_LENGTH.

Signon Password No Reuse

This profile option specifies the number of days that a user must wait before being allowed to reuse a password.

Users can see but not update this profile option.

Level Visible Allow Update
Site Yes Yes
Application No No
Responsibility No No
User Yes Yes

The internal name for this profile option is SIGNON_PASSWORD_NO_REUSE.

Single Sign-On Account Settings

The internal name for this profile category is FND_SSO_ACCOUNT_SETTINGS.

ICX: Client IANA Encoding

This profile option is used to determine the character set of text displayed by Java Server pages. The value is the code set of the middle tier. It is used to allow the online help system to support languages other than American English. The default setting is the Western European character set (ISO-8859-1).

This profile option should be set only at the site level.

Users can see this profile option, but they cannot update it.

This profile option is visible and updatable at the all levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is ICX_CLIENT_IANA_ENCODING.

Web Server Deployment

The internal name for this profile category is FND_WS_DEPLOYMENT.

Applications Servlet Agent

This profile option must be set to the URL base for the servlet execution engine on Apache. Oracle Applications uses the value of this profile option to construct URLs for JSP and SERVLET type functions. The syntax is:

 https://<hostname>:<port>/<servlet_zone>

Example:

 https://ap523sun.us.oracle.com:8888/oa_servlets

Users can see this profile option, but they cannot update it.

This profile option is visible and updatable at all levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is APPS_SERVLET_AGENT.

Applications Web Agent

Provides the base URL for the Apps Schema’s WebServer DAD. You set this profile option during the install process.

This profile option is visible and updatable at all levels.

Level Visible Allow Update
Site Yes Yes
Application No No
Responsibility No No
User Yes Yes

The internal name for this profile option is APPS_WEB_AGENT.

 

Oracle Application R12 ,

Oracle Apps 11i – Profiles

Leave a Comment Posted by on November 1, 2010

Profile Options in Oracle Application Object Library

This section lists each profile option in Oracle Application Object Library. These profile options are available to every product in Oracle Applications. For each profile option, we give a brief overview of how Oracle Application Object Library uses the profile’s setting.

Unless otherwise noted, a profile option uses the Security hierarchy type.

A table is provided for most profile options that lists the access levels for the profile option. For Security profile options, there are four possible levels at which system administrators can view and update a profile option value: site, application, responsibility, and user. This table lists whether the profile option’s value is visible at each of these levels, and whether it is updatable at each level.

Account Generator:Debug Mode

This profile option controls Oracle Workflow process modes for the Account Generator feature. This profile option should normally be set to “No” to improve performance. If you are testing your Account Generator implementation and using the Oracle Workflow Monitor to see your results, set this profile option to “Yes”.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is ACCOUNT_GENERATOR:DEBUG_MODE.

Applications Help Web Agent

Applications Help Web Agent is optional and should only be used if you want to launch online help on a web server different from the one specified by the Applications Servlet Agent.

Attention: For most installations, this profile should be set to NULL. Only specify a value if you want to use a different web server than that for the Applications Servlet Agent.

Specify the entire online help URL for this profile’s value:

http://<host name of servlet agent>:<port number of servlet agent>/
OA_HTML/jsp/fnd/fndhelp.jsp?dbc=<DBC file name>

This new usage of HELP_WEB_AGENT provides one with the flexibility of reverting back to the previous Release 11i applet version of the tree navigator if desired. To do this, set this profile option to

http://<host name of PL/SQL agent>[:<portnumber of PL/SQL agent>]/<PL/SQL agent name>/
fnd_help.launch?par_root=

This is usually identical to the Applications Web Agent profile option but with the string “/fnd_help.launch?par_root=” appended at the end.

If this profile option is not set, the online help tree navigator will default to starting up at the host name and port number that is specified by the Applications Servlet Agent profile option. The DBC file used will be that of the database where online help was invoked.

Users can see this profile option, but they cannot update it.

This profile option is visible and updatable at all levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is HELP_WEB_AGENT.

Applications Servlet Agent

This profile option must be set to the URL base for the servlet execution engine on Apache. Oracle Applications uses the value of this profile option to construct URLs for JSP and SERVLET type functions. The syntax is:

https://<hostname>:<port>/<servlet_zone>

Example:

https://ap523sun.us.oracle.com:8888/oa_servlets

Users can see this profile option, but they cannot update it.

This profile option is visible and updatable at all levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is APPS_SERVLET_AGENT.

Applications Web Agent

Provides the base URL for the Apps Schema’s WebServer DAD. You set this profile option during the install process.

This profile option must be set to the URL which identifies the mod_plsql PL/SQL Gateway Database Access Descriptor base URL for your Applications instance. Oracle Applications use the value of this profile option to construct URLs for ‘WWW’ type functions, Attachments, Export, and other features.

Use the following syntax to enter your URL:

        https://<hostname>:<port>/pls/<dad_name>

Users can see but not update this profile option.

This profile option is visible and updatable at all levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is APPS_WEB_AGENT.

Applications Web Authentication Server

The web server used for authentication for Oracle Self-Service Web Applications.

Users can see but not update this profile option.

This profile option is visible and updatable at the site level.

Level Visible Allow Update
Site Yes Yes
Application No No
Responsibility No No
User No No

The internal name for this profile option is WEB_AUTHENTICATION_SERVER.

Attachment File Directory

The directory in which file type attachments data is stored. You set this profile option during the install process.

Users can see but not update this profile option.

This profile option is visible and updatable at all levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is ATTACHMENT_FILE_DIRECTORY.

AuditTrail:Activate

You can turn AuditTrail on or off (Yes or No). The default setting is No (Off).

When you enter or update data in your forms, you change the database tables underlying the forms you see and use.

AuditTrail tracks which rows in a database table(s) were updated at what time and which user was logged in using the form(s).

  • Several updates can be tracked, establishing a trail of audit data that documents the database table changes.
  • AuditTrail is a feature enabled on a form-by-form basis by a developer using Oracle’s Application Object Library.
  • All the forms that support AuditTrail are referred to as an audit set.
  • Not all forms may be enabled to support AuditTrail.
  • To enable or disable AuditTrail for a particular form, you need access to Oracle Application Object Library’s Application Developerresponsibility.

Users cannot see nor change this profile option.

This profile option is visible and updatable at the site and application levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility No No
User No No

The internal name for this profile option is AUDITTRAIL:ACTIVATE.

BIS/AOL:Debug Log Directory

The directory for BIS debugging log files.

Users can see and change this profile option.

System administrators can see and update this profile option at the site level.

Level Visible Allow Update
Site Yes Yes
Application No No
Responsibility No No
User No No

The internal name for this profile option is BIS_DEBUG_LOG_DIRECTORY.

Concurrent:Active Request Limit

You can limit the number of requests that may be run simultaneously by each user. or for every user at a site. If you do not specify a limit, no limit is imposed.

Users cannot see or update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes No
Responsibility Yes No
User Yes Yes

The internal name for this profile option is CONC_REQUEST_LIMIT.

Concurrent:Attach URL

Setting this option to “Yes” causes a URL to be attached to request completion notifications. When a user submits a request, and specifies people to be notified in the Defining Completion Options region, everyone specified is sent a notification when the request completes. If this profile option is set to Yes, a URL is appended to the notification that enables them to view the request results on-line.

Only the System Administrator can update this profile option.

Users can see but not update this profile option.

This profile options is visible at all levels but can only updated at the Site level.

Level Visible Allow Update
Site Yes Yes
Application Yes No
Responsibility Yes No
User Yes No

The internal name for this profile option is CONC_ATTACH_URL.

Concurrent:Conflicts Domain

Specify a conflict domain for your data. A conflict domain identifies the data where two incompatible programs cannot run simultaneously.

Users can see but not update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is CONC_CD_ID.

Concurrent:Collect Request Statistics

Set this profile option to “Yes” to have statistics for your runtime concurrent processes collected.

To review the statistics you must run the Purge Concurrent Request and/or Manager Data program to process the raw data and have it write the computed statistics to the FND_CONC_STAT_SUMMARY table. You can then retrieve your data from this table using SQL*PLUS or on a report by report basis using the Diagnostics window from the Requests window.

Users cannot see nor change this profile option.

This profile option is visible at all levels bu can only be updated at the Site level.

Level Visible Allow Update
Site Yes Yes
Application Yes No
Responsibility Yes No
User Yes No

The internal name for this profile option is CONC_REQUEST_STAT.

Concurrent:Debug Flags

Your Oracle support representative may access this profile option to debug Transaction Managers. Otherwise, it should be set to null.

Users cannot see nor change this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is CONC_DEBUG.

Concurrent:Hold Requests

You can automatically place your concurrent requests on hold when you submit them.

The default is “No”. The concurrent managers run your requests according to the priority and start time specified for each.

Changing this value does not affect requests you have already submitted.

“Yes” means your concurrent requests and reports are automatically placed on hold. To take requests off hold, you:

  • Navigate to the Requests window to select a request
  • Select the Request Control tabbed region
  • Uncheck the Hold check box

Users can see and update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is CONC_HOLD.

Concurrent:Multiple Time Zones

“Yes” sets the default value to ‘Sysdate-1′ for the ‘Schedules Start Date’ used by request submissions. Sysdate-1 ensures that you request is scheduled immediately regardless of which time zone your client session is running in. You should use this profile option when the client’s session is running in a different time zone than the concurrent manager’s session.

Users cannot see nor change this profile option.

This profile option is visible at all four levels and updatable at the Site level.

Level Visible Allow Update
Site Yes Yes
Application Yes No
Responsibility Yes No
User Yes No

The internal name for this profile option is CONC_MULTI_TZ.

Concurrent:PMON method

PMON refers to process monitor. The Internal Concurrent Manager monitors the individual concurrent managers’ processes to verify the managers are running.

This profile option should always be set to LOCK. Using the LOCK method, the Internal Concurrent Manager tries to get a lock on the individual concurrent manager’s process. The name of the lock is determined by a sequence (which is the ID of the individual manager) and the program in question. If the Internal Concurrent Manager is able to get the lock, then it knows the process is no longer running.

Users cannot see nor change this profile option.

This profile option is neither visible nor updatable from the System Profile Options form.

Level Visible Allow Update
Site No No
Application No No
Responsibility No No
User No No

The internal name for this profile option is CONC_PMON_METHOD.

Concurrent:Report Access Level

Determines access privileges to report output files and log files generated by a concurrent program. This profile option can be set by a System Administrator to User or Responsibility.

If your Concurrent:Report Access Level profile option is set to “User” you may:

  • View the completed report output for your requests online
  • View the diagnostic log file for those requests online. (system administrator also has this privilege)
  • Reprint your completed reports, if the Concurrent:Save Output profile option is set to “Yes”.
  • If you change responsibilities, then the reports and log files available for online review do not change.

If your Concurrent:Report Access Level profile option is set to “Responsibility”, access to reports and diagnostic log files is based on the your current responsibility.

  • If you change responsibilities, then the reports and log files available for online review change to match your new responsibility. You can always see the output and log files from reports you personally submit, but you also see reports and log files submitted by any user from the current responsibility.

Users can see this profile option, but they cannot update it.

This profile option is visible and updatable at the site, responsibility, and user levels.

Level Visible Allow Update
Site Yes Yes
Application No No
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is CONC_REPORT_ACCESS_LEVEL.

Concurrent:Report Copies

You can set the number of output copies that print for each concurrent request. The default is set to 1.

  • Changing this value does not affect requests that you have already submitted.

Users can see and update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is CONC_COPIES.

Concurrent:Request Priority

This displays the default priority number for your concurrent requests. Only a system administrator can change your request priority.

Requests normally run according to start time, on a “first-submitted, first-run” basis. Priority overrides request start time. A higher priority request starts before an earlier request.

Priorities range from 1 (highest) to 99 (lowest). The standard default is 50.

Users can see this profile option, but they cannot update it.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is CONC_PRIORITY.

Concurrent:Request Start Time

You can set the date and time that your requests are available to start running.

  • If the start date and time is at or before the current date and time, requests are available to run immediately.
  • If you want to start a request in the future, for example, at 3:45 pm on June 12, 2002, you enter 2002/06/12 15:45:00 as the profile option value.

Attention: You must ensure that this value is in canonical format (YYYY/MM/DD HH24:MI:SS) to use the Multilingual Concurrent Request feature.

  • You must include both a date and a time.
  • Changing this value does not affect requests that you have already submitted.
  • Users can override the start time when they submit requests. Or, this profile option can be left blank and users will be prompted for a start time when they submit requests.

Users can see and update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is CONC_REQ_START.

Concurrent:Save Output

The Concurrent: Save Output profile is used to determine whether the default behavior of certain concurrent programs should be to save or delete their output files. This only affects concurrent programs that were created in the character mode versions of Oracle Applications and that have a null value for “Save Output”.

  • “Yes” saves request outputs.
  • Some concurrent requests do not generate an output file.
  • If your request output is saved, you can reprint a request. This is useful when requests complete with an Error status, for example, the request runs successfully but a printer malfunctions.
  • Changing this value does not affect requests you have already submitted.

Users can see and update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is CONC_SAVE_OUTPUT.

Concurrent:Sequential Requests

You can force your requests to run one at a time (sequentially) according to the requests’ start dates and times, or allow them to run concurrently, when their programs are compatible.

  • Concurrent programs are incompatible if simultaneously accessing the same database tables incorrectly affects the values each program retrieves.
  • When concurrent programs are defined as incompatible with one another, they cannot run at the same time.

“Yes” prevents your requests from running concurrently. Requests run sequentially in the order they are submitted.

“No” means your requests can run concurrently when their concurrent programs are compatible.

Changing this value does not affect requests you have already submitted.

Users can see and update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is CONC_SINGLE_THREAD.

Concurrent: Show Requests Summary After Each Request Submission

Using this new profile option, you can choose to either have the Requests Summary displayed each time you submit a request, or retain the request submission screen.

The default is “Yes”. “Yes” means the Requests Summary screen is displayed each time you submit a request.

If you choose “No”, a decision window is opened asking you if you wish to submit another request. When you choose to submit another request you are returned to the submission window and the window is not cleared, allowing you to easily submit copies of the same request with minor changes.

Users can see and update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is CONC_REQ_SUMMARY.

Concurrent:Wait for Available TM

You can specify the maximum number of seconds that the client will wait for a given transaction manager (TM) to become available before moving on to try a different TM.

Users can see and update this profile option.

This profile option is visible and updatable at the site and application levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility No No
User No No

The internal name for this profile option is CONC_TOKEN_TIMEOUT.

Concurrent:URL Lifetime

The numeric value you enter for this profile option determines the length of time in minutes a URL for a request ouput is maintained. After this time period the URL will be deleted from the system. This profile option only affects URLs created for requests where the user has entered values in the notify field of the Submit Request or Submit Request Set windows.

Attention: All request ouput URLs are deleted when the Pruge Concurrent Requests and Manager… program is run even if the URL liftime has not expired.

Users can see and update this profile option.

This profile option is visible and updatable at the all levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is CONC_URL_LIFETIME.

Currency:Mixed Precision

Use Mixed Currency Precision to specify how many spaces are available to the right of the decimal point when displaying numbers representing different currencies.

  • Normally, currency numbers are right-justified.
  • Each currency has its own precision value that is the number of digits displayed to the right of a decimal point. For U.S. dollars the precision default is 2, so an example display is 345.70.
  • Set Mixed Currency Precision to be equal to or greater than the maximum
  • precision value of the currencies you are displaying.

For example, if you are reporting on rows displaying U.S. dollars (precision=2), Japanese yen (precision=0), and Bahraini dinar (precision=3), set Mixed Currency Precision=3.

Users can see and update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is CURRENCY:MIXED_PRECISION.

Currency:Negative Format

You can use different formats to identify negative currency. The default identifier is a hyphen ( – ) preceding the currency amount, as in “-xxx”. You can also select:

Angle brackets < > < xxx >

Trailing hyphen – xxx -

Parentheses ( ) ( xxx )

Square Brackets [ ] [ xxx ]

If you use the negative number formats of “(xxx)” or “[xxx],” in Oracle Applications Release 11, your negative numbers appear as “<xxx>”.

Users can see and update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is CURRENCY:NEGATIVE_FORMAT.

Note: Currency:Negative Format only affects the display currency. Non-currency negative numbers appear with a preceding hyphen regardless of the option selected here.

Currency:Positive Format

You can use different formats to identify positive currency values. The default condition is no special identifier.

Users can see and update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is CURRENCY:POSITIVE_FORMAT.

Currency:Thousands Separator

You can separate your currency amounts in thousands by placing a thousands separator. For example, one million appears as 1,000,000.

Users can see and update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is CURRENCY:THOUSANDS_SEPARATOR.

Database Instance

Entering a valid two_task connect string allows you to override the default two_task. This profile is specifically designed for use with Oracle Parallel Server, to allow different responsibilities and users to connect to different nodes of the server.

Users can see this profile option, but they cannot update it.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is INSTANCE_PATH.

Default Country

This is the default source for the Country field for all address zones and is used by the Flexible Address Formats feature, the Flexible Bank Structures feature and the Tax Registration Number and Taxpayer ID validation routines.

The profile can be set to any valid country listed in the Maintain Countries and Territories form and can be set to a different value for each user.

Users can see and update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is DEFAULT_COUNTRY.

Enable Security Groups

This profile option is used by the Security Groups feature, which is used by HRMS security only.

The possible values are ‘None’ (N), ‘Hosted’ (HOSTED), and ‘Service Bureau’ (Y).

Only the System Administrator can update this profile option.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility No No
User No No

The internal name for this profile option is ENABLE_SECURITY_GROUPS.

Flexfields:AutoSkip

You can save keystrokes when entering data in your flexfields by automatically skipping to the next segment as soon as you enter a complete valid value into a segment.

  • “Yes” means after entering a valid value in a segment, you automatically move to the next segment.
  • “No” means after entering a valid value in a segment, you must press [Tab] to go to the next segment.

Note: You may still be required to use tab to leave some segments if the valid value for the segment does not have the same number of characters as the segment. For example, if a segment in the flexfield holds values up to 5 characters and a valid value for the segment is 4 characters, AutoSkip will not move you to the next segment.

Users can see and update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is FLEXFIELDS:AUTOSKIP.

Flexfields:BiDi Direction

This profile option controls the appearance of the flexfields window in Applications running in Semitic languages. Possible values are “Left To Right” and “Right To Left”. If the profile option is not defined on a particular installation, the default value is “Right To Left”, where the window appears in a normal, left to right fashion, and the text and layout are reversed to accommodate the right-to-left nature of the Semitic language environment.

Users can see and update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is FLEXFIELDS:BIDI_DIRECTION.

Flexfields:LOV Warning Limit

Use Flexfields:LOV Warning Limit to improve efficiency when retrieving a list of values.

Sometimes, particularly when no reduction criteria has been specified, an LOV can take a very long time to run if there is a very significant amount of data in it. Set this profile option to the number of rows to be returned before the user is asked whether to continue retrieving the entire list.

Users can see and update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is QUICKPICK_ROWS_BEFORE_WARN.

Flexfields:Open Descr Window

You can control whether a descriptive flexfield window automatically opens when you navigate to a customized descriptive flexfield.

  • “Yes” means that the descriptive flexfield window automatically opens when you navigate to a customized descriptive flexfield.
  • “No” means that when you navigate to a customized descriptive flexfield, you must choose Edit Field from the Edit menu or use the List of Values to open the descriptive flexfield window.

Users can see and update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is FLEXFIELDS:OPEN_DESCR_WINDOW.

Note: This profile option does not apply to descriptive flexfields in folders.

Flexfields:Open Key Window

You can control whether a key flexfield window automatically opens when you navigate to a key flexfield.

  • “Yes” means that the key flexfield window automatically opens when you navigate to a key flexfield.
  • “No” means that when you navigate to a key flexfield, you must choose Edit Field from the Edit menu or use the List of Values to open the key flexfield window.

Users can see and update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is FLEXFIELDS:OPEN_KEY_WINDOW.

Flexfields:Shared Table Lock

This profile option is used by the Oracle Applications flexfields internal code only. You should not alter the value of this profile option unless instructed to do so by an Oracle representative.

The internal name for this profile option is FLEXFIELDS:SHARED_TABLE_LOCK.

Flexfields:Shorthand Entry

If shorthand flexfield entry is defined for your flexfield, you can use a shorthand alias to automatically fill in values for some or all of the segments in a flexfield.

Not Enabled Shorthand Entry is not available for any flexfields for this user, regardless of whether shorthand aliases are defined.
New Entries Only Shorthand Entry is available for entering new records in most foreign key forms. It is not available for combinations forms, updating existing records, or entering queries.
Query and New Entry Shorthand Entry is available for entering new records or for entering queries. It is not available for updating existing records.
All Entries Shorthand Entry is available for entering new records or updating old records. It is not available for entering queries.
Always Shorthand Entry is available for inserting, updating, or querying flexfields for which shorthand aliases are defined.

Users can see and update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is FLEXFIELDS:SHORTHAND_ENTRY.

Flexfields:Show Full Value

If an alias defines valid values for all of the segments in a flexfield, and Flexfields: Shorthand Entry is enabled, when you enter the alias the flexfield window does not appear.

“Yes” displays the full flexfield window with the cursor resting on the last segment.

Users can see and update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is FLEXFIELDS:SHOW_FULL_VALUE.

Flexfields:Validate On Server

This profile option is set to “Yes” to enable server side, PL/SQL flexfields validation for Key Flexfields. This improves performance when using Key Flexfields over a wide area network by reducing the number of network round trips needed to validate the entered segment combinations.

You may find, however, that your validation’s performance is better with client side validation. In this case, set this profile option to “No”.

Users can see and update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is FLEXFIELDS:VALIDATE_ON_SERVER.

FND: Debug Log Filename

The file name for the file to hold debugging messages used in the Logging Service. If the value of this profile option is null, then the Logging Service is turned off.

Users can see and update this profile option.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is AFLOG_FILENAME.

FND: Debug Log Level

The Logging Service can filter out debugging messages depending on their priority level.. There are five levels of the Debug/Trace Service:. In order from highest priority to lowest priority, they are: Errors, Exceptions, Events, Procedures, and Statements. The Debug Log Level is the lowest level that the user wants to see messages for.. The possible profile option values are Null (which means off), and the five priority levels above. For instance, if the “FND: Debug Log Level” profile is set to “EVENT”, then the file will get the messages that the programmer had marked as “EVENT”, “EXCEPTION”, or “ERROR”.

Users can see and update this profile option.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is AFLOG_LEVEL.

FND: Debug Log Module

The Logging Service can filter out debugging messages depending on their module. Module names are unique across applications and coding languages. If a module is specified for this profile option, then only messages for that module will be written to the log file. If this profile option is left blank then messages for all modules will be written to the log file.

Users can see and update this profile option.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is AFLOG_MODULE.

FND: Enable Cancel Query

Oracle Applications allows end users to cancel certain long-running queries, such as retrieving data in a block. When these operations exceed a threshold of time, approximately ten seconds, a dialog will display that allows the user to cancel the query.

Set the FND: Enable Cancel Query profile option to Yes if you wish to enable the ability to cancel a form query. This profile option may be set at the site, application, responsibility or the user level.

Users can see but not update this profile option.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is FND_ENABLE_CANCEL_QUERY.

FND: Indicator Colors

The default for this profile option is null, which means “Yes.” When this profile option is set to Yes:

  • Required fields are displayed in yellow.
  • Queryable fields are displayed in a different color while in enter-query mode.
  • Fields that cannot be entered (read-only) are rendered in dark gray.

Users can see and update this profile option.

Level Visible Allow Update
Site No No
Application No No
Responsibility No No
User Yes Yes

The internal name for this profile option is FND_INDICATOR_COLORS.

FND: Native Client Encoding

FND: Native Client Encoding indicates the character set that a client machine uses as its native character set. The value must be one of the Oracle character sets and should correspond to the client native character set. The character set used in a client machine varies depending on language and platform. For example, if a user uses a Windows machine with Japanese, the value should be JA16SJIS. But if a user uses a Solaris machine with Japanese, the value should be JA16EUC. The value is normally set in the user level since each user uses different machine, but it can be set in every level for a default value.

This profile option is used when storing text data. When a user uploads a text file to be stored in the FND_LOBS table, the current value of FND: Native Client Encoding is stored along with the text data. With the value of this profile option, the server can then convert the text data to another character set as necessary when the text data is downloaded.

Users can see and update this profile option.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is FND_NATIVE_CLIENT_ENCODING.

FND: Override Directory

The FND:Override Directory profile option is used by the Work Directory feature. The value of FND: Override Directory should be the directory containing your alternate files. Typically, this profile option should be set at the User level only.

Users can see and update this profile option.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is APPLWRK.

FND: Resource Consumer Group

Resource consumer groups are used by the Oracle8i Database Resource Manager, which allocates CPU resources among database users and applications. Each form session is assigned to a resource consumer group. The system administrator can assign users to a resource consumer group for all of their forms sessions and transactions. If no resource consumer group is found for a process, the system uses the default group “Default_Consumer_Group”.

Users can see this profile option, but they cannot update it.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is FND_RESOURCE_CONSUMER_GROUP.

Folders:Allow Customization

Your system administrator controls whether you can create or customize a folder definition layout in folder block.

  • “Yes” means that you can create or customize a folder definition, that is, the entire Folder menu is enabled in the folder block.
  • “No” means that you can only open an existing folder definition in a folder block, that is, only the Open option is enabled in the Folder menu.

Users can see this profile option, but they cannot update it.

Level Visible Allow Update
Site No No
Application No No
Responsibility No No
User Yes Yes

The internal name for this profile option is FLEXVIEW:CUSTOMIZATION.

Forms Keyboard Mapping File

Use this profile option to define the path of the Keyboard Mapping File.

The “Keys” window displays the keystrokes to perform standard Forms operations, such as “Next Block” and “Clear Record.” This window can be viewed at anytime by pressing Ctrl+k. The keyboard mappings can be customized as follows:

  • The System Administrator must locate the Oracle Forms resource file on the middle tier, typically called fmrweb.res.
  • Make a copy of the file, name it as desired, and locate it in the same directory as the original.
  • Open the new file in any text editor and make the desired keystroke mapping changes. Comments at the top of the file explain how the mappings are performed.
  • To run the new mapping file, specify the complete path and file name in this profile option.

Users can see and update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is FND_FORMS_TERM.

Forms Runtime Parameters

Use this profile to specify certain forms runtime parameters. The profile value must be entered in as parameter=value. Each parameter-value pair must be separated by a single space. For example:

record=collect log=/tmp/frd.log debug_messages=yes

In order for the parameters updated in this profile option to go into effect, you must exit and log back in to Oracle Applications.

Users can see and update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is FND_MORE_FORM_PARAMS.

Gateway User ID

Oracle login for gateway account. This should be the same as the environment variable GWYUID. For example, applsyspub/pub.

Users can see and but not update this profile option.

This profile option is visible at all levels but may only be updated at the site level.

Level Visible Allow Update
Site Yes Yes
Application Yes No
Responsibility Yes No
User Yes No

The internal name for this profile option is GWYUID.

Help Localization Code

Localized context-sensitive help files are preferred when your System Administrator sets this profile option.

Users can see this profile option, but they cannot update it.

This profile option is visible and updatable at the responsibility and user levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is HELP_LOCALIZATION_CODE.

Help Tree Root

This profile option determines which tree is shown in the navigation frame when context-sensitive help is launched.

If Help Tree Root is set to “null” or “NULL” (case insensitive), then the online help is launched in a single frame, without the navigation and search features.

Users can see this profile option, but they cannot update it.

This profile option is visible and updatable at all levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is HELP_TREE_ROOT.

Help Utility Download Path

Use this profile option to define the directory into which the Help Utility downloads help files from the Oracle Applications Help System.

Users can see this profile option, but they cannot update it.

This profile option is visible and updatable at all levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is HELP_UTIL_DL_PATH.

Help Utility Upload Path

Use this profile option to define the directory from which the Help Utility uploads help files to the Oracle Applications Help System.

Users can see this profile option, but they cannot update it.

This profile option is visible and updatable at all levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is HELP_UTIL_UL_PATH.

Hide Diagnostics Menu Entry

This profile option determines whether users can access the Diagnostics menu entry from the Help menu. If it is set to Yes, the Diagnostics menu entry is hidden. If it is set to No, the Diagnostics menu entry is visible.

Users cannot see nor change this profile option.

This profile option is visible and updatable at the all levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is FND_HIDE_DIAGNOSTICS.

ICX: Client IANA Encoding

This profile option is used to determine the character set of text displayed by Java Server pages. This profile option must be set to match the character set of the Apache server on the Web tier in order for the online help system to support languages other than American English. The default setting is the Western European character set (ISO-8859-1).

This profile option should be set only at the site level.

Users can see this profile option, but they cannot update it.

This profile option is visible and updatable at the all levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is ICX_CLIENT_IANA_ENCODING.

ICX: Discoverer Launcher, Forms Launcher, and Report Launcher

These profile options are used by the Oracle Applications Personal Homepage.

Set the site level value of each of these profile options to the base URL for launching each application. The profile option value should be sufficient to launch the application, but should not include any additional parameters which may be supplied by the Personal Homepage.

Users can see these profile options, but they cannot update them.

These profile options are visible and updatable at all levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for these profile options are ICX_DISCOVERER_LAUNCHER, ICX_FORMS_LAUNCHER, and ICX_REPORT_LAUNCHER.

ICX: Limit connect

This profile option determines the maximum number of connection requests a user can make in a single session.

Users cannot see or update this profile option.

Level Visible Allow Update
Site Yes Yes
Application No No
Responsibility No No
User Yes Yes

The internal name for this profile option is ICX_LIMIT_CONNECT.

ICX: Limit time

This profile option determines the absolute maximum duration (in hours) of a user’s session, regardless of activity.

Users cannot see or update this profile option.

Level Visible Allow Update
Site Yes Yes
Application No No
Responsibility No No
User Yes Yes

The internal name for this profile option is ICX_LIMIT_TIME.

ICX: Session Timeout

This profile option determines the length of time (in minutes) of inactivity in a user’s session before the session is disabled. If the user does not perform any operation in Oracle Applications for longer than this value, the session is disabled. The user is provided the opportunity to re-authenticate and re-enable a timed-out session. If re-authentication is successful, the session is re-enabled and no work is lost. Otherwise, Oracle Applications exit without saving pending work.

If this profile option to 0 or NULL, then user sessions will never time out due to inactivity.

Users can see this profile option, but they cannot update it.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is ICX_SESSION_TIMEOUT.

Indicate Attachments

This profile option allows you to turn off indication of attachments when querying records (for performance reasons).

Users can see and update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is ATCHMT_SET_INDICATOR.

Initialization SQL Statement – Custom

This profile option allows you to add site-specific initialization code (such as optimizer settings) that will be executed at database session startup. The value of this profile option must be a valid SQL statement.

The system administrator may set this profile option at any level.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is FND_INIT_SQL.

Initialization SQL Statement – Oracle

This profile option is used to add application-specific code. The value is a valid SQL statement (or a PL/SQL block for more than one statement), that is executed at startup of every database session.

The value of this profile option is delivered as seed data and cannot be updated.

Attention: Do not attempt to modify the value of this profile option. Use the profile option Initialization SQL Statement – Custom to add custom initialization code.

This profile option is set at the application level only. The initialization code will be executed only for responsibilities owned by that application.

Level Visible Allow Update
Site Yes No
Application Yes No
Responsibility Yes No
User Yes No

The internal name for this profile option is FND_APPS_INIT_SQL.

Java Color Scheme

If the Java Look and Feel profile option is set to Oracle, the Java Color Scheme can be specified as follows:

  • Teal
  • Titanium
  • Red
  • Khaki
  • Blue
  • Olive
  • Purple

The Java Color Scheme profile has no effect if the Java Look and Feel is set to Generic.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is FND_COLOR_SCHEME.

Java Look and Feel

Oracle Applications Professional User Interface can be run with either the Oracle Look and Feel or the Generic Look and Feel. The Oracle Look and Feel consists of a new look and feel for each item, and a predefined set of color schemes. The Generic Look and Feel adheres to the native interface and color scheme of the current operating system.

To specify the look and feel set this profile to “generic” or “oracle”.

If the Oracle Look and Feel is used, the profile Java Color Scheme can be set. The Java Color Scheme profile has no effect if the Java Look and Feel is set to Generic.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is FND_LOOK_AND_FEEL.

Maximum Page Length

Determines the maximum number of lines per page in a report.

Users can see and update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is MAX_PAGE_LENGTH.

MO:Operating Unit

Determines the Operating Unit the responsibility logs onto.

Users can see and update this profile option.

This profile option is visible and updatable at the responsibility level only.

Level Visible Allow Update
Site No No
Application No No
Responsibility Yes Yes
User No No

The internal name for this profile option is ORG_ID.

Node Trust Level

Determines the level of trust assigned to a Web server. This profile option uses the Server hierarchy type.

Users can see but not update this profile option.

This profile option is visible and updatable at the site and server level only.

Level Visible Allow Update
Site Yes Yes
Application NA NA
Responsibility NA NA
Server Yes Yes
User No No

The internal name for this profile option is NODE_TRUST_LEVEL.

Personnel Employee:Installed

When enabled, “Personnel Employee:Installed” allows you as System Administrator to link an application username and password to an employee name.

  • The “Person” field is usable on the Define Application User form (\ Navigate Security User).

Oracle Purchasing uses this capability to associate an employee in your organization with an Oracle Applications user.

The installation process enables this profile option. You cannot change the value of “Personnel Employee: Installed”.

Users cannot see nor change this profile option.

This profile option is visible at the site level, but cannot be updated.

Level Visible Allow Update
Site Yes No
Application No No
Responsibility No No
User No No

The internal name for this profile option is PER_EMPLOYEE:INSTALLED.

Printer

You can select the printer which prints your reports. If a printer cannot be selected, contact your system administrator. Printers must be registered with Oracle Applications.

Users can see and update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is PRINTER.

RRA:Delete Temporary Files

When using a custom editor to view a concurrent output or log file, the Report Review Agent will make a temporary copy of the file on the client. Set this profile to “Yes” to automatically delete these files when the user exits Oracle Applications.

Only the System Administrator can update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is FS_DELETE.

RRA:Enabled

Set this user profile to “Yes” to use the Report Review Agent to access files on concurrent processing nodes.

Only the System Administrator can update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is FS_ENABLED.

RRA: Service Prefix

Using this new profile option allows you to override the default service name prefix (FNDFS_) assigned to the Report Review Agent. By assigning a new prefix to the Report Review Agent you can avoid having multiple instances of the Applications share executables.

Valid values for this option must be nine characters or less and use only alphanumeric characters or the underscore. We recommend using the underscore character as the last character of your value as in the default value “FNDFS_”.

Users cannot see or update this profile option.

This profile option is visible and updatable at the site level only.

Level Visible Allow Update
Site Yes Yes
Application No No
Responsibility No No
User No No

The internal name for this profile option is FS_SVC_PREFIX.

Attention: GLDI will not support the “RRA: Service Prefix” profile until release 4.0 and so uses the default prefix “FNDFS_” regardless of the value entered for the profile option. Consequently, you must ensure that at least one of your Report Review Agents maintains the default prefix in order for GLDI to access the application executables.

RRA:Maximum Transfer Size

Specify, in bytes, the maximum allowable size of files transferred by the Report Review Agent, including those downloaded by a user with the “Copy File…” menu option in the Oracle Applications Report File Viewer and those “temporary” files which are automatically downloaded by custom editors. For example, to set the size to 64K you enter 65536. If this profile is null, there is no size limit.

Only the System Administrator can update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is FS_MAX_TRANS.

Self Service Personal Home Page Mode

This profile option determines the type of home page for users. The three possible values are: “Framework only”,” Personal Home Page”, and “Personal Home Page with Framework”.

Framework only The E-Business Suite Home page is used.
Personal Home Page The Personal Homepage is used.
Personal Home Page with Framework The Personal Homepage appears first when a user logs in. After the user chooses a responsibility, an Oracle Applications Framework page appears for navigation among the functions for that responsibility.

After this profile option is set, you need to bounce the middle tier server to clear its cache and to see your changes.

Users can see and update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is APPLICATIONS_HOME_PAGE.

Sequential Numbering

Sequential Numbering assigns numbers to documents created by forms in Oracle financial products. For example, when you are in a form that creates invoices, each invoice document can be numbered sequentially.

Sequential numbering provides a method of checking whether documents have been posted or lost. Not all forms within an application may be selected to support sequential numbering.

Sequential Numbering has the following profile option settings:

Always Used You may not enter a document if no sequence exists for it.
Not Used You may always enter a document.
Partially Used You will be warned, but not prevented from entering a document, when no sequence exists.

Users can see this profile option, but they cannot update it.

This profile option is visible and updatable at the site, application, and responsibility levels.

Note: If you need to control Sequential Numbering for each of your set of books, use the ‘Responsibility’ level. Otherwise, we recommend that you use either the ‘Site’ or ‘Application’ level to set this option.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User No No

The internal name for this profile option is UNIQUE:SEQ_NUMBERS.

Server Timezone

The time zone of the database server.

Users can see this profile option, but they cannot update it.

This profile option is visible and updatable at the site, application, and responsibility levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User No No

The internal name for this profile option is SERVER_TIMEZONE_ID.

Session ID

This runtime profile option contains the session ID number of the last database session that was created.

Users can see this profile option, but they cannot update it.

This profile option is neither visible nor updatable from the System Profile Options form.

Level Visible Allow Update
Site No No
Application No No
Responsibility No No
User No No

The internal name for this profile option is DB_SESSION_ID.

Sign-On:Audit Level

Sign-On:Audit Level allows you to select a level at which to audit users who sign on to Oracle Applications. Four audit levels increase in functionality: None, User, Responsibility, and Form.

None is the default value, and means do not audit any users who sign on to Oracle Applications.

Auditing at the User level tracks:

  • who signs on to your system
  • the times users log on and off
  • the terminals in use

Auditing at the Responsibility level performs the User level audit functions and tracks:

  • the responsibilities users choose
  • how much time users spend using each responsibility

Auditing at the Form level performs the Responsibility level audit functions and tracks:

  • the forms users choose
  • how long users spend using each form
  • System Administrator visible, updatable at all levels.

Users cannot see nor change this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is SIGNONAUDIT:LEVEL.

Sign-On:Notification

“Yes” displays a message at login that indicates:

  • If any concurrent requests failed since your last session,
  • How many times someone tried to log on to Oracle Applications with your username but an incorrect password, and
  • When the default printer identified in your user profile is unregistered or not specified.

Users can see and update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is SIGNONAUDIT:NOTIFY.

Signon Password Failure Limit

The Signon Password Failure Limit profile option determines the maximum number of login attempts before the user’s account is disabled.

Users cannot see or update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is SIGNON_PASSWORD_FAILURE_LIMIT.

Signon Password Hard to Guess

The Signon Password Hard to Guess profile option sets rules for choosing passwords to ensure that they will be “hard to guess.” A password is considered hard-to-guess if it follows these rules:

  • The password contains at least one letter and at least one number.
  • The password does not contain the username.
  • The password does not contain repeating characters.

Users can see but not update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is SIGNON_PASSWORD_HARD_TO_GUESS.

Signon Password Length

Signon Password Length sets the minimum length of an Applications signon password. If no value is entered the minimum length defaults to 5.

Users can see but not update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is SIGNON_PASSWORD_LENGTH.

Signon Password No Reuse

This profile option specifies the number of days that a user must wait before being allowed to reuse a password.

Users can see but not update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is SIGNON_PASSWORD_NO_REUSE.

Site Name

Site Name identifies an installation of Oracle Applications. The installation process sets this to “No Site Name Specified”.

You should set a value for “Site Name” after installation.

The Site Name appears in the title of the MDI window. If you want additional information on your installation to appear in the title, for example, “Test” or “Production”, you can add that information here

Users cannot see nor change this profile option.

This profile option is visible and updatable at the site level.

Level Visible Allow Update
Site Yes Yes
Application No No
Responsibility No No
User No No

The internal name for this profile option is SITENAME.

Socket Listener Activated

This profile option is a flag that indicates whether the FormsClient Controller (Socket Listener) should be started by the signon form.

Users can see but not update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is SOCKET_LISTENER_ACTIVATED.

Socket Listener Port

This profile option defines the port number used by the Forms Client Controller.

The default value for this profile option is ’6945′.

Oracle Workflow uses this profile option. When a user chooses to launch a form from a Workflow notification, Oracle Workflow will look for the value of this profile option and launch the form in the specified port.

If the socket listener port is not set at user level, Oracle Workflow launches attached forms at the default port set for the site. However, if users have set different ports, Oracle Workflow launches the forms for each user at the specified port. By using different socket listener ports, two different users logged into Oracle Applications on the same machine can both launch attached forms at the same time without interference from each other.

The E-Business Suite Home page also uses the Socket Listener Port profile for launching forms from Framework HTML sessions. With this architecture, a user navigating through different forms/responsibilities in a Framework session will reuse the same Oracle Forms session instead of opening multiple ones. So a user will never have more than one Forms session open on his/her PC at any given time, for a given database.

It is possible to have multiple Oracle Forms sessions open where each is connected to a different database, but the Socket Listener Port profile must be set to a different value beforehand on each database. For example, set it to 6945 on database A, 6946 on database B, and 6947 on database C. This profile option must be set at the site level in advance of any users attempting to use this functionality, as it cannot be set on a per-user basis.

Users can see but not update this profile option.

This profile option is visible and updatable at the site, application, and responsibility levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User No No

The internal name for this profile option is SOCKET_LISTENER_PORT.

Stored Procedure Log Directory

Specifying a log directory enables stored procedures used with the Oracle database to generate and store log files. You must also set this log directory in the init.ora file of the database.

For example, if the Stored Procedure Log Directory is /rladev/rla/1.1/log and the Stored Procedure Output Directory is /rladev/rla/1.1/out, then the following entry should be made in the init.ora file of the database containing stored procedures that write to these directories:

UTL_FILE_DIR = /rladev/rla/1.1/log,/rladev/rla/1.1/out

Users cannot see nor change this profile option.

This profile option is visible and updatable at the site level.

Level Visible Allow Update
Site Yes Yes
Application No No
Responsibility No No
User No No

The internal name for this profile option is UTL_FILE_LOG.

Stored Procedure Output Directory

Specifying a output directory enables stored procedures used with the Oracle database to generate and store output files. You must also set this output directory in the init.ora file of the database.

For example, if the Stored Procedure Log Directory is /rladev/rla/1.1/log and the Stored Procedure Output Directory is /rladev/rla/1.1/out, then the following entry should be made in the init.ora file of the database containing stored procedures that write to these directories:

UTL_FILE_DIR = /rladev/rla/1.1/log,/rladev/rla/1.1/out

Users cannot see nor change this profile option.

This profile option is visible and updatable at the site level.

Level Visible Allow Update
Site Yes Yes
Application No No
Responsibility No No
User No No

The internal name for this profile option is UTL_FILE_OUT.

TCF: HOST

Together with the TCF:PORT profile, this profile identifies the network location of the TCF Server. The TCF Server supports various parts of the Oracle Applications UI by executing some of their associated server logic and providing access to the database.

In most configurations, these profiles will be set by the TCF Server’s administrative utility ‘ServerControl’ at the same time the TCF Server is started up. ServerControl will set these two profiles (TCF:HOST, TCF:PORT) at the site level.

For particularly complex environments, it may be appropriate to direct different users to separate TCF Servers by setting these profiles to distinct values at the Application level. Consult the post installation instructions for details on TCF Server configuration options.

This profile option is visible at all levels and updatable at the site and application level only.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes No
User Yes No

The internal name for this profile option is TCF:HOST.

See: Administering the TCF Server

TCF: PORT

Together with the TCF:HOST profile, this profile identifies the network location of the TCF Server. The TCF Server supports various parts of the Oracle Applications UI by executing some of their associated server logic and providing access to the database.

In most configurations, these profiles will be set by the TCF Server’s administrative utility ‘ServerControl’ at the same time the TCF Server is started up. ServerControl will set these two profiles (TCF:HOST, TCF:PORT) at the site level.

For particularly complex environments, it may be appropriate to direct different users to separate TCF Servers by setting these profiles to distinct values at the Application level. Consult Installing Oracle Applications for details on the TCF Server configuration options.

Users can see and but not update this profile option.

This profile option is visible at all levels and updatable at the site and application level only.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes No
User Yes No

The internal name for this profile option is TCF:PORT.

See: Administering the TCF Server

Two Task

TWO_TASK for the database. This profile is used in conjunction with the Gateway User ID profile to construct a connect string for use in creating dynamic URLs for the Web Server. This should be set to the SQL*NET. alias for the database.

Note: The TWO_TASK must be valid on the node upon which the WebServer is running

Users can see and but not update this profile option.

This profile option is visible at all levels but may only be updated at site level.

Level Visible Allow Update
Site Yes Yes
Application Yes No
Responsibility Yes No
User Yes No

The internal name for this profile option is TWO_TASK.

Utilities: Diagnostics

Utilities: Diagnostics determines whether a user can automatically use the Diagnostics features. If Utilities:Diagnostics is set to Yes, then users can automatically use these features. If Utilities:Diagnostics is set to No, then users must enter the password for the APPS schema to use the Diagnostics features.

Users cannot see nor change this profile option.

This profile option is visible and updatable at the all levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is DIAGNOSTICS.

Utilities:SQL Trace

SQL trace files can now be generated for individual concurrent programs. The trace can be enabled at the user level by setting the profile “Utilities:SQL Trace” to “Yes”. This profile can be enabled for a user only by System Administrator so that it is not accidentally turned on and disk usage can be monitored.

For more information on SQL trace, see the Oracle database documentation.

Users cannot see nor change this profile option.

This profile option is visible and updatable at the all levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

Viewer: Application for HTML, PCL, PDF, Postscript, and Text

These profile options determine the applications a user will use to view reports in the given output formats. For example, you could set Viewer: Application for Text to ‘application/word’ to view a Text report in Microsoft Word.

Valid values are defined by the system administrator in the Viewer Options form.

Users can see and update these profile options.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal names for these profile options are FS_MIME_HTML, FS_MIME_PCL, FS_MIME_PDF, FS_MIME_PS, and FS_MIME_TEXT.

Viewer:Default Font Size

Using this new profile option, you can set the default font size used when you display report output in the Report Viewer.

The valid values for this option are 6, 8, 10, 12, and 14.

Users can see and update this profile option.

This profile option is visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Yes

The internal name for this profile option is FNDCPVWR_FONT_SIZE..

Viewer: Text

The Viewer: Text profile option allows you to send report output directly to a browser window rather than using the default Report Viewer. Enter “Browser” in this profile option to enable this feature.

Users can see and update the Viewer:Text profile option.

This profile option is both visible and updatable at all four levels.

Level Visible Allow Update
Site Yes Yes
Application Yes Yes
Responsibility Yes Yes
User Yes Ys

The internal name for this profile option is EDITOR_CHAR.

 

Oracle Applications 11i

Row Chaining and Migeration

Leave a Comment Posted by on November 1, 2010

Overview

If you notice poor performance in your Oracle database Row Chaining and Migration may be one of several reasons, but we can prevent some of them by properly designing and/or diagnosing the database. Row Migration & Row Chaining are two potential problems that can be prevented. By suitably diagnosing, we can improve database performance. The main considerations are: What is Row Migration & Row Chaining ? How to identify Row Migration & Row Chaining ? How to avoid Row Migration & Row Chaining ? Migrated rows affect OLTP systems which use indexed reads to read singleton rows. In the worst case, you can add an extra I/O to all reads which would be really bad. Truly chained rows affect index reads and full table scans.

Oracle Block

The Operating System Block size is the minimum unit of operation (read /write) by the OS and is a property of the OS file system. While creating an Oracle database we have to choose the «Data Base Block Size» as a multiple of the Operating System Block size. The minimum unit of operation (read /write) by the Oracle database would be this «Oracle block», and not the OS block. Once set, the «Data Base Block Size» cannot be changed during the life of the database (except in case of Oracle 9i). To decide on a suitable block size for the database, we take into consideration factors like the size of the database and the concurrent number of transactions expected. The database block has the following structure (within the whole database structure)

oracle_data_block

Header

Header contains the general information about the data i.e. block address, and type of segments (table, index etc). It Also contains the information about table and the actual row (address) which that holds the data.

Free Space

Space allocated for future update/insert operations. Generally affected by the values of PCTFREE and PCTUSED parameters.

Data

Actual row data.

FREELIST, PCTFREE and PCTUSED While creating / altering any table/index, Oracle used two storage parameters for space control.

  • PCTFREE – The percentage of space reserved for future update of existing data.
  • PCTUSED – The percentage of minimum space used for insertion of new row data. This value determines when the block gets back into the FREELISTS structure.
  • FREELIST – Structure where Oracle maintains a list of all free available blocks. Oracle will first search for a free block in the FREELIST and then the data is inserted into that block. The availability of the block in the FREELIST is decided by the PCTFREE value. Initially an empty block will be listed in the FREELIST structure, and it will continue to remain there until the free space reaches the PCTFREE value.

When the free space reach the PCTFREE value the block is removed from the FREELIST, and it is re-listed in the FREELIST table when the volume of data in the block comes below the PCTUSED value. Oracle use FREELIST to increase the performance. So for every insert operation, oracle needs to search for the free blocks only from the FREELIST structure instead of searching all blocks.

Row Migration

We will migrate a row when an update to that row would cause it to not fit on the block anymore (with all of the other data that exists there currently). A migration means that the entire row will move and we just leave behind the «forwarding address». So, the original block just has the rowid of the new block and the entire row is moved.

ora_row_migration_1.jpg

Full Table Scans are not affected by migrated rows

The forwarding addresses are ignored. We know that as we continue the full scan, we’ll eventually get to that row so we can ignore the forwarding address and just process the row when we get there. Hence, in a full scan migrated rows don’t cause us to really do any extra work — they are meaningless.

Index Read will cause additional IO’s on migrated rows

When we Index Read into a table, then a migrated row will cause additional IO’s. That is because the index will tell us «goto file X, block Y, slot Z to find this row». But when we get there we find a message that says «well, really goto file A, block B, slot C to find this row». We have to do another IO (logical or physical) to find the row.

Row Chaining

A row is too large to fit into a single database block. For example, if you use a 4KB blocksize for your database, and you need to insert a row of 8KB into it, Oracle will use 3 blocks and store the row in pieces. Some conditions that will cause row chaining are: Tables whose rowsize exceeds the blocksize. Tables with long and long raw columns are prone to having chained rows. Tables with more then 255 columns will have chained rows as Oracle break wide tables up into pieces. So, instead of just having a forwarding address on one block and the data on another we have data on two or more blocks.

ora_row_chained_1.jpg

Chained rows affect us differently. Here, it depends on the data we need. If we had a row with two columns that was spread over two blocks, the query: select column1 from table where column1 is in Block 1, would not cause any «table fetch continued row». It would not actually have to get column2, it would not follow the chained row all of the way out. On the other hand, if we ask for: select column2 from table and column2 is in Block 2 due to row chaining, then you would in fact see a «table fetch continued row»

Example

The following example was published by Tom Kyte, it will show row migration and chaining. We are using an 4k block size:

select name,value from v$parameter where name = ‘db_block_size’;

NAME   VALUE
————– ——
db_block_size  4096

Create the following table with CHAR fixed columns:

create table row_mig_chain_demo (
x int primary key,
a char(1000),
b char(1000),
c char(1000),
d char(1000),
e char(1000)
);

That is our table. The char(1000)’s will let us easily cause rows to migrate or chain. We used 5 columns a,b,c,d,e so that the total rowsize can grow to about 5K, bigger than one block, ensuring we can truly chain a row.

insert into row_mig_chain_demo (x) values (1);
insert into row_mig_chain_demo (x) values (2);
insert into row_mig_chain_demo (x) values (3);
commit;

We are not interested about seeing a,b,c,d,e – just fetching them. They are really wide so we’ll surpress their display.

column a noprint
column b noprint
column c noprint
column d noprint
column e noprint

select * from row_mig_chain_demo;

X
———-
1
2
3

Check for chained rows:

select a.name, b.value
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and lower(a.name) = ‘table fetch continued row’;

NAME                             VALUE
----------------------------     ----------
table fetch continued row        0

Now that is to be expected, the rows came out in the order we put them in (Oracle full scanned this query, it processed the data as it found it). Also expected is the table fetch continued row is zero. This data is so small right now, we know that all three rows fit on a single block. No chaining.

ora_row_example_1.jpg

Demonstration of the Row Migration

Now, lets do some updates in a specific way. We want to demonstrate the row migration issue and how it affects the full scan:

update row_mig_chain_demo set a = ‘z1′, b = ‘z2′, c = ‘z3′ where x = 3;
commit;
update row_mig_chain_demo set a = ‘y1′, b = ‘y2′, c = ‘y3′ where x = 2;
commit;
update row_mig_chain_demo set a = ‘w1′, b = ‘w2′, c = ‘w3′ where x = 1;
commit;

Note the order of updates, we did last row first, first row last.

select * from row_mig_chain_demo;

X
———-
3
2
1

select a.name, b.value
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and lower(a.name) = ‘table fetch continued row’;

NAME                                                                  VALUE
—————————————————————- ———-
table fetch continued row                                                 0

Interesting, the rows came out «backwards» now. That is because we updated row 3 first. It did not have to migrate, but it filled up block 1. We then updated row 2. It migrated to block 2 with row 3 hogging all of the space, it had to. We then updated row 1, it migrated to block 3. We migrated rows 2 and 1, leaving 3 where it started.

So, when Oracle full scanned the table, it found row 3 on block 1 first, row 2 on block 2 second and row 1 on block 3 third. It ignored the head rowid piece on block 1 for rows 1 and 2 and just found the rows as it scanned the table. That is why the table fetch continued row is still zero. No chaining.

ora_row_example_2.jpg

So, lets see a migrated row affecting the «table fetch continued row»:

select * from row_mig_chain_demo where x = 3;

X
———-
3

select a.name, b.value
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and lower(a.name) = ‘table fetch continued row’;

NAME                                                                  VALUE
—————————————————————- ———-
table fetch continued row                                                 0

This was an index range scan / table access by rowid using the primary key.  We didn’t increment the «table fetch continued row» yet since row 3 isn’t migrated.

select * from row_mig_chain_demo where x = 1;

X
———-
1

select a.name, b.value
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and lower(a.name) = ‘table fetch continued row’;

NAME                                      VALUE
————————— ———-
table fetch continued row       1

Row 1 is migrated, using the primary key index, we forced a «table fetch continued row».

Demonstration of the Row Chaining

update row_mig_chain_demo set d = ‘z4′, e = ‘z5′ where x = 3;
commit;

Row 3 no longer fits on block 1. With d and e set, the rowsize is about 5k, it is truly chained.

select x,a from row_mig_chain_demo where x = 3;

X
———-
3

select a.name, b.value
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and lower(a.name) = ‘table fetch continued row’;

NAME                                                                  VALUE
—————————————————————- ———-
table fetch continued row                                                 1

We fetched column «x» and «a» from row 3 which are located on the «head» of the row, it will not cause a «table fetch continued row». No extra I/O to get it.

ora_row_example_3.jpg select x,d,e from row_mig_chain_demo where x = 3;

select a.name, b.value
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and lower(a.name) = ‘table fetch continued row’;

NAME                                                                  VALUE
—————————————————————- ———-
table fetch continued row                                                 2

Now we fetch from the «tail» of the row via the primary key index. This increments the «table fetch continued row» by one to put the row back together from its head to its tail to get that data.

Now let’s see a full table scan – it is affected as well:

select * from row_mig_chain_demo;

X
———-
3
2
1

select a.name, b.value
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and lower(a.name) = ‘table fetch continued row’;

NAME                                                                  VALUE
—————————————————————- ———-
table fetch continued row                                                 3

The «table fetch continued row» was incremented here because of Row 3, we had to assemble it to get the trailing columns.  Rows 1 and 2, even though they are migrated don’t increment the «table fetch continued row» since we full scanned.

select x,a from row_mig_chain_demo;

X
———-
3
2
1

select a.name, b.value
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and lower(a.name) = ‘table fetch continued row’;

NAME                                                                  VALUE
—————————————————————- ———-
table fetch continued row                                                 3

No «table fetch continued row» since we didn’t have to assemble Row 3, we just needed the first two columns.

select x,e from row_mig_chain_demo;

X
———-
3
2
1

select a.name, b.value
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and lower(a.name) = ‘table fetch continued row’;

NAME                                                                  VALUE
—————————————————————- ———-
table fetch continued row                                                 4

But by fetching for d and e, we incemented the «table fetch continued row». We most likely have only migrated rows but even if they are truly chained, the columns you are selecting are at the front of the table.

So, how can you decide if you have migrated or truly chained?

Count the last column in that table. That’ll force to construct the entire row.

select count(e) from row_mig_chain_demo;

COUNT(E)
———-
1

select a.name, b.value
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and lower(a.name) = ‘table fetch continued row’;

NAME                                                                  VALUE
—————————————————————- ———-
table fetch continued row                                                 5

Analyse the table to verify the chain count of the table:

analyze table row_mig_chain_demo compute statistics;

select chain_cnt
from user_tables
where table_name = ‘ROW_MIG_CHAIN_DEMO’;

CHAIN_CNT
———-
3

Three rows that are chained. Apparently, 2 of them are migrated (Rows 1 and 2) and one is truly chained (Row 3).

Total Number of «table fetch continued row» since instance startup?

The V$SYSSTAT view tells you how many times, since the system (database) was started you did a «table fetch continued row» over all tables.

sqlplus system/<password>

SELECT ‘Chained or Migrated Rows = ‘||value
FROM v$sysstat
WHERE name = ‘table fetch continued row’;

Chained or Migrated Rows = 31637

You could have 1 table with 1 chained row that was fetched 31’637 times. You could have 31’637 tables, each with a chained row, each of which was fetched once. You could have any combination of the above — any combo.

Also, 31’637 – maybe that’s good, maybe that’s bad. it is a function of

  • how long has the database has been up
  • how many rows is this as a percentage of total fetched rows.
    For example if 0.001% of your fetched are table fetch continued row, who cares!

Therefore, always compare the total fetched rows against the continued rows.

select name,value from v$sysstat where name like ‘%table%’;

select name,value from v$sysstat where name like ‘%table%’;

NAME                                                                  VALUE
—————————————————————- ———-
table scans (short tables)                                           124338
table scans (long tables)                                              1485
table scans (rowid ranges)                                                0
table scans (cache partitions)                                           10
table scans (direct read)                                                 0
table scan rows gotten                                             20164484
table scan blocks gotten                                            1658293
table fetch by rowid                                                1883112
table fetch continued row                                             31637
table lookup prefetch client count                                        0

How many Rows in a Table are chained?

The USER_TABLES tells you immediately after an ANALYZE (will be null otherwise) how many rows in the table are chained.

analyze table row_mig_chain_demo compute statistics;

select chain_cnt,
round(chain_cnt/num_rows*100,2) pct_chained,
avg_row_len, pct_free , pct_used
from user_tables
where table_name = ‘ROW_MIG_CHAIN_DEMO’;

CHAIN_CNT PCT_CHAINED AVG_ROW_LEN   PCT_FREE   PCT_USED
———- ———– ———– ———- ———-
3         100        3691         10         40

PCT_CHAINED shows 100% which means all rows are chained or migrated.

How to avoid Chained and Migrated Rows?

Increasing PCTFREE can help to avoid migrated rows. If you leave more free space available in the block, then the row has room to grow. You can also reorganize or re-create tables and indexes that have high deletion rates. If tables frequently have rows deleted, then data blocks can have partially free space in them. If rows are inserted and later expanded, then the inserted rows might land in blocks with deleted rows but still not have enough room to expand. Reorganizing the table ensures that the main free space is totally empty blocks.

The ALTER TABLE … MOVE statement enables you to relocate data of a nonpartitioned table or of a partition of a partitioned table into a new segment, and optionally into a different tablespace for which you have quota. This statement also lets you modify any of the storage attributes of the table or partition, including those which cannot be modified using ALTER TABLE. You can also use the ALTER TABLE … MOVE statement with the COMPRESS keyword to store the new segment using table compression.

  1. ALTER TABLE MOVEFirst count the number of Rows per Block before the ALTER TABLE MOVEselect dbms_rowid.rowid_block_number(rowid) “Block-Nr”, count(*) “Rows”
    from row_mig_chain_demo
    group by dbms_rowid.rowid_block_number(rowid) order by 1; Block-Nr        Rows
    ———- ———-
    2066          3Now, de-chain the table, the ALTER TABLE MOVE rebuilds the row_mig_chain_demo table in a new segment, specifying new storage parameters:ALTER TABLE row_mig_chain_demo MOVE
    PCTFREE 20
    PCTUSED 40
    STORAGE (INITIAL 20K
    NEXT 40K
    MINEXTENTS 2
    MAXEXTENTS 20
    PCTINCREASE 0);Table altered. 

    Again count the number of Rows per Block after the ALTER TABLE MOVE

    select dbms_rowid.rowid_block_number(rowid) “Block-Nr”, count(*) “Rows”
    from row_mig_chain_demo
    group by dbms_rowid.rowid_block_number(rowid) order by 1;

    Block-Nr        Rows
    ———- ———-
    2322          1
    2324          1
    2325          1

  2. Rebuild the Indexes for the TableMoving a table changes the rowids of the rows in the table. This causes indexes on the table to be marked UNUSABLE, and DML accessing the table using these indexes will receive an ORA-01502 error. The indexes on the table must be dropped or rebuilt. Likewise, any statistics for the table become invalid and new statistics should be collected after moving the table.analyze table row_mig_chain_demo compute statistics;ERROR at line 1:
    ORA-01502: index ‘SCOTT.SYS_C003228′ or partition of such index is in unusable
    stateThis is the primary key of the table which must be rebuilt.alter index SYS_C003228 rebuild;
    Index altered.analyze table row_mig_chain_demo compute statistics;
    Table analyzed. 

    select chain_cnt,
    round(chain_cnt/num_rows*100,2) pct_chained,
    avg_row_len, pct_free , pct_used
    from user_tables
    where table_name = ‘ROW_MIG_CHAIN_DEMO’;

    CHAIN_CNT PCT_CHAINED AVG_ROW_LEN   PCT_FREE   PCT_USED
    ———- ———– ———– ———- ———-
    1       33.33        3687         20         40If the table includes LOB column(s), this statement can be used to move the table along with LOB data and LOB index segments (associated with this table) which the user explicitly specifies. If not specified, the default is to not move the LOB data and LOB index segments.

Detect all Tables with Chained and Migrated Rows

Using the CHAINED_ROWS table, you can find out the tables with chained or migrated rows.

  1. Create the CHAINED_ROWS tablecd $ORACLE_HOME/rdbms/admin
    sqlplus scott/tiger
    @utlchain.sql
  2. Analyse all or only your Tablesselect ‘analyze table ‘||table_name||’ list chained rows into CHAINED_ROWS;’
    from user_tables
    /analyze table ROW_MIG_CHAIN_DEMO list chained rows into CHAINED_ROWS;
    analyze table DEPT list chained rows into CHAINED_ROWS;
    analyze table EMP list chained rows into CHAINED_ROWS;
    analyze table BONUS list chained rows into CHAINED_ROWS;
    analyze table SALGRADE list chained rows into CHAINED_ROWS;
    analyze table DUMMY list chained rows into CHAINED_ROWS;Table analyzed.
  3. Show the RowIDs for all chained rowsThis will allow you to quickly see how much of a problem chaining is in each table. If chaining is prevalent in a table, then that table should be rebuild with a higher value for PCTFREEselect owner_name,
    table_name,
    count(head_rowid) row_count
    from chained_rows
    group by owner_name,table_name
    /OWNER_NAME                     TABLE_NAME                      ROW_COUNT
    —————————— —————————— ———-
    SCOTT                          ROW_MIG_CHAIN_DEMO                      1

Conclusion

Migrated rows affect OLTP systems which use indexed reads to read singleton rows. In the worst case, you can add an extra I/O to all reads which would be really bad. Truly chained rows affect index reads and full table scans.

  • Row migration is typically caused by UPDATE operation
  • Row chaining is typically caused by INSERT operation.
  • SQL statements which are creating/querying these chained/migrated rows will degrade the performance due to more I/O work.
  • To diagnose chained/migrated rows use ANALYZE command , query V$SYSSTAT view
  • To remove chained/migrated rows use higher PCTFREE using ALTER TABLE MOVE.

 

Database 10g Performance Tuning, Oracle Database 10g ,

← Older posts

Newer posts →

Follow

Get every new post delivered to your Inbox.

Join 156 other followers