[20240911]查看超长视图的定义2.txt

[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 <= 10 order by 1; ADDR               ADDR_DEC LAG_ADDR_DEC ROW_SIZE_HEX        ROW_SIZE       INDX ---------------- ---------- ------------ ----------------- ---------- ---------- 0000000016BECC00  381602816                                                    0 0000000016BECC20  381602848    381602816                20         32          1 0000000016BECC40  381602880    381602848                20         32          2 0000000016BECC60  381602912    381602880                20         32          3 0000000016BECC80  381602944    381602912                20         32          4 0000000016BECCA0  381602976    381602944                20         32          5 0000000016BECCC0  381603008    381602976                20         32          6 0000000016BECCE0  381603040    381603008                20         32          7 0000000016BECD00  381603072    381603040                20         32          8 0000000016BECD20  381603104    381603072                20         32          9 0000000016BECD40  381603136    381603104                20         32         10 11 rows selected. --//我的测试版本也许与原作者不同,查看x$kqfvt行的长度，您会发现行长度为32(对方的测试结果40). SELECT ta.kqftanam, co.kqfconam, co.kqfcodty, co.kqfcotyp, co.kqfcomax,        co.kqfcolsz, co.kqfcolof, co.kqfcosiz, co.kqfcooff, co.kqfcoidx,        co.kqfcoipo   FROM x$kqfta ta, x$kqfco co  WHERE co.kqfcotab = ta.indx    AND kqftanam LIKE UPPER('%kqfvt%')  ORDER BY ta.kqftanam, co.kqfcooff, co.indx; KQFTANAM   KQFCONAM     KQFCODTY   KQFCOTYP   KQFCOMAX   KQFCOLSZ   KQFCOLOF   KQFCOSIZ   KQFCOOFF   KQFCOIDX   KQFCOIPO ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- X$KQFVT    ADDR               23          9          0          0          0          8          0          1          0 X$KQFVT    INDX                2         11          0          0          0          4          0          2          0 X$KQFVT    INST_ID             2         11          0          0          0          4          0          0          0 X$KQFVT    CON_ID              2         11          0          0          0          2          0          0          0 X$KQFVT    KQFTPSEL            1          6          0          0          0       4000          0          0          0 --//X$KQFVT包含5个字段,可以发现一个细节,实际上占用8+4+4+2+4000 = 4018.而实际上行长仅仅32个字节. --//也就是不能以普通表方式思考看待X$ fixed table. --//是否可以这样理解INST_ID,CON_ID 可以通过函数取出,KQFTPSEL通过addr指向的地址取出. SYS@book> @ 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 << EOF # set head off verify off feedback off # select t.addr from x\$kqfvi i,x\$kqfvt t where i.kqfvinam = '$tn' and t.indx = i.indx; # quit # EOF t_addr=$(sqlplus -s -l / as sysdba << EOF set head off verify off feedback off select t.addr addr from x\$kqfvi i,x\$kqfvt t where i.kqfvinam = '$tn' and t.indx = i.indx; quit EOF ) t_addr=$(echo $t_addr|tr -d '\n ') #echo x/s *0x${t_addr} echo -e "set pagination off\nset print elements 0\n x/s *0x${t_addr}" | gdb -q -p $(pgrep ora_dbw0_${ORACLE_SID}) | grep '^(gdb)' --//注:x/s 前面有1个*,表示指向地址的指针.没有set print elements 0仅仅取出前面1段文本. $ . ext_sql.sh 'GV$ACTIVE_SESSION_HISTORy' (gdb) (gdb) (gdb) 0x16e985c0:   "SELECT  /*+ qb_name(gv_ashv) no_merge ordered use_nl(s,a) */ a.inst_id, s.sample_id, s.sample_time,s.sample_time_utc, a.usecs_per_row, s.is_awr_sample, a.session_id, a.session_serial#, decode(a.session_type, 1, 'FOREGROUND', 'BACKGROUND'), a.flags, a.user_id, a.sql_id, decode(bitand(a.flags, power(2, 4)), NULL, 'N', 0, 'N', 'Y'), a.sql_child_number, a.sql_opcode, a.force_matching_signature, decode(a.top_level_sql_id, NULL, a.sql_id, a.top_level_sql_id), decode(a.top_level_sql_id, NULL, a.sql_opcode,        a.top_level_sql_opcode), a.sql_opname,a.sql_adaptive_plan_resolved,a.sql_full_plan_hash_value,a.sql_plan_hash_value, decode(a.sql_plan_operation, NULL, to_number(NULL),       a.sql_plan_line_id), a.sql_plan_operation, a.sql_plan_options, decode(a.sql_exec_id, 0, to_number(NULL), a.sql_exec_id), a.sql_exec_start, decode(a.plsql_entry_object_id,0,to_number(NULL),       a.plsql_entry_object_id), decode(a.plsql_entry_object_id,0,to_number(NULL),       a.plsql_entry_subprogram_id), decode(a.plsql_object_id,0,to_number(NULL),a.plsql_object_id), decode(a.plsql_object_id,0,to_number(NULL),a.plsql_subprogram_id), decode(a.qc_session_id, 0, to_number(NULL), a.qc_instance_id), decode(a.qc_session_id, 0, to_number(NULL), a.qc_session_id), decode(a.qc_session_id, 0, to_number(NULL), a.qc_session_serial#), decode(a.px_flags, 0, to_number(NULL), a.px_flags), decode(a.wait_time, 0, a.event,    NULL), decode(a.wait_time, 0, a.event_id, NULL), decode(a.wait_time, 0, a.event#,   NULL), a.seq#, a.p1text, a.p1, a.p2text, a.p2, a.p3text, a.p3, decode(a.wait_time, 0, a.wait_class,    NULL), decode(a.wait_time, 0, a.wait_class_id, NULL), a.wait_time, decode(a.wait_time, 0, 'WAITING', 'ON CPU'), a.time_waited, (case when a.blocking_session = 4294967295         then 'UNKNOWN'       when a.blocking_session = 4294967294         then 'GLOBAL'       when a.blocking_session = 4294967293         then 'UNKNOWN'       when a.blocking_session = 4294967292         then 'NO HOLDER'       when a.blocking_session = 4294967291         then 'NOT IN WAIT'       else 'VALID'  end), (case when a.blocking_session between 4294967291 and 4294967295         then to_number(NULL)       else a.blocking_session  end), (case when a.blocking_session between 4294967291 and 4294967295         then to_number(NULL)       else a.blocking_session_serial#  end), (case when a.blocking_session between 4294967291 and 4294967295         then to_number(NULL)       else a.blocking_inst_id  end), (case when a.blocking_session between 4294967291 and 4294967295         then NULL      else decode(bitand(a.flags, power(2, 3)),NULL, 'N', 0,'N','Y')  end), a.current_obj#, a.current_file#, a.current_block#, a.current_row#,a.top_level_call#, a.top_level_call_name, decode(a.consumer_group_id, 0, to_number(NULL), a.consumer_group_id), a.xid, decode(a.remote_instance#, 0, to_number(NULL), a.remote_instance#), a.time_model, a.in_connection_mgmt, a.in_parse, a.in_hard_parse, a.in_sql_execution, a.in_plsql_execution, a.in_plsql_rpc, a.in_plsql_compilation, a.in_java_execution, a.in_bind, a.in_cursor_close, a.in_sequence_load, a.in_inmemory_query, a.in_inmemory_populate, a.in_inmemory_prepopulate, a.in_inmemory_repopulate, a.in_inmemory_trepopulate, a.in_tablespace_encryption, decode(bitand(a.flags, power(2, 5)), NULL, 'N', 0, 'N', 'Y'), decode(bitand(a.flags, power(2, 6)), NULL, 'N', 0, 'N', 'Y'), decode(bitand(a.flags, power(2, 0)), NULL, 'N', 0, 'N', 'Y'), decode(bitand(a.flags, power(2, 2)), NULL, 'N', 0, 'N', 'Y'), decode(bitand(a.flags, power(2, 8)), NULL, 'N', 0, 'N', 'Y'), a.service_hash, a.program, a.module, a.action, a.client_id, a.machine, a.port, a.ecid, a.dbreplay_file_id, a.dbreplay_call_counter, decode(a.tm_delta_time, 0, to_number(null),        a.tm_delta_time), decode(a.tm_delta_time, 0, to_number(null),        a.tm_delta_cpu_time), decode(a.tm_delta_time, 0, to_number(null),        a.tm_delta_db_time), decode(a.delta_time, 0, to_number(null),         a.delta_time), decode(a.delta_time, 0, to_number(null),         a.delta_read_io_requests), decode(a.delta_time, 0, to_number(null),         a.delta_write_io_requests), decode(a.delta_time, 0, to_number(null),         a.delta_read_io_bytes), decode(a.delta_time, 0, to_number(null),         a.delta_write_io_bytes), decode(a.delta_time, 0, to_number(null),         a.delta_interconnect_io_bytes), decode(a.delta_time, 0, to_number(null),         a.delta_read_mem_bytes), decode(a.pga_allocated, 0, to_number(null), a.pga_allocated), decode(a.pga_allocated, 0, to_number(null),         a.temp_space_allocated), a.con_dbid, a.con_id, a.dbop_name, a.dbop_exec_id FROM  x$kewash s, x$ash a WHERE s.sample_addr = a.sample_addr and       s.sample_id   = a.sample_id   and       s.sample_time = a.sample_time and       nlssort(s.need_awr_sample,'nls_sort = binary') =       nlssort(a.need_awr_sample,'nls_sort = binary')" (gdb) quit $ . ext_sql.sh 'v$waitstat' (gdb) (gdb) (gdb) 0x16e31ec0:   "select class,count,time, con_id from gv$waitstat where inst_id = USERENV('Instance') " (gdb) quit $ . ext_sql.sh 'v$waitstat'| sed 's/^(gdb).*:\t"//;s/"$//;/quit$/d' select class,count,time, con_id from gv$waitstat where inst_id = USERENV('Instance') 4.附上xind.sql脚本: $ cat xind.sql -- Copyright 2023 lfree. All rights reserved. -- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms and conditions. -------------------------------------------------------------------------------- -- -- File name:   xind.sql -- Purpose:     query X$ index -- Author:      lfree -- -- Usage: --     @ xind <x$table_name,...> --     @ xind <x$kglob,x$kgldp> -------------------------------------------------------------------------------- set termout off column column_string new_value column_string format a200 select decode('&1','','1=1','1','1=1','1=1','1=1','table_name in ('||''''||replace(upper('&1'),',',''',''')||''')' ) column_string from dual ; set termout on select * from V$INDEXED_FIXED_COLUMN where 1=1 and (&column_string);