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.

How to unload rows in an Oracle database to disk using sqlcl

In sqlcl, execute these lines to unload SCOTT.EMP to disk:

set arraysize 200
set load batch_rows 200
UNLOAD TABLE SCOTT.EMP DIR /datadisk1/export

Do not use semicolon after the end of the command!

Find total size of all databases in PostgreSQL cluster

SELECT database, size FROM (
  SELECT datname AS database,
         pg_size_pretty(pg_database_size(datname)) AS size,
         0 AS sort_order
  FROM pg_database
  UNION ALL
  SELECT 'TOTAL',
         pg_size_pretty(SUM(pg_database_size(datname))),
         1
  FROM pg_database
) AS sub
ORDER BY sort_order, size DESC;

Example output:

             database             |  size
----------------------------------+---------
 mydb01                           | 7819 kB
 mydb02                           | 7795 kB
 postgres                         | 7739 kB
 template0                        | 7731 kB
 template1                        | 7715 kB
 proddb01                         | 76 GB
 proddb02                         | 2971 GB
 proddb03                         | 22 GB
 warehouse01                      | 11 TB
 testdb01                         | 106 MB
 TOTAL                            | 14 TB
(11 rows)

Find the number of columns in a table in postgreSQL

SELECT count(*)
FROM information_schema.columns
WHERE table_name = 'mytable'
  AND table_schema = 'jim';

Exchange all occurences of a string in all files in a sub-directory structure

find /var/lib/pgsql/cities/aberdeen/schema -type f -exec sed -i 's/aberdeen/glascow/g' {} +

List all files containg a string in a subdirectory structure

grep -Rl 'aberdeen' /var/lib/pgsql/cities/aberdeen/schema

Result:

/var/lib/pgsql/cities/aberdeen/schema/grants/grant.sql
/var/lib/pgsql/cities/aberdeen/schema/sequences/sequence.sql
/var/lib/pgsql/cities/aberdeen/schema/sequence_values/sequence_value.sql
/var/lib/pgsql/cities/aberdeen/schema/tables/table.sql
/var/lib/pgsql/cities/aberdeen/schema/tables/AUTOINCREMENT_table.sql
/var/lib/pgsql/cities/aberdeen/schema/tablespaces/tablespace.sql
/var/lib/pgsql/cities/aberdeen/schema/triggers/trigger.sql
/var/lib/pgsql/cities/aberdeen/schema/views/view.sql

Generate truncate table statements in PostgreSQL

To generate a script in postgreSQL, equivalent to the Oracle-style shown below:

select 'truncate table ' || table_name || ' cascade;' from dba_tables where owner='MYSCHEMA';

, put this in a file called gen_truncate.sql

/*
| Setting                  | Effect                                                    |
| ------------------------ | --------------------------------------------------------- |
| `\o /path/to/file`       | Redirects all query output to the file                    |
| `\pset format unaligned` | Produces plain text output (no table formatting)          |
| `\pset tuples_only on`   | Suppresses headers and row counts                         |
| `\pset footer off`       | Removes `x rows` footer                                   |
| `\pset border 0`         | Removes any border formatting (mostly for aligned format) |
*/

\o truncate_tables.sql
\pset format unaligned
\pset tuples_only on
\pset footer off
\pset border 0
SELECT 'TRUNCATE TABLE ' || schemaname || '.' || tablename || ' CASCADE;' AS stmt
FROM pg_tables
WHERE schemaname = 'myschema';
\o

Execute it:

psql mydb -f gen_truncate.sql

or login to the desired database directly as the owner of the table:

psql -h localhost -d mydb -U myuser -f gen_truncate.sql

Do I need special privileges to create temporary tables in a database in PostgreSQL?

To verify if your user has the privilege to create temporary segments in a postgreSQL database, use this check:

psql -h localhost -d mydb -U myuser

 SELECT has_database_privilege(current_user, current_database(), 'TEMP');

If the value returned shows:

has_database_privilege
------------------------
 f

To be able to create a temporary table in this database, my user needs privileges to do so:

psql
postgres=# grant temp on database mydb to myser;
GRANT

The same test will now yield true instead of false:

SELECT has_database_privilege(current_user, current_database(), 'TEMP');
 has_database_privilege
------------------------
 t
(1 row)

I can now create a temporary table:

CREATE TEMP TABLE table_counts (table_name text, row_count bigint);

Before the grant, the access privileges for my database was

mydb=> \l+ mydb

  |      Access privileges       |
  +------------------------------+
  | postgres=CTc/postgres       +|
  | myuser=c/postgres           +|
 

After the grant:

  |      Access privileges       |
  +------------------------------+
  | postgres=CTc/postgres       +|
  | myuser=Tc/postgres          +|

Notice how the privileges for "myuser" has a "T" ammended to it. This indicate the permission to create temporary objects.

PostgreSQL query that mimics Oracle's DBA_OBJECTS aggregation

