Skip to main content
ExLibris
  • Subscribe by RSS
    • Back
    Aleph
    Ex Libris Knowledge Center

    How to tell if Oracle indexes for a particular library are present & VALID

    1. Last updated
    2. Save as PDF
    • Article Type: General
    • Product: Aleph
    • Product Version: 20, 21, 22, 23
    • Relevant for Installation Type: Dedicated-Direct; Direct; Local;

    Description:
    Aside from doing util a/17/14 for each Oracle table, how can one tell if the Oracle tables and indexes for a particular library are present and VALID?

    Resolution:
    The following SQL commands can be used. They presume that you have valid USMnn and USR00 tables in this Oracle instance.

    Note: If parallel indexing has been used to generate a particular Aleph index table in a different library and there's an "LS" to that other library for this table, the following SQL will show the table as missing. util a/17/14 needs to be run in the other library to check the table(s) Oracle indexes.

    The following SQL can be used to confirm that the USMnn and USR00 tables are present at your site:
    select table_name from all_tables where owner = 'USM01';
    select table_name from all_tables where owner = 'USM50';
    etc.

    If the USMnn/USR00 tables are not present, please consult article     "How to get USMnn Oracle tables / Demo libraries"

    ( https://knowledge.exlibrisgroup.com/...Demo_libraries ).

    NOTE:  The local three-character library code needs to be substituted for "XXX" in the SQL statements below. ***It must be in uppercase.***

    1. Check your xxx01, xxx10, xxx30, and xxx60 Oracle indexes:
    select index_name from all_indexes where owner = 'USM01' and index_name like 'Z%' minus select index_name from all_indexes where owner = 'XXX01' and index_name like 'Z%' and status = 'VALID';

    select index_name from all_indexes where owner = 'USM10' and index_name like 'Z%' minus select index_name from all_indexes where owner = 'XXX10' and index_name like 'Z%' and status = 'VALID';

    select index_name from all_indexes where owner = 'USM30' and index_name like 'Z%' minus select index_name from all_indexes where owner = 'XXX30' and index_name like 'Z%' and status = 'VALID';

    select index_name from all_indexes where owner = 'USM40' and index_name like 'Z%' minus select index_name from all_indexes where owner = 'XXX40' and index_name like 'Z%' and status = 'VALID';

    select index_name from all_indexes where owner = 'USM60' and index_name like 'Z%' minus select index_name from all_indexes where owner = 'XXX60' and index_name like 'Z%' and status = 'VALID';


    2a. Check your xxx50 indexes where xxx50 is an ADM library only:
    select index_name from all_indexes where owner = 'USM50' and index_name like 'Z%' minus select index_name from all_indexes where owner = 'XXX50' and index_name like 'Z%' and status = 'VALID';


    2b. Check your xxx50 indexes where xxx50 is the ADM library, the usr_library*, the pw_library**, and the z105_library***:

    select index_name from all_indexes where owner = 'USM50' or owner = 'USR00' and index_name like 'Z%' minus select index_name from all_indexes where owner = 'XXX50' and index_name like 'Z%' and status = 'VALID';


    2c. Check your xxx50 indexes where xxx50 is the ADM library, the usr_library*, and the z105_library*** (but not the pw_library**):

    select index_name from all_indexes where owner = 'USM50' or owner = 'USR00' and index_name like 'Z%'
    minus select index_name from all_indexes where owner = 'XXX50'
    minus select index_name from all_indexes where index_name in ('Z34_ID', 'Z34_ID1', 'Z66_ID', 'Z66_ID1', 'Z67_ID', 'Z107_ID', 'Z700_ID', 'Z700_ID1', 'Z700_ID2') and status = 'VALID';

    * To see what your usr_library is, do this command from the unix prompt:

    echo $usr_library

    ** To see what your pw_library is, do this command from the unix prompt:

    echo $pw_library

    *** To see what your z105_library is, do this command from the unix prompt:

    echo $z105_library

    3a. Check your xxx00 (or xxx50) indexes where xxx00 (or xxx50) is the usr_library*, the pw_library**, and the z105_library*** -- but not the ADM library:

    select index_name from all_indexes where owner = 'USR00' and index_name like 'Z%' minus select index_name from all_indexes where owner = 'XXX00' and index_name like 'Z%' and status = 'VALID';

    3b. Check your xxx00 (or xxx50) indexes where xxx00 (or xxx50) is the usr_library* and the z105_library*** -- but not the $pw_library*** or the ADM library:

    select index_name from all_indexes where owner = 'USR00' and index_name like 'Z%'
    minus select index_name from all_indexes where owner = 'XXX00' and index_name like 'Z%'
    minus select index_name from all_indexes where index_name in ('Z34_ID', 'Z34_ID1', 'Z66_ID', 'Z66_ID1', 'Z67_ID', 'Z107_ID', 'Z700_ID', 'Z700_ID1', 'Z700_ID2') and status = 'VALID';


    4. Check VIR01 Oracle indexes:

    select index_name from all_indexes where index_name in
    ('Z00_ID', 'Z01_ID', 'Z01_ID2', 'Z01_ID3', 'Z01_ID4', 'Z02_ID', 'Z02_ID1', 'Z05_ID', 'Z05_ID1', 'Z05_ID2',
    'Z05_ID3', 'Z05_ID4', 'Z101_ID', 'Z103_ID', 'Z103_ID1', 'Z110_ID', 'Z11_ID', 'Z11_ID1', 'Z130_ID', 'Z131_ID',
    'Z13_ID','Z140_ID',
    'Z51_ID', 'Z52_ID', 'Z60_ID', 'Z63_ID', 'Z63_ID1', 'Z64_ID', 'Z65_ID')
    minus select index_name from all_indexes where owner = 'VIR01';

    Note: the Z56 (ADAM session table) has been omitted since it is not used by North American sites and seems to not be present in some cases and z200 (used only with ILL).

     

    Category: System management

    • Article last edited: 31-Mar-2017