How to explain a query in PostgreSQL

If you are using partitioned tables, make sure you have enabled partition pruning:

SET enable_partition_pruning to on;

The explain statement is simple enough:

explain select * from documents where dokumenttype='SUBPOENA';

                            QUERY PLAN
---------------------------------------------------------------------
 Append  (cost=0.00..1.02 rows=1 width=774)
   ->  Seq Scan on P_SUBPOENA  (cost=0.00..1.01 rows=1 width=774)
         Filter: ((documenttype)::text = 'SUBPOENA'::text)
(3 rows)

Since this is the LIST-partitioned table outlined in this post, I know the optimizer picked the correct partition for my predicate.

For the LIST-range subpartitioned table outlined in this post, I get the following query plan:

explain select * from orders where country_code='se' and order_total between 4000 and 4999;
                                                           QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------
 Append  (cost=0.00..28.21 rows=1 width=50)
   ->  Seq Scan on large_orders_se  (cost=0.00..28.20 rows=1 width=50)
         Filter: ((order_total >= '4000'::numeric) AND (order_total <= '4999'::numeric) AND ((country_code)::text = 'se'::text))
(3 rows)

Regarding parallelism, the documentation tells you to watch for the terms Gather or Gather Merge Node in the query plan:
An example:

"Subquery Scan on andre  (cost=1000.00..10024950.78 rows=294885 width=84)"
"  ->  Gather  (cost=1000.00..10013892.60 rows=294885 width=64)"
"        Workers Planned: 2"
"        ->  ProjectSet  (cost=0.00..9983404.10 rows=29488500 width=64)"
"              ->  ProjectSet  (cost=0.00..9830063.90 rows=12286900 width=128)"

How to create a LIST partitioned table in PostgreSQL

Create the table:

CREATE TABLE DOCUMENTS(
  DOC_ID                      INTEGER  NOT NULL,
  LEGAL_ENTITY                INTEGER       NULL,
  CREATED_DT                  DATE      NOT NULL,
  REGION                      VARCHAR(30)   NULL,
  DOCUMENTTYPE                VARCHAR(100)  NULL,
  DOCUMENTNAME                VARCHAR(1000) NULL
)
PARTITION BY LIST (DOCUMENTTYPE);

Create a couple of partitions, including a default partition:

CREATE TABLE P_SUBPOENAS PARTITION OF  DOCUMENTS FOR VALUES IN  ('SUBPOENA');
CREATE TABLE P_AFFIDAVITS PARTITION OF DOCUMENTS FOR VALUES IN  ('AFFIDAVIT');
CREATE TABLE P_MEMORANDOMS PARTITION OF DOCUMENTS FOR VALUES IN ('MEMORANDOM');
CREATE TABLE P_DEFAULT PARTITION OF DOCUMENTS DEFAULT;

To add a primary key to a partitioned table, read this post

If your LIST-partitioned table would benefit from sub-partitioning, read this post

What exactly is meant by "global statistics" on a partitioned table?

From the whitepaper "Understanding Optimizer Statistics":

"When dealing with partitioned tables the Optimizer relies on both the statistics for the entire table
(global statistics) as well as the statistics for the individual partitions (partition statistics) to select a
good execution plan for a SQL statement. If the query needs to access only a single partition, the
Optimizer uses only the statistics of the accessed partition. If the query access more than one partition,
it uses a combination of global and partition statistics."

How to manuall purge your unified auditing audit trail

This is how I purged my unified audit trail on an Oracle 12.2 test instance.

First, check the LAST_ARCHIVE_TS:

SELECT * FROM DBA_AUDIT_MGMT_LAST_ARCH_TS;

If you want to keep some of your audit records, set a timestamp before which all records should be purged. In this case, I want to purge everything, so I set it to SYSTIMESTAMP:

BEGIN
  DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP (
    AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
    LAST_ARCHIVE_TIME => SYSTIMESTAMP);
END;
/

Purge the audit trail:

BEGIN
  DBMS_AUDIT_MGMT.clean_audit_trail(
   audit_trail_type        => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
   use_last_arch_timestamp => TRUE);
END;
/

If you want to purge all records, run the same procedure but without the "use_last_arch_timestamp" directive:

BEGIN
  DBMS_AUDIT_MGMT.clean_audit_trail(
   audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED
   );
END;
/

The dbms_audit_mgmt is documented here

How to create a unique constraint with an existing index (the USING-clause)

Create the index:

CREATE UNIQUE INDEX MYTABLE_IDX1 ON MYTABLE
(ID,CATEGORY)
LOCAL
TABLESPACE USERS
ONLINE;

Create a unique constraint:

ALTER TABLE MYTABLE ADD (
CONSTRAINT UK_MYTABLE
UNIQUE (ID,CATEGORY)
USING INDEX MYTABLE_IDX1
);

