Isn't AS a reserved word? It seems to me a very strange name for a
tablespace.
Nobody mentioned to check for referential constraints. When you use the
create table as statement, you aren't recreating any existing referential
constraints.
--
Terry Dykstra
Canadian Forest Oil Ltd.
"[ste parker]" <imaginey_at_hotmail.com> wrote in message
news:aff8lo$e1kbb$1_at_ID-84852.news.dfncis.de...
>
> "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 - 10:01:46 CDT
