OCM Exam Topic – Creating composite partitions (part 1)

OCM Tips and Tricks

by Kamran Agayev on June 12, 2018 0 Comments

Reference:

11g new feature: Extended Composite Partitioning (Overview, Example and Use) (Doc ID 943567.1)

Examples For Creating Partitioned table With Different Partitioning Strategies (Doc ID 733311.1)

11g Partitioning Enhancements (Doc ID 452447.1)

To get more detailed information and SQL syntax related with composite partitioning tables, refer to the documentation that is provided in the beginning of the chapter and go to “Creating Composite Partitioned Tables” section. Check the following documentation to get more detailed information about the composite partitioning:

Oracle® Database VLDB and Partitioning Guide -> Creating Partitions

Sometimes, defining only one partition is not enough. By creating a composite partition, the table is firstly partitioned by the first data distribution method, and then each partition is subpartitioned by the second data distribution method.

In this and next post, we will talk about the following composite partitioned tables:

Range-hash partitioning
Range-list partitioning
Range-range partitioning
List-range partitioning
List-hash partitioning
List-list partitioning
Hash-hash partitioning
Interval-list partitioning
Interval-range partitioning
Interval-hash partitioning

Note: Only Range-Hash and Range-List composite partitions can be created from OEM.

Range-Hash partitioning

To create a range-hash partitioned table, use PARTITION BY RANGE SUBPARTITION BY HASH syntax as follows:

SQL> CREATE TABLE tbl_range_hash_part
PARTITION BY RANGE
(object_id)
SUBPARTITION BY HASH (namespace)
SUBPARTITIONS 4
(
PARTITION part1 VALUES LESS THAN (1000),
PARTITION part2 VALUES LESS THAN (10000),
PARTITION part3 VALUES LESS THAN (30000),
PARTITION part4 VALUES LESS THAN (maxvalue))
AS
SELECT * FROM dba_objects;
Table created.
SQL>

Gather the statistics of the table to update the statistics at DBA_TAB_PARTITIONS:

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(‘sys’,’tbl_range_hash_part’);
PL/SQL procedure successfully completed.

To get the list of range partitions and the data distribution, query DBA_TAB_PARTITIONS view as follows:

SQL> SELECT table_name,
partition_name,
subpartition_count,
num_rows,
blocks
FROM dba_tab_partitions
WHERE table_name = ‘TBL_RANGE_HASH_PART’;

TABLE_NAME PARTITION_NAME SUBPARTITION_COUNT NUM_ROWS BLOCKS
------------------------- ---------------------- ---------------- ---------- ---------
TBL_RANGE_HASH_PART PART1 4 942 12
TBL_RANGE_HASH_PART PART2 4 8763 113
TBL_RANGE_HASH_PART PART3 4 19802 287
TBL_RANGE_HASH_PART PART4 4 45740 666

As you see, each partition has 4 has subpartitions. To get the list of all subpartitions of this table, query DBA_TAB_SUBPARTITIONS view as follows:

SQL> SELECT table_owner,
table_name,
partition_name,
subpartition_name
FROM dba_tab_subpartitions
WHERE table_name = ‘TBL_RANGE_HASH_PART’;

TABLE_OWNER TABLE_NAME PARTITION_NAME SUBPARTITION_NAME
---------------- -------------------- -------------- --------------
SYS TBL_RANGE_HASH_PART PART1 SYS_SUBP61
SYS TBL_RANGE_HASH_PART PART1 SYS_SUBP62
SYS TBL_RANGE_HASH_PART PART1 SYS_SUBP63
SYS TBL_RANGE_HASH_PART PART1 SYS_SUBP64
SYS TBL_RANGE_HASH_PART PART2 SYS_SUBP65
SYS TBL_RANGE_HASH_PART PART2 SYS_SUBP66
SYS TBL_RANGE_HASH_PART PART2 SYS_SUBP67
SYS TBL_RANGE_HASH_PART PART2 SYS_SUBP68
SYS TBL_RANGE_HASH_PART PART3 SYS_SUBP69
SYS TBL_RANGE_HASH_PART PART3 SYS_SUBP70
SYS TBL_RANGE_HASH_PART PART3 SYS_SUBP71
SYS TBL_RANGE_HASH_PART PART3 SYS_SUBP72
SYS TBL_RANGE_HASH_PART PART4 SYS_SUBP73
SYS TBL_RANGE_HASH_PART PART4 SYS_SUBP74
SYS TBL_RANGE_HASH_PART PART4 SYS_SUBP75
SYS TBL_RANGE_HASH_PART PART4 SYS_SUBP76
16 rows selected.
SQL>

Range-list partitioning

In some cases, you may have a huge range partitioned table that can also be subpartitioned with a list partition. Use PARTITION BY RANGE SUBPARTITION BY LIST syntax to create a range-list partition as follows:

SQL> CREATE TABLE tbl_range_list_part
PARTITION BY RANGE (object_id)
SUBPARTITION BY LIST (object_type)
(PARTITION part1
VALUES LESS THAN (100)
(
SUBPARTITION part1_sub_index VALUES (‘INDEX’),
SUBPARTITION part1_sub_table VALUES (‘TABLE’)),
PARTITION part2
VALUES LESS THAN (200)
(
SUBPARTITION part2_sub_index VALUES (‘INDEX’),
SUBPARTITION part2_sub_table VALUES (‘TABLE’)),
PARTITION part3 VALUES LESS THAN (maxvalue))
AS
SELECT * FROM dba_objects where object_type in (‘INDEX’,’TABLE’);
Table created.
SQL>

You can get count of rows in any subpartition as follows:

SQL> SELECT COUNT (1) FROM tbl_range_list_part SUBPARTITION (part1_sub_index);
COUNT(1)
----------
56

SQL> SELECT COUNT (1) FROM tbl_range_list_part SUBPARTITION (part2_sub_table);
COUNT(1)
----------
40

Range-range partitioning

To create a range-range partitioned table, use PARTITION BY RANGE SUBPARTITION BY RANGE syntax as follows:

SQL> CREATE TABLE tbl_range_range_part
PARTITION BY RANGE (object_id)
SUBPARTITION BY RANGE (namespace)
(PARTITION part1
VALUES LESS THAN (1000)
(
SUBPARTITION part1_sub1 VALUES LESS THAN (4),
SUBPARTITION part1_sub2 VALUES LESS THAN (MAXVALUE)),
PARTITION part2
VALUES LESS THAN (maxvalue)
(
SUBPARTITION part2_sub1 VALUES LESS THAN (10),
SUBPARTITION part2_sub2 VALUES LESS THAN (MAXVALUE)))
AS
SELECT * FROM dba_objects;
Table created.
SQL>

Now query DBA_TAB_PARTITIONS and DBA_TAB_SUBPARTITIONS view to get list of partitions and subpartitions:

SQL> SELECT TABLE_NAME, PARTITION_NAME, SUBPARTITION_COUNT
FROM dba_tab_partitions
WHERE table_name = ‘TBL_RANGE_RANGE_PART’;

TABLE_NAME PARTITION_NAME SUBPARTITION_COUNT
------------------------------ ------------------------------ ------------------
TBL_RANGE_RANGE_PART PART1 2
TBL_RANGE_RANGE_PART PART2 2

SQL> SELECT table_name, partition_name, subpartition_name
FROM DBA_TAB_SUBPARTITIONS
WHERE TABLE_NAME = ‘TBL_RANGE_RANGE_PART’;

TABLE_NAME PARTITION_NAME SUBPARTITION_NAME
------------------------------ ------------------------------ ------------------------
TBL_RANGE_RANGE_PART PART1 PART1_SUB1
TBL_RANGE_RANGE_PART PART1 PART1_SUB2
TBL_RANGE_RANGE_PART PART2 PART2_SUB1
TBL_RANGE_RANGE_PART PART2 PART2_SUB2

