Hi Guys, Welcome back
scenarios in migrating table to different tablespaces
- In-order to reorganize table
- Filesystem is full, need to migrate the table to different tablespace
following the steps
STEP1: Find out the tables and objects want to move to new tablespace ?
STEP2: Find out the size of each object to migrate ?
STEP3: Create new tablespace with size from step 2
STEP4: Discuss the best strategy to move tables to newly created tablespace in step3
STEP5: When to implement it ?
Best strategy as follows
a) move table from one tablespace to another tablespace
sql> alter table <Table Name> move tablespace <Tablespace_Name>;
when we move table to another tablespace, index's on that table will be in unused state and we need to rebuild index's on the table. We want to distribute the data across tablespaces make sure index is created on different tablespace
Sql> alter index >index_name> rebuild tablespace <tablespacename>;
Note ---- Don't forget to collect stats on table. I recommend to create histograms on the large volume tables
----Thank you --
----Nikhil Tatineni---
----Oracle In-memory---
