Simple SQL to list

The following SQL lists the indexes defined on a table, along with the columns and their positioning:

SELECT I.INDEX_NAME,I.INDEX_TYPE,I.NUM_ROWS,I.DEGREE, C.COLUMN_NAME,C.COLUMN_POSITION
FROM DBA_INDEXES I JOIN DBA_IND_COLUMNS C
ON (I.INDEX_NAME = C.INDEX_NAME)
WHERE I.OWNER='MYSCHEMA'
AND I.OWNER = C.INDEX_OWNER
AND I.TABLE_NAME='MYTABLE'
ORDER BY I.INDEX_NAME, C.COLUMN_POSITION;

How to set a dynamic parameter in a postgreSQL database cluster

As with oracle, some parameters may be set dynamically in a postgreSQL database cluster. A postgreSQL database cluster uses a parameter file called postgres.conf. This file holds the cluster wide parameters. If you set a dynamic parameter using the ALTER SYSTEM SET command, the parameter will be written to yet another file called postgres.auto.conf, which values will always override the ones parameters in the postgres.conf Before the change, postgres.auto.conf look like this:

log_line_prefix = '[%m] – %p %q- %u@%h:%d – %a'
wal_level = 'replica'
hot_standby = 'on'

I then make a change to the system configuration:

alter system set hot_standby_feedback=on;
ALTER SYSTEM

After this change, the file postgres.auto.conf has another entry:

log_line_prefix = '[%m] – %p %q- %u@%h:%d – %a'
wal_level = 'replica'
hot_standby = 'on'
hot_standby_feedback = 'on'

I will then have to reload the database using the function pg_reload_conf() for the new parameter to take effect:

postgres=#  select pg_reload_conf();
 pg_reload_conf
----------------
 t
(1 row)

The current logfile for the postgreSQL database cluster records this fact:

[2022-01-03 14:45:23.127 CET] – 1683 LOG:  received SIGHUP, reloading configuration files
[2022-01-03 14:45:23.129 CET] – 1683 LOG:  parameter "hot_standby_feedback" changed to "on"

For details, check the documentation

How to find out if a hot standby postgres database is receiving logs

 select pg_is_in_recovery();
 pg_is_in_recovery
-------------------
 t
(1 row)

https://www.postgresql.org/docs/11/functions-admin.html#FUNCTIONS-RECOVERY-INFO-TABLE

What is the difference between "force parallel" and "enable parallel" used in the "alter session" statement in Oracle?

What is the difference between these two statements?

ALTER SESSION ENABLE PARALLEL DML | DDL | QUERY;

and

ALTER SESSION FORCE PARALLEL DDL | DML | QUERY;

Answer:

The difference here lays in the details: the ENABLE statement merely enables parallelization using a concrete parallel directive or parallel hint. If this is not specified, Oracle will execute the statements sequenctually. The FORCE statement will parallelize everything it can with the default DOP (degree of parallelism), without you having to state anyting about this in your DML | DDL or query statements.

If the default DOP isn't good enough for you (for example during an index rebuild), you can force your session to use a DOP higher than the default, like this:

ALTER SESSION FORCE PARALLEL DDL | DML | QUERY PARALLEL 32;

This will override any other DOP in the same session and use 32 parallel workers.

Alter session in 19c is documentet here
The concept of forcing/enabling parallelization is explained here

How to work around error [INS-08101] Unexpected error while executing the action at state: ‘supportedOSCheck’

Thanks so much to Martin Berger for his blog post showing how to work around an error that shows up when you attempt to install Oracle 19c software on a RHEL8 distribution.

I wanted to install Oracle software on a RH8.5 Linux server:

cat /etc/redhat-release
Red Hat Enterprise Linux release 8.5 (Ootpa)
  

This is the error I received:

    

In my case, the only thing I had to do was to add another environmental variable:

export CV_ASSUME_DISTID=OEL7.8

Execute the installer again and you will see a different screen:

./runInstaller

How to find tables with specific column data types in a PostgreSQL

Thanks to Bart Gawrych for blogging this very useful post on which my own post is based on.

I needed to find all tables in a specific schema that used json or jsonb data type columns. Here is my query, saved in a file called "find_cols.sql":

select col.table_schema,
       col.table_name,
       col.ordinal_position as column_id,
       col.column_name,
       col.data_type
from information_schema.columns col
join information_schema.tables tab on tab.table_schema = col.table_schema
                                   and tab.table_name = col.table_name
                                   and tab.table_type = 'BASE TABLE'
where col.table_schema in ('myschema')
      and col.data_type IN ( 'json','jsonb' )
order by col.table_schema,
         col.table_name,
         col.ordinal_position;

Execution in psql directly on server like this:

psql mydb -f find_cols.sql

How to add a string to specific line in a text file

Here is an example of how I inserted a line needed in a postgres pg_restore command, at line 20:

sed -i '20s/^/SET search_path to sales,public;\n/' myfile.txt

If your string contains special characters, remember to escape it properly. In the next example, I want to append the string \timing at line number 7 in myfile.txt:

 sed -i '7s/^/\\timing on\n/' myfile.txt