How to drop a user from a PostgreSQL database

Tested against PostgreSQL version 11


This is how you drop a user (or "role" as it is called in postgresQL):

--If necessary, transfer object to another user first:
reassigned owned by scott TO jim;
-- remove privileges granted to scott for objects that it doesn't own:
drop owned by scott;
-- finally, drop the user:
drop user scott;

Documentation

How to describe and analyze a partitioned table in postgreSQL

In this example, I am describing a table called "albums" in a database called "music". The table is partitioned:

psql -h myserver.mydomain.com -U music musicdb
Password for user music:
psql (11.2)
Type "help" for help.

Describe the table:
musicdb=> \d album

Table "music.album"
    Column    |         Type          | Collation | Nullable |           Default
--------------+-----------------------+-----------+----------+------------------------------
 album_id     | integer               |           | not null | generated always as identity
 album_name   | character varying(40) |           |          |
 genre        | character varying(20) |           |          |
 label        | character varying(40) |           |          |
 release_year | date                  |           |          |
Partition key: RANGE (date_part('year'::text, release_year))
Number of partitions: 3 (Use \d+ to list them.)

Describe the table's partitions:
musicdb=> \d+ album

Table "music.album"
    Column    |         Type          | Collation | Nullable |           Default            | Storage  | Stats target | Description
--------------+-----------------------+-----------+----------+------------------------------+----------+--------------+-------------
 album_id     | integer               |           | not null | generated always as identity | plain    |              |
 album_name   | character varying(40) |           |          |                              | extended |              |
 genre        | character varying(20) |           |          |                              | extended |              |
 label        | character varying(40) |           |          |                              | extended |              |
 release_year | date                  |           |          |                              | plain    |              |
Partition key: RANGE (date_part('year'::text, release_year))
Partitions: albums_1974 FOR VALUES FROM ('1974') TO ('1975'),
            albums_1979 FOR VALUES FROM ('1979') TO ('1980'),
            albums_default DEFAULT

Analyze the table:
musicdb=> analyze verbose album;

INFO:  analyzing "music.album" inheritance tree
INFO:  "albums_1974": scanned 1 of 1 pages, containing 2 live rows and 0 dead rows; 2 rows in sample, 2 estimated total rows
INFO:  "albums_1979": scanned 1 of 1 pages, containing 1 live rows and 0 dead rows; 1 rows in sample, 1 estimated total rows
INFO:  analyzing "music.albums_1974"
INFO:  "albums_1974": scanned 1 of 1 pages, containing 2 live rows and 0 dead rows; 2 rows in sample, 2 estimated total rows
INFO:  analyzing "music.albums_1979"
INFO:  "albums_1979": scanned 1 of 1 pages, containing 1 live rows and 0 dead rows; 1 rows in sample, 1 estimated total rows
INFO:  analyzing "music.albums_default"
INFO:  "albums_default": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows

To check the statistics:

select attname,inherited,null_frac,n_distinct,most_common_vals,most_common_freqs,histogram_bounds 
from pg_stats 
where tablename='album';

attname	inherited	null_frac	n_distinct	most_common_vals	most_common_freqs	histogram_bounds
album_id	true	0.0	-1.0			{1,2,3}
album_name	true	0.0	-0.6666667	{KISS}	{0.666666687}
genre	true	0.0	-0.33333334	{"Hard Rock"}	{1}
label	true	0.6666667	-0.3333333
release_year	true	0.0	-0.6666667	{1974-01-18}	{0.666666687}

How to connect to a non-default PostgreSQL database as a specific user using pslq

To connect to a specific database as a specific user:

psql -h myserver.mydomain.com -U music musicdb

or 

psql -h 127.0.0.1 -U music musicdb

Or, when already connected to database in psql.
Here, I first connect to the default database (postgres) as the software owner (also postgres) but then change the database connection to a database called "mydb" logged in as user "jim":

[postgres@myserver.mydomain.com ~]$ psql
psql (11.7)
Type "help" for help.

postgres=# \conninfo
You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5432".
postgres=# \c mydb jim myserver.mydomain.com 5432
Password for user jim:
You are now connected to database "mydb" as user "jim" on host "myserver.mydomain.com" at port "5432".

How to show current connect info in PosgreSQL

[postgres@myserver.mydomain.com ~]$ psql
psql (11.2)
Type "help" for help.

postgres=# \connect musicdb
You are now connected to database "musicdb" as user "postgres".
musicdb=# \conninfo
You are connected to database "musicdb" as user "postgres" via socket in "/var/run/postgresql" at port "5432".

How to create a unified auditing policy

For example, create a policy to audit the "create session" privilege:

create audit policy audit_cre_session
  privileges create session;

Start auditing of the policy:

audit policy audit_cre_session;

In the next example, I am creating a policy to audit DML statements on a specific table:

create audit policy audit_dml_emp
actions delete on scott.emp,
        insert on scott.emp,
        update on scott.emp
;

Create a separate policy for auditing of queries against a specific table:

create audit policy audit_select_emp
actions select on scott.emp
;

Finally, start auditing both policies:

audit policy audit_dml_emp;
audit policy audit_select_emp;

The results of the auditing can be observed through the unified_audit_trail view:

select audit_type,
       os_username,
       userhost,
       terminal,
       authentication_type,
       dbusername,
       client_program_name,
       event_timestamp,
       action_name,
       return_code,
       object_name,
       sql_text,
       system_privilege_used,
       unified_audit_policies
from unified_audit_trail
order by event_timestamp desc;

If you later need to modify a policy, use

alter audit policy audit_dml_emp drop actions delete on scott.emp;

to reverse your change back to its original state:

alter audit policy audit_dml_emp add actions delete on scott.emp;

12.2 documentation here

19c documentation here

How to count the number of LOGONS based on audit entries in DBA_AUDIT_SESSION

CREATE SESSION is audited by default

To get an idea of the number of sessions created by the different users, you can execute the following query against DBA_AUDIT_SESSION:

SELECT TO_DATE(TIMESTAMP,'dd.mm.yyyy') "logon  time",USERNAME, COUNT(*)
FROM    DBA_AUDIT_SESSION
WHERE   ACTION_NAME = 'LOGON'
AND     USERNAME IN ('JIM','DWIGHT','KEVIN')
AND     TRUNC(TIMESTAMP) BETWEEN '12.03.2019' AND '13.03.2019'
GROUP BY TO_DATE(TIMESTAMP,'dd.mm.yyyy'),USERNAME
ORDER BY 1;

logon time	USERNAME	COUNT(*)
12.03.2019	JIM	1
12.03.2019	DWIGHT	71
12.03.2019	KEVIN	1
13.03.2019	JIM	10
13.03.2019	DWIGHT	1
13.03.2019	KEVIN	1

How to perform an online shrink of a segment

This feature has been around since Oracle 11.1, and can be a convenient alternative to reorganizing an object to reclaim wasted space.

First, row movement must be enabled:

alter table SCOTT.EMP enable row movement;

If desirable, use the COMPACT keyword to tell Oracle to defragment the segment space and compact the table rows.
You can then postpone the resetting of the high water mark and the deallocation of the space until off-peak hours:

alter table SCOTT.EMP shrink space compact;

The final phase will do the actual reset the HWM. Note that cursors depending on the object will be invalidated and need to be reparsed:

alter table SCOTT.EMP shrink space;

The official documentation can be found here

Another good source is oracle-base.com

An interesting note regarding shrinking of LOB segments is Doc ID 1451124.1: "How to Shrink (make less sparse) a LOB (BASICFILE or SECUREFILE)?" available at support.oracle.com