Re: 9iR2 import

From: joel garry <joel-garry_at_home.com>
Date: 7 Sep 2006 16:00:42 -0700
Message-ID: <1157670042.837550.51170@h48g2000cwc.googlegroups.com>

Sylvestre wrote:
> Hello
>
> In fact, I ask this cause one of my collegue run a
> "drop column" order on the table where I import
> 20Gig of data, the result was a deadlock and I was
> oblige to kill my import process...
> so if my import session could get a table level
> lock, it will make sense.
>
> Thank you
>

Ummm, what do you expect will happen if you import after the column is dropped? Something like:

. . importing table "PRODUCT_MASTER"

IMP-00058: ORACLE error 904 encountered
ORA-00904: "PL_WRITE_OFF_CODE": invalid identifier
IMP-00017: following statement failed with ORACLE error 20000:

"DECLARE SREC DBMS_STATS.STATREC; BEGIN SREC.MINVAL := NULL; SREC.MAXVAL :="
" NULL; SREC.EAVS := 0; SREC.CHVALS := NULL; SREC.NOVALS := DBMS_STATS.NUMAR"
"RAY(0,0); SREC.BKVALS := DBMS_STATS.NUMARRAY(0,1); SREC.EPC := 2; DBMS_STAT"
"S.SET_COLUMN_STATS(NULL,'"PRODUCT_MASTER"','"PL_WRITE_OFF_CODE"', NULL ,NUL"
"L,NULL,0,0,6805,srec,0,2); END;"
IMP-00003: ORACLE error 20000 encountered ORA-20000: Unable to set values for column PL_WRITE_OFF_CODE: does not exist or insufficient privileges

ORA-06512: at "SYS.DBMS_STATS", line 3397
ORA-06512: at "SYS.DBMS_STATS", line 3555
ORA-06512: at line 1

Import terminated successfully with warnings.

I think it would make more sense to have a DBA in charge of these things. I don't think you had a deadlock, I think you were properly locked out. How would you like to start getting the above errors in the _middle_ of your import? Nobody should be able to insert to a table having its structure modified. Nobody should be able to insert with a structure mismatch. How could that make sense?

It's a little more sensible to be asking (as you are) "how to prevent structure modification while I'm importing?" Unfortunately, commit=n doesn't do it (ie, it would be nice if it locked out the structure change until table is done importing) because partitioning or certain objects prevent deterministically having a single transaction for the table. But even if it did, especially if you are importing more than one table, you'd have a race between the drop column and the imp. So the only answer is manual control of data loads and dictionary modifications by the DBA.

You might mention why you ask when you first post the question. http://www.dbaoracle.net/readme-cdos.htm

--
@home.com is bogus.
Those darn sql injection attacks!
http://www.computerworld.com/action/article.do?command=viewArticleBasic&articleId=9003095&source=rss_topic17

Received on Thu Sep 07 2006 - 18:00:42 CDT