How to monitor redo apply in a snapshot standby database

SELECT PROCESS, STATUS, THREAD#, SEQUENCE#,  BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;

Sample output (abbriviated):

PROCESS   STATUS          THREAD#  SEQUENCE#     BLOCK#     BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
ARCH      CLOSING               1         30   18288640        750
ARCH      CLOSING               1     232528   17645568       1637
ARCH      CLOSING               1     232550          1        342
ARCH      CONNECTED             0          0          0          0
RFS       RECEIVING             1     232559     976898       2048
RFS       IDLE                  0          0          0          0
...
60 rows selected.

Tested with Oracle version 12.1.0.2.0.
Documentation for the view v$managed_standby is here
From Oracle version 12.2.0.1 and onwards, this view is deprecated. Use V$DATAGUARD_PROCESS instead, for example

SELECT  NAME,TYPE,ROLE,ACTION,CLIENT_ROLE,STOP_STATE FROM V$DATAGUARD_PROCESS;

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