ITPub博客

oracle实用sql(14)--查询分区表的分区列和子分区列

原创 Oracle 作者:selectshen 时间:2017-04-05 12:57:20 0 删除 编辑
SELECT *
  FROM (SELECT B.OWNER,
               B.TABLE_NAME,
               B.PARTITIONING_TYPE,
               ' ' SUBPARTITIONING_TYPE,
               A.COLUMN_NAME,
               A.COLUMN_POSITION
          FROM DBA_PART_KEY_COLUMNS A, DBA_PART_TABLES B
         WHERE A.OWNER = B.OWNER
           AND A.NAME = B.TABLE_NAME
        UNION ALL
        SELECT B.OWNER,
               B.TABLE_NAME,
               '' PARTITIONING_TYPE,
               B.PARTITIONING_TYPE SUBPARTITIONING_TYPE,
               A.COLUMN_NAME,
               A.COLUMN_POSITION
          FROM (SELECT U.NAME OWNER,
                       O.NAME,
                       DECODE(BITAND(C.PROPERTY, 1), 1, A.NAME, C.NAME) COLUMN_NAME,
                       PC.POS# COLUMN_POSITION
                  FROM SUBPARTCOL$ PC, OBJ$ O, COL$ C, USER$ U, ATTRCOL$ A
                 WHERE PC.OBJ# = O.OBJ#
                   AND PC.OBJ# = C.OBJ#
                   AND C.INTCOL# = PC.INTCOL#
                   AND U.USER# = O.OWNER#
                   AND C.OBJ# = A.OBJ#(+)
                   AND C.INTCOL# = A.INTCOL#(+)
                   AND O.NAMESPACE = 1
                   AND O.REMOTEOWNER IS NULL
                   AND O.LINKNAME IS NULL
                   AND O.SUBNAME IS NULL) A,
               DBA_PART_TABLES B
         WHERE A.OWNER = B.OWNER
           AND A.NAME = B.TABLE_NAME)
 WHERE OWNER LIKE '%SCOTT%'
   AND TABLE_NAME NOT LIKE 'BIN$%'
 ORDER BY OWNER,
          TABLE_NAME,
          PARTITIONING_TYPE,
          COLUMN_POSITION,
          SUBPARTITIONING_TYPE,
          COLUMN_POSITION

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28539951/viewspace-2136595/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2014-01-05

  • 博文量
    169
  • 访问量
    1419037