CODEWITHSUNDEEP
oracle-database
TS74
TS74

Reputation: 61

Move an Oracle table to being Index Organised

I have an Oracle table in a live production environment and the table is over half a gig in size. Is it possible to change this normal Oracle table from being heap organised to index organised or is this only achievable by moving the data from this table to another new table which is index organised? Either way, I would be grateful if you could you please list the steps involved in this procedure.

Upvotes: 4

Views: 585

Answers (2)

atokpas
atokpas

Reputation: 3351

There is no way to alter a table to make it index-organized table. Instead you can redefine the table(using DBMS_REDEFINITION)or can create new table using CTAS.

Example:

 create table t2 (
 id number, first_name varchar2(20),
 constraint pk_id primary key (id)
 )
 organization index
 as select * from t1;

Upvotes: 2

Kacper
Kacper

Reputation: 4818

I never used DBMS_REDEFINITION but with CTAS it is not only step to create table if it is production.

  1. List all indexes, constraints, foreign keys and so on based on system views
  2. Prepare create index, constraints and alter foreign keys statements. prepare list of triggers, procedures that depend on table.
  3. Create table as select (consider lock before that step if you can)
  4. Create all indexes, constraints with prepared statements from step 2
  5. Swap table names and swap foreign keys (this step may cause some errors if you hit insert on foreign keys (if you expect it on that time you should lock the table and tables referencing by foreign key).
  6. Compile dependent objects from 2 (if you locked tables unlock here)
  7. (if you haven't locked on step 3) Insert into table select * from new minus select * from old; or if you have timstamp of inserting row just insert new rows.

I hope the list is complete.

Upvotes: 1

Related Questions