Database

Oracle Spatial Studio is a bit like a music studio. It is a place to explore things and get new ideas in the process. With this tool you can explore data on maps and discover new connections between various datasets. You don’t need a lot of data before a viualization becomes useful, especially for spatial data. First session with Oracle Spatial Studio With Spatial Studio it is easy to load, verify and visualize spatial data. Data you want to put on a map are often distributed in special format like GeoJSON and ESRI spatial files. I am not aware of any other simple tool that makes it easier to load this kind of data into the Oracle database. ...

This is part 0 in a series on Oracle Spatial Studio. There are a few ways to run Oracle Spatial Studio, this post shows you how to run Oracle Spatial Studio from your PC and from a stack in Oracle Cloud. Get started with Oracle Spatial Studio - Installation It has been a while… I have looked into Oracle Spatial Studio and decided to blog about it. Run it from your PC Also known as on-prem. You can choose between: ...

I wrote about UUIDs in the Oracle database two months back in this post , but it turns out that UUID v4 is so… 2005 . You probably need UUID v7 Head over to How UUIDv7 makes your (database) life easier to understand why you want v7. As mentioned in the article there is no native support for v7 in Oracle, neither a function to generate it or a proper datatype for UUIDs like PostgreSQL has. But the article shows an implementation of v7 generation in a PL/SQL function called generate_uuid_v7 that I used in some testing. ...

Update: I wrote another post about UUID v7 with some alternative solutions. UUIDs are useful, especially when you expose data in REST APIs, but there are cases where you may want to stick to the good old sequence-based primary key. UUIDs in Oracle Database #JoelKallmanDay UUID, short for universally unique identifier, are also known as globally unique identifier (GUID). You can use the function sys_guid() in the Oracle database to generate it: ...

Oracle launched version 23ai of the database May 2. Immediately after the event we could pull the Free edition from the Oracle Container Registry and play with it. I blogged about how you can set up 23c Free in Your Own Oracle Database Lab in 1-2-3 218 days ago 1. This command pulls the latest version: podman pull container-registry.oracle.com/database/free:latest Have a look in Your Own Oracle Database Lab in 1-2-3 for more details on how to create a new container with it. I created mine with: ...

Update: See this post for an update for version 2.1 on Oracle Linux. How to install OML4Py Client on Ubuntu 22.04 I needed the Oracle Machine Learning for Python (OML4Py) Client (not the Server) in order to convert a pretrained model so that I could import it into the newly released Oracle database 23ai. I wrote this from information I found in the User’s Guide and a lot of internet search. The PC I am using for this runs Ubuntu, so I adapted the installation for Ubuntu 22.04 (Jammy Jellyfish). After I completed the whole procedure I decided to verify it on a fresh Vagrant box: ...

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. ...

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. ...

Oracle Database 12.2 became available for download last week. This weekend I’ve been playing with Docker and created a container with it. The whole process is so easy, thanks to the work by Gerald Venzl at Oracle. You’ll find all the information you need in his blog post . Though the post is for version 12.1.0.2, the dockerfiles have already been updated for 12.2.0.1, and you can download it from Github using the link he provides in the post. ...