Friday, December 17, 2021

Convert Partitioned Table to Non-partitioned Table and vice versa

You might come across the situation that you need to convert Partitioned Table to Non-partitioned Table and vice versa.

You can RUN below command to identify weather the Table is partitioned or not

SQL> SELECT TABLE_NAME, PARTITIONED FROM USER_TABLES WHERE TABLE_NAME=‘EMP’;

TABLE_NAME PAR
--------------------- ---
EMP YES

I suggest using data pump to convert Partitioned Table to Non-partitioned Table. Below are the steps
Take a backup of the table that you want to convert to NON-Partitioned table

$ expdp SCOTT/xxxxx directory=EXP_DIR dumpfile=emp_expdp.dmp log=emp_expdp.log tables=EMP

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "SCOTT"."EMP":"YEAR_2001" 15.398 MB 40023 rows
. . exported "SCOTT"."EMP":"YEAR_2002" 23.456 MB 89898 rows
. . exported "SCOTT"."EMP":"YEAR_2003" 89.675 MB 100453 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

Import the table into TEST schema using PARTITIONS_OPTIONS parameter

$ impdp TEST/xxxxx directory=EXP_DIR dumpfile=emp_expdp.dmp log=imp_emp.log remap_schema=SCOTT:TEST partition_options=merge

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported “TEST"."EMP":"YEAR_2001" 15.398 MB 40023 rows
. . imported "TEST"."EMP":"YEAR_2002" 23.456 MB 89898 rows
. . imported "TEST"."EMP":"YEAR_2003" 89.675 MB 100453 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "TEST"."SYS_IMPORT_FULL_01" successfully completed at Sun July 5 08:44:21 2020 elapsed 0 00:00:10

This command will merge all partitions while importing as single table.

RUN below command to verify and you should see the out as
SQL> SELECT TABLE_NAME, PARTITIONED FROM USER_TABLES WHERE TABLE_NAME=‘EMP’;

TABLE_NAME PAR
---------------------- ---
EMP NO

DROP PARTATION

If you need to drop the partition use below command

Method 1: Drop the specific partition
SQL> ALTER TABLE EMP DROP PARTITION YEAR_2001;

Method 2: DELETE all rows part of partition and remove partition
SQL> DELETE FROM EMP PARTITION (YEAR_2001;);
SQL> ALTER TABLE EMP DROP PARTITION YEAR_2001;

Method 3:- Drop partition and update indexes
SQL> ALTER TABLE EMP DROP PARTITION YEAR_2001 UPDATE INDEXES;

ADD/MODFY partition

You can use ALTER TABLE ADD PARTITION, MODIFY PARTITION statement on a table

For ex:-
SQL>ALTER TABLE EMP MODIFY PARTITION BY RANGE (JOIN_DATE)
(PARTITION YEAR_2001 VALUES LESS THAN (to_date('01-JAN-2002','dd-mon-yyyy')),
PARTITION YEAR_2002 VALUES LESS THAN (to_date('01-JAN-2003','dd-mon-yyyy')));

SQL> ALTER TABLE EMP ADD PARTITION YEAR_2003 VALUES LESS THAN ( '01-JAN-2004' ) ;

Please refer oracle documentation for detailed PARTITIONS options and methods..etc

Thanks
http://oracleracexpert.com, Oracle ACE

Thursday, December 2, 2021

Webinar: Cloning an Oracle Home or Oracle Install

This webinar helps to Clone Oracle Home or Oracle Install while migrating the databases.

Date and time: Dec 15th 2022 8:00am-9:00am
Pacific Daylight Time (San Francisco, GMT-07:00)

This Webinar covers following Topics.
  • What is Cloning
  • When Cloning useful
  • Different methods of Cloning
  • How to perform Cloning
  • References
  • Q&A 
To register for this Webinar, please send an email to SatishbabuGunukula@gmail.com.
Note that registrations are limited and first come and first serve basis.

You will receive an email confirmation with meeting session link.

For Presentation link "Click here"

Regards,
Satishbabu Gunukula, Oracle ACE
http://www.oracleracexpert.com