Modify Partitioned Table to Non-partitioned Table

In previous post we saw modifying non-partitioned table into partitioned one.Today we will see how we can convert partitioned table into non-partitioned table.

To read non-partitioned table to partitioned table:

Modify Non-Partitioned Table to Partitioned

Step 1 : Lets check first table is partitioned one.

SQL> SELECT TABLE_NAME,PARTITIONED FROM USER_TABLES WHERE TABLE_NAME='TRANSACTIONS';

TABLE_NAME															 PAR
-------------------------------------------------------------------------------------------------------------------------------- ---
TRANSACTIONS															 YES

Step 2 : To convert partitioned table to non-partitioned one we need to take backup and import it using PARTITIONS_OPTIONS parameter option of impdp.

Let’s take backup first.

[oracle@prod-18c ~]$ expdp jagruti/oracle directory=DIR_PART dumpfile=expdp_transactions_1.dmp logfile=expdp_transactions_1.log tables=transactions

Export: Release 12.2.0.1.0 - Production on Sun Aug 4 07:46:36 2019

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "JAGRUTI"."SYS_EXPORT_TABLE_01":  jagruti/******** directory=DIR_PART dumpfile=expdp_transactions_1.dmp logfile=expdp_transactions_1.log tables=transactions 
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 "JAGRUTI"."TRANSACTIONS":"Y_2012"           2.564 MB   94208 rows
. . exported "JAGRUTI"."TRANSACTIONS":"Y_2011"           2.455 MB   90112 rows
. . exported "JAGRUTI"."TRANSACTIONS":"Y_2013"           2.455 MB   90112 rows
. . exported "JAGRUTI"."TRANSACTIONS":"Y_2014"           2.455 MB   90112 rows
. . exported "JAGRUTI"."TRANSACTIONS":"Y_2016"           2.455 MB   90112 rows
. . exported "JAGRUTI"."TRANSACTIONS":"Y_2017"           2.455 MB   90112 rows
. . exported "JAGRUTI"."TRANSACTIONS":"Y_2018"           2.455 MB   90112 rows
. . exported "JAGRUTI"."TRANSACTIONS":"Y_2019"           2.455 MB   90112 rows
. . exported "JAGRUTI"."TRANSACTIONS":"Y_2015"               0 KB       0 rows
Master table "JAGRUTI"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for JAGRUTI.SYS_EXPORT_TABLE_01 is:
  /u01/expdp_transactions_1.dmp
Job "JAGRUTI"."SYS_EXPORT_TABLE_01" successfully completed at Sun Aug 4 07:47:33 2019 elapsed 0 00:00:50

[oracle@prod-18c ~]$

Step 3 : Let’s import the backup using PARTITIONS_OPTIONS parameter.

Here we need to specify merge value in PARTITIONS_OPTIONS that will merge all partitions and import as a single table.

[oracle@prod-18c ~]$ impdp tst/oracle directory=DIR_PART dumpfile=expdp_transactions_1.dmp logfile=impdp_transactions_1.log remap_schema=JAGRUTI:TST PARTITION_OPTIONS=merge 

Import: Release 12.2.0.1.0 - Production on Sun Aug 4 07:53:15 2019

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Master table "TST"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "TST"."SYS_IMPORT_FULL_01":  tst/******** directory=DIR_PART dumpfile=expdp_transactions_1.dmp logfile=impdp_transactions_1.log remap_schema=JAGRUTI:TST PARTITION_OPTIONS=merge 
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "TST"."TRANSACTIONS":"Y_2012"               2.564 MB   94208 rows
. . imported "TST"."TRANSACTIONS":"Y_2011"               2.455 MB   90112 rows
. . imported "TST"."TRANSACTIONS":"Y_2013"               2.455 MB   90112 rows
. . imported "TST"."TRANSACTIONS":"Y_2014"               2.455 MB   90112 rows
. . imported "TST"."TRANSACTIONS":"Y_2016"               2.455 MB   90112 rows
. . imported "TST"."TRANSACTIONS":"Y_2017"               2.455 MB   90112 rows
. . imported "TST"."TRANSACTIONS":"Y_2018"               2.455 MB   90112 rows
. . imported "TST"."TRANSACTIONS":"Y_2019"               2.455 MB   90112 rows
. . imported "TST"."TRANSACTIONS":"Y_2015"                   0 KB       0 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "TST"."SYS_IMPORT_FULL_01" successfully completed at Sun Aug 4 07:53:22 2019 elapsed 0 00:00:06

[oracle@prod-18c ~]$

Step 4 : Let’s check now table is imported as partitioned or non-partitioned table.

SQL> set lin 1000
SQL> conn tst/oracle
Connected.
SQL> SELECT TABLE_NAME,PARTITIONED FROM USER_TABLES WHERE TABLE_NAME='TRANSACTIONS';

TABLE_NAME															 PAR
-------------------------------------------------------------------------------------------------------------------------------- ---
TRANSACTIONS															 NO

SQL>

So you need to follow above steps to convert partitioned table to non partitioned table.

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

Tagged:

About The Author

I am 11g OCP and OCE(RAC and GRID Infrastructure). Currently working as Oracle Database Administrator at _VOIS and Blogger (Freelancer) at Oracle-help . Previously I worked with Acute Informatics pvt ltd. and Mobile Tornado where I handled Banking Data Migration , created DR using log shipping , Golden Gate and Data Guard. I have experience of handling planned and unplanned DB role switchover and failovers and monitoring and troubleshooting Database Performance. Key skills : RMAN , GG , DG , RAC , Virtualization in OVMM.I have experience in Banking and Telecom domain. Reach me at : https://www.linkedin.com/in/jagruti-jasleniya

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.