Script for checking the status of a user and its designated tablespace, before drop

The following script will generate a "drop user" and a "drop tablespace" command if the tablespace is used exclusively by the user you want to drop.

-- ============================================================
-- drop_user_safety_check.sql
-- Run as SYSDBA
-- ============================================================

set pagesize 200
set linesize 200
set verify off
set feedback off
set trimspool on

prompt
prompt Enter username to analyse:
accept v_username char prompt 'Username: '

column username format a30
column account_status format a20
column tablespace_name format a30
column status format a12
column other_owners format a12
column generated_statement format a120

prompt
prompt ============================================================
prompt 1. Account status
prompt ============================================================

select
    username,
    account_status,
    lock_date,
    expiry_date
from
    dba_users
where
    username = upper('&v_username');

prompt
prompt ============================================================
prompt 2. Tablespaces the user actually writes to (has segments in)
prompt ============================================================

select distinct
    s.tablespace_name
from
    dba_segments s
where
    s.owner = upper('&v_username')
order by
    s.tablespace_name;

prompt
prompt ============================================================
prompt 3. Tablespace status
prompt ============================================================

select distinct
    t.tablespace_name,
    t.status,
    t.contents
from
    dba_tablespaces t
    join dba_segments s
      on s.tablespace_name = t.tablespace_name
where
    s.owner = upper('&v_username')
order by
    t.tablespace_name;

prompt
prompt ============================================================
prompt 4. Exclusivity check (must be owned ONLY by this user)
prompt ============================================================
col tablespace_name format a30
col owner_count format 999999
col owners format a20
with ts_usage as (
    select
        tablespace_name,
        count(distinct owner) as owner_count,
        listagg(distinct owner, ', ') within group (order by owner) as owners
    from
        dba_segments
    where
        tablespace_name in (
            select distinct tablespace_name
            from dba_segments
            where owner = upper('&v_username')
        )
    group by
        tablespace_name
)
select
    tablespace_name,
    owner_count,
    owners,
    case
        when owner_count = 1 then 'YES'
        else 'NO'
    end as exclusive_to_user
from
    ts_usage
order by
    tablespace_name;

prompt
prompt ============================================================
prompt 5. Generated DROP statements (SAFE ONLY)
prompt ============================================================
set heading off
set pagesize 0
spool exec.sql

-- Drop user (always generated, copy-ready) or execute the file exec.sql
select
    'DROP USER ' || upper('&v_username') || ' CASCADE;' as generated_statement
from
    dual;

-- Drop tablespaces ONLY if exclusive
with ts_usage as (
    select
        tablespace_name,
        count(distinct owner) as owner_count
    from
        dba_segments
    where
        tablespace_name in (
            select distinct tablespace_name
            from dba_segments
            where owner = upper('&v_username')
        )
    group by
        tablespace_name
)
select
    'DROP TABLESPACE ' || tablespace_name ||
    ' INCLUDING CONTENTS AND DATAFILES;' as generated_statement
from
    ts_usage
where
    owner_count = 1
order by
    tablespace_name;
spool off;

prompt
prompt ============================================================
prompt End of report
prompt ============================================================

set feedback on
exit

What is an OS collation often referred to when dealing with PostgreSQL servers?

We have seen the following error in on of our postgres databases:

WARNING: database "testdb" has a collation version mismatch DETAIL: 
The database was created using collation version 2.17, but the operating system provides version 2.34. 
HINT: Rebuild all objects in this database that use the default collation and run ALTER DATABASE testdb REFRESH COLLATION VERSION, or build PostgreSQL with the right library version.

So what exactly is a "Collation"?

An OS collation is the set of language‑dependent rules supplied by the operating system that define how strings are compared and sorted (alphabetical order, case rules, accents, etc.). On Linux, these rules come from glibc locales.

What the error means

PostgreSQL databases rely on the operating system’s locale / collation implementation (typically glibc on Linux) for:

string ordering (ORDER BY)

comparisons (<, >, =)

indexes on text, varchar, char using the default collation The database testdb was created when the OS provided collation version 2.17, but the server is now running glibc 2.34.

In other words:

• Database collation version: 2.17 
• OS collation version: 2.34

This usually happens after:

• an OS upgrade
• moving a data directory to a newer system
• restoring a dump created on an older system

Collation rules can change between glibc versions.

Implications for the database

• The sort order of strings may differ
• Existing indexes may no longer match the current collation rules
• Query results involving ordering or comparisons could be wrong
• Index corruption does not occur, but index semantics may be invalid

