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

How to list users in a postgreSQL database

Use the psql meta-command "\du" or "\du+" to list the users (or roles as they are called in PostgreSQL):

psql
psql (11.2)
Type "help" for help.

postgres=# \du
                                            List of roles
     Role name     |                         Attributes                         |      Member of
-------------------+------------------------------------------------------------+---------------------
 music             | Create DB                                                 +| {}
                   | Password valid until 2019-02-10 00:00:00+01                |
 postgres          | Superuser, Create role, Create DB, Replication, Bypass RLS+| {}
                   | Password valid until 2019-01-16 00:00:00+01                |
 resource          | Cannot login                                               | {}
 superuser         | Superuser                                                 +| {}
                   | Password valid until 2019-03-02 00:00:00+01                |
 vegard            | Password valid until 2019-03-02 00:00:00+01                | {}

Sources: List schemas and List users
from dataedo.com

How to check if fine-grained auditing (FGA) is enabled in your database

SELECT  POLICY_NAME,ENABLED,AUDIT_TRAIL
FROM  DBA_AUDIT_POLICIES;

Result:

POLICY_NAME	ENABLED	AUDIT_TRAIL
MY_FGAPOLICY_1	YES	DB+EXTENDED
MY_FGAPOLICY_2	YES	DB+EXTENDED
MY_FGAPOLICY_3	YES	DB+EXTENDED
MY_FGAPOLICY_4	YES	DB+EXTENDED

Find audit information about dropped users

If you have created your database with dbca, or have manually run the secconf.sql script after your database was created, the "DROP USER" is audited by default in Oracle 12c.
This is a part of what Oracle calls default auditing which I have written about in another post.

Here is how you can find information about dropped users:

select os_username,username,userhost,timestamp,obj_name,action_name,priv_used
from dba_audit_trail 
where action_name='DROP USER'
and to_char(cast ( timestamp as date),'dd.mm.yyyy') > '08.02.2019'
AND OBJ_NAME IN ('JIM','DWIGHT')
order by timestamp desc;

Result:

OS_USERNAME	USERNAME	USERHOST	TIMESTAMP	OBJ_NAME	ACTION_NAME	PRIV_USED
root	SYSTEM	hostname1.mydomain.com	12.02.2019 11:31:05	JIM	DROP USER	DROP USER
root	SYSTEM	hostname1.mydomain.com	12.02.2019 11:28:10	DWIGHT	DROP USER	DROP USER

What is the 100046 trace event anyway?

The 100046 event is a Process Trace event. These trace certain system operations continuously until the tracing is canceled. The results are written to a trace files.

When do I start running 10046 events, then?

* When a process is having problems that can be identified, and you can control which session (or sessions) need to be traced without impacting other things.
* When users can clearly point to processes that need help, and you cannot clearly see what it is other means (statspack, v$sqlarea, etc)


Source: Burlesson Consulting