[20191206]確定sys.file$相關資訊.txt

[20191206]確定sys.file$相關資訊.txt

--//我僅僅關注相關資訊如何確定.

1.環境:
SCOTT@book> @ ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SYS@book> select rowid x ,a.* from sys.file$ a;
X                  FILE# STATUS$ BLOCKS TS# RELFILE# MAXEXTEND  INC CRSCNWRP  CRSCNBAS OWNERINSTANCE   SPARE1 SPARE2 SPARE4
------------------ ----- ------- ------ --- -------- --------- ---- -------- --------- ------------- -------- ------ ------
AAAAARAABAAAADpAAA     1       2  64000   0        1   4194302 1280        0         7                4194306
AAAAARAABAAAADpAAB     2       2  51200   1        2   4194302 1280        0      1834                8388610
AAAAARAABAAAADpAAC     3       2   3200   2        3   4194302  640        0    923328               12582914
AAAAARAABAAAADpAAD     4       2    640   4        4   4194302  160        0     16143               16777218
AAAAARAABAAAADpAAE     5       2  12800   6        5   4194302   80        0    952916               20971522
AAAAARAABAAAADpAAF     6       2   5120   7        6   4194302  128        3 391355879               25165826
6 rows selected.
--//以下僅僅分析資料檔案6.我測試環境對應資料檔案是/mnt/ramdisk/book/tea01.dbf.

2.對比資料檔案6看看:
SCOTT@book> alter session set events 'immediate trace name FILE_HDRS level 12';
Session altered.

--//檢視轉儲:
DATA FILE #6:
  name #10: /mnt/ramdisk/book/tea01.dbf
creation size=5120 block size=8192 status=0xe head=10 tail=10 dup=1
 tablespace 7, index=7 krfil=6 prev_file=0
 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
 Checkpoint cnt:224 scn: 0x0003.175e5791 12/05/2019 10:25:28
 Stop scn: 0xffff.ffffffff 12/05/2019 09:33:42
 Creation Checkpointed at scn:  0x0003.17539de7 02/13/2017 15:09:58
 thread:1 rba:(0x1d6.48.10)

--//@?/rdbms/admin/dcore.bsq:
create table file$                                             /* file table */
( file#         number not null,                   /* file identifier number */
  status$       number not null,                      /* status (see KTS.H): */
                                               /* 1 = INVALID, 2 = AVAILABLE */
  blocks        number not null,                   /* size of file in blocks */
                                           /* zero for bitmapped tablespaces */
  ts#           number,                         /* tablespace that owns file */
  relfile#      number,                              /* relative file number */
  maxextend     number,                                 /* maximum file size */
  inc           number,                                  /* increment amount */
  crscnwrp      number,                                 /* creation SCN wrap */
  crscnbas      number,                                 /* creation SCN base */
  ownerinstance varchar("M_IDEN"),                    /* Owner instance name */
  spare1        number,      /* tablespace-relative DBA of space file header */
                                   /* NULL for dictionary-mapped tablespaces */
  spare2        number,
  spare3        varchar2(1000),
  spare4        date
)
/

--//file#=6
--//status$ =2
--//blocks= creation size = 5120??? 可能不對.
--//ts# = tablespace 7, index=7
--//relfile# =  krfil=6
--//maxextend  = ??   --unlimit=4194302=2^22-2 = 4194302
--//inc = ?? maxextend,inc透過轉儲檔案塊2確定.
--//crscnwrp ,crscnbas
SCOTT@book> select CREATION_CHANGE# from v$datafile where file#=6;
CREATION_CHANGE#
----------------
     13276257767

--//13276257767 = scn_wrap,scn_base(10): 3,391355879 = scn_wrap,scn_base(16): 0x3,0x17539de7
--//ownerinstance = NULL
--//spare1 = dba  6,2  
--//第0塊os塊,第1塊是檔案頭,第2塊是KTFB Bitmapped File Space Header.
--//6,2 = file#,block# dba(10): 25165826 = file#,block# dba(16): 0x1800002
SCOTT@book> @ convrdba 6 2
old   2:  TO_CHAR (dbms_utility.make_data_block_address(&1,&2), 'xxxxxxxxxxxxx') rdba16,
new   2:  TO_CHAR (dbms_utility.make_data_block_address(6,2), 'xxxxxxxxxxxxx') rdba16,
old   3: dbms_utility.make_data_block_address(&&1,&&2) rdba
new   3: dbms_utility.make_data_block_address(6,2) rdba
RDBA16                 RDBA
-------------- ------------
       1800002     25165826

