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

How to replace a specific string in a very large text file

Here is how I removed the string "public." from a 4G file:

sed -i 's/public\.//g' mylargetextfile.txt

Notice the forward slash in front of the puncuation mark. It tells sed to interpret it literally, not as a special character used in regular expressions. Source: Stackoverflow

An Oracle procedure that allow users to kill their own sessions

First, create a view that lets the users check their own sessions:

connect / as sysdba

create or replace view my_sessions as
select username,sid,serial#,osuser,status,program,machine
from v$session
where username=SYS_CONTEXT('userenv','CURRENT_SCHEMA');

Create a public synonym, and grant select to public:

create public synonym my_sessions for my_sessions;
grant select on my_sessions to public;

To get the procedure to compile in the SYSTEM schema, it is required to grant the "alter system" privilege directly to the user - having the same privilege through a role won't do.

grant alter system to system;
grant select on v_$session to system;

Log in as system, and create the procedure:

CREATE OR REPLACE PROCEDURE KILL_SESSION(PN_SID NUMBER,PN_SERIAL NUMBER) AUTHID DEFINER
AS
LV_USER VARCHAR2(30);
EXE_USER VARCHAR2(30);
BEGIN
    SELECT S.USERNAME INTO LV_USER 
    FROM V$SESSION S, USER_USERS U 
    WHERE S.SID    = PN_SID 
    AND S.SERIAL#  = PN_SERIAL
    AND S.USERNAME = S.USERNAME;

    SELECT USERNAME INTO EXE_USER
    FROM V$SESSION 
    WHERE AUDSID=SYS_CONTEXT('userenv','SESSIONID');

 IF EXE_USER = LV_USER THEN
    EXECUTE IMMEDIATE 'alter system kill session '''||PN_SID||','||PN_SERIAL||'''';
    dbms_output.put_line('Session ' || PN_SID || ',' || PN_SERIAL || ' killed.');
ELSE
    RAISE_APPLICATION_ERROR(-20000,'Only your own sessions may be killed.');
END IF;
END;
/  

Create a synonym for the procedure and give everyone execute privileges on it:

CREATE PUBLIC SYNONYM KILL_SESSION FOR KILL_SESSION;
GRANT EXECUTE ON KILL_SESSION TO PUBLIC;

Now the users have a way to check their own sessions, and to terminate them if they chose to do so:

connect scott
select * from my_session;

USERNAME                    SID    SERIAL# OSUSER               STATUS               PROGRAM                                          MACHINE
-------------------- ---------- ---------- -------------------- -------------------- ------------------------------------------------ ----------------------------------------------------------------
SCOTT                        1152       9510 oracle               INACTIVE             sqlplus@myserver.mydomain.com (TNS V1- myserver.mydomain.com
SCOTT                        1726      31531 oracle               ACTIVE               sqlplus@myserver.mydomain.com (TNS V1- myserver.mydomain.com

Kill a session:

scott@mydb>SQL>exec kill_session(1152,9510);

Afterwards, the view will change to show session 1152 to be in status KILLED:

USERNAME                    SID    SERIAL# OSUSER               STATUS               PROGRAM                                          MACHINE
-------------------- ---------- ---------- -------------------- -------------------- ------------------------------------------------ ----------------------------------------------------------------
SCOTT                        1152       9510 oracle               KILLED             sqlplus@myserver.mydomain.com (TNS V1- myserver.mydomain.com
SCOTT                        1726      31531 oracle               ACTIVE               sqlplus@myserver.mydomain.com (TNS V1- myserver.mydomain.com