1. Installation<\/strong><\/h1>\nGithub link:\u00a0https:\/\/github.com\/dbarj\/pkg_xdollar_search\/<\/a><\/p>\nDownload and unzip pkg_xdollar_search_master.zip, navigate to the root pkg_xdollar_search_master directory, and connect as SYS to deploy the package:<\/p>\n
$ wget -O pkg_xdollar_search.zip\u00a0https:\/\/github.com\/dbarj\/pkg_xdollar_search\/archive\/master.zip\r\n$ unzip pkg_xdollar_search.zip\r\n$ cd pkg_xdollar_search-master\r\n$ sqlplus \/ as sysdba @pkg_xdollar_search.sql<\/pre>\nNow just execute the package:<\/p>\n
SQL> select * from table(pkg_xdollar_search.f_string('DBARJ','X$KQ%'));\r\n\r\nTABLE_NAME COLUMN_NAME ADDR INDX CONTENTS SEARCH_QRY EXEC_TIME_SECS\r\n---------- ------------- ---------------- ---- -------- ------------------------------------------- --------------\r\nX$KQLFBC KQLFBC_STRVAL 00007F5E2B2CEA98 37 DBARJ SELECT * FROM \"X$KQLFBC\" WHERE \"INDX\"=37; .06\r\nX$KQLFBC KQLFBC_STRVAL 00007F5E2B3739B8 918 DBARJ SELECT * FROM \"X$KQLFBC\" WHERE \"INDX\"=918; .06\r\nX$KQLFBC KQLFBC_STRVAL 00007F5E2B58FEC8 3543 DBARJ SELECT * FROM \"X$KQLFBC\" WHERE \"INDX\"=3543; .06\r\nX$KQLFBC KQLFBC_STRVAL 00007F5E2B41DB80 9853 DBARJ SELECT * FROM \"X$KQLFBC\" WHERE \"INDX\"=9853; .06<\/pre>\n2.\u00a0Documentation<\/strong><\/h1>\nThe package has basically 7 functions and 3 variables:<\/p>\n
FUNCTIONS:<\/strong><\/h3>\n\n- F_STRING<\/strong> : Search for a string value in VARCHAR2 X$ columns.<\/li>\n
- F_NUMBER<\/strong> : Search for a numeric value in NUMBER X$ columns.<\/li>\n
- F_RAW<\/strong> : Search for a raw value in RAW X$ columns.<\/li>\n
- F_DATE<\/strong> : Search for a date value in DATE X$ columns.<\/li>\n
- F_TIMESTAMP<\/strong> : Search for a timestamp value in TIMESTAMP X$ columns.<\/li>\n
- F_TIMESTAMPTZ<\/strong> : Search for a timestamp with time zone value in TIMESTAMP WITH TIME ZONE X$ columns.<\/li>\n
- F_CLOB<\/strong> : Search for a clob value in CLOB X$ columns.<\/li>\n<\/ul>\n
FUNCTION PARAMETERS:<\/strong><\/h3>\nAll functions above accept up to 3 parameters:<\/p>\n
\n- 1st param: p_input<\/strong> (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.<\/li>\n
- 2nd param: p_tab_name<\/strong> (optional) – You can optionally filter what X$ tables will be included in the search also using wild cards. By default, all are included: ‘X$%’<\/li>\n
- 3rd param: p_col_name<\/strong> (optional) – You can optionally filter what X$ columns will be included in the search also using wild cards. By default, all are included. ‘%’<\/li>\n<\/ul>\n
VARIABLES:<\/strong><\/h3>\nYou can also change some package variables to change the default behaviour of the program:<\/p>\n
\n- SKIP_DIAG<\/strong> (default TRUE) : By default the search functions will skip X$ tables starting with X$DIAG. Change to FALSE to include all.<\/li>\n
- SKIP_NONINDEXED<\/strong> (default FALSE) : By default the search functions will scan all X$ columns, including the non-indexed. Change to TRUE to skip them.<\/li>\n
- DEBUG<\/strong> (default FALSE) : Change to TRUE to enable DEBUG mode and see execution step by step in the query output.<\/li>\n<\/ul>\n
3. Usage Example<\/strong><\/h1>\nAll functions return a pipelined table in the following format:<\/p>\n
Column Name | Type | Description\r\n-------------- | ------------- | -----------------------------------------------------\r\nTABLE_NAME | VARCHAR2(30) | X$ table where value was found.\r\nCOLUMN_NAME | VARCHAR2(30) | X$ column where value was found.\r\nADDR | RAW(8) | ADDR of X$ table.\r\nINDX | NUMBER | INDX of X$ table.\r\nCONTENTS | VARCHAR2(4000) | Content found in the column.\r\nSEARCH_QRY | VARCHAR2(1000) | Query to retrieve the entire row for the found value.\r\nEXEC_TIME_SECS | NUMBER | Executed time in seconds.<\/pre>\nEXAMPLE<\/h4>\n\n- Searching in all X$ tables any column containing ‘DBARJ’.<\/li>\n<\/ul>\n
SQL> select * from table(pkg_xdollar_search.f_string(‘%DBARJ%’));<\/span><\/p>\n\n- Searching in X$KQLFBC table any column with the exactly text ‘DBARJ’.<\/li>\n<\/ul>\n
SQL> select * from table(pkg_xdollar_search.f_string(‘DBARJ’,’X$KQLFBC’));<\/span><\/p>\n\n- Searching for raw in all X$ tables starting with X$KC any column containing value ‘00000000B715CCB8’.<\/li>\n<\/ul>\n
SQL> select * from table(pkg_xdollar_search.f_raw(‘00000000B715CCB8′,’X$KC%’));<\/span><\/p>\n\n- Searching for CLOB value.<\/li>\n<\/ul>\n
SQL> var myclob clob;<\/span>
\nSQL> exec :myclob := ‘Bind UACs mismatch’;<\/span>
\nSQL> select * from table(pkg_xdollar_search.f_clob(:myclob));<\/span>
\n— OR<\/span>
\nSQL> select * from table(pkg_xdollar_search.f_clob(‘Bind UACs mismatch’));<\/span><\/p>\n\n- By default X$DIAG tables are skipped. To include them, change SKIP_DIAG variable to FALSE.<\/li>\n<\/ul>\n
SQL> exec pkg_xdollar_search.SKIP_DIAG := FALSE;<\/span>
\nSQL> select * from table(pkg_xdollar_search.f_string(‘%DBARJ%’));<\/span><\/p>\n\n- Note that SKIP_DIAG variable is also ignored when table filters are used.<\/li>\n<\/ul>\n
SQL> exec pkg_xdollar_search.SKIP_DIAG := TRUE;<\/span>
\nSQL> select * from table(pkg_xdollar_search.f_string(‘%DBARJ%’,’X$DIAG%’));<\/span><\/p>\n\n- It’s also possible to filter by column name instead of table name, using 3rd parameter.<\/li>\n<\/ul>\n
SQL> select * from table(pkg_xdollar_search.f_number(1234,NULL,’%INDX%’));<\/span><\/p>\n\n- 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.\u00a0Note that indexed columns performs an “FIXED TABLE FIXED INDEX” vs “FIXED TABLE FULL” which generates less latches and thus less impact in the environment.<\/li>\n<\/ul>\n
SQL> exec pkg_xdollar_search.SKIP_NONINDEXED := TRUE;<\/span>
\nSQL> select * from table(pkg_xdollar_search.f_raw(‘00000000B715CCB8′,’X$KC%’));<\/span><\/p>\nDEBUG<\/h4>\n\n- To debug the execution, change DEBUG variable to TRUE. Please note that all steps of package will be spooled.<\/li>\n<\/ul>\n
SQL> exec pkg_xdollar_search.DEBUG := TRUE;<\/span>
\nSQL> select * from table(pkg_xdollar_search.f_clob(‘Bind UACs mismatch’));<\/span><\/p>\nMISC<\/h4>\n\n- All column types of X$ tables:<\/li>\n<\/ul>\n
SYS> select kqfcodty,count(*) from x$kqfco group by kqfcodty order by 2 desc;<\/span><\/p>\n KQFCODTY COUNT(*)\r\n----------- -----------\r\n 2 13853 -- NUMBER\r\n 1 2906 -- VARCHAR2\r\n 23 1891 -- RAW\r\n 12 282 -- DATE\r\n 181 189 -- TIMESTAMP WITH TIME ZONE\r\n 180 40 -- TIMESTAMP\r\n 112 25 -- CLOB<\/pre>\n4. Download<\/strong><\/h1>\n
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:<\/p>\n
$ wget -O pkg_xdollar_search.zip\u00a0https:\/\/github.com\/dbarj\/pkg_xdollar_search\/archive\/master.zip\r\n$ unzip pkg_xdollar_search.zip\r\n$ cd pkg_xdollar_search-master\r\n$ sqlplus \/ as sysdba @pkg_xdollar_search.sql<\/pre>\nNow just execute the package:<\/p>\n
SQL> select * from table(pkg_xdollar_search.f_string('DBARJ','X$KQ%'));\r\n\r\nTABLE_NAME COLUMN_NAME ADDR INDX CONTENTS SEARCH_QRY EXEC_TIME_SECS\r\n---------- ------------- ---------------- ---- -------- ------------------------------------------- --------------\r\nX$KQLFBC KQLFBC_STRVAL 00007F5E2B2CEA98 37 DBARJ SELECT * FROM \"X$KQLFBC\" WHERE \"INDX\"=37; .06\r\nX$KQLFBC KQLFBC_STRVAL 00007F5E2B3739B8 918 DBARJ SELECT * FROM \"X$KQLFBC\" WHERE \"INDX\"=918; .06\r\nX$KQLFBC KQLFBC_STRVAL 00007F5E2B58FEC8 3543 DBARJ SELECT * FROM \"X$KQLFBC\" WHERE \"INDX\"=3543; .06\r\nX$KQLFBC KQLFBC_STRVAL 00007F5E2B41DB80 9853 DBARJ SELECT * FROM \"X$KQLFBC\" WHERE \"INDX\"=9853; .06<\/pre>\n2.\u00a0Documentation<\/strong><\/h1>\n
The package has basically 7 functions and 3 variables:<\/p>\n
FUNCTIONS:<\/strong><\/h3>\n
\n
- F_STRING<\/strong> : Search for a string value in VARCHAR2 X$ columns.<\/li>\n
- F_NUMBER<\/strong> : Search for a numeric value in NUMBER X$ columns.<\/li>\n
- F_RAW<\/strong> : Search for a raw value in RAW X$ columns.<\/li>\n
- F_DATE<\/strong> : Search for a date value in DATE X$ columns.<\/li>\n
- F_TIMESTAMP<\/strong> : Search for a timestamp value in TIMESTAMP X$ columns.<\/li>\n
- F_TIMESTAMPTZ<\/strong> : Search for a timestamp with time zone value in TIMESTAMP WITH TIME ZONE X$ columns.<\/li>\n
- F_CLOB<\/strong> : Search for a clob value in CLOB X$ columns.<\/li>\n<\/ul>\n
FUNCTION PARAMETERS:<\/strong><\/h3>\n
All functions above accept up to 3 parameters:<\/p>\n
\n
- 1st param: p_input<\/strong> (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.<\/li>\n
- 2nd param: p_tab_name<\/strong> (optional) – You can optionally filter what X$ tables will be included in the search also using wild cards. By default, all are included: ‘X$%’<\/li>\n
- 3rd param: p_col_name<\/strong> (optional) – You can optionally filter what X$ columns will be included in the search also using wild cards. By default, all are included. ‘%’<\/li>\n<\/ul>\n
VARIABLES:<\/strong><\/h3>\n
You can also change some package variables to change the default behaviour of the program:<\/p>\n
\n
- SKIP_DIAG<\/strong> (default TRUE) : By default the search functions will skip X$ tables starting with X$DIAG. Change to FALSE to include all.<\/li>\n
- SKIP_NONINDEXED<\/strong> (default FALSE) : By default the search functions will scan all X$ columns, including the non-indexed. Change to TRUE to skip them.<\/li>\n
- DEBUG<\/strong> (default FALSE) : Change to TRUE to enable DEBUG mode and see execution step by step in the query output.<\/li>\n<\/ul>\n
3. Usage Example<\/strong><\/h1>\n
All functions return a pipelined table in the following format:<\/p>\n
Column Name | Type | Description\r\n-------------- | ------------- | -----------------------------------------------------\r\nTABLE_NAME | VARCHAR2(30) | X$ table where value was found.\r\nCOLUMN_NAME | VARCHAR2(30) | X$ column where value was found.\r\nADDR | RAW(8) | ADDR of X$ table.\r\nINDX | NUMBER | INDX of X$ table.\r\nCONTENTS | VARCHAR2(4000) | Content found in the column.\r\nSEARCH_QRY | VARCHAR2(1000) | Query to retrieve the entire row for the found value.\r\nEXEC_TIME_SECS | NUMBER | Executed time in seconds.<\/pre>\nEXAMPLE<\/h4>\n
\n
- Searching in all X$ tables any column containing ‘DBARJ’.<\/li>\n<\/ul>\n
SQL> select * from table(pkg_xdollar_search.f_string(‘%DBARJ%’));<\/span><\/p>\n
\n
- Searching in X$KQLFBC table any column with the exactly text ‘DBARJ’.<\/li>\n<\/ul>\n
SQL> select * from table(pkg_xdollar_search.f_string(‘DBARJ’,’X$KQLFBC’));<\/span><\/p>\n
\n
- Searching for raw in all X$ tables starting with X$KC any column containing value ‘00000000B715CCB8’.<\/li>\n<\/ul>\n
SQL> select * from table(pkg_xdollar_search.f_raw(‘00000000B715CCB8′,’X$KC%’));<\/span><\/p>\n
\n
- Searching for CLOB value.<\/li>\n<\/ul>\n
SQL> var myclob clob;<\/span>
\nSQL> exec :myclob := ‘Bind UACs mismatch’;<\/span>
\nSQL> select * from table(pkg_xdollar_search.f_clob(:myclob));<\/span>
\n— OR<\/span>
\nSQL> select * from table(pkg_xdollar_search.f_clob(‘Bind UACs mismatch’));<\/span><\/p>\n\n
- By default X$DIAG tables are skipped. To include them, change SKIP_DIAG variable to FALSE.<\/li>\n<\/ul>\n
SQL> exec pkg_xdollar_search.SKIP_DIAG := FALSE;<\/span>
\nSQL> select * from table(pkg_xdollar_search.f_string(‘%DBARJ%’));<\/span><\/p>\n\n
- Note that SKIP_DIAG variable is also ignored when table filters are used.<\/li>\n<\/ul>\n
SQL> exec pkg_xdollar_search.SKIP_DIAG := TRUE;<\/span>
\nSQL> select * from table(pkg_xdollar_search.f_string(‘%DBARJ%’,’X$DIAG%’));<\/span><\/p>\n\n
- It’s also possible to filter by column name instead of table name, using 3rd parameter.<\/li>\n<\/ul>\n
SQL> select * from table(pkg_xdollar_search.f_number(1234,NULL,’%INDX%’));<\/span><\/p>\n
\n
- 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.\u00a0Note that indexed columns performs an “FIXED TABLE FIXED INDEX” vs “FIXED TABLE FULL” which generates less latches and thus less impact in the environment.<\/li>\n<\/ul>\n
SQL> exec pkg_xdollar_search.SKIP_NONINDEXED := TRUE;<\/span>
\nSQL> select * from table(pkg_xdollar_search.f_raw(‘00000000B715CCB8′,’X$KC%’));<\/span><\/p>\nDEBUG<\/h4>\n
\n
- To debug the execution, change DEBUG variable to TRUE. Please note that all steps of package will be spooled.<\/li>\n<\/ul>\n
SQL> exec pkg_xdollar_search.DEBUG := TRUE;<\/span>
\nSQL> select * from table(pkg_xdollar_search.f_clob(‘Bind UACs mismatch’));<\/span><\/p>\nMISC<\/h4>\n
\n
- All column types of X$ tables:<\/li>\n<\/ul>\n
SYS> select kqfcodty,count(*) from x$kqfco group by kqfcodty order by 2 desc;<\/span><\/p>\n
KQFCODTY COUNT(*)\r\n----------- -----------\r\n 2 13853 -- NUMBER\r\n 1 2906 -- VARCHAR2\r\n 23 1891 -- RAW\r\n 12 282 -- DATE\r\n 181 189 -- TIMESTAMP WITH TIME ZONE\r\n 180 40 -- TIMESTAMP\r\n 112 25 -- CLOB<\/pre>\n4. Download<\/strong><\/h1>\n
