[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