PostgreSQL cannot automatically fix this, so it warns you.

Only objects that use the default collation, typically:

• btree indexes on text / varchar
• constraints using string comparison
• materialized views

Objects using COLLATE "C" / POSIX or ICU collations are not affected.

Solution:

For each database, and every schema:

Rebuild all indexes and other dependet objects that rely on the default collation

Then refresh the collation version:

ALTER DATABASE prod REFRESH COLLATION VERSION;

The alternative to this is stated in the messages itself:

or build PostgreSQL with the right library version

which means downgrade glibc or run PostgreSQL on a system with glibc 2.17 — usually not desirable.

For references, how does this work on the oracle platform?

For Oracle

• Collation (NLS sorting/comparison rules) is implemented inside the database engine
• Controlled via NLS_SORT, NLS_COMP, character set, etc.
• Independent of the OS
• OS upgrades do not change string ordering semantics

For PostgreSQL (default setup)

• Delegates collation behavior to the operating system locale
• Uses glibc on Linux for ORDER BY, comparisons, and btree indexes on text
• If glibc changes, collation rules may change → PostgreSQL detects and warns

So PostgreSQL is not missing a feature; it is designed to rely on the OS by default. It is a design choice: PostgreSQL usually delegates collation to the OS; Oracle does not

Workaround for ORA-00942: table or view does not exist when executing GRANT on a table list

When executing dynamic SQL against a schema that has differenly named objects, you will run into the "ORA-00942: table or view does not exist" during execution.

For example in my schema, I had 32 tables and one of them were named "flyway_schema_history". This means oracle will preserve the lower case naming in the dictionary.

The work around is to use DBMS_ASSERT.ENQUOTE_NAME to quote only when required.

In the example below, I am granting all of SCOTT's object to JIM:

BEGIN
  FOR r IN (
    SELECT object_name
    FROM   dba_objects
    WHERE  owner = 'SCOTT'
    AND    object_type IN ('TABLE', 'VIEW')
    AND    status = 'VALID'
  ) LOOP
    EXECUTE IMMEDIATE
      'GRANT SELECT ON SCOTT.' ||
      DBMS_ASSERT.ENQUOTE_NAME(r.object_name, FALSE) ||
      ' TO JIM';
  END LOOP;
END;
/

Inter-database import and exports using Oracle data pump

To move an entire schema from one schema to another within the same Oracle database, there is no need to export data to disk and then reimport it into the other schema. It can all be done using data pump network mode.

Create a loopback database link, meaning a database link pointing to the same database:

CREATE DATABASE LINK loopback_dblink
CONNECT TO system IDENTIFIED BY secretpassword
USING 'proddb01';

Then, precreate the schema JIM and create a separate tablespace for the schema:

CREATE BIGFILE TABLESPACE JIM_TABSPC
DATAFILE 
  '/data/oradata/proddb01/jim_tabscp.dbf' SIZE 9G AUTOEXTEND ON NEXT 256M MAXSIZE UNLIMITED
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;


CREATE USER JIM
  IDENTIFIED BY jimsseecretpassword 
  DEFAULT TABLESPACE JIM_TABSPC
  TEMPORARY TABLESPACE TEMP
  PROFILE APP_USER
  ACCOUNT UNLOCK;

GRANT CONNECT TO JIM;
GRANT RESOURCE TO JIM;
ALTER USER JIM DEFAULT ROLE ALL;

GRANT UNLIMITED TABLESPACE TO JIM;

ALTER USER JIM QUOTA UNLIMITED ON JIM_TABSPC;

Finally, execute the data pump script that will clone the data in schema SCOTT to schema JIM:

impdp system/secretpassword \
    schemas=SCOTT \
    network_link=loopback_dblink \
    remap_schema=SCOTT:JIM \
    remap_tablespace=SCOTT:JIM \
    directory=DATA_PUMP_DIR \
    logfile=scott_to_jim_clone.log \
    transform=OID:N \
    transform=SEGMENT_ATTRIBUTES:N

How to dump the entire schema's DDL into a file

pg_dump -d mydb \
  --schema-only \
  --no-owner \
  --no-privileges \
  -n myschema \
  -f create_schema.sql

The flag "--no-owner" tells pg_dump not to include OWNER TO ... statements in the dump. When you restore the file in another database, objects will automatically be owned by the user running psql, not by the owner of the the schema in the mydb database.

The flag "--no-privileges" tells pg_dump not to include GRANT/REVOKE statements. This avoids restoring production permissions into test and lets you manage privileges separately.

