[20240913]建立完善xind.sql脚本.txt

 $ 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_string1 from dual ;
select decode('&1','','1=1','1','1=1','1=1','1=1',''''||replace(upper('&1'),',',''',''')||'''') column_string from dual ;
set termout on

--//select * from V$INDEXED_FIXED_COLUMN where 1=1 and table_name in (&column_string);
set feedback off
column derived_tables format a30
SELECT NULL derived_tables, V$INDEXED_FIXED_COLUMN.*
  FROM V$INDEXED_FIXED_COLUMN
 WHERE 1 = 1 AND  table_name in (&column_string)
-- WHERE 1 = 1 AND  regexp_like (table_name ,upper('&1'))
;

SELECT x$kqfdt.KQFDTNAM derived_tables, V$INDEXED_FIXED_COLUMN.*
  FROM V$INDEXED_FIXED_COLUMN, x$kqfdt
 WHERE  1 = 1
       AND V$INDEXED_FIXED_COLUMN.table_name = x$kqfdt.KQFDTEQU
       AND x$kqfdt.kqfdtnam in (&column_string)
--     AND regexp_like ( x$kqfdt.kqfdtnam ,upper('&1'))
order by x$kqfdt.KQFDTNAM ,INDEX_NUMBER
;
prompt
set feedback 6

SYS@book> @ xind x$kgldp,x$kglob,x$kglcursor,X$KCCFNCDB

DERIVED_TABLES                 TABLE_NAME                     INDEX_NUMBER COLUMN_NAME                    COLUMN_POSITION     CON_ID
------------------------------ ------------------------------ ------------ ------------------------------ --------------- ----------
                               X$KGLOB                                   1 KGLNAHSH                                     0          0
                               X$KGLOB                                   2 KGLOBT03                                     0          0
                               X$KGLDP                                   1 KGLNAHSH                                     0          0

DERIVED_TABLES                 TABLE_NAME                     INDEX_NUMBER COLUMN_NAME                    COLUMN_POSITION     CON_ID
------------------------------ ------------------------------ ------------ ------------------------------ --------------- ----------
X$KCCFNCDB                     X$KCCFN                                   1 FNNUM                                        0          0
X$KGLCURSOR                    X$KGLOB                                   1 KGLNAHSH                                     0          0
X$KGLCURSOR                    X$KGLOB                                   2 KGLOBT03                                     0          0