{"id":14539,"date":"2020-01-01T07:13:04","date_gmt":"2020-01-01T07:13:04","guid":{"rendered":"https:\/\/dbtut.com\/?p=14539"},"modified":"2021-02-09T10:18:24","modified_gmt":"2021-02-09T10:18:24","slug":"oracle-19c-new-features-and-how-to-apply-them","status":"publish","type":"post","link":"https:\/\/dbtut.com\/index.php\/2020\/01\/01\/oracle-19c-new-features-and-how-to-apply-them\/","title":{"rendered":"ORACLE 19C NEW FEATURES AND HOW TO APPLY THEM"},"content":{"rendered":"
Automatically Run Configuration Scripts<\/h2>\n
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.<\/p>\n
While installing the database we will get an option to run the scripts automatically like below<\/p>\n
<\/p>\n
Automatically run configuration scripts<\/strong>, and provide the password for the root user and click next.<\/p>\n
The execution of the database via DBCA will continue until we get the below message<\/p>\n
Click Yes and continue.<\/p>\n
In the response file we can see this feature of the database<\/p>\n
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.<\/p>\n
12c version allows us to partition a table \u00a0either by internally which stores the table in oracle database datafiles or externally means saving the table on external source.<\/p>\n
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.<\/p>\n
How to create Hybrid partitioned tables<\/h3>\n
We need to create tablespace for the internal partitions and OS level directories for external partitions<\/p>\n
We can check if data is inserted in the external partition using the below sql<\/p>\n
SELECT history_event, TO_CHAR(time_id, 'dd-MON-yyyy')\r\nFROM Amit.Vas_tabPARTITION (EXTERNAL2) ORDER BY 1;<\/pre>\n
We can different views to check and maintain the internal and external partitions of the table like:<\/p>\n
\n
dba_external_tables<\/li>\n
dba_tab_partitions<\/li>\n<\/ul>\n
IN-MEMORY HYBRID PARTITIONED TABLES<\/h3>\n
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.<\/p>\n
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.<\/p>\n
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.<\/p>\n
\n
Set the IM column store size<\/li>\n<\/ul>\n
SQL> ALTER SYSTEM SET \u00a0INMEMORY_SIZE= 1GB scope=spfile ;<\/pre>\n
We need to bounce the database to enable the above changes in the database.<\/p>\n
We will use the above create tablespace and local directories for this .<\/p>\n
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<\/p>\n
CREATE TABLE Amit.Vas_tab (history_event NUMBER , time_id DATE) TABLESPACE Internal1\r\n\u00a0 EXTERNAL PARTITION ATTRIBUTES\r\n\u00a0\u00a0\u00a0\u00a0 (TYPE ORACLE_LOADER\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 DEFAULT DIRECTORY External2\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 ACCESS PARAMETERS\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (FIELDS TERMINATED BY ','\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (history_event , time_id DATE 'dd-MON-yyyy')\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 )\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 REJECT LIMIT UNLIMITED\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 )\r\n\u00a0 PARTITION BY RANGE (time_id)\r\n\u00a0\u00a0 (PARTITION External1 VALUES LESS THAN (TO_DATE('01-Jan-1800','dd-MON-yyyy'))\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 EXTERNAL,\r\n\u00a0\u00a0\u00a0 PARTITION External2 VALUES LESS THAN (TO_DATE('01-Jan-1900','dd-MON-yyyy'))\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 EXTERNAL DEFAULT DIRECTORY External2 LOCATION ('External2.dat'),\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 INMEMORY MEMCOMPRESS FOR CAPACITY HIGH;\r\n\u00a0\u00a0\u00a0 PARTITION Int VALUES LESS THAN (TO_DATE('01-Jan-2001','dd-MON-yyyy'))\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 TABLESPACE internal2\r\n\u00a0\u00a0\u00a0 PARTITION pmax VALUES LESS THAN (MAXVALUE))\r\nINMEMORY MEMCOMPRESS FOR QUERY HIGH ;\r\n\r\n\u00a0We can check which partition are defined as in-memory segments.\r\n\r\nSELECT partition_name, inmemory, inmemory_compression FROM dba_tab_partitions\r\nWHERE\u00a0 table_name = 'Vas_tab';\r\nPARTITION_NAME INMEMORY INMEMORY_COMPRESS\r\n\r\n--------------\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 --------\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 -----------------s\r\n\r\nExternal1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ENABLED\u00a0\u00a0\u00a0\u00a0\u00a0 FOR CAPACITY HIGH\r\n\r\nExternal2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ENABLED\u00a0\u00a0\u00a0\u00a0 FOR CAPACITY HIGH\r\n\r\nPMAX\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ENABLED\u00a0\u00a0\u00a0 FOR QUERY HIGH\r\n\r\nINT\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ENABLED\u00a0\u00a0\u00a0 FOR QUERY HIGH<\/pre>\n
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.<\/p>\n
![\"\"](\"https:\/\/dbtut.com\/wp-content\/uploads\/2020\/01\/img_5e0c459927d3d.png\")
<\/p>\n