[20240823]查询namespace的方法.txt

[20240823]查询namespace的方法.txt --//我一般查询使用如下加过滤条件: --//select distinct kglhdnsp,kglhdnsd,kglobtyd from x$kglob   order by 1; --//这样查询效率低下,甚至可能无法查询. --//实际上查询 x$kglst 底层X表,就可以知道对应的细节. $ cat nmsp.sql -- Copyright 2023 lfree. All rights reserved. -- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms and conditions. -------------------------------------------------------------------------------- -- -- File name:   nmsp.sql -- Purpose:     query namespace from x$kglst -- -- Author:      lfree -- -- Usage: --     @ nmsp <namespace> <namespace_num> -- for example --     @ nmsp table -1 --     @ nmsp instance -1 --     @ nmsp '' 74 --     @ nmsp '' 0x4a|x4a -- -------------------------------------------------------------------------------- prompt @ nmsp table -1 prompt @ nmsp '' 74  or @ nmsp '' 0x4a|x4a set term off head off column calc_dec  new_value _calc_dec column namespace new_value _namespace; SELECT to_number(substr('&2', instr(upper('&2'),'X')+1), p1.format) calc_dec ,decode('&1',NULL,NULL,'%&1%') namespace FROM ( SELECT CASE WHEN upper('&2') LIKE '%X%' THEN 'XXXXXX' ELSE '999999' END format FROM dual) p1; set term on head on column kglstidn_hex format a12 SELECT kglstdsc,kglstidn,to_char(kglstidn,'FMxxxx') kglstidn_hex   FROM x$kglst  WHERE KGLSTTYP = 'NAMESPACE' AND (kglstdsc LIKE upper('&_namespace') OR kglstidn = &_calc_dec)  ORDER BY 2; --//测试例子: SYS@book> @ nmsp 'dblink' -1 @ nmsp table -1 @ nmsp '' 74  or @ @ nmsp '' 0x4a|x4a KGLSTDSC                                                           KGLSTIDN KGLSTIDN_HEX ---------------------------------------------------------------- ---------- ------------ DBLINK                                                                   69 45 SYS@book> @ nmsp '' x45 @ nmsp table -1 @ nmsp '' 74  or @ nmsp '' 0x4a|x4a KGLSTDSC                                                           KGLSTIDN KGLSTIDN_HEX ---------------------------------------------------------------- ---------- ------------ DBLINK                                                                   69 45 SYS@book> @ nmsp '' 69 @ nmsp table -1 @ nmsp '' 74  or @ nmsp '' 0x4a|x4a KGLSTDSC                                                           KGLSTIDN KGLSTIDN_HEX ---------------------------------------------------------------- ---------- ------------ DBLINK                                                                   69 45