Re: How do you make a copy of a tablespace?

dolans_at_stripe.Colorado.EDU (Sean Dolan) wrote:

> My goal is to make a "demo" for potential customers to use, but I don;t want them using the actual databse that holds important information - so I want to create a similar tablespace that they will access and "play" with without hurting the real live d-base.
>
> How would I copy the exact structure (and we'll say data too) to another tablespace... as well as User privledges, etc.
>
>Much Appreciated,
>Sean

I assume that you want to import the tables into another user's schema. Let's say the your data is owned by OLDUSER, tables are stored in OLDDATA and indexes in OLDINDEX, and that you want to copy over to NEWUSER with tables in NEWDATA and indexes in NEWINDEX.

If you don't want data exported, export the tables in the table space using the ROWS=N switch to stop data from being exported, otherwise just export the tables for that user.

The user NEWUSER should be set up so that it does not have any privilege or resource on tablespaces other than NEWDATA and NEWINDEX. Alter NEWUSER so that its default tablespace is NEWDATA. Run an import along the lines of:

imp fromuser=olduser touser=newuser indexes=n ignore=y log=import.log

This will create the tables in the new tablespace, grant user access privileges and the like. Then alter the user NEWUSER so that its default tablespace is NEWINDEX, and run the following:

imp fromuser=olduser touser=newuser indexes=y rows=n ignore=y log=import.log

This will create the indexes. You may have some tidying up to do, since primary key indexes may be created in NEWDATA, but this is quite easily done by disabling and then re-enabling the appropriate constraints.

If you use public synonyms in your database, you will have to create private synonyms in the schemas of those users wanting to access the structures in NEWUSER's schema.

It is probably advisable to use different grants from those on the source table, just in case a synonym goes missing - it can cause horrible problems when you have cross-schema matching.

I hope this helps a bit.

David

David Greensmith :-)
(david_at_green1.demon.co.uk) Received on Wed Jul 01 1998 - 16:51:52 CDT