This post is also available in:
Português
This package is useful when you need to scan in some X$ table for a given value but don't know exactly in which X$ table or column this value is.
So this package will scan in ALL X$ and print in which table and column the given value was located.
Remember that more scans on the memory means more CPU assumption, more latch acquisition and potential concurrency problem. So it's NOT recommended to run this package in PRODUCTION environment.
1. Installation
Github link: https://github.com/dbarj/pkg_xdollar_search/
Download and unzip pkg_xdollar_search_master.zip, navigate to the root pkg_xdollar_search_master directory, and connect as SYS to deploy the package:
$ wget -O pkg_xdollar_search.zip https://github.com/dbarj/pkg_xdollar_search/archive/master.zip $ unzip pkg_xdollar_search.zip $ cd pkg_xdollar_search-master $ sqlplus / as sysdba @pkg_xdollar_search.sql
Now just execute the package:
SQL> select * from table(pkg_xdollar_search.f_string('DBARJ','X$KQ%'));
TABLE_NAME COLUMN_NAME ADDR INDX CONTENTS SEARCH_QRY EXEC_TIME_SECS
---------- ------------- ---------------- ---- -------- ------------------------------------------- --------------
X$KQLFBC KQLFBC_STRVAL 00007F5E2B2CEA98 37 DBARJ SELECT * FROM "X$KQLFBC" WHERE "INDX"=37; .06
X$KQLFBC KQLFBC_STRVAL 00007F5E2B3739B8 918 DBARJ SELECT * FROM "X$KQLFBC" WHERE "INDX"=918; .06
X$KQLFBC KQLFBC_STRVAL 00007F5E2B58FEC8 3543 DBARJ SELECT * FROM "X$KQLFBC" WHERE "INDX"=3543; .06
X$KQLFBC KQLFBC_STRVAL 00007F5E2B41DB80 9853 DBARJ SELECT * FROM "X$KQLFBC" WHERE "INDX"=9853; .06
2. Documentation
The package has basically 7 functions and 3 variables:
FUNCTIONS:
- F_STRING : Search for a string value in VARCHAR2 X$ columns.
- F_NUMBER : Search for a numeric value in NUMBER X$ columns.
- F_RAW : Search for a raw value in RAW X$ columns.
- F_DATE : Search for a date value in DATE X$ columns.
- F_TIMESTAMP : Search for a timestamp value in TIMESTAMP X$ columns.
- F_TIMESTAMPTZ : Search for a timestamp with time zone value in TIMESTAMP WITH TIME ZONE X$ columns.
- F_CLOB : Search for a clob value in CLOB X$ columns.
FUNCTION PARAMETERS:
All functions above accept up to 3 parameters:
- 1st param: p_input (mandatory) - The value that you are searching. For F_STRING, wild cards '%' are accepted. For F_CLOB, the value will always be searched as substr. For others, will do an exact match.
- 2nd param: p_tab_name (optional) - You can optionally filter what X$ tables will be included in the search also using wild cards. By default, all are included: 'X$%'
- 3rd param: p_col_name (optional) - You can optionally filter what X$ columns will be included in the search also using wild cards. By default, all are included. '%'
VARIABLES:
You can also change some package variables to change the default behaviour of the program:
- SKIP_DIAG (default TRUE) : By default the search functions will skip X$ tables starting with X$DIAG. Change to FALSE to include all.
- SKIP_NONINDEXED (default FALSE) : By default the search functions will scan all X$ columns, including the non-indexed. Change to TRUE to skip them.
- DEBUG (default FALSE) : Change to TRUE to enable DEBUG mode and see execution step by step in the query output.
3. Usage Example
All functions return a pipelined table in the following format:
Column Name | Type | Description -------------- | ------------- | ----------------------------------------------------- TABLE_NAME | VARCHAR2(30) | X$ table where value was found. COLUMN_NAME | VARCHAR2(30) | X$ column where value was found. ADDR | RAW(8) | ADDR of X$ table. INDX | NUMBER | INDX of X$ table. CONTENTS | VARCHAR2(4000) | Content found in the column. SEARCH_QRY | VARCHAR2(1000) | Query to retrieve the entire row for the found value. EXEC_TIME_SECS | NUMBER | Executed time in seconds.
EXAMPLE
- Searching in all X$ tables any column containing 'DBARJ'.
SQL> select * from table(pkg_xdollar_search.f_string('%DBARJ%'));
- Searching in X$KQLFBC table any column with the exactly text 'DBARJ'.
SQL> select * from table(pkg_xdollar_search.f_string('DBARJ','X$KQLFBC'));
- Searching for raw in all X$ tables starting with X$KC any column containing value '00000000B715CCB8'.
SQL> select * from table(pkg_xdollar_search.f_raw('00000000B715CCB8','X$KC%'));
- Searching for CLOB value.
SQL> var myclob clob;
SQL> exec :myclob := 'Bind UACs mismatch';
SQL> select * from table(pkg_xdollar_search.f_clob(:myclob));
-- OR
SQL> select * from table(pkg_xdollar_search.f_clob('Bind UACs mismatch'));
- By default X$DIAG tables are skipped. To include them, change SKIP_DIAG variable to FALSE.
SQL> exec pkg_xdollar_search.SKIP_DIAG := FALSE;
SQL> select * from table(pkg_xdollar_search.f_string('%DBARJ%'));
- Note that SKIP_DIAG variable is also ignored when table filters are used.
SQL> exec pkg_xdollar_search.SKIP_DIAG := TRUE;
SQL> select * from table(pkg_xdollar_search.f_string('%DBARJ%','X$DIAG%'));
- It's also possible to filter by column name instead of table name, using 3rd parameter.
SQL> select * from table(pkg_xdollar_search.f_number(1234,NULL,'%INDX%'));
- By default, all X$ columns (indexed and non indexed) will be included in the output. However, if you wish to scan only on indexed columns, change SKIP_NONINDEXED to TRUE. Note that indexed columns performs an "FIXED TABLE FIXED INDEX" vs "FIXED TABLE FULL" which generates less latches and thus less impact in the environment.
SQL> exec pkg_xdollar_search.SKIP_NONINDEXED := TRUE;
SQL> select * from table(pkg_xdollar_search.f_raw('00000000B715CCB8','X$KC%'));
DEBUG
- To debug the execution, change DEBUG variable to TRUE. Please note that all steps of package will be spooled.
SQL> exec pkg_xdollar_search.DEBUG := TRUE;
SQL> select * from table(pkg_xdollar_search.f_clob('Bind UACs mismatch'));
MISC
- All column types of X$ tables:
SYS> select kqfcodty,count(*) from x$kqfco group by kqfcodty order by 2 desc;
KQFCODTY COUNT(*)
----------- -----------
2 13853 -- NUMBER
1 2906 -- VARCHAR2
23 1891 -- RAW
12 282 -- DATE
181 189 -- TIMESTAMP WITH TIME ZONE
180 40 -- TIMESTAMP
112 25 -- CLOB
4. Download
https://github.com/dbarj/pkg_xdollar_search/archive/master.zip
Have you enjoyed? Please leave a comment or give a 👍!
