See ROW Placement in Partitions

We have seen creation of partitions , its benefits , different methods etc. Today we will see method to check the records we are inserting in the partitioning table are placed properly in intended partitions.

Let’s create one table using range partitioning method. 

SQL> CREATE TABLE TRAN_RANGE
  2  (TRAN_ID NUMBER,
DATE_OF_TRAN DATE,
AMOUNT NUMBER(10,2))
PARTITION BY RANGE(DATE_OF_TRAN)
(PARTITION P1 VALUES LESS THAN(TO_DATE('01-01-2019','DD-MM-YYYY')),
PARTITION P2 VALUES LESS THAN(TO_DATE('01-02-2019','DD-MM-YYYY')),
PARTITION P3 VALUES LESS THAN(TO_DATE('01-03-2019','DD-MM-YYYY')),
PARTITION P4 VALUES LESS THAN(TO_DATE('01-04-2019','DD-MM-YYYY')),
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')));  3    4    5    6    7    8    9   10   11  

Table created.

SQL> 

Let us insert some random records in table.

SQL> INSERT INTO TRAN_RANGE VALUES(1,TO_DATE('17-01-2019','DD-MM-YYYY'),10000);
/
INSERT INTO TRAN_RANGE VALUES(2,TO_DATE('31-03-2019','DD-MM-YYYY'),20000);
/
INSERT INTO TRAN_RANGE VALUES(3,TO_DATE('22-05-2019','DD-MM-YYYY'),50000);
/

1 row created.

SQL> 
1 row created.

SQL> 
1 row created.

SQL> 
1 row created.

SQL> 
1 row created.

SQL> 
1 row created.

SQL> COMMIT;

Commit complete.

SQL> 

Now lets query the table with partitioning details. Using DBMS_ROWID.ROWID_OBJECT function we can get the object_id of table partition from rowid column of table. And subobject_name of dba_objects stores table partition name.

SQL> SELECT TRAN_ID,TO_DATE(DATE_OF_TRAN,'DD-MM-YYYY'),AMOUNT,DO.SUBOBJECT_NAME
FROM TRAN_RANGE TI, DBA_OBJECTS DO
WHERE DBMS_ROWID.ROWID_OBJECT(TI.ROWID)=DO.OBJECT_ID
;  2    3    4  

   TRAN_ID TO_DATE(DA	  AMOUNT SUBOBJECT_NAME
---------- ---------- ---------- --------------------------------------------------------------------------------------------------------------------------------
	 3 22-05-2019	   50000 P6
	 3 22-05-2019	   50000 P6
	 2 31-03-2019	   20000 P4
	 2 31-03-2019	   20000 P4
	 1 17-01-2019	   10000 P2
	 1 17-01-2019	   10000 P2

6 rows selected.

SQL> 

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