Scripting the Data Dictionary
You can write scripts to remove some of the burden of writing queries against the data dictionary. Example 10-14 shows one way you might go about writing such a script, by presenting one that lists all the indexes on a table. Don't take in the entire script now. Glance over it to get the gist of how it's put together. Then read the sections that follow; they explain the more significant parts of the script in detail.
Example 10-14. A script to list all indexes on a given table
SET ECHO OFF
--DESCRIPTION
--Displays information about an index. The index name
--is passed as a parameter to this script.
--Remind the user of what the first argument should be.
--If the user forgot to specify the argument, he/she will
--be prompted for it when the first occurrence of &&1 is encountered.
PROMPT Argument 1 - Table name in [owner.]table_name format
PROMPT Describing indexes on table &&1
SET RECSEP OFF
SET NEWPAGE NONE
SET VERIFY OFF
SET PAGESIZE 9999
SET HEADING OFF
SET LINESIZE 80
SET FEEDBACK OFF
CLEAR COMPUTES
CLEAR COLUMNS
CLEAR BREAKS
--Turn off terminal output to avoid spurious blank lines
--caused by the SELECT that is done only to load the
--substitution variables.
SET TERMOUT OFF
--Dissect the input argument, and get the owner name and
--table name into two, separate substitution variables.
--The owner name defaults to the current user.
DEFINE s_owner_name = ' '
DEFINE s_table_name = ' '
COLUMN owner_name NOPRINT NEW_VALUE s_owner_name
COLUMN table_name NOPRINT NEW_VALUE s_table_name
SELECT
DECODE(INSTR('&&1','.'),
0,USER, /*Default to current user.*/
UPPER(SUBSTR('&&1',1,INSTR('&&1','.')-1))) owner_name,
DECODE(INSTR('&&1','.'),
0,UPPER('&&1'), /*Only the table name was passed in.*/
UPPER(SUBSTR('&&1',INSTR('&&1','.')+1))) table_name
FROM dual;
SET TERMOUT ON
--The following variables receive information about each index
DEFINE s_index_owner = ' '
DEFINE s_index_name = ' '
DEFINE s_index_type = ' '
DEFINE s_uniqueness = ' '
DEFINE s_tablespace_name = ' '
--Place new, index-related values into the above substitution variables
COLUMN owner NOPRINT NEW_VALUE s_index_owner
COLUMN table_name NOPRINT NEW_VALUE s_table_name
COLUMN index_name NOPRINT NEW_VALUE s_index_name
COLUMN index_type NOPRINT NEW_VALUE s_index_type
COLUMN uniqueness NOPRINT NEW_VALUE s_uniqueness
COLUMN tablespace_name NOPRINT NEW_VALUE s_tablespace_name
--Format the two columns that we'll actually display from the query
COLUMN indent FORMAT A19
COLUMN column_name FORMAT A30
--Skip a page for each new index
BREAK ON owner ON index_name SKIP PAGE
--Information about the index as a whole is printed in
--the page title.
TTITLE SKIP 1 LEFT 'INDEX ' s_index_owner "." s_index_name -
' ' s_index_type ' ' s_uniqueness SKIP 1 -
'DEFINED ON TABLE ' s_owner_name "." s_table_name SKIP 1 -
'STORED IN TABLESPACE ' s_tablespace_name SKIP 1 -
'CONTAINING COLUMNS: '
--List the columns that make up the index.
--The indent column moves the column list over to the
--right so that it comes after the 'CONTAINING COLUMNS:'
--portion of the header.
SELECT ai.owner, ai.index_name, ai.index_type,
ai.uniqueness, ai.tablespace_name,
' ' indent,
aic.column_name
FROM all_indexes ai JOIN all_ind_columns aic
ON ai.owner = aic.index_owner
AND ai.index_name = aic.index_name
WHERE ai.table_owner = '&&s_owner_name'
AND ai.table_name = '&&s_table_name'
ORDER BY ai.owner, ai.index_name, aic.column_position;
--Change all settings back to defaults
CLEAR COLUMNS
CLEAR BREAKS
SET PAGESIZE 14
SET HEADING ON
SET NEWPAGE 1
SET FEEDBACK ON
UNDEFINE 1
10.9.1 Running the Script
Run the script in Example 10-14 as follows , by passing a table name as a command-line argument:
SQL> @ex10-14 employee Argument 1 - Table name in [owner.]table_name format Describing indexes on table gennick.employee INDEX GENNICK.EMPLOYEE_PK NORMAL UNIQUE DEFINED ON TABLE GENNICK.EMPLOYEE STORED IN TABLESPACE USERS CONTAINING COLUMNS: EMPLOYEE_ID INDEX GENNICK.EMPLOYEE_BY_NAME NORMAL NONUNIQUE DEFINED ON TABLE GENNICK.EMPLOYEE STORED IN TABLESPACE USERS CONTAINING COLUMNS: EMPLOYEE_NAME
As you can see, the script displays information about the two indexes on employee . Both indexes consist of a single column each. The unique index happens to be the primary key index, on the employee_id column. The other index is on employee_name .
10.9.2 When the Parameter Is Omitted
Example 10-14 begins with an interesting bit of script that serves to remind you of what the command-line parameter should be:
--Remind the user of what the first argument should be. --If the user forgot to specify the argument, he/she will --be prompted for it when the first occurrence of &&1 is encountered. PROMPT Argument 1 - Table name in [owner.]table_name format
This reminder prompt is useful because if you forget to pass the table name as a parameter, SQL*Plus will prompt you when the parameter reference is first encountered in the script. However, parameters are referenced by numerical position, using substitution variable names such as 1 , 2 , etc. SQL*Plus's default prompt, when you omit the command-line argument, will ask you to enter a value for 1 , which doesn't help:
SQL> @ex10-14 Argument 1 - Table name in [owner.]table_name format Enter value for 1: employee Describing indexes on table employee . . .
Although the default prompt isn't helpful in this case, the output from the PROMPT command will serve to remind you of what 1 is supposed to be. This is a handy technique to use in scripts that accept parameters, especially when you plan to run those scripts interactively from the SQL*Plus command line.
|
10.9.3 Separating Owner and Table Names
The next significant part of Example 10-14 is a set of COLUMN commands along with a SELECT statement that serve to separate a parameter in owner . table_name format into two separate values:
COLUMN owner_name NOPRINT NEW_VALUE s_owner_name
COLUMN table_name NOPRINT NEW_VALUE s_table_name
SELECT
DECODE(INSTR('&&1','.'),
0,USER, /*Default to current user.*/
UPPER(SUBSTR('&&1',1,INSTR('&&1','.')-1))) owner_name,
DECODE(INSTR('&&1','.'),
0,UPPER('&&1'), /*Only the table name was passed in.*/
UPPER(SUBSTR('&&1',INSTR('&&1','.')+1))) table_name
FROM dual;
The DECODE function calls are what makes this SELECT flexible enough to deal with whether you choose to specify a username in response to the prompt. The first DECODE function call returns the appropriate owner name. You can interpret the arguments to this DECODE call as follows:
INSTR('&&1','.')
Looks for the character position of the period in the parameter passed to the script. If there is no period, INSTR returns zero. The result of INSTR is argument #1 to the DECODE function.
0,USER
If argument #1 is zero, meaning that no owner name was given, default to the currently logged in user's name.
UPPER(SUBSTR('&&1',1,INSTR('&&1','.')-1))
Otherwise, return everything up to, but not including, the period as the owner name.
The second DECODE function implements similar logic, but this time to return the table name:
INSTR('&&1','.')
Again, looks for the period in the parameter, returning zero if one is not found.
0,UPPER('&&1'), /*Only the table name was passed in.*/
If argument #1 is zero, return the entire command-line parameter as the table name.
UPPER(SUBSTR('&&1',INSTR('&&1','.')+1))
Otherwise, return the characters following the period as the table name.
The SELECT described in this section is done with TERMOUT off so you aren't bothered by the output from this SELECT against dual when you run the script.
10.9.4 Generating the Index Headings
Example 10-14 describes each index using a two-part structure. The first part, the index header , displays information about each index as a whole. The second part lists the columns in the index. The following are the header and column for employee 's primary key index:
INDEX GENNICK.EMPLOYEE_PK NORMAL UNIQUE DEFINED ON TABLE GENNICK.EMPLOYEE STORED IN TABLESPACE USERS CONTAINING COLUMNS: EMPLOYEE_ID
The reason for this two-part approach is that all the information couldn't possibly fit on a single line. To generate a header for each index, Example 10-14 takes advantage of SQL*Plus's pagination capabilities.
The SELECT statement joins all_indexes with all_ind_columns , and retrieves columns from both views. The following COLUMN commands cause SQL*Plus to continually (for each row retrieved) update a set of substitution variables with information from all_indexes .
COLUMN owner NOPRINT NEW_VALUE s_index_owner COLUMN table_name NOPRINT NEW_VALUE s_table_name COLUMN index_name NOPRINT NEW_VALUE s_index_name COLUMN index_type NOPRINT NEW_VALUE s_index_type COLUMN uniqueness NOPRINT NEW_VALUE s_uniqueness COLUMN tablespace_name NOPRINT NEW_VALUE s_tablespace_name
This information from all_indexes needs to be displayed only once. To that end, Example 10-14s TTITLE command references the substitution variables:
TTITLE SKIP 1 LEFT 'INDEX ' s_index_owner "." s_index_name - ' ' s_index_type ' ' s_uniqueness SKIP 1 - 'DEFINED ON TABLE ' s_owner_name "." s_table_name SKIP 1 - 'STORED IN TABLESPACE ' s_tablespace_name SKIP 1 - 'CONTAINING COLUMNS: '
The following BREAK command generates a page break each time a new index is encountered in the query's result set:
BREAK ON owner ON index_name SKIP PAGE
The result set is sorted by index and within index by column. The column name is the only value that SQL*Plus displays for each row (aside from some spaces for indention). As SQL*Plus lists the column names, every new combination of owner and index name forces a page break. For each new page, the page title prints, displaying the current values of the s_ substitution variables. SET HEADING OFF prevents any column headings from displaying.
