|
ORA-01555原理分析
SQL> create undo tablespace undotbs1 datafile '/opt/app/oracle/RHYS/undotbs1.dbf' size 30M;
Tablespace created.
SQL> alter system set undo_tablespace=undotbs1;
System altered.
SQL> select a.usn,a.status,b.name from v$rollstat a,v$rollname b where a.usn=b.usn;
USN STATUS NAME
---------- --------------- ------------------------------
0 ONLINE SYSTEM
1 ONLINE _SYSSMU1_3143656763$
2 ONLINE _SYSSMU2_2613008803$
3 ONLINE _SYSSMU3_4015766757$
4 ONLINE _SYSSMU4_2233917592$
5 ONLINE _SYSSMU5_1374253473$
6 ONLINE _SYSSMU6_1614295565$
7 ONLINE _SYSSMU7_874818192$
8 ONLINE _SYSSMU8_2318326044$
9 ONLINE _SYSSMU9_1447715703$
10 ONLINE _SYSSMU10_2612552350$
11 rows selected.
SQL>
SQL>
SQL>
SQL>
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_optimizer_undo_cost_change string 11.2.0.1
_undo_autotune boolean FALSE
undo_management string AUTO
undo_retention integer 3600
undo_tablespace string UNDOTBS1
SQL> 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 immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL>
SQL> startup
ORACLE instance started.
Total System Global Area 405020672 bytes
Fixed Size 2213816 bytes
Variable Size 260048968 bytes
Database Buffers 138412032 bytes
Redo Buffers 4345856 bytes
Database mounted.
Database opened.
SQL>
SQL> select * from v$rollname;
USN NAME
---------- ------------------------------
0 SYSTEM
SQL> create rollback segment one tablespace undotbs1;
Rollback segment created.
SQL> select * from v$rollname;
USN NAME
---------- ------------------------------
0 SYSTEM
SQL> alter rollback segment one online;
Rollback segment altered.
SQL> select * from v$rollname;
USN NAME
---------- ------------------------------
0 SYSTEM
21 ONE
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_optimizer_undo_cost_change string 11.2.0.1
_undo_autotune boolean FALSE
undo_management string MANUAL
undo_retention integer 3600
undo_tablespace string UNDOTBS1
SQL>
SQL> set linesize 200
SQL> col value for a60
SQL> col name for a20
SQL> select * from t1;
ID SAL JOB
---------- ---------- ----------
2 3 b
2 1 a
3 D
SQL> update t1 set id=1 where sal=3;
1 row updated.
SQL> select usn,xacts from v$rollstat where xacts>0;
USN XACTS
---------- ----------
21 1
SQL> select xidusn,ubafil,ubablk from v$transaction;
XIDUSN UBAFIL UBABLK
---------- ---------- ----------
21 3 290
SQL> alter system dump datafile 3 block 290;
System altered.
SQL> set linesize 200
SQL> select * from v$diag_info;
INST_ID NAME VALUE
---------- -------------------- ------------------------------------------------------------
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 |