[20230302]建立完善tpt o2.sql指令碼.txt

[20230302]建立完善tpt o2.sql指令碼.txt

--//發現該指令碼顯示沒有分割槽名，順便加上，這樣結合seg2.sql的執行，顯示更加直觀一些。

$ cat o2.sql
-- Copyright 2018 Tanel Poder. All rights reserved. More info at
-- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms & conditions.

col o_owner heading owner for a25
col o_object_name heading object_name for a30
col o_object_type heading object_type for a20
col o_status heading status for a9

select
    owner o_owner,
    object_name o_object_name,
    object_type o_object_type,
    subobject_name seg_part_name,
    status o_status,
    object_id oid,
    data_object_id d_oid,
    created,
    last_ddl_time
from
    dba_objects
where
        upper(object_name) LIKE
                                upper(CASE
                                        WHEN INSTR('&1','.') > 0 THEN
                                            SUBSTR('&1',INSTR('&1','.')+1)
                                        ELSE
                                            '&1'
                                        END
                                     )
AND     owner LIKE
                CASE WHEN INSTR('&1','.') > 0 THEN
                        UPPER(SUBSTR('&1',1,INSTR('&1','.')-1))
                ELSE
                        user
                END
order by
    o_object_name,
    o_owner,
    o_object_type
/

--//測試例子:
SYS@192.168.100.141:1521/dyhis> @ pr
==============================
PORT_STRING                   : x86_64/Linux 2.4.xx
VERSION                       : 19.0.0.0.0
BANNER                        : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
BANNER_FULL                   : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.9.0.0.0
BANNER_LEGACY                 : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
CON_ID                        : 0
PL/SQL procedure successfully completed.

SYS@192.168.100.141:1521/dyhis> @ seg2 audsys.AUD$UNIFIED
SEG_MB OWNER  SEGMENT_NAME SEG_PART_NAME SEGMENT_TYPE    SEG_TABLESPACE_NAME BLOCKS HDRFIL HDRBLK
------ ------ ------------ ------------- --------------- ------------------- ------ ------ ------
    10 AUDSYS AUD$UNIFIED  SYS_P20923    TABLE PARTITION SYSAUX                1280      3 145690
   144 AUDSYS AUD$UNIFIED  SYS_P19622    TABLE PARTITION SYSAUX               18432      3 656234
    88 AUDSYS AUD$UNIFIED  SYS_P20283    TABLE PARTITION SYSAUX               11264      3 550906

SYS@192.168.100.141:1521/dyhis> @ o2 audsys.AUD$UNIFIED
owner  object_name object_type     SEG_PART_NAME status     OID  D_OID CREATED             LAST_DDL_TIME
------ ----------- --------------- ------------- ------ ------- ------ ------------------- -------------------
AUDSYS AUD$UNIFIED TABLE                         VALID    18580        2020-10-20 10:28:13 2023-03-01 11:21:46
AUDSYS AUD$UNIFIED TABLE PARTITION SYS_P20283    VALID   174234 174234 2023-02-01 08:00:00 2023-02-01 08:00:00
AUDSYS AUD$UNIFIED TABLE PARTITION SYS_P20923    VALID   176513 176513 2023-03-01 08:00:05 2023-03-01 08:00:05
AUDSYS AUD$UNIFIED TABLE PARTITION SYS_P19622    VALID   171804 171804 2023-01-01 08:00:04 2023-01-01 08:00:04