Exporting LOB Tables 2

[THoey]

Are there any known issues with exporting LOB tables? I did an export from database A, schema A. Then did an import of that data into database B, schema B. The import generates warnings as it is trying to create three of the 82 tables in a particular tablespace instead of the default user tablespace. The only difference I can see in the tables that generated the warning were new tables with fields of LOB type.

Suggestions or comments?

Terry
**************************
* General Disclaimer - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...

 

[Thargy]

in my experience, export and import cope with LOBS just fine.

Is the target tablespace set to autoextend to sufficient size to accommodate the huge influx of data?

Can you check on the space requirements, and/or temporarily allow the target tablespace to be unlimited in size. LOB's are gigabyte gobblers, so don't be surprised if you hit sizing issue.

What warnings are you getting from the import?
Can you post an excerpt from the log file?

Regards

Tharg

Grinding away at things Oracular

 

[THoey]

To me the issue is that due to them having LOB's, they are trying to write to a different tablespace than the default that I want it to. All the other tables get created in the default tablespace fine. Below is the log:

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning and Data Mining options

Export file created by EXPORT:V09.02.00 via conventional path

Warning: the objects were exported by EASL_PROD_SEC, not by you

import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
import server uses US7ASCII character set (possible charset conversion)
IMP-00017: following statement failed with ORACLE error 1950:
 "CREATE TABLE "STB_SIM_TESTDATA_SET" ("SIM_TESTDATA_SET_ID" NUMBER(5, 0), "A"
 "CCOUNT_ID" NUMBER(5, 0) NOT NULL ENABLE, "SERVICE_METHOD_ID" NUMBER(5, 0) N"
 "OT NULL ENABLE, "SIM_SERVICE_RETURN" CLOB, "MODIFIED_TIMESTAMP" TIMESTAMP ("
 "6) NOT NULL ENABLE, "OWNER" VARCHAR2(6) NOT NULL ENABLE, "UPDATED_BY" VARCH"
 "AR2(6) NOT NULL ENABLE)  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STOR"
 "AGE(INITIAL 131072 FREELISTS 1 FREELIST GROUPS 1) TABLESPACE "SMFDATA" LOGG"
 "ING NOCOMPRESS LOB ("SIM_SERVICE_RETURN") STORE AS  (TABLESPACE "SMFDATA" E"
 "NABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10 NOCACHE  STORAGE(INITIAL 1310"
 "72 FREELISTS 1 FREELIST GROUPS 1))"
