oracle 索引表空间分区,ORACLE 12.2 分区梳理之02-(分区表及索引表空间确定)

ORACLE 12.2 分区梳理之02-(分区表及索引表空间确定)

创建表空间

[oracle@xag182 ~]$ sql xag1/123456@127.0.0.1:1521/PDB1

SQL> SET SQLFORMAT ansiconsole

#如下为2018创建案例，本次测试还需要创建2019、2020，创建方法同创建2018空间的如下脚本

create tablespace D_XAG2018 datafile

'/u02/oradata/MPAPEX/pdb1/D_XAG20181.dbf' size 16M AUTOEXTEND ON NEXT 16M MAXSIZE UNLIMITED,

'/u02/oradata/MPAPEX/pdb1/D_XAG20182.dbf' size 16M AUTOEXTEND ON NEXT 16M MAXSIZE UNLIMITED

default storage (initial 128K next 2M pctincrease 0);

Create tablespace I_XAG2018 datafile

'/u02/oradata/MPAPEX/pdb1/I_XAG20181.dbf' size 16M AUTOEXTEND ON NEXT 16M MAXSIZE UNLIMITED,

'/u02/oradata/MPAPEX/pdb1/I_XAG20182.dbf' size 16M AUTOEXTEND ON NEXT 16M MAXSIZE UNLIMITED

default storage (initial 128K next 2M pctincrease 0);

create tablespace D_XAG2018_SUBA datafile

'/u02/oradata/MPAPEX/pdb1/D_XAG2018_SUBA1.dbf' size 16M AUTOEXTEND ON NEXT 16M MAXSIZE UNLIMITED,

'/u02/oradata/MPAPEX/pdb1/D_XAG2018_SUBA2.dbf' size 16M AUTOEXTEND ON NEXT 16M MAXSIZE UNLIMITED

default storage (initial 128K next 2M pctincrease 0);

Create tablespace I_XAG2018_SUBA datafile

'/u02/oradata/MPAPEX/pdb1/I_XAG2018_SUBA1.dbf' size 16M AUTOEXTEND ON NEXT 16M MAXSIZE UNLIMITED,

'/u02/oradata/MPAPEX/pdb1/I_XAG2018_SUBA2.dbf' size 16M AUTOEXTEND ON NEXT 16M MAXSIZE UNLIMITED

default storage (initial 128K next 2M pctincrease 0);

create tablespace D_XAG2018_SUBB datafile

'/u02/oradata/MPAPEX/pdb1/D_XAG2018_SUBB1.dbf' size 16M AUTOEXTEND ON NEXT 16M MAXSIZE UNLIMITED,

'/u02/oradata/MPAPEX/pdb1/D_XAG2018_SUBB2.dbf' size 16M AUTOEXTEND ON NEXT 16M MAXSIZE UNLIMITED

default storage (initial 128K next 2M pctincrease 0);

Create tablespace I_XAG2018_SUBB datafile

'/u02/oradata/MPAPEX/pdb1/I_XAG2018_SUBB1.dbf' size 16M AUTOEXTEND ON NEXT 16M MAXSIZE UNLIMITED,

'/u02/oradata/MPAPEX/pdb1/I_XAG2018_SUBB2.dbf' size 16M AUTOEXTEND ON NEXT 16M MAXSIZE UNLIMITED

default storage (initial 128K next 2M pctincrease 0);

创建普通表(未指定表空间)

#不指定表空间时 表及索引都使用默认的用户表空间 D_XAG

CREATE TABLE test_tab

(

tab_seq number not null,

tab_no varchar2(20),

tab_name varchar2(20),

tab_type varchar2(1), -- A,B

tab_date date

);

alter table test_tab add constraint pk_test_tab primary key (tab_seq) using index;

create index idx_test_tab_1 on test_tab(tab_no);

SQL> select t.table_name,t.tablespace_name,t.status,t.partitioned from tabs t;

TABLE_NAME TABLESPACE_NAME STATUS PARTITIONED

TEST_TAB D_XAG VALID NO

SQL> select b.index_name,b.index_type,b.table_name,b.uniqueness,b.tablespace_name,b.status,b.partitioned,b.global_stats from user_indexes b;

INDEX_NAME INDEX_TYPE TABLE_NAME UNIQUENESS TABLESPACE_NAME STATUS PARTITIONED GLOBAL_STATS

PK_TEST_TAB NORMAL TEST_TAB UNIQUE D_XAG VALID NO YES

IDX_TEST_TAB_1 NORMAL TEST_TAB NONUNIQUE D_XAG VALID NO YES

创建普通表(指定表空间)

CREATE TABLE test_tab

(

tab_seq number not null,

tab_no varchar2(20),

tab_name varchar2(20),

tab_type varchar2(1), -- A,B

tab_date date

) tablespace D_XAG;

