oracle查看分区的数据

来源：互联网发布：电信80端口编辑：程序博客网时间：2024/06/06 05:26

查看分区的数据：

select * from table_name partition(partitionname);

1、创建测试表

create table test
( INT_ID          INTEGER,
VENDOR_ID       INTEGER,
SCAN_START_TIME     DATE
)partition by range (SCAN_START_TIME)
(
partition test0 values less than (TO_DATE(' 2014-06-21 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace testdbs01
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 64K
      next 1M
      minextents 1
      maxextents unlimited
    ),
partition test1 values less than (TO_DATE(' 2014-10-16 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace testdbs02
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 64K
      next 1M
      minextents 1
      maxextents unlimited
    )
);

insert into test values (1,0,TO_DATE(' 2014-10-15 00:00:00', 'YYYY-MM-DD HH24:MI:SS'));
insert into test values (2,0,TO_DATE(' 2014-10-15 00:00:00', 'YYYY-MM-DD HH24:MI:SS'));
insert into test values (3,0,TO_DATE(' 2014-10-15 00:00:00', 'YYYY-MM-DD HH24:MI:SS'));
insert into test values (4,0,TO_DATE(' 2014-10-15 00:00:00', 'YYYY-MM-DD HH24:MI:SS'));
insert into test values (5,0,TO_DATE(' 2014-10-15 00:00:00', 'YYYY-MM-DD HH24:MI:SS'));

insert into test values (6,1,TO_DATE(' 2014-06-1 00:00:00', 'YYYY-MM-DD HH24:MI:SS'));
insert into test values (7,2,TO_DATE(' 2014-06-1 00:00:00', 'YYYY-MM-DD HH24:MI:SS'));
insert into test values (8,3,TO_DATE(' 2014-06-1 00:00:00', 'YYYY-MM-DD HH24:MI:SS'));
insert into test values (9,4,TO_DATE(' 2014-06-1 00:00:00', 'YYYY-MM-DD HH24:MI:SS'));
insert into test values (10,5,TO_DATE(' 2014-06-1 00:00:00', 'YYYY-MM-DD HH24:MI:SS'));

select * from user_tab_partitions a where a.table_name='TEST';显示2个分区。
2、查看分区的数据

select * from TEST partition(test1); 显示分区1的5条数据

0 0