SQL> SELECT COUNT (1) FROM tbl_range_range_part SUBPARTITION (part2_sub2);
COUNT(1)
----------
1327
SQL>

OCM exam topic – Creating Interval, Reference and System partitioned tables – part 2

OCM Tips and Tricks

by Kamran Agayev on May 21, 2018 0 Comments

In this post I will show how to create Interval, Reference and System partitioned tables.

Creating Interval partitioned table

References:

11g New Features:INTERVAL PARTITIONING (Doc ID 805976.1)
11g Feature: Interval Partitioning Example (Doc ID 466352.1)
Interval Partitioning By Week (Doc ID 757754.1)
11g Partitioning Enhancements (Doc ID 452447.1)
Interval Partitioning Essentials – Common Questions – Top Issues (Doc ID 1479115.1)
Interval Partitioning By Week (Doc ID 757754.1)

Interval partitioning is an extension to the range partitioning where new partitions are created automatically when the new data doesn’t fit any available partition. Instead of getting “ORA-14400: inserted partition key does not map to any partition” error, a new partition
is created based on an interval criterion.
It is not possible to create an interval partition from OEM. To create it from a command line, refer to the main partitioning documentation that was provided above and go to “Creating Interval-Partitioned Tables” section.

Below, we will create an interval partitioned table with one partition, and the next partitions will be created automatically:
SQL> CREATE TABLE tbl_interval_part
( id NUMBER,
name VARCHAR2 (20),
surname VARCHAR (20),
ddate DATE
)
PARTITION BY RANGE (ddate)
INTERVAL ( NUMTOYMINTERVAL (1, ‘MONTH’) )
(PARTITION part1 VALUES LESS THAN (TO_DATE (‘05052016’, ‘ddmmyyyy’)));
Table created.
SQL>

Now query DBA_TAB_PARTITIONS to get the list of all partitions and insert one row where the value of the date column is less than the value of the partition part1.

