Oracle12c-Transparent Data Encryption (TDE) Tips And Tricks

Transparent Data Encryption :-

  • TDE is an encryption mechanism present in Oracle database used to encrypt the data stored in a table column or tablespace. It protects the data stored on database files (DBF) by doing an encryption in case the file is stolen or hacked.
  • Transparent Data Encryption (TDE) provides mechanism to encrypt the data stored in the OS data files. TDE enables the encryption of data at the storage level to prevent data tempering from outside of the database.
  • When using transparent encryption, the Oracle encryption wallet must be created and the wallet should be opened every time the database starts.

TDE supports two levels of encryption

  • Columns Level Encryption: Encrypt the table column data.
  • Tablespace Level Encryption: Encrypt all the data in a tablespace.

Oracle database 12c introduced a new way to manage keystores, encryption keys and secrets using the ADMINISTER KEY MANAGEMENT command. This replaces the ALTER SYSTEM SET ENCRYPTION KEY and ALTER SYSTEM SET ENCRYPTION WALLET commands for key and wallet administration from previous releases.

Steps to create wallet and enable encryption for table column and tablespace:

  • Create a wallet location :
[oracle@orcl:~ ] mkdir -p /home/oracle/wallet
  • Update the wallet/keystore location in SQLNET.ORA :

A keystore must be created to hold the encryption key.Add the below entry to SQLNET.ORA,

[oracle@orcl:~ orcldemo] cat $ORACLE_HOME/network/admin/sqlnet.ora
# sqlnet.ora Network Configuration File: /oradb/app/oracle/product/12.2.0.1/db_1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

ENCRYPTION_WALLET_LOCATION =
(SOURCE =(METHOD = FILE)(METHOD_DATA =
(DIRECTORY = /home/oracle/wallet)))
  • Create a keystore :
SQL> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/home/oracle/wallet' IDENTIFIED BY wallet$123;

keystore altered.

SQL> HOST ls /home/oracle/wallet
ewallet.p12
  • Open the keystore :
SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY wallet$123;

keystore altered.
  • Check the v$encryption_keys view to see the key activated:
SQL> SELECT con_id, key_id FROM v$encryption_keys;

no rows selected
  • Set the encyrption key :
SQL> ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY wallet$123 WITH BACKUP;

keystore altered.

WITH BACKUP creates a backup of the software keystore.It also creates a backup of the keystore before creating the new master encryption key.

Now,check the v$encryption_keys view to check the keystore is enabled.

SQL> SELECT con_id, key_id FROM v$encryption_keys;

 CON_ID          KEY_ID
---------- ----------------------------------------------------
0          AVRRh/0Uok8dvyvCAPMtZhwAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

Check keystore information from v$encryption_wallet view.

The wallet type is password-based software keystore: As names suggests, this type of keystore is protected by a password, and password is required to open the keystore to retrieve the encryption keys.

TDE Implementation in Oracle 12c database :

Table creation with encrypted wallet :

SQL> CREATE TABLE student (name VARCHAR2(30),rollno NUMBER,dept VARCHAR2(30) ENCRYPT);

Table created.

SQL> INSERT INTO student values('hari',101,'MCA');

1 row created.

SQL> commit;

Commit complete.

SQL> select owner,table_name,column_name,encryption_alg from dba_encrypted_columns where table_name='STUDENT';

OWNER      TABLE_NAME COLUMN_NAME          ENCRYPTION_ALG
---------- ---------- -------------------- -----------------
HARI       STUDENT    DEPT                 AES 192 bits key
  • Restart the database,
SQL> startup force
ORACLE instance started.

Total System Global Area 4294967296 bytes
Fixed Size 8628936 bytes
Variable Size 603981112 bytes
Database Buffers 1526726656 bytes
Redo Buffers 8146944 bytes
In-Memory Area 2147483648 bytes
Database mounted.
Database opened.
  • After restart database,check the encrypted table column data,
SQL> SELECT * FROM hari.student;
SELECT * FROM hari.student;
                   *
