Re: HELP: Copying Tablespaces within Oracle 8.1.7

Bob wrote:
> Dear Group:
>
> I am running Oracle 8.1.7 (non-enterprise) on Windows NT 4.0 SP5 and I
> am wondering if there are any automated batch commands or scripts in
> SQL that will allow me to copy existing tablespaces within the *same*
> global database. For example, we have 4 extents (Prod, Pilot, Demo and
> Test) and I want to make duplicate copies of "Prod" within the same
> RDBMS. Is there a way to automatically recreate a second copy of
> "Prod", with the related IDX and TEMP tablespaces (with all of the
> tables / data). Any help would be greatly appreciated, I am a newbie
> at this.
>
> PEACE
I'm not sure, if what you describe is actually what you want. A Tablespace in Oracle is just a place to put objects like tables, indexes etc. An object might span several tablespaces which can be the case e.g. with index organized tables, partitioned tables and tables with lob segments.

In Oracle, objects are owned by an user (a user owning objects is also referenced as schema). The users objects can be put in any tablespace the user has a quota on.

So what you most likely want to do is to take all objects of a certain user a, create a *new* schema a2 and create a copy of the all the objects from a in the new schema a2.

If this roughly describes your needs, you're now ready to read up on exp / imp utilities in the oracle documentation which is freely available on http://tahiti.oracle.com. You must register, but registration is free and it's certainly worth it.

Best regards,

Holger Received on Wed Mar 05 2003 - 07:36:42 CST