Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2317
  • Last Modified:

Can one import tables to a different tablespace?

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
dips27_v
Asked:
dips27_v
  • 10
  • 8
  • 7
  • +4
1 Solution
 
DatamonkeyCommented:
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
 
DatamonkeyCommented:
I meant to say "I'm NOT entirely sure", not "I'm entirely sure" :-)
Anyway, I hope this workaround works for you
0
 
dips27_vAuthor Commented:
But in my case, i need to have other option. i cant pre create the table
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
dips27_vAuthor Commented:
But in my case, i need to have other option. i cant pre create the table
0
 
Jeff_NeillCommented:
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
 
dips27_vAuthor Commented:
But in my case, i need to have other option. i cant pre create the table
0
 
dips27_vAuthor Commented:
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
 
dips27_vAuthor Commented:
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
 
dips27_vAuthor Commented:
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
 
dips27_vAuthor Commented:
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
 
DatamonkeyCommented:
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
 
Jeff_NeillCommented:
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
 
Jeff_NeillCommented:
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
 
Jeff_NeillCommented:
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
 
Jeff_NeillCommented:
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
 
Jeff_NeillCommented:
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
 
Jeff_NeillCommented:
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
 
dips27_vAuthor Commented:
this doesent answer my qn
0
 
dips27_vAuthor Commented:
this doesent answer my qn
0
 
DatamonkeyCommented:
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
 
Mark GeerlingsDatabase AdministratorCommented:
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
 
giteshtrivediCommented:
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
 
DatamonkeyCommented:
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
 
Jeff_NeillCommented:
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
 
legendcainCommented:
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
 
legendcainCommented:
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
 
DatamonkeyCommented:
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
 
dips27_vAuthor Commented:
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
 
DatamonkeyCommented:
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
 
anand_2000vCommented:
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 10
  • 8
  • 7
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now