转:分区表与分区索引（一）：范围分区表的操作

--0.版本及环境

SQL> conn / as sysdba

已连接。

SQL> create tablespace tp_p1 datafile 'd:\oracle\oradata\test\tp_p1.dbf' size 10m

   2 autoextend on next 1m maxsize 100m

   3 extent management local

   4 segment space management auto;

 

表空间已创建。

 

SQL> create tablespace tp_p2 datafile 'd:\oracle\oradata\test\tp_p2.dbf' size 10m

   2 autoextend on next 1m maxsize 100m

   3 extent management local

   4 segment space management auto;

 

表空间已创建。

 

SQL> select * from v$version;

 

BANNER
----------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE    9.2.0.1.0       Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production

 

SQL> conn test/test

已连接。

SQL> show user
USER 为"TEST"

 

SQL> select USERNAME, DEFAULT_TABLESPACE from user_users where username='TEST';

 

USERNAME                       DEFAULT_TABLESPACE
------------------------------ ------------------------------
TEST                              TP_TEST

--1.表不指定表空间，则为用户默认表空间；分区不指定表空间，则默认为表所在的表空间
SQL> create table testrp(a number(10), b varchar2(10))
  2  partition by range(a)(
  3  partition testrp1 values less than (51),
  4  partition testrp2 values less than (maxvalue)
  5  );

 

表已创建。

 

SQL> select SEGMENT_NAME, PARTITION_NAME, SEGMENT_TYPE, TABLESPACE_NAME 
  2  from user_segments where segment_name = 'TESTRP';

 

SEGMENT_NAME  PARTITION_NAME         SEGMENT_TYPE       TABLESPACE_NAME
------------- ------------------------------ ------------------ --------
TESTRP        TESTRP1                TABLE PARTITION    TP_TEST
TESTRP        TESTRP2                TABLE PARTITION    TP_TEST

 

--2.表可以指定自己的表空间，分区仍是表所在的表空间
SQL> create table testrp(a number(10), b varchar2(10))
  2  tablespace tp_p1
  3  partition by range(a)(
  4  partition testrp1 values less than (51),
  5  partition testrp2 values less than (maxvalue)
  6  );

 

表已创建。

 

SQL> select SEGMENT_NAME, PARTITION_NAME, SEGMENT_TYPE, TABLESPACE_NAME
  2  from user_segments where segment_name = 'TESTRP';

 

SEGMENT_NAME  PARTITION_NAME         SEGMENT_TYPE       TABLESPACE_NAME
------------- ------------------------------ ------------------ -----------
TESTRP        TESTRP2                TABLE PARTITION    TP_P1
TESTRP        TESTRP1                TABLE PARTITION    TP_P1

 

--3.分区可以指定自己的表空间

SQL> drop table testrp;

 

表已丢弃。

 

SQL> create table testrp(a number(10), b varchar2(10))
  2  tablespace tp_p1
  3  partition by range(a)(
  4  partition testrp1 values less than (51),
  5  partition testrp2 values less than (maxvalue) tablespace tp_p2
  6  );

 

表已创建。

 

SQL> select SEGMENT_NAME, PARTITION_NAME, SEGMENT_TYPE, TABLESPACE_NAME
  2  from user_segments where segment_name = 'TESTRP';

 

SEGMENT_NAME  PARTITION_NAME         SEGMENT_TYPE       TABLESPACE_NAME
------------- ------------------------------ ------------------ -----------
TESTRP        TESTRP1                TABLE PARTITION    TP_P1
TESTRP        TESTRP2                TABLE PARTITION    TP_P2

 

--4.可以移动单个分区到指定表空间

SQL> alter table testrp move partition testrp2 tablespace tp_p2;

 

表已更改。

 

SQL> select SEGMENT_NAME, PARTITION_NAME, SEGMENT_TYPE, TABLESPACE_NAME
  2  from user_segments where segment_name = 'TESTRP';

 

SEGMENT_NAME  PARTITION_NAME         SEGMENT_TYPE       TABLESPACE_NAME
------------- ------------------------------ ------------------ -------
TESTRP        TESTRP2                TABLE PARTITION    TP_P2
TESTRP        TESTRP1                TABLE PARTITION    TP_P1

 

--5.分区名称可以修改

SQL> alter table testrp rename partition testrp1 to testrp01;

 

表已更改。

 

SQL> alter table testrp rename partition testrp2 to testrp02;

 

表已更改。

 

SQL> select SEGMENT_NAME, PARTITION_NAME, SEGMENT_TYPE, TABLESPACE_NAME
  2  from user_segments where segment_name = 'TESTRP';

 

