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.