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

How to gather histograms on a column of a table

Use the METHOD_OPT directive in DBMS_STATS.GATHER_TABLE_STATS to collect histograms for column ARTICLE_NAME in table SALES_Q1_RESULTS. In this example, 6 buckets are defined.
Check the view DBA_TAB_COLUMNS to confirm whether or not there are histograms collected on the column:

SELECT  NUM_DISTINCT,DENSITY,NUM_NULLS,NUM_BUCKETS,HISTOGRAM
FROM    DBA_TAB_COLUMNS 
WHERE   TABLE_NAME = 'SALES_Q1_RESULTS' 
AND     COLUMN_NAME = 'ARTICLE_NAME';

NUM_DISTINCT	DENSITY	NUM_NULLS	NUM_BUCKETS	HISTOGRAM
0	0	0	0	NONE

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS (OWNNAME => 'SH', TABNAME => 'SALES_Q1_RESULTS', METHOD_OPT => 'FOR COLUMNS SIZE 6 ARTICLE_NAME');
END;
/

SELECT  NUM_DISTINCT,DENSITY,NUM_NULLS,NUM_BUCKETS,HISTOGRAM
FROM    DBA_TAB_COLUMNS 
WHERE   TABLE_NAME = 'SALES_Q1_RESULTS' 
AND     COLUMN_NAME = 'ARTICLE_NAME';

NUM_DISTINCT	DENSITY	NUM_NULLS	NUM_BUCKETS	HISTOGRAM
7	0.166666666666667	0	6	HEIGHT BALANCED

Read more in Oracles Documentation

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".