alter table test_tab add constraint pk_test_tab primary key (tab_seq) using index tablespace I_XAG;

create index idx_test_tab_1 on test_tab(tab_no) tablespace I_XAG;

SQL> select t.table_name,t.tablespace_name,t.status,t.partitioned from tabs t;

TABLE_NAME TABLESPACE_NAME STATUS PARTITIONED

TEST_TAB D_XAG VALID NO

SQL> select b.index_name,b.index_type,b.table_name,b.uniqueness,b.tablespace_name,b.status,b.partitioned,b.global_stats from user_indexes b;

INDEX_NAME INDEX_TYPE TABLE_NAME UNIQUENESS TABLESPACE_NAME STATUS PARTITIONED GLOBAL_STATS

PK_TEST_TAB NORMAL TEST_TAB UNIQUE I_XAG VALID NO YES

IDX_TEST_TAB_1 NORMAL TEST_TAB NONUNIQUE I_XAG VALID NO YES

创建range 分区(不指定表空间)

#分区表的分区增加分区假如不指定表空间，会根据分区表的默认表空间，假如分区表没有指定默认表空间，则建立在用户的默认表空间上

CREATE TABLE test_range_tab

(

tab_seq number not null,

tab_no varchar2(20),

tab_name varchar2(20),

tab_type varchar2(1),

tab_date date

)partition by range (tab_date)

(

partition P2018 values less than (TO_DATE('2019-01-01', 'YYYY-MM-DD')),

partition P2019 values less than (TO_DATE('2020-01-01', 'YYYY-MM-DD'))

);

alter table test_range_tab

add constraint pk_test_range_tab primary key (tab_date,tab_seq)

using index local;

create index ind_test_range_tab_1 on test_range_tab(tab_date,tab_no)

local;

SQL> select t.table_name,t.tablespace_name,t.status,t.partitioned from tabs t where t.table_name in('TEST_RANGE_TAB');

TABLE_NAME TABLESPACE_NAME STATUS PARTITIONED

TEST_RANGE_TAB VALID YES

SQL> select b.index_name,b.index_type,b.table_name,b.uniqueness,b.tablespace_name,b.status,b.partitioned,b.global_stats from user_indexes b where b.table_name in('TEST_RANGE_TAB');

INDEX_NAME INDEX_TYPE TABLE_NAME UNIQUENESS TABLESPACE_NAME STATUS PARTITIONED GLOBAL_STATS

PK_TEST_RANGE_TAB NORMAL TEST_RANGE_TAB UNIQUE N/A YES NO

IND_TEST_RANGE_TAB_1 NORMAL TEST_RANGE_TAB NONUNIQUE N/A YES NO

SQL> SELECT TABLE_NAME, A.INDEX_NAME, A.TABLESPACE_NAME,a.partition_name ,a.partition_position

FROM USER_IND_PARTITIONS A, USER_INDEXES B

WHERE A.INDEX_NAME = B.INDEX_NAME

and b.table_name in('TEST_RANGE_TAB')

ORDER BY 4;

TABLE_NAME INDEX_NAME TABLESPACE_NAME PARTITION_NAME PARTITION_POSITION

TEST_RANGE_TAB PK_TEST_RANGE_TAB D_XAG P2018 1

TEST_RANGE_TAB IND_TEST_RANGE_TAB_1 D_XAG P2018 1

TEST_RANGE_TAB PK_TEST_RANGE_TAB D_XAG P2019 2

TEST_RANGE_TAB IND_TEST_RANGE_TAB_1 D_XAG P2019 2

SQL> select b.table_name,b.partition_name,b.tablespace_name,b.partition_position from USER_TAB_PARTITIONS b

where b.table_name in ('TEST_RANGE_TAB')

ORDER BY 4;

TABLE_NAME PARTITION_NAME TABLESPACE_NAME PARTITION_POSITION

TEST_RANGE_TAB P2018 D_XAG 1

TEST_RANGE_TAB P2019 D_XAG 2

创建range 分区(指定表空间)

drop TABLE test_range_tab;

CREATE TABLE test_range_tab

(

tab_seq number not null,

tab_no varchar2(20),

tab_name varchar2(20),

tab_type varchar2(1),

tab_date date

)partition by range (tab_date)

(

partition P2018 values less than (TO_DATE('2019-01-01', 'YYYY-MM-DD')) tablespace D_XAG2018,

partition P2019 values less than (TO_DATE('2020-01-01', 'YYYY-MM-DD')) tablespace D_XAG2019

);

alter table test_range_tab

add constraint pk_test_range_tab primary key (tab_date,tab_seq)

using index local

(

PARTITION P2018 TABLESPACE I_XAG2018,

PARTITION P2019 TABLESPACE I_XAG2019

);

create index ind_test_range_tab_1 on test_range_tab(tab_date,tab_no)

