How to find audit information about SELECT statements

This article is based on setup in a database running classical auditing in version 18.6.0.0.0, but should be possible to use in older versions, too.

After you have verified that your table is indeed being audited, you can move on to see exactly what was executed at a specific point in time.

To populate the columns SQL_BIND and SQL_TEXT, you need to make sure you gather extended auditing information in your database.

alter system set audit_trail=db, extended scope=spfile;
shutdown immediate 
startup

Now you can use the following query to find

SELECT TIMESTAMP, OS_USERNAME, OBJ_NAME,USERHOST, SESSIONID, USERNAME,ACTION_NAME, RETURNCODE,SQL_BIND,SQL_TEXT
FROM   DBA_AUDIT_TRAIL
WHERE  OWNER = 'SCOTT'
ORDER  BY TIMESTAMP DESC;

TIMESTAMP	OS_USERNAME	OBJ_NAME	USERHOST	SESSIONID	USERNAME	ACTION_NAME	RETURNCODE	SQL_BIND	SQL_TEXT
07.08.2019	JOHN	EMP	jonsPC	174335	DBAADMIN	SELECT	0		SELECT * FROM SCOTT.EMP
06.08.2019	LISA	EMP	lisaspc	171886	LISA	SELECT	0
05.08.2019	FRED	DEPT	fredsPC	141131	SCOTT	SELECT	0

How to put a mounted standby database in Real-Time Query mode

The database is currently mounted as a normal physical standby database:

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
MOUNTED

Cancel managed recovery:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

If you use the broker, the equivalent command would be

EDIT DATABASE 'proddb01_stby1' SET STATE='APPLY-OFF';

Open the database. It will automatically open in read-only mode:

SQL> ALTER DATABASE OPEN;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ ONLY

Start managed recovery again:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

Database altered.

If you use the broker, the equivalent command would be

EDIT DATABASE 'proddb01_stby1' SET STATE='APPLY-ON';

Check the database status:

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ ONLY WITH APPLY

Before this change, the broker status showed:

DGMGRL> show database proddb01_stby1

Database - proddb01_stby1

Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 9 minutes 31 seconds (computed 1 second ago)
Apply Lag: (unknown)
Average Apply Rate: (unknown)
Real Time Query: OFF

After the change:

DGMGRL> show database proddb01_stby1

Database - proddb01_stby1

Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: 1 second (computed 0 seconds ago)
Average Apply Rate: 2.55 MByte/s
Real Time Query: ON

How to work around RMAN-08120: warning: archived log not deleted, not yet applied by standby

Problem scenario:

* You have a primary database with two standby databases.
* One of them is functional, the other one is about to be rebuilt.
* Archived redo logs are piling up on the primary.

You have already set the log_archive_dest_n to DEFERRED on the primary site, and this will prevent logs from being shipped to the standby database that is about to be rebuilt.

The reason for this is explained in Doc ID 1380368.1 on My Oracle Support: Oracle will, by default, also consider deferred locations, before deleting archive logs from the primary.

Solution:
This can be changed by setting a hidden parameter, _deferred_log_dest_is_valid, from TRUE to FALSE.

To verify that this worked as intended, I listed a couple of the archivelogs on the primary:

RMAN> list archivelog sequence between 110880 and 110881;

List of Archived Log Copies for database with db_unique_name PRODDB01
=====================================================================

Key     Thrd Seq     S Low Time
------- ---- ------- - ---------
173550  1    110880  A 19-JUL-19
        Name: /fra/proddb01/archivelog/2019_07_19/o1_mf_1_110880__2rnqx83t_.arc

173551  1    110881  A 19-JUL-19
        Name: /fra/proddb01/archivelog/2019_07_19/o1_mf_1_110881__2rrrp5ml_.arc

Now try to delete them from the primary server:

RMAN> delete archivelog sequence between 110880 and 110881;

allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=78 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=723 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=14 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=295 device type=DISK
RMAN-08120: warning: archived log not deleted, not yet applied by standby
archived log file name=/fra/proddb01/archivelog/2019_07_19/o1_mf_1_110880__2rnqx83t_.arc thread=1 sequence=110880
RMAN-08120: warning: archived log not deleted, not yet applied by standby
archived log file name=/fra/proddb01/archivelog/2019_07_19/o1_mf_1_110881__2rrrp5ml_.arc thread=1 sequence=110881

Oracle doesn't allow you to delete them, since they haven't been applied to all standby destinations.

To work around this, set the hidden parameter:

alter system set "_deferred_log_dest_is_valid" = FALSE scope=both;

Try deleting them once more shows that Oracle now allows you to do so:

RMAN>  delete archivelog sequence between 110880 and 110881;

List of Archived Log Copies for database with db_unique_name proddb01
=====================================================================

Key     Thrd Seq     S Low Time
------- ---- ------- - ---------
173550  1    110880  A 19-JUL-19
        Name: /fra/proddb01/archivelog/2019_07_19/o1_mf_1_110880__2rnqx83t_.arc

173551  1    110881  A 19-JUL-19
        Name: /fra/proddb01/archivelog/2019_07_19/o1_mf_1_110881__2rrrp5ml_.arc


Do you really want to delete the above objects (enter YES or NO)? YES
deleted archived log
archived log file name=/fra/proddb01/archivelog/2019_07_19/o1_mf_1_110880__2rnqx83t_.arc RECID=173550 STAMP=1014076575
Deleted 1 objects

deleted archived log
archived log file name=/fra/proddb01/archivelog/2019_07_19/o1_mf_1_110881__2rrrp5ml_.arc RECID=173551 STAMP=1014076710
Deleted 1 objects

Use

delete noprompt archivelog sequence between 110880 and 110881;

to avoid having to confirm the delete operation.

How to list all objects in a database in Postgres

Use the psql meta-command \d+

psql -h myserver.mydomain.com -U music musicdb


music=> \d+
                                           List of relations
     Schema     |              Name              |   Type   |     Owner      |    Size    | Description
----------------+--------------------------------+----------+----------------+------------+-------------
 public         | pg_stat_statements             | view     | postgres       | 0 bytes    |
 music          | music_seq1                     | sequence | music          | 8192 bytes |
 music          | music_seq2                     | sequence | music          | 8192 bytes |
 music          | table1                         | table    | music          | 16 kB      |
 music          | music_seq3                     | sequence | music          | 8192 bytes |
 music          | table2                         | table    | music          | 3723 MB    |
 music          | table3                         | sequence | music          | 8192 bytes |
 music          | table4                         | table    | music          | 0 bytes    |
 music          | table5                         | table    | music          | 6117 MB    |
 music          | table6                         | table    | music          | 1884 MB    |
 music          | table7                         | table    | music          | 8192 bytes |
(24 rows)

If you only want to view the tables, use "\dt" instead of "\d+"

A good source for other size-related queries is this tutorial

How to find indexes on a table in postgres

SELECT
    tablename,
    indexname,
    indexdef
FROM
    pg_indexes
WHERE
    schemaname = 'myuser'
    and tablename='mytable'
ORDER BY
    tablename,
    indexname;

tablename	indexname	indexdef
mytable	idx1	CREATE INDEX idx1 ON myschema.mytableUSING btree (col1, col2)
mytable	idx2	CREATE INDEX idx2 ON myschema.mytable USING btree (col3)

Or use the psql command \d mytable, which lists the indexes after the description of the table columns

\d mytable

 
Indexes:
    "pk_mytable" PRIMARY KEY, btree (col1)
    "idx1" btree (col1, col2)
    "idx2" btree (col3)
    "idx3" btree (col3, col4)

How to list subpartitions and their sizes

First, find the number of partitions and subpartitions in a specific tablespace:

