ORACLE 19C NEW FEATURES AND HOW TO APPLY THEM

Automatically Run Configuration Scripts

Before 19c in the oracle database software installation requires manual effort to run the root.sh scripts. But from 19c we can specify while installing the database using DBCA.

While installing the database we will get an option to run the scripts automatically like below

Automatically run configuration scripts, and provide the password for the root user and click next.

The execution of the database via DBCA will continue until we get the below message

Click Yes and continue.

In the response file we can see this feature of the database

oracle.install.db.rootconfig.executeRootScript=true

Hybrid Partitioned Tables

Partitioning of table is nothing but dividing a big table into smaller, more accessible and manageable parts. There are different way to partition table like by range or value.

12c version allows us to partition a table  either by internally which stores the table in oracle database datafiles or externally means saving the table on external source.

19c gave us the option of doing both we can save the partition table internally as well externally these tables are called as Hybrid partitioned tables.

How to create Hybrid partitioned tables

We need to create tablespace for the internal partitions and OS level directories for external partitions

Create the user who will have all the privileges on the OS directory as well the tablespace which we created.

In the below example we created a hybrid partition table with the following parameters.

Default tablespace for internal partition is internal.

Default tablespace for external partition is External.

The table is partitioned into four parts:

1.Two external partitions: External1 is empty and External2 has External2.dat has datafile stored in another directory than the default

2.Two internal partition Int with default tablespace internal2  and  PMAX with default tablespace  Internal1

We can insert the data into internal partitions using a Insert command

The data can be inserted into the external partition only via the external source data file.

host echo “41,12-Aug-1997” >> /home/oracle/vashishth/EXTERNAL2.dat

We can check if data is inserted in the external partition using the below sql

We can different views to check and maintain the internal and external partitions of the table like:

• dba_external_tables
• dba_tab_partitions

IN-MEMORY HYBRID PARTITIONED TABLES

From Oracle 12c the oracle database supports the in Memory Dual format architecture means the buffer cache maintains the row format and IN memory column store maintains the column format.

Oracle allows us to insert data into the In-Memory column store from external tables as well. We know this data is stored in external source datafiles. To populate the data into the IM column store we need to manually run the procedure DBMS_INMEMORY.POPULATE.

But for 19c when we query an in memory enabled external table it will automatically populates the data from external table into the IM column store.

• Set the IM column store size

We need to bounce the database to enable the above changes in the database.

We will use the above create tablespace and local directories for this .

We will create a similar hybrid partitioned table that we created in the above example only change will be to use INMEMORY MEMCOMPRESS FOR QUERY HIGH in the end

We can insert the data into the partitions as described above. After insert is successful it will automatically populate into the IM column store. We can verify which partitions are populated into the IM column store.

When we check the execution plan, we can check the table access would be like INMEMORY FULL.