ERROR at line 1:
ORA-28365: wallet is not open
  • Reopen the keystore :
SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY wallet$123; 

keystore altered.

Here the wallet_type is PASSWORD , i.e every time we restart the database, we need to open the key/wallet separately.To overcome this, we can enable auto login ,so that next time when db gets restart, it will open the wallet automatically.

Auto-login software keystore: 

This kind of keystores are protected by system-generated password, and does not need to opened explicitly because these keystores open automatically.

  • Enable AUTOLOGIN  wallet type :
SQL> SELECT * FROM v$encryption_wallet;

WRL_TYPE WRL_PARAMETER    STATUS WALLET_TYP WALLET_OR  FULLY_BAC CON_ID
-------- -------------    ------ ---------- ---------- --------- ------
FILE     /home/oracle/wallet/ OPEN PASSWORD SINGLE     NO        0
SQL> ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE '/home/oracle/wallet' IDENTIFIED BY wallet$123;

keystore altered.
SQL> HOST ls /home/oracle/wallet
cwallet.sso ewallet.p12 ewallet_2018111618242920.p12 ewallet_2018111618410185.p12

As soon as we execute above statement, we will see cwallet.sso file gets created under keystore location directory. Once we have AUTOLOGIN keystore, there is no need to open keystore for individual pluggable databases because auto-login keystore would open automatically for all pluggable databases as well.

  • Restart the database,
SQL> startup force
ORACLE instance started.

Total System Global Area 4294967296 bytes
Fixed Size 8628936 bytes
Variable Size 603981112 bytes
Database Buffers 1526726656 bytes
Redo Buffers 8146944 bytes
In-Memory Area 2147483648 bytes
Database mounted.
Database opened.
  • After restarting database,check the wallet type:
SQL> SELECT * FROM v$encryption_wallet;

WRL_TYPE WRL_PARAMETER STATUS WALLET_TYP WALLET_OR FULLY_BAC CON_ID
-------- ------------- ------ ---------- ---------- --------- ------
FILE /home/oracle/wallet/ OPEN AUTOLOGIN SINGLE NO 0
  • Now,the encrypted table data and column name ‘DEPT’,
SQL> SELECT * FROM hari.student;

NAME  ROLLNO  DEPT
----- ------- -----
hari  101      MCA
  • Create Encrypted Tablespace :
SQL> CREATE TABLESPACE encrypt_ts DATAFILE '/oradb/app/oracle/oradata/ORCLDEMO/datafile/encrypt_ts.dbf' SIZE 2G ENCRYPTION USING 'AES256' DEFAULT STORAGE(ENCRYPT);

Tablespace created.


SQL> CREATE TABLE student_demo (name VARCHAR2(30),rollno NUMBER,dept VARCHAR2(30)) tablespace encrypt_ts ;

Table created.

SQL> INSERT INTO student_demo values('hari',101,'MCA'); 

1 row created. 

SQL> commit; 

Commit complete.

SQL> select tablespace_name,encrypted from dba_tablespaces where tablespace_name='ENCRYPT_TS';

TABLESPACE_NAME   ENC
---------------   ---
ENCRYPT_TS        YES
  • Restart the database,
SQL> startup force
ORACLE instance started.

Total System Global Area 4294967296 bytes
Fixed Size 8628936 bytes
Variable Size 603981112 bytes
Database Buffers 1526726656 bytes
Redo Buffers 8146944 bytes
In-Memory Area 2147483648 bytes
Database mounted.
Database opened.
  • Check the table data present in tablespace ‘ENCRYPT_TS’        
SQL> SELECT * FROM hari.student_demo;

NAME  ROLLNO  DEPT
----- ------- ----
hari  101     MCA

Local auto-login software keystores:

 This type of keystores have auto-login functionality on the computer where these are created and these cannot be opened from any other computer.

  • To create a local auto-login keystore, use following syntax
