|| TO_CHAR(dbms_rowid.rowid_relative_fno(NVL2('^^td_____y', rid, NULL)), 'FM0009')
|| '.'
|| TO_CHAR(dbms_rowid.rowid_block_number(NVL2('^^td_____y', rid, NULL)), 'FM00000009')
|| '.'
|| TO_CHAR(dbms_rowid.rowid_row_number(NVL2('^^td_____y', rid, NULL)), 'FM00009')05/17/2010: There was an issue with the syntax highlighter, some of XML tags were removed when the highlighter renders the page. It's addressed now. Thanks to Timur Akhmadeev for reporting the issue.Almost five years ago I published a post about custom treedump script. Since the original script had to be polished and I did not have time (or desire) to return back to that subject I kinda put it all aside. But recently I was asked to get it published. I decided to clean things up, get rid of PL/SQL logic and implement it all as a standalone SQL script. I did not spend to much time of testing it though; hopefully it does not have significant errors. If anything strange or abnormal pops up I guess I would have to fix, extend the functionality or error handling. Some primitive things are done:
SQL> SHOW USER
USER is "SYS"
SQL> ALTER SESSION SET CURRENT_SCHEMA=SCOTT;
Session altered.
SQL> @td
Usage: td index_name [owner_name]
SQL> @td blah
td: index "SCOTT"."BLAH" does not exist
SQL> @td i_user2 sys
Treedump of I_USER2 [47] (NORMAL) on
SYS."USER$"
index table
file block dba -> file block row (keys/block) USER# TYPE# SPARE1 SPARE2
----------------------------------------------------------------------- ---------- ---------- ---------- ----------
0001.00000116 (0x00400074/4194420) -> 0001.00000054.00001 (00001/0078) 0 1 0
0001.00000116 (0x00400074/4194420) -> 0001.00000054.00002 (00002/0078) 1 0 0
0001.00000116 (0x00400074/4194420) -> 0001.00000054.00003 (00003/0078) 2 0 0
0001.00000116 (0x00400074/4194420) -> 0001.00000054.00004 (00004/0078) 3 0 0
0001.00000116 (0x00400074/4194420) -> 0001.00000054.00005 (00005/0078) 4 0 0
0001.00000116 (0x00400074/4194420) -> 0001.00000054.00006 (00006/0078) 5 1 0
0001.00000116 (0x00400074/4194420) -> 0001.00000054.00007 (00007/0078) 6 0 0
...
0001.00000116 (0x00400074/4194420) -> 0001.00051166.00011 (00072/0078) 71 1 0
0001.00000116 (0x00400074/4194420) -> 0001.00051166.00012 (00073/0078) 72 1 0
0001.00000116 (0x00400074/4194420) -> 0001.00051166.00013 (00074/0078) 76 1 0
0001.00000116 (0x00400074/4194420) -> 0001.00051166.00014 (00075/0078) 77 1 0
0001.00000116 (0x00400074/4194420) -> 0001.00051166.00015 (00076/0078) 78 1 0
0001.00000116 (0x00400074/4194420) -> 0001.00000054.00000 (00077/0078) 79 0 0
0001.00000116 (0x00400074/4194420) -> 0001.00008152.00000 (00078/0078) 2147483638 1 0But if you have a case depicting a misbehavior of the script let me know.
.
REM
REM The script simulates treedump event behaviour rendering
REM the leaf block -> table structure.
REM
REM It is provided for illustrative purpose. The script is not
REM meant to be used to dump structures of huge indexes or be
REM part of any production infrastructure.
REM
REM It is provided "AS IS"; NO WARRANTY is implied.
REM
REM Usage: td index_name [owner_name]
REM
REM Author: Vladimir Begun (http://vbegun.blogspot.com/)
REM Version 0.2
REM
SET TERMOUT OFF
REM Saving the original SQL*Plus environment settings
STORE SET .td REPLACE
REM Settings
SET DEFINE "^" PAGES 0 TRIMSPOOL ON LINESIZE 32767 TAB OFF
SET HEADING OFF FEEDBACK OFF ARRAYSIZE 1 LONG 1024000 ECHO OFF
SET VERIFY OFF SERVEROUTPUT ON SIZE 100000 FORMAT TRUNCATED TERMOUT ON
REM Columns and substitution variables
COLUMN td_____i NEW_VALUE td_____i NOPRINT
COLUMN td_____o FORMAT A30 NEW_VALUE td_____o NOPRINT
COLUMN td_____t FORMAT A30 NEW_VALUE td_____t NOPRINT
COLUMN td_____x FORMAT A30 NEW_VALUE td_____x NOPRINT
COLUMN td_____f FORMAT A1 NEW_VALUE td_____f NOPRINT
COLUMN td_____c FORMAT A1000 NEW_VALUE td_____c NOPRINT
COLUMN td_____v FORMAT A1000 NEW_VALUE td_____v NOPRINT
COLUMN td_____fn FORMAT A10 NEW_VALUE td_____fn NOPRINT
COLUMN td_____y FORMAT A30 NEW_VALUE td_____y NOPRINT
COLUMN td_____y1 FORMAT A30 NEW_VALUE td_____y1 NOPRINT
COLUMN td_____banner FORMAT A60 HEADING OFF
COLUMN td_____irid NOPRINT
BREAK ON td_____irid NODUP SKIP 1
DEFINE td_____c = NULL
DEFINE td_____v = NULL
DEFINE td_____i = NULL
DEFINE td_____fn = SYS_OP_LBID
REM Command line handling
SET TERMOUT OFF
COLUMN 1 NEW_VALUE 1
COLUMN 2 NEW_VALUE 2
SELECT '' AS "1", '' AS "2"
FROM dual
WHERE ROWNUM = 0
/
SELECT UPPER('^^1') td_____x
, UPPER(NVL('^^2', SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA'))) td_____o
FROM dual
/
SET TERMOUT ON
SELECT 'Usage: td index_name [owner_name]' td_____banner
FROM dual
WHERE '^^1' IS NULL
/
REM Gathering of index/table information from the data dictionary.
REM It limits the number of indexes which are subject of dumping.
SELECT TO_CHAR(o.object_id, 'FM9999999') td_____i
, dbms_assert.schema_name(i.table_owner) td_____o
, dbms_assert.enquote_name(i.table_name) td_____t
, RTRIM(
LTRIM(
REPLACE(
dbms_xmlgen.convert(
XMLTYPE.GetStringVal(
XMLAGG(
XMLELEMENT(
"c"
, dbms_assert.enquote_name(column_name)
|| ' '
|| descend
)
ORDER BY column_position
)
)
, 1
)
, '</c><c>'
, ','
)
, '</c>'
)
, '</c>'
) td_____c
, RTRIM(
LTRIM(
REPLACE(
dbms_xmlgen.convert(
XMLTYPE.GetStringVal(
XMLAGG(
XMLELEMENT(
"c"
, dbms_assert.enquote_name(column_name)
)
ORDER BY column_position
)
)
, 1
)
, '</c><c>'
, ','
)
, '</c>'
)
, '</c>'
) td_____v
, CASE WHEN index_type LIKE '%NORMAL%' AND t.iot_type IS NULL THEN index_type END td_____y
, index_type td_____y1
FROM all_objects o
, all_indexes i
, all_ind_columns aic
, all_tables t
WHERE o.object_name = UPPER('^^td_____x')
AND o.owner = UPPER('^^td_____o')
AND o.object_name = i.index_name
AND o.owner = i.owner
AND o.object_type = 'INDEX'
AND aic.index_owner = i.owner
AND aic.index_name = i.index_name
AND aic.table_owner = i.table_owner
AND aic.table_name = i.table_name
AND t.owner = i.table_owner
AND t.table_name = i.table_name
GROUP BY
o.object_id
, i.table_owner
, i.table_name
, i.index_type
, t.iot_type
/
REM Handling of non-existing indexes
SELECT 'td: index "^^td_____o"."^^td_____x" does not exist' td_____banner
FROM dual
WHERE '^^td_____i' = 'NULL' AND '^^1' IS NOT NULL
/
SELECT 'DECODE' td_____fn, 'SYS' td_____o, 'DUAL' td_____t
FROM dual
WHERE '^^td_____i' = 'NULL'
/
SET PAGES 10000 HEADING ON
COLUMN td_____treedump FORMAT A71 -
HEADING 'Treedump of ^^td_____x [^^td_____i] (^^td_____y1) on|^^td_____o..^^td_____t|| index table |file block dba -> file block row (keys/block)' -
JUSTIFY CENTER
REM Actual dumping
WITH i AS (
SELECT /*+
MATERIALIZE
CURSOR_SHARING_EXACT
NO_MONITORING
DYNAMIC_SAMPLING(0)
INDEX_FFS(t ^^td_____x)
NOPARALLEL_INDEX(t ^^td_____x)
*/
ROWID rid
, ^^td_____fn(^^td_____i, 'L', ROWID) irid
, dbms_rowid.rowid_relative_fno(^^td_____fn(^^td_____i, 'L', ROWID)) irid_f
, dbms_rowid.rowid_block_number(^^td_____fn(^^td_____i, 'L', ROWID)) irid_b
FROM ^^td_____o..^^td_____t t
WHERE '^^td_____i' <> 'NULL'
)
SELECT i.irid td_____irid
, TO_CHAR(irid_f, 'FM0009')
|| '.'
|| TO_CHAR(irid_b, 'FM00000009')
|| ' ('
|| '0x'
|| TO_CHAR(dbms_utility.make_data_block_address(irid_f, irid_b), 'FM0000000x')
|| '/'
|| dbms_utility.make_data_block_address(irid_f, irid_b)
|| ')'
|| ' -> '
|| TO_CHAR(dbms_rowid.rowid_relative_fno(DECODE('^^td_____y', NULL, NULL, rid)), 'FM0009')
|| '.'
|| TO_CHAR(dbms_rowid.rowid_block_number(DECODE('^^td_____y', NULL, NULL, rid)), 'FM00000009')
|| '.'
|| TO_CHAR(dbms_rowid.rowid_row_number(DECODE('^^td_____y', NULL, NULL, rid)), 'FM00009')
|| ' ('
|| TO_CHAR(ROW_NUMBER() OVER (PARTITION BY i.irid ORDER BY ^^td_____c, rid), 'FM00009')
|| '/'
|| TO_CHAR(COUNT(*) OVER (PARTITION BY i.irid), 'FM0009')
|| ')' td_____treedump
, ^^td_____v
FROM i
, ^^td_____o..^^td_____t t
WHERE i.rid = t.ROWID
ORDER BY
^^td_____c
, rid
, i.irid
/
REM Cleanup and restoration of the original SQL*Plus environment
REPHEADER OFF
@.td
UNDEFINE 1 2 td_____i td_____o td_____t td_____x td_____f td_____c td_____v td_____fn td_____y td_____y1
COLUMN 1 CLEAR
COLUMN 2 CLEAR
COLUMN td_____i CLEAR
COLUMN td_____o CLEAR
COLUMN td_____t CLEAR
COLUMN td_____x CLEAR
COLUMN td_____f CLEAR
COLUMN td_____c CLEAR
COLUMN td_____v CLEAR
COLUMN td_____y CLEAR
COLUMN td_____y1 CLEAR
COLUMN td_____banner CLEAR
COLUMN td_____fn CLEAR
COLUMN td_____treedump CLEAR
COLUMN td_____irid CLEAR
SET TERMOUT ON
2 comments:
Thanks a lot, awesome script.
@jc, you are welcome
Post a Comment