SELECT p.table_name AS 表名, decode(p.partitioning_key_count, 1, '主分区') AS 分区类型, p.partitioning_type AS 分区类型, p.column_name AS 分区键, decode(nvl(q.subpartitioning_key_count, 0), 0, '无子分区', 1, '子分区') AS 有无子分区, q.subpartitioning_type AS 子分区类型, q.column_name AS 子分区键 FROM (SELECT a.table_name, a.partitioning_type, b.column_name, a.partitioning_key_count FROM user_part_tables a, user_part_key_columns b WHERE a.table_name = b.NAME AND b.object_type = 'TABLE') p, (SELECT a.table_name, a.subpartitioning_type, b.column_name, a.subpartitioning_key_count FROM user_part_tables a, user_subpart_key_columns b WHERE a.table_name = b.NAME AND a.subpartitioning_key_count <> 0 AND b.object_type = 'TABLE') q WHERE p.table_name = q.table_name(+) ORDER BY 5, 4, 1;
SELECT p.table_name AS 表名, decode(p.partitioning_key_count, 1, '主分区') AS 分区类型, p.partitioning_type AS 分区类型, p.column_name AS 分区键, decode(nvl(q.subpartitioning_key_count, 0), 0, '无子分区', 1, '子分区') AS 有无子分区, q.subpartitioning_type AS 子分区类型, q.column_name AS 子分区键 FROM (SELECT a.table_name, a.partitioning_type, b.column_name, a.partitioning_key_count FROM user_part_tables a, user_part_key_columns b WHERE a.table_name = b.NAME AND b.object_type = 'TABLE') p, (SELECT a.table_name, a.subpartitioning_type, b.column_name, a.subpartitioning_key_count FROM user_part_tables a, user_subpart_key_columns b WHERE a.table_name = b.NAME AND a.subpartitioning_key_count <> 0 AND b.object_type = 'TABLE') q WHERE p.table_name = q.table_name(+) ORDER BY 5, 4, 1;
SELECT p.table_name AS 表名,
decode(p.partitioning_key_count, 1, '主分区') AS 分区类型,
p.partitioning_type AS 分区类型,
p.column_name AS 分区键,
decode(nvl(q.subpartitioning_key_count, 0),
0,
'无子分区',
1,
'子分区') AS 有无子分区,
q.subpartitioning_type AS 子分区类型,
q.column_name AS 子分区键
FROM (SELECT a.table_name,
a.partitioning_type,
b.column_name,
a.partitioning_key_count
FROM user_part_tables a, user_part_key_columns b
WHERE a.table_name = b.NAME
AND b.object_type = 'TABLE') p,
(SELECT a.table_name,
a.subpartitioning_type,
b.column_name,
a.subpartitioning_key_count
FROM user_part_tables a, user_subpart_key_columns b
WHERE a.table_name = b.NAME
AND a.subpartitioning_key_count <> 0
AND b.object_type = 'TABLE') q
WHERE p.table_name = q.table_name(+)
ORDER BY 5, 4, 1;
decode(p.partitioning_key_count, 1, '主分区') AS 分区类型,
p.partitioning_type AS 分区类型,
p.column_name AS 分区键,
decode(nvl(q.subpartitioning_key_count, 0),
0,
'无子分区',
1,
'子分区') AS 有无子分区,
q.subpartitioning_type AS 子分区类型,
q.column_name AS 子分区键
FROM (SELECT a.table_name,
a.partitioning_type,
b.column_name,
a.partitioning_key_count
FROM user_part_tables a, user_part_key_columns b
WHERE a.table_name = b.NAME
AND b.object_type = 'TABLE') p,
(SELECT a.table_name,
a.subpartitioning_type,
b.column_name,
a.subpartitioning_key_count
FROM user_part_tables a, user_subpart_key_columns b
WHERE a.table_name = b.NAME
AND a.subpartitioning_key_count <> 0
AND b.object_type = 'TABLE') q
WHERE p.table_name = q.table_name(+)
ORDER BY 5, 4, 1;