What is the difference between real-time apply and real-time query?

Real-time apply is the process of applying the redo in the current standby redo log file as it is being filled, in contrast to waiting for the standby redo log to be filled up. By default, apply services will wait for a standby redo log file to be archived, then apply the redo that it contains.


Real-time query is the process of applying redo redo while the physical standby database is open
This allows for read-only usage of the data which is identical to the primary database.
Note: Real-time query is licenced as the "Active Data Guard" option.


Real-time apply is documented here

Real-time query is documented here

How to list index subpartitions and their sizes

SELECT IDXSUBPART.INDEX_OWNER, IDXSUBPART.INDEX_NAME, IDXSUBPART.PARTITION_NAME, IDXSUBPART.SUBPARTITION_NAME,IDXSUBPART.TABLESPACE_NAME, ROUND(SUM(BYTES)/1024/1024/1024) "GB"
FROM   DBA_IND_SUBPARTITIONS IDXSUBPART JOIN DBA_SEGMENTS S
ON (IDXSUBPART.SUBPARTITION_NAME = S.PARTITION_NAME )
WHERE  IDXSUBPART.INDEX_NAME='SALES'
AND    IDXSUBPART.TABLESPACE_NAME='SH'
AND    S.SEGMENT_TYPE='INDEX SUBPARTITION'
GROUP BY IDXSUBPART.INDEX_OWNER, IDXSUBPART.INDEX_NAME, IDXSUBPART.PARTITION_NAME, IDXSUBPART.SUBPARTITION_NAME,IDXSUBPART.TABLESPACE_NAME;

Output:

INDEX_OWNER	INDEX_NAME	PARTITION_NAME	SUBPARTITION_NAME	TABLESPACE_NAME	GB
SH	SALES_IDX_SK1	SYS_P18153	SYS_SUBP14709	DATA1	1
SH	SALES_IDX_SK1	SYS_P18157	SYS_SUBP14831	DATA1	1
SH	SALES_IDX_SK1	SYS_P18158	SYS_SUBP14862	DATA2	25
SH	SALES_IDX_SK1	SYS_P18157	SYS_SUBP14832	DATA1	0
SH	SALES_IDX_SK1	SYS_P18142	SYS_SUBP12409	DATA2	32
SH	SALES_IDX_SK1	SYS_P18139	SYS_SUBP12414	DATA1	1

How do you move an index organized table?

The following index needs to be moved from tablespace DATA1 to tablespace DATA2:

select unique segment_name,segment_type,tablespace_name 
from dba_segments 
where owner='SCOTT'
and tablespace_name = 'DATA1'
;

Result:

SEGMENT_NAME	SEGMENT_TYPE	TABLESPACE_NAME
COUNTRY_ID_PK	INDEX	DATA1

Turns out, the index belongs to an IOT:

select i.owner,i.table_name,i.tablespace_name,i.status, t.iot_type
from dba_indexes i join dba_tables t
on (i.table_name = t.table_name)
and t.table_name ='COUNTRIES'
and t.owner=i.owner
where i.index_name='COUNTRY_ID_PK'
and i.owner='SCOTT';

Result in:

OWNER	TABLE_NAME	TABLESPACE_NAME	STATUS	IOT_TYPE
SCOTT	COUNTRIES	DATA1	VALID	IOT

If you try to move the index COUNTR_ID_PK with the usual "alter index ... rebuild" clause, it will fail with

ORA-28650: Primary index on an IOT cannot be rebuilt

Instead, move the table. Since it's an IOT, the index will be moved automatically with it:

alter table SCOTT.COUNTRIES move tablespace DATA2 ONLINE;

Verify that the IOT was moved by executing the same query as previously:

OWNER	TABLE_NAME	TABLESPACE_NAME	STATUS	IOT_TYPE
SCOTT	COUNTRIES	DATA2	VALID	IOT

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

Workaround for error Warning: ORA-16714: the value of property LogFileNameConvert is inconsistent with the member setting

Error from data guard:

oracle@oric-dbserver01:[vegdb01]# dgmgrl /
DGMGRL for Linux: Release 18.0.0.0.0 - Production on Thu Mar 12 08:02:54 2020
Version 18.9.0.0.0