Just paste it into your terminal as the user owning the postgres software, and the file "create_schema.sql" will be created in your current directory.

Some examples of how to use the function pg_partition_tree

From PostgreSQL 11, the fuction pg_partition_tree has been available

Usage, in its simplest form:

SELECT *
FROM pg_partition_tree('ldksf.entitet');

select * from pg_partition_tree('ldksf.entitet');
       relid        | parentrelid | isleaf | level
--------------------+-------------+--------+-------
 entitet            |             | f      |     0
 entitet_default    | entitet     | t      |     1
 entitet_p0         | entitet     | t      |     1
 entitet_p120000000 | entitet     | t      |     1
 entitet_p150000000 | entitet     | t      |     1

Make it a bit more informativ, together with other tables in the data dictionary. Put the following into a file called pg_tree_info.sql:

\echo myschema = :myschema
\echo mytable  = :mytable

SELECT
    s.schemaname,
    s.relname AS table_name,
    s.n_live_tup,
    s.last_analyze,
    s.last_autoanalyze
FROM pg_partition_tree(format('%I.%I', :'myschema', :'mytable')::regclass) pt
JOIN pg_class c
  ON c.oid = pt.relid
JOIN pg_namespace n
  ON n.oid = c.relnamespace
JOIN pg_stat_all_tables s
  ON s.schemaname = n.nspname
 AND s.relname = c.relname
ORDER BY s.n_live_tup DESC, s.last_analyze;

SELECT
    pt.level,
    pt.isleaf,
    n.nspname,
    c.relname
FROM pg_partition_tree(
        format('%I.%I', :'myschema', :'mytable')::regclass
     ) pt
JOIN pg_class c ON c.oid = pt.relid
JOIN pg_namespace n ON n.oid = c.relnamespace
ORDER BY pt.level, c.relname;

Example output:

myschema = scott
mytable = entitet
 schemaname |     table_name     | n_live_tup |         last_analyze          |       last_autoanalyze
------------+--------------------+------------+-------------------------------+-------------------------------
 ldksf      | entitet_p30000000  |   14706380 | 2026-03-10 22:15:50.390363+01 | 2026-03-10 16:29:36.398134+01
 ldksf      | entitet_p0         |   12193064 | 2026-03-10 22:15:50.749426+01 | 2026-03-10 16:27:35.272815+01
 ldksf      | entitet_p60000000  |    5481387 | 2026-03-10 22:15:51.069335+01 | 2026-03-10 16:31:35.842357+01
 ldksf      | entitet_default    |          0 | 2026-03-10 22:15:53.688216+01 |
 ldksf      | entitet_p180000000 |          0 | 2026-03-10 22:15:53.68893+01  |
 
 
 level | isleaf | nspname  |      relname
-------+--------+----------+--------------------
     0 | f      |   ldksf  | entitet
     1 | t      |   ldksf  | entitet_default
     1 | t      |   ldksf  | entitet_p0
     1 | t      |   ldksf  | entitet_p120000000
     1 | t      |   ldksf  | entitet_p150000000

Excute it like this:

psql -h prod1.pgsql01.oric.no -d mydb -U scott -v myschema=ldksf -v mytable=entitet -f pg_tree_info.sql

Find active queries in PostgreSQL

SELECT pid, state, now() - query_start AS duration, left(query, 80) AS query
FROM pg_stat_activity
WHERE state != 'idle' AND usename = 'myuser'
ORDER BY query_start;

Example output:

   pid   | state  |    duration     |                                    query
