Help with Import please!

[Fiona]

Hello.

Within the same database, I have exported schema A, and want to import it into schema B.

Problem is, when I import schema B, the tables goes back into tablespace for schema A instead of going into tablespace for schema B.

Import parfile I use use is as below:

show=n
commit=y
file=f:\exp_imp\exp_A.dmp
log=f:\exp_imp\imp_A.log
fromuser=A
touser=B
feedback=10000
ignore=n

Could anyone tell me what I've done wrong and how to remedy it please!

Thanx in advance.

[jovery]

Because the tablespace exists Oracle will place the objects in that tablespace rather than the tablespace assigned as default to the new users.

I'm not sure if this will work (because I've not tried it) try restricting the quota for userB on that tablespace and then perform the import.

Otherwise you will need to create the objects seperatly and then import the rows.

Regards

[kgb]

Precreate all tables with correct tablespace,
to get SQL code for all tables use:
exp useird=s/s fromuser=a touser=b indexfile=sql.txt file=a.dmp

And import:
commit=y
file=f:\exp_imp\exp_A.dmp
log=f:\exp_imp\imp_A.log
fromuser=A
touser=B
feedback=10000
ignore=y

If you revoke quotas on tablespace and give quota on the other tablespaces, it will not place all your tables in correct tablespace.
Moreover if your tables have different tablespaces, revoking quota will not help you at all.

[shandj]

You have to revoke unlimited quota privilege from the second user (Schema B). Import the objects using second user (Schema B) instead of System or some other user having DBA privileges.

Try out this.. I hope this will work for you.

Still if you face with the same problem, then onething you can do is open the dmp file and replace "TABLESPACE XXXX" with your tablesapce "TABLESPACE YYY".

Thanks,

[Fiona]

I couldn't "do exp useird=s/s fromuser=a touser=b indexfile=sql.txt file=a.dmp" as it did not recognise "fromuser"

I couldn't open dmp file either as server do no hav enough memories.


I tried just to create table in schema B and it came back with message:

ORA-01950: no privileges on tablespace 'B'

Do any of you know the syntax to add pricileges to the tablespace?

[kgb]

Originally posted by Fiona
I couldn't "do exp useird=s/s fromuser=a touser=b indexfile=sql.txt file=a.dmp" as it did not recognise "fromuser"

SORRY my fault, you have to use IMP, but not exp.
To get help please type 'imp help=y' or 'exp help=y'

[kgb]

Originally posted by Fiona
I tried just to create table in schema B and it came back with message:
ORA-01950: no privileges on tablespace 'B'
Do any of you know the syntax to add pricileges to the tablespace?

Alter user Fiona quota unlimited on Tablespace_name;
Alter user Fiona quota 10M on Tablespace_name;

[jmodic]

Originally posted by kgb
If you revoke quotas on tablespace and give quota on the other tablespaces, it will not place all your tables in correct tablespace.

That is nonsence. We've cover this topic about a zillion times here....

[tycho]

Hi,

If you make a seconde export with rows=n and import this dumpfile
use constaints=n indexes=n grants=n triggers=n

then move all the new empty tables to the tablespace of interest and import the big dumpfile with ignore=y.

tycho

[Fiona]

I have tried some of the suggested solutiongs (those I understood at least) but the problem is still there.

Schema B has unlimited tablespace priviledge, and yet still refuses to create tables in schema A using "create table..." in its default tablespace D, even when I specify tablespace to D in the syntax.

Any other tables (that doesn't exsist in schema A) is created in tablespace D.

How could this be?