7 Things To Consider When Reorganizing Tables And Rebuilding Indexes

It would be so easy to do the maintenance on a database if you only deal with regular tables, with regular data types. It seems that each time you attempt to reorganize tables, there are some gotchas. Now, there is this table with a LONG column, and you feel anxious performing the reorg, because you are afraid of something breaking. Next, there are these partitioned tables, and you kind of remember a special statement for these partitions. And what about the indexes, what happens to them? The list of why not to do the maintenance just keeps growing and growing.

If this sounds familiar, you are not the only DBA who fears these tasks. Every DBA at one point in their career, has some fear around the reorg process.
Why is that?
Because this is not a task that you perform every day, or every week or even every year. By the time you revisit such a task, you have forgotten everything from the previous reorg process.

Let me ask you a question, are you anxious about extending a datafile, or creating a tablespace? I imagine you say NO. And why is that? because you have performed the task so many times, that you could probably do it in your sleep.

Need not worry any longer. Today’s post will summarize the most encountered scenarios when performing database maintenance, and you can use this post as a reference in the future.

1. How Do I Reorganize A Regular Table (you know, the one that has no LONG or LOB columns, and it’s not partitioned)?
2. How Do I Move A Partition To A New Tablespace?
3. What About LONG datatype? Conquer Your Fear of Export – Drop – Import.
4. Do I Need To Worry About LOBS?
5. Where Do I See The IOTs? I Don’t Think I Have Any In The Database
6. What About Indexes? I Just Wanted To Reorganize The Tables This Time.
7. The Forgotten One – Statistics On Tables.

At the end of this post, I have a surprise BONUS summary, make sure you read all the way to the end!

If you missed last weeks post, or you would like to have an overview of the Oracle database maintenance process, you can read it here: Reorganize Tables And Rebuild Indexes, The Oil Change For Your Database.

1. How Do I Reorganize A Regular Table (you know the one that has no LONG or LOB columns, and it’s not partitioned)?

The following statement can be used for any table that has no LONG column. The statement will work for tables with LOBs, however the LOB column will not be moved out of the tablespace.

SQL> alter table hr.employees move tablespace emp_data;

To Remember:

• Target tablespace needs enough free space to hold the table and you need quota on the tablespace.
• ROWIDs of the rows in the table are changed, thus all indexes on the table become UNUSABLE.
• No DML operation on the table is allowed during this process

DBA Views For Gathering Information:

• DBA_TABLES

2. How Do I Move A Partition To A New Tablespace?

For partitioned tables, you need to move out each partition, one by one to the target tablespace:

 SQL> alter table hr.emp_partitioned move partition emp_2000 tablespace emp_data;
 SQL> alter table hr.emp partitioned move partition emp_2001 tablespace emp_data;

Same constraints apply for partitioned tables as for regular tables.

DBA Views For Gathering Information:

• DBA_TABLES
• DBA_TAB_PARTITIONS

3. What About LONG datatype?
Conquer Your Fear of Export – Drop – Import.

When attempting to move a table that has a LONG datatype, you will receive the following error message:

ORA-00997: illegal use of LONG datatype

This is because you cannot move tables with LONG column, using the standard alter table move command.
In order to move a table that has a LONG column, you need to do the following:

1. Datapump export the table
2. Drop the table in the database
3. Datapump import the table

There is nothing to be afraid of when performing this set of operations. The datapump export will extract grants as well. On the import remember to use REMAP_TABLESPACE, if you want to move your table to another tablespace.

DBA Views For Gathering Information:

• DBA_TABLES
• DBA_TAB_COLUMNS

4. Do I Need To Worry About LOBS?

Fortunately you can move a table that has a LOB column, to a different tablespace. By default the LOB column will not be moved.
This is the statement to use to move the LOB:

SQL> alter table hr.employees move lob(details) store as (tablespace emp_data);

*details is the name of the LOB column

DBA Views For Gathering Information:

• DBA_TABLES
• DBA_LOBS

5. Where Do I See The IOTs? I Don’t Think I Have Any In The Database

You might not even be aware that you have Index organized tables (IOT) in your database. These tables will not show up in DBA_TABLES view.
These tables will show up in DBA_INDEXES (this is not a mistake). You will need to get the TABLE_NAME column, where INDEX_TYPE like ‘%IOT%’
Here is an example on generating the move statements for IOT:

SQL> select ' alter table ' || owner || '.' || table_name || ' move tablespace EMP_DATA;'
from dba_indexes where index_type like '%IOT%' and owner='HR';

The statement to move IOT, is the same:

SQL> alter table hr.emp_IOT move tablespace EMP_DATA;

DBA Views For Gathering Information:

• DBA_INDEXES

6. What About Indexes? I Just Wanted To Reorganize The Tables This Time.

Reorganizing tables and rebuilding indexes go hand in hand. You cannot reorganize tables only, and have a functional database. The ROWIDs of the rows in the table will change, and this will invalidate the indexes. The indexes become UNUSABLE. After the table move, if the indexes stay unusable, and the users issue DML statement against the table, the users will receive:
ORA-01502: index “string.string” or partition of such index is in unusable state.

This leads to the fact that:
After Alter Table Move, You MUST REBUILD INDEXES on the table.

What I prefer to do, when I generated the alter table move statements, I also generate the index rebuild statements.

SQL> alter index hr.emp_ix1 rebuild online tablespace EMP_IDX;

DBA Views For Gathering Information:

• DBA_SEGMENTS
• DBA_INDEXES

7. Almost Forgot, Statistics On The Tables?

Statistics on the tables that have been moved to a different tablespace, become invalid. It is highly recommended to gather statistics on the tables after the alter table move operation. When I generate the move commands, I generate the gather stats commands as well.

SQL> exec dbms_stats.gather_table_stats('HR','EMPLOYEES');

Surprise Bonus:

Database Maintenance CheatSheet:

-Diana