IMP-00003: ORACLE error 1950 encountered
ORA-01950: no privileges on tablespace 'SMFDATA'
IMP-00017: following statement failed with ORACLE error 1950:
 "CREATE TABLE "STB_TESTCASE" ("TESTCASE_ID" NUMBER(5, 0), "ARTT_CLASS_ID" NU"
 "MBER(5, 0) NOT NULL ENABLE, "ACCOUNT_ID" NUMBER(5, 0) NOT NULL ENABLE, "TES"
 "TCASE_NAME" VARCHAR2(100) NOT NULL ENABLE, "TESTCASE_DESCRIPTION" VARCHAR2("
 "1000), "INPUT" CLOB, "SIM_RETURN_OVERRIDE" CLOB, "EXPECTED_TESTCASE_OUTPUT""
 " CLOB, "CREATION_TIMESTAMP" TIMESTAMP (6) NOT NULL ENABLE, "MODIFIED_TIMEST"
 "AMP" TIMESTAMP (6) NOT NULL ENABLE, "OWNER" VARCHAR2(6) NOT NULL ENABLE)  P"
 "CTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 131072 FREELIS"
 "TS 1 FREELIST GROUPS 1) TABLESPACE "SMFDATA" LOGGING NOCOMPRESS LOB ("INPUT"
 "") STORE AS  (TABLESPACE "SMFDATA" ENABLE STORAGE IN ROW CHUNK 8192 PCTVERS"
 "ION 10 NOCACHE  STORAGE(INITIAL 131072 FREELISTS 1 FREELIST GROUPS 1)) LOB "
 "("SIM_RETURN_OVERRIDE") STORE AS  (TABLESPACE "SMFDATA" ENABLE STORAGE IN R"
 "OW CHUNK 8192 PCTVERSION 10 NOCACHE  STORAGE(INITIAL 131072 FREELISTS 1 FRE"
 "ELIST GROUPS 1)) LOB ("EXPECTED_TESTCASE_OUTPUT") STORE AS  (TABLESPACE "SM"
 "FDATA" ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10 NOCACHE  STORAGE(INIT"
 "IAL 131072 FREELISTS 1 FREELIST GROUPS 1))"
IMP-00003: ORACLE error 1950 encountered
ORA-01950: no privileges on tablespace 'SMFDATA'
IMP-00017: following statement failed with ORACLE error 1950:
 "CREATE TABLE "STB_TESTCASE_LOG" ("CALL_ID" VARCHAR2(100), "ARTT_CLASS_ID" N"
 "UMBER(5, 0) NOT NULL ENABLE, "TESTRUN_ID" NUMBER(5, 0) NOT NULL ENABLE, "AN"
 "I" VARCHAR2(15) NOT NULL ENABLE, "LOGGED_TIMESTAMP" TIMESTAMP (6) NOT NULL "
 "ENABLE, "TESTCASE_ID" NUMBER(5, 0) NOT NULL ENABLE, "TESTCASE_NAME_LOG" VAR"
 "CHAR2(100) NOT NULL ENABLE, "TESTCASE_DESC_LOG" VARCHAR2(1000), "INPUT" CLO"
 "B, "SIM_RETURN_OVERRIDE" CLOB, "EXPECTED_TESTCASE_OUTPUT" CLOB NOT NULL ENA"
 "BLE, "ENABLE_SIM_LOG" VARCHAR2(100), "TESTCASE_RETURN" CLOB, "TESTCASE_PASS"
 "" CHAR(1) NOT NULL ENABLE)  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 S"
 "TORAGE(INITIAL 131072 FREELISTS 1 FREELIST GROUPS 1) TABLESPACE "SMFDATA" L"
 "OGGING NOCOMPRESS LOB ("INPUT") STORE AS  (TABLESPACE "SMFDATA" ENABLE STOR"
 "AGE IN ROW CHUNK 8192 PCTVERSION 10 NOCACHE  STORAGE(INITIAL 131072 FREELIS"
 "TS 1 FREELIST GROUPS 1)) LOB ("SIM_RETURN_OVERRIDE") STORE AS  (TABLESPACE "
 ""SMFDATA" ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10 NOCACHE  STORAGE(I"
 "NITIAL 131072 FREELISTS 1 FREELIST GROUPS 1)) LOB ("EXPECTED_TESTCASE_OUTPU"
 "T") STORE AS  (TABLESPACE "SMFDATA" ENABLE STORAGE IN ROW CHUNK 8192 PCTVER"
 "SION 10 NOCACHE  STORAGE(INITIAL 131072 FREELISTS 1 FREELIST GROUPS 1)) LOB"
 " ("TESTCASE_RETURN") STORE AS  (TABLESPACE "SMFDATA" ENABLE STORAGE IN ROW "
 "CHUNK 8192 PCTVERSION 10 NOCACHE  STORAGE(INITIAL 131072 FREELISTS 1 FREELI"
 "ST GROUPS 1))"
IMP-00003: ORACLE error 1950 encountered
ORA-01950: no privileges on tablespace 'SMFDATA'
Import terminated successfully with warnings.

Terry
**************************
* General Disclaimer - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...

 

[Thargy]

Terry,

the

ORA-01950: no privileges on tablespace 'SMFDATA'

seems significant. I know from your post that data is being written, but is SMFDATA the default tablespace that you want to load into?

If not, could you allow the load to go ahead, and then move your tables to the desired tablespace afterwards?
(Obviously the notion is useless if this is to become a regular activity).

Regards

Tharg

Grinding away at things Oracular

 

[THoey]

Yeah, that is the major issue. I am trying to figure out why the import is trying to use a tablespace besides the default. I ended up letting it run and then recreating the tables via scripts, but it was just a question I had. Luckily, right now those tables are empty. Next time we do this (about monthly) we may not be so lucky.

That SMFDATA tablespace does exist on that DB, so I can grant the schema permissions and then try to move it to the appropriate tablespace, but that seems unnecessary.

Terry
**************************
* General Disclaimer - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...

 

[Thargy]

Terry,

was the data exported from the SMFDATA tablespace?

If it was, then isn't the system performing correctly and trying to load it back into SMFDATA?

T

Grinding away at things Oracular

 

[THoey]

Maybe I am misunderstanding, but shouldn't it try to load to the default tablespace of the new schema it is being loaded into?

That being asked, all of the tables in the export (82) are stored in the SMFDATA schema of the exported instance, but only these three tables fail creation when imported.

Thanks for hanging in there Tharg. I am just trying to get my hands around this concept.

Terry
**************************
* General Disclaimer - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...

 

[Thargy]

terry,

I reckon you should just grant privileges on the SMFDATA tablespace, and see what happens. To my shame, I confess to not knowing what imp will do - but I surmise that it will try to load into the same tablespace.

Can you let this happen, just to prove the point one way or another?

I know it's Friday, and I assume you're in U.S., so a tough afternoon, but please do give it a go, and let me know what happens.

Regards

T

Grinding away at things Oracular

 

[THoey]

Thanks for following up. Unfortunately, I will be unable to let it run through just to test the import. We finished our deployment to that server on Friday and it is in use now. I'yy try to do it when we have our next deployment.

Thanks again.

Terry
**************************
* General Disclaimer - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...

 

[THoey]

Just a follow up. I was able to grant unlimited quota to that tablespace and then the import performs without an issue. Just weird that only the tables with CLOB fields try to write to a specific tablespace and the rest write to the default tablespace for the schema.

Thanks again.

Terry
**************************
* General Disclaimer - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...

 

[Thargy]

thoey,

you are one of the very few who bother to close out their posts.

I thank you for taking the time to post this response.
therefore, have a purple pointy thingy.

Regards

Tharg

Grinding away at things Oracular