ORA-01555 Principle Analysis

ORA-01555 Principle Analysis

SQL> create undo tablespace undotbs1 datafile '/opt/app/oracle/RHYS/undotbs1.dbf' size 30 M; Tablespace created. SQL> alter system set undo_tablespace = undotbs1; System altered. SQL> select. usn,. status, B. name from v $ rollstat a, v $ rollname B where. usn = B. usn; usn status name ---------- --------------- -------------------------------- 0 online system 1 ONLINE _ SYSSMU1_3143656763 $2 ONLINE _ SYSSMU2_26130088 03 $3 ONLINE _ SYSSMU3_4015766757 $4 ONLINE _ shopping $5 ONLINE _ syssmu5_13700003473 $6 ONLINE _ shopping $7 ONLINE _ shopping $8 ONLINE _ SYSSMU8_2318326044 $9 ONLINE _ shopping $10 ONLINE _ SYSSMU10_2612552350 $11 rows selected. SQL> show parameter undoNAME TYPE VALUE -----------------------------------------------------------------------------_ Optional string 11.2.0.1 _ undo_autotune boolean FALSEundo_management string AUTOundo_retention integer 3600undo_tablespace string UNDOTBS1SQL> alter system set undo_management = manual scope = spfile; System altered. SQL> alter system set event = "10203 trace name context forever" scope = spfile; System altered. SQL> shutdown immediateDatabase closed. database dismounted. ORACLE instance shut d Own. SQL> startupORACLE instance started. total System Global Area 405020672 bytesFixed Size 2213816 bytesVariable Size 260048968 bytesDatabase Buffers 138412032 bytesRedo Buffers 4345856 bytesDatabase mounted. database opened. SQL> select * from v $ rollname; USN NAME ---------- -------------------------------- 0 SYSTEMSQL> create rollback segment one tablespace undotbs1; Rollback segme Nt created. SQL> select * from v $ rollname; USN NAME ---------- -------------------------------- 0 SYSTEMSQL> alter rollback segment one online; Rollback segment altered. SQL> select * from v $ rollname; USN NAME ---------- ------------------------------ 0 SYSTEM 21 ONESQL> show parameter undoNAME TYPE VALUE values ------------- condition _ optimizer_undo_cost_c Hange string 11.2.0.1 _ undo_autotune boolean FALSEundo_management string comment integer 3600undo_tablespace string UNDOTBS1SQL> SQL> set linesize 200SQL> col value for a60SQL> col name for a20SQL> select * from t1; id sal job ---------- 2 3 B 2 1 a 3 DSQL> update t1 set id = 1 where sal = 3; 1 row updated. SQL> select usn, xacts from v $ rollstat where xacts> 0; USN XACTS -- -------- ---------- 21 1SQL> select xidusn, ubafil, ubablk from v $ transaction; xidusn ubafil ubablk ---------- 21 3 290SQL> alter system dump datafile 3 block 290; System altered. SQL> set linesize 200SQL> select * from v $ diag_info; INST_ID NAME VALUE ------------------------ limit 1 Diag Enabled TRUE 1 ADR Base/opt/app /Oracle 1 ADR Home/opt/app/oracle/diag/rdbms/rhys/RHYS 1 Diag Trace/opt/app/oracle/diag/rdbms/rhys/RHYS/trace 1 Diag alert/opt/app/oracle/diag/rdbms/rhys/RHYS/alert 1 Diag Incident/opt/app/oracle/diag/rdbms/rhys/RHYS/incident 1 Diag Cdump/ opt/app/oracle/diag/rdbms/rhys/RHYS/cdump 1 Health Monitor/opt/app/oracle/diag/rdbms/rhys/RHYS/hm 1 Default Trace File/opt /app/oracle/diag/rdbms/rhys/RHYS /Trace/RHYS_ora_3316.trc 1 Active Problem Count 1 1 Active Incident Coun 1 INST_ID name value ---------- ------------------ define t11 rows selected. SQL>! Vi/opt/app/oracle/diag/rdbms/rhys/RHYS/trace/RHYS_ora_3316.trc view the image before undo: * Rec #0x16 slt: 0x3c objn: 73888 (0x000120a0) objd: 73888 tblspc: 5 (0x00000005) * Layer: 11 (Row) opc: 1 rci 0x00Undo type: Regular undo Begin trans Last buffer split: NoTemp Object: NoTablespace Undo: nordba: 0x00000000Ext idx: 0flg2: 0 * ------------------------------- uba: 0x00c00122. 016e. 13 ctl max scn: 0x0000. 00acf0a4 prv tx scn: 0x0000. 00acf0a6txn start scn: 0x0000. 00acf1_logon user: 0 prev brb: 12583237 prev bcl: 0KDO undo record: KTB Redoop: 0x04 ver: 0x01compat bit: 4 (post-11) padding: 1op: L itl: xid: 0x000d. 016.000000d8 uba: 0x020000ed. 02b6. 08 flg: C --- lkc: 0 scn: 0x0000. 00aa0d31KDO Op code: URP row dependencies Disabled xtype: XA flags: 0x00000000 bdba: 0x01400087 hdba: 0x01400082itli: 1 ispac: 0 maxfr: 4858 tabn: 0 slot: 1 (0x1) flag: 0x2c lock: 0 ckix: 0 ncol: 3 nnew: 1 size: 0col 0: [2] c1 03SQL> select utl_raw.cast_to_number (replace ('c1 03', '') a from dual; A ---------- 2SQL> View undoheader trn tbl :: index state cflags wrap # uel scn dba parent-xid nub stmt_num defaults 0x00 9 0x80 0x0024 0x0060 0x0000. 00acf3c9 0x00c0016c 0x0000. 000.00000000 0x00000001 0x00000000 0x39 9 0x80 0x0023 0x0031 0x0000. 00acf37f 0x00c00163 0x0000. 000.00000000 0x00000001 0x00000000 0x3a 9 0x80 0x0023 0x003e 0x0000. 00acf38c 0x00c0016a 0x0000. 000.00000000 0x00000001 0x00000000 0x3b 9 0x80 0x0023 0x0035 0x0000. 00acf384 0x00c0016a 0x0000. 000.00000000 0x00000001 0x00000000 0x3c 10 0x80 0x0022 0x0000 0x0000. 00acf00000x00c00122 0x0000. 000.00000000 0x00000001 0x00000000 0x3d 9 0x80 0x0023 0x0046 0x0000. 00acf39e 0x00c0016b 0x0000. 000.00000000 0x00000002 0x00000000 View data block information: SQL> alter system dump datafile 5 block 135; System altered. itl Xid Uba Flag Lck Scn/Fsc0x01 0x0015. 03c. 00000022 0x00c00122. 016e. 16 ---- 1 fsc 0x0000.000000000x02 0x000b. 016.000000df 0x02006448. 04fb. 07 C --- 0 scn 0x0000. 00ab118abdba: 0x01400087data_block_dump, data header at 0x7ff873eada64 ================ tsiz: 0x1f98hsiz: 0x1epbl: release 76543210 flag = -------- ntab = 1 nrow = 6 frre = 0 fsbo = 0x1efseo = 0x1f39avsp = 0x1f5btosp = 0x1f5b0xe: pti [0] nrow = 6 offs = 00x12: pri [0] sfll = 20x14: pri [1] offs = 0x1f820x16: pri [2] sfll = 30x18: pri [3] sfll =-10x1a: pri [4] offs = 0x1f610x1c: pri [5] offs = 0x1f39block_row_dump: tab 0, row 1, @ 0x1f82tl: 11 fb: -- H-FL -- lb: 0x1 cc: 3col 0: [2] c1 02col 1: [2] c1 04col 2: [1] 62tab 0, row 4, @ 0x1f61tl: 11 fb: -- H-FL -- lb: 0x0 cc: 3col 0: [2] c1 03col 1: [2] c1 02col 2: [1] 61tab 0, row 5, @ 0x1f39tl: 9 fb: -- H-FL -- lb: 0x0 cc: 3col 0: [2] c1 04col 1: * NULL * col 2: [1] 44end_of_block_dumpEnd dump data blocks tsn: 5 file #: 5 minblk 135 maxblk 135 submit the transaction: view the undo header trn tbl :: index state cflags wrap # uel scn dba parent-xid nub stmt_num defaults 0x00 9 0x00 0x0026 0x0060 0x0000. 00acf596 0x00c00175 0x0000. 000.00000000 0x00000001 0x0000000 0x36 9 0x80 0x0026 0x0034 0x0000. 00acf5fe 0x00c00179 0x0000. 000.00000000 0x00000001 0x00000000 0x37 9 0x80 0x0026 0x003e 0x0000. 00acf61e 0x00c0017d 0x0000. 000.00000000 0x00000002 0x00000000 0x38 9 0x80 0x0026 0x0035 0x0000. 00acf60c 0x00c0017b 0x0000. 000.00000000 0x00000002 0x00000000 0x39 9 0x80 0x0026 0x0033 0x0000. 00acf604 0x00c0017a 0x0000. 000.00000000 0x00000001 0x00000000 0x3a 9 0x80 0x0026 0x0037 0x0000. 00acf610 0x00c0017c 0x0000. 000.00000000 0x00000001 0x00000000 0x3b 9 0x80 0x0026 0x003a 0x0000. 00acf60f 0x00c0017c 0x0000. 000.00000000 0x00000002 0x00000000 0x3c 9 0x80 0x0022 0 xffff 0x0000. 00acf666 0x00c00122 0x0000. 000.00000000 0x00000001 0x00000000 view undo pre-image: * ----------------------------- * Rec #0x16 slt: 0x3c objn: 73888 (0x000120a0) objd: 73888 tblspc: 5 (0x00000005) * Layer: 11 (Row) opc: 1 rci 0x00Undo type: Regular undo Begin trans Last buffer split: NoTemp Object: NoTablespace Undo: Nordba: 0x00000000Ext idx: 0flg2: 0 * 0000uba: 0x00c00122. 016e. 13 ctl max scn: 0x0000. 00acf0a4 prv tx scn: 0x0000. 00acf0a6txn start scn: 0x0000. 00acf1_logon user: 0 prev brb: 12583237 prev bcl: 0KDO undo record: KTB Redoop: 0x04 ver: 0x01compat bit: 4 (post-11) padding: 1op: L itl: xid: 0x000d. 016.000000d8 uba: 0x020000ed. 02b6. 08 flg: C --- lkc: 0 scn: 0x0000. 00aa0d31KDO Op code: URP row dependencies Disabled xtype: XA flags: 0x00000000 bdba: 0x01400087 hdba: 0x01400082itli: 1 ispac: 0 maxfr: 4858 tabn: 0 slot: 1 (0x1) flag: 0x2c lock: 0 ckix: 0 ncol: 3 nnew: 1 size: 0col 0: [2] c1 03 View data block information: itl Xid Uba Flag Lck Scn/Fsc0x01 0x0015. 03c. 00000022 0x00c00122. 016e. 16 ---- 1 fsc 0x0000.000000000x02 0x000b. 016.000000df 0x02006448. 04fb. 07 C --- 0 scn 0x0000. 00ab118abdba: 0x01400087data_block_dump, data header at 0x7ff873eada64