Often after running Oracle Application Tablespace Migration (OATM) you will find that there are still indexes, tables and objects left in some of the old tablespaces. You want to clean them up so you can delete the old tablespaces and recover the space used by the datafiles. In the OATM documentation there’s no clear instructions on how to do this since it assumes that OATM will handle it for you.
I recently had this situation and here’s some steps I took to resolve my problem. This example assumes that I have some tables, indexes and objects left in the old FNDD and FNDX tablespaces. I wanted to move everything over to one of the new APPS_TS_* tablespaces so I could drop these two old tablespaces and recover the disk space.
Before I got started I found this nice article that outlines options with working on Large Objects (which I had). The syntax examples are nice.
First I wanted to list all objects left in FNDD so I knew what to move. I used this script and modifed the Tablespace Name to list all objects (including “free space” segments).
Next I wanted to list any tables left in FNDD so I did this.
select table_name from dba_tables where tablespace_name = ‘FNDD’;
Before you move the table (and any associated LOB segments), you may want to verify you have enough space in the destination tablespace. You can query dba_segments to find out how many bytes are currently being used for the segments that you are planning to move. Then verify you have enough free space at the destination.
I found that I had a table with a LOB column and the two associated LOB and LOB Index segments. I wanted to move the table and both LOB segments to APPS_TS_MEDIA in one group. The command to move these three objects was:
alter table OWNER.TABLENAME
move tablespace NEWTABLESPACENAME
lob(COLUMNNAME)
store as (tablespace NEWTABLESPACENAME);
The COLUMNNAME is the column name in the table that contains the Large Object.
After the move I verified that there were no other objects left in FNDD using the same script above. I also did a:
select owner, table_name from dba_tables where tablespace_name = ‘OLDTABLESPACENAME’; to ensure that the table was gone as well.
Now it was time to check FNDX to ensure that any indexes were moved as well. I modified the script from above to reference FNDX instead of FNDD. I also ran a select index_name from dba_indexes where tablespace_name = ‘FNDX’;
Once I identifed the indexes I used this command to move them to the new tablespace.
alter index OWNER.INDEXNAME rebuild NEWTABLESPACENAME;
After verifying that everything was gone from FNDX and now in the new tablespaces I was able to drop the FNDD and FNDX tablespaces freeing up lots of needed space.
Several documents on Metalink were also helpful so I’ve listed them here.
- Summary Note to LOB’s/BLOB’s/CLOB’s/NCLOB’s and BFILEs DOC ID 198160.1
- How to move LOB Data to another Tablespace DOC ID 130814.1
Finally, though I didn’t have to use this syntax, here is the command to move just the Large Object (LOBs) segments to a new tablespace while NOT moving the actual table that uses them.
alter table TABLENAME move lob(LOBCOLUMNNAME) store as (tablespace NEWTABLESPACENAME);
And here’s the syntax to just move a Table which doesn’t have any LOB columns.
alter table TABLENAME move tablespace NEWTABLESPACENAME;
That about wraps it up, so good luck on your tablespace cleaning!