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

oracle實用sql(14)--查詢分割槽表的分割槽列和子分割槽列

相關文章