木匠的微型博客 Charlie Twitter

    follow me on Twitter

    Tuesday, March 31, 2009

    另一种方法显示ROWID扩充信息-table function

    TrackBack: http://www.ningoo.net/html/2009/how_to_get_extent_id_from_rowid.html

    NinGoo的把常用工具打包进工具箱的方法很好.
    然而木匠天生缺乏分类和组织能力,一路坚持学习,持续遗忘,自身却没有留下啥积累;好比猴子搬苞谷,抱在怀里的玉米棒永远也不会增多,郁闷.
    所以鄙人决定依靠GTD方法,每周总结学到的新知识,然后放进自己的便携工具箱.

    好了,鉴于本人不喜欢dbms_output.put_line,索性把NinGoo的RowID工具改造成了Table Function.
    通过简单的查询,可以在任何地方使用.

    后面附的PL/SQL Package源代码,包含了许多PL/SQL编程优秀实践,将来找机会慢慢展开来讲.

    用法:

    SQL> select * from table(UTILITY.tb_obj.rowid_info('AAARUtAAEAAABm2AAA'));

    COLUMN_VALUE
    --------------------------------------------------------------------------------
    rowid: AAARUtAAEAAABm2AAA
    file_id: 4
    block_id: 6582
    data_object_id: 70957
    owner: INVDB
    object_name: BOOK_UPLOAD_LOG
    extent_id: 0

    7 rows selected.

    tb_ 是toolbox的缩写.



    工具用户需要的对象权限:

    grant select on dba_objects to utility;
    grant select on dba_extents to utility;

    源代码:

    注意: 如果你还没有安装捕获错误工具包u$err和(instrument)代码跟踪包debug, 可以先用备注屏蔽掉相关的代码.


    CREATE OR REPLACE PACKAGE UTILITY.tb_obj
    /*
    | Copyright Information Here
    |
    | File name:
    |
    | Overview:
    |
    | Author(s):
    |
    | Modification History:
    | Date Who What
    |
    */
    IS

    TYPE print_tab IS TABLE OF varchar2(200);

    FUNCTION rowid_info (p_rowid in varchar2) return print_tab PIPELINED DETERMINISTIC;
    PROCEDURE subprogram_name;

    END tb_obj;
    /

    CREATE OR REPLACE PACKAGE BODY UTILITY.tb_obj
    /*
    | Copyright Information Here
    |
    | File name:
    |
    | Overview:
    Tool box to get object information.

    | Author(s):
    |
    | Modification History:
    | Date Who What
    31-Mar-2009 Charlie (Yi): Create the package.

    It meets user requirements
    It is maintainable
    It runs fast enough to minimize user frustration

    **) Pl/SQL development workflow, Four steps of preparing an application

    - Validate program requirements
    # ask lots of questions
    # what users ask for is not always the easiest way to solve a problem
    # consider other approaches, include business processes and programming algorithms
    - Implement header of the program
    # good name for the program, accurately represent the purpose of the program
    # inputs and outputs
    # overload sub-procedure ?
    - Define the test cases
    # Verify it works
    # how will I know when I am done with this program
    - Build test code

    |
    |
    */
    IS
    PROCEDURE initialize_pkg
    IS
    BEGIN
    DBMS_APPLICATION_INFO.SET_MODULE( 'tb_obj','init' );
    END initialize_pkg;

    FUNCTION rowid_info (p_rowid in varchar2)
    return print_tab PIPELINED DETERMINISTIC
    /*
    | Copyright Information Here
    |
    | File name:
    |
    | Overview: Display extended rowid information.
    |
    | Author(s): Charlie MuJiang
    |
    | Modification History:
    | Date Who What
    31-Mar-2009 Charlie (Yi): Create the function.

    Privilege:

    grant select on dba_objects to utility;
    grant select on dba_extents to utility;

    Call example:

    SQL> select * from table(UTILITY.tb_obj.rowid_info('AAARUtAAEAAABm2AAA'));

    COLUMN_VALUE
    --------------------------------------------------------------------------------
    rowid: AAARUtAAEAAABm2AAA
    file_id: 4
    block_id: 6582
    data_object_id: 70957
    owner: INVDB
    object_name: BOOK_UPLOAD_LOG
    extent_id: 0

    7 rows selected.

    |
    */
    IS

    ln_block_id number;
    ln_file_id number;
    ln_object_id number;
    ln_extent_id number;
    ls_object_name varchar2(30);
    ls_owner varchar2(30);

    PROCEDURE initialize
    IS
    BEGIN
    DBMS_APPLICATION_INFO.SET_ACTION( 'rowid_info' );
    END initialize;

    PROCEDURE cleanup
    IS
    BEGIN
    DBMS_APPLICATION_INFO.SET_MODULE(Null,Null);
    END cleanup;
    BEGIN
    initialize;
    /*
    Main body of program
    */
    debug.f('start by user %s', user);

    select dbms_rowid.ROWID_BLOCK_NUMBER(p_rowid),
    dbms_rowid.ROWID_RELATIVE_FNO(p_rowid),
    dbms_rowid.ROWID_OBJECT(p_rowid)
    into ln_block_id,ln_file_id,ln_object_id
    from dual;

    select owner,object_name
    into ls_owner,ls_object_name
    from dba_objects
    where data_object_id=ln_object_id;

    PIPE ROW(' rowid: '||p_rowid);
    PIPE ROW(' file_id: '||ln_file_id);
    PIPE ROW(' block_id: '||ln_block_id);
    PIPE ROW('data_object_id: '||ln_object_id);

    for c in (
    select extent_id,
    owner, segment_name,partition_name, segment_type,tablespace_name
    from dba_extents
    where
    owner=ls_owner and
    segment_name=ls_object_name and
    file_id=ln_file_id and
    ln_block_id between block_id and block_id + blocks - 1
    )
    loop
    PIPE ROW(' owner: '||c.owner);
    PIPE ROW(' object_name: '||c.segment_name);
    PIPE ROW('partition_name: '||c.partition_name);
    PIPE ROW(' segment_type: '||c.segment_type);
    PIPE ROW(' tbs_name: '||c.tablespace_name);
    PIPE ROW(' extent_id: '||c.extent_id);
    end loop;

    debug.f('process %s rows', SQL%RowCount);

    cleanup;
    EXCEPTION
    WHEN NO_DATA_NEEDED THEN
    cleanup;
    return;
    WHEN OTHERS
    THEN
    /* Don't forget to clean up here, too! */
    cleanup;

    /* Standard error logging mechanism */
    u$err.err;
    RollBack;
    raise;
    END rowid_info;

    PROCEDURE subprogram_name
    /*
    | Copyright Information Here
    |
    | File name:
    |
    | Overview:
    |
    | Author(s):
    |
    | Modification History:
    | Date Who What
    |
    */
    IS
    PROCEDURE initialize
    IS
    BEGIN
    DBMS_APPLICATION_INFO.SET_ACTION( 'subprogram_name' );
    END initialize;

    PROCEDURE cleanup
    IS
    BEGIN
    DBMS_APPLICATION_INFO.SET_MODULE(Null,Null);
    END cleanup;
    BEGIN
    initialize;
    /*
    Main body of program
    */
    debug.f('update %s rows', SQL%RowCount);

    cleanup;
    EXCEPTION
    WHEN OTHERS
    THEN
    /* Don't forget to clean up here, too! */
    cleanup;

    /* Standard error logging mechanism */
    u$err.err;
    --RollBack;
    raise;
    END subprogram_name;

    BEGIN
    initialize_pkg;
    END tb_obj;
    /


    2 comments:

    NinGoo's Notes said...

    and ln_block_id between block_id and block_id+blocks;

    这个地方有bug,忘了-1了,呵呵

    and ln_block_id between block_id and block_id+blocks-1;

    Unknown said...

    To NinGoo,

    改正啦. 有错就改.