SELECT S.TABLE_NAME, S.TABLESPACE_NAME,COUNT(DISTINCT S.PARTITION_NAME) "num partitions", COUNT(DISTINCT S.SUBPARTITION_NAME) "num subpartitions"
FROM DBA_TAB_SUBPARTITIONS S 
WHERE S.TABLE_OWNER='SCOTT'
GROUP BY S.TABLE_NAME, S.TABLESPACE_NAME;

TABLE_NAME	TABLESPACE_NAME	num partitions	num subpartitions
MYTABLE1	DATA1	75	450
MYTABLE2	DATA2	73	219
MYTABLE3	DATA1	74	222
MYTABLE4	DATA2	74	222
MYTABLE5	DATA1	81	243

For a specific table, all partitions and their subpartitions:

SELECT P.PARTITION_NAME, P.SUBPARTITION_NAME, S.SEGMENT_TYPE,S.TABLESPACE_NAME,P.COMPRESSION,P.COMPRESS_FOR, P.NUM_ROWS, P.LAST_ANALYZED,ROUND(S.BYTES/1024/1024/1024,1) "gb" 
FROM DBA_SEGMENTS S JOIN DBA_TAB_SUBPARTITIONS P
ON (S.SEGMENT_NAME = P.TABLE_NAME)
WHERE S.SEGMENT_NAME='MYTABLE'
AND   S.OWNER = 'SCOTT'
AND S.PARTITION_NAME = P.SUBPARTITION_NAME
ORDER BY P.PARTITION_NAME, P.SUBPARTITION_NAME, S.BYTES DESC;

PARTITION_NAME	SUBPARTITION_NAME	SEGMENT_TYPE	TABLESPACE_NAME	COMPRESSION	COMPRESS_FOR	NUM_ROWS	LAST_ANALYZED	gb
SYS_P177766	SYS_SUBP177763	TABLE SUBPARTITION	DATA2	DISABLED		26021384	25.06.2020 22:55:36	12
SYS_P177766	SYS_SUBP177764	TABLE SUBPARTITION	DATA2	DISABLED		21531914	26.06.2020 23:32:34	9,1
SYS_P177766	SYS_SUBP177765	TABLE SUBPARTITION	DATA2	DISABLED		0	02.03.2020 00:56:19	0
SYS_P177774	SYS_SUBP177771	TABLE SUBPARTITION	DATA2	DISABLED		285	16.07.2020 23:21:12	0
SYS_P177774	SYS_SUBP177772	TABLE SUBPARTITION	DATA1	DISABLED		259	16.07.2020 23:21:30	0

For specific subpartitions:

SELECT P.PARTITION_NAME "Subpartition name", S.PARTITION_NAME,S.SEGMENT_TYPE,S.TABLESPACE_NAME,P.COMPRESSION,P.COMPRESS_FOR, P.NUM_ROWS, P.LAST_ANALYZED,ROUND(S.BYTES/1024/1024/1024,1) "gb" 
FROM DBA_SEGMENTS S JOIN DBA_TAB_SUBPARTITIONS P
ON (S.SEGMENT_NAME = P.TABLE_NAME)
AND   S.OWNER = 'SCOTT'
AND S.PARTITION_NAME = P.SUBPARTITION_NAME
AND P.SUBPARTITION_NAME in ('SYS_SUBP177773','SYS_SUBP177763','SYS_SUBP177764','SYS_SUBP177765');

PARTITION_NAME	Subpartition name	SEGMENT_TYPE	TABLESPACE_NAME	COMPRESSION	COMPRESS_FOR	NUM_ROWS	LAST_ANALYZED	gb
SYS_P177766	SYS_SUBP177763	TABLE SUBPARTITION	DATA1	DISABLED		26021384	25.06.2020 22:55:36	12
SYS_P177766	SYS_SUBP177764	TABLE SUBPARTITION	DATA1	DISABLED		21531914	26.06.2020 23:32:34	9,1
SYS_P177766	SYS_SUBP177765	TABLE SUBPARTITION	DATA1	DISABLED		0	02.03.2020 00:56:19	0
SYS_P177774	SYS_SUBP177773	TABLE SUBPARTITION	DATA1	DISABLED		0	02.03.2020 00:56:20	0

