A function that shows schema size in PostgreSQL

Thanks to Emanuel Calvo for publishing this procedure.

I put it in a script:

SET search_path TO public;
SHOW search_path;
CREATE OR REPLACE FUNCTION pg_schema_size(text) RETURNS BIGINT AS $$
SELECT SUM(pg_total_relation_size(quote_ident(schemaname) || '.' || quote_ident(tablename)))::BIGINT FROM pg_tables WHERE schemaname = $1
$$ LANGUAGE SQL;

and then executed it:

]$ psql
psql (11.7)
Type "help" for help.


postgres=# \i cre_funk.sql
SET
 search_path
-------------
 public
(1 row)

CREATE FUNCTION

When this is done, it can be executed in any database part of the postgres instance, as long as you connect to the right one first:

postgres=# \connect sales
You are now connected to database "sales" as user "postgres".
sales=# select pg_size_pretty(pg_schema_size('sales_archive'));
 pg_size_pretty
----------------
 28 MB
(1 row)

Av en eller annen grunn får vi nå: HINT: No function matches the given name and argument types. You might need to add explicit type casts. når vi kjører den i postgres versjoner > 11

Comparing users, schemas, instances and databases between Oracle and PostgreSQL

If you have to deal with both Oracle and PostgreSQL databases in your daily work, it can be confusing to deal with terms such as users, schemas and instances, since they mean different things in each software installation.

After discussing the matter with an experienced coworker, I have made the following matrix which may clarify the differences and the similarities between the two:

Oracle	PostgreSQL
One database may contain many schemas	One database may contain many schemas
A user equals a schema	A user equals a role (with login)
One instance supports one database *	One instance may support many databases
One user belongs to one database	One user belongs to the entire instance
Users belong in a global namespace together with roles and public synonyms	Users does not belong in any namespace
By default, users can only access objects in their own schema	By default, users can only access objects in the schemas that they own
For a user to access objects in a different schema, object privileges or the ANY system privilege is required	For a user to access objects in a different schema, in must be granted USAGE on the schema
A schema has a password	A schema does not have a password
A schema cannot be owned by a (another) user	A schema is owned by a user, by default the user that created the schema
A schema may contain up until 3 namespaces: one for indexes, another one for constraints and a final one for tables, views, sequences etc.	A schema is in itself a namespace. This namespace may in turn contain tables, views, indexes etc

* Exceptions to this rule are 1) Oracle RAC, where multiple instances support one database and 2) Oracle Multitenant, where one instance supports many pluggable databasess

A good tutorial regarding schemas can be found here

How to display a run-time parameter in a postgreSQL instance

Like with Oracle, PostgreSQL has an easy way to display run-time parameters:

[postgres@oric-pg01~]$ psql
psql (11.7)
Type "help" for help.

postgres=# show log_min_duration_statement;
 log_min_duration_statement
----------------------------
 -1
(1 row)

the log_min_duration_statement parameter is documented here

How to specifiy default compression for a tablespace

CREATE BIGFILE TABLESPACE star_data DATAFILE
'/oradata/proddb01/star_data.dbf' SIZE 1024M AUTOEXTEND ON NEXT 128M MAXSIZE 12T
LOGGING
DEFAULT
TABLE COMPRESS FOR OLTP
INDEX COMPRESS ADVANCED LOW
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

Note that the syntax used for tablespaces

TABLE COMPRESS FOR OLTP

is equivalent to the ROW STORE COMPRESS ADVANCED clause of CREATE TABLE.

Read more about tablespaces with default compression attributes in the 12.2 documentation

How to compress an existing table online

The ONLINE keyword makes it simple to compress an existing table online:

alter TABLE scott.emp
move new_tablespace
row store compress advanced
online;

If you omit the new_tablespace clause, Oracle will move it to the same tablespace as it is already residing in and the table will be reorganized and compressed.

How to modify a unified auditing policy to make exceptions based on login information

The audit policy had been created as follows:

create audit policy all_select_policy actions select;

Here is how you can alter an existing policy so that the policy will make an exception for session created by user "DBAADMIN"

alter audit policy ALL_SELECT_POLICY 
condition 'SYS_CONTEXT (''USERENV'',''CURRENT_USER'') NOT IN (''DBAADMIN'')' 
evaluate per Session;

Documentation can be found here
The oracle-supplied policy ORA_LOGIN_FAILURES automatically audits all failed login attempts. You can alter it to exclude certain uninteresting connections, such as for example DBSNMP, like this:

