Read-Only partitions are of benefits
1.When you are working in Data-Warehouse environment there must be many partitions which are not updated frequently you can add those partitions to read-only state and then add those partitions to read-only tablespace. These read-only tablespace can help you take your RMAN backup faster as read-only tablespace can be avoided to be backed up in incremental backups.
2.By putting partitions to read-only state you can perform maintenance activity.
3.You can put partition or subpartition in read only state.
4.You can manage table partitions effectively as per there use using this option.
Now , how to create read-only partitions and check if they are in read only state or not.
1.Create table with read-only partitions
SQL> CREATE TABLE TRAN_READ_ONLY
PARTITION BY RANGE(DATE_OF_TRANSACTION)
(PARTITION P1 VALUES LESS THAN(TO_DATE('01-01-2019','DD-MM-YYYY')) read only,
PARTITION P2 VALUES LESS THAN(TO_DATE('01-02-2019','DD-MM-YYYY')) read only,
PARTITION P3 VALUES LESS THAN(TO_DATE('01-03-2019','DD-MM-YYYY')) read only,
PARTITION P4 VALUES LESS THAN(TO_DATE('01-04-2019','DD-MM-YYYY')) read only,
PARTITION P5 VALUES LESS THAN(TO_DATE('01-05-2019','DD-MM-YYYY')),
PARTITION P6 VALUES LESS THAN(TO_DATE('01-06-2019','DD-MM-YYYY')))
AS
SELECT TRAN_ID,DATE_OF_TRANSACTION,AMOUNT,ACCOUNT_NUMBER FROM TRANSACTIONS; 2 3 4 5 6 7 8 9 10
Table created.2.Let’s check in USER_TAB_PARTITIONS view if partitions are created in read only state
SQL> SET LIN 1000
SQL> COL TABLE_NAME FOR A30
SQL> COL PARTITION_NAME FOR A30
SQL> SELECT TABLE_NAME,PARTITION_NAME,READ_ONLY FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='TRAN_READ_ONLY';
TABLE_NAME PARTITION_NAME READ
------------------------------ ------------------------------ ----
TRAN_READ_ONLY P1 YES
TRAN_READ_ONLY P2 YES
TRAN_READ_ONLY P3 YES
TRAN_READ_ONLY P4 YES
TRAN_READ_ONLY P5 NO
TRAN_READ_ONLY P6 NO
6 rows selected.
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/
Joel Perez’s LinkedIn: Joel Perez’s Profile
LinkedIn Group: Oracle Cloud DBAAS
Facebook Page: OracleHelp
