如何得到视图具体存储的block号

这个是Itpub上一个网友提出的问题，原贴地址http://www.itpub.net/719442.html

实际上，view不过是一段存储在数据库中的select语句，所以没有所谓的view的存储结构的。

view本身的定义语句保存在一个基表中:view$，所以，该网友实际上是想知道如何查找view的定义保存在哪个block中，这个问题就比较简单了。

SYS@ning>create view v_test as select * from ning.test;View created.SYS@ning>select object_id from all_objects where object_name='V_TEST';
OBJECT_ID----------10540

SYS@ning>select dbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid) from view$ where obj#=10540;DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------ ------------------------------------1 14184

SYS@ning>alter system dump datafile 1 block 14184;
SYS@ning>select dbms_rowid.rowid_row_number(rowid) from view$ where obj#=10540;DBMS_ROWID.ROWID_ROW_NUMBER(ROWID)
----------------------------------7

tab 0, row 7 @0x126a
tl: 248 fb: --H-FL-- lb: 0x2 cc: 8
col 0: [ 4] c3 02 06 29
col 1: [38]2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d
2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d
col 2: [ 2] c1 0e
col 3: [ 2] c1 0e
col 4: [ 1] 80
col 5: [ 1] 80
col 6: [ 3] c2 02 57
col 7: [186]73 65 6c 65 63 74 20 22 4f 57 4e 45 52 22 2c 22 4f 42 4a 45 43 54 5f 4e 41
4d 45 22 2c 22 53 55 42 4f 42 4a 45 43 54 5f 4e 41 4d 45 22 2c 22 4f 42 4a
45 43 54 5f 49 44 22 2c 22 44 41 54 41 5f 4f 42 4a 45 43 54 5f 49 44 22 2c
22 4f 42 4a 45 43 54 5f 54 59 50 45 22 2c 22 43 52 45 41 54 45 44 22 2c 22
4c 41 53 54 5f 44 44 4c 5f 54 49 4d 45 22 2c 22 54 49 4d 45 53 54 41 4d 50
22 2c 22 53 54 41 54 55 53 22 2c 22 54 45 4d 50 4f 52 41 52 59 22 2c 22 47
45 4e 45 52 41 54 45 44 22 2c 22 53 45 43 4f 4e 44 41 52 59 22 20 66 72 6f
6d 20 6e 69 6e 67 2e 74 65 73 74

对照view$的定义
SYS@ning>desc view$Name Null? Type---------------------------------------- -------- ----------------------------OBJ# NOT NULL NUMBERAUDIT$ NOT NULL VARCHAR2(38)COLS NOT NULL NUMBER
INTCOLS NOT NULL NUMBER
PROPERTY NOT NULL NUMBER
FLAGS NOT NULL NUMBER
TEXTLENGTH NUMBER
TEXT LONG

其中col0就是obj#SYS@ning>select dump(10540,16) from dual;DUMP(10540,16)
--------------------Typ=2 Len=4: c3,2,6,29

col7就是view的text
SYS@ning>select text from view$ where obj#=10540;TEXT-------------------------------------------------------------------------------- select "OWNER","OBJECT_NAME","SUBOBJECT_NAME","OBJECT_ID","DATA_OBJECT_ID","OBJE

SYS@ning>select dump('select "OWNER","OBJECT_NAME","SUBOBJECT_NAME","OBJECT_ID",
"DATA_OBJECT_ID","OBJE',16) from dual;

DUMP('SELECT"OWNER","OBJECT_NAME","SUBOBJECT_NAME","OBJECT_ID","DATA_OBJECT_ID",
--------------------------------------------------------------------------------Typ=96 Len=80: 73,65,6c,65,63,74,20,22,4f,57,4e,45,52,22,2c,22,4f,42,4a,45,43,54,5f,4e,41,4d,45,22,2c,22,53,55,42,4f,42,4a,45,43,54,5f,4e,41,4d,45,22,2c,22,4f,4
2,4a,45,43,54,5f,49,44,22,2c,22,44,41,54,41,5f,4f,42,4a,45,43,54,5f,49,44,22,2c,22,4f,42,4a,45

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/193161/viewspace-50279/，如需转载，请注明出处，否则将追究法律责任。