Home » RDBMS Server » Server Utilities » Data not getting inserted to specified tablespace
Data not getting inserted to specified tablespace [message #284151] Thu, 29 November 2007 01:02 Go to next message
pournami
Messages: 8
Registered: November 2007
Junior Member
Hi,

I hav created a new tablespace and a new user.
Even though i hav assigned the new tablespace to the user, while importing , the data is taking system table space.

I am able to extend the system table space and watever path i give ther , its taking. But data is not getting inserted to the new tablespace with the same path of dbf file

How to resolve this issue??

regards
pournami

Re: Data not getting inserted to specified tablespace [message #284153 is a reply to message #284151] Thu, 29 November 2007 01:03 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Use show=y and see the ddl while invoking imp command.
Re: Data not getting inserted to specified tablespace [message #284162 is a reply to message #284151] Thu, 29 November 2007 01:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Revoke unlimited tablespace from your user and remove its quota on system tablespace then all objects (but LOB) will go to its default tablespace.

Regards
Michel
Re: Data not getting inserted to specified tablespace [message #284189 is a reply to message #284162] Thu, 29 November 2007 02:32 Go to previous messageGo to next message
pournami
Messages: 8
Registered: November 2007
Junior Member
hi Arju,

Thanks for the reply..

as said i included Show = y in my imp command and cheked the ddl.. Found the table space as 'system' in the table scripts.
is it because of this , that even while importing ther r taking system tablespace. if so, in such cases how to change for the entire dump??



hi michael ,

thanks for the reply..

as said i revoked the umlimited table space from the user.cheked for quota but nothing has been asssigned.
But no impact has been seen while importing even though i changed.

Also , while creating the user we grant dba privilege. Because of this , the unlimited table space is getting selected in the system privilege option.. right?
if uncheked unlimited tablespace, then wat all privileges should i select if i want to giv the user all the privileges?

regards
pournami

Re: Data not getting inserted to specified tablespace [message #284195 is a reply to message #284189] Thu, 29 November 2007 02:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Revoke DBA, it includes UNLIMITED TABLESPACE.

Regards
Michel
Re: Data not getting inserted to specified tablespace [message #284203 is a reply to message #284151] Thu, 29 November 2007 02:59 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

First try as Michel specified. If you fail with some of tables ( if you have LOB, partitioning table) then extract ddl from dump (easily can be done by indexfile and remove rem from them) and create table in other tablespace and then re import.
Re: Data not getting inserted to specified tablespace [message #284211 is a reply to message #284195] Thu, 29 November 2007 03:11 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

In addition DBA role. revoke also "resource" role if granted
Re: Data not getting inserted to specified tablespace [message #284227 is a reply to message #284151] Thu, 29 November 2007 03:35 Go to previous messageGo to next message
pournami
Messages: 8
Registered: November 2007
Junior Member
Hi michael,

i revoked the DBA grant given..
but now i am not able to import.

i am getting a message as shown below

IMP-00058: ORACLE error 1045 encountered
ORA-01045: user TEST_P lacks CREATE SESSION privilege; logon deniedUsername:
Password:

for this i gave grant for 'create' dddl like create session, create table, etc.
then i got this error;

IMP-00013: only a DBA can import a file exported by another DBA
IMP-00000: Import terminated unsuccessfully

wat to do now??


Hi arju,

I did as Michael said and above listed is the output.
Also, in ur reply u hav mentioned " extract ddl from dump (easily can be done by indexfile and remove rem from them) ".

I am not clear about it.. how to extract the ddl from dump?.. the dump might hav many tables. how to do for all??

hi mohammad,
i hav not grant resource role. only dba role role is present

regards
pournami
Re: Data not getting inserted to specified tablespace [message #284236 is a reply to message #284151] Thu, 29 November 2007 03:48 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Try all of these ..


Quote:

Revoke the "UNLIMITED TABLESPACE" privilege from the user
Revoke the user's quota from the tablespace from where the object was exported. This forces the import utility to create tables in the user's default tablespace.
Make the tablespace to which you want to import the default tablespace for the user
And then Import the table


I afraid these are all suggested Confused

Thumbs Up
Rajuvan

[Updated on: Thu, 29 November 2007 03:49]

Report message to a moderator

Re: Data not getting inserted to specified tablespace [message #284242 is a reply to message #284227] Thu, 29 November 2007 03:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you exported with a DBA, you have to grant back DBA to your import user and use Arju's solution:
- extract the DDL using "import indexfile=..."
- modify the DDL with the correct tablespace
- execute the file
- import with "ignore=y"

Regards
Michel
Re: Data not getting inserted to specified tablespace [message #284251 is a reply to message #284242] Thu, 29 November 2007 04:14 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

@op, only IMP_FULL_DATABASE and CREATE SESSION also work without given DBA role.
Re: Data not getting inserted to specified tablespace [message #284282 is a reply to message #284151] Thu, 29 November 2007 05:27 Go to previous messageGo to next message
pournami
Messages: 8
Registered: November 2007
Junior Member
hi michael and Arju,

thanks a lot for the support given.

i did as u said.

first created the index file, removed rem , then corrected with correct tablespace.

the command given is
imp username/password@db file = x.dmp indexfile = i.dat show =y log = l.log

then after making changes i executed the indexfile after converting to sql file.

then gav command as
imp username/password@db file = x.dmp full = y show =y ignore = y log =l1.log

The data went to the specified tablespace now. But none of the procedures, functions and packages hav been imported..
Can u please tell me where i went wrong and tell me the correct command..

the grant given for user is still DBA.

regards
pournami
Re: Data not getting inserted to specified tablespace [message #284310 is a reply to message #284282] Thu, 29 November 2007 06:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Only you knows what's inside the log file.

Regards
Michel
Re: Data not getting inserted to specified tablespace [message #284316 is a reply to message #284151] Thu, 29 November 2007 06:30 Go to previous messageGo to next message
pournami
Messages: 8
Registered: November 2007
Junior Member
Michael,,,

in the log file ther is no errors reported..infact the procedures has been read but no error speciefied. also the import ended without warnings..

wat i realised is the import has not taken place now.

wat can be the reason??

the same import command has imported all the data earlier but to system tablespace..

the only change now i made in the import command was adding the ignore =y .

except for the tables created as a result of executing the index file, nothing has imported..

is my command missing anything??

Re: Data not getting inserted to specified tablespace [message #284329 is a reply to message #284316] Thu, 29 November 2007 06:51 Go to previous message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Same answer.

Regards
Michel
Previous Topic: Loading data from .dat file
Next Topic: Load datas from Excel sheet to oracle table (merged)
Goto Forum:
  


Current Time: Wed Dec 07 05:15:45 CST 2016

Total time taken to generate the page: 0.11510 seconds