0×1A, better know as the end of file character.
Now also known as the cause of me waisting several hours on analyzing a 500MB raw trace file trying to figure out why the tkprof report did not seemed to be correct.
Still wondering why the application I was tracing had an EOF character in the value of a varchar2 type bind variable.
Ok, not as good as beer and they can give you a nasty headache, so you have been warned ;)
Reason for this post are 2 bugs I discovered with Oracle RAC, both resulting in a single point of failure.
The platform on which I’m working is Oracle 10gR2 (10.2.0.4) on OEL 4.7.
The first one is when you are using NFS volumes to host the ocr and ocrmirror volumes.
Normally, when the ocr volume gets corrupted or unavailable , oracle should failover to the ocrmirror volume. The exact response is documented in the RAC FAQ on metalink (note 220970.1) and is currently discussed in a series of blog posts by Geert de Paep.
With NFS, however, you must use both the nointr and hard mount options (OS is OEL 4.7) and as a result the process that is trying to read or write an unavailable ocr volume will wait undefinitly on a response. This is not only happening when using commands such as crs_stat or srvctl, but also when an instance or service failover is initiated.
Oracle support however, does not exactly see it this way and has first blamed the os, then the storage and finally stated that there is no failover foreseen between the ocr and ocrmirror volumes…
It took some escalating and a change in support engineer to get some progress in that SR (mind you that after more then 4 months, they still have not acknowledged it as a bug).
The second problem is that, when you made the public interface redundant with os bonding, the racgvip script does not detect when all interfaces in the bond are disconnected.
This is caused because the script, unlike older version, is using mii-tool to check the availability of the public interface. Only when mii-tool states that the link is down, a ping test is done to the public gateway. If that test fails as well, then the vip fails over and the rac instances on that node are placed in a blocked state.
The problem however with mii-tool is that it plays not very well with bonds, and always reports the bond status as being up (in fact, regardless of the link state, mii-tool is always reporting a network bond as “bond0: 10 Mbit, half duplex, link ok”). So, the racgvip script always thinks that the public interface is up.
As mii-tool is an os utility, I first opened a case on the Oracle Enterprise Linux support, to check with them if its behavior was normal (I already confirmed that by googeling, but Oracle support does not seem to accept results from google :) ). And after running multiple tests with different bond options, they finally stated that mii-tool was indeed obsolete and should not be used to verify a bond status (yes, I know. Its own man page already states that mii-tool is obsolete).
So next, I opened a SR on part of the clusterware and oracle development promptly stated that it was not a clusterware bug but an os issue, pointing the finger to mii-tool and asking where it was written that mii-tool is obsolete… . After making them aware of the statement made by their OEL colleagues and the mii-tool man page, they have seemed to have accepted it as a bug.
I have checked the 11gR2 version of the racgvip script, and it seems to suffer the same problem.
ps) Note 365605.1 – “Oracle Bug Status Codes, Descriptions and Usage” is, although it seems incomplete, very usefull to understand the different status codes
During my prior post on the effect of daylight saving settings on the Oracle scheduler, I already pointed out that it is best to set your session timezone information to a named timezone and not to an absolute offset. In this post I would like to investigate how the session timezone settings affect the sysdate, current_date, systimestamp and current_timestamp variables during the switchover to or from daylight saving time. Current_date and current_timestamp, are using the date/time information of the server on which the database runs and modify that time using the timezone settings of the session.
As with the last post, the tests where done in response to the switching from wintertime to summertime, and I’m to lazy to redo them.
In the first test, I do not explicitly set timezone information in my session.
Both the server time and the client time has been set to a couple of minutes before the swithover from wintertime to summertime:
sys@GUNNAR> alter session set nls_date_format = 'DD/MM/YYYY HH24:MI:SS'; Session altered. sys@GUNNAR> alter session set nls_timestamp_tz_format='DD/MM/YYYY HH24:MI:SS "TZ:" TZR "DS:" TZD '; Session altered. sys@GUNNAR> column systimestamp format a35 sys@GUNNAR> column current_timestamp format a35 sys@GUNNAR> select sysdate, current_date, systimestamp, current_timestamp from dual; SYSDATE CURRENT_DATE SYSTIMESTAMP CURRENT_TIMESTAMP ------------------- ------------------- ----------------------------------- ----------------------------------- 29/03/2009 01:58:32 29/03/2009 01:58:32 29/03/2009 01:58:32 TZ: +01:00 DS: 29/03/2009 01:58:32 TZ: +01:00 DS:
As you can see the timezone information uses the absolute offset notation and is set to GMT +1 (which corresponds with wintertime in Belgium).
After some minutes (when the summertime came in effect), I execute the same query again:
sys@GUNNAR> select sysdate, current_date, systimestamp, current_timestamp from dual; SYSDATE CURRENT_DATE SYSTIMESTAMP CURRENT_TIMESTAMP ------------------- ------------------- ----------------------------------- ----------------------------------- 29/03/2009 03:00:18 29/03/2009 02:00:18 29/03/2009 03:00:18 TZ: +02:00 DS: 29/03/2009 02:00:18 TZ: +01:00 DS:
Both sysdate and systimestamp has jumped 1 hour in the feature and systimestamp now shows the timezone as “GMT + 2″ (summertime in Belgium).
Current_date and current_timestamp both show the time without summertime corrections, but with current_timestamp the timezone information places the time in the right context.
Next, I disconnect and reconnect the session:
sys@GUNNAR> select sysdate, current_date, systimestamp, current_timestamp from dual; SYSDATE CURRENT_DATE SYSTIMESTAMP CURRENT_TIMESTAMP ------------------- ------------------- ----------------------------------- ----------------------------------- 29/03/2009 03:01:15 29/03/2009 03:01:15 29/03/2009 03:01:15 TZ: +02:00 DS: 29/03/2009 03:01:15 TZ: +02:00 DS:
This time, all 4 show the same time and timezone information (all using summertime).
The explanation for this is that the timezone information for a session is determined when the session is created, and Oracle only applies daylight saving settings when using a named timezone. So as long as the session is connected, it uses the “old” timezone of GMT +1. With sysdate and systimestamp the timezone information comes from the server, not from the client.
In the second test, I have set the ORA_SDTZ variable in the client environment to “Europe/Brussels”
sys@GUNNAR> select sysdate, current_date, systimestamp, current_timestamp from dual; SYSDATE CURRENT_DATE SYSTIMESTAMP CURRENT_TIMESTAMP ------------------- ------------------- ----------------------------------- ----------------------------------------------- 29/03/2009 01:57:37 29/03/2009 01:57:38 29/03/2009 01:57:37 TZ: +01:00 DS: 29/03/2009 01:57:37 TZ: EUROPE/BRUSSELS DS: CET ### a couple of minutes later sys@GUNNAR> select sysdate, current_date, systimestamp, current_timestamp from dual; SYSDATE CURRENT_DATE SYSTIMESTAMP CURRENT_TIMESTAMP ------------------- ------------------- ----------------------------------- ------------------------------------------------ 29/03/2009 03:00:04 29/03/2009 03:00:04 29/03/2009 03:00:04 TZ: +02:00 DS: 29/03/2009 03:00:04 TZ: EUROPE/BRUSSELS DS: CEST
Both current_date and current_timestamp have now also jumped 1 hour in the “future” and the daylight saving settings in current_timestamp has changed from CET (Central European Time) to CEST (Central European Summer Time).
To me this shows that it is important to set the timezone of you clients correctly, even if the database is not used from different timezones.
A long running session is sufficient to pollute your data, certainly if you are using current_date as it has no timezone information.
In Belgium we are switching to wintertime this Sunday, which is good opportunity for me to write this post.
I normally intended to write it when we switched to summer time, so everything will be from the point of view of changing from winter time to summer time (confused yet? ).
The reason that I wanted to write about it, where some alerts we got back then from our monitoring considering scheduler jobs which where no longer running on time.
Quickly it became clear that these jobs did not follow the change to summer time, but instead ran an hour later.
The key is to look at the dba_scheduler_jobs table in the correct format. You see, the *_run_date columns are of the datatype “timestamp(6) with timezone”, so to get all the information you need to use the right format model. Using the TZR and TZD models you can respectively see the timezone and the daylight saving information:
sys@WPS50> select job_name, to_char(last_start_date, 'DD/MM/YYYY HH24:MI:SS "TZ:" TZR "DS:" TZD ') last_start_date, to_char(next_run_date, 'DD/MM/YYYY HH24:MI:SS "TS:" TZR "DS:" TZD ') next_run_date from dba_scheduler_jobs; JOB_NAME LAST_START_DATE NEXT_RUN_DATE ------------------------------ -------------------------------------------------- -------------------------------------------------- AUTO_SPACE_ADVISOR_JOB 28/03/2009 06:00:04 TZ: +01:00 DS: GATHER_STATS_JOB 02/02/2009 22:00:00 TZ: +01:00 DS: FGR$AUTOPURGE_JOB PURGE_LOG 29/03/2009 03:00:00 TZ: MET DS: MEST 30/03/2009 03:00:00 TS: MET DS: MEST ANALYZETHIS_PURGEHISTORY 29/03/2009 17:00:00 TZ: +01:00 DS: 30/03/2009 17:00:00 TS: +01:00 DS: GATHER_WK_TEST_STATS 29/03/2009 18:00:00 TZ: +01:00 DS: 30/03/2009 18:00:00 TS: +01:00 DS: GATHER_SESSIONUSR_STATS 29/03/2009 18:00:00 TZ: +01:00 DS: 30/03/2009 18:00:00 TS: +01:00 DS: GATHER_RELEASEUSR_STATS 29/03/2009 18:00:00 TZ: +01:00 DS: 30/03/2009 18:00:00 TS: +01:00 DS: GATHER_LMDBUSR_STATS 29/03/2009 18:00:00 TZ: +01:00 DS: 30/03/2009 18:00:00 TS: +01:00 DS: GATHER_ICMADMIN_STATS 29/03/2009 18:00:00 TZ: +01:00 DS: 30/03/2009 18:00:00 TS: +01:00 DS: GATHER_COMMUNITYUSR_STATS 29/03/2009 18:00:00 TZ: +01:00 DS: 30/03/2009 18:00:00 TS: +01:00 DS: GATHER_CUSTOMIZATIONUSR_STATS 29/03/2009 18:00:00 TZ: +01:00 DS: 30/03/2009 18:00:00 TS: +01:00 DS: MGMT_STATS_CONFIG_JOB 01/03/2009 01:01:01 TZ: +01:00 DS: 01/04/2009 01:01:01 TS: +01:00 DS: MGMT_CONFIG_JOB 28/03/2009 06:00:04 TZ: +01:00 DS: 14 rows selected. (note the additional space after the TZD format, I needed to add this to actually show the information if I used the "DS:" litteral in front (probably this is a bug) )
As you can see, each job has its own timezone offset and some have also daylight saving information.
So, what happened with our jobs? Well, when a job gets created, Oracle stores the timezone information of the start_date parameters. If this timezone is specified in an absolute offset then no daylight saving changes are applied.
When the server switches to summer time (GMT +2 in Belgium), the scheduler job stays in its own little world and remains in the timezone GMT +1.
So, when for the rest of the database the time is 07:00, the job thinks it is still 06:00 and does not start. As the monitoring check did not take the timezone of the job in account, it reported the job as being late.
To avoid this situation, you need to use a named timezone, in which case oracle will apply automatically the correct daylight saving settings.
How do you do this? Well either you use the to_timestamp_tz to convert a text string to a timestamp with timezone information or Oracle retrieves the timezone from your session.
The timezone information in your session can be set with alter session, or by using the ORA_SDTZ variable in your client environment.
But there is a catch. In the following example I have set my timezone to Europe/Brussels, and then verified the timezone information in systimestamp:
sys@WPS50> select sessiontimezone from dual; SESSIONTIMEZONE --------------------------------------------------------------------------- Europe/Brussels sys@WPS50> select to_char(systimestamp, 'DD/MM/YYYY HH24:MI:SS "TZ:" TZR "DS:" TZD ') from dual; TO_CHAR(SYSTIMESTAMP,'DD/MM/YYYYHH24:MI:SS"TZ:"TZR"DS:"TZD') -------------------------------------------------------------------- 30/03/2009 01:57:15 TZ: +02:00 DS:
As you can see, the timezone part an absolute notation, not a named timezone.
Systimestamp will never use the named timezone notation, so whenever you use systimestamp as value for the next_date parameter in dbms_scheduler, you will use an absolute offset and thus not follow daylight saving switches.
The current_timestamp variable, will however use the correct notation:
sys@WPS50> select to_char(current_timestamp, 'DD/MM/YYYY HH24:MI:SS "TZ:" TZR "DS:" TZD ') from dual; TO_CHAR(CURRENT_TIMESTAMP,'DD/MM/YYYYHH24:MI:SS"TZ:"TZR"DS:"TZD') -------------------------------------------------------------------- 30/03/2009 01:57:18 TZ: EUROPE/BRUSSELS DS: CEST
So, when you want to specify the current date as value for the next_date parameter, use current_timestamp and not systimestamp.
This timezone stuff is only applicable when you have an interval that is at least 1 day. With smaller intervals, Oracle will make sure that the period between 2 runs remain the same.
If a job runs every 3 hours and last ran on midnight and the clock is then moved forward from 02:00 to 03:00, then the next run date of the job becomes 04:00, so that the 3 hour period between two job runs is retained.
More information on this, including how Oracle behaves when no start_date parameter is given can be found here:
The database version on which the tests where done is 10.2.0.4
A quick warning:
When you setup a logical standby database, you need to activate supplemental logging on the primary database.
This is done automatically when you build the data dictionary (by running the dbms_logstdby.build procedure).
Activating supplemental logging is however (I know now) a control file change and is thus not replicated to the other physical standby databases.
As a result, the logical standby will become (logical) corrupt when you perform a role switch between your primary and another physical standby database.
I learned this the hard way :(
Luckily it was during a proof of concept and not in a real production environment … .
Of course, AFTERWARDS, I found the following maa document which points out that you have to enable supplemental logging yourself on the other physical standby databases.
It still makes a good read though
Last week I was asked to write a little script to automate an upgrade of oracle client 9.2.0.1 to 9.2.0.8.
Reason for this was that we needed to update arround 1.300 clients to enable them to connect to a 10g database (we couldn’t install 10g clients because of other applications restricted the client version to 9i).
Ok, easy enough. Oracle allows you to automate installations and upgrades via response files and the response file for a client upgrade from 9.2.0.1 to 9.2.0.8 is very simple.
When I started testing the upgrade, I immediately spotted a first problem. The setup.exe (it was on windows xp) started a new console and then returned directly to the prompt in the original console. This would make it impossible to check the return codes to know if a upgrade was successful or not.
The upgrade itself finished without a problem, but at the end the following message appeared in the newly started console: “Press enter to exit”.
Huh!? This was supposed to be a “silent” install, meaning no interraction needed. But here it was, asking to press enter to exit.
And the documentation was not telling anything about it.
After some searching, I found that you can specify the “-noconsole” flag when starting the setup, which would surpress the new console and avoid the question to press enter.
You still would see the question in the logfiles, but the installation presumed you responded to it and finishes the upgrade.
This left me with the first problem: the prompt would still directly return while the upgrade was running in the background.
After some searching in the documentation I found a note stating that you need to modify the oraparam.ini file and change the BOOTSTRAP parameter from TRUE to FALSE.
Unfortunately this did not help. Yelling at it did either.
Then I found that in 10g, you had a “-waitforcompletion” flag you could set, that would do exactly what I needed. So I tried if it would work for the oui shipped in the 9.2.0.8 patchset.
At first, it didn’t, but then I found metalink note 293044.1 that said that the setup.exe in Disk1 and Disk1/install where not the same and that the one in Disk1/install should be used for the “-waitforcompletion” flag.
At last it worked.
For those interested, here is the full command I used to start the silent upgrade:
start /wait C:\oracle\patches\9.2.0.8\Disk1\install\setup.exe -silent -noconsole -waitforcompletion -responsefile c:\oracle\patches\9.2.0.8\patchset.rsp -paramfile c:\oracle\patches\9.2.0.8\oraparam.ini
—————————-
Thanks to Geert for the yelling link :)
Lets say you need to upgrade your 9i database to 10g (10.2.0.4 to be exact), but you actually want to sabotage the upgrade (don’t know why, just assume you do).
Granted, there are many ways to do this, but you want to do it subtle. What are your options then?
Well, one option is to create the plan_table table in your sys schema (or a synonym plan_table to a plan table in another schema if you want to make it really subtle) before the upgrade.
If you do this, you will see the following message in your upgrade log:
Warning: Package Body created with compilation errors. SQL> show errors; Errors for PACKAGE BODY DBMS_SQLPA: LINE/COL ERROR -------- ----------------------------------------------------------------- 113/5 PL/SQL: SQL Statement ignored 118/44 PL/SQL: ORA-00904: "OTHER_XML": invalid identifier
And the “oracle database server” component in the dba_registry will be marked as invalid.
Mission accomplished I would say.
What is that?
You regret your actions and you want to fix the problem?
*sigh*
Ok then, to fix it you can use the following steps:
For those seeking more information:
Metalink note 565600.1 – ERROR IN CATUPGRD: ORA-00904 IN DBMS_SQLPA
Metalink note 605317.1 – DBMS_SQLPA ORA-00904 OTHER_XML invalid identifier
According to the notes, the problem only exists with upgrades to 10.2.0.4 or to 11.1.0.7.
ps. Don’t ask me why I had a synonym called plan_table in my sys schema. I didn’t do it.
pps. This is why you should test your migration (I’m glad I did)
Long time since I blogged, but I have been busy with tinkering with Ubuntu, learning about Oracle streams, client projects and playing with my now 10 month old son (not necessarily in this order).
Still, to let you know that I am still alive: a short overview of things I learned that you might find useful:
If you still have the possibility to follow it, then do so. I really learned a lot during this course.
The word “advanced” in the course title is not soley there for marketing purposes
More information about the course contents can be found here
Until 10.2.0.4 there is a bug in datampump with lobs, causing them to be slow on importing. This is due to bug 5555463, which is fixed in 10.2.0.4 (there is also an one-off patch available). Another work-arround is to enforce the access_method used by datapump, but this is unsupported.
Some articles about this that I found usefull:
Note:5555463.8 – Bug 5555463 – Slow performance of datapump IMPORT with small LOBs
Note:552424.1 – Export/Import DataPump Parameter ACCESS_METHOD – How to Enforce a Method of Loading and Unloading Data ?
Note:286496.1 – Export/Import DataPump Parameter TRACE – How to Diagnose Oracle Data Pump
This release has some important new features, such as time based retention policies.
Don’t have the time to play with it myself, but one of my colleagues has started to test it, and is blogging about it: http://bjornnaessens.wordpress.com
http://geekozoid.blogspot.com/2008/06/auto-complete-hostnames-with-using-bash.html
I created a vmware image from my laptop to run it under Ubuntu, unfortunately my laptop was installed with an OEM edition of XP, so it kept comlaining that I needed to reactivate the license again.
Luckally there is a vmware parameter you can use to avoid this: http://communities.vmware.com/thread/99057
(Thanks to Filiep Declerck for the link)
With 10.2.0.4, opatch does not seems to update the $ORACLE_HOME/inventory/oneoffs/ and the $ORACLE_HOME/.patch_storage/ directories on the remote nodes when applying one-off patches. We have noticed this issue on several servers with Solaris 10 and oracle 10.2.0.4 (and opatch 10.2.0.4.3 and 10.2.0.4.5). Other combination might have the same problem.
The patch itself seems to be applied on the remote nodes, but because the directories are not copied a query (such as lsinventory) on the remote inventory will fail on the remote nodes (preventing installing other patches).
This issue is now tracked in bug 7611827. The workarround is to use the “-local” flag and install the patch node per node.
That’s all for now.
Here’s something I stumbled upon when I was researching a performance problem involving the oracle merge command.
fdh@GUNNAR> create table t_dest
2 ( n1 number(10,0),
3 n2 number(10,0),
4 v1 varchar2(1)
5 )
6 /
Table created.
fdh@GUNNAR> create table t_source
2 ( n1 number(10,0)
3 )
4 /
Table created.
fdh@GUNNAR> create sequence s_seq
2 start with 1
3 increment by 1
4 nomaxvalue
5 nocache
6 nocycle
7 noorder
8 /
Sequence created.
fdh@GUNNAR> insert into t_dest
2 select rownum, s_seq.nextval, 'O'
3 from all_objects
4 where rownum <= 1000;
1000 rows created.
fdh@GUNNAR> insert into t_source
2 select rownum
3 from all_objects
4 where rownum <= 500;
500 rows created.
fdh@GUNNAR> commit;
Commit complete.
fdh@GUNNAR> select last_number from user_sequences where sequence_name = 'S_SEQ';
LAST_NUMBER
-----------
1001
fdh@GUNNAR> set timing on
fdh@GUNNAR> exec dbms_monitor.session_trace_enable(waits => TRUE, binds => TRUE);
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.07
fdh@GUNNAR> merge into t_dest
2 using t_source
3 on ( t_dest.n1 = t_source.n1)
4 when matched then update
5 set v1 = 'U'
6 when not matched then insert (n1, n2, v1)
7 values (0, s_seq.nextval, 'N');
500 rows merged.
Elapsed: 00:00:03.68
fdh@GUNNAR> commit;
Commit complete.
Elapsed: 00:00:00.06
fdh@GUNNAR> exec dbms_monitor.session_trace_disable;
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.06
fdh@GUNNAR> select v1, count(*)
2 from t_dest
3 group by v1
4 order by v1;
V COUNT(*)
- ----------
O 500
U 500
Elapsed: 00:00:00.09
As you can see, the merge statement has updated 500 records and inserted no new records.
But check out the sequence:
fdh@GUNNAR> select last_number from user_sequences where sequence_name = 'S_SEQ';
LAST_NUMBER
-----------
1501
Elapsed: 00:00:00.07
Apparently the sequence has increased with 500 (number of records from t_source), even with the “when not matched” clause never evaluating to true.
The trace output confirms this as well.
merge into t_dest
using t_source
on ( t_dest.n1 = t_source.n1)
when matched then update
set v1 = 'U'
when not matched then insert (n1, n2, v1)
values (0, s_seq.nextval, 'N')
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.01 0 6 0 0
Execute 1 0.59 0.56 0 9 1014 500
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.60 0.58 0 15 1014 500
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 43
Rows Row Source Operation
------- ---------------------------------------------------
2 MERGE T_DEST (cr=509 pr=0 pw=0 time=3478125 us)
500 VIEW (cr=509 pr=0 pw=0 time=3510161 us)
500 SEQUENCE S_SEQ (cr=509 pr=0 pw=0 time=3508659 us)
500 HASH JOIN OUTER (cr=9 pr=0 pw=0 time=6366 us)
500 TABLE ACCESS FULL T_SOURCE (cr=3 pr=0 pw=0 time=1042 us)
1000 TABLE ACCESS FULL T_DEST (cr=6 pr=0 pw=0 time=1049 us)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
log file sync 2 0.01 0.01
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 0.01 0.01
********************************************************************************
update seq$ set increment$=:2,minvalue=:3,maxvalue=:4,cycle#=:5,order$=:6,
cache=:7,highwater=:8,audit$=:9,flags=:10
where
obj#=:1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 500 0.00 0.00 0 0 0 0
Execute 500 2.96 2.90 0 500 1013 500
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1000 2.96 2.91 0 500 1013 500
Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 UPDATE SEQ$ (cr=1 pr=0 pw=0 time=167 us)
1 INDEX UNIQUE SCAN I_SEQ1 (cr=1 pr=0 pw=0 time=16 us)(object id 102)
********************************************************************************
Clearly something to remember when you are using a merge command with a sequence in the “when not matched” clause and a source table containing a couple of million rows. Certainly when you expect most records to be matched.
In such a case it could be better to move the sequence to a row trigger on the target table.
Today, 23/09 is Earth Overshoot Day. That is the day we have used up more natural resources then the Earth can regenerate in one year.
Currently we are using 1.4 planets to support our lifestyle, resulting in a decrease of our supply of natural resources.
Maybe it’s not a bad idea to make a simular calculation for the resource usage on our databases and present the results to developers :)