SEGMENT_NAME PARTITION_NAME         SEGMENT_TYPE       TABLESPACE_NAME
------------ ------------------------------ ------------------ --------
TESTRP       TESTRP02               TABLE PARTITION    TP_P2
TESTRP       TESTRP01               TABLE PARTITION    TP_P1

 

--6.可以添加分区，但添加的分区的条件必须大于现有分区的最大值，否则报错

SQL> create tablespace tp_p3 datafile 'd:\oracle\oradata\test\tp_p3.dbf' size 10m
  2  autoextend off
  3  extent management local
  4  segment space management auto;

 

表空间已创建。

 

SQL> alter table testrp add partition testrp03 values less than (100) tablespace tp_p3;
alter table testrp add partition testrp03 values less than (100) tablespace tp_p3
                                 *
ERROR 位于第 1 行:
ORA-14074: 分区界限必须调整为高于最后一个分区界限

 

SQL> drop table testrp;

 

表已丢弃。

 

SQL> create table testrp(a number(10), b varchar2(10))
  2  partition by range(a)(
  3  partition testrp1 values less than (51),
  4  partition testrp2 values less than (81)
  5  ) tablespace tp_p1;

 

表已创建。

 

SQL> alter table testrp add partition testrp03 values less than (100) tablespace tp_p3;

 

表已更改。

 

SQL> alter table testrp add partition testrp04 values less than (maxvalue) tablespace tp_p3;

 

表已更改。

--7.可以合并分区

--7.1将小值分区合并到maxvalue分区中

SQL> alter table testrp merge partitions testrp03, testrp04 into partition testrp04;

 

表已更改。

 

SQL> set long 10000
SQL> set wrap on
SQL> select dbms_metadata.get_ddl('TABLE', 'TESTRP') from dual;

 

