Duplicate Values in a Sequence

I was called immediately to look into GL transactions failure, the issue was GL transaction were failing due to constraint violation. After closely looking into the error then came to conclusion that primary key posting_id in a table was primary key and this value is generated by sequence and its generating the duplicate values.


How can the sequence generate the duplicate values started looking into it.. hence my finding on this issue are.. when and how duplicate values are generated

1. Values can be duplicate if the CYCLE clause is used at the time of creating sequence, what it means when the high value is reached, it shall start from min-value resulting in duplicate values
2. the problem is that someone either accidentally used a different sequence to generate a few primary keys
3. Somehow have set some primary key manually
4. or copied rows from other environment without changing the primary key or updating the sequence.

Looking at above scenario my case fits in case 1.

Now the action plan to fix

1.extract the definition from dba_sequences
2.Take the invalid object count(why becoz dependents objects in db will be invalid need to compile them later)
3.Create the sequence with nocycle clause
4.compile invalids

I also learned two more things.

1.>>>>The current value of Sequence is not stored in dba_sequences whereas its stored in v$_sequences.nextvalue. DBA_Sequences store the cache values so it doesn't reflect the correct value of sequence.

Query

SQL> select demo.nextval from dual;

NEXTVAL
----------
2

=>Current Value of sequence is 2

Querying value from dba_sequences

SQL> select sequence_owner,sequence_name,last_number
2 from dba_sequences where sequence_name='DEMO';

SEQUENCE_OWNER SEQUENCE_NAME LAST_NUMBER
------------------------------ ------------------------------ -----------
SYS DEMO 21

>SEE the value of last_number.

Exact value can be retrieved

SQL> select sequence_owner,sequence_name,nextvalue
2 from v$_sequences where sequence_name='DEMO';

SEQUENCE_OWNER SEQUENCE_NAME NEXTVALUE
-------------------- -------------------- ----------
SYS DEMO 2



2>>>> Difference in sequence cant be avoided because of instance failure, transaction failure, rollback.

Example lets do instance failure and verify

SQL> select sequence_owner,sequence_name,last_number
2 from dba_sequences where sequence_name='DEMO';

SEQUENCE_OWNER SEQUENCE_NAME LAST_NUMBER
-------------------- -------------------- -----------
SYS DEMO 21

SQL> select demo.nextval from dual;

NEXTVAL
----------
3

SQL> shut abort
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area 564957184 bytes
Fixed Size 1384956 bytes
Variable Size 201330180 bytes
Database Buffers 356515840 bytes
Redo Buffers 5726208 bytes
Database mounted.
Database opened.

SQL> select demo.nextval from dual;

NEXTVAL
----------
21

I read on asktom - > three things cant be avoided

1.Taxes
2.Death
3.Gap in sequences.

Hence beware of Gaps and if you need strictly in order then use some other logic as well.

When is last time DML issues on table

Today I have seen interesting scenerio, after the database migration one user complain of missing records. how can db migration result in missing records logically it cannot be possible. Then started looking for information when was the last dml happened on my table. I got the result and that was even before migration so might be they were not updated.

->In our scenerio auditing was disabled so that is why I used ora_rowscn

Here is how we can query.

Table A resides in schema A.

SQL> select max(ora_rowscn), scn_to_timestamp(max(ora_rowscn)) from a.a;

SQL> insert into a.a values(3);

1 row created.

SQL> select max(ora_rowscn), scn_to_timestamp(max(ora_rowscn)) Modified from a.a
;

MAX(ORA_ROWSCN) MODIFIED
--------------- ------------------------------
709093 20-OCT-11 14.13.01.000000000

SQL> commit;

Commit complete.

SQL> select max(ora_rowscn), scn_to_timestamp(max(ora_rowscn)) Modified from a.a
;

MAX(ORA_ROWSCN) MODIFIED
--------------- ------------------------------
709229 20-OCT-11 14.19.17.000000000

Here you can see the timestamp of last DML.

Missing grant

Issue:

Development instance is missing some grant which production has, thats was the challenge is. We identified the owner and the role from production, challenge is to create similar role for dev

SQL> SPOOL role.sql
SQL> select 'grant '||privilege ||' on '||owner|| '.' ||table_name||' to '||grantee ||';'
FROM dba_tab_privs
where GRANTEE='&ROLE_NAME';
SQL> @role

Ah ha.. Got the privs.. Happy development.

APPS USER Actual EBS user

select
fl.login_id,
fu.user_name,
to_char(fl.start_time,'DD-MON-YYYY HH:MI'),
fl.end_time,
fl.pid,
fl.spid,
fl.process_spid,
vs.sid,
vs.module,
vs.program
from
apps.fnd_logins fl,
apps.fnd_user fu,
v$process vp,
v$session vs
where
fl.user_id=fu.user_id
and fl.pid = vp.pid
and vp.addr = vs.paddr
and fl.end_time is null

Shell Script: SQL:How to Assign value to shell variable

PID=`sqlplus -s $1@$INST <<
whenever sqlerror exit 1
set escape off
set head off
set verify off
select oracle_process_id from fnd_concurrent_requests where request_id=$5;
`

Export Import Scenerio

Requirement
Export from schema userA with all objects and Import to Schema userBIt should replace the table t itself
expdp system/pass schemas=usera directory=DATA_PUMP_DIR dumpfile=myfile.dmp logfile=myfile.logimpdp system/pass REMAP_schema=usera:userb directory=DATA_PUMP_DIR dumpfile=myfile.dmp logfile=myfile.log TABLE_EXISTS_ACTION=REPLACE

html

Lock information Here is lock output

Lock Script

To: address@example.com
Subject: Subject
Content-Type: text/html; charset="us-ascii"
/* You can of course add more header lines if necessary. */


... your carefully crafted html content ...


#!/bin/ksh
#set -x
.
DATESTR=`date +"%Y-%m-%d_%H%M"`
DBALIST="Email id"
lckfile="/vac/home/scripts/log/$ORACLE_SID/"$DATESTR".log"
header="To: $DBALIST \nFrom: PATCH_INSTANCE@company.com \nSubject: Lock Report \nMIME-Version: 1.0 \nContent-Type: text/html; charset=us-ascii\n \n"
sqlplus -s -m "HTML ON" '/as sysdba' @SCRIPT_LOCATION/lock.sql >$lckfile"1"
if [ `fgrep 'No one requesting locks held by others' $lckfile"1" |wc -l` -ne 0 ]
then
echo "do nothing"
else

echo $header > $lckfile
echo " DBA Team," >> $lckfile
echo "
" >> $lckfile
echo " Please advise on the following locks" >> $lckfile
cat $lckfile"1" | grep -v 'Connected' >> $lckfile
echo "
" >> $lckfile
echo "
" >> $lckfile
echo "

---

" >> $lckfile
echo "opatch@IP_ADDRESS:/lock.sh Ver 1.0" >> $lckfile
cat $lckfile|/usr/lib/sendmail -t
fi