Oracle表分区

分区表

创建分区表

create table test(
id number,
name char(30),
systemtime char(12))
partition by range (systemtime)(
partition test202201 values less than ('202202010000'),
partition test202202 values less than ('202203010000'),
partition test202203 values less than ('202204010000')
);

查看分区表信息

col table_owner for a5
col table_name for a5
col partition_name for a10
select table_owner,table_name,partition_name,tablespace_name from dba_tab_partitions where table_name = 'TEST';

插入数据

insert into test values (1,'name1','202201300000');
insert into test values (2,'name2','202202280000');
insert into test values (3,'name2','202203300000');
COMMIT;

查看数据分布

select count(*) from test;
select count(*) from test partition(test202201);
select count(*) from test partition(test202202);
select count(*) from test partition(test202203);

添加分区

alter table test add partition test202204 values less than ('202205010000');

删除分区

alter table test drop partition test202204;

截断分区

alter table test truncate partition test202203;

锁定分区

12C以后，数据库支持设置分区只读

alter table test modify partition test202203 read only;

查看锁定情况

set linesize 100
column table_name format a30
column partition_name format a30
column read_only format a9
select table_name, partition_name,read_only from user_tab_partitions where  table_name = 'TEST' order by 1, 2;

12C以前需要使用lock命令
当前会话不断开，其他会话不能向当前分区写数据

lock table test partition (test202203) in exclusive mode;

查看锁定情况

col owner for a10
col object_name for a10
select b.owner,b.object_name,a.session_id,a.locked_mode from v$locked_object a,dba_objects b where b.object_id = a.object_id;

索引

索引创建

线上创建索引，加online没错

create index idx_test_01 on test(id) local online;

索引分区信息

col index_owner for a10
col index_name for a20
col partition_name for a20
select index_owner,index_name,partition_name,tablespace_name from dba_ind_partitions where index_name='IDX_TEST_01';

索引重建

alter index idx_test_01 rebuild partition TEST202201 online;

统计信息

exec dbms_stats.gather_table_stats(ownname => 'TEST',tabname => 'TEST', partname => 'TEST202201',estimate_percent => 100,method_opt=> 'for all indexed columns',cascade=>TRUE,granularity=>'ALL');

导出导入

exp/imp

exp test/test file='/home/oracle/dmp/test_test202201.dmp' log='/home/oracle/dmp/test_test202201.log' tables=test:test202201 statistics=none

导入前需要确保有对应的分区

alter table test add partition test202203 values less than ('202203010000');

imp test/test file='/home/oracle/dmp/test_test202201.dmp' log='/home/oracle/dmp/test_test202201.log' tables=test:test202201 ignore=y

expdp/impdp

多个分区tables=“(test:test202201,test:test202202)”

expdp test/test directory=DATA_DIR dumpfile=test_test202201.dmp logfile=test_test202201.log tables=test:test202201

导入前需要确保有对应的分区

alter table test add partition test202203 values less than ('202203010000');

impdp test/test directory=DATA_DIR dumpfile=test_test202203.dmp logfile=test_test202203.log table_exists_action=append