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

How to create a RANGE partitioned table in PostgreSQL

Create the table:

create table album
(
album_id integer generated always as identity,
album_name varchar(40),
genre varchar(20),
label varchar(40),
release_year date
)
partition by range( extract (year from release_year) );

Create a couple of partitions, including a default partition:

create table albums_1974
partition of album
for values from ( '1974' ) to ('1975'); 

create table albums_1979
partition of album
for values from ( '1979' ) to ('1980'); 

create table albums_default 
partition of album DEFAULT;

Another example using a timestamp column as a partition key:

create table shared_docs (
 share_id character      varying(100),
 docid character varying(100),
 identificator character  varying(100),
 package character varying(100),
 fromd timestamp without time zone,
 tod timestamp without time zone,
 rev_year character     varying(100),
 creationtime timestamp without time zone,
 agreedparty character varying(300)
) partition by range (creationtime);

Create the partitions:

create table docs_jan2022 partition of shared_docs for values from    ('2022-01-01 00:00:00') to ('2022-02-01 00:00:00');
create table docs_feb2022 partition of shared_docs for values from    ('2022-02-01 00:00:00') to ('2022-03-01 00:00:00');
create table docs_march2022 partition of shared_docs for values from   ('2022-03-01 00:00:00') to ('2022-04-01 00:00:00');

Yet another example using an integer column:

CREATE TABLE mytable
(
    key text COLLATE pg_catalog."default" NOT NULL,
    outdata jsonb,
    partno integer,
    koffset bigint,
    handled_at timestamp with time zone,
    inserted_at timestamp with time zone NOT NULL DEFAULT now(),
    kyear integer,
    nk boolean NOT NULL,
    CONSTRAINT mytable_pkey PRIMARY KEY (id,kyear),
    CONSTRAINT key_unique_int UNIQUE (key,kyear)
)
partition by range (kyear);

Create the partitions:

create table mytable_2021
partition of mytable
for values from (2021) to (2022);

create table mytable_2022
partition of mytable
for values from (2022) to (2023);

How to use dbms_session to set client_identifier in your session

Logged in as user scott, I set the following in my session:

EXEC DBMS_SESSION.SET_IDENTIFIER (client_id=>'vegardk');

Verify that your session info is set:

SELECT SYS_CONTEXT( 'USERENV', 'CLIENT_IDENTIFIER' ) "client identifier", SYS_CONTEXT( 'USERENV', 'CURRENT_USER' ) "current user" 
FROM DUAL;

client identifier	current user
vegardk	SCOTT

The same information can be found in v$session, columnn CLIENT_IDENTIFIER.

There is also another column in v$session called CLIENT_INFO.
A value for this column can be generated by calling a different procedure, dbms_application_info.set_client_info.

For example:

exec dbms_application_info.set_client_info('Execute summary');

Check v$session:

SELECT USERNAME,CLIENT_IDENTIFIER,CLIENT_INFO
FROM V$SESSION 
WHERE USERNAME='SCOTT';

Result:

USERNAME	CLIENT_IDENTIFIER	CLIENT_INFO
SCOTT	vegardk	Execute summary

A good way to pick up up any relevant session information already set is to use NVL:

SELECT NVL(SYS_CONTEXT( 'USERENV', 'CLIENT_IDENTIFIER' ), SYS_CONTEXT( 'USERENV', 'CURRENT_USER' )) 
FROM DUAL;

If client_identifier is set, we pick up that value. If not, we use the current_user, which is always set.
This could also be used as a default value for a column definition:

  CREATE TABLE T1 (
  ...
  LAST_CHANGED_BY     VARCHAR2(30 CHAR)         DEFAULT NVL(SYS_CONTEXT( 'USERENV', 'CLIENT_IDENTIFIER' ), SYS_CONTEXT( 'USERENV', 'CURRENT_USER' )) NULL
 );

How to create a range-list partitioned table, using interval partitioning and subpartition template

Note: I am placing the subpartitions, the initial partition, the table definition itself, and the LOB segments, in different tablespaces simply to illustrate this possiblity.

