[20260925]建立完善oname.sql脚本.txt

--//tpt 有1个ou.sql脚本，通过对象名查询相关信息，但是没有显示data_object_id,object_id的功能，自己写一个oname.sql脚本，支
--//持data_object_id,object_id的显示,并且对象名查询支持正则表达式，这样更加灵活一些，在原来基础上再修改增加
--//data_object_id,object_id的16进制的显示功能。

$ cat sqllaji/tpt/oname.sql
-- Copyright 2018 Tanel Poder. All rights reserved. More info at http://tanelpoder.com
-- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms & conditions.

-- Script by Tanel Poder (http://www.tanelpoder.com)
--
-- Look up object info by object_name

col o_owner heading OWNER for a25
col o_object_name heading OBJECT_NAME for a30
col o_object_type heading OBJECT_TYPE for a18
col o_status heading STATUS for a9

set term off
column v_owner new_value v_owner
column v_table new_value v_table
select
 upper(CASE
           WHEN INSTR('&1','.') > 0 THEN
               SUBSTR('&1',INSTR('&1','.')+1)
           ELSE
               '&1'
            END
            )  v_table,
 nvl(CASE WHEN INSTR('&1','.') > 0 THEN
            UPPER(SUBSTR('&1',1,INSTR('&1','.')-1))
        ELSE
            user
        END,user) v_owner
from dual;
set term on

select
    owner o_owner,
    object_name o_object_name,
    object_type o_object_type,
    subobject_name,
    created,
    last_ddl_time,
    status o_status,
    data_object_id,
    object_id,
        '0x'|| to_char( data_object_id,'fm0xxxxxxxx') data_object_id_hex,
        '0x'|| to_char( object_id,'fm0xxxxxxxx') object_id_hex
from
    dba_objects
where
    owner = '&v_owner'
    and regexp_like (object_name , '&v_table','i')
order by
    o_object_name,
    o_owner,
    o_object_type
/


--//简单测试：
SCOTT@book01p> @ oname scott.^dept$|^emp$
OWNER OBJECT_NAME OBJECT_TYPE SUBOBJECT_NAME CREATED                           LAST_DDL_TIME       STATUS    DATA_OBJECT_ID  OBJECT_ID DATA_OBJECT_ OBJECT_ID_HE
----- ----------- ----------- -------------- --------------------------------- ------------------- --------- -------------- ---------- ------------ ------------
SCOTT DEPT        TABLE                      2026-06-25 09:20:13               2026-06-25 09:20:13 VALID              75828      75828 0x000012834  0x000012834
SCOTT EMP         TABLE                      2026-06-25 09:20:13               2026-06-25 09:20:13 VALID              75830      75830 0x000012836  0x000012836