SQL> ADMINISTER KEY MANAGEMENT CREATE LOCAL AUTO_LOGIN KEYSTORE FROM KEYSTORE 'wallet location' IDENTIFIED BY password;

But for creation of LOCAL AUTO_LOGIN keystore,the wallet type before has to be PASSWORD ( password-based keystore).

  • Close the Password based software keystore :
SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE IDENTIFIED BY wallet$123;

keystore altered.

This above option can be enable when the wallet_type is PASSWORD.

To close a password-based software keystore or a hardware keystore, specify the Keystore password.

  • Close the Autologin based software keystore :
SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE;

keystore altered.

To close an auto-login keystore, do not specify Keystore password.Before you close an auto-login keystore, check the WALLET_TYPE  column of the v$encryption_wallet view. If it returns  AUTOLOGIN, then you can close the keystore. Otherwise, if you attempt to close the keystore, then an error occurs.

 

Catch Me On:- Hariprasath Rajaram 

LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/ Facebook:https://www.facebook.com/HariPrasathdba                        FB Group:https://www.facebook.com/groups/894402327369506/             FB Page :https://www.facebook.com/dbahariprasath/?                 Twitter :https://twitter.com/hariprasathdba

Oracle Partitioning Tips And Tricks

Range Partition :- Range Partitioning maps data to partitions based on ranges of partition key values that you establish for each partition. It is the most common type of partitioning and is often used with dates.

Create a range partition table,

SQL> create table details(order_id number,order_date date) partition by range (order_date) (partition p1 values less than ('01-jan-2018') tablespace t1,partition p2 values less than ('01-mar-2018') tablespace t2,partition p3 values less than ('01-aug-2018') tablespace t3,partition p4 values less than ('01-dec-2018') tablespace t4 );

Table created.

Insert data into partition 1

SQL> insert into details values(1,'10-dec-2017');

1 row created.

SQL> select * from details partition(p1);

ORDER_ID   ORDER_DAT
---------- ---------
1          10-DEC-17

Insert data into partition 2

SQL> insert into details values(2,'10-feb-2018');

1 row created.

SQL> select * from details partition(p2);

ORDER_ID   ORDER_DAT
---------- ---------
2          10-FEB-18

Insert data into partition 3

SQL> insert into details values(3,'13-jul-2018');

1 row created.

SQL> select * from details partition(p3);

ORDER_ID   ORDER_DAT
---------- ---------
3          13-JUL-18

Insert data into partition 4

SQL> insert into details values(4,'1-nov-2018');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from details partition(p4);

ORDER_ID   ORDER_DAT
---------- ---------
4          01-NOV-18

SQL> commit;

Partition table data :

SQL> select * from details;

ORDER_ID   ORDER_DAT
---------- ---------
1          10-DEC-17
2          10-FEB-18
3          13-JUL-18
4          01-NOV-18

 

LIST PARTITION :- List Partitioning is used to list together unrelated data into partitions. It is a technique where you specify a list of discrete values for the partitioning key in the description for each partition.

Create a list partition table

SQL> CREATE TABLE sales_list (salesman_id NUMBER(5),sales_state VARCHAR2(20)) PARTITION BY LIST(sales_state)(PARTITION south VALUES ('py','tn','ap','kl','ka') tablespace t1,PARTITION east VALUES ('cac','or','bi') tablespace t2,PARTITION west VALUES ('pu','go') tablespace t3,PARTITION north VALUES ('dl','ja') tablespace t4);

Table created.

Insert data into partition table

SQL> insert into sales_list values(1,'py');

1 row created.

SQL> insert into sales_list values(2,'cac');

1 row created.

SQL> insert into sales_list values(3,'pu');

1 row created.


SQL> insert into sales_list values(4,'dl');

1 row created.

SQL> commit;

Commit complete.

Partition table data :

SQL> select * from sales_list;

SALESMAN_ID   SALES_STATE
-----------   ------------
1               py
2               cac
3               pu
4               dl
SQL> select * from sales_list partition(south);

SALESMAN_ID SALES_STATE
----------- -----------
1           py

