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

时间：2021-01-01 11:05:27
--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>
标签：

相关文章