SCOTT@book> alter system dump datafile '/mnt/ramdisk/book/tea01.dbf' block 2;
System altered.

--//檢查轉儲:
*** 2019-12-05 11:26:51.506
 V10 STYLE FILE HEADER:
    Compatibility Vsn = 186647552=0xb200400
    Db ID=1337401710=0x4fb7216e, Db Name='BOOK'
    Activation ID=0=0x0
    Control Seq=36291=0x8dc3, File size=5120=0x1400
    File Number=6, Blksiz=8192, File Type=3 DATA
Dump all the blocks in range:
buffer tsn: 7 rdba: 0x01800002 (6/2)
scn: 0x0003.175500fa seq: 0x02 flg: 0x04 tail: 0x00fa1d02
frmt: 0x02 chkval: 0x4059 type: 0x1d=KTFB Bitmapped File Space Header
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00007F15C0F84400 to 0x00007F15C0F86400
7F15C0F84400 0000A21D 01800002 175500FA 04020003  [..........U.....]
7F15C0F84410 00004059 00000006 00000008 00001400  [Y@..............]
7F15C0F84420 00000009 00000080 003FFFFE 0000007E  [..........?.~...]
7F15C0F84430 000013FF 00000000 00000270 175500F9  [........p.....U.]
7F15C0F84440 00000003 00000000 00000000 00000000  [................]
7F15C0F84450 00000080 00000008 00000000 00000000  [................]
7F15C0F84460 00000000 00000000 00000000 00000000  [................]
        Repeat 504 times
7F15C0F863F0 00000000 00000000 00000000 00FA1D02  [................]
File Space Header Block:
Header Control:
RelFno: 6, Unit: 8, Size: 5120, Flag: 9
AutoExtend: YES, Increment: 128, MaxSize: 4194302
Initial Area: 126, Tail: 5119, First: 0, Free: 624
Deallocation scn: 391446777.3
Header Opcode:
Save: No Pending Op
End dump data block from file /mnt/ramdisk/book/tea01.dbf minblk 2 maxblk 2

--//inc= Increment: 128
--//blocks=File size=5120=0x1400
--//maxextend= MaxSize: 4194302
--//這樣相關資訊可以全部確定.
--//基本透過轉儲控制檔案,以及檔案塊2可以確定.

--//最後補充說明一下:
--//blocks 記錄的並不是size of file in blocks.而是creation size的大小,可以很容易驗證.

SCOTT@book> alter database datafile '/mnt/ramdisk/book/tea01.dbf' resize 38m;
Database altered.

SYS@book> select rowid x ,a.* from sys.file$ a where file#=6;
X                  FILE# STATUS$ BLOCKS TS# RELFILE# MAXEXTEND  INC CRSCNWRP  CRSCNBAS OWNERINSTANCE   SPARE1 SPARE2 SPARE4
------------------ ----- ------- ------ --- -------- --------- ---- -------- --------- ------------- -------- ------ ------
AAAAARAABAAAADpAAF     6       2   5120   7        6   4194302  128        3 391355879               25165826
6 rows selected.

SCOTT@book> alter database datafile '/mnt/ramdisk/book/tea01.dbf' resize 41m;
Database altered.

SYS@book> select rowid x ,a.* from sys.file$ a where file#=6;
X                  FILE# STATUS$ BLOCKS TS# RELFILE# MAXEXTEND  INC CRSCNWRP  CRSCNBAS OWNERINSTANCE   SPARE1 SPARE2 SPARE4
------------------ ----- ------- ------ --- -------- --------- ---- -------- --------- ------------- -------- ------ ------
AAAAARAABAAAADpAAF     6       2   5120   7        6   4194302  128        3 391355879               25165826
6 rows selected.