Now I start thinking about processing this activity transaction and a returning segment containing active transactions.
First prepare the Drop this table to try (first back, then Drop, then rebuild):
First use the CTAS backup this table:
SQL> CREATE TABLE WAP_AUTHPRICE_USER_SP_RD060308 TABLESPACE INDEX_SUBN01 NOLOGGING Asselect * from WAP_AUTHPRICE_USER_SP_RD WHERE 1 = 2; 2 Table Created.
SQL> insert into WAP_AUTHPRICE_USER_SP_RD060308 nologging 2 select * from WAP_AUTHPRICE_USER_SP_RD; insert into WAP_AUTHPRICE_USER_SP_RD060308 nologging * ERROR at line 1: ORA-01555: snapshot too old: rollback segment number 19 with name "_SYSSMU19 $" too small
SQL> INSERT INTO WAP_AUTHPRICE_USER_SP_RD060308 NOLOGGING 2 SELECT * WAP_AUTHPRICE_USER_SP_RD WHERE ROWNUM <100;
99 rows created.
SQL> ROLLBACK;
Rollback Complete.
SQL>
We see that every time you scan the database to the database in the rollover segment, the system will report ORA-01555.
This inferred, all the FTS of the table will fail, such as Exp:
Oracle @ rdb01: / oracle> exp revert / abc parfile = a.par
Export: Release 9.2.0.5.0 - Production on Thu Mar 9 14:31:45 2006
CopyRight (C) 1982, 2002, Oracle Corporation. All Rights Reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit ProductionWith the Partitioning and OLAP optionsJServer Release 9.2.0.5.0 - ProductionExport done in ZHS16GBK character set and UTF8 NCHAR character set
About to export specified tables via Direct Path .... exporting table WAP_AUTHPRICE_USER_SP_RDEXP-00056:. ORACLE error 942 encounteredORA-00942: table or view does not existExport terminated successfully with warnings.oracle@rdb01: / oracle>
Now, only DUL is used:
DUL> unload_user_sp_rd; uloading table wap_authprice_user_sp_rd 9430056 Rows unlineddul> EXIT
oracle @ rdb01: / data / tmp / dul> gzip REPORT_WAP_AUTHPRICE_USER_SP_RD.dmporacle@rdb01: / data / tmp / dul> ll REPORT_WAP_AUTHPRICE_USER_SP_RD.dm * -rw-r - r-- 1 oracle dba 49514464 Mar 13 14:18 REPORT_WAP_AUTHPRICE_USER_SP_RD. Dmp.gzoracle@rdb01: / data / tmp / dul> At this time, I suddenly thought of using OracleDebug to track the process or session, perhaps a bit gain:
SQL> oradebug setospid 15664 ORA-00072: process "Unix process pid: 15664, image:" is not activeSQL> oradebug setospid 15664 ORA-00072: process "Unix process pid: 15664, image:" is not activeSQL>
SQL> SELECT PID, SPID, Username from V $ Process Where SPID = 15664;
PID SPID UserName ---------- ------------ --------------- 63 15664 Oracle
SQL> SQL> ORADEBUG SETORAPID 63 ORA-00072: Process "Unix Process PID: 15664, Image: Oracle @ RDB01 (TNS V1-V3)" IS Not Activesql>
Obviously, this result is the result of Kill Process in the operating system, as well as the result of Kill Session in the database - we can't find these processs and session behaviors.
Since the content of Alert.log has been emptied, you can see:
USN Name Status TableSpace_name Addr Sid Serial # UserName Program Machine Osuser ------------------------------------ ------------------------------------------------------------------------------------------------------ ------ ---------------------------- ---------- 19 _syssmu19 $ pense Offline undotbs1 C0000001A7BF5218 71 39809 Report SqlLDR @ rpt01 (TNS CS_RPT01 Report16
This rollback segment is damaged, and the active transaction is a SQLLoader process (the SID 71 is actually executed in the database just seen is just such an INSERT statement). This confirmed my initial guess. For some reason, the developer will be executing the Sqlloader process kill, and at this time, there is a row of activities in the rollback segment, which occurs after 12/30/05 01:48:33, because there is no timely discovery And handle this problem, the database's log has been overwritten, and then, we have happened now.
Although the content of Alert.log is not there, according to what happens, we found the corresponding trace - report_ora_15664.trc, there is the following content:
*** Session ID: (71.25999) 2005-12-30 01: 48: 33.580 *** 2005-12-30 01: 48: 33.580ksedmp: Internal OR Fatal Errorora-00600: Internal Error Code, arguments: [4193] [2987], [2984], [], [], [], [], [] Current SQL Statement for this session: INSERT INTO WAP_AUTHPRICE_USER_SP_RD (LocationID, ICPID, Subscat, Icpattr, Icpcode, Counts, Period, Stat_time) VALUES (: LocationID,: ICPID,: Subscat,: Icpattr,: icpcode,: counts,: period, to_date (: stat_time, 'yyyy-mm-dd hh24: mi: ss')))))
ORA-00600 [4193], this type of error is generally an incorrect of the active returning segment, which can be viewed for Metalink.
Now I have developed a set of execution plans, starting to deal with this problem next Monday (because 24 * 7, this type of risk must be written to the company's leadership and customer leadership, layered approval, huh). . .
