本帖最后由 xhsfvy70485 于 2016-4-11 14:15 编辑
你可以自己写PL/SQL程序,循序取出各个分区表的数据。
先确定分区表下的分区。比如下例为确定COSTS分区表下的所有分区。
SQL> SELECT OBJ#,OWNER#,NAME,SUBNAME FROM OBJ$ WHERE NAME LIKE '%COSTS%';
OBJ# OWNER# NAME SUBNAME
---------- ---------- ------------------------------ ------------------------------
74118 88 COSTS COSTS_1995
74119 88 COSTS COSTS_1996
74120 88 COSTS COSTS_H1_1997
74121 88 COSTS COSTS_H2_1997
74122 88 COSTS COSTS_Q1_1998
74126 88 COSTS COSTS_Q1_1999
74130 88 COSTS COSTS_Q1_2000
74134 88 COSTS COSTS_Q1_2001
74138 88 COSTS COSTS_Q1_2002
74142 88 COSTS COSTS_Q1_2003
74123 88 COSTS COSTS_Q2_1998
OBJ# OWNER# NAME SUBNAME
---------- ---------- ------------------------------ ------------------------------
74127 88 COSTS COSTS_Q2_1999
74131 88 COSTS COSTS_Q2_2000
74135 88 COSTS COSTS_Q2_2001
74139 88 COSTS COSTS_Q2_2002
74143 88 COSTS COSTS_Q2_2003
74124 88 COSTS COSTS_Q3_1998
74128 88 COSTS COSTS_Q3_1999
74132 88 COSTS COSTS_Q3_2000
74136 88 COSTS COSTS_Q3_2001
74140 88 COSTS COSTS_Q3_2002
74144 88 COSTS COSTS_Q3_2003
OBJ# OWNER# NAME SUBNAME
---------- ---------- ------------------------------ ------------------------------
74125 88 COSTS COSTS_Q4_1998
74129 88 COSTS COSTS_Q4_1999
74133 88 COSTS COSTS_Q4_2000
74137 88 COSTS COSTS_Q4_2001
74141 88 COSTS COSTS_Q4_2002
74145 88 COSTS COSTS_Q4_2003
74117 88 COSTS
29 rows selected.
PL/SQL程序循环SUBNAME字段不为空的记录,逐个取得各分区表的记录数。
SELECT COUNT(*) FROM SH.COSTS PARTITION(XXXXXX);