Re: Moving tables from one tablespace to another
Date: Thu, 10 Feb 94 21:12:56 GMT
Message-ID: <1994Feb10.211256.20492_at_colesmyer.com.au>
In article <CKxKxu.Ix1_at_suncad.camosun.bc.ca>,
Dan MacDonald <ua168_at_freenet.Victoria.BC.CA> wrote:
>
< See text after mine >
This is a common question...
Q: How do I move a table to another tablespace and how do I change the
storage parameters?
- exp <user/password> indexes=y parameters...
imp <user/password> indexfile=a.lis
(This will create the file a.lis with all create index statements and
create table statements commented out.)
edit the file a.lis to your hearts content, then run it under the username you want to own the tables. You can change the storage or tablespace name before you run it. There is no need to edit the export dump. Once you are happy with the tables, you can import the data. I recommend you create the indexes after the data is loaded.
The indexfile option came in about 6.0.33 or 6.0.31 on DOS.
Regards,
Andrew.
>i am about to do something similar. moving to a differentb
>database & user isn't hard, but transferring to a differemt
>tablespace is a trick.
>
>firstly, in case you didn't know this already, use EXP/IMP.
>so export the user's file.
>
>then create the new user on the new system. but what you
>want to do is make his/her default tablespace point to
>a REALLY small tablespace (of the same name as was in the
>original system). i mean REALLY small. then make the
>temporary tablespace point to the one you want the stuff to
>go in. then IMP the files. what will happen is that when
>there isn't room in the 'default' tablespace, the files will
>load into the 'temporary' tablespace (or so i have been assured).
>once the load is done, reset the user's default tablespace to
>what it shoould be (ie where you just loaded the data).
>
>i THINK this will work.
>
>at one point in the past i editted the EXPDAT.DMP file
>and changed all the tablespaces. i DO NOT recommend this. it
>was a real pain!
>
>if anyone has any other ideas.....
>
>dan macdonald
-- --------------------------------------*--------------------------------------- N. Andrew May. | Database Administrator Coles-Myer Ltd | These are my humble opinions alone. 53 Hoddle Street, Collingwood, | Tel: +61 3 483 7389. Fax: 483 7381Received on Thu Feb 10 1994 - 22:12:56 CET