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