DBMS_METADATA.GET_DDL('TABLE','TESTRP')
--------------------------------------------------------------------------------

 

  CREATE TABLE "TEST"."TESTRP"
   (    "A" NUMBER(10,0),
        "B" VARCHAR2(10)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  STORAGE(
  BUFFER_POOL DEFAULT)
  TABLESPACE "TP_P1"
  PARTITION BY RANGE ("A")
 (PARTITION "TESTRP1"  VALUES LESS THAN (51)
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

 

DBMS_METADATA.GET_DDL('TABLE','TESTRP')
--------------------------------------------------------------------------------
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "TP_P1" NOCOMPRESS ,
 PARTITION "TESTRP2"  VALUES LESS THAN (81)
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "TP_P1" NOCOMPRESS ,
 PARTITION "TESTRP04"  VALUES LESS THAN (MAXVALUE)
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

 

DBMS_METADATA.GET_DDL('TABLE','TESTRP')
--------------------------------------------------------------------------------
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "TP_P1" NOCOMPRESS )

 

--7.2将maxvalue分区并到小值分区中：失败！

SQL> alter table testrp drop partition testrp04;

 

表已更改。

 

SQL> alter table testrp add partition testrp03 values less than (100) tablespace tp_p3;

 

表已更改。

 

SQL> alter table testrp add partition testrp04 values less than (maxvalue) tablespace tp_p3;

 

表已更改。

SQL> alter table testrp merge partitions testrp03,testrp04 into partition testrp03;
alter table testrp merge partitions testrp03,testrp04 into partition testrp03
                                    *
ERROR 位于第 1 行:
ORA-14275: 不能将下界分区作为结果分区重用

 

--7.3将大值分区合并到小值分区中：失败！

SQL> alter table testrp drop partition testrp04;

 

表已更改。

SQL> alter table testrp add partition testrp04 values less than (200) tablespace tp_p3;

 

表已更改。

 

SQL> alter table testrp merge partitions testrp03,testrp04 into partition testrp03;

alter table testrp merge partitions testrp03,testrp04 into partition testrp03
                                    *
ERROR 位于第 1 行:
ORA-14275: 不能将下界分区作为结果分区重用

 

--7.4但可以将两个分区合并到一个新的分区中

SQL> alter table testrp merge partitions testrp03,testrp04 into partition test03;

 

表已更改。

 

SQL> select dbms_metadata.get_ddl('TABLE','TESTRP') from dual;

 

DBMS_METADATA.GET_DDL('TABLE','TESTRP')
--------------------------------------------------------------------------------

 

  CREATE TABLE "TEST"."TESTRP"
   (    "A" NUMBER(10,0),
        "B" VARCHAR2(10)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  STORAGE(
  BUFFER_POOL DEFAULT)
  TABLESPACE "TP_P1"
  PARTITION BY RANGE ("A")
 (PARTITION "TESTRP1"  VALUES LESS THAN (51)
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

 

DBMS_METADATA.GET_DDL('TABLE','TESTRP')
--------------------------------------------------------------------------------
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "TP_P1" NOCOMPRESS ,
 PARTITION "TESTRP2"  VALUES LESS THAN (81)
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "TP_P1" NOCOMPRESS ,
 PARTITION "TEST03"  VALUES LESS THAN (200)
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

 

DBMS_METADATA.GET_DDL('TABLE','TESTRP')
--------------------------------------------------------------------------------
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "TP_P1" NOCOMPRESS )

 

--此时是3个段

SQL> set wrap off
SQL> select SEGMENT_NAME, PARTITION_NAME, SEGMENT_TYPE, TABLESPACE_NAME
  2  from user_segments where segment_name='TESTRP';

 

SEGMENT_NAME PARTITION_NAME         SEGMENT_TYPE       TABLESPACE_NAME
------------------------------------------- ------------------ --------
TESTRP       TEST03                 TABLE PARTITION    TP_P1
TESTRP       TESTRP1                TABLE PARTITION    TP_P1
TESTRP       TESTRP2                TABLE PARTITION    TP_P1

 

--7.4将小结果集合并到大结果集：成功，合并后的结果集仍是大结果集

--增加的新分区值必须大于目前最大的分区值

SQL> alter table testrp add partition testrp04 values less than (30) tablespace tp_p3;
alter table testrp add partition testrp04 values less than (30) tablespace tp_p3
                                 *
ERROR 位于第 1 行:
ORA-14074: 分区界限必须调整为高于最后一个分区界限

 

SQL> alter table testrp add partition testrp04 values less than (300) tablespace tp_p3;

 

表已更改。

 

SQL> alter table testrp merge partitions test03,testrp04 into partition testrp04;

 

表已更改。

 

SQL> set wrap on
SQL> select dbms_metadata.get_ddl('TABLE', 'TESTRP') from dual;

 

DBMS_METADATA.GET_DDL('TABLE','TESTRP')
--------------------------------------------------------------------------------

 

  CREATE TABLE "TEST"."TESTRP"
   (    "A" NUMBER(10,0),
        "B" VARCHAR2(10)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  STORAGE(
  BUFFER_POOL DEFAULT)
  TABLESPACE "TP_P1"
  PARTITION BY RANGE ("A")
 (PARTITION "TESTRP1"  VALUES LESS THAN (51)
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

 

DBMS_METADATA.GET_DDL('TABLE','TESTRP')
--------------------------------------------------------------------------------
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "TP_P1" NOCOMPRESS ,
 PARTITION "TESTRP2"  VALUES LESS THAN (81)
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "TP_P1" NOCOMPRESS ,
 PARTITION "TESTRP04"  VALUES LESS THAN (300)
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

 

DBMS_METADATA.GET_DDL('TABLE','TESTRP')
--------------------------------------------------------------------------------
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "TP_P1" NOCOMPRESS )

 

 

--8.还可以分裂分区，我们把testrp04按照100裂开成testrp3和testrp4

SQL> alter table testrp split partition testrp04 at (100) into
  2  (
  3  partition testrp3 tablespace tp_p3,
  4  partition testrp4 tablespace tp_p3
  5  );

 

表已更改。

 

SQL> select SEGMENT_NAME, PARTITION_NAME, SEGMENT_TYPE, TABLESPACE_NAME
  2  from user_segments where segment_name='TESTRP';

 

SEGMENT_NAME  PARTITION_NAME  SEGMENT_TYPE       TABLESPACE_NAME
------------- ----------------------- ------------------ ----------------
TESTRP        TESTRP3         TABLE PARTITION    TP_P3
TESTRP        TESTRP1         TABLE PARTITION    TP_P1
TESTRP        TESTRP2         TABLE PARTITION    TP_P1
TESTRP        TESTRP4         TABLE PARTITION    TP_P3

 

SQL> set wrap on
SQL> select dbms_metadata.get_ddl('TABLE', 'TESTRP') from dual;

 

DBMS_METADATA.GET_DDL('TABLE','TESTRP')
-----------------------------------------------------

 

  CREATE TABLE "TEST"."TESTRP"
   (    "A" NUMBER(10,0),
        "B" VARCHAR2(10)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  STORAGE(
  BUFFER_POOL DEFAULT)
  TABLESPACE "TP_P1"
  PARTITION BY RANGE ("A")
 (PARTITION "TESTRP1"  VALUES LESS THAN (51)
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

 

DBMS_METADATA.GET_DDL('TABLE','TESTRP')
--------------------------------------------------------------------------------
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "TP_P1" NOCOMPRESS ,
 PARTITION "TESTRP2"  VALUES LESS THAN (81)
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "TP_P1" NOCOMPRESS ,
 PARTITION "TESTRP3"  VALUES LESS THAN (100)
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

 

DBMS_METADATA.GET_DDL('TABLE','TESTRP')
--------------------------------------------------------------------------------
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "TP_P3" NOCOMPRESS ,
 PARTITION "TESTRP4"  VALUES LESS THAN (300)
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "TP_P3" NOCOMPRESS )

 

--8.1我们看指定错误的分裂上限会这么样。分区testrp3的范围是[81, 100)

SQL> alter table testrp split partition testrp3 at (101) into(
  2  partition testrp31 tablespace tp_p3,
  3  partition testrp32 tablespace tp_p3
  4  );
alter table testrp split partition testrp3 at (101) into(
                                   *
ERROR 位于第 1 行:
ORA-14080: 无法按指定的上限来分割分区

 

SQL> alter table testrp split partition testrp3 at (80) into(
  2  partition testrp31 tablespace tp_p3,
  3  partition testrp32 tablespace tp_p3
  4  );
alter table testrp split partition testrp3 at (80) into(
                                   *
ERROR 位于第 1 行:
ORA-14080: 无法按指定的上限来分割分区

 

--8.2我们增加一个maxvalue的分区，然后将它分裂
SQL> alter table testrp add partition testrp5 values less than (maxvalue) tablespace tp_p3;

 

表已更改。

 

SQL> alter table testrp split partition testrp5 at (400) into(
  2  partition testrp5 tablespace tp_p3,
  3  partition testrp6 tablespace tp_p3
  4  );

 

表已更改。

SQL> select dbms_metadata.get_ddl('TABLE', 'TESTRP') from dual;

 

DBMS_METADATA.GET_DDL('TABLE','TESTRP')
---------------------------------------------------------------------------

 

  CREATE TABLE "TEST"."TESTRP"
   (    "A" NUMBER(10,0),
        "B" VARCHAR2(10)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  STORAGE(
  BUFFER_POOL DEFAULT)
  TABLESPACE "TP_P1"
  PARTITION BY RANGE ("A")
 (PARTITION "TESTRP1"  VALUES LESS THAN (51)
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

 

DBMS_METADATA.GET_DDL('TABLE','TESTRP')
---------------------------------------------------------------------------
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "TP_P1" NOCOMPRESS ,
 PARTITION "TESTRP2"  VALUES LESS THAN (81)
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "TP_P1" NOCOMPRESS ,
 PARTITION "TESTRP3"  VALUES LESS THAN (100)
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

 

DBMS_METADATA.GET_DDL('TABLE','TESTRP')
---------------------------------------------------------------------------
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "TP_P3" NOCOMPRESS ,
 PARTITION "TESTRP4"  VALUES LESS THAN (300)
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "TP_P3" NOCOMPRESS ,
 PARTITION "TESTRP5"  VALUES LESS THAN (400)
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)

 

DBMS_METADATA.GET_DDL('TABLE','TESTRP')
---------------------------------------------------------------------------
  TABLESPACE "TP_P3" NOCOMPRESS ,
 PARTITION "TESTRP6"  VALUES LESS THAN (MAXVALUE)
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "TP_P3" NOCOMPRESS )

 

--9.其他：分区表可以不指定分区名称，oracle会自动命名，不过还是建议手工命名。

SQL> drop table testrp;

 

表已丢弃。

 

SQL> create table testrp(a number(10), b varchar2(10))
  2  partition by range(a)(
  3  partition values less than (51),
  4  partition values less than (maxvalue)
  5  );

 

表已创建。

 

SQL> select SEGMENT_NAME, PARTITION_NAME, SEGMENT_TYPE, TABLESPACE_NAME
  2  from user_segments where segment_name = 'TESTRP';

 

SEGMENT_NAME PARTITION_NAME         SEGMENT_TYPE       TABLESPACE_NAME
------------------------------------------- ------------------ -------
TESTRP       SYS_P1                 TABLE PARTITION    TP_TEST
TESTRP       SYS_P2                 TABLE PARTITION    TP_TEST

 

SQL> create table testrp2(a number(10), b varchar2(10))
  2  partition by range(a)(
  3  partition values less than (51),
  4  partition values less than (maxvalue)
  5  );

 

表已创建。

 

SQL> select SEGMENT_NAME, PARTITION_NAME, SEGMENT_TYPE, TABLESPACE_NAME
  2  from user_segments where segment_name = 'TESTRP2';

 

SEGMENT_NAME  PARTITION_NAME         SEGMENT_TYPE       TABLESPACE_NAME
------------- ------------------------------ ------------------ -------
TESTRP2       SYS_P4                 TABLE PARTITION    TP_TEST
TESTRP2       SYS_P3                 TABLE PARTITION    TP_TEST

 

SQL> drop table testrp2;

 

表已丢弃。

 

SQL> drop table testrp;

 

表已丢弃。

 

SQL>