CREATE TABLE COUNTRY_TABLE
(
  ID                  VARCHAR2(36 CHAR)         NULL,
  DYEAR               NUMBER(4)                 NULL,
  COUNTRY             VARCHAR2(50 CHAR)         NULL,
  CREATED             TIMESTAMP(6)              DEFAULT systimestamp  NULL,
  CREATED_BY          VARCHAR2(30 CHAR)         DEFAULT NVL(SYS_CONTEXT( 'USERENV', 'CLIENT_IDENTIFIER' ), SYS_CONTEXT( 'USERENV', 'CURRENT_USER' )) NULL,
  LAST_CHANGED        TIMESTAMP(6)              DEFAULT systimestamp  NULL,
  LAST_CHANGED_BY     VARCHAR2(30 CHAR)         DEFAULT NVL(SYS_CONTEXT( 'USERENV', 'CLIENT_IDENTIFIER' ), SYS_CONTEXT( 'USERENV', 'CURRENT_USER' )) NULL,
  VERSION             NUMBER(5)                 DEFAULT 1 NULL,
  DOCUMENT            CLOB                      NULL,
  PERIOD AS (
                CAST ( CREATED AS DATE)
             ) VIRTUAL
)
LOB (DOCUMENT) STORE AS SECUREFILE (
 TABLESPACE  scott_data
)
PARTITION BY RANGE(PERIOD) INTERVAL ( NUMTOYMINTERVAL(1,'MONTH') )
SUBPARTITION BY LIST (COUNTRY)
 SUBPARTITION TEMPLATE
(
 SUBPARTITION SP_GERMAN VALUES ('GERMANY','SWITZERLAND','AUSTRIA') TABLESPACE USERS
,SUBPARTITION SP_FRENCH VALUES ('FRANCE','BELGIUM','LUXENBOURGH')  TABLESPACE SCOTT_DATA
,SUBPARTITION SP_SCANDI VALUES ('NORWAY','SWEDEN','DENMARK') TABLESPACE USERS
,SUBPARTITION SP_ENGLISH VALUES ('ENGLAND', 'WALES', 'SCOTLAND','IRELAND') TABLESPACE SCOTT_DATA
,SUBPARTITION SP_DEFAULT VALUES (default) TABLESPACE USERS
)
(
PARTITION PRE_2018 VALUES LESS THAN (TO_DATE('2018-01','YYYY-MM') ) TABLESPACE SCOTT_DATA
)
TABLESPACE USERS
ROW STORE COMPRESS ADVANCED
;

Analyze the table quickly;

EXEC DBMS_STATS.GATHER_TABLE_STATS (OWNNAME=>'SCOTT', TABNAME=>'COUNTRY_TABLE',GRANULARITY=>'AUTO');

After creation, the DD confirms the default partition with 5 subpartitions were created:

PARTITION_NAME	SUBPARTITION_NAME	NUM_ROWS	LAST_ANALYZED
PRE_2018	PRE_2018_SP_GERMAN	0	07.03.2019 10:41:21
PRE_2018	PRE_2018_SP_FRENCH	0	07.03.2019 10:41:21
PRE_2018	PRE_2018_SP_SCANDI	0	07.03.2019 10:41:21
PRE_2018	PRE_2018_SP_ENGLISH	0	07.03.2019 10:41:21
PRE_2018	PRE_2018_SP_DEFAULT	0	07.03.2019 10:41:21

Let's insert an "old" record, which sets the column CREATED to 2016-01:

INSERT INTO COUNTRY_TABLE (ID,DYEAR,COUNTRY,VERSION,CREATED)
VALUES ('8268b2c0-a526-11e8-8030-005056837631', 2017,'FRANCE',1, TO_TIMESTAMP('2016-01','yyyy-mm') );

Analyze the table with dbms_stats again, and we see that the PRE_2018 subpartition for countries with French language, now has one row:

