How to generate insert statements for a table in PostgreSQL

Thanks to Carlos Becker for pointing out the following solution on how to generate insert statments for a table in PostgreSQL

pg_dump mydb -n 'myschema' -a -Fp  -t myschema.mytable --column-inserts > /data1/insert_statements.sql| gzip

How to find the oid for a table in PostgreSQL

Connected to the relevant database using the connection tool of your choice, execute the following to find the oid for a table:

mydb # select 'myschema.mytable'::regclass::oid;
   oid
----------
 19561436
(1 row)

Query to find schema size in PostgreSQL

SELECT pg_size_pretty(sum(pg_relation_size(quote_ident(schemaname) || '.' || quote_ident(tablename)))::bigint) FROM pg_tables 
WHERE schemaname = 'yourschema';

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;
/