马上注册，结交更多好友，享用更多功能，让你轻松玩转社区。
您需要 登录 才可以下载或查看，没有账号？立即注册
x
[20240911]查看超长视图的定义2.txt
--//昨天看了链接:https://www.anbob.com/archives/8295.html,提供了另外的方式获得超长定义试图的长文本。
--//我重复验证看看.
1.情况:
SYS@book> @ver2
==============================
PORT_STRING : x86_64/Linux 2.4.xx
VERSION : 21.0.0.0.0
BANNER : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
BANNER_FULL : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
BANNER_LEGACY : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
CON_ID : 0
PL/SQL procedure successfully completed.
2.测试:
select
addr,
to_number(addr,'XXXXXXXXXXXXXXXX') addr_dec,
to_number(lag(addr,1) over (order by indx),'xxxxxxxxxxxxxxxx') lag_addr_dec,
to_char(
to_number(addr,'XXXXXXXXXXXXXXXX') -
to_number(lag(addr,1) over (order by indx),'xxxxxxxxxxxxxxxx'),
'xxxxxxxxxxxxxxxx'
) row_size_hex,
to_number(addr,'XXXXXXXXXXXXXXXX') -
to_number(lag(addr,1) over (order by indx),'xxxxxxxxxxxxxxxx') row_size,
indx
from x$kqfvt
where indx @ xind X$KQFVT
TABLE_NAME INDEX_NUMBER COLUMN_NAME COLUMN_POSITION CON_ID
------------------------------ ------------ ------------------------------ --------------- ----------
X$KQFVT 1 ADDR 0 0
X$KQFVT 2 INDX 0 0
--//仅仅存在2个索引addr,indr.
SYS@book> select i.addr,t.addr from x$kqfvi i,x$kqfvt t where i.kqfvinam = 'GV$ACTIVE_SESSION_HISTORY' and t.indx = i.indx;
ADDR ADDR
---------------- ----------------
0000000016BDE4C0 0000000016BF3880
--//sql语句的信息生存在地址0000000016BF3880指向的地址.
SYS@book> oradebug setmypid
Statement processed.
SYS@book> oradebug peek 0x0000000016BF3880 32
[016BF3880, 016BF38A0) = 16E985C0 00000000 16C30640 00000000 00000000 00000000 00000000 00000000
--//sql语句生存在地址0000000016E985C0开始处.受KQFTPSEL varchar2(4000)限制仅仅取出4000字节.
SYS@book> @ fchaz 0000000016BF3880
no rows selected
SYS@book> @ fchaz 16E985C0
no rows selected
--//这些地址不再sga uga等地方,到底在那里呢?
SYS@book> @ spid
SID SERIAL# PROCESS SERVER SPID PID P_SERIAL# C50
---------- ---------- ------------------------ --------- ------------------------------ ------- ---------- --------------------------------------------------
288 19200 3795 DEDICATED 3796 62 4 alter system kill session '288,19200' immediate;
$ pmap -x 3796 | head -10
3796: oraclebook (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
Address Kbytes RSS Dirty Mode Mapping
0000000000400000 422472 37660 0 r-x-- oracle
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
000000001a292000 1540 396 4 r---- oracle
000000001a413000 468 28 24 rw--- oracle
000000001a488000 344 196 196 rw--- [ anon ]
000000001ad7e000 316 284 284 rw--- [ anon ]
0000000060000000 10240 0 0 rw-s- SYSV00000000 (deleted)
0000000060c00000 770048 0 0 rw-s- SYSV00000000 (deleted)
000000008fc00000 8192 0 0 rw-s- SYSV00000000 (deleted)
SYS@book> @ calcx 422472*1024
DEC HEX
----------------------------------- --------------------
432611328.000000 0000000019c92000
SYS@book> @ calc 0x400000 + x0000000019c92000
DEC HEX
----------------------------------- --------------------
436805632.000000 1A092000
--//0x400000 = 4194304
--//4194304+422472*1024 = 436805632
--//4194304+422472*1024 = 0x1a092000
$ echo p/x 0x400000+422472*1024 | gdb -q
(gdb) $1 = 0x1a092000
(gdb) quit
--//很显着在实行文件oracle里面,也就是通过strings抽取文本,可以找到对应的sql文本.
--//我以前通过抽取libserver.a,效果一样的.
$ strings /u01/app/oracle/product/21.0.0/dbhome_1/lib/libserver.a | grep "x\$kewash.*x\$ash" > cc1.txt
$ strings $(which oracle) |grep "x\$kewash.*x\$ash" > cc2.txt
$ diff cc1.txt cc2.txt
--//结果不再贴出.
3.实验通过脚本是否可以实现,测试看看:
$ cat ext_sql.sh
#! /bin/bash
tn="${1^^}"
#echo $tn
# sqlplus -s -l / as sysdba |
