without analytics
Igor, April 05, 2018 - 3:13 pm UTC
Apparently it can be done without analytics. Am i missing anything?
SQL> with t as
2 ( select round(DBMS_RANDOM.value(100000,999999)) v
3 from dual
4 connect by level <= 10000
5 )
6 select *
7 from (
8 select v
9 from t
10 group by v
11 having count(*) = 1
12 )
13 where rownum <= 100;
V
----------
636783
242534
652196
445016
813455
353460
119531
etc.
April 06, 2018 - 3:49 am UTC
That should be fine as well, or a 'distinct' in the inline view. Lots of ways to do it.
Replace a sequence with a call to generate unique values
Durga, November 25, 2020 - 3:25 pm UTC
Hello Connor,
We have a sequence RQS_SESSION
CREATE SEQUENCE "PROD"."RQS_SESSION" MINVALUE 1004 MAXVALUE 999999999999999999 INCREMENT BY 10 START WITH 819597576284 CACHE 50000 NOORDER CYCLE NOKEEP GLOBAL ;
We are testing the migration of our one of our databases to Oracle Cloud.
The Oracle Cloud requires that the DB should be in Read-Only mode.
During testing, we caused an exception in Data Guard which identified that we are making a DB change in a call to "NextVal" for a DB sequence at the following line -
Select RQS_Session.NextVal into v_Session_ID from dual;
The current on-prem DB is apparently in Read-Write mode and allows this modification.
But this sequence seems to be a problem for the Cloud DB.
Could you suggest a way so that v_Session_ID could be implemented as a 'unique' value rather than a sequential value (using a DB sequence).
Could we replace the Sequence.NextVal call with a call to generate a unique value.
Please suggest.
Thank you in advance.
November 25, 2020 - 5:44 pm UTC
Why exactly is your database in read-only mode? It'll be in read-write mode once you migrate, right?
Replace a sequence with a call to generate unique values
Durga, November 25, 2020 - 8:27 pm UTC
Hello Chris,
We have an on-prem architecture which has 1 Primary DB where all the WRITES happen and has 10 databases which get data continuously replicated from the primary and are designed for the READS.
These 10 DBs are in READ-WRITE mode in the on-prem architecture (but they are designed for READS)
(we have a legacy system)
We are testing migration of on-prem system to Oracle Cloud.
The Oracle Cloud requires that the DB which have been designed for READS should be in Read-Only mode.
So, the testing fails at following SQL statement -
Select RQS_Session.NextVal into v_Session_ID from dual;
As the current on-prem DB is apparently in Read-Write mode, it allows this modification.
But this sequence is a problem for the Cloud DB.
So, how can we implement v_Session_ID so that it fits the use case where our DB has to be in Read-Only mode post migration to Oracle Cloud DB?
Please suggest.
Thank you for your time.