For a primary key constraint the syntax is similar.
In this example, an index named MYTABLE_PK will be automatically created on the fly:

ALTER TABLE MYTABLE ADD (
CONSTRAINT MYTABLE_PK
PRIMARY KEY (ID)
USING INDEX);

A workaround for ORA-54033: column to be modified is used in a virtual column expression

Applicable for Oracle 12.1.0.2.

My customer reports that an attempt to execute a simple DDL statement failed:

ALTER TABLE mytable
MODIFY (COL2 DATE );

The error thrown was: ORA-54033: column to be modified is used in a virtual column expression.

Quite correctly, a look at the table revealed a virtual column:

set lines 200
col owner format a15
col table_name format a30
col column_name format a30
col data_default format a60

select owner,table_name,column_name, data_Default
from dba_tab_cols
where table_name='MYTABLE'
and hidden_column='YES';

Output:

OWNER           TABLE_NAME      COLUMN_NAME                    DATA_DEFAULT                                                                    
--------------- --------------- ------------------------------ -----------------------------------------------
SCOTT           MYTABLE         SYS_STSC13O20ML6_5OD25YOF16STK SYS_OP_COMBINED_HASH("COL1","COL2","COL3","COL4")             
1 row selected.

You can also query the DBA_STAT_EXTENSION to get similar information:

set lines 200
col extension_name format a30
col extension format a50

SELECT EXTENSION_NAME, EXTENSION,creator,droppable
FROM DBA_STAT_EXTENSIONS
WHERE TABLE_NAME='MYTABLE';

Output:

EXTENSION_NAME                 EXTENSION                      CREATOR DROPPABLE
------------------------------ ------------------------------ ------- ---------
SYS_STSC13O20ML6_5OD25YOF16STK ("COL1","COL2","COL3","COL4")  SYSTEM  YES      
1 row selected.

So in order to modify the existing column, the extended statistics will have to be dropped and recreated:

set lines 200
set serveroutput on
set timing on

BEGIN
  dbms_stats.drop_extended_stats('SCOTT', 'MYTABLE', '(COL1,COL2,COL3,COL4)');
END;
/

ALTER TABLE mytable
MODIFY (COL2 DATE );

select dbms_stats.create_extended_stats('SCOTT', 'MYTABLE', '(COL1,COL2,COL3,COL4)') 
from dual;

How to audit specific users' actions on other schemas' objects

This was executed on Oracle 12.2

To audit actions by users on another schema's objects, execute this:

audit insert table,update table,select table, delete table by scott by access; 

Verify that the auditing is in place:

SELECT audit_option,success,failure
FROM DBA_STMT_AUDIT_OPTS
WHERE USER_NAME='SCOTT';

You can see that the actions we want to audit for user SCOTT is indeed set:

audit_option	success	failure
SELECT TABLE	BY ACCESS	BY ACCESS
INSERT TABLE	BY ACCESS	BY ACCESS
UPDATE TABLE	BY ACCESS	BY ACCESS
DELETE TABLE	BY ACCESS	BY ACCESS

This will generate audit entries whenever scott tries to perform queries or DML on other schemas' tables.

In my case, I tested this by logging on to the database as user scott, and executed a delete statement against a test table called "yy", owned by the SALES schema:

sqlplus / as sysdba
grant create synonym to scott;

sqlplus scott/tiger@testdb01
SQL> create synonym yy for sales.yy;

Synonym created.

SQL> delete from yy where antall in (1,2);

7 rows deleted.

SQL> commit;

Commit complete.

This particular database writes its audit information to the operating system, as indicated by the audit_trail parameter:

sqlplus / as sysdba
SQL> show parameter audit_trail

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_trail                          string      OS

My audit_file_dest resides inside the ADR directory structure:

show parameter audit_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- --------------------------------
audit_file_dest                      string      /u01/oracle/admin/testdb01/adump

Checking the /u01/oracle/admin/testdb01/adump I found the file and it showed:

Thu Apr  4 16:32:56 2019 +02:00
LENGTH: "276"
SESSIONID:[8] "14060442" 
ENTRYID:[2] "67" 
STATEMENT:[2] "14" 
USERID:[6] "SCOTT" 
USERHOST:[18] "MYDOMAIN\MYPC123" 
TERMINAL:[12] "MYPC123" 
ACTION:[1] "7" 
RETURNCODE:[1] "0" 
OBJ$CREATOR:[11] "SALES" 
OBJ$NAME:[2] "YY" 
OS$USERID:[6] "vegardk" 
DBID:[10] "2425899399"

The action codes are important. Number 7 indicates a DELETE statement, which is what I expected to find.
The entire list can be found here