ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 如何得到视图具体存储的block号

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

原创 Linux操作系统 作者:NinGoo 时间:2019-06-16 21:33:04 0 删除 编辑

这个是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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2004-12-07

  • 博文量
    200
  • 访问量
    124514