统计oracle分区表各个分区的数据条数

建立分区表LOAN，插入数据

create table LOAN
(
  name                VARCHAR2(30),
  BILL_NO             VARCHAR2(30) not null,
  DATE1               NUMBER(8) not null
)
PARTITION BY RANGE(date1) INTERVAL(100)
  (
    PARTITION  PART1 VALUES LESS THAN (20190601)      
   tablespace USERS
   pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 20M
      next 1M
      minextents 1
      maxextents unlimited
    )
  );

insert into loan (bill_no,date1) values ('wq3333243',20190209);
insert into loan (bill_no,date1) values ('wq3333243',20200209);
insert into loan (bill_no,date1) values ('wq3333243',20200215);
insert into loan (bill_no,date1) values ('wq3333243',20200225);
insert into loan (bill_no,date1) values ('wq3333666',20220305);
insert into loan (bill_no,date1) values ('wq3333666',20220405);
insert into loan (bill_no,date1) values ('wq3333545',20220605);
insert into loan (bill_no,date1) values ('wq3333545',20220601);
insert into loan (bill_no,date1) values ('wq3333545',20220616);
insert into loan (bill_no,date1) values ('wq3333545',20220643);
insert into loan (bill_no,date1) values ('wq3333545',20220703);
insert into loan (bill_no,date1) values ('wq3333545',20220916);

使用pl/sql代码统计数量

 1 declare
 2       CURSOR CUR_01 IS select * from user_tab_partitions where table_name='LOAN';
 3       CUR_02 CUR_01%ROWTYPE; 
 4       a1 number(2);
 5       sql01 varchar2(150);
 6      
 7     BEGIN 
 8       FOR CUR_02 IN CUR_01 LOOP  
 9          
10        sql01 := 'select count(1)  from LOAN partition('||CUR_02.PARTITION_NAME||') ';
11        EXECUTE IMMEDIATE sql01 INTO a1 ;
12         DBMS_OUTPUT.PUT_LINE('分区名：'||CUR_02.PARTITION_NAME ||'  数量：'|| a1);
13       END LOOP
14   end;

注意第10行要使用动态sql，不能直接传分区名，结果

 

posted @ 2022-02-21 16:14  fascinating  阅读(2041)  评论(0)    收藏  举报