Import to different user with different tablespace [message #74262] |
Mon, 25 October 2004 23:28 |
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 #74275 is a reply to message #74265] |
Tue, 26 October 2004 22:45 |
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 #74281 is a reply to message #74280] |
Wed, 27 October 2004 05:11 |
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 #74286 is a reply to message #74283] |
Wed, 27 October 2004 22:24 |
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 |
|
Mahesh Rajendran
Messages: 10708 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 |
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 |
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 |
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
|
|
|
|