Re: Moving tables between tablespaces?

"Imprecise" <f_puhan_at_precise.com> wrote in message news:f_puhan-31EDC5.09285027062002_at_vienna7.his.com...
> In article <aff1pk$dod3k$1_at_ID-84852.news.dfncis.de>,
> "[ste parker]" <imaginey_at_hotmail.com> wrote:
>
> > 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.
> >
> > 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?
>
> It sounds to me like your DBA is either (a) timid, (b) lazy, or (c)
> incompetent. Of course, I don't know the full scope of your
> environment, so there may be other issues at hand, but here's how I
> might go about doing what you describe:
>
> During a period of inactivity (forced or scheduled), or on a test
> instance, I would
>
> 1) Query DBA_INDEXES for any that might exist based on the original
> tables.
> 1) RENAME the original tables. My convention is to append "__X"
> (that's double-underscore-ex) to the table name.
> 2) Issue the CREATE TABLE AS SELECT statement, specifying the new
> tablespace in the storage parameter clause.
> 3) REBUILD any indices as necessary.
> 4) Query DBA_OBJECTS and recompile any views, etc. that may have become
> invalidated through this activity.
> 5) Turn back on the database
> 6) After confirming all operations are working properly, DROP the
> original -- now renamed -- tables.
>
> Please note that I've only provided an overview to the process. I have
> not included determining sizing, time to completion, etc. However,
> please also consider that I've actually performed this maneuver more
> than once, without any ill effect. However, I'm concerned that the
> application code, if it contains embedded references to tablespaces, is
> violating the concept of "independence between data and data objects."
> This might cause me to be very cautious about step 6.
>

Thanks for the help. The application code doesn't actually have embedded references to tablespace names, it's used to alter tables, add columns etc maintaining data & tablespace location as it goes. What I noticed it doesn't do is quote anything when it tries to run SQL, so seeing as the tablespace was called "AS", the SQL ended up looking like this:

Creating Table 'NEW_ASOS'.
SQL Error: Code: -2216: ORA-02216: tablespace name expected SQL QUERY (code 0) was:
CREATE TABLE NEW_ASOS (
<snip col info>
)
PCTFREE 10 PCTUSED 40
TABLESPACE AS Which obviously breaks the statement as is. Ideally, the app would have been written perfectly in the first place and quoted round the tablespace name, but it would have been nice if they'd put some thought into their naming convention as well!

[ste] Received on Thu Jun 27 2002 - 09:49:23 CDT