Re: HELP - Export/Import version 8.05

Julie Warden wrote:
> I'm running Oracle 8.05 with Solaris 6.
>
> I have a database with several schemas, and I want to create a test
> schema from one of the other ones. I'm having a problem with my
> export/import procedures.
>
> I created the schema:
> grant connect,resource to tst identified by tst;
> grant DBA to tst;
> revoke unlimited tablespace from tst;
> alter user tst default tablespace TST quota unlimited on TST;
> alter user tst temporary tablespace temp;
> grant create public synonym to tst;
> grant drop public synonym to tst;
> grant create user to tst;
> grant drop user to tst;
>
> Exported from prod with a table list:
> exp userid=prod/prod parfile=tables.lst file=prod.exp
>
> Then imported for the tst user
> imp userid=tst/tst fromuser=prod touser=tst parfile=tables.lst
> file=prod.exp
>
> The import gives this:
> IMP-00003: ORACLE error 1950 encountered
> ORA-01950: no privileges on tablespace SYSTEM
> Then it shows my CREATE TABLE statement.
>
> This repeats for every table that needs to be created, then you get
> the
> errors that the table doesn't exist when you try to load the data.
>
> Naturally, I looked up all the exp/imp info I could, but no mention of
> granting access to create tables when you're loading into your own
> tablespace. I assume that my import, with all it's references to tst,
> is using the tst tablespace, right?
>
> Can some kind soul please explain this to me? Other than pointing out
> that I didn't grant privileges to tst on the SYSTEM tablespace: I know
> that much.
>
> Thanks,
> Julie

Oracle is going to try to create tables and indexes in the same tablespaces that were "in play" by the prod user.

You can attack this kind of thing by doing the import in multiple steps. It's going to involve using the IGNORE ROWS and INDEXFILE parameters.

There's got to be some good examples of how to do this already if you do some searches either against cdos or maybe http://asktom.oracle.com Received on Fri Jan 05 2007 - 08:56:52 CST