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);