?
Solved

Can one import tables to a different tablespace?

Posted on 2003-03-10
31
Medium Priority
?
2,310 Views
Last Modified: 2010-05-18
i have created a table in tbs1 tablepsace. exported it and am tryring to import it to another tablespace. it gives me no problem when the table has any other datatypes than blob. but when blob is a datatype of one of the columns then it fails saying tablespace doesent exist. Please help me at earliest .

Thanks in advance
0
Comment
Question by:dips27_v
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 8
  • 7
  • +4
31 Comments
 
LVL 2

Accepted Solution

by:
Datamonkey earned 800 total points
ID: 8102085
I've seen this sort of thing before. I'm entirely sure what causes it but it has something to do with the way that export parses the create table statement.
I believe the oracle utilities guide says something about this as well.
Anyway, the very simple worksaround is to pre-create the table.
simply run the create table command with the correct tablespace etc. (as the correct user). Then run the import again, it will try to create the table again but obviously that fails because the table already exists so if you run the import with IGNORE=Y the import will then continue to load the rows into the pre-created table.
--dm
0
 
LVL 2

Expert Comment

by:Datamonkey
ID: 8102091
I meant to say "I'm NOT entirely sure", not "I'm entirely sure" :-)
Anyway, I hope this workaround works for you
0
 

Author Comment

by:dips27_v
ID: 8102185
But in my case, i need to have other option. i cant pre create the table
0
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 

Author Comment

by:dips27_v
ID: 8102201
But in my case, i need to have other option. i cant pre create the table
0
 

Expert Comment

by:Jeff_Neill
ID: 8102254
I assume you mean you are importing to another database because if you try to create the table in a different tablespace of the same schema I think you'll get a "ORA-00955: name is already used by an existing object".

On the destination database create a tablespace with the same name as the one you have exported from (ie. tbs1). When you import the data for the table containing the BLOB data the BLOB details will be stored in this tablespace. Rename the table you have created to be something like <table_name>_TEMP:

ALTER TABLE <table_name> RENAME TO <new_table_name>;


Then recreate the original table in the tablespace you want and copy the details from the <table_name>_TEMP into the table:

INSERT INTO <table_name>
SELECT * FROM <table_name>_TEMP;


0
 

Author Comment

by:dips27_v
ID: 8102260
But in my case, i need to have other option. i cant pre create the table
0
 

Author Comment

by:dips27_v
ID: 8102267
actually.. i m getting ORA::00959
can i not avoid the recreation of tables etc.?
it is possible in other datatypes but not in this datatype BLOB
0
 

Author Comment

by:dips27_v
ID: 8102268
actually.. i m getting ORA::00959
can i not avoid the recreation of tables etc.?
it is possible in other datatypes but not in this datatype BLOB
0
 

Author Comment

by:dips27_v
ID: 8102279
actually.. i m getting ORA::00959
can i not avoid the recreation of tables etc.?
it is possible in other datatypes but not in this datatype BLOB
0
 

Author Comment

by:dips27_v
ID: 8102287
actually.. i m getting ORA::00959
can i not avoid the recreation of tables etc.?
it is possible in other datatypes but not in this datatype BLOB
0
 
LVL 2

Expert Comment

by:Datamonkey
ID: 8102517
The 'known problem' I was talking about is specific for LONGs and LOBs. I'm quite sure that there is no other way than to precreate the table. In itself that shouldn't be to much of an issue, even if you haven't got the sql for it anymore you can first import with the show=y option which will give you the exsisting sql. Simply use that to precreate the table
0
 

Expert Comment

by:Jeff_Neill
ID: 8102641
Is it possible to create a link between the two databases you are using? If it is you could use

CREATE TABLE <tablename>
AS SELECT * FROM <tablename>@instance2;

OR

INSERT INTO <tablename>
SELECT * FROM <tablename>@instance2;

0
 

Expert Comment

by:Jeff_Neill
ID: 8102651
Is it possible to create a link between the two databases you are using? If it is you could use

CREATE TABLE <tablename>
AS SELECT * FROM <tablename>@instance2;

OR

INSERT INTO <tablename>
SELECT * FROM <tablename>@instance2;

0
 

Expert Comment

by:Jeff_Neill
ID: 8102693
Is it possible to create a link between the two databases you are using? If it is you could use

CREATE TABLE <tablename>
AS SELECT * FROM <tablename>@instance2;

OR

INSERT INTO <tablename>
SELECT * FROM <tablename>@instance2;

0
 

Expert Comment

by:Jeff_Neill
ID: 8102728
Is it possible to create a link between the two databases you are using? If it is you could use

CREATE TABLE <tablename>
AS SELECT * FROM <tablename>@instance2;

OR

INSERT INTO <tablename>
SELECT * FROM <tablename>@instance2;

0
 

Expert Comment

by:Jeff_Neill
ID: 8102769
Is it possible to create a link between the two databases you are using? If it is you could use

CREATE TABLE <tablename>
AS SELECT * FROM <tablename>@instance2;

OR

INSERT INTO <tablename>
SELECT * FROM <tablename>@instance2;

0
 

Expert Comment

