Oracle’s Online Table Redefinition can be used for a lot of things, but I have used it the most for partitioning an existing table. Because I do this so much, I created some “helper” shell scripts to make it easier and ensure I don’t miss something important. The scripts are available in my personal git repo: https://github.com/dwheltzel/Shell-Scripts-for-Oracle-DBAs/tree/master/online_partitioning
Preparing the scripts
You will need to copy 3 scripts and edit at least 2 of them. For this exercise, I am assuming you are starting with the scripts directly from the repo and want to partition a table called SCOTT.ORDERS by order_date (a date type column), and that you want monthly partitions.
Copy/create scripts
- cp partition_CUST-1.sql partition_ORDERS-1.sql
- cp partition_CUST-setup.sql partition_ORDERS-setup.sql
- touch partition_CUST-local.sql
Edit setup script
vi partition_ORDERS-setup.sql – This file as 3 variables defined. Change these to match your table. The PART_TABLE is the name of a temporary table name that is needed and will remain as the old, unpartitioned table after the job completes. This will need to be manually dropped by you after determining that you don’t need it as a backup.
Edit -1.sql script
vi partition_ORDERS-1.sql – This file has all the details about the new table structure. Make these edits:
- First line – Change “CUST” to “ORDERS” so the setup file gets called correctly.
- DEFINE V_TS – this is the tablespace the final partitions should reside in, by default
- dbms_redefinition.can_redef_table – uncomment the correct line, depending on whether a PK exists for this table.
- create table – change this section to create the table you are using. Column names must match and number of columns needs to remain the same, but you can change the order of the columns if you wish.
- PARTITION BY RANGE – make this match your partitioning column name (order_date in our example). You can also change the partition range here – INTERVAL(numtoyminterval(1,’MONTH’))
- PARTITION cust_ – change the names of the partitions, update the dates, and add or delete rows as needed to make the partitions match what you want.
- dbms_redefinition.start_redef_table – If you are partitioning a table without a PK, edit these to run the version with ROWID
Run the -1 script you just edited
Run this from sqlplus – @partition_ORDERS-1.sql. It can take a while to run, as it copies all the records to the new partitioned table you defined. If it errors, note the error and run the abort script as shown in the output. Fix the error and try again.
Once it completes without error, run the -2 script. The syntax is output at the end of the -1 script (@partition-2.sql CUST).
When the second script completes, test the resulting table that is now partitioned. You might count rows or run your application. Once you are convinced it worked as expected, you can drop the temporary table.