Does PostgreSQL support global indexes on partitioned tables?

PostgreSQL does not support Global Indexes, i.e indexes that spans all partitions in a partitioned table. The documentation states:

Although all partitions must have the same columns as their partitioned parent, partitions may have their own indexes, constraints and default values, distinct from those of other partitions.

and

Create an index on the key column(s), as well as any other indexes you might want, on the partitioned table. ... This automatically creates a matching index on each partition, and any partitions you create or attach later will also have such an index. An index or unique constraint declared on a partitioned table is “virtual” in the same way that the partitioned table is: the actual data is in child indexes on the individual partition tables. A proposal to add such feature seem to have been suggested back in 2019, but currently no implementation exists.

I also found this comparison between Oracle and PostgreSQL that elaborates on the topic a bit more.

How to extract hostname withouth FQDN in a shell variable

Taken from https://stackoverflow.com/questions/36151206/hostname-variable-in-shell-script-not-fqdn and really useful:

export HOST=${HOSTNAME%%.*}

Workaround for ORA-65011 in an upgraded EBS database running multitenant

If you are converting a non-cdb EBS database to a pdb in a multitenant setup, and your non-cdb had a name that used lower case ("pdb1" instead of "PDB1") the different configurationscripts that you will run during the upgrade will set the hidden parameter _pdb_name_case_sensitive to TRUE.

If you try to connect to your pdb as you normally would, you will see this error:

SYS@cdb SQL>  alter session set container=pdb1;
ERROR:
ORA-65011: Pluggable database PDB1 does not exist.

Check the setting of _pdb_name_case_sensitive:

SYS@cdb SQL> show parameter pdb

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
_pdb_name_case_sensitive             boolean     TRUE

To connect, enclose your pdb name with double quotation marks:

SYS@cdb SQL> alter session set container="pdb1";

Session altered.

SYS@cdb SQL> show con_name

CON_NAME
------------------------------
pdb1
SYS@cdb SQL> show con_id

CON_ID
------------------------------
3

The note ORA-65011: Pluggable Database Does Not Exist (Doc ID 2642230.1) from Oracle Support confirms this finding.

How to change an Oracle instance parameter based on database version and container type

Here is a simple pl/sql script that will set parameters based on version and container type.

set serveroutput on
DECLARE

v_version     sys.v_$instance.version%type;
v_param_value sys.v_$system_parameter.value%type;
v_pdb boolean;
v_con_id number;
v_con_type varchar(7);

BEGIN

-- find current setting of optimizer_dynamic_sampling
select value
into   v_param_value
from   v$parameter
where  name = 'optimizer_dynamic_sampling';

-- find current(major) version
select version
into   v_version
from   sys.v_$instance;

-- find container type
select 
    case when (sys_context('USERENV','CON_ID') = 0 ) then 'NON-CDB' 
       when (sys_context('USERENV','CON_ID') = 1 ) then 'CDB'
       when (sys_context('USERENV','CON_ID') > 1 ) then 'PDB'
    end
into v_con_type
from DUAL;

--dbms_output.put_line('v_con_type: ' || v_con_type);
--dbms_output.put_line('version: ' || v_version);
--dbms_output.put_line('optimizer_dynamic_sampling: ' || v_param_value);

v_pdb := FALSE;

IF v_con_type = 'PDB' THEN
  v_pdb := TRUE;
ELSE
  v_pdb := FALSE;
END IF;


CASE WHEN v_version IN ('18.0.0.0.0','19.0.0.0.0') AND v_param_value = '0' THEN
    dbms_output.put_line('version is >= 18 and optimizer_dynamic_sampling = 0');
    
    IF v_pdb = FALSE THEN
      dbms_output.put_line('execute immediate alter system set optimizer_dynamic_sampling=4 scope=both;');
      execute immediate 'alter system set optimizer_dynamic_sampling=4 scope=both';
    ELSIF v_pdb = TRUE THEN
      dbms_output.put_line('execute immediate alter system set optimizer_dynamic_sampling=4 container=current scope=both;');
      execute immediate 'alter system set optimizer_dynamic_sampling=4 container=current scope=both';
    END IF;
    
WHEN v_version IN ('18.0.0.0.0','19.0.0.0.0') AND v_param_value <> '0' THEN   
     dbms_output.put_line('version is >= 18 and optimizer_dynamic_sampling is already set.');
ELSE
  dbms_output.put_line('version is < 18');
END CASE;

END;
/

How to check for existence of multiple files in a shell script

# check for installation files existence
if  [ ! -f /tmp/file1 -a -f /tmp/file2 -a -f /tmp/file3 ]
then
echo "Some of the files file1, file2 or file3 are missing from /tmp "
exit 1
fi

How to list all nfs mountpoints

findmnt -t nfs

