Posts

A Norwegian version of this post can be found here . Add SRID 5973 to Oracle Spatial Oracle Spatial comes with over 6000 coordinate systems, but SRID 5973 is currently missing. However this is easy to add with a few insert-statements. Run this as user SYSTEM: insert into sdo_datums ( DATUM_ID, DATUM_NAME, DATUM_TYPE, ELLIPSOID_ID, PRIME_MERIDIAN_ID, INFORMATION_SOURCE, DATA_SOURCE, SHIFT_X, SHIFT_Y, SHIFT_Z, ROTATE_X, ROTATE_Y, ROTATE_Z, SCALE_ADJUST, IS_LEGACY, LEGACY_CODE) values ( 1096, 'Norway Normal Null 2000', 'VERTICAL', null, null, 'Norwegian Mapping Authority', 'OGP', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'FALSE', NULL); INSERT INTO sdo_coord_ref_system ( srid, coord_ref_sys_name, coord_ref_sys_kind, coord_sys_id, datum_id, geog_crs_datum_id, source_geog_srid, projection_conv_id, cmpd_horiz_srid, cmpd_vert_srid, information_source, data_source, is_legacy, legacy_code, legacy_wktext, legacy_cs_bounds, is_valid, supports_sdo_geometry ) VALUES ( '5941', 'NN2000 height', 'VERTICAL', '6499', '5206', '5206', NULL, NULL, NULL, NULL, 'Norwegian Mapping Authority.', 'EPSG', 'FALSE', NULL, NULL, NULL, 'TRUE', 'TRUE' ); INSERT INTO sdo_coord_ref_system ( srid, coord_ref_sys_name, coord_ref_sys_kind, coord_sys_id, datum_id, geog_crs_datum_id, source_geog_srid, projection_conv_id, cmpd_horiz_srid, cmpd_vert_srid, information_source, data_source, is_legacy, legacy_code, legacy_wktext, legacy_cs_bounds, is_valid, supports_sdo_geometry ) VALUES ( '5973', 'ETRS89 / UTM zone 33N + NN2000 height', 'COMPOUND', NULL, NULL, NULL, NULL, NULL, '25833', '5941', 'Norwegian Mapping Authority', 'EPSG', 'FALSE', NULL, NULL, NULL, 'TRUE', 'TRUE' ); commit;

REST: Don’t Let Them Wait I have been working with Oracle REST Data Services (ORDS) lately and have observed others struggling with third-party REST APIs from the sideline. Many things need to be done right, but speed is critical. Some systems are susceptible to latency, especially if the call to your API is just one piece of a more comprehensive workflow. Spare yourself from the hassle when people call and complain about timeouts. ...

ORA-01821 Why does this SQL fail? SELECT TO_DATE ('10-Sep-02 14:10:10.123000', 'DD-Mon-RR HH24:MI:SS.FF') FROM DUAL The example is from the 19c SQL Language Reference, with a slight change. The format is valid, but the thing is that the DATE data type does not support fractions of seconds. Therefore the last part of the format string is unknown to the TO_DATE-function. One day I lost a few minutes because I stared at the format string too long before I noticed that I was using TO_DATE instead of TO_TIMESTAMP. ...

Although you think you have verified that everything works after a switchover, you may have a hidden problem with the applications. You need to know how the applications will react if one of the network interfaces stops responding. Dataguard: Switchover vs Failover I have been using Data Guard for many years but have never had to do an actual failover in production; it has always been a switchover for planned maintenance. The other day, we did a controlled power-off of the standby side some days after a switchover. We discovered that many applications started to hang after this power-off, although they worked fine when both the standby and primary sides were online. ...

Be careful with identity columns. Primary Key as Generated Identity From Oracle 12c you can have Oracle generate the primary key for you. As an example: create table customer ( id number generated always as identity, name varchar2(50), constraint customer_pk primary key (id)); Later you can remove this identity with an alter table command, but you can’t add it back! SQL> alter table customer modify (id drop identity); Table CUSTOMER altered. SQL> alter table customer modify (id number generated always as identity); Error starting at line : 1 in command - alter table customer modify (id number generated always as identity) Error report - ORA-30673: column to be modified is not an identity column 30673.0000 - "column to be modified is not an identity column" *Cause: An attempt was made to modify the identity properties of that is not an identity column. *Action: Modify the identity properties of an identity column. You can check the documentation for 21c , there is no other syntax to solve this issue. ...

Is your SYSAUX tablespace way too large? If you think your SYSAUX tablespace is too big, it may be that AWR is leaving behind stuff that it should have deleted. Doc ID 2099998.1 on Oracle Support describes the problem. The database deletes snapshots that are older than the configured retention. But sometimes, if the process gets interrupted, it may leave some orphan data behind. Ensure you are correctly licensed before you start querying the AWR tables; it requires the diagnostic management pack (AFAIK, but I have not worked for years on an enterprise database without all the management packs licensed). You can use the script $ORACLE_HOME/rdbms/admin/awrinfo.sql to check how much space the various components of AWR occupy. ...

The combination of a reverse proxy, ORDS in standalone and APEX Social Sign-In gave me a headache. ORDS, APEX, and Social Sign-In If you have the following setup you may need an extra parameter in the global ORDS configuration: ORDS running in standalone with HTTP (non-TLS) on port 8080 ORDS behind a reverse proxy with TLS terminated in reverse proxy APEX served by ORDS You want to use authentication scheme Social Sign-In and OpenID Connect (OIDC) or OAuth 2.0 In this case you need to add one line to the file global/settings.xml in the ORDS configuration directory: ...

This simple tip may help you troubleshooting integrations in APEX and ORDS. Show CGI Environment in APEX I have been struggling with integration issues in APEX lately. Listing out the CGI environment helped to identify the probable cause. Just create a region of type Dynamic Content and add this code: begin OWA_UTIL.PRINT_CGI_ENV; end; It will list values for HTTP_SERVER, HTTP_PORT, etc. If the configuration on your reverse proxy is wrong, it will likely show up here with values that will not work in the external world. ...

A few tips on resolving ORA-29024 in the Oracle database. This error is thrown when Oracle cannot verify the SSL certificate. Troubleshooting ORA-29024: Certificate Validation Failure I have struggled with the ORA-29024 error a few times. Here are a few tips for sorting it out. Start fresh with a new wallet for SSL certificates only, and troubleshoot on a test database. Do not use the wallet that TDE uses or that has the database passwords. Add one certificate at a time. You can set the path to the new wallet as a parameter to various procedures like MAKE_REQUEST in APEX_WEB_SERVICE. In UTL_HTTP, you can set it with the procedure SET_WALLET. ...

If you connect to the database through SSH you may come across this message. Error message Server key did not validate in SQL Developer I frequently use SSH to connect to the Oracle database through some jump server. It is straightforward to set up; I blogged about it over six years ago. Today my Windows PC had the BSoD , and after it came back, I had this error when I tried to connect to the database again: ...