SQL> COL partition_name FORMAT a20
COL high_value FORMAT a50 WORD_WRAPPED
SELECT partition_name, high_value
FROM dba_tab_partitions
WHERE table_name = ‘TBL_INTERVAL_PART’
ORDER BY partition_position;
PARTITION_NAME HIGH_VALUE
-------------------- --------------------------------------------------
PART1 TO_DATE(‘ 2016-05-05 00:00:00’, ‘SYYYY-MM-DDHH24:MI:SS’, ‘NLS_CALENDAR=GREGORIA


SQL> INSERT INTO tbl_interval_part VALUES (1,’first’,’row’,TO_DATE (‘01052016’, ‘ddmmyyyy’));
1 row created.

Now insert the second row with the date value greater than the partition value:

SQL> INSERT INTO tbl_interval_part VALUES (2,’second’,’row’,TO_DATE (‘03062016’, ‘ddmmyyyy’));
1 row created.

A new partition with “interval one month” will be created automatically.

Here’s what we have so far:
– 05-05-2016 is the partition with the highest value
– Interval is one month
– Inserted row is 03-06-2016. It means that new partition will be created with the highest value 05-06-2016 (05-05-2016+1 month).
Let’s query DBA_TAB_PARTITIONS view to get information about the new partition:

SQL> COL partition_name FORMAT a20
COL high_value FORMAT a50 WORD_WRAPPED
SELECT partition_name, high_value
FROM dba_tab_partitions
WHERE table_name = ‘TBL_INTERVAL_PART’
ORDER BY partition_position;
PARTITION_NAME HIGH_VALUE
-------------------- --------------------------------------------------
PART1 TO_DATE(‘ 2016-05-05 00:00:00’, ‘SYYYY-MM-DDHH24:MI:SS’, ‘NLS_CALENDAR=GREGORIA
SYS_P57 TO_DATE(‘ 2016-06-05 00:00:00’, ‘SYYYY-MM-DDHH24:MI:SS’, ‘NLS_CALENDAR=GREGORIA

Now let’s insert a third row with the value greater that the highest boundary value of the partition column. The third partition will be created automatically:

SQL> INSERT INTO tbl_interval_part VALUES (3,’third’,’row’,TO_DATE (‘06062016’, ‘ddmmyyyy’));
1 row created.


SQL> SELECT partition_name, high_value
FROM dba_tab_partitions
WHERE table_name = ‘TBL_INTERVAL_PART’
ORDER BY partition_position;

PARTITION_NAME HIGH_VALUE
-------------------- --------------------------------------------------
PART1 TO_DATE(‘ 2016-05-05 00:00:00’, ‘SYYYY-MM-DDHH24:MI:SS’, ‘NLS_CALENDAR=GREGORIA
SYS_P57 TO_DATE(‘ 2016-06-05 00:00:00’, ‘SYYYY-MM-DDHH24:MI:SS’, ‘NLS_CALENDAR=GREGORIA
SYS_P58 TO_DATE(‘ 2016-07-05 00:00:00’, ‘SYYYY-MM-DDHH24:MI:SS’, ‘NLS_CALENDAR=GREGORIA
SQL>

Now, try to insert a row with a few months greater than the highest value of the partition, and see what happens:

SQL> INSERT INTO tbl_interval_part VALUES (4, ‘fourth’,’row’,TO_DATE (‘07092016’,‘ddmmyyyy’));
1 row created.


SQL> SELECT partition_name, high_value
FROM dba_tab_partitions
WHERE table_name = ‘TBL_INTERVAL_PART’
ORDER BY partition_position;
PARTITION_NAME HIGH_VALUE
-------------------- --------------------------------------------------
PART1 TO_DATE(‘ 2016-05-05 00:00:00’, ‘SYYYY-MM-DDHH24:MI:SS’, ‘NLS_CALENDAR=GREGORIA
SYS_P57 TO_DATE(‘ 2016-06-05 00:00:00’, ‘SYYYY-MM-DDHH24:MI:SS’, ‘NLS_CALENDAR=GREGORIA
SYS_P58 TO_DATE(‘ 2016-07-05 00:00:00’, ‘SYYYY-MM-DDHH24:MI:SS’, ‘NLS_CALENDAR=GREGORIA
SYS_P59 TO_DATE(‘ 2016-10-05 00:00:00’, ‘SYYYY-MM-DDHH24:MI:SS’, ‘NLS_CALENDAR=GREGORIA

A new partition might also be created in the middle if the date value of the new row is one month less than the boundary partition of the table:

SQL> INSERT INTO tbl_interval_part VALUES (5,’fifth’,’row’,TO_DATE (‘08072016’, ‘ddmmyyyy’));
1 row created.


SQL> SELECT partition_name, high_value
FROM dba_tab_partitions
WHERE table_name = ‘TBL_INTERVAL_PART’
ORDER BY partition_position;
PARTITION_NAME HIGH_VALUE
-------------------- --------------------------------------------------
PART1 TO_DATE(‘ 2016-05-05 00:00:00’, ‘SYYYY-MM-DDHH24:MI:SS’, ‘NLS_CALENDAR=GREGORIA
SYS_P57 TO_DATE(‘ 2016-06-05 00:00:00’, ‘SYYYY-MM-DDHH24:MI:SS’, ‘NLS_CALENDAR=GREGORIA
SYS_P58 TO_DATE(‘ 2016-07-05 00:00:00’, ‘SYYYY-MM-DDHH24:MI:SS’, ‘NLS_CALENDAR=GREGORIA
SYS_P60 TO_DATE(‘ 2016-08-05 00:00:00’, ‘SYYYY-MM-DDHH24:MI:SS’, ‘NLS_CALENDAR=GREGORIA
SYS_P59 TO_DATE(‘ 2016-10-05 00:00:00’, ‘SYYYY-MM-DDHH24:MI:SS’, ‘NLS_CALENDAR=GREGORIA
SQL>

Note: If you are asked to create a monthly or yearly partition, use NUMTOYMINTERVAL which converts a given value to an interval year to month literal. When creating a daily or weekly partitions, use NUMTODSINTERVAL which converts a given value to an interval day to second

You can check “Database SQL Language Reference” to get more information about NUMTOYMINTERVAL and NUMTODSINTERVAL functions.
Interval partitioning cannot be specified at subpartition level. You can specify only one partition key with the type either NUMBER or DATE.
Run the following command to disable an interval partitioning on the specific table:

SQL> ALTER TABLE <table name> SET INTERVAL ();

Creating Reference partitioned table

Reference:

Reference Partitioning Examples in Oracle 11G and above (Doc ID 761251.1)
11g Partitioning Enhancements (Doc ID 452447.1)

With parent-child relationship, a child table now can inherit partitioning key from the parent table without duplicating the key columns.

To get the syntax of reference partitioned table, go to the documentation that is provided in the beginning of this section and move to the “Creating Reference-Partitioned Tables” part.
To see how it works, we need to create one parent and one child table. Create a parent table as follows:

SQL> CREATE TABLE TBL_REF_PART
(
id NUMBER PRIMARY KEY,
name VARCHAR2 (20),
surname VARCHAR2 (20)
)
PARTITION BY RANGE (id)
(PARTITION p1 VALUES LESS THAN (10), PARTITION p2 VALUES LESS THAN (20));
Table created.
SQL>

Now create a child table with foreign key to the parent table and use PARTITION BY REFERENCE syntax to inherit a partitioning from the parent table as follows:

SQL> CREATE TABLE tbl_ref_part_child
(
id NUMBER PRIMARY KEY,
name VARCHAR2 (20),
CONSTRAINT fk_id FOREIGN KEY (id) REFERENCES tbl_ref_part (id)
)
PARTITION BY REFERENCE (fk_id);
Table created.
SQL>

If you query DBA_TAB_PARTITIONS view, you will see the list of partitions that belongs to the child table are created automatically:

SQL> SET LINESIZE 150
SQL> SELECT table_name, partition_name
FROM dba_tab_partitions
WHERE table_name LIKE ‘TBL_REF%’;
TABLE_NAME PARTITION_NAME
------------------------------ --------------------
TBL_REF_PART P1
TBL_REF_PART P2
TBL_REF_PART_CHILD P1
TBL_REF_PART_CHILD P2

Now query DBA_PART_TABLES view to see the foreign key constraint name on which the
partition was created:

SQL> SELECT table_name, partitioning_type, ref_ptn_constraint_name
FROM dba_part_tables
WHERE table_name LIKE ‘TBL_REF%’;
TABLE_NAME PARTITION REF_PTN_CONSTRAINT_NAME
------------------------------ --------- ------------------------------
TBL_REF_PART RANGE
TBL_REF_PART_CHILD REFERENCE FK_ID

Creating System partitioned table

Reference:

Documentation – Unstructured Data and Content Management->Database Data Cartridge Developer’s Guide->Using System Partitioning
11g New Features: System Partitioning (Doc ID 785462.1)
11g Partitioning Enhancements (Doc ID 452447.1)

By using system partitioning option, you don’t define a specific partitioning key and create many physical partitions. When you insert a row, however, you need to specify the partition which will receive the data. Use PARTITION BY SYSTEM syntax of the CREATE TABLE command to create a
system partitioned table as follows:

SQL> CREATE TABLE tbl_sys_part
(
id NUMBER,
name VARCHAR2 (10),
surname VARCHAR2 (10)
)
PARTITION BY SYSTEM
(PARTITION part1
TABLESPACE SYSTEM,
PARTITION part2
TABLESPACE users);
Table created.
SQL>

Query USER_PART_TABLES view to see the type of the partition:

SQL> SELECT partitioning_type
FROM user_part_tables
WHERE table_name =’TBL_SYS_PART’
PARTITION
---------
SYSTEM
SQL>

The following error will appear if you try to insert a row to the table without specifying the partition name:

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

The partition name must be implicitly defined when inserting a row in a table:

SQL> INSERT INTO tbl_sys_part PARTITION (part1) VALUES (1, ‘first’, ‘row’);
1 row created.
SQL> INSERT INTO tbl_sys_part PARTITION (part2) VALUES (2, ‘second’, ‘row’);
1 row created.

Partition name might be omitted when deleting a row:

SQL> DELETE tbl_sys_part WHERE id = 1;
1 row deleted.

To delete all rows of the specific partition, the partition name could be specified as follows:

SQL> DELETE tbl_sys_part PARTITION (part2);
1 row deleted.
SQL>

OCM exam topic – Administer partitioned tables and indexes using appropriate methods and keys – part 1

OCM Tips and Tricks

by Kamran Agayev on May 15, 2018 2 Comments

In this and next consequence posts, I will be talking in more detail about creating different type of partitioned tables and how to manage them. I will try to skip basics and show different partition creation and management methods that I covered in OCM 11g Study Guide book

So here’s the topics we’re going to cover regarding the partition creation:

– Creating Range partitioned table
– Creating List partitioned table
– Creating Hash partitioned table
– Creating Interval partitioned table
– Creating Reference partitioned table
– Creating Composite partitioned table
– Using multicolumn partitioned keys
– Creating Virtual Column based partitioning

As a reference, please use the following documentation and metalink notes:

– Database VLDB and Partitioning Guide -> 4 Partition Administration -> Creating Partitions
– Examples For Creating Partitioned table With Different Partitioning Strategies (Doc ID 733311.1)
– Master Note for Partitioning (Doc ID 1312352.1)
– Information Center: Using Partitioning in the Oracle Database (Doc ID 1551512.2)
– 11g Partitioning Enhancements (Doc ID 452447.1)

And here’s the topics regarding the partition maintenance operations that we are going to cover in the next posts:

– Adding a new partition

– Dropping a partition

– Coalescing partitions

– Exchange partitions

– Merging partitions

– Renaming partitions

– Moving partitions

– Splitting partitions

– Truncating partitions

Creating Range partitioned table

If you have configured OEM (during the OCM exam, you should have already configured the OEM, it’s the mandatory task before the partitioning section), I would highly recommend to use OEM to create a partitioned table. I’m not going to show how to create it, as it is very easy – but will show the brief step.

To create a range partitioned table, open EM, switch to the GRIDDB database and click on Schema link. From the Database objects section, select Tables link and click Create button. Select Standard (Heap Organized) option and click Continue.

You should see Partitions tab, click on it, select the Range partition from the list and click Continue.

Provide the partition column, specify the range

On the next screen, provide “1” for the “Number of partitions” value. Check “Use MAXVALUE” option, uncheck “Automatically generate partition values” option and click Next.

Provide the tablespace name for the partitions to be stored as it is required in the OCM task and click Next.
On the last screen, click on “Insert Before” button and provide 4 partitions with different values
PART_2015 will contain all rows with DATE value before 2016 January 1: to_date(‘01-JAN-2016’,’dd-MON-yyyy’)
PART_JAN_2016 will contain all rows with DATE value for 2016 January: to_date(‘01-FEB-2016’,’dd-MON-yyyy’)

PART_FEB_2016 will contain all rows with DATE value for 2016 February:to_date(‘01-MAR-2016’,’dd-MON-yyyy’)

PART_REST will contain all rows that doesn’t match any criteria: MAXVALUE

Then click Finish and “Show SQL” button to see the SQL command of partition creation

To get the name of all partitions of the specific table, query DBA_TAB_PARTITIONS view:

SQL> SELECT partition_name FROM dba_tab_partitions WHERE table_name=’TBL_RANGE_PART’;
PARTITION_NAME
------------------------------
PART_2015
PART_JAN_2016
PART_FEB_2016
TBL_RANGE_PART_P1

Creating multi column range partition

You might be asked to create a multi column range partition during an exam. In the following example, we will specify 3 columns as a partitioning key:

CREATE TABLE TBL_RANGE_PART2
(
id NUMBER,
name VARCHAR2 (10),
surname VARCHAR2 (10),
year NUMBER,
month NUMBER,
day NUMBER
)
PARTITION BY RANGE
(year, month)
(
PARTITION JAN_2016
VALUES LESS THAN (2016,02),
PARTITION FEB_2016
VALUES LESS THAN (2016,03))

Creating List partitioned table

By using PARTITION BY LIST clause of the CREATE TABLE command, you can create a list partitioned table by specifying distinct values for the partitioning key for each partition. List partition is used to group an unsorted data. Only one single column can be specified as a key partition and the same values can’t be specified in the same or different partition. To create a List partitioned table, create a new table (TBL_LIST_PART) from EM with 4 columns with following columns:

Id: NUMBER
Name: VARCHAR2(20)
Surname: VARCHAR2(20)
Course: VARCHAR2(20)

From the partitioning columns section, select “Course” column and click Next

Provide “1” for “Number of partitions” value and click Next. Make sure “Use DEFAULT” option is selected.
Then provide name of the tablespace and click Next.

Create four partitions and provide value for each partition.

Creating Hash partitioned table

If the data doesn’t have any logical range and can’t be presented in a list, the hash partitioned can be used. If hash partition is used, the rows are mapped into corresponding partitions based on the hash value of the partitioning key.
Use PARTITION BY HASH syntax to provide the partitioning key and use PARTITIONS clause to specify number of partitions.
Creating HASH partition is from OEM is a simple process. Create a table, switch to Partitions tab and click on Create button. Select HASH partition and click Next. Provide the number of partitions and provide the tablespaces if required.

If you are asked to create a hash partitioned table without GUI, use an examples provided under “Creating a Hash Partitioned Table” topic in the documentation provided above.
Below, we create a hash partitioned table with 8 partitions based on DBA_OBJECTS view as follows:

SQL> CREATE TABLE tbl_hash_part
PARTITION BY HASH (object_id)
PARTITIONS 8
AS
SELECT * FROM dba_objects;
Table created.
SQL>

Query DBA_TAB_PARTITIONS view to get the list of all partitions:

SQL> SELECT table_name, partition_name
FROM dba_tab_partitions
WHERE table_name = ‘TBL_HASH_PART’;


TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
TBL_HASH_PART SYS_P41
TBL_HASH_PART SYS_P42
TBL_HASH_PART SYS_P43
TBL_HASH_PART SYS_P44
TBL_HASH_PART SYS_P45
TBL_HASH_PART SYS_P46
TBL_HASH_PART SYS_P47
TBL_HASH_PART SYS_P48
8 rows selected.
SQL>

If you are asked to store partitions on different tablespaces, use STORE IN syntax

SQL> CREATE TABLE tbl_hash_part1
PARTITION BY HASH (object_id)
PARTITIONS 8
store in (tbs_part1, tbs_part2, tbs_part3, tbs_part4)
AS
SELECT * FROM dba_objects;
Table created.

SQL>

Use the following syntax to store specific partition in specific tablspace:

SQL> CREATE TABLE tbl_hash_part2
PARTITION BY HASH (object_id)
(partition part1 tablespace tbs_part1, partition part2 tablespace tbs_part2)
AS
SELECT * FROM dba_objects;
Table created.

SQL>

If you want to verify the data distribution among partitions, gather the table statistics and query DBA_TAB_PARTITIONS view as follows:

SQL> EXECUTE dbms_stats.gather_table_stats(‘SYS’,’TBL_HASH_PART2’)
PL/SQL procedure successfully completed.


SQL> SELECT table_name,
partition_name,
partition_position,
num_rows
FROM user_tab_partitions
WHERE table_name = ‘TBL_HASH_PART2’;


TABLE_NAME PARTITION_NAME PARTITION_POSITION NUM_ROWS
------------------------- ------------------------- ------------------ ----------
TBL_HASH_PART2 PART1 1 37750
TBL_HASH_PART2 PART2 2 37438
SQL>

In the next part, we will talk about how to create an Interval partitioned, Reference partitioned, System partitioned, Virtual Column based partitioned tables, Composite partitioned like List-List, List-Interval, Interval-Range, Interval-hash partitioned tables and etc.

Configure data guard environment to reduce overheads of fast incremental backups on the primary database

by Kamran Agayev on October 13, 2017 0 Comments

9.2. Configure the data guard environment to reduce overheads of fast incremental backups on the primary database

To increase speed of incremental backup of standby database, block change tracking should be enabled. A detailed explanation has been provided in the Chapter 3 regarding the block change tracking feature.

To reduce the overhead of backup on the primary database, a standby database might be used to backup the database.

Switch to the standby database and enable block change tracking as follows:
SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE ‘/home/oracle/block_chg_tracking.trc’;
Database altered.

SQL> COL FILENAME FORMAT a50
SQL> SELECT * FROM v$block_change_tracking;
STATUS FILENAME BYTES
———- ————————————————– ———-
ENABLED /home/oracle/block_chg_tracking.trc 11599872

SQL>

You might be asked to recover the standby database using incremental backup of primary database during exam. To practice this case, disable shipment of archived log files to standby database, perform some log switched and delete archived log files from the primary database.

Then take an incremental backup from the primary database and apply it to the standby database as shown in the following steps.

Reference:
High Availability -> Data Guard Concepts and Administration-> 11 Using RMAN to Back Up and Restore Files -> 11.10 Using RMAN Incremental Backups to Roll Forward a Physical Standby Database

First of all, move to the primary database, disable the archivelog shipment, create archive log gap by switching some log files and deleting archived log files from primary database as follows:

SQL> SHOW PARAMETER log_archive_dest_2
NAME TYPE VALUE
———————————— ———– ——————————
log_archive_dest_2 string service=STBDB ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STBDB

Check the list of archived log files on the primary database:
SQL> ALTER SESSION SET nls_date_format=’HH24:MI:SS’;
Session altered.

SQL> SELECT sequence#, first_time, next_time FROM v$archived_log ORDER BY sequence#;
SEQUENCE# FIRST_TI NEXT_TIM
———- ——– ——–
65 13:00:55 13:05:00
66 13:05:00 13:10:45
66 13:05:00 13:10:45
67 13:10:45 13:10:46
68 13:10:46 13:12:03
SQL>

Now run the same query on the standby database:
SQL> ALTER SESSION SET nls_date_format=’HH24:MI:SS’;
Session altered.

SQL> SELECT sequence#, first_time, next_time FROM v$archived_log ORDER BY sequence#;
SEQUENCE# FIRST_TI NEXT_TIM
———- ——– ——–
63 12:57:32 12:59:27
64 12:59:27 13:00:55
65 13:00:55 13:05:00
66 13:05:00 13:10:45

As you see, 2 archived log files are missing. Now we will delete all archived log files from the primary database, enable the archive log shipping and check alert log file of standby database:

[oracle@ocmnode1 rman_backup]$ cd /home/oracle/arch/
[oracle@ocmnode1 arch]$ rm -rf *.dbf
SQL> ALTER SYSTEM SET log_archive_dest_2=’service=STBDB ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STBDB’;
System altered.
SQL>

[oracle@ocmnode2 trace]$ tail -f alert_STBDB.log
Fetching gap sequence in thread 1, gap sequence 67-68
Tue Jun 21 13:17:56 2016
FAL[client]: Failed to request gap sequence
GAP – thread 1 sequence 67-68
DBID 654484567 branch 914436829
FAL[client]: All defined FAL servers have been attempted.

As you see, we have missing archived log files that are not available at primary database.
We either need to recreate a standby database, or apply an incremental backup of the primary database to the standby database.

To get an incremental backup, we need to get SCN of the standby database. Get the SCN value from both databases:

Primary database
SQL> SELECT TO_CHAR(current_scn) FROM v$database;
TO_CHAR(CURRENT_SCN)
———————–
924379
SQL>

Standby database
SQL> SELECT TO_CHAR(current_scn) FROM v$database;
TO_CHAR(CURRENT_SCN)
—————————————-
923999
SQL>

Switch to primary database now and get an incremental backup starting from the SCN value of the standby database:
RMAN> BACKUP INCREMENTAL FROM SCN 923999 DATABASE FORMAT ‘/home/oracle/rman_backup/incr_backup_gap_%U’;

Take backup of standby control file and move it with along the backup files to the standby host:

SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS ‘/home/oracle/rman_backup/stb_control.ctl’;
Database altered.

SQL>

[oracle@ocmnode1 rman_backup]$ scp /home/oracle/rman_backup/stb_control.ctl ocmnode2:/home/oracle/rman_backup/
[oracle@ocmnode1 rman_backup]$ scp /home/oracle/rman_backup/incr_backup_gap_1* ocmnode2:/home/oracle/rman_backup/
[oracle@ocmnode1 rman_backup]$

Switch to standby, restore controlfile, mount the database and recover it:
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP NOMOUNT

[oracle@ocmnode2 admin]$ rman target /
RMAN> RESTORE CONTROLFILE FROM ‘/home/oracle/rman_backup/stb_control.ctl’;

[oracle@ocmnode2 admin]$ sqlplus / as sysdba
SQL> STARTUP FORCE NOMOUNT

SQL> ALTER DATABASE MOUNT STANDBY DATABASE;
Database altered.
SQL>

RMAN> RECOVER DATABASE NOREDO;

After the recovery completed successfully, start the apply process:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Database altered.
SQL>

Switch to primary database and switch a few log files:
SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.
SQL> /
System altered.
SQL>

Now check alert.log file at standby database to see that new archived log files are shipped and applied successfully:

[oracle@ocmnode2 trace]$ tail -f alert_STBDB.log
Check alert.log file at standby
Tue Jun 21 13:25:35 2016
Archived Log entry 2 added for thread 1 sequence 71 rlc 914436829 ID 0x27028757 dest 2:
RFS[2]: Opened log for thread 1 sequence 72 dbid 654484567 branch 914436829
Tue Jun 21 13:25:37 2016
Media Recovery Log /home/oracle/arch1_71_914436829.dbf
Media Recovery Waiting for thread 1 sequence 72 (in transit)
Archived Log entry 3 added for thread 1 sequence 72 rlc 914436829 ID 0x27028757 dest 2:
RFS[2]: Opened log for thread 1 sequence 73 dbid 654484567 branch 914436829
Tue Jun 21 13:25:47 2016
Media Recovery Log /home/oracle/arch1_72_914436829.dbf
Media Recovery Waiting for thread 1 sequence 73 (in transit)

▼Practice:
► Stop the archive log apply process and remove archived log files from the standby database that were not applied. Create a table on the primary database, take an incremental backup and recover it on standby database. Query the table on the standby database.

Practice Time
►15 min.
If you want to get more information on creating, configuring and managing Data Guard get my book from the following link:
https://www.amazon.com/Oracle-Certified-Master-Exam-Guide/dp/1536800791/

Creating and managing ACFS File System

by Kamran Agayev on September 27, 2017 0 Comments

Reference:
Documentation: Automatic Storage Management Administrator’s Guide -> 5 Introduction to Oracle
ACFS

ACFS is an ASM based clustered file system that is used to store all type of data types and is installed with Grid Infrastructure. There are different ways to create ACFS. The way how to create an ACFS file system using ASMCA utility is shown below.

ACFS is based on the volumes that are created in a disk group. So, first of all, an ASM diskgroup should be created. Then the volume will be created in an ASM diskgroup. This volume device will be used to create an ACFS. Before starting to create a new diskgroup, make sure you deleted all diskgroups that are created in the previous section.

Run asmca command to open ASM Configuration Assistant page as shown in figure 7.12.

FIGURE 7-12. ASM Configuration Assistant

Click Create button to create a new diskgroup named DF_ACFS with external redundancy and provide 2 disks as shown in Figure 7.13.

FIGURE 7-13. Creating DG_ACFS diskgroup

Now switch to Volumes tab and create a new volume based on the newly created disk group as shown in Figure 7.14.

FIGURE 7-14. Creating a Volume

Next, switch to the “ASM Cluster File Systems” tab and click Create button to create an ASM Cluster file system as in Figure 7.15.

FIGURE 7-15. Create ASM Cluster File System

Click on “Show mount command” to see the command that is needed to run to mount the ACFS on other nodes as in Figure 7.16.

FIGURE 7-16. Mount All ACFS Command

In the exam, you might be asked to create an ACFS using command line and the GUI might not be available. Check the following documentation and following steps to see how to create an ACFS using command line:

Reference:
Documentation: Automatic Storage Management Administrator’s Guide -> Basic Steps to Manage
Oracle ACFS Systems

First of all, create an ASM diskgroup by using the last two disks:

SQL> CREATE DISKGROUP dg_acfs2 EXTERNAL REDUNDANCY DISK ‘/dev/newdisk3’,’/dev/newdisk4’;
Diskgroup created.

SQL> CREATE DISKGROUP dg_acfs2 EXTERNAL REDUNDANCY DISK ‘/dev/newdisk3’,’/dev/newdisk4’ ATTRIBUTE ‘compatible.asm’ = ‘11.2’;
Diskgroup created.

SQL> ALTER DISKGROUP dg_acfs2 ADD VOLUME VL_ACFS2 SIZE 200M;
Diskgroup altered.

Run the following command to create a volume from ASMCMD utility:
ASMCMD [+] > volcreate –G data –s 200m vl_acfs2

Check if the volume device is created:
ASMCMD [+] > volinfo –G data vl_acfs2

To get more information about volume management with ASMCMD command, run it with
the help option to see the available options as follows:
[oracle@ocmnode1 ~]$ asmcmd help

< —- Output trimmed —- >
volcreate, voldelete, voldisable, volenable, volinfo
volresize, volset, volstat
[oracle@ocmnode1 ~]$

Query GV$ASM_VOLUME view to get information about volume devices:

SQL> SELECT inst_id, volume_name, volume_device, size_mb, usage, state FROM gv$asm_
volume ORDER BY inst_id, volume_name;
SQL> SET LINESIZE 150
SQL> COL volume_device FORMAT a30
INST_ID VOLUME_NAME VOLUME_DEVICE SIZE_MB USAGE STATE
— —————- ——————– ———- ———————- ——–
1 VL_ACFS /dev/asm/vl_acfs-31 256 ACFS ENABLED
1 VL_ACFS2 /dev/asm/vl_acfs2-238 256 ENABLED
SQL>

Now using the volume device and create a file system:
[root@ocmnode1 ~]# /sbin/mkfs -t acfs /dev/asm/vl_acfs2-238
mkfs.acfs: version = 11.2.0.1.0.0
mkfs.acfs: on-disk version = 39.0
mkfs.acfs: volume = /dev/asm/vl_acfs2-238
mkfs.acfs: volume size = 268435456
mkfs.acfs: Format complete.

Register the new filesystem in the ACFS mount registry:
[root@ocmnode1 ~]# /sbin/acfsutil registry -a /dev/asm/vl_acfs2-238 /u01/acfs2
acfsutil registry: mount point /u01/acfs2 successfully added to Oracle Registry

Mount the filesystem:
[root@ocmnode1 ~]# /bin/mount -t acfs /dev/asm/vl_acfs2-238 /u01/acfs2

Verify if the filesystem is mounted correctly:
[root@ocmnode1 acfs2]# mount
< — Output trimmed — >
/dev/asm/vl_acfs-31 on /u01/acfs type acfs (rw)
/dev/asm/vl_acfs2-238 on /u01/acfs2 type acfs (rw)
[root@ocmnode1 acfs2]#

Resize ACFS file system

To resize ACFS file system, use acfsutil as follows:
[oracle@ocmnode1 ~]$ /sbin/acfsutil size +100m /u01/acfs

Creating ACFS Snapshots

To create a snapshot of an ACFS filesystem, “snap create” syntax is used. Create an empty
file in the ACFS folder, and create a snapshot of the filesystem.
[oracle@ocmnode1 ~]$ touch /u01/acfs2/a
[oracle@ocmnode1 ~]$ /sbin/acfsutil snap create test_snapshot /u01/acfs2
acfsutil snap create: Snapshot operation is complete.

Once you created a snapshot, a “test_snapshot” folder is created under
/u01/acfs2/.ACFS/snaps/ folder and all files are moved from the ACFS file system to this folder.
Now remove that file and restore it from the snapshot folder:

[oracle@ocmnode1 ~]$ rm –rf /u01/acfs2/a
[oracle@ocmnode1 acfs2]$ cp /u01/acfs2/.ACFS/snaps/test_snapshot/a /u01/acfs2/

Deregsitering and Dismounting ACFS file system

To cleanup the environment or just to remove the ACFS file system, follow below steps.

Deregister the ACFS:
[oracle@ocmnode1 ~]$ /sbin/acfsutil registry -d /u01/acfs

Dismount the ACFS:
[oracle@ocmnode1 ~]$ /bin/umount /u01/acfs
[oracle@ocmnode1 ~]$ /sbin/fsck -a -v -y -t acfs /dev/asm/vl_acfs-31

Remove the file system:
[oracle@ocmnode1 acfs2]$ /sbin/acfsutil rmfs /dev/asm/vl_acfs-31

Disable a Volume:
ASMCMD> voldisable -G data vl_acfs

Delete the volume:
ASMCMD> voldisable -G data vl_acfs

▼Practice:
Practice Time
► Create an ACFS file system using ASMCA utility ►10 min.
► Create an ACFS file system manually ►15 min.
► Deregister and Dismount an ACFS file system ►7 min.

OCM Tips and Tricks – Gather statistics on a specific table without invalidating cursors

by Kamran Agayev on September 4, 2017 0 Comments

Gather statistics on a specific table without invalidating cursors

Reference:
Oracle® Database PL/SQL Packages and Types Reference 11g Release 2 -> 140 DBMS_STATS
Rolling Cursor Invalidations with DBMS_STATS.AUTO_INVALIDATE (Doc ID 557661.1)

When gathering statistics using DBMS_STATS package, NO_INVALIDATE parameter can be used to not invalidate the cursors in the cache automatically. This parameter accepts 3 values: 1) TRUE doesn’t invalidate dependent cursors 2) FALSE invalidate dependent cursors 3) AUTO_INVALIDATE which is the default value that lets Oracle decide when to invalidate the cursors. To get the default value of NO_VALIDATE parameter, use GET_PREFS function as follows:

SQL> SELECT DBMS_STATS.GET_PREFS( ‘NO_INVALIDATE’ ) FROM dual;
DBMS_STATS.GET_PREFS(‘NO_INVALIDATE’)
————————————————————
DBMS_STATS.AUTO_INVALIDATE
SQL>

In the following example NO_INVALIDATE with FALSE option will be used to show how cursors are invalidated in the cache. Let’s flush the shared pool, create a new table and gather the statistics:

SQL> ALTER SYSTEM FLUSH SHARED_POOL;
System altered.

SQL> CREATE TABLE tbl_invalidation_test AS SELECT * FROM dba_objects;
Table created.

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(NULL,’tbl_invalidation_test’);
PL/SQL procedure successfully completed.

Now query the table twice and check V$SQL to see count of executions, status of the cursor and count of invalidations:

SQL> SELECT COUNT(1) FROM tbl_invalidation_test;
COUNT(1)
———-
71972

SQL> /
COUNT(1)
———-
71972

SQL> SELECT child_number,
parse_calls,
executions,
object_status,
invalidations
FROM v$sql
WHERE sql_text = ‘SELECT COUNT(1) FROM tbl_invalidation_test’;

CHILD_NUMBER PARSE_CALLS EXECUTIONS FIRST_LOAD_TIME INVALIDATIONS
———— ———– ———- ——————- ————-
0 2 2 2016-07-19/19:09:06 0

Now gather the statistics of the table with NO_INVALIDATE=>FALSE which will invalidate the cached cursors, query the table and check V$SQL view again:
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(
NULL,’TBL_INVALIDATION_TEST’,
NO_INVALIDATE => FALSE);
PL/SQL procedure successfully completed.

SQL> SELECT COUNT(1) FROM tbl_invalidation_test;
COUNT(1)
———-
71972

SQL> SELECT child_number, parse_calls, executions, object_status, invalidations
FROM v$sql WHERE sql_id = ‘002swby2jr0qz’;

CHILD_NUMBER PARSE_CALLS EXECUTIONS OBJECT_STATUS INVALIDATIONS
———— ———– ———- ——————- ————-
0 2 2 INVALID_UNAUTH 1

As it is seen from the output, the status of the cursor is INVALID_UNAUTH and the count of invalidations is 1.
To change the default value of NO_VALIDATE parameter, use SET_PREFS procedure as follows:

SQL> EXEC DBMS_STATS.SET_PARAM(‘NO_INVALIDATE’,’FALSE’);
PL/SQL procedure successfully completed.

SQL> SELECT DBMS_STATS.GET_PREFS( ‘NO_INVALIDATE’ ) FROM dual;
DBMS_STATS.GET_PREFS(‘NO_INVALIDATE’)
——–
FALSE
SQL>

▼Practice:
Perform the following practice in 15 min.

► Flush the shared pool, create a new table based on DBA_OBJECTS and gather table statistics. Query the table twice and get count of executions and invalidations from V$SQL view. Then gather a statistics again and invalidate the cached cursors and query V$SQL view again.

If you want to get more information on this topic, get my book from the following link:
https://www.amazon.com/Oracle-Certified-Master-Exam-Guide/dp/1536800791/

OCM 11g Tips and Tricks – Creating Incrementally updated backups

by Kamran Agayev on August 21, 2017 0 Comments

To speed up the recovery process during a failure, image copy backups can be updated with daily incremental backups. By taking an incremental backup, you recover it over the image copy backups. And when database is crashed, you will only need to restore the incrementally updated backup and apply changes from the redo log files. The restored backup will already contain all incremental backups applied.

Let’s try the following scenario to show how it works and make sure we can use and implement incrementally updated backup/recovery during OCM exam:

– Create an Incrementally updated backup

– Add new tablespace with new datafile, create table on that tablespace

– Take an incrementally updated backup again

– Delete the datafile and query the table

– Restore a datafile from the backup

First of all, enable block change tracking as follows:

SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE ‘/tmp/blk_change.trc’;

Next, connect to RMAN and take an incrementally updated backup:

RMAN> RUN {
BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG ‘mydb_incr_backup’ DATABASE;
RECOVER COPY OF DATABASE WITH TAG ‘mydb_incr_backup’;
}

This script will take a LEVEL 1 image backup of the database. Now add new tablespace, create a table and query it:

SQL> CREATE TABLESPACE mytbs01 DATAFILE ‘/home/oracle/oradata/mytbs03.dbf’;
SQL> CREATE TABLE tbl_test01 (id NUMBER, name VARCHAR2(10)) TABLESPACE mytbs01;
SQL> INSERT INTO tbl_test01 VALUES(1, ‘oracle’);
SQL> COMMIT;
SQL> SELECT * FROM tbl_test01;
ID NAME
---------- ----------
1          oracle

Run the same backup command again. This time it will take an incremental backup and recover it over the image copy backup of the database:

RMAN> RUN {
BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG ‘mydb_incr_backup’ DATABASE;
RECOVER COPY OF DATABASE WITH TAG ‘mydb_incr_backup’;
}

Remove the datafile that you’ve already created, flush the buffer and query the table:

[oracle@ocm11g oradata] mv mytbs01.dbf mytbs01.dbf_backup
SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;
SQL> SELECT * FROM tbl_test01;
SELECT *FROM tbl_test01
*

ERROR at line 1:
ORA-01116: error in opening database file 5
ORA-01110: data file 5: ‘/home/oracle/oradata/mytbs01.dbf’
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

As we have incrementally updated backup, we can restore it using SWITCH DATAFILE … TO COPY command as follows:

RMAN> LIST COPY OF DATAFILE 5;
List of Datafile Copies
=======================
Key File S Completion Time Ckp SCN Ckp Time

——- —- – ————— ———- —————

21 5 A 24-OCT-14 1458646 24-OCT-14
Name: /home/oracle/flash_recovery_area/OCM_NEW/datafile/o1_mf_mytbs01_b4mqo4k5_.dbf
Tag: MYDB_INCR_BACKUP

RMAN> SWITCH DATAFILE 5 TO COPY;
datafile 5 switched to datafile copy “/home/oracle/flash_recovery_area/OCM_NEW/datafile/o1_mf_mytbs01_b4mqo4k5_.dbf”
RMAN>

Now recover the datafile and query the table:

RMAN> RECOVER DATAFILE 5;
SQL> SELECT * FROM tbl_test01;
ID NAME
---------- ----------
1          oracle

OCM Tip of the week – Implement fine-grained access control

by Kamran Agayev on April 18, 2017 0 Comments

Implement fine-grained access control

Reference:

Database Security Guide -> 7 Using Oracle Virtual Private Database to Control Data Access

Database PL/SQL Packages and Types Reference -> DBMS_RLS

Understanding Fine-Grained Access Control (DBMS_RLS) on INSERT (Doc ID 99250.1)

Master Note For Oracle Virtual Private Database ( VPD / FGAC / RLS ) (Doc ID 1352641.1)

Oracle8i Fine Grained Access Control – Working Examples (Doc ID 67977.1)

Evolution of Fine Grain Access Control FGAC Feature From 8i to 10g (Doc ID 281829.1)

Virtual Private Database (VPD) is used to prevent a user from access to unrelated data. VPD uses DBMS_RLS package that contains fine grained access control interface to limit specific data to specific users by applying the security policy directly to the database objects and modifies the SQL statement automatically before accessing the data by adding a WHERE condition that is returned from the security policy function.

Let’s create a simple VPD and see how it works. Refer to the Chapter 7 under “Database Security Guide” documentation to get syntax of main commands. In the following example, we create a table (TBL_VPD_TEST) based on ALL_OBJECTS view under the new user USR_VPD, grant SELECT access to the second user USR_VPD_2 and create a policy to prevent the second user to query some rows.

To implement this scenario, let’s create users and a table:

SQL> CREATE USER USR_VPD IDENTIFIED BY usr_vpd;
User created.

SQL> CREATE USER usr_vpd_2 IDENTIFIED BY usr_vpd_2;
User created.

SQL> GRANT CONNECT, RESOURCE TO usr_vpd, usr_vpd_2;
Grant succeeded.

SQL> CONN usr_vpd/usr_vpd;
Connected.


SQL> CREATE TABLE tbl_vpd_test
AS
SELECT * FROM all_objects;
Table created.

SQL> GRANT SELECT ON tbl_vpd_test TO usr_vpd_2;
Grant succeeded.

SQL>

Now we need to have a function which generates a dynamic WHERE clause. This function must take a schema and table name as an input, must provide a return value for the WHERE clause and must generate a valid WHERE clause. The following error will appear if the WHERE clause is invalid:

SQL>
ERROR at line 1:
ORA-28113: policy predicate has error

To access user’s session information to build up a WHERE clause, the next topic comprehensively describes the context is used. Create a function as follows:

SQL> CREATE OR REPLACE FUNCTION hide_all_except_100 (v_schema IN VARCHAR2, v_objname IN VARCHAR2)
RETURN VARCHAR2
AS
where_clause VARCHAR2 (100);
BEGIN
IF USER = ‘USR_VPD_2’
THEN
where_clause := ‘object_id=100’;
ELSE
where_clause := ‘’;
END IF;
RETURN (where_clause);
END hide_all_except_100;
/
Function created.

SQL>

 Note: Please note that the preferred method to pass a session information to the function used in the policy is to use an application context. Detailed information is provided about an application context in the next chapter.

Now DBMS_RLS.ADD_POLICY procedure to add fine-grained access control policy to the table.

The syntax of ADD_POLICY command is as follows:

DBMS_RLS.ADD_POLICY (
object_schema IN VARCHAR2 NULL,
object_name IN VARCHAR2,
policy_name IN VARCHAR2,
function_schema IN VARCHAR2 NULL,
policy_function IN VARCHAR2,
statement_types IN VARCHAR2 NULL,
update_check IN BOOLEAN FALSE,
enable IN BOOLEAN TRUE,
static_policy IN BOOLEAN FALSE,
policy_type IN BINARY_INTEGER NULL,
long_predicate IN BOOLEAN FALSE,
sec_relevant_cols IN VARCHAR2,
sec_relevant_cols_opt IN BINARY_INTEGER NULL);

Detailed information about all parameters of ADD_POLICY procedure is available in the “Database PL/SQL Packages and Types Reference” chapter “110 DBMS_RLS”. Now connect with SYS user and create a new policy that prevents user USR_VPD_2 from seeing all rows except where id=100 as follows:

SQL> BEGIN
DBMS_RLS.add_policy (object_schema => ‘USR_VPD’,
object_name => ‘TBL_VPD_TEST’,
policy_name => ‘MY_VPD_POLICY_01’,
function_schema => ‘USR_VPD’,
policy_function => ‘HIDE_ALL_EXCEPT_100’,
statement_types => ‘SELECT’);
END;
/
PL/SQL procedure successfully completed.
SQL>

You can also specify rest DML command for the statement_types parameter like INSERT, UPDATE and DELETE.

Now query table with both USR_VPD and USR_VPD_2 users:

SQL> SHOW USER
USER is “USR_VPD”

SQL> SELECT COUNT(1) FROM usr_vpd.tbl_vpd_test;
COUNT(1)
----------
55713

SQL> CONN usr_vpd_2/usr_vpd_2
Connected.

SQL> SELECT COUNT(1) FROM usr_vpd.tbl_vpd_test;
COUNT(1)
----------
1

Exam tip: Remember, if you successfully create a policy, but it doesn’t work for certain reason and you need to recreate it, use DROP_POLICY procedure to drop the policy from the table. It is easy to use, simply provide the schema name, table name and policy name as follows:

SQL> EXEC DBMS_RLS.DROP_POLICY(‘usr_vpd’,’tbl_vpd_test’,’my_vpd_policy_01’);
PL/SQL procedure successfully completed.
SQL>

update_check parameter

During an exam you might be asked to create a policy that must prevent a user from making an update that violates WHERE condition of the policy. In this special case, update_check parameter must be set to TRUE. To understand how it works, check the following example. The policy we have created earlier doesn’t contain update_check parameter and the default value of this parameter is FALSE.

Let’s connect with the second user, query the table and try to update it:

SQL> SELECT object_id FROM usr_vpd.tbl_vpd_test;
OBJECT_ID
----------
100


SQL> UPDATE usr_vpd.tbl_vpd_test SET object_id=500;
1 row updated.
SQL>

Update completed successfully. If you query the table, you will get no rows:

SQL> SELECT object_id FROM usr_vpd.tbl_vpd_test;
no rows selected


SQL> ROLLBACK
Rollback complete.

SQL> CONN / AS SYSDBA
Connected.

SQL>

Make sure the second user has a grant privilege on the table:

SQL> GRANT UPDATE ON usr_vpd.tbl_vpd_test TO usr_vpd_2;
Grant succeeded.
SQL>

Next, drop the policy and recreate it:

SQL> EXEC DBMS_RLS.DROP_POLICY(‘usr_vpd’,’tbl_vpd_test’,’my_vpd_policy_01’);
PL/SQL procedure successfully completed.


SQL> BEGIN
DBMS_RLS.add_policy (object_schema => ‘USR_VPD’,
object_name => ‘TBL_VPD_TEST’,
policy_name => ‘MY_VPD_POLICY_01’,
function_schema => ‘USR_VPD’,
policy_function => ‘HIDE_ALL_EXCEPT_100’,
update_check => TRUE,
statement_types => ‘SELECT, UPDATE’);
END;
/
PL/SQL procedure successfully completed.
SQL>

Now run update command of the row that is the only row you can view:

SQL> SELECT object_id FROM usr_vpd.tbl_vpd_test;
OBJECT_ID
----------
100


SQL> UPDATE usr_vpd.tbl_vpd_test SET object_id=500;
UPDATE usr_vpd.tbl_vpd_test SET object_id=500
*
ERROR at line 1:
ORA-28115: policy with check option violation
SQL>

Apply specific function to each row that is being exported (REMAP_DATA)

by Kamran Agayev on April 3, 2017 0 Comments

Using REMAP_DATA parameter you can apply a function to the rows that being exported and get a remapped data as a result.

In the following example, we create a function that accepts a string as an input and converts the string to uppercase. Then we use this function with REMAP_DATA parameter and remap the characters of specific column of the table.

Create a package with one function which converts input string to uppercase and return it:

SQL> CREATE OR REPLACE PACKAGE usr_dptest1.pkg_remap

AS

FUNCTION make_upper (p_val VARCHAR2)

RETURN VARCHAR2;

END;

/

Package created.

SQL> CREATE OR REPLACE PACKAGE BODY usr_dptest1.pkg_remap

AS

FUNCTION make_upper (p_val VARCHAR2)

RETURN VARCHAR2

IS

BEGIN

RETURN UPPER (p_val);

END;

END;

/

Package body created.

Create a table with 5 rows and make values of OBJ_NAME column lowercase:

SQL> CREATE TABLE usr_dptest1.all_lowers

AS

SELECT LOWER (object_name) obj_name, object_id, object_typeFROM dba_objects

WHERE ROWNUM <= 5;

Table created.




SQL> SET LINESIZE 150




SQL> COL obj_name FORMAT a15




SQL> SELECT * FROM usr_dptest1.all_lowers ;

OBJ_NAME OBJECT_ID OBJECT_TYPE

--------------- ---------- -------------------

icol$ 20 TABLE

i_user1 46 INDEX

con$ 28 TABLE

undo$ 15 TABLE

c_cobj# 29 CLUSTER

Create a parameter file and use REMAP_DATA parameter to convert values of OBJ_NAME column to uppercase:

[oracle@oemgrid ~]$ more /tmp/parfile03.dat

directory=my_dir

dumpfile=exp_remap_data_01.dmp

schemas=usr_dptest1

include=table:”IN (‘ALL_LOWERS’)”

remap_data=usr_dptest1.all_lowers.obj_name:usr_dptest1.pkg_remap.make_upper

[oracle@oemgrid ~]$

Export the dump file, drop the table, import it back and query the table. All rows of column OBJ_NAME will be converted to uppercase:

[oracle@oemgrid ~]$ expdp system/oracle parfile=/tmp/parfile03.dat

Starting “SYSTEM”.”SYS_EXPORT_SCHEMA_03”: system/******** parfile=/tmp/parfile03.dat

Estimate in progress using BLOCKS method...

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 64 KB

Processing object type SCHEMA_EXPORT/TABLE/TABLE

. . exported “USR_DPTEST1”.”ALL_LOWERS” 5.937 KB 5 rows

Master table “SYSTEM”.”SYS_EXPORT_SCHEMA_03” successfully loaded/unloaded

[oracle@oemgrid ~]$




SQL> DROP TABLE usr_dptest1.all_lowers ;

Table dropped.

SQL>




[oracle@oemgrid ~]$ impdp system/oracle directory=my_dir dumpfile=exp_remap_data_01.dmp

Master table “SYSTEM”.”SYS_IMPORT_FULL_01” successfully loaded/unloaded

Starting “SYSTEM”.”SYS_IMPORT_FULL_01”: system/******** directory=my_dir dumpfile=exp_remap_data_01.dmp

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

. . imported “USR_DPTEST1”.”ALL_LOWERS” 5.937 KB 5 rows

Job “SYSTEM”.”SYS_IMPORT_FULL_01” successfully completed at 03:37:10

[oracle@oemgrid ~]$ sqlplus / as sysdba




SQL> SET LINESIZE 150

SQL> COL obj_name FORMAT a20




SQL> SELECT * FROM usr_dptest1.all_lowers;

OBJ_NAME OBJECT_ID OBJECT_TYPE

-------------------- ---------- -------------------

ICOL$ 20 TABLE

I_USER1 46 INDEX

CON$ 28 TABLE

UNDO$ 15 TABLE

C_COBJ# 29 CLUSTER

SQL>

Encryption and Caching features of Oracle Securefile LOB segments

by Kamran Agayev on March 21, 2017 0 Comments

Encryption

You can enable block level encryption for LOB segments using ENCRYPT USING ‘encryption_algorithm” syntax. If you want to prevent the encryption explicitly, use DECRYPT option.

SecureFiles supports 4 encryption algorithms: 3DES168, AES128, AES192, AES256.

The following error will appear if you try to encrypt an object that is owned by SYS user:

ORA-28336: cannot encrypt SYS owned objects

The following error will appear if you didn’t create and open a wallet:

ORA-28365: wallet is not open

Check the following documentation to configure the wallet properly:

Reference:

Database Advanced Security Administrator’s Guide -> 3 Securing Stored Data Using Transparent Data Encryption-> 3.3.1.4 Using Transparent Data Encryption in a Multi-Database Environment

Edit sqlnet.ora file and specify the folder to store the wallet as follows:

ENCRYPTION_WALLET_LOCATION =

(SOURCE =

(METHOD = FILE)

(METHOD_DATA =

(DIRECTORY = /u01/app/oracle/product/11.2.0/dbhome_1/wallet)))

Make sure to create a wallet folder under $ORACLE_HOME and run the following command to create a wallet:

SQL> ALTER SYSTEM SET ENCRYPTION KEY AUTHENTICATED BY mypassword;

You can query V$ENCRYPTION_WALLET view to get the status of the wallet:

SQL> COL wrl_parameter format a50

SQL> SELECT status,wrl_parameter FROM V$ENCRYPTION_WALLET;

STATUS WRL_PARAMETER

—————— ————————————————–

OPEN /u01/app/oracle/product/11.2.0/dbhome_1/wallet

SQL>

Use the following command to open the wallet again after the database is restarted:

SQL> ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY mypassword;

Now use ENCRYPT option to encrypt the LOB segment as follows:

SQL> CREATE TABLE dbsnmp.tbl_lob_encrypt (id number, lob_data clob) LOB (lob_data)

STORE AS SECUREFILE (TABLESPACE users ENCRYPT USING ‘AES128’);

Table created.

SQL>

Caching

With caching feature you can bring the LOB segment to the buffer cache. You have to think twice before enabling caching option for the LOB segments, because it can flush necessary block from the buffer cache and can affect the performance of the database.

To put LOB segment to the cache, use CACHE clause as follows:

SQL> CREATE TABLE tbl_lob_cache (lob_data CLOB)

LOB (lob_data) STORE AS SECUREFILE (tablespace users CACHE);

Table created.

SQL>

To enable cache feature for a LOB segment, use ALTER TABLE .. MODIFY LOB command as follows:

SQL> ALTER TABLE tbl_lob_cache MODIFY LOB (lob_data)(CACHE);

Table altered.

SQL>