parttab.sql


Print this article  Send the URL to a friend

— ==========================================================================

— 
— Copyright (C) RoughSea Ltd, 2005
— http://www.roughsea.com
— 
— This program is free software; you can redistribute it and/or modify
— it under the terms of the GNU General Public License as published by
— the Free Software Foundation; either version 2 of the License, or
— any later version.
— 
— This program is distributed in the hope that it will be useful,
— but WITHOUT ANY WARRANTY; without even the implied warranty of
— MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
— GNU General Public License for more details.
— 
— You should have received a copy of the GNU General Public License
— along with this program; if not, write to the Free Software
— Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
— 
— ==========================================================================
— 
— A script which can be useful for having a quick look at partitioning.
— It lists what is partitioned for the current schema (ie one must connect
— to an account which can see the SYS tables and execute ALTER SESSION SET
— CURRENT_SCHEMA=SCOTT to make SCOTT the current schema). Sample output :
— 
— OBJECT_NAME TY PARTITIONING
— --------------------------------- — ---------------------------------
— PS_RT_RATE_TBL 100R
— P EFFDT
— — PS_RT_RATE_TBL LN 100R
— P EFFDT
— — PSART_RATE_TBL LN 100R
— P EFFDT
— — PSBRT_RATE_TBL LN 100R
— P EFFDT
— 
— PS_JRNL_LN 100R
— P JOURNAL_DATE
— — PS_JRNL_LN LN 100R
— P JOURNAL_DATE
— — PSAJRNL_LN LN 100R
— P JOURNAL_DATE
— 
— PS_LEDGER_ADB 100R
— P FISCAL_YEAR
— ACCOUNTING_PERIOD
— — PS_LEDGER_ADB LN 100R
— P FISCAL_YEAR
— ACCOUNTING_PERIOD
— 
— Comments : OBJECT_NAME is the name of the table or, if the line is
— prefixed by —, of an associated (partitioned) index, or can be empty
— to list the partitioning keys.
— TY(PE) is always empty for tables. For an index, it is either G (global)
— or LP (local/prefixed) or LN (local/non prefixed) (LP and LN are of course
— meaningless when the partition key is a single column as above).
— For partitioning keys, a value is displayed for the first column (we may
— have several in the case of a composite partitioning key), and is either
— P (partition), or SP (sub-partition).
— PARTITIONING indicates :
— - for tables and indexes the number of partitions followed by the
— partitioning mode ([R]ange, [H]ash, [L]ist), which may be followed
— by / and similar information for subpartitions.
— - otherwise the names (iin the proper order) of the columns used as
— partitioning keys.
—  column special_dummy noprint column dummy noprint column OBJECT_NAME format A33 column "TYPE" format A2 column PARTITIONING format A33 break on special_dummy skip 1 on "TYPE" select /*+ ORDERED FIRST_ROWS */ o.obj# special_dummy, 0 dummy, 1 dummy, 0 dummy, o.name OBJECT_NAME, ’ ’ "TYPE", to_char(po.partcnt) || decode(po.parttype, 1, ’R’, 2, ’H’, 3, ’S’, 4, ’L’, ’?’) || decode(mod(po.spare2, 256), 0, to_char(NULL), 2, ’/’ || to_char(mod(trunc(po.spare2/65536), 65536)) || ’H’, 3, ’/’ || to_char(mod(trunc(po.spare2/65536), 65536)) || ’S’, 4, ’/’ || to_char(mod(trunc(po.spare2/65536), 65536)) || ’L’, ’/’ || to_char(mod(trunc(po.spare2/65536), 65536)) || ’?’) PARTITIONING from sys.partobj$ po, sys.obj$ o where o.obj# = po.obj# and o.type# in (2, 25) — Table + IOT and o.owner# = sys_context(’USERENV’, ’CURRENT_SCHEMAID’) union all select /*+ ORDERED FIRST_ROWS */ i.bo#, i.obj#, 4, 0, substr(’— ’ || io.name, 1, 33), decode(bitand(po.flags, 1), 1, ’L’, ’G’) || decode(po.partkeycols, 0, ’ ’, decode(bitand(po.flags,2), 2, ’P’, ’N’)) "IF", to_char(po.partcnt) || decode(po.parttype, 1, ’R’, 2, ’H’, 3, ’S’, 4, ’L’, ’?’) || decode(mod(po.spare2, 256), 0, to_char(NULL), 2, ’/’ || to_char(mod(trunc(po.spare2/65536), 65536)) || ’H’, 3, ’/’ || to_char(mod(trunc(po.spare2/65536), 65536)) || ’S’, 4, ’/’ || to_char(mod(trunc(po.spare2/65536), 65536)) || ’L’, ’/’ || to_char(mod(trunc(po.spare2/65536), 65536)) || ’?’) PARTITIONING from sys.partobj$ po, sys.ind$ i, sys.obj$ o, sys.obj$ io where i.obj# = po.obj# and i.obj# = io.obj# and o.obj# = i.bo# and o.owner# = sys_context(’USERENV’, ’CURRENT_SCHEMAID’) union all select /*+ ORDERED FIRST_ROWS */ o.obj#, 0, 2, pc.pos# COLUMN_POSITION, to_char(NULL), ’P ’ PARTITIONING_TYPE, decode(bitand(c.property, 1), 1, a.name, c.name) COLUMN_NAME from sys.partcol$ pc, sys.obj$ o, sys.col$ c, sys.attrcol$ a where pc.obj# = o.obj# and pc.obj# = c.obj# and c.intcol# = pc.intcol# and o.owner# = sys_context(’USERENV’, ’CURRENT_SCHEMAID’) and c.obj# = a.obj#(+) and c.intcol# = a.intcol#(+) union all select /*+ ORDERED FIRST_ROWS */ i.bo#, i.obj#, 5, pc.pos#, to_char(NULL), ’P ’, decode(bitand(c.property, 1), 1, a.name, c.name) from sys.partcol$ pc, sys.ind$ i, sys.obj$ o, sys.obj$ io, sys.col$ c, sys.attrcol$ a where pc.obj# = i.obj# and i.obj# = io.obj# and i.bo# = c.obj# and c.intcol# = pc.intcol# and o.obj# = i.bo# and o.owner# = sys_context(’USERENV’, ’CURRENT_SCHEMAID’) and c.obj# = a.obj#(+) and c.intcol# = a.intcol#(+) union all select /*+ ORDERED FIRST_ROWS */ spc.obj#, 0, 4, spc.pos# COLUMN_POSITION, to_char(NULL), ’SP’ OBJECT_TYPE, decode(bitand(c.property, 1), 1, a.name, c.name) COLUMN_NAME from sys.subpartcol$ spc, sys.obj$ o, sys.col$ c, sys.attrcol$ a where spc.obj# = o.obj# and spc.obj# = c.obj# and c.intcol# = spc.intcol# and o.owner# = sys_context(’USERENV’, ’CURRENT_SCHEMAID’) and c.obj# = a.obj#(+) and c.intcol# = a.intcol#(+) union all select /*+ ORDERED FIRST_ROWS */ i.bo#, i.obj#, 6, spc.pos#, to_char(NULL), ’SP’, decode(bitand(c.property, 1), 1, a.name, c.name) from sys.subpartcol$ spc, sys.ind$ i, sys.obj$ o, sys.col$ c, sys.attrcol$ a where spc.obj# = i.obj# and i.bo# = c.obj# and c.intcol# = spc.intcol# and o.obj# = i.bo# and o.owner# = sys_context(’USERENV’, ’CURRENT_SCHEMAID’) and c.obj# = a.obj#(+) and c.intcol# = a.intcol#(+) order by 1, 2, 3, 4 /