Home » RDBMS Server » Server Utilities » Import to different user with different tablespace
Import to different user with different tablespace [message #74262] Mon, 25 October 2004 23:28 Go to next message
Uwe
Messages: 260
Registered: February 2003
Location: Zürich, Switzerland
Senior Member
Hi all,

I have a maybe simple question.

We need to import data from one user to another user who have another tablespaace to store the data. Bothe user/schema residing within one database.

I tried to imp with fromuser and touser but it was stored inthe originally tablespace and not in the tablespace for the second user.

The default tablespace of user2 is the the tablesspace i want to import in.

any help is stongly welcome

 

regards

Uwe
Re: Import to different user with different tablespace [message #74265 is a reply to message #74262] Tue, 26 October 2004 03:23 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
it is becuase the user2 is been granted with RESOURCE role.
RESOURCE role gives the ability for the user to write in ANY tablespace.
during import ,
first the table ddl is read and created in target.
Since the table ddl has the original tablespace defined, the new table is also created in the original tablespace ( from where it came).

REVOKE the resource role from user2.
Assign a default tablespace (tablespace2) for user2.
allocate quota on the tablespace2 for user2.
do the import using fromuser/touser options.
Re: Import to different user with different tablespace [message #74275 is a reply to message #74265] Tue, 26 October 2004 22:45 Go to previous messageGo to next message
Uwe
Messages: 260
Registered: February 2003
Location: Zürich, Switzerland
Senior Member
Hi Mahesh,

thanks for your reply.
It seems that this won't work in some parts.
I revoke the resource from the target user and import as target user with fromuser=orig_user and touser=target_user.
But some tables could not be created with ORA1950 because he will create them in the original tablespace.
What's happened here ??
any suggestions ?

regards
Uwe
Re: Import to different user with different tablespace [message #74280 is a reply to message #74275] Wed, 27 October 2004 04:14 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Post the complete error message.
assuming the error message is
ora-01950
docs say.

ORA-01950: no privileges on tablespace "<name>"
Cause: The attempt to give the user a tablespace quota
failed because the user does not have
the necessary system privileges.
Action: Either
grant the user the system privileges needed to
create objects in the specified tablespace or grant the user a specific space resource in the tablespace.

</endquote>

so give unlimited quota on the new tablespace.
if there are any ther specific privs required to create the table, grant it.
Re: Import to different user with different tablespace [message #74281 is a reply to message #74280] Wed, 27 October 2004 05:11 Go to previous messageGo to next message
Uwe
Messages: 260
Registered: February 2003
Location: Zürich, Switzerland
Senior Member
yes, it is because the user does not have the permissions on that tablespace. But he should not have.

We have 2 users prod and test and 2 tablespaces prod and test. Now everything is installed in prod. I exported everything from prod with user prod and try to import into test as test with fromuser=prod touser=test.
Now around 75 % of all tables will be imported to test, only a few the import will import to prod. But all should go to test and no single table to prod - because we need it devided.

Why does the import tries to import them to prod ?
I revoke RESOURCE from user test and gave him full quota on tablespace test.

I started it with
imp test/test file=prod.dmp log=test.log fromuser=prod touser=test

The export is created as user prod.

Uwe
Re: Import to different user with different tablespace [message #74283 is a reply to message #74281] Wed, 27 October 2004 06:29 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
If you would have revoked RESOURCE role from user TEST and grant him quota on ONLY ONE TABLESPACE ( test) then there is NO WAY that the import is going to write the tables back to tablespace prod.
POst your session with complete error message ( relevant)
IN the base tables ( source tables) are there any indeces create in a seperate tablespaces?
or any LOB segments stored separatelY?

Make sure the other user is not having DBA role granted.
Re: Import to different user with different tablespace [message #74286 is a reply to message #74283] Wed, 27 October 2004 22:24 Go to previous messageGo to next message
Uwe
Messages: 260
Registered: February 2003
Location: Zürich, Switzerland
Senior Member
DBA is not granted, because of the not importable tables in the prod tablespace. I could not post the complete logfile, but I will send you from one table which could not created in the prod-tablespace. But thgis table should be created in test and not in prod !
IMP-00017: following statement failed with ORACLE error 1950:
"CREATE TABLE "PACKSTUECKBEDINGUNG" ("TAGFIELD" VARCHAR2(100), "CASESENSITIV"
"E" NUMBER(1, 0), "ORACLEHINT" VARCHAR2(508), "PROFBEDBEMERKUNG" VARCHAR2(50"
"0), "PACKSTUECKNRVON" VARCHAR2(20), "SYSUSRKUERZEL" VARCHAR2(20), "PACKSTUE"
"CKNRBIS" VARCHAR2(20), "PCKARTCODE" VARCHAR2(20), "BOSTATUSCODE" VARCHAR2(4"
"), "PCKINHCODE" NUMBER(20, 0), "PROFBEDCODE" NUMBER(20, 0) NOT NULL ENABLE,"
" "ISGESCHLOSSEN" NUMBER(1, 0), "VALIDFORGROUPKUERZEL" VARCHAR2(20), "EXTERN"
"CRITERIA" VARCHAR2(500), "ISBOSTATUSNEW" NUMBER(1, 0), "REFNRBEIMSPEDITEUR""
" VARCHAR2(100), "PROFBEDNAME" VARCHAR2(100), "EINLIEFERUNGSNR" VARCHAR2(20)"
", "CLASSCODE" VARCHAR2(100), "BEZEICHNUNG" VARCHAR2(30), "LASTMODIFIED" DAT"
"E, "LASTMODIFIEDBY" VARCHAR2(20), "PROFBEDBEDINGUNG" BLOB) PCTFREE 10 PCTU"
"SED 40 INITRANS 1 MAXTRANS 255 LOGGING STORAGE(INITIAL 16384 NEXT 524288 MI"
"NEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS"
" 1 BUFFER_POOL DEFAULT) TABLESPACE "ASSIST_DATA01" LOB ("PROFBEDBEDINGUNG")"
" STORE AS (TABLESPACE "ASSIST_DATA01" ENABLE STORAGE IN ROW CHUNK 8192 PCT"
"VERSION 10 NOCACHE STORAGE(INITIAL 16384 NEXT 524288 MINEXTENTS 1 MAXEXTEN"
"TS 2147483645 PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFA"
"ULT))"
IMP-00003: ORACLE error 1950 encountered
ORA-01950: no privileges on tablespace 'ASSIST_DATA01'

Where ASSIST_DATA01 is the prod-tablespace.
LOB's are inside some tables, but we have no seperately tablespace for LOB.
Re: Import to different user with different tablespace [message #74287 is a reply to message #74286] Thu, 28 October 2004 04:19 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Ok.
You see, there are LOBS.
LOBS should be handled differently.
regular export / import will NOT handle this.

METHOD 1:

generate the ddl for all the tables that have the lob (use indexfile option during import
or use dbms_metadata).
Please have a look here for examples for both methods.

http://www.orafaq.com/forum/t/22316/0/
http://www.orafaq.com/forum/t/20862/0/

edit the ddl and replace the tablespace PROD to USER/TEST.
Make user replace all entries (both for table data segment and lob segment).
create ONLY those tables with LOBs in the USER schema
DO the import with ignore=y

METHOD 2:

Let the user have privs on the PROD schema (temporarily, give quota on the the prod tablespace) 
do the import as usual.
all the regular tables  will be created in USER tablespace.
ONly tables with LOBS will be created in PROD tablespace.
Now MOVE those tables along with lobs (owned by TEST USER by residing in PROD tablespace) to TEST tablespace.
Only everything is all set, check whehter all tables and lobs are in TEST tablepace.
IF so, revoke the  RESOURCE role
grant quota unlimited ONLY ON test tablespace.

THere is something you need to know.
THERE IS a known intermittent bug you may encounter in oracle 9.2.0.1 if you follow the above procedure.
Moving the lobs may create some ora-600 internal erros or soft corruptions may occur.

Please have look here for example to move a table with lob segment.
Make sure you move the table,lobsegment and lobindex.

http://www.orafaq.net/msgboard/server/messages/20908.htm

[Updated on: Fri, 18 February 2005 23:32]

Report message to a moderator

Re: Import to different user with different tablespace [message #74300 is a reply to message #74287] Mon, 08 November 2004 03:41 Go to previous messageGo to next message
Uwe
Messages: 260
Registered: February 2003
Location: Zürich, Switzerland
Senior Member
Hi Mahesh,

I tried the first way with indexfile option. We're using 8.1.7.4, but I could not import data in the manually created tables.
If I start the import with
imp test/test file=dumpfile fromuser=prod touser=test ignore=y log=test.log
I got error messages for all previously by hand generated tables with LOB's . I got ORA-0904. invalid column name. But this are the original names from the prod tables.
Also the building of the contraints on the end of file fails with ORA-2298
Any hints now ?

regards
Uwe
Re: Import to different user with different tablespace [message #74302 is a reply to message #74287] Mon, 08 November 2004 04:00 Go to previous messageGo to next message
Uwe
Messages: 260
Registered: February 2003
Location: Zürich, Switzerland
Senior Member
Hi Mahesh,

I tried the first way with indexfile option. We're using 8.1.7.4, but I could not import data in the manually created tables.
If I start the import with
imp test/test file=dumpfile fromuser=prod touser=test ignore=y log=test.log
I got error messages for all previously by hand generated tables with LOB's . I got ORA-0904. invalid column name. But this are the original names from the prod tables.
Also the building of the contraints on the end of file fails with ORA-2298
Any hints now ?

regards
Uwe
Re: Import to different user with different tablespace [message #74306 is a reply to message #74300] Mon, 08 November 2004 23:38 Go to previous messageGo to next message
Uwe
Messages: 260
Registered: February 2003
Location: Zürich, Switzerland
Senior Member
Hi,
I guess I found the cause of our last problems. It seems that the Database loses some fields.
There are check constraint violations - Parent key not found. This seems the problem, but I need to ask the company who installed the product how they work with such constraints.
We don't change anything in the db-layout.
thanks for your help so far, I will use this after we know how to deal with this problem.
ciao
Uwe
Re: Import to different user with different tablespace [message #74308 is a reply to message #74306] Tue, 09 November 2004 04:02 Go to previous message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
It seems im no longer notified when there is a reply to my posting ( a used-to-be functionality in these orafaq forums). So i am not aware of your replies.
Please open a new thread to post again...else it would never CATCH any eyes~
Previous Topic: Error in Export Database
Next Topic: seting the long of line in svrmgr30
Goto Forum:
  


Current Time: Mon Apr 29 08:24:21 CDT 2024