alter audit policy ORA_LOGON_FAILURES
condition 'SYS_CONTEXT (''USERENV'',''CURRENT_USER'') NOT IN (''DBSNMP'')'
evaluate per session;

For more information about the SYS_CONTEXT function, check the official 12.2 documentation.

Remember to delete archivelogs from obsolete incarnations after a flashback database operation

I have recently been supporting a customer by setting guaranteed restore points and using these to flash the database back on their signal, typically when they need to redeploy some code for their application.

It's easy to forget that every time you open the database with the RESETLOG options, you create another incarnation of your database.
Here is what my flash recovery area looked like:

cd /fra/SALESDB/archivelogs
du -sh *
300M    2020_01_30
36G     2020_02_03
248M    2020_02_04
1.5G    2020_02_05
273M    2020_02_06
284M    2020_02_07
332M    2020_02_08
3.7G    2020_02_09
510G    2020_02_11
15G     2020_02_12
1.1G    2020_02_13
386M    2020_02_14
237M    2020_02_15
3.7G    2020_02_16
14G     2020_02_17
523G    2020_02_18
1.5G    2020_02_19
208M    2020_02_20
213M    2020_02_21
239M    2020_02_22
3.6G    2020_02_23
217M    2020_02_24
293M    2020_02_25
1.5G    2020_02_26
258M    2020_02_27
261M    2020_02_28
296M    2020_02_29
3.6G    2020_03_01
507G    2020_03_02
216M    2020_03_03
13G     2020_03_04
214M    2020_03_05
211M    2020_03_06
237M    2020_03_07
3.6G    2020_03_08
288M    2020_03_09
244M    2020_03_10
1.6G    2020_03_11
16G     2020_03_12
504G    2020_03_13

The latest incarnation was created on the 12.03.2020:

RMAN> list incarnation of database;

List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       SALESDB 1626528189       PARENT  270012427233 30.01.2020 11:39:03
2       2       SALESDB 1626528189       PARENT  270012433324 30.01.2020 11:51:46
3       3       SALESDB 1626528189       PARENT  270020068677 11.02.2020 11:00:20
4       4       SALESDB 1626528189       PARENT  270020175869 13.02.2020 12:02:52
5       5       SALESDB 1626528189       PARENT  270020238995 18.02.2020 10:50:25
6       6       SALESDB 1626528189       PARENT  270020500540 02.03.2020 13:51:53
7       7       SALESDB 1626528189       CURRENT 270020646622 12.03.2020 14:18:33

This situation caused the flash recovery area to fill, since Oracle wasn't able to delete archivelogs from the previous incarnation.

I solved this by deleting archivelogs from before the last incarnation was created.

First, list the logs you want to delete:

rman target / nocatalog log=list_archlogs.txt'
RMAN> list archivelog all completed before "to_date('12.03.2020 14:18:33')";

When I check the file, these were all older logs, residing in folders dated before the last incarnation was created.

I then deleted them as follows:

delete archivelog all completed before "to_date('12.03.2020 14:18:33')";

The size of the flash recovery area has now dropped to 17% and the file listing of /fra/SALESDB/archivelogs now shows empty folders:

4.0K    2020_01_30
8.0K    2020_02_03
4.0K    2020_02_04
4.0K    2020_02_05
4.0K    2020_02_06
4.0K    2020_02_07
4.0K    2020_02_08
16K     2020_02_09
48K     2020_02_11
4.0K    2020_02_12
4.0K    2020_02_13
4.0K    2020_02_14
4.0K    2020_02_15
4.0K    2020_02_16
4.0K    2020_02_17
52K     2020_02_18
4.0K    2020_02_19
4.0K    2020_02_20
4.0K    2020_02_21
4.0K    2020_02_22
4.0K    2020_02_23
4.0K    2020_02_24
4.0K    2020_02_25
4.0K    2020_02_26
4.0K    2020_02_27
4.0K    2020_02_28
4.0K    2020_02_29
4.0K    2020_03_01
52K     2020_03_02
4.0K    2020_03_03
4.0K    2020_03_04
4.0K    2020_03_05
4.0K    2020_03_06
4.0K    2020_03_07
4.0K    2020_03_08
4.0K    2020_03_09
4.0K    2020_03_10
4.0K    2020_03_11
15G     2020_03_12
504G    2020_03_13