For a specific partition:

SELECT S.PARTITION_NAME "Subpartition name", S.SEGMENT_TYPE,S.TABLESPACE_NAME,P.COMPRESSION,P.COMPRESS_FOR, P.NUM_ROWS, P.LAST_ANALYZED,ROUND(S.BYTES/1024/1024/1024,1) "gb" 
FROM DBA_SEGMENTS S JOIN DBA_TAB_SUBPARTITIONS P
ON (S.SEGMENT_NAME = P.TABLE_NAME)
WHERE S.SEGMENT_NAME='MYTABLE'
AND   S.OWNER = 'SCOTT'
AND S.PARTITION_NAME = P.SUBPARTITION_NAME
AND P.PARTITION_NAME ='SYS_P14675'
ORDER BY S.BYTES DESC;

Subpartition name	SEGMENT_TYPE	TABLESPACE_NAME	COMPRESSION	COMPRESS_FOR	NUM_ROWS	LAST_ANALYZED	gb
SYS_SUBP14672	TABLE SUBPARTITION	DATA1	DISABLED		127103150	29.06.2019	51,4
SYS_SUBP14673	TABLE SUBPARTITION	DATA1	DISABLED		89059917	29.06.2019	34,1
SYS_SUBP14674	TABLE SUBPARTITION	DATA1	DISABLED		0	29.06.2019	0

For a specific tablespace:

SELECT S.PARTITION_NAME "Subpartition name",S.SEGMENT_TYPE,S.TABLESPACE_NAME,P.COMPRESSION,P.COMPRESS_FOR, P.NUM_ROWS, P.LAST_ANALYZED,ROUND(S.BYTES/1024/1024/1024,1) "gb" 
FROM DBA_SEGMENTS S JOIN DBA_TAB_SUBPARTITIONS P
ON (S.SEGMENT_NAME = P.TABLE_NAME)
WHERE S.OWNER = 'SCOTT'
AND S.TABLESPACE_NAME='DATA1'
AND S.PARTITION_NAME = P.SUBPARTITION_NAME
ORDER BY S.BYTES DESC; 

Subpartition name	SEGMENT_TYPE	TABLESPACE_NAME	COMPRESSION	COMPRESS_FOR	NUM_ROWS	LAST_ANALYZED	gb
SYS_SUBP27395	TABLE SUBPARTITION	SCOTT	DISABLED		100835414	27.10.2018 19:06:53	44,6
SYS_SUBP29902	TABLE SUBPARTITION	SCOTT	DISABLED		88951135	27.10.2018 21:23:24	41,6
SYS_SUBP29332	TABLE SUBPARTITION	SCOTT	DISABLED		83142250	27.10.2018 23:40:00	38,3
SYS_SUBP29395	TABLE SUBPARTITION	SCOTT	DISABLED		78610455	27.10.2018 19:00:05	37,1
SYS_SUBP28115	TABLE SUBPARTITION	SCOTT	DISABLED		75810738	07.07.2018 15:54:52	35,7

How to avoid RMAN-10015 when using sql-directives in an RMAN script

When attempting to use an sql directive in RMAN, you will sometimes be instructed to use the following syntax:

sql "alter session set events ' '1110 trace name errorstack level 3' '";

This will throw an error upon execution:

RMAN-03009: failure of sql command on default channel at 08/01/2019 11:20:02
RMAN-10015: error compiling PL/SQL program

Solution: do not use white spaces between the single quotes.
This will work:

sql "alter session set events ''1110 trace name errorstack level 3'' ";