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
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/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/28539951/viewspace-2136595/