Re: HELP - Export/Import version 8.05

From: Julie Warden <julie_warden_at_nospamhotmail.com>
Date: Wed, 10 Jan 2007 00:40:59 GMT
Message-ID: <Xns98B3C83B1857Cjuliewarden@140.99.99.130>

"Anurag Varma" <avoracle_at_gmail.com> wrote in news:1168025269.716954.298930_at_11g2000cwr.googlegroups.com:

> 
> hpuxrac wrote:

>> Julie here's the contents of the URL that I cited ...
>>
>> I can't vouche for it entirely but at a quick glance it looks fairly
>> reasonable to me. Ed Stevens is I believe the author of this.
>>
>> **********************************************************************
>> *********************************
>>
>> Posting this mostly as a reference in case anyone searches the
>> archives
>>
>> with a similar problem. Following up on a thread I started a few
>> days
>>
>> ago.
>>
>> Task is to clone a schema. The first difficulty came with
>> FROMUSER=schema-A TOUSER=schema-B, in that we want each schema to
>> have it's own tablespace, but the DDL in the .dmp file had hard-coded
>> tablespace references to the TS of the original exported objects.
>> Various solutions found on the web involved extracting the DDL from
>> the
>>
>> .dmp file and messaging it by hand, correcting the tablespace
>> references and adding the statement terminators. This became
>> untenable
>>
>> when it was found that with several hundred DDL statements, some were
>> contained on a single line, and others were broken (sometimes badly)
>> across multiple lines. The solution is in the use of the
>> 'indexfiles' parm on import. It produces a text file of good, usable
>> DDL that is easily edited with global changes to a usable file for
>> initially creating the tables and indexes. Here's the full sequence:
>>

> --snip sequence--
> 
> additionally:
> OP can possibly use DDL wizard to do the same (Create schema objects).
> I had used it some while back and believe it would allow you to filter
> out
> tablespace names fairly easily.
> 
> http://www.ddlwizard.com/
> 
> Anurag
> 

Group,

Thanks for the help, but I got the answer today. It turns out that when I was on the project 6.5 years ago I created documentation on how to create and load schemas in this system. By the way, it's Maximo version 4.3 by PSDI, a very nice Facilities management package. Here's how it had to go: 1. Create the tablespace as sysdba, logged in as sysadm (I named it after the user/owner - "tst) 2. Setup username, granting connect/resource and DBA to "TST" 3. Now the tricky part: Run this program that creates the schema while logged in as "sys" - this was the part I didn't get right. This creates the schema to allow us to import as the schema owner (tst) from any other schema export.
4. Import the file that was exported with an explicit tablelist (the schema create program creates this funky encryption table that you can't export correctly)

This worked fine, as it gets around that schema weirdness mentioned above. I seem to remember a PSDI employee telling me how to export from a schema that would create a file that could be loaded into any empty tablespace. Maybe not.

Anyway, thanks for all the time and help - you did point me in the correct direction for exporting with the to/from user - that saved me a day at least.

Take Care,
Julie Received on Tue Jan 09 2007 - 18:40:59 CST