Re: alter table a move...

In article <9mfteo$659s$1_at_as201.hinet.hr>, "Dario says...
>
> 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.
>

Oracle8i release 1 and up:

scott_at_ORA815> alter table emp move tablespace users;

Table altered.

new feature in 8i. see
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:47812348053 for a "big" example.

>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?
>> > >
>> > >
>> >
>> >
>> >
>>
>
>

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp