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

How to rename a database to use lower case instead of upper case characters

At my customer's site, we have standardized the Oracle SIDs (System ID) to use lower case character.

During an upgrade, the SID was accidently set to upper case. This new uppercase SID was picked up by Oracle Cloud Control and all of a sudden we had a non-standardized database within our ranks. Not a disaster, but irritating, nevertheless.

Given that your environment variable in you operating system is also set to lower case, you can simply restart the database with a new value for the parameter db_name in the parameter file to rectify the situation. There is no need to use the nid ("new id") tool or to recreate the controlfile. A simple restart with the right value for the parameter db_name will do.

It's easy to verify this conclution if you have access to another database which adheres to the standard. Check the value of the columnn name in v$database:

select name from v$database;

NAME
---------
PRODDB01

SQL> show parameter db_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      PRODDB01

As expected, the query from v$database it is displayed in upper case as Oracle alwaysd does by default, but we also see that the value of the parameter db_name is actually in lower case.

In other words, there is no connection between the value of ORACLE_SID and the name of the database used in the controlfile. Oracle will always refer to the database in upper case. How we present and use this value in the operating system, is up to us.

These are the simple steps to get the value of the db_name parameter aligned with the value of your operating system variable ORACLE_SID:

Check your environment variable:

env | grep ORA
ORACLE_SID=testdb01 

It is indeed shown in lower case

Create a new pfile:

sqlplus / as sysdba
create pfile='inittestdb01.ora' from spfile;

Edit the pfile so that the db_name parameter has its value in lower case:

Old value:
*.db_name='TESTDB01'

New value:

*.db_name='testdb01'

Restart the database:

sqlplus / as sysdba
shutdown immediate
startup

If desirable, create a new spfile and restart the database one final time to instruct oracle to use it instead of the pfile:

create spfile from pfile='inittestdb01.ora';
shutdown immediate
startup

How to use opatch to check if Oracle Gateway for DRDA is installed

Log on to your oracle server, and type

cd $ORACLE_HOME/OPatch
./opatch lsinventory -oh /full/path/to/oracle_home -details > /home/oracle/inventory_details.log

The top lines of the generated file will reveal what's installed in your ORACLE_HOME:

cat /home/oracle/inventory_Details.log | more

Result:

Installed Top-level Products (2):

Oracle Database 12c                                                  12.1.0.2.0
Oracle Database Gateways                                             12.1.0.2.0
There are 2 products installed in this Oracle Home.

After that, more details about your installation is listed:

Installed Products (137):
Oracle Database Gateway for DRDA                                     12.1.0.2.0
Oracle Database Gateway for ODBC                                     12.1.0.2.0
Oracle Database Gateways                                             12.1.0.2.0