SQL> select * from sales_list partition(east);

SALESMAN_ID SALES_STATE
----------- -----------
2           cac

SQL> select * from sales_list partition(west);

SALESMAN_ID SALES_STATE
----------- -----------
3           pu

SQL> select * from sales_list partition(north);

SALESMAN_ID SALES_STATE
----------- -----------
 4          dl

 

HASH PARTITION :

Hash partitioning based on a hash algorithm. Hash partitioning enables partitioning of data that does not lend itself to range or list partitioning. The records in a table, are partitions based on Hash value found in the value of the column, which is used for partitioning. Hash partitioning does not have any logical meaning to the partitions as do the range partitioning.

Syntax:-

CREATE TABLE table-name (...col-list...) ....
PARTITION BY HASH (col-names) PARTITIONS number-of-partitions STORE IN (tbs-name1,tbs-name2,...);

Create a hash partition table

SQL> CREATE TABLE emp (empno NUMBER(4),sal NUMBER) PARTITION BY HASH(empno) PARTITIONS 3 STORE IN (t1,t2,t3);

Table created.

Check the partition under EMP table

SQL> select partition_name from user_tab_partitions where table_name='EMP';
PARTITION_NAME
---------------
SYS_P581
SYS_P582
SYS_P583

Insert data into partition table

SQL> insert into emp values(1,11);

1 row created.

SQL> insert into emp values(2,22);

1 row created.

SQL> insert into emp values(4,44);

1 row created.

SQL> insert into emp values(3,33);

1 row created.

SQL> insert into emp values(5,55);

1 row created.

SQL> insert into emp values(6,66);

1 row created.

SQL> select * from emp;

EMPNO     SAL
-------   ----
6          66
1          11
4          44
3          33
2          22
5          55

6 rows selected.
SQL> select * from emp partition(SYS_P581);

EMPNO      SAL
---------- -----
6          66

SQL> select * from emp partition(SYS_P582);

EMPNO      SAL
---------- ----
1          11
4          44
3          33

SQL> select * from emp partition(SYS_P583);

EMPNO        SAL
-------     ----
2           22
5           55

INTERVAL PARTITION :

Interval partitioning is an enhancement to range partitioning in Oracle 11g and interval partitioning automatically creates time-based partitions as new data is added.The PARTITION BY RANGE clause is used in the normal way to identify the transition point for the partition, then the new INTERVAL clause used to calculate the range for new partitions when the values go beyond the existing transition point.

Create a table with range partition and interval clause

create table order_details(order_id number,order_date date) partition by range (order_date) INTERVAL (NUMTOYMINTERVAL(1,'MONTH')) (partition p1 values less than (TO_DATE('01-NOV-2007','DD-MON-YYYY'))) tablespace t1;

Table created.

Gather the stats

SQL> EXEC DBMS_STATS.gather_table_stats('HARI', 'ORDER_DETAILS');

PL/SQL procedure successfully completed.

Check the partition created and NUM_ROWS under USER_TAB_PARTITIONS

COLUMN table_name FORMAT A20
COLUMN partition_name FORMAT A20
COLUMN high_value FORMAT A40
SELECT table_name, partition_name, high_value, num_rows FROM user_tab_partitions where table_name='ORDER_DETAILS';


