• 博客访问: 1409034
  • 博文数量: 169
  • 用 户 组: 普通用户
  • 注册时间: 2014-01-05 07:21
  • 认证徽章:
个人简介

QQ:20084622

文章分类

全部博文(169)

文章存档

2017年(13)

2016年(49)

2015年(82)

2014年(25)

分类: Oracle

2017-04-05 12:57:20

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
阅读(543) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~
评论热议
请登录后评论。

登录 注册