TARGET SOURCE                                               FSTYPE OPTIONS
/u01   pzl2ora1:/Oracle/software/pzh0oric/u01 nfs    rw,nodiratime,relatime,vers=3,rsize=65536,wsize=65536,namlen=255,acregmin=300,acregmax=300,acdirmin=300,ac
/u02   pzl2ora1:/Oracle/pzh0oric/u02          nfs    rw,nodiratime,relatime,vers=3,rsize=65536,wsize=65536,namlen=255,acregmin=300,acregmax=300,acdirmin=300,ac
/u03   pzl2ora1:/Oracle/pzh0oric/u03          nfs    rw,nodiratime,relatime,vers=3,rsize=65536,wsize=65536,namlen=255,acregmin=300,acregmax=300,acdirmin=300,ac
/u04   pzl2ora1:/Oracle/pzh0oric/u04          nfs    rw,nodiratime,relatime,vers=3,rsize=65536,wsize=65536,namlen=255,acregmin=300,acregmax=300,acdirmin=300,ac

How to solve ORA-01187: cannot read from file because it failed verification tests

After a successful clone, alert log reports:

022-06-30T04:04:17.542368+02:00
Errors in file /orainst/oracle/diag/rdbms/testdb01/testdb01/trace/testdb01_dbw0_130154.trc:
ORA-01186: file 201 failed verification tests
ORA-01122: database file 201 failed verification check
ORA-01110: data file 201: '/data/oradata/testdb01/temp01.dbf'
ORA-01203: wrong incarnation of this file - wrong creation SCN
File 201 not verified due to error ORA-01122

Any operation you try against the database will give the following error stack in return:

ORA-01187: cannot read from file  because it failed verification tests
ORA-01110: data file 201: '/data/oradata/testdb01/temp01.dbf'
ORA-06512: at "SYS.DBMS_LOB", line 741
ORA-06512: at "SYS.DBMS_DATAPUMP", line 5420
ORA-06512: at line 1

Check tempfile situation:

SYS@testdb01>SQL>select ts#,status,enabled, name from v$tempfile;

       TS# STATUS  ENABLED    NAME
---------- ------- ---------- ---------------------------------
         3 ONLINE  READ WRITE /data/oradata/testdb01/temp01.dbf
         3 ONLINE  READ WRITE /data/oradata/testdb01/temp02.dbf

A query against dba_temp_files however, will give an error:

SYS@testdb01>SQL>SELECT tablespace_name, file_name  FROM dba_temp_files WHERE tablespace_name = 'TEMP';
SELECT tablespace_name, file_name  FROM dba_temp_files WHERE tablespace_name = 'TEMP'
                                        *
ERROR at line 1:
ORA-01187: cannot read from file  because it failed verification tests
ORA-01110: data file 201: '/data/oradata/testdb01/temp01.dbf'

Is the temporary tablespace database default?

SYS@testdb01>SQL>
col property_value format a30
SELECT PROPERTY_VALUE   
FROM DATABASE_PROPERTIES 
WHERE PROPERTY_NAME = 'DEFAULT_TEMP_TABLESPACE';

PROPERTY_VALUE
-----------------------
TEMP

Yes it is, so we cannot drop it, but will have to add new files and drop the old ones

Add new tempfile:

SYS@testdb01>SQL>ALTER TABLESPACE temp ADD TEMPFILE '/data/oradata/testdb01/temp.dbf' size 256M;

Tablespace altered.

Drop the old ones:

SYS@testdb01>SQL>ALTER DATABASE TEMPFILE '/data/oradata/testdb01/temp01.dbf' DROP INCLUDING DATAFILES;

Database altered.

SYS@testdb01>SQL>ALTER DATABASE TEMPFILE '/data/oradata/testdb01/temp02.dbf' DROP INCLUDING DATAFILES;

Database altered.

Both of the old datafiles are now gone from the data dictionary:

SYS@pserser4>SQL>
set lines 200
col tablespace_name format a20
col file_name format a50
SELECT tablespace_name, file_name  FROM dba_temp_files WHERE tablespace_name = 'TEMP';

TABLESPACE_NAME         FILE_NAME
---------------------   --------------------------------------
TEMP                    /data/oradata/testdb01/temp.dbf

Note that the old tempfiles are still on disk:

SYS@testdb01>SQL>!ls -la /data/oradata/testdb01/temp01.dbf
-rw-r----- 1 oracle dba 114302976 Jun 30 04:04 /data/oradata/testdb01/temp01.dbf

If you want to add the old datafiles back to your temp tablespace you can do this with the REUSE keyword:

SYS@testdb01>SQL>ALTER TABLESPACE temp ADD TEMPFILE '/data/oradata/testdb01/temp01.dbf' size 256M reuse;

Tablespace altered.

In my case, the other tempfile /data/oradata/testdb01/temp02.dbf, was not present physically on disk.
The alert log confirms this:

2022-06-30T04:04:18.302852+02:00
Errors in file /data/oracle/diag/rdbms/testdb01/testdb01/trace/testdb01_dbw0_130154.trc:
ORA-01157: cannot identify/lock data file 202 - see DBWR trace file
ORA-01110: data file 202: '/data/oradata/testdb01/temp02.dbf'
ORA-17503: ksfdopn:4 Failed to open file /data/oradata/testdb01/temp02.dbf
ORA-17500: ODM err:File does not exist