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
