Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Data corruption from: alter table tab_name drop unused columns

Data corruption from: alter table tab_name drop unused columns

From: zhu chao <chao_ping_at_vip.163.com>
Date: Sun, 18 Jan 2004 11:34:26 -0800
Message-ID: <F001.005DD2B9.20040118113426@fatcity.com>


Hi, list friends:

    I hit some strange data corruption in my production database today, from that infamous "unused columns".     Platform is oracle 8172 for solaris. We once had function index on it and later someone dropped the function index. As note 148740.1 said, we have dump file cannot be imported. And I dropped the unused index today, without making a backup of that table.     Strange things happened. We see data corruption. It seems some REAL column is dropped via that command.Analyze table validate structures cascade is ok.select /*+full(a)*/ count(*) from tab a;is ok. But doing a select * from tab hangs,and with many strange characters. From v$session_wait, it shows "sqlnet more message from client". And an event 10046 shows.

select * from bc_bankcard
END OF STMT
PARSE #1:c=1,e=1,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=2,tim=69202236 EXEC #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=2,tim=69202236 WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1650815232 p2=1 p3=0 FETCH #1:c=0,e=0,p=0,cr=1,cu=4,mis=0,r=1,dep=0,og=2,tim=69202236

WAIT #1: nam='SQL*Net message from client' ela= 0 p1=1650815232 p2=1 p3=0
WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1650815232 p2=1 p3=0
WAIT #1: nam='SQL*Net more data to client' ela= 0 p1=1650815232 p2=2024 p3=0
FETCH #1:c=0,e=0,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=2,tim=69202236 *** 2004-01-19 00:53:29.710
WAIT #1: nam='SQL*Net message from client' ela= 1096335 p1=1650815232 p2=1 p3=0 STAT #1 id=1 cnt=16 pid=0 pos=0 obj=7724 op='TABLE ACCESS FULL BC_BANKCARD '

    Later I did various tests to reproduce the error, but cannot reproduce the same result. All new tests are just fine, like metalink note said.

    So, has anyone else hit similiar data corruption via the "alter table tab_name drop unused columns;"? Please share your experience.

Thanks
Zhu Chao.

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: zhu chao
  INET: chao_ping_at_vip.163.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Sun Jan 18 2004 - 13:34:26 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US