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