Re: alter table a move...

 Hi Martin,

I think that I will dissapoint you.
As far as I know, Oracle 8.0.5 nor Oracle 8i does not allow moving tables to different tablespace with ALTER TABLE or any other statement.

There exists a workaround, and that is using export/import utility. It's a bit
tricky, but the procedure goes like this: 1. export tables you want using exp utility 2. change default tablespace of the user using ALTER USER .. DEFAULT TABLESPACE clause to the tablespace you want to import to

3. set the user quota to 0 on the tablespace you are exporting from
4. drop old tables
5. run import utility to import tables to a new tablespace
6. return the qouta to desired value
7. return the default tablespace

example:

1. exp userid=system/manager tables=username.table_name file=export.dmp
2. ALTER USER username DEFAULT TABLESPACE import_tablespace
3. ALTER USER username QUOTA 0K ON TABLESPACE export_tablespace
4. DROP TABLE username.table_name
5. imp userid=system/manager tables=username.table_name
6. ALTER USER username QUOTA UNLIMITED ON TABLESPACE export_tablespace
7. ALTER USER username DEFAULT TABLESPACE tablespace_name

Hope this helps!

Dario

"Martin Burkert" <martin_at_a1plus.at> wrote in message news:Pine.LNX.4.33.0108281110460.27845-100000_at_nebukadnezar-proxy.local...
>
>
> On Tue, 28 Aug 2001, Dario Bilic wrote:
>
> > Hi there,
> >
> > Error message "Keyword partition expected" showed up because
> > ALTER TABLE statement has only MOVE PARTITION clause,
> > not the MOVE clause:
> >
> > The following statement moves partition depot2 to tablespace ts094:
> > ALTER TABLE parts
> > MOVE PARTITION depot2 TABLESPACE ts094 NOLOGGING;
> >
> > I'm not quite sure what are you trying to do?
>
> I would like to write old Tables with all constraints.... to a new
> Tablespace.
> The tables are not partitioned, but sql plus says, that the keyword
> "partition" is expectet.
> However I tried the expected Keyword "partition => the new error-message
> is, that the table is not partitioned. :-|
>
> Is it possible, Oracle 8.0.5 doesnt support this feature?
>
>
> >
> > Dario
> >
> > "Martin Burkert" <martin.burkert_at_A1plus.at> wrote in message
> > news:9mfg1n$5ne$1_at_fstgss02.tu-graz.ac.at...
> > > Hi!
> > >
> > > I've got a problem with this Statment:
> > >
> > > alter table a move tablespace xy;
> > >
> > > I would like to change the default tablespace usr from table a to a
new
> > > tablespace xy. I created the tablespace xy with Oracle Storage
Manager,
> > > table a is not a partitioned.
> > >
> > > But the Error-message is: "keyword partition expectet" (this is my
German
> > to
> > > Englisch-Translation) ;-)
> > >
> > > What can i do?
> > >
> > >
> >
> >
> >
>
Received on Tue Aug 28 2001 - 05:57:27 CDT