local

(

PARTITION P2018 TABLESPACE I_XAG2018,

PARTITION P2019 TABLESPACE I_XAG2019

);

SQL> SELECT TABLE_NAME, A.INDEX_NAME, A.TABLESPACE_NAME,a.partition_name ,a.partition_position

FROM USER_IND_PARTITIONS A, USER_INDEXES B

WHERE A.INDEX_NAME = B.INDEX_NAME

and b.table_name in('TEST_RANGE_TAB')

ORDER BY 4;

TABLE_NAME INDEX_NAME TABLESPACE_NAME PARTITION_NAME PARTITION_POSITION

TEST_RANGE_TAB PK_TEST_RANGE_TAB I_XAG2018 P2018 1

TEST_RANGE_TAB IND_TEST_RANGE_TAB_1 I_XAG2018 P2018 1

TEST_RANGE_TAB PK_TEST_RANGE_TAB I_XAG2019 P2019 2

TEST_RANGE_TAB IND_TEST_RANGE_TAB_1 I_XAG2019 P2019 2

SQL> select b.table_name,b.partition_name,b.tablespace_name,b.partition_position from USER_TAB_PARTITIONS b

where b.table_name in ('TEST_RANGE_TAB')

ORDER BY 4;

TABLE_NAME PARTITION_NAME TABLESPACE_NAME PARTITION_POSITION

TEST_RANGE_TAB P2018 D_XAG2018 1

TEST_RANGE_TAB P2019 D_XAG2019 2

drop table test_range_hast_tab;

CREATE TABLE test_range_hast_tab

(

tab_seq number not null,

tab_type varchar2(1),

tab_date date

) tablespace D_XAG2018

partition by range(tab_date)

subpartition by hash(tab_type)

SUBPARTITION TEMPLATE

(

SUBPARTITION PA TABLESPACE D_XAG2018_SUBA,

SUBPARTITION PB TABLESPACE D_XAG2018_SUBB

)

(

partition P2018 values less than (TO_DATE('2019-01-01', 'YYYY-MM-DD')) --此处不能指定表空间否则上面指定的子表空间会失效

);

alter table test_range_hast_tab

add constraint pk_test_range_hast_tab primary key (tab_date,tab_type,tab_seq)

using index local

(

PARTITION P2018 TABLESPACE I_XAG2018

(

SUBPARTITION P2018_PA TABLESPACE I_XAG2018_SUBA,

SUBPARTITION P2018_PB TABLESPACE I_XAG2018_SUBB

)

);

-----------------------------------------------------------------------------------------------------------------

ALTER TABLE test_range_hast_tab SET SUBPARTITION TEMPLATE

(

SUBPARTITION PA TABLESPACE D_XAG2019_SUBA,

SUBPARTITION PB TABLESPACE D_XAG2019_SUBB

);

#修改分区表的默认表空间：

alter table test_range_hast_tab modify default attributes tablespace D_XAG2019;

#修改该表上某个索引的默认表空间：

alter index pk_test_range_hast_tab modify default attributes tablespace I_XAG2019;

alter table test_range_hast_tab add partition P2019 values less than (TO_DATE('2020-01-01', 'YYYY-MM-DD'));

--1

select b.table_name,b.partition_name,b.tablespace_name,b.partition_position from USER_TAB_PARTITIONS b

where b.table_name=upper('test_range_hast_tab')

ORDER BY 4;

--2

select c.table_name,c.partition_name,c.subpartition_name,c.partition_position,c.subpartition_position,c.tablespace_name

from USER_TAB_SUBPARTITIONS c

where c.table_name=upper('test_range_hast_tab')

ORDER BY 4,5;

--3

SELECT TABLE_NAME, A.INDEX_NAME, A.TABLESPACE_NAME,a.partition_name ,a.partition_position

FROM USER_IND_PARTITIONS A, USER_INDEXES B

WHERE A.INDEX_NAME = B.INDEX_NAME

and A.INDEX_NAME=upper('pk_test_range_hast_tab')

ORDER BY 4;

--4.1

select d.index_name,d.partition_name,d.subpartition_name,d.tablespace_name

,d.partition_position,d.subpartition_position

from user_ind_subpartitions d where d.index_name=upper('pk_test_range_hast_tab')

order by 5,6

alter index pk_test_range_hast_tab rebuild subPARTITION P2019_PA TABLESPACE I_XAG2019_SUBA;

alter index pk_test_range_hast_tab rebuild subPARTITION P2019_PB TABLESPACE I_XAG2019_SUBB;

--4.2

select d.index_name,d.partition_name,d.subpartition_name,d.tablespace_name

,d.partition_position,d.subpartition_position,d.status

from user_ind_subpartitions d where d.index_name=upper('pk_test_range_hast_tab')

order by 5,6