OraFAQ Forum: Server Utilities

Home » RDBMS Server » Server Utilities » import issue

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

import issue [message #160507]

Mon, 27 February 2006 01:05

srhl
Messages: 24
Registered: December 2005

Junior Member

for a DB containing some 900 tables,
I want to create a new schema and i just want to import the table structure alone from user1 schema to user2 schema.

exp user1/pwd file=exp.dmp log=exp.log tables=\(user1.%\) grants=n rows=n compress=y statistics=none

imp user2/user2 file=exp.dmp log=imp.log fromuser=user1 touser=user2 ignore=Y grants=n rows=n constraints=n indexes=n tables=\(*\)

Its working fine. but the problem is with tablespace because of large initial extent.
One solution to this is:
to create indexfile and change the intial extent and then run that file but it consumes lot of time.

Any other way to change the intial extent?
Any other solution to this?

Thanks in Advance.

Report message to a moderator

Re: import issue [message #160564 is a reply to message #160507]

Mon, 27 February 2006 05:18

srhl
Messages: 24
Registered: December 2005

Junior Member

Also , The tables are imported to the user1 tablespace.
How can we import to the user2 tablespace?

Report message to a moderator

Re: import issue [message #160569 is a reply to message #160564]

Mon, 27 February 2006 05:36

Littlefoot
Messages: 21813
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

As of wrong import tablespace ... you should alter its default tablespace, such as

ALTER USER user2 DEFAULT TABLESPACE user2's_default_tablespace;

Report message to a moderator

Re: import issue [message #160573 is a reply to message #160569]

Mon, 27 February 2006 05:50

srhl
Messages: 24
Registered: December 2005

Junior Member

thats done but still its going to user1 tablespace only because when i saw the export structure it specifies user1 tablespace.

Is there any parameter i need to specify?

Report message to a moderator

Re: import issue [message #160587 is a reply to message #160573]

Mon, 27 February 2006 07:25

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

>> still its going to user1 tablespace only because when i saw the export structure it specifies user1 tablespace.
It is because the USER2 is been granted with RESOURCE role.
When RESOURCE role is granted , the user gets the permission to write to any datafile in database.
All you have to do is,
Revoke RESOURCE role from USER2.
Make tablespace2 default tablespace of USER2.
Allocate quota for USER2 on tablespace2.
Do the import with fromuser/touser clause.

>> but the problem is with tablespace because of large initial extent.
It is because you are using this during export.
>> compress=y
It is bad,bad,bad.
use compress=n.
Read documentation. see what that parameter does for you.

Report message to a moderator

Re: import issue [message #160655 is a reply to message #160587]

Mon, 27 February 2006 22:20

srhl
Messages: 24
Registered: December 2005

Junior Member

Hi Mahesh Rajendran,
Thanks...
Now the table is created in the user2 tablespace.
One more doubt,
Even with compress = n, the table without data occupies same space as table with data

Report message to a moderator

Re: import issue [message #160785 is a reply to message #160655]

Tue, 28 February 2006 06:21

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

Please post what you done.
session logs/expor import statments, how you calculated the space etc.

Report message to a moderator

Re: import issue [message #161074 is a reply to message #160785]

Wed, 01 March 2006 21:42

srhl
Messages: 24
Registered: December 2005

Junior Member

now i am able to import it to user2 tablespace but for some tables i am getting the data also getting imported.

this is the exp/imp command i used:

exp user1/user1 compress=y rows=n statistics=none file=sample_dummy.dmp log=sample_dummy.log

imp user2/user2 file=sample_dummy.dmp log=imp_sample.log fromuser=user1 touser=user2 ignore=Y grants=n

Thanks for your help Mahesh.

Report message to a moderator

Re: import issue [message #161075 is a reply to message #161074]

Wed, 01 March 2006 21:49

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

Not possible.
WIth rows=n in export, you are not exporting any rows at all.

Report message to a moderator

Re: import issue [message #161077 is a reply to message #161075]

Wed, 01 March 2006 21:59

srhl
Messages: 24
Registered: December 2005

Junior Member

The table which gets the data, has given the following error in import log:
1.
IMP-00003: ORACLE error 959 encountered
IMP-00017: following statement failed with ORACLE error 959:
<create table statement>
ORA-00959: tablespace '<tablespace in source but not in destination>' does not exist

2.
and for some of the tables, it gives the follwoing error:
ORA-01659: unable to allocate MINEXTENTS beyond 91 in tablespace <user2 tablespace>

but anyway, i am getting all the table structure.

Report message to a moderator

Re: import issue [message #161164 is a reply to message #161077]

Thu, 02 March 2006 06:06

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

1). Means a tablespace in source does not exist in target or the us has no privs to write to that tablespace.
2). Becuase of differences in tablespace stroage parameters.
Create the tablespace in a similiar way as in source and give more space.
first , try this.
As i stated before, DO NOT GIVE compress=y during export.

Report message to a moderator

Previous Topic:	error in export
Next Topic:	exp compatibility with lower verison of Oracle database

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Jul 05 18:59:48 CDT 2024