by:Jeff_Neill
ID: 8103251
Is it possible to create a link between the two databases you are using? If it is you could use

CREATE TABLE <tablename>
AS SELECT * FROM <tablename>@instance2;

OR

INSERT INTO <tablename>
SELECT * FROM <tablename>@instance2;

0
 

Author Comment

by:dips27_v
ID: 8103496
this doesent answer my qn
0
 

Author Comment

by:dips27_v
ID: 8103570
this doesent answer my qn
0
 
LVL 2

Expert Comment

by:Datamonkey
ID: 8104346
Dips, I'm really sorry but I gave you the answer to your question in my first comment. You have to precreate a table if you want to place it in a different tablespace than the one it was exported from AND you have a BLOB column.
i really don't think that there are any other workarounds for this tham precreating the table.
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 8104563
I agree with Datamonkey.  Manually creating the table in the different tablespace you want it in is certianly the most direct way to get the table in the tablespace you want.  It may work to revoke the schema owner's quota on the tablespace where the table was, and give that schema a quota on the tablespace you want the table created in, but I'm not sure that import will create it there.

Can you help us understand why creating the table manually is not an option for you?
0
 

Expert Comment

by:giteshtrivedi
ID: 8105030
Dear friend,

If you import from any dmp file then if you have any BLOB datatypes or any partition table than same tablespace must be at your target database.

For BLOB datatypes if you haven't same name of tablespace but you have system tablespace full quota(like resource role)than oracle will create same table with blob in system tablespace and import all rows belongs in.It will be fragment your system tablespace.

For partition table you must have same name tablespaces as well as export database.

If you import thro' ignore=y than that will not working for this circumstances.

I think you should clear.

Gitesh Trivedi.
0
 
LVL 2

Expert Comment

by:Datamonkey
ID: 8105111
that's indeed the same issue as I've been talking about for the last 7 hours...
Sorry dips, it's just one of those issues
0
 

Expert Comment

by:Jeff_Neill
ID: 8109432
Is it possible to create a link between the two databases you are using? If it is you could use

CREATE TABLE <tablename>
AS SELECT * FROM <tablename>@instance2;

OR

INSERT INTO <tablename>
SELECT * FROM <tablename>@instance2;

0
 
LVL 1

Expert Comment

by:legendcain
ID: 8121356
dips, are you still having problems?

i've experienced a very similar issue before ...

unfortunately database links don't help you migrate data when you have LOB type columns in your tables...

if you could answer markgeer's last question, perhaps someone may be able to help you better

-jimbo
0
 
LVL 1

Expert Comment

by:legendcain
ID: 8121382
dips, are you still having problems?

i've experienced a very similar issue before ...

unfortunately database links don't help you migrate data when you have LOB type columns in your tables...

if you could answer markgeer's last question, perhaps someone may be able to help you better

-jimbo
0
 
LVL 2

Expert Comment

by:Datamonkey
ID: 8122463
Hi again Dips,

I've had another look at this and there really is a problem with the combination of trying to import a BLOB and trying to do that into a different schema than the original, as I've been saying all along.

The way around this is, as I have been saying, that you have to precreate the table and then it will all work ok.

If you let us know what the problem is with pre-creating the table, as a few people have asked now, we can probably find a workaround for that problem for you between us.

So please let us know...

--dm
0
 

Author Comment

by:dips27_v
ID: 8125002
C actually i shall be having a database created by my client. I need to import that data to my database. and hence dont want to pre create tht table.
0
 
LVL 2

Expert Comment

by:Datamonkey
ID: 8126420
I'm not sure if you're sending your client the export or if he is sending you his export, but either way I don't think it's that much of a problem realy

1.
If you send the export to your client to import then you can send him a .sql script with it as well to create the table first. surely if you trust the client to run the import you can trust him to run a little sql script. Even if you don't think that's wise you can wrap the whole thing in a small script anyway:
file:create_tabs.sql
---start create_table.sql---
create table ....
exit
---end create_table.sql---
file:<scriptfile>
---start scriptfile----
sqlplus username/password @create_tabs.sql
imp username/password file=... etc.etc.
---end scriptfile----

now just tell him to run the scriptfile which will call sqlplus to create the table first and then it calls the import.


2.
if the customer sends the export to you for you to import there is certainly not a problem.
When you get the export file simple first of all run:
imp username/password file=<filename> show=y table=<tablename>
This will not do anything in the database, it will simply 'show you' what it would do. In other words, it will output the create table command.
copy and paste this command into a sql script, change the tablespaces etc. to fit your needs and run it.
when that is done run the import as normal and that should be it.


So, if you receive the file it's a little bit more work because you might not know the layout of the table so you need to use the show=y option first to find that out. Once you have that it's a simple case of running a small script before the import. Let us know if this causes unacceptable problems but it looks to me as if this is not a hurdle that cannot be taken.
0
 
LVL 13

Expert Comment

by:anand_2000v
ID: 9999333
No comment has been added lately, so it's time to clean up this TA.
I will leave the following recommendation for this question in the Cleanup topic area:

Accept: Datamonkey {http:#8102085}

Please leave any comments here within the next seven days.
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

anand_2000v
EE Cleanup Volunteer
0

Featured Post

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question