with base_objects as (
  select 
    case 
      when c.relkind = 'r' then 'TABLE'
      when c.relkind = 'v' then 'VIEW'
      when c.relkind = 'm' then 'MATERIALIZED VIEW'
      when c.relkind = 'i' then 'INDEX'
      when c.relkind = 'S' then 'SEQUENCE'
      when c.relkind = 'f' then 'FOREIGN TABLE'
      when c.relkind = 'p' then 'PARTITIONED TABLE'
      when c.relkind = 'I' then 'PARTITIONED INDEX'
      else 'OTHER'
    end as object_type
  from pg_class c
  join pg_namespace n on n.oid = c.relnamespace
  where n.nspname = 'myschema'

  union all

  select 
    case p.prokind
      when 'f' then 'FUNCTION'
      when 'p' then 'PROCEDURE'
      when 'a' then 'AGGREGATE'
      when 'w' then 'WINDOW'
      else 'OTHER'
    end as object_type
  from pg_proc p
  join pg_namespace n on n.oid = p.pronamespace
  where n.nspname = 'myschema'

  union all
  select 'TYPE'
  from pg_type t
  join pg_namespace n on n.oid = t.typnamespace
  where n.nspname = 'myschema'
    and t.typtype in ('c', 'e', 'd') -- composite, enum, domain

  union all

  select 'CONSTRAINT'
  from pg_constraint c
  join pg_namespace n on n.oid = c.connamespace
  where n.nspname = 'myschema'
)

select object_type, count(*)
from base_objects
group by object_type
order by object_type;

How to delete all lines above and below the current line in the text editor vi

In vi (or vim), you can delete all lines above the current line with this one simple command:

:1,.−1d

To delete all rows above the current lin, press d (delete) followed by capital G (end of document):

d + G

How to check if your EBS system is on AD/TXK Delta 13

SELECT bug_number, creation_date
FROM   ad_bugs
WHERE  bug_number IN (
  -- AD/TXK Delta 13 driver patch numbers
  '35163625','35163283','35163924','35162879'
)
ORDER BY creation_date DESC;

If no rows are returned, you are not on AD/TXK Delta 13, yet

How to pass parameters to a psql script

To use variables in your sql script, use the -v option to set variables on the command line.

Consider this example:

export PGPASSWORD=mysecretpassword
psql -h myserver.oric.no -d db01 -U scott -v schema=scott -v table=mytable -f myquery.sql

The query in myquery.sql can then referance the "schema" and "table" variables, like this:

SELECT
    att.attname AS column_name
FROM
    pg_attribute att
JOIN
    pg_class cls ON cls.oid = att.attrelid
JOIN
    pg_namespace ns ON ns.oid = cls.relnamespace
WHERE
    ns.nspname = :'schema'
    AND cls.relname = :'table'
    AND att.attnotnull
    AND att.attnum > 0
    AND NOT att.attisdropped;

Find out if a constraint in PostgreSQL is defined as deferrable

Logged into the relevant database, find out which FK are deferrable:

SELECT conname, condeferrable, condeferred
FROM pg_constraint
WHERE conrelid in ( 'mytable1'::regclass,'mytable2'::regclass) 
AND contype='f';
           conname            | condeferrable | condeferred
------------------------------+---------------+-------------
 mytable1_id_fkey             | f             | f
 mytable1_id_fkey             | f             | f
 mytable2_id_fkey             | f             | f

If condeferrable = t (true), the constraint is created initially deferrable

If condeferrable = f (false), like in my case, it is not.

Terminate PostgresSQL sessions

-- Terminate all connections from user "sales"
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE usename = 'sales';

In my case the result was:

 pg_terminate_backend
----------------------
 t
 t
(2 rows)

Check active sessions in PostgreSQL

The closest equivalent to this oracle query:

SELECT USERNAME, COUNT(*) 
FROM v$session 
WHERE type <> 'BACKGROUND' 
GROUP BY username;

Would be

SELECT datname, usename AS username, COUNT(*)
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY datname, usename
ORDER BY datname, usename;

Example output:

 datname  |     username      | count
----------+-------------------+-------
 postgres | postgres          |     1
 postgres | postgres_exporter |     1
 mydb     | myser             |     2
(3 rows)

Convert all contents in a file to lower case using vim

Open your file in vi

vi myfile

Press the escape key to enter interactive mode, then type

:%s/.*/\L&/

Press escape, then save + exit the editor:

:wq

How to set echo in a SQL script executed in psql

Use one of two options 1. in the sql file, add the following directive

\set ECHO queries

2. execute the script using the -e flag

psql -e -f myfile.sql

Syntax for drop/recreate of constraints in PostgreSQL

1. Find the constraints belonging to a specific table:

SELECT conname
FROM pg_constraint
WHERE conrelid = 'mytable'::regclass;
    conname
----------------
 sys_c002733781
 sys_c002733782
(2 rows)

2. Drop them:

ALTER TABLE mytable DROP CONSTRAINT sys_c002733781;
ALTER TABLE
ALTER TABLE mytable DROP CONSTRAINT sys_c002733782;
ALTER TABLE

3. Load your data using your preferred method

4. Recreate the FK constraints but skip validating existing data (only new inserts/updates are checked):

alter table mytable add constraint sys_c002733781 foreign key(job_id) references jobs(id) on delete cascade not valid;
ALTER TABLE

alter table mytable add constraint sys_c002733782 foreign key(task_id) references tasks(id) on delete cascade not valid;
ALTER TABLE

5. Validate constraints:

alter table mytable validate constraint sys_c002733781;
ALTER TABLE
alter table mytable validate constraint sys_c002733782;
ALTER TABLE