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

How to exctract a specific file from a .tar file

Say I would like to extract an old version of the oracle inventory, packed in a tar file called "oracle.tar". Change directory to the physical location you want to extract the files to:

cd /home/oracle

Find the path to the file you are interested in:

tar -tvf oracle.tar | grep oraInst.loc
-rw-rw---- oracle/dba        55 2018-01-25 15:02 ./oraInventory/oraInst.loc

Extract the tar file using the path displayed above:

tar xvf /u01/data/oracle.tar ./oraInventory/oraInst.loc

How to only insert rows that do not violate a specific constraint

PostgreSQL offers an alternative to raising an exception upon a unique key violation: ON CONFLICT ON CONSTRAINT

INSERT INTO sales.sales_history 
SELECT * from sales.sales_history_p201801 
ON CONFLICT ON CONSTRAINT uc_sh1 DO NOTHING;

Comes in as a handy way of transfering rows from an old table to a partially populated new table. Duplicate rows will simply be ignored.

Documentation for PostgreSQL 11 can be found here.

How to export a single table in PostgreSQL

Example of exporting a single table. In the directory-format:

pg_dump -Fd mydb -t myschema.mytable -v -f  /data/mydirectory -j 2

Where

-Fd = export into a directory-format archive
-t = table name
-v = verbose
-f = the target directory
-j = number of export jobs

Note that -f can be omitted for file based output formats.
Since I am using the directory output format however, I am required to use it, and it specifies the target directory instead of a file. In this case the directory is created by pg_dump and it must not exist before.

In plain text file format:

pg_dump mydb -n 'myschema' -a -Fp  -t mytable | gzip > mydb_myschema_mytable.gz

Where

-Fp = plain text format
-a  = data only, no metadata. In the case of plain text, this means no "create table" statement is generated
-t  = table name

When the export is done, you may unpack the mydb_myschema_mytable.gz and you will observe that the file contains COPY directives for each row.

In custom format:

pg_dump mydb -n 'myschema' -a -Fc  -t mytable | gzip > mydb_myschema_mytable_custom.gz

Where all the options are identical to the plain text format, except for

-Fc = custom data format

How to list the number of rows per partition in PostgreSQL

I found an old post from 2013 on stackoverflow.com which solved a problem I had finding the number of rows per partition in PostgreSQL.

I couldn't find a simple way to list the estimated number of rows per partition and subpartition in PostgreSQL so I created a view as suggested by the member MatheusOl:

CREATE OR REPLACE VIEW row_counts AS
    SELECT relname, reltuples::numeric
    FROM pg_class pg, information_schema.tables i
    WHERE pg.relname = i.table_name
        AND relkind='r'
        AND table_schema NOT IN ('pg_catalog', 'information_schema');

Logon to your database using psql and create the view. Then, query it and the result would be something similar to querying dba_tab_partitions and dba_tab_subpartitions in Oracle:

            relname              |  reltuples
---------------------------------+-------------
 event_sp_2019_ukf               |       20799
 event_sp_2019_vasplpr           |           0
 event_sp_2019_ukp               |         120
 event_sp_2019_ltp               |           0
 event_sp_2019_smp               |          95

The view will not distinguished between tables, partitiones or subpartitions - they're all tables in terms of object types.

In PostgreSQL, what does "regclass" mean?

"regclass" is an alias for an Object Identifier, which are used internally by PostgreSQL as primary keys for several data dictionary tables.

It can be used as a translator when querying the dictionary tables in postgres.

For example, the following query produces an error, since the predicate expects an integer ("oid"):

select relname,relowner,reltuples,relkind  from pg_class where oid='myuser.mytable_1';
ERROR:  invalid input syntax for type oid: "myuser.mytable_1"
LINE 1: ...lowner,reltuples,relkind  from pg_class where oid='myuser.mytabl...

However, if you add "::regclass", the alias for the object is used instead of the literal string:

 select relname,relowner,reltuples,relkind  from pg_class where oid='myuser.mytable_1'::regclass;
  relname  | relowner | reltuples | relkind
-----------+----------+-----------+---------
 mytable_1 |    69094 |         0 | p

Note that there are several types of OIDs. For example, for roles (users), you should use "regrole" instead of "regclass":

select relname,relowner,reltuples,relkind  from pg_class where relowner='myuser'::regrole;
        relname        | relowner | reltuples | relkind
-----------------------+----------+-----------+---------
 unique_idx_2          |    69094 |         0 | I
 pg_toast_191274       |    69094 |         0 | t
 mytable_1_id_seq      |    69094 |         1 | S
 mytable_1_pkey        |    69094 |         0 | I
 unique_idx_1          |    69094 |         0 | i
 mytable_2             |    69094 |         0 | p
 mytable_3             |    69094 |         0 | r
 

If you use them together, translate to different types of OID. In the below case, it's really not necessary, but illustrates the point nevertheless:

 select relname,relowner,reltuples,relkind  from pg_class where oid='myuser.mytable_1'::regclass and relowner='myuser'::regrole;

More information about OIDs
More information about pg_class