I have met with circumstance to change the partitioning option used for table. This needs to make the particular tbale nonpartioned.
I have used the DBMS_REDEFINITION package to acheive this.
1. Create a interim table with same structure as of the souce table WITHOUT partition.
2. Check the redefinition is possible using the following command on source tbale
EXEC Dbms_Redefinition.Can_Redef_Table(USER, 'SOURCE_TABLE');
3. If no errors are reported proceed with the redefintion using the following command.
BEGIN
DBMS_REDEFINITION.start_redef_table(
uname => USER,
orig_table => 'SOURCE_TABLE',
int_table => 'INTERIM_TABLE');
END;
/
4. Synchronize new table with interim data before index creation
BEGIN
dbms_redefinition.sync_interim_table(
uname => USER,
orig_table => 'SOURCE_TABLE', int_table => 'ITERIM_TABLE');
END;
/
5. Create Contraints and indexes. The constraints and indexes from the original table must be applied to interim table using alternate names to prevent errors.
6. Complete the redefinition process
BEGIN
dbms_redefinition.finish_redef_table(
uname => USER,
orig_table => 'SOURCE_TABLE',
int_table => 'INTERIM_TABLE');
END;
/
Now the interim table has become the real table and their names have been switched in the data dictionary. Now perform some cleanup operations.
- Remove original table which now has the name of the interim table
DROP TABLE INTERIM_TABLE'
- Rename all the constraints and indexes to original name
- Make grants as on original table
