There are two methods of doing Automatic Asser Numbering:
Method#1: Make the Sequence No Cache
1. The first and most obvious option would be not to cache the sequence values at all. But the result of this could be a moderate to severe degradation of the application's performance.
SQL> alter sequence fa_additions_s nocache;
Method#2: Pin the Sequence:
2. A better solution is to prevent sequences from ageing out of the library cache by pinning them using dbms_shared_pool.keep(). Pinning the sequence will prevent the sequence values from being aged out of the cache. Pinning the sequence is achieved by invoking the rdbms package dbms_shared_pool.keep() as follows:
SQL> exec dbms_shared_pool.keep('APPS.FA_ADDITIONS_S','Q')
When you shut down a database normally, either "SHUTDOWN NORMAL" or "SHUTDOWN IMMEDIATE", the database takes care of making sure all sequences are "in sequence". All cached sequence values are verified, and the last_number column in dba_sequences is updated accordingly. Thus, under normal circumstances and assuming that your sequence has been pinned, you shouldn't lose cached sequence values during a normal shutdown.
If the database experiences instance failure or a "SHUTDOWN ABORT" statement is issued, you lose any unused cached sequence values. Oracle may also skip sequence values after an export and Import and rollbacks.
To achive this either you have to modify the $ORACLE_HOME/bin/dbstart script and incorporate the pinning or you can create after database startup trigger.
Create a Startup Trigger:
create table AGCST.AG_FA_DBSTRT_PIN (
execution_date date
);
CREATE OR REPLACE TRIGGER DB_STRT_FA_PINING AFTER STARTUP ON DATABASE BEGIN
dbms_shared_pool.keep('APPS.FA_ADDITIONS_S','Q');
INSERT INTO AGCST.AG_FA_DBSTRT_PIN (execution_date) select sysdate from dual;
END;
/
This is the trigger I have used for Pinning the object while doing Automatic Gapless Asset Numbering Implementation. Table is created to know that the trigger has fired after every startup
Method#1: Make the Sequence No Cache
1. The first and most obvious option would be not to cache the sequence values at all. But the result of this could be a moderate to severe degradation of the application's performance.
SQL> alter sequence fa_additions_s nocache;
Method#2: Pin the Sequence:
2. A better solution is to prevent sequences from ageing out of the library cache by pinning them using dbms_shared_pool.keep(). Pinning the sequence will prevent the sequence values from being aged out of the cache. Pinning the sequence is achieved by invoking the rdbms package dbms_shared_pool.keep() as follows:
SQL> exec dbms_shared_pool.keep('APPS.FA_ADDITIONS_S','Q')
When you shut down a database normally, either "SHUTDOWN NORMAL" or "SHUTDOWN IMMEDIATE", the database takes care of making sure all sequences are "in sequence". All cached sequence values are verified, and the last_number column in dba_sequences is updated accordingly. Thus, under normal circumstances and assuming that your sequence has been pinned, you shouldn't lose cached sequence values during a normal shutdown.
If the database experiences instance failure or a "SHUTDOWN ABORT" statement is issued, you lose any unused cached sequence values. Oracle may also skip sequence values after an export and Import and rollbacks.
To achive this either you have to modify the $ORACLE_HOME/bin/dbstart script and incorporate the pinning or you can create after database startup trigger.
Create a Startup Trigger:
create table AGCST.AG_FA_DBSTRT_PIN (
execution_date date
);
CREATE OR REPLACE TRIGGER DB_STRT_FA_PINING AFTER STARTUP ON DATABASE BEGIN
dbms_shared_pool.keep('APPS.FA_ADDITIONS_S','Q');
INSERT INTO AGCST.AG_FA_DBSTRT_PIN (execution_date) select sysdate from dual;
END;
/
This is the trigger I have used for Pinning the object while doing Automatic Gapless Asset Numbering Implementation. Table is created to know that the trigger has fired after every startup
