The Oracle online table reorganization package, (dbms_redefinition) is used to reorganize tables while they are accepting updates. See here for details on using the dbms_redefinition package for reorganizing Oracle tables online. The online reorganization packages does this by creating a snapshot on the target table and applying all table. Oracle 11g - Reorganizing Tables with Oracle Utilities There are several Oracle utilities available to the Oracle administrator to use for reorganizing tables. These include the CREATE TABLE AS SELECT (CTAS) SQL statement and Oracle’s export/import utilities.
I am so excited to share with you the blueprint, or the plan that you can use to implement the maintenance process on your database. If you how to promote your blog on google have a plan, and want to verify you covered all the steps, then this article is for you. I am showing you step by step the exact process I use, to reorganize tables and rebuild indexes in an Oracle database.
I am taking the guesswork out of the process. This is the last part of the 3 part series for Oracle Database Maintenance. Today, I am putting it all together, and share with you, the Step By Step Deployment Planfor reorganizing tables and rebuilding indexes.
You will need to create your own scripts, and that part is easy, once you know what scripts to create. What challenges will you encounter? Enjoy my friend! If you have any questions or feedback, feel free to leave a comment in the comments section, or email me!
It is critical that you are in the right environment when you proceed. At times it can be confusing as to where you are running the scripts, especially when you have multiple windows open.
My preference is to run the scripts right on the database server. This is the step where you think about everything that could reside in the tablespace that you are reorganizing, or any objects that you are working on. This is where the scope of this deployment plan can be increased or decreased.
Each script generates a RUN script, that will be run in a Step7. This is the step where you are actually doing the work. Everything up until now, lead to this step. You need to confirm that all indexe that were usable, are still in a usable state, and that no index rebuilds got missed. If your check is positive, and how to download guitar rig 5 free objects exist in the tablespace, it is safe to drop it.
The database maintenance could invalidate some objects, such as synonyms, views, procedures. These objects need to be compiled. You need to how to reset cable modem that all the objects that were valid prior to your deployment, are still valid after. The output file can be compared with the log file generated in Step3. Now you can create some nice reports for management.
You can create a comparison between tablespace sizes, table and index sizes before and after the reorg process. Management likes to see reports, and numbers. Hi Sam, The scripts are not provided. The plan and all the things sr3 how to make josh birk need to consider when going through the reorg process, that is provided. The scripts would be needed to be build on a case by case basis. I can write a final article for this series, with some scripts that can be customized!
Hi Zeeshan! Hi Kumar, thank you for your kind words. I might come up with something later and post it on the blog. This is great step by step, thanks. Hope this is useful. Thank you for these steps. Wondering how would you estimate the time needed to complete this effort before starting it?
That will help planning for the maintenance window. Secondly, can you please share some sample script as well? You would estimate the time, by first running through the process in the test environment.
That is a good start! I got lots of requests! Your email address will not be published. This site uses Akismet to reduce spam. Learn how your comment data is processed. June 29, 22 By dianarobete. Step13 — Backup Your Database You created new tablespaces and dropped old ones. Take a backup of your database! Category Database Oracle. July 15, at am. July 19, at pm. Sam says:.
July 30, at am. August 10, at pm. Aijaz Sabir Hussain says:. August 25, at pm. Pinto says:. October 21, at am. Ijaz Shaikh says:. November 13, at pm. November 14, at pm. January 19, at am. January 19, at pm. Bilal says:. April 4, at am. April 5, at pm. July 29, at am. August 3, at pm. Kumar says:. August 16, at am. August 21, at pm. How to reorg a table in oracle 11g says:. October 7, at am. October 11, at pm. Adam says:. January 24, at pm. Nilesh says:. June 5, at am. July 17, at pm.
Leave a Reply Cancel reply Your email address will not be published.
Tablespace Reorganization. Because they are dynamic, Oracle databases will always fragment over time and may require a periodic clean-up. In general, reorganization ensures that all tables and indexes do not have row fragmentation, and that they reside in a single extent, with all free space in a tablespace in a single, contiguous chunk. Table Reorg Most of the DBA books on Oracle discuss that if a table has lot of empty blocks below the HWM, the best way to reorg is to do the following steps CREATE TABLE N AS select * from O2. Drop O3. Create Owith appropriate INITIAL_EXTENT4. CREATE TABLE O AS SELECT * . Aug 12, · The above command reorganizes the table in the same table space. Basically, the reorganization allocates new space that is needed for the amount of data the table has. Then it releases all the previously allocated space that doesn't have data any more. All the physical attributes of the table remain the same.
The online reorganization packages does this by creating a snapshot on the target table and applying all table changes after the table has been reorganized with the " Create table as select " command:. Online Redefinition. In a very active database environment, it sometimes becomes necessary to modify a hot table to improve the performance of queries and DML statements.
To achieve this, Oracle provides a mechanism called online redefinition. When a table is redefined online, it is accessible to DML during much of the redefinition process.
However, the table is locked in the exclusive mode only during a very small period. This window or period is usually independent of the size of the table and the complexity of the redefinition.
The Online Redefinition process was made available in Oracle9 i. However, with Oracle Database 10 g , this feature becomes much more easy to use and thus useful for redefining online tables.
In Oracle, there were many restrictions to performing redefinition. It was even necessary to create dependent objects, such as constraints and indexes, manually. Oracle Database 10 g introduces many enhancements to facilitate easier administration and to minimize manual tasks. They are discussed next. Tables that are involved in master-master replication can undergo redefinition. However, there is a restriction that no horizontal or vertical sub-setting, or column transformations, are allowed.
They are as follows:. The following constants are also introduced in specifying the type of the dependent object. You can use this parameter to specify the columns that should be used to order the rows during the initial instantiation of the interim table. The remaining restrictions are shown below:. Let us look at the total online redefinition process using these newly introduced features and enhancements. It also has two additional indexes.
We intend to redefine the table by dropping one column, modifying another column, and adding an additional column. First, determine the method of redefinition. Here, we will use the primary key to perform the redefinition.
This procedure will also determine whether the table is redefinable according to the documented limitations, and if not, issue an error message telling why the table is not redefinable.
We need to create the interim table with an appropriate definition. This table structure will reflect addition, modification, and deletion of columns. Also notice that we have increased ticker column size to 6 from 4. Supply the parameters, such as table to be redefined, interim table name, the column mapping, and the method of redefinition. This is a new feature in Oracle Database 10 g.
This procedure is newly introduced in Oracle Database 10 g to ease the redefinition process. Then we drop the interim table after ensuring the desired results are achieved. This view shows the errors that occurred while cloning dependent objects involved in ongoing redefinitions. Feel free to ask questions on our Oracle forum. Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise.
All legitimate Oracle experts publish their Oracle qualifications. Oracle technology is changing and we strive to update our BC Oracle support information. If you find an error or have a suggestion for improving our content, we would appreciate your feedback.
Just e-mail: and include the URL for the page. All rights reserved by Burleson. The online reorganization packages does this by creating a snapshot on the target table and applying all table changes after the table has been reorganized with the " Create table as select " command: Here is a simple execution of an online table reorganization: -- Check table can be redefined. Online Redefinition In a very active database environment, it sometimes becomes necessary to modify a hot table to improve the performance of queries and DML statements.
The remaining restrictions are shown below: - The table to be redefined cannot be part of a cluster. Example of online redefinition of a table Let us look at the total online redefinition process using these newly introduced features and enhancements. Step 1. Search BC Oracle Sites. Step 2. Step 3. Step 4. Step 5. Burleson is the American Team Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.