---------+--------+-----------------+------------------------------------------------------------------------------
 1648516 | active | 01:29:28.979693 |                                                                             +
         |        |                 | UPDATE myschema.mytable1 eeu                                   +
         |        |                 | SET aggregated_value = (SELECT e.aggregated_value FROM

This is the query I am looking for, started in another session. The full query text is

UPDATE myschema.mytable1 eeu
SET aggregated_value = (SELECT e.aggregated_value FROM myschema.mytable2 e WHERE e.systemid = eeu.entitet AND e.instansid = eeu.instansid);

Identity columns vs free-standing sequences in PostgreSQL

Are the columns typically used in primary key columns, defined as IDENTITY columns, or as standalone columns with a default value generated from a free-standing sequence?

SELECT table_name, column_name, is_identity, column_default
FROM information_schema.columns
WHERE table_schema = 'myschema'
AND column_name IN ('sekvnr', 'id')
AND table_name IN ('table1','table2','table3','table4',
                   'table5','table6',
                   'table7','table8')
ORDER BY table_name, column_name;

Result:

        table_name          |  column_name  | is_identity | column_default
----------------------------+---------------+-------------+----------------
 table1                     | id            | YES         |
 table2                     | id            | YES         |
 table3                     | sekvnr        | NO          |
 table4                     | id            | YES         |
 table5                     | id            | YES         |
 table6                     | id            | YES         |
 table7                     | id            | YES         |
 table8                     | id            | YES         |
(8 rows)

It's clear from the query that the only table that is still supported by a free-standing sequence to generate its primary key values, is table3!

How to find the current value for a PostgreSQL sequence

SELECT last_value FROM myschema.mysequence;

or

SELECT nextval('myschema.mysequence');

Note: nextval() advances the sequence — use last_value if you just want to inspect without side effects.

How to find triggers in a PostgreSQL schema

Execute the SQL below as a user with ownership to the schema. To verify that you have schema privileges, execute:

SELECT has_schema_privilege('myuser', 'myschema', 'USAGE');

If the reply is

has_schema_privilege
----------------------
 t

You can login

psql -h localhost -d mydb -U myuser

and execute

\x
Expanded display is on.

SELECT
    'TRIGGER' AS object_type,
    t.tgname AS trigger_name,
    n.nspname AS schema_name,
    c.relname AS table_name,

    -- Decode tgenabled letters
    CASE t.tgenabled
        WHEN 'O' THEN 'ENABLE'
        WHEN 'A' THEN 'ENABLE ALWAYS'
        WHEN 'R' THEN 'ENABLE REPLICA'
        WHEN 'D' THEN 'DISABLED'
        ELSE t.tgenabled
    END AS enabled_state,

    -- Trigger timing from tgtype
    CASE
        WHEN (t.tgtype & 2)  <> 0 THEN 'BEFORE'
        WHEN (t.tgtype & 64) <> 0 THEN 'INSTEAD OF'
        ELSE 'AFTER'
    END AS timing,

    -- Events
    (SELECT string_agg(event, ', ')
     FROM (
            SELECT unnest(
                ARRAY[
                    CASE WHEN (t.tgtype & 4)  <> 0 THEN 'INSERT'   END,
                    CASE WHEN (t.tgtype & 8)  <> 0 THEN 'DELETE'   END,
                    CASE WHEN (t.tgtype & 16) <> 0 THEN 'UPDATE'   END,
                    CASE WHEN (t.tgtype & 32) <> 0 THEN 'TRUNCATE' END
                ]
            ) AS event
        ) ev
    ) AS events,

    pron.nspname || '.' || p.proname AS trigger_function

FROM pg_trigger t
JOIN pg_class c        ON c.oid = t.tgrelid
JOIN pg_namespace n    ON n.oid = c.relnamespace
JOIN pg_proc p         ON p.oid = t.tgfoid
JOIN pg_namespace pron ON pron.oid = p.pronamespace

WHERE n.nspname = current_schema()
  AND NOT t.tgisinternal
  AND t.tgconstraint = 0
  AND c.relkind <> 'p'
ORDER BY trigger_name;

Which will list your triggers:

-[ RECORD 1 ]----+------------------------------------------------------------------
object_type      | TRIGGER
trigger_name     | mytrg1
schema_name      | myschema
table_name       | mytable1
enabled_state    | E
timing           | BEFORE
events           | UPDATE
trigger_function | myschema.trigger_fct_upd_mytable1
-[ RECORD 2 ]----+------------------------------------------------------------------
object_type      | TRIGGER
trigger_name     | mytrg2
schema_name      | myschema
table_name       | mytable2
enabled_state    | E
timing           | AFTER
events           | DELETE
trigger_function | myschema.trg_close_fct

Useful query against v$archived_log in recovery situations

SELECT thread#,
         COUNT(*)                        AS cnt,
         MIN(sequence#)                  AS min_seq,
         MAX(sequence#)                  AS max_seq,
         MIN(first_time)                 AS min_time,
         MAX(next_time)                  AS max_time
  FROM   v$archived_log
  WHERE  deleted = 'NO'
  GROUP  BY thread#
  ORDER  BY thread#;

   THREAD#    CNT    MIN_SEQ    MAX_SEQ               MIN_TIME               MAX_TIME
__________ ______ __________ __________ ______________________ ______________________
         1     80       7936       8015 2026-02-16 15:38:02    2026-02-18 14:15:42

Dropping an audit policy for a specific user

You have the following audit policy:

CREATE AUDIT POLICY monitor_sys_everything
  ACTIONS ALL;

AUDIT POLICY monitor_sys_everything BY SYS;

I wanted to drop the auditing policy:

SQL> noaudit policy monitor_sys_everything;

Noaudit succeeded.

SQL> drop audit policy monitor_Sys_everything;
drop audit policy monitor_Sys_everything
*
ERROR at line 1:
ORA-46361: Audit policy cannot be dropped as it is currently enabled.

Reason:

The policy is enabled for a specific user. The "noaudit" statement used above will disable it for everyone.

Solution:

Disable it for the specific user:

SQL> NOAUDIT POLICY monitor_sys_everything BY SYS;

It can now be dropped:

drop audit policy monitor_sys_everything;

Compression for Postgres tables

In PostgreSQL you don’t create a “compressed table” in the Oracle sense. Rather, compression is achieved per-column compression via TOAST, plus (if desirable) a few extension/filesystem tricks.

Postgres compresses large variable-length columns using TOAST:

text, varchar, bytea, jsonb, xml, numeric, etc.

TOAST will be applied when a row is too big to fit in an 8kB page; large values are stored in a separate TOAST table, optionally compressed.

There is no built-in heap/row compression for normal fixed-width columns (e.g. integer, bigint) in vanilla Postgres.

From PostgreSQL 14 onward you can choose compression algorithm per column: pglz (classic) or lz4 (faster, generally preferred).

Here is how I did it in my test environment:

First, ensure your PostgreSQL server was compiled with LZ4 support (use pg_config --configure and look for --with-lz4)

pg_config --configure | grep lz4

It will show you a long list of options that was used when PostgreSQL was built. Look for '--with-lz4'

Set compression globally:

show default_toast_compression;
 default_toast_compression
---------------------------
 pglz
(1 row)

postgres=# ALTER SYSTEM SET default_toast_compression = 'lz4';
ALTER SYSTEM
postgres=# SELECT pg_reload_conf();
 pg_reload_conf
----------------
 t
(1 row)

postgres=# show default_toast_compression;
 default_toast_compression
---------------------------
 lz4

Optional: default to LZ4 for this session

  SET default_toast_compression = 'lz4';

Yet another option is to set LZ4 for a specific database:

 ALTER DATABASE mydb SET default_toast_compression = 'lz4';

Create the table:

CREATE TABLE app_logs (
    log_id      bigserial PRIMARY KEY,
    log_time    timestamptz NOT NULL,
    level       text        NOT NULL,
    message     text        COMPRESSION lz4,
    details     jsonb       COMPRESSION lz4
);

Note:

COMPRESSION lz4 / COMPRESSION pglz is a column option.

Only matters for TOAST-able types; it won’t change anything for integer, date, etc.

Compression only happens when the row gets large enough for TOAST to kick in (roughly when row > ~2kB).

You can switch existing columns to LZ4 (or back to pglz):

 ALTER TABLE app_logs
    ALTER COLUMN message SET COMPRESSION lz4,
    ALTER COLUMN details SET COMPRESSION lz4;

 

Note that an ALTER TABLE only changes the future TOAST entries. To actually recompress existing rows you need to cause a rewrite. Common options:

-- 1) Table rewrite (heavy, but clean)
ALTER TABLE app_logs SET (toast_tuple_target = 2040);  -- optional tweak
VACUUM FULL app_logs;

-- or 2) Cluster on some index (also rewrites)
CLUSTER app_logs USING app_logs_pkey;
ANALYZE app_logs;

Any bulk rewrite (incl. CREATE TABLE AS ..., INSERT INTO new SELECT ... FROM old) will store new TOAST values using the new compression method.

Check that the table is using column compression for TOAST values:

-- Main table vs TOAST table sizes
SELECT
    relname,
    pg_size_pretty(pg_relation_size(oid))          AS heap_size,
    pg_size_pretty(pg_total_relation_size(oid))    AS total_with_indexes_toast
FROM pg_class
WHERE relname IN ('app_logs2','app_logs3','app_logs4');

-- Look at TOAST table directly
SELECT
    c1.relname       AS main_table,
    c2.relname       AS toast_table,
    pg_size_pretty(pg_total_relation_size(c2.oid)) AS toast_total
FROM pg_class c1
JOIN pg_class c2 ON c1.reltoastrelid = c2.oid
WHERE c1.relname IN ('app_logs2','app_logs3','app_logs4');

In a simple test, I created three tables with three different compression directives and created one long value that would make sure it was TOASTED:

CREATE TABLE app_logs2 (
    log_id      bigserial PRIMARY KEY,
    log_time    timestamptz NOT NULL,
    level       text        NOT NULL,
    message     text,
    details     jsonb
);

CREATE TABLE app_logs3 (
    log_id      bigserial PRIMARY KEY,
    log_time    timestamptz NOT NULL,
    level       text        NOT NULL,
    message     text        COMPRESSION lz4,
    details     jsonb       COMPRESSION lz4
);

CREATE TABLE app_logs4 (
    log_id      bigserial PRIMARY KEY,
    log_time    timestamptz NOT NULL,
    level       text        NOT NULL,
    message     text        COMPRESSION pglz,
    details     jsonb       COMPRESSION pglz
);

INSERT INTO app_logs2 (log_time, level, message, details)
VALUES (
    now(),
    'INFO',
    repeat('x', 100000),                -- make it large enough to be TOASTed
    jsonb_build_object('k', repeat('y', 100000))
);

INSERT INTO app_logs3 (log_time, level, message, details)
VALUES (
    now(),
    'INFO',
    repeat('x', 100000),                -- make it large enough to be TOASTed
    jsonb_build_object('k', repeat('y', 100000))
);

INSERT INTO app_logs4 (log_time, level, message, details)
VALUES (
    now(),
    'INFO',
    repeat('x', 100000),                -- make it large enough to be TOASTed
    jsonb_build_object('k', repeat('y', 100000))
);

As expected, the app_logs2 defaulted to lz4 (set globally):

SELECT
    relname,
    pg_size_pretty(pg_relation_size(oid))          AS heap_size,
    pg_size_pretty(pg_total_relation_size(oid))    AS total_with_indexes_toast
FROM pg_class
WHERE relname IN ('app_logs2','app_logs3','app_logs4');

-- Look at TOAST table directly
SELECT
    c1.relname       AS main_table,
    c2.relname       AS toast_table,
    pg_size_pretty(pg_total_relation_size(c2.oid)) AS toast_total
FROM pg_class c1
JOIN pg_class c2 ON c1.reltoastrelid = c2.oid
WHERE c1.relname IN ('app_logs2','app_logs3','app_logs4');

 relname  | heap_size  | total_with_indexes_toast
-----------+------------+--------------------------
 app_logs2 | 8192 bytes | 32 kB
 app_logs3 | 8192 bytes | 32 kB
 app_logs4 | 8192 bytes | 48 kB
(3 rows)

 main_table |   toast_table    | toast_total
------------+------------------+-------------
 app_logs2  | pg_toast_2510179 | 8192 bytes
 app_logs3  | pg_toast_2510188 | 8192 bytes
 app_logs4  | pg_toast_2510197 | 24 kB
(3 rows)

Remember, per-column compression via default_toast_compression doesn not show up in \d+ unless it was explicitly set in the column definition.

So even if table app_logs2 uses compression for TOASTable columns, it does not reveal this fact when being described:

CREATE TABLE app_logs2 (
    log_id      bigserial PRIMARY KEY,
    log_time    timestamptz NOT NULL,
    level       text        NOT NULL,
    message     text,
    details     jsonb
);

 \d+ app_logs2
                                                                 Table "myschema.app_logs2"
  Column  |           Type           | Collation | Nullable |                  Default                  | Storage  | Compression | Stats target | Description
----------+--------------------------+-----------+----------+-------------------------------------------+----------+-------------+--------------+-------------
 log_id   | bigint                   |           | not null | nextval('app_logs2_log_id_seq'::regclass) | plain    |             |              |
 log_time | timestamp with time zone |           | not null |                                           | plain    |             |              |
 level    | text                     |           | not null |                                           | extended |             |              |
 message  | text                     |           |          |                                           | extended |             |              |
 details  | jsonb                    |           |          |                                           | extended |             |              |
 

How to detach an ORACLE_HOME to an existing inventory

I want to detach an existing oracle_home from my inventory.

Note that the last argument must be the full path to the oracle software, NOT the oracle name found in the inventory:

./runInstaller -silent -detachHome ORACLE_HOME="/sw/oracle/product/19c29"
Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB.   Actual 6143 MB    Passed
The inventory pointer is located at /etc/oraInst.loc
You can find the log of this install session at:
 /home/oracle/oraInventory/logs/DetachHome2026-01-25_12-51-17PM.log
'DetachHome' was successful.