How to remove the schema OWF_MGR from a database

Applicable for Oracle version 12.1.0.2.0.

Workflow manager is a component used for Orchestrating of Oracle Warehouse Builder (OWB).
If you have a schema called OWF_MGR dangling in your database, it can be removed. I did it the following way:

Find the number of grants per user from OWF_MGR:

SELECT UNIQUE GRANTEE,COUNT(*)
FROM DBA_TAB_PRIVS
WHERE OWNER='OWF_MGR'
GROUP BY GRANTEE;

This query gave me to grantees: PUBLIC and the role WF_PLSQL_UI.

Revoke the privileges granted. Generate the revoke statements:

select 'revoke execute on '|| owner ||'.' || table_name || ' from WF_PLSQL_UI;' 
from  dba_tab_privs
where owner='OWF_MGR'
AND grantee='WF_PLSQL_UI';

Generate the same statements for PUBLIC.

Drop the role:

drop role WF_PLSQL_UI;

Finally, drop the user with the cascade option:

drop user owf_mgr cascade;

I found this information here

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

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

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