showptkey.sql
パーティションキー一覧を表示する
code:SQL
set lines 200
@column_format
col NAME for a30
col OBJECT_TYPE for a12
col KEY_COLUMN_NAME for a30
col DATA_TYPE for a15
set timing offset timing off
select
pkc.OWNER
,case when pkc.OWNER = sys_context('userenv','current_schema') then '*'
else null
end OWN
,pkc.NAME
,pkc.OBJECT_TYPE
,pkc.COLUMN_NAME as KEY_COLUMN_NAME
,case when tc.DATA_TYPE in ('CHAR') then tc.DATA_TYPE || '(' || tc.DATA_LENGTH || ')'
else tc.DATA_TYPE
end DATA_TYPE
,pkc.COLUMN_POSITION
from
ALL_PART_KEY_COLUMNS pkc
inner join
ALL_TAB_COLS tc
on pkc.OWNER = tc.OWNER
and pkc.NAME = tc.TABLE_NAME
and pkc.COLUMN_NAME = tc.COLUMN_NAME
and pkc.OWNER not in ('SYS','SYSTEM','AUDSYS','MDSYS')
order by
OWNER
,NAME
,COLUMN_POSITION
;
@default_settings
17:10:33 SQL> @showptkey
OWNER O NAME OBJECT_TYPE KEY_COLUMN_NAME DATA_TYPE COLUMN_POSITION
--------------- - ------------------------------ ------------ ------------------------------ --------------- ---------------
SH * COSTS TABLE TIME_ID DATE 1
SH * SALES TABLE TIME_ID DATE 1