Welcome to the Blog of Mike Ross

« New Technology Study - Flash + XML + PL/SQL | Main | Movin' On »

December 9, 2010

Moving Objects To New Tablespace

Today I took a bit of time to clean up a messed up tablespace on Aero. A while back Dale and I reorganized a tablespace to make it locally managed with automatic (as opposed to manual) segment management.

This is an automated process, and the database renames the tablespace from myspace to myspace_reorg0. Well, we forgot to modify the schema owner's properties to default to the new tablespace. End result = all new objects went right back in to the original tablespace.

My plan is to now fix the schema properties to default to the auto tablespace, then move all objects into the new tablespace. This is a pretty straightforward process for "normal" objects such as tables and indexes. A command such as this does the trick, and takes less than a second for most reasonably sized objects:

ALTER TABLE [TABLENAME] MOVE TABLESPACE [TABLESPACE NAME];

or

ALTER INDEX [INDEX NAME] REBUILD TABLESPACE [NEW TABLESPACE NAME];

Well, that's fine, but this particular tablespace also contains a bunch of these:

MYSPACE.SYS_LOB0944542804C48302 - LOBSEGMENT

Ya, dang blobs and clobs everywhere - all segments stored in the wrong tablespace. Well, this can also be fixed, but first you have to know the tables to which these lobsegments belong. I haven't yet figured out a good way to identify the table-lobsegment relationship out of the data dictionary. Instead, I just looked through the schema for tables with blob or clob columns in them. Once located, this is the syntax for moving the lobsegment to the correct tablespace:

ALTER TABLE myspace.test_lobtable
MOVE LOB(blobclobcolumnnamehere) STORE AS (
TABLESPACE myspace_reorg0
)

The second complication is that the tablespace contains a bunch of these, too:

MYSPACE.BIN$bp8vY4FWOlfgRAADunpu8Q==$0 - TABLE

Unless someone is a naming putz, this is a flashback/recycle bin object. You can get rid of these (theoretically) with a command like this:

PURGE TABLESPACE MYSPACE;

Unfortunately, this didn't remove any of these objects - they are apparently no longer recognized by the database - I'm really not sure why it doesn't remove them. It just doesn't.

Once everything except these crazy-named objects are all moved, the next step is to take the original myspace tablespace offline, and see what happens/breaks, if anything.
Assuming nothing breaks, then the myspace tablespace can be dropped, and the myspace_reorg0 with the correct segment management can be used.

Posted by rossm at December 9, 2010 10:14 AM

Comments

Post a comment