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

TOP undo usage

Top 10 UNDO usage SQL
select SQL_TEXT from DBA_HIST_SQLTEXT where SQL_ID in
(select distinct MAXQUERYSQLID from
(select * from
(select SNAP_ID,
MAXQUERYLEN,
UNDOBLKS,
MAXQUERYSQLID,
to_char(BEGIN_TIME,’yyyy/mm/dd hh24:mi’) begin,
to_char(END_TIME,’yyyy/mm/dd hh24:mi’) end
from DBA_HIST_UNDOSTAT
order by UNDOBLKS desc,
MAXQUERYLEN desc
) where rownum<11
)
);

how to get ddl

set heading off;

set echo off;

Set pages 999;

set long 10000;

spool ddl_list.sql

select dbms_metadata.get_ddl('TABLE','TABLENAME','USERNAME') from dual;

select dbms_metadata.get_ddl('INDEX','INDEXNAME','USERNAME') from dual;

spool off;

grant select on all objects in a schema to another user

Spool priv.sql
select 'grant select on &&USER'||'.'||object_name|| ' to &ROLE;' from dba_objects where owner='&&USER';
@priv.sql

Output of CLOB

set long 32767;
select max(dbms_lob.getlength(e)) from d;

select dbms_lob.substr(MYCLOBCOL,4000,1) from TABLE

dbms_lob.substr(col_2,4000,1) col_2_1
,dbms_lob.substr(col_2,4000,4001) col_2_2
,dbms_lob.substr(col_2,4000,8001) col_2_3
from clob_table
/



select max(dbms_lob.getlength(e)) from d;

who is using my temp tablespace

SELECT
a.tablespace_name,
a.file_name,
a.bytes allocated_bytes,
b.free_bytes
FROM
dba_data_files a,
(SELECT file_id, SUM(bytes) free_bytes
FROM dba_free_space b GROUP BY file_id) b
WHERE
a.file_id=b.file_id
ORDER BY
a.tablespace_name;

http://www.dbspecialists.com/files/presentations/temp_space.html

Oracle 11g has a new view called DBA_TEMP_FREE_SPACE that displays information about
temporary tablespace usage.


SQL> SELECT * FROM dba_temp_free_space;

TABLESPACE_NAME                TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE
-------------------------- ---------------             --------------                 ----------
TEMP
                                                        134217728 
                   134217728                    133169152

With this information, you can perform an online shrink of a temporary tablespace
using the ALTER TABLESPACE command.

SQL> alter tablespace temp shrink space keep 100m;

Tablespace altered.

SQL> SELECT * FROM dba_temp_free_space;

TABLESPACE_NAME                TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE
--------------------------    ---------------                  ---------------           ------------
TEMP                           
                              105906176                           
1048576              104857600

The shrink can also be directed to a specific tempfile using the TEMPFILE clause.

SQL> ALTER TABLESPACE temp SHRINK TEMPFILE '/grdbms/32bit/app/oracle/oradata/S111W3/temp01.dbf' KEEP 30M;

Tablespace altered.

SQL> SELECT * FROM dba_temp_free_space;

TABLESPACE_NAME                TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE
------------------------        ---------------              ---------------            ------------
TEMP                               
                               31522816                           
1040384                    31457280

1 row selected.

The
KEEP clause specifies the minimum size of the tablespace or tempfile. If
this is omitted, the database will shrink the tablespace or tempfile to
the smallest possible size.

SQL> ALTER TABLESPACE temp SHRINK SPACE;

Tablespace altered.

SQL> SELECT * FROM dba_temp_free_space;

TABLESPACE_NAME                TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE
-------------------------        ---------------           ---------------            -------------
TEMP                                  2088960                        1040384                     1048576
set pages 999 lines 100
col username format a15
col mb format 999,999
select  su.username
,       ses.sid
,       ses.serial#
,       su.tablespace
,       ceil((su.blocks * dt.block_size) / 1048576) MB
from    v$sort_usage    su
,       dba_tablespaces dt
,       v$session ses
where   su.tablespace = dt.tablespace_name
and     su.session_addr = ses.saddr

Interesting Requirement

I have been asked to give user A account lock and unlock so that it can lock and unlock only user B but if I happen to give it to user A then it can be used against any user.

Researched and came to following conclusion which looks promising


SQL> create or replace procedure MYLOCK as
2 begin
3 execute immediate 'alter user A account lock';
4 end;
5 /

Procedure created.

SQL> create or replace procedure MYUNLOCK as
2 begin
3 execute immediate 'alter user A account unlock';
4 end;
5 /

Procedure created.

SQL> grant execute on mylock to B;

Grant succeeded.

SQL> grant execute on myunlock to B;

Grant succeeded.

SQL> conn b/b
Connected.

SQL> conn a/a
Connected.

SQL> conn b/b
Connected.

SQL> exec sys.mylock;

PL/SQL procedure successfully completed.

SQL> conn a/a
ERROR:
ORA-28000: the account is locked


Warning: You are no longer connected to ORACLE.
SQL> conn b/b
Connected.
SQL> exec sys.myunlock;

PL/SQL procedure successfully completed.

SQL> conn a/a
Connected.

Dadosa

First blog