Re: Importing a .dmp into a different tablespace (RH7.1, Oracle 81610)

You wrote all details down, good, except the point where it does not work. What parameters do you use at import? And "it" does not work: what doesn't work?
No import? Imported but in the wrong tablespace? That would be strange because the scenario you wrote is how I would have done it too.
You even did not forget the "revoke unlimited tablespace" that Oracle grants you under water when you grant the resource role. For some reason the user devintranet still has the privilege to allocate space in tablespace fred. Can devintranet create a table in tablespace fred (try in sqlplus)? It should not be possible.

Another solution is to create the tables in advance and then run imp. With imp ... indexfile=fred.sql rows=n you get all create index statements from the dump file in file fred.sql and create table statements but as comment (rem ....). With some edit work you can make a proper create script for the tables from this. This also makes it possible to change the storage clause if needed.

michela rossi <michela_rossi66_at_hotmail.com> schreef in berichtnieuws 1ed550fd.0210310703.7b01d4c4_at_posting.google.com...
| >> I have a .dmp file, say, fred.dmp - produced from a tablespace
| called fred, with user fred. I wish to create a new tablespace and
| user and import fred.dmp into that tablespace. I've read round various
| newsgroups and it does seem that there is a bit of an issue making
| Oracle import into a different tablespace from its' source.
|
| >> I am attempting to do this on RedHat 7.1 and Oracle 8.1.6.1.0.
 

| >> So, I have fred.dmp in /tmp/fred.dmp
 

| >> I create a new table space and user using the following commands:
|
| svrmgrl
| connect internal;
| create tablespace devintranet datafile
| '/ora8/m02/oradata/ora8/devintranet01.dbf' size 50m autoextend on
| default storage (pctincrease 1);
| create user devintranet identified by blah default tablespace
| devintranet temporary tablespace temp quota unlimited on devintranet;
| grant connect, resource, ctxapp, javasyspriv, query rewrite to
| devintranet;
| revoke unlimited tablespace from devintranet;
| alter user devintranet quota unlimited on devintranet;
| exit;
|
| >> Next, I try to import fred.dmp into devintranet:
|
| imp devintranet/blah file=intranet.dmp
|
| But - this doesn't work - does anyone know what I should do to force
| Oracle to do this import?
|
| Also: supposing I've reached the pt where I've finished experimenting
| with devintranet - what commands should I use to completely remove
| both the user and the tablespace, so that I can safely remove any
| resulting .dbf file that has been created?
|
| Any help would be most most welcome,
| Thanks, Michela.
Received on Thu Oct 31 2002 - 20:17:04 CET