TABLE_NAME      PARTITION  HIGH_VALUE			NUM_ROWS	
--------------- ---------- ---------------------------  --------
ORDER_DETAILS	P1	 TO_DATE(' 2007-11-01 00:00:00'    0 

Insert data with a ORDER_DATE  less than  ’01-NOV-2007′ the data will be placed in the existing partition p1 and no new partitions will be created.

INSERT INTO order_details VALUES (1, TO_DATE('16-OCT-2007', 'DD-MON-YYYY'));

1 row created.

COMMIT;

Commit complete.

EXEC DBMS_STATS.gather_table_stats('HARI', 'ORDER_DETAILS');

PL/SQL procedure successfully completed.

Check the NUM_ROWS count

SELECT table_name, partition_name, high_value, num_rows FROM user_tab_partitions where table_name='ORDER_DETAILS';

TABLE_NAME      PARTITION  HIGH_VALUE                    NUM_ROWS 
--------------- ---------- ---------------------------   --------
ORDER_DETAILS      P1      TO_DATE(' 2007-11-01 00:00:00'   1

If we add data beyond the range of the existing partition, a new partition is created ie., 01-JAN-2008

If we insert data for two months after the current largest transition point, only the required partition is created, not the intervening partitions ie. 2008-02-01

SQL> INSERT INTO order_details VALUES (5, TO_DATE('01-JAN-2008', 'DD-MON-YYYY'));

1 row created.

SQL> INSERT INTO order_details VALUES (6, TO_DATE('31-JAN-2008', 'DD-MON-YYYY'));

1 row created.
SQL> commit;

Commit complete.

SQL> EXEC DBMS_STATS.gather_table_stats('HARI', 'ORDER_DETAILS');

PL/SQL procedure successfully completed.

 

SQL> SELECT table_name,partition_name,high_value,interval from user_tab_partitions where table_name='ORDER_DETAILS';

TABLE_NAME    PARTI HIGH_VALUE                               INT
----------    ----- ---------------------------------------- ---
ORDER_DETAILS P1    TO_DATE(' 2007-11-01 00:00:00', 'SYYYY-M  NO
M-DD HH24:MI:SS',   'NLS_CALENDAR=GREGORIA


ORDER_DETAILS SYS_P TO_DATE(' 2008-02-01 00:00:00', 'SYYYY-M YES
              625   M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

Restrictions in Interval partitioning :

  • Interval partitioning is restricted to a single partition key that must be a numerical or date range.
  • At least one partition must be defined when the table is created.
  • Interval partitioning is not supported for index-organized tables.
  • You cannot create a domain index on an interval partitioned table.
  • Interval partitioning can be used as the primary partitioning mechanism in composite partitioning, but it can’t be used at the subpartition level.
  • A MAXVALUE partition cannot be defined for an interval partitioned table.
  • NULL values are not allowed in the partition column.

SYSTEM PARTITIONING  :

There are scenarios where a database developer or database designer is not able to make a logical way to partition a huge table. Oracle 11g has provided us a way to define partitions in an intelligent manner by System Partitioning, where application needs to control destination partition for a specific record. The DBA just needs to define the partitions.

Table Creation

SQL> create table system_order_details(order_id number,order_date date) partition by SYSTEM (PARTITION p1,PARTITION p2) tablespace t1;

Table created.

The partition must be explicitly defined in all insert statements or an error is produced.

SQL> INSERT INTO system_order_details VALUES (1, SYSDATE);
INSERT INTO system_order_details VALUES (1, SYSDATE)
*
ERROR at line 1:
ORA-14701: partition-extended name or bind variable must be used for DMLs on
tables partitioned by the System method

The PARTITION clause is used to define which partition the row should be placed in.

SQL> INSERT INTO system_order_details PARTITION (p1) VALUES (1, SYSDATE);

1 row created.

SQL> INSERT INTO system_order_details PARTITION (p2) VALUES (2, SYSDATE);

1 row created.

SQL> commit;

Commit complete.

SQL> EXEC DBMS_STATS.gather_table_stats('HARI', 'system_order_details');

PL/SQL procedure successfully completed.

Check the created system table partitions

SQL> COLUMN table_name FORMAT A25
COLUMN partition_name FORMAT A20
COLUMN high_value FORMAT A10

SELECT table_name, partition_name, high_value, num_rows FROM user_tab_partitions where table_name='SYSTEM_ORDER_DETAILS';

TABLE_NAME                PARTITION_NAME   HIGH_VALUE  NUM_ROWS
------------------------- ---------------  ----------  --------
SYSTEM_ORDER_DETAILS      P1                                 1
SYSTEM_ORDER_DETAILS      P2                                 1

Notice that the HIGH_VALUE for the partitions is blank.

The PARTITION clause is optional for update and delete statements, but omitting this clause will force all partitions to be scanned, since there is no way perform automatic partition pruning when the database has no control over row placement. When the PARTITION  clause is used, you must be sure to perform the operation against the correct partition.

For Delete and Update statements,

SQL> DELETE FROM SYSTEM_ORDER_DETAILS PARTITION (p2) WHERE order_id = 1;

0 rows deleted.

SQL> UPDATE SYSTEM_ORDER_DETAILS PARTITION (p1) SET order_id =3 WHERE order_id=2;

0 rows updated.

The PARTITION  clause can also be used to check specific partitions.

SQL> SELECT * FROM SYSTEM_ORDER_DETAILS PARTITION (p1);

ORDER_ID   ORDER_DAT
---------- ---------
1          24-SEP-18

When selecting, updating, or deleting data from a system partitioned object, the best practice is to always specify the partition to avoid scanning all partitions.  The use of system partitioning should be viewed with scrutiny by a DBA since it transfers responsibility of optimal use of partitions away from the DBA and to the developers and applications. 

REFERENCE PARTITIONING :-

Reference partitioning is for a child table is inherited from the parent table through a primary key – foreign key relationship. The partitioning keys are not stored in actual columns in the child table.

The child table is partitioned using the same partitioning key as the parent table without having to duplicate the key columns.Partition maintenance operations performed on the parent table are reflected on the child table, but no partition maintenance operations are allowed on the child table.

Parent table creation

CREATE TABLE parent_tab (
  id           NUMBER NOT NULL,
  code         VARCHAR2(10) NOT NULL,
  description  VARCHAR2(50),
  created_date DATE,
  CONSTRAINT parent_tab_pk PRIMARY KEY (id)
)
PARTITION BY RANGE (created_date)
(
   PARTITION part_2007 VALUES LESS THAN (TO_DATE('01-JAN-2008','DD-MON-YYYY')),
   PARTITION part_2008 VALUES LESS THAN (TO_DATE('01-JAN-2009','DD-MON-YYYY'))
);

Child table creation 

CREATE TABLE child_tab (
  id             NUMBER NOT NULL,
  parent_tab_id  NUMBER NOT NULL,
  code           VARCHAR2(10),
  description    VARCHAR2(50),
  created_date   DATE,
  CONSTRAINT child_tab_pk PRIMARY KEY (id),
  CONSTRAINT child_parent_tab_fk FOREIGN KEY (parent_tab_id)
                               REFERENCES parent_tab (id))
PARTITION BY REFERENCE (child_parent_tab_fk);

Insert data in parent table

INSERT INTO parent_tab VALUES (1, 'ONE', '1 ONE', SYSDATE);
INSERT INTO parent_tab VALUES (2, 'TWO', '2 TWO', SYSDATE);
INSERT INTO parent_tab VALUES (3, 'THREE', '3 THREE',ADD_MONTHS(SYSDATE,12));

Insert data in child table

INSERT INTO child_tab VALUES (1, 1, 'ONE', '1 1 ONE', SYSDATE);
INSERT INTO child_tab VALUES (2, 3, 'TWO', '2 3 TWO', SYSDATE);
INSERT INTO child_tab VALUES (3, 3, 'THREE', '3 3 THREE', SYSDATE);
COMMIT;

Child records that foreign key to rows in the first partition of the parent table should be placed in the first partition of the child table.So we insert two rows into the first partition

Gather stats for both tables 

SQL> exec dbms_stats.gather_table_stats(ownname =>user,tabname=>PARENT_TAB’);

Procédure PL/SQL successfully completed.

SQL> exec dbms_stats.gather_table_stats(ownname =>user,tabname=>CHILD_TAB’); 

Procédure PL/SQL successfully completed.

We now expect the parent table to have 2 records in the 2007 partition and 1 in the 2008 partition, while the child table should have 1 row in the 2007 partition and 2 rows in the 2008 partition. The following query confirms out expectation.

SELECT table_name, partition_name, high_value, num_rows
FROM   user_tab_partitions
ORDER BY table_name, partition_name;

TABLE_NAME   PARTITION_NAME HIGH_VALUE                        NUM_ROWS         
------------ -------------- -------------------------------- ----------
CHILD_TAB   PART_2007                                              1                1
CHILD_TAB   PART_2008                                              2               2
PARENT_TAB  PART_2007     TO_DATE(' 2008-01-01 00:00:00',          2
                          'SYYYY-MM-DD HH24:MI:SS',                   
                           'NLS_CALENDAR=GREGORIA

PARENT_TAB  PART_2008     TO_DATE(' 2009-01-01 00:00:00',          1          
                          'SYYYY-MM-DD HH24:MI:SS',                      
                           'NLS_CALENDAR=GREGORIA

4 rows selected.

 

COMPOSITE PARTITIONING :-

Composite partitioning is a combination of the basic partitioning techniques of Range, List, Hash, and Interval Partitionings.

Composite Partitioning:

RANGE-HASH PARTITION:

This is basically a combination of range and hash partitions. The data is divided using the range partition and then each range partitioned data is further subdivided into a hash partition using hash key values. All sub partitions, together, represent a logical subset of the data.

SQL> CREATE TABLE rng_hash (cust_id NUMBER(10),time_id DATE) PARTITION BY RANGE(time_id) SUBPARTITION BY HASH(cust_id) SUBPARTITION TEMPLATE(SUBPARTITION sp1 TABLESPACE t1,SUBPARTITION sp2 TABLESPACE t2,SUBPARTITION sp3 TABLESPACE t3)(PARTITION R1 VALUES LESS THAN ('01-apr-2010'),PARTITION R2 VALUES LESS THAN ('01-aug-2010'),PARTITION R3 VALUES LESS THAN ('01-dec-2010'),PARTITION R4 VALUES LESS THAN(MAXVALUE));

Table created.

 

RANGE-LIST PARTITION:

This is a combination of Range and List partitions, first the data is divided using the Range partition and then each Range partitioned data is further subdivided into List partitions using list key values. Each subpartition individually represents logical subset of the data not like composite Range-Hash Partition.

SQL> CREATE TABLE rng_list (cust_state VARCHAR2(2),time_id DATE)PARTITION BY RANGE(time_id)SUBPARTITION BY LIST (cust_state)SUBPARTITION TEMPLATE(SUBPARTITION south VALUES ('tn','py') TABLESPACE t1,SUBPARTITION east VALUES ('wb','bi') TABLESPACE t2,SUBPARTITION north VALUES ('jm','dl') TABLESPACE t3)(PARTITION a1 VALUES LESS THAN ('01-apr-2010'),PARTITION a2 VALUES LESS THAN ('01-aug-2010'),PARTITION a3 VALUES LESS THAN ('01-dec-2010'),PARTITION a4 VALUES LESS THAN(MAXVALUE));

Table created.

COMPRESS :-

Create a COMPRESS table,

SQL> CREATE TABLE sales (region VARCHAR2(10)) COMPRESS PARTITION BY LIST (region) (PARTITION southwest VALUES ('SOUTHWEST'),PARTITION northeast VALUES ('NORTHEAST') NOCOMPRESS,PARTITION western VALUES ('WESTERN'));

Table created.

Check the COMPRESS and NOCOMPRESS partition

SELECT table_name, partition_name, compression, compress_for FROM user_tab_partitions where table_name='SALES';

TABLE_NAME   PARTITION_NAME       COMPRESS     COMPRESS_FOR
----------   -------------------- --------     ------------
SALES        NORTHEAST            DISABLED
SALES        SOUTHWEST            ENABLED      BASIC
SALES        WESTERN              ENABLED      BASIC

 

Catch Me On:- Hariprasath Rajaram

Telegram:https://t.me/joinchat/I_f4DhGF_Zifr9YZvvMkRg
LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter: https://twitter.com/hariprasathdba