Copyright (c) 1982, 2018, Oracle and/or its affiliates.  All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected to "vegdb01"
Connected as SYSDG.
DGMGRL> show configuration;

Configuration - myConfig

  Protection Mode: MaxPerformance
  Members:
  vegdb01      - Primary database
    vegdb01_stby - Physical standby database
      Warning: ORA-16792: configurable property value is inconsistent with member setting

Fast-Start Failover: DISABLED

Configuration Status:
WARNING   (status updated 10 seconds ago)

DGMGRL> show database vegdb01_stby

Database - vegdb01_stby

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 1 second ago)
  Apply Lag:          0 seconds (computed 1 second ago)
  Average Apply Rate: 143.00 KByte/s
  Real Time Query:    OFF
  Instance(s):
    vegdb01
      Warning: ORA-16714: the value of property LogFileNameConvert is inconsistent with the member setting

Database Status:
WARNING

Solved by setting the log_file_name_convert on the standby side.
Using dgmgrl:

edit DATABASE "vegdb01_stby" SET PROPERTY LogFileNameConvert =  " "," "; 

Or set it by using sqlplus on the standby site, see this post for how to do that

How to grant access to all tables in a schema in a PostgreSQL database

PostgreSQL offers a feature that Oracle currently does not: the ability to grant access rights to all objects in a specific schema.
Here is how.

Connect to the database "klmdb" and grant user JIM DML rights + query rights to all tables in the schema "klm":

postgres-# \connect klmdb
You are now connected to database "klmdb" as user "postgres".
klmdb=# GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA klm to JIM;

or

klmdb=# GRANT ALL ON ALL TABLES IN SCHEMA KLM TO JIM;

How to describe a table in a different schema in PostgreSQL

First, list the databases on the server:

[postgres@oric-pg01.oric.no ~]$ psql
psql (11.6)
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=# \list
                                    List of databases
   Name    |  Owner   | Encoding |  Collate   |   Ctype    |      Access privileges
-----------+----------+----------+------------+------------+------------------------------
 postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =Tc/postgres                +
           |          |          |            |            | postgres=CTc/postgres       +
           |          |          |            |            | postgres_exporter=c/postgres
 kldb      | postgres | UTF8     | en_US.utf8 | en_US.utf8 | postgres=CTc/postgres       +
 template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres                 +
           |          |          |            |            | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres                 +
           |          |          |            |            | postgres=CTc/postgres
(4 rows)

Then, connect to the correct database

postgres=# \connect kldb
You are now connected to database "kldb" as user "postgres".

You can now describe any table in the database:

kldb=# \d kld.actions

                                          Table "kld.actions"
     Column     |           Type           | Collation | Nullable |                             Default
----------------+--------------------------+-----------+----------+-----------------------------------------------------------------
 id             | integer                  |           | not null | nextval('kld.action_id_seq'::regclass)
 qid            | character varying(11)    |           | not null |
 systemid       | character varying(20)    |           | not null |
 actiontype     | character varying(20)    |           | not null |
 relation       | character varying(50)    |           |          |
 messageheader  | character varying(50)    |           |          |
 rnumber        | character varying(13)    |           |          |
 versionID      | numeric                  |           |          |
 errormsg       | character varying(4000)  |           |          |
 occured_time   | timestamp with time zone |           | not null | now()
Indexes:
    "pk__actions" PRIMARY KEY, btree (id)
    "idx__actions_sk1" btree (partsid, systemid, hendelsestype)

You can also use the metacommand "dt" or "dt+", like this:

prod=# \dt+ sales.*
                                       List of relations
  Schema   |                Name                | Type  |   Owner   |    Size    | Description
-----------+------------------------------------+-------+-----------+------------+-------------
 sales     | mytable1                           | table | jim       | 8192 bytes |
 sales     | mytable2                           | table | jim       | 8192 bytes |
 sales     | mytable3                           | table | jim       | 16 kB      |
 sales     | mytable4                           | table | jim       | 9757 MB    |
 sales     | mytable5                           | table | jim       | 312 GB     |
 (5 rows)