Re: Moving tables between tablespaces?

"[ste parker]" <imaginey_at_hotmail.com> wrote in message news:aff1pk$dod3k$1_at_ID-84852.news.dfncis.de...
> Hi,
> I'm having a problem at the moment for which the solution is 1.
> Recompile a program, or 2. Change the tablespace that two tables reside
in,
> so the program works as intended.
>
> 1. Is difficult, as I don't have the source code, and if I did I'd have to
> set up a build environment, and unfortunately there's no time for that.

Does *anyone* have access to the source code? What happens if there is a bug. Fair enough if this is a thrid party app.

>
> 2. Seems reasonable enough to me, but the DBA who would have to carry this
> out says he'd have to "restructure the whole database", which I thought
odd.
> It might be a language thing, but I would have thought that it would be
> simple enough to create another tablespace with the same extents etc, just
> not called "AS" this time, then move the two tables into the new
tablespace.
> If this is a bad/wrong/difficult thing to do, could someone please explain
> why to me? Also, if it isn't an unreasonable thing to do, can someone
tell
> me exactly what needs to be done to do this (under Oracle 8.1.6), so I can
> put a proper proposal forward?

What needs to be done is one of

1.create two new tablespaces with the correct names. This means consuming the same amount of disk space again as is currently consumed by the data. Preferably the tablespaces would be created in the same locations as the existing ones. Then each object in the tablespace would have to be moved - tables first and then indexes. This can (assuming enterprise edition) all be done online. All existing schemas for the app might need quotas and default tablespaces changing.

2. export the database. shutdown the db, drop the db, recreate the db with the new tablespaces. import with ignore=y.

Both of these are pretty hefty operations that will adversely affect either or both of uptime and performance.

I also have problems with what exactly is going on.You seem to suggest that only two tables are affected. I can't think of any operation (other than drop table and recreate) that you could do to these tables that would care what tablespace they were in. If the app is dropping and recreating tables then it seems likely that in fact the tables should be global temporary tables and the app does indeed need a redesign.

--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer

******************************************