PARTITION_NAME	SUBPARTITION_NAME	NUM_ROWS	LAST_ANALYZED
PRE_2018	PRE_2018_SP_GERMAN	0	07.03.2019 10:42:20
PRE_2018	PRE_2018_SP_FRENCH	1	07.03.2019 10:42:20
PRE_2018	PRE_2018_SP_SCANDI	0	07.03.2019 10:42:20
PRE_2018	PRE_2018_SP_ENGLISH	0	07.03.2019 10:42:20
PRE_2018	PRE_2018_SP_DEFAULT	0	07.03.2019 10:42:20

The rows was inserted into the PRE_2018_SP_FRENCH subpartition, as expected.

Now, let's insert a value which is greater than 2018-01, which I specified as the high value for my initial partition, called "PRE_2018":

-- Setting the created column to January 2ond, 2018:
INSERT INTO COUNTRY_TABLE (ID,DYEAR,COUNTRY,VERSION,CREATED)
VALUES ('8268b2c0-a526-11e8-8030-005056837631', 2018,'FRANCE',1, TO_TIMESTAMP('2018-01-02','YYYY-MM-DD') );
COMMIT;

This creates 4 new subpartitions with system generated names, with one row inserted into one of these:

   
PARTITION_NAME
   
SUBPARTITION_NAME
   
NUM_ROWS
   
LAST_ANALYZED
   
    
SYS_P2187
     
SYS_SUBP2182
     
0
     
07.03.2019 10:56:32
   
    
SYS_P2187
     
SYS_SUBP2183
     
1
     
07.03.2019 10:56:32
   
    
SYS_P2187
     
SYS_SUBP2184
     
0
     
07.03.2019 10:56:32
   
    
SYS_P2187
     
SYS_SUBP2185
     
0
     
07.03.2019 10:56:32
   
    
SYS_P2187
     
SYS_SUBP2186
     
0
     
07.03.2019 10:56:32
   
    
PRE_2018
     
PRE_2018_SP_GERMAN
     
0
     
07.03.2019 10:56:32
   
    
PRE_2018
     
PRE_2018_SP_FRENCH
     
1
     
07.03.2019 10:56:32
   
    
PRE_2018
     
PRE_2018_SP_SCANDI
     
0
     
07.03.2019 10:56:32
   
    
PRE_2018
     
PRE_2018_SP_ENGLISH
     
0
     
07.03.2019 10:56:32
   
    
PRE_2018
     
PRE_2018_SP_DEFAULT
     
0
     
07.03.2019 10:56:32
   

Another row for January 2018 will go into the same subpartition:

-- Setting created to January 3rd, 2018:
INSERT INTO COUNTRY_TABLE (ID,DYEAR,COUNTRY,VERSION,CREATED)
VALUES ('8268b2c0-a526-11e8-8030-005056837631', 2018,'FRANCE',1, TO_TIMESTAMP('2018-01-03','YYYY-MM-DD') );

PARTITION_NAME	SUBPARTITION_NAME	NUM_ROWS	LAST_ANALYZED
SYS_P2187	SYS_SUBP2183	2	07.03.2019 10:59:06

When inserting rows for a new month, addition 4 new subpartitions will be created, etc:

-- February 1st, 2018
INSERT INTO COUNTRY_TABLE (ID,DYEAR,COUNTRY,VERSION,CREATED)
VALUES ('8268b2c0-a526-11e8-8030-005056837631', 2018,'FRANCE',1, TO_TIMESTAMP('2018-02-01','YYYY-MM-DD') );

PARTITION_NAME	SUBPARTITION_NAME	NUM_ROWS	LAST_ANALYZED
SYS_P2205	SYS_SUBP2200	0	07.03.2019 11:00:47
SYS_P2205	SYS_SUBP2201	1	07.03.2019 11:00:47
SYS_P2205	SYS_SUBP2202	0	07.03.2019 11:00:47
SYS_P2205	SYS_SUBP2203	0	07.03.2019 11:00:47
SYS_P2205	SYS_SUBP2204	0	07.03.2019 11:00:47