This time I will introduce some about tablespace UNDO:
There are three types tablespace :
Permanent
temporary
undo
Step1:
If you want to see the tablespace type, you can use below command:
SQL> select tablespace_name,contents from dba_tablespaces;
TABLESPACE_NAME CONTENTS
------------------------------ ---------
SYSTEM PERMANENT
UNDOTBS1 UNDO
SYSAUX PERMANENT
TEMP TEMPORARY
USERS PERMANENT
Step2:
If you want to create the tablespace, please see below database dictionary first:
SQL> select file_name,tablespace_name from dba_data_files;
FILE_NAME TABLESPA
---------------------------------------------------------- --------
D:\ORACLE\PRODUCT\10.2.0\ORADATA\SHENG\USERS01.DBF USERS
D:\ORACLE\PRODUCT\10.2.0\ORADATA\SHENG\SYSAUX01.DBF SYSAUX
D:\ORACLE\PRODUCT\10.2.0\ORADATA\SHENG\UNDOTBS01.DBF UNDOTBS1
D:\ORACLE\PRODUCT\10.2.0\ORADATA\SHENG\SYSTEM01.DBF SYSTEM
Step3:
create the undo tablespace:
CREATE UNDO TABLESPACE UNDOTBS1 DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\SHENG\UNDOTBS01.DBF'
SIZE 10M AUTOEXTEND ON NEXT 5M;
WE CAN not put the data on undo tablespace:
example:
create table t1(ID int) tablespace undotbs1
error:
ora-30022: can not create segement on undo tablespace
Step4:
Let us look at the segemnt about database:
Oracle have its special key : Undo space, it can put unupdated data in undo space.
other database dose not have undo function.
----------
We will do a test to check the relationship between tablespace and data:
SQL> create tablespace test datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\SHENG\test.dbf' size 5m;
表空间已创建。
SQL> create table t1 (ID int) tablespace test;
表已创建。
SQL> select * from t1;
ID
----------
1981
2012
SQL> update t1 set id=1983 where id=1981;
已更新 1 行。
SQL> select * from t1;
ID
----------
1983
2012
Now I will open another session to check the t1:
SQL> select * from t1;
ID
----------
1981
2012
We found that the data had not updated, because the second data check from the undo tablespace:
------------------------------
Next step we will see the data in which datafile:
Step1:
SQL> select dbms_rowid.rowid_relative_fno(rowid) fon, dbms_rowid.rowid_block_number(rowid) bno from t1;
FON BNO
---------- ----------
5 13
5 13
Step2:
next step we will dump the datafile number: 5, block NO: 13
The text file location: $ORACLE_BASE/admin/udump
SQL> alter system dump datafile 5 block 13;
系统已更改。
let us look at below user process trace file details:
------
Dump file d:\oracle\product\10.2.0\admin\sheng\udump\sheng_ora_1140.trc
Sun Feb 12 15:15:47 2012
ORACLE V10.2.0.1.0 - Production vsnsta=0
vsnsql=14 vsnxtr=3
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Windows XP Version V5.1 Service Pack 2
CPU : 2 - type 586
Process Affinity : 0x00000000
Memory (Avail/Total): Ph:1093M/1983M, Ph+PgF:2085M/3270M, VA:1272M/2047M
Instance name: sheng
Redo thread mounted by this instance: 1
Oracle process number: 16
Windows thread id: 1140, image: ORACLE.EXE (SHAD)
*** 2012-02-12 15:15:47.921
*** SERVICE NAME:(SYS$USERS) 2012-02-12 15:15:47.906
*** SESSION ID:(154.124) 2012-02-12 15:15:47.906
Start dump data blocks tsn: 6 file#: 5 minblk 13 maxblk 13
buffer tsn: 6 rdba: 0x0140000d (5/13)
scn: 0x0000.000d266d seq: 0x01 flg: 0x00 tail: 0x266d0601
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x04D02200 to 0x04D04200
4D02200 0000A206 0140000D 000D266D 00010000 [......@.m&......]
4D02210 00000000 00000001 0000C900 000D266D [............m&..]
4D02220 00000000 00320002 01400009 002A0004 [......2...@...*.]
4D02230 0000018F 00800A5F 0032011C 00000001 [...._.....2.....]
4D02240 00000000 00180007 0000017E 00800473 [........~...s...]
4D02250 001C0149 00008000 000D1F8F 00000000 [I...............]
4D02260 00000000 00020100 0016FFFF 1F701F8A [..............p.]
4D02270 00001F70 1F910002 00001F8A 00000000 [p...............]
4D02280 00000000 00000000 00000000 00000000 [................]
Repeat 501 times
4D041E0 00000000 00000000 00000000 002C0000 [..............,.]
4D041F0 15C20301 01012C0D 5414C203 266D0601 [.....,.....T..m&]
Block header dump: 0x0140000d
Object id on Block? Y
seg/obj: 0xc900 csc: 0x00.d266d itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x1400009 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0004.02a.0000018f 0x00800a5f.011c.32 ---- 1 fsc 0x0000.00000000
0x02 0x0007.018.0000017e 0x00800473.0149.1c C--- 0 scn 0x0000.000d1f8f
data_block_dump,data header at 0x4d02264
===============
tsiz: 0x1f98
hsiz: 0x16
pbl: 0x04d02264
bdba: 0x0140000d
76543210
flag=--------
ntab=1
nrow=2
frre=-1
fsbo=0x16
fseo=0x1f8a
avsp=0x1f70
tosp=0x1f70
0xe:pti[0] nrow=2 offs=0
0x12:pri[0] offs=0x1f91
0x14:pri[1] offs=0x1f8a
block_row_dump:
tab 0, row 0, @0x1f91
tl: 7 fb: --H-FL-- lb: 0x1 cc: 1
col 0: [ 3] c2 14 54
tab 0, row 1, @0x1f8a
tl: 7 fb: --H-FL-- lb: 0x0 cc: 1
col 0: [ 3] c2 15 0d
end_of_block_dump
End dump data blocks tsn: 6 file#: 5 minblk 13 maxblk 13
---
Let us clarify:
lb: lock block
"tl: 7 fb: --H-FL-- lb: 0x1 cc: 1"
"0x1" : not "0x0" means : this row has been locked.
uba: undo block address
(the database suggest user to check the file from uba)
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0004.02a.0000018f 0x00800a5f.011c.32 ---- 1 fsc 0x0000.00000000
0x02 0x0007.018.0000017e 0x00800473.0149.1c C--- 0 scn 0x0000.000d1f8f
If you want to check the data, you can go "0x00800a5f" to see the first data:
(Flag Lck is :"---- 1" : this data is locked)
---------
You know the datafile contains : file NO and block NO
Let us to do:
Oracle packet: 10 decimal
block address is 16 hex:
let us do a transfer from hex to decimal :
SQL> select to_number('00800a5f','xxxxxxxx') from dual;
TO_NUMBER('00800A5F','XXXXXXXX')
--------------------------------
8391263
SQL> select dbms_utility.data_block_address_file(8391263) from dual;
DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(8391263)
---------------------------------------------
2
SQL> select dbms_utility.data_block_address_block(8391263) from dual;
DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(8391263)
----------------------------------------------
2655
----
if you want to see the data, you can go: datafile: 2 ,block: 2655 check:
SQL> alter system dump datafile 2 block 2655;
系统已更改。
ok,let check the dump file again:
let us check the datafile: 2 :
ok, we find the datafile is undotable:
SQL> select file_name,file_id from dba_data_files;
FILE_NAME FILE_ID
---------------------------------------------------------- ----------
D:\ORACLE\PRODUCT\10.2.0\ORADATA\SHENG\USERS01.DBF 4
D:\ORACLE\PRODUCT\10.2.0\ORADATA\SHENG\SYSAUX01.DBF 3
D:\ORACLE\PRODUCT\10.2.0\ORADATA\SHENG\UNDOTBS01.DBF 2
D:\ORACLE\PRODUCT\10.2.0\ORADATA\SHENG\SYSTEM01.DBF 1
D:\ORACLE\PRODUCT\10.2.0\ORADATA\SHENG\TEST.DBF 5
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
------
let us check how many undo segment:
select * from dba_rollback_segs
------
Next we will face one case:
if you have not commit one session, at this time, you have to change one undotablespace to another,
how about the undo tablespace do:
we will invole another database dictionary: v$rollstat
SQL> select xacts,status,usn from v$rollstat;
XACTS STATUS USN
---------- --------------- ----------
0 ONLINE 0
0 ONLINE 1
0 ONLINE 2
0 ONLINE 3
1 ONLINE 4
0 ONLINE 5
0 ONLINE 6
0 ONLINE 7
0 ONLINE 8
0 ONLINE 9
0 ONLINE 10
已选择11行。
Xacts: means: active session, No 4 session,
we will check another database dictionary: V$transation
Oracle involed on parameter to control the session number in one roll block:
SQL> show parameter roll
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
fast_start_parallel_rollback string LOW
rollback_segments string
transactions_per_rollback_segment integer 5
---
Check oracle is there is undo session waiting : v$waitstat
---
If there is more than 10 (USN : 0 is used by sys by private) session:
smon process will create added undo segment to process the session:
select * from v$rollstat, you will see more than 10 counts.
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ----------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
-------------------
Step5:
The following , I will give detailed about undo_retention parameter:
if the undo size is low, ora-01555 will occur.
because the undo datafile had be overwrite.
Reason:
1: The undo tablespace autoextend : off
2: The undo_retention time is short (the default is 900 seconds)
3: The undo size is low.
4: The sql search time is too long.
扫一扫
562
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
