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

[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