oracle 普通表-分区表改造流程

1.先做备份（保险）

创建虚拟路径
create directory mydata as ‘/oracle/oradata/mydata’;

查看逻辑目录创建成功没
select * from dba_directories;

逻辑备份表
expdp ‘/ as sysdba’ directory=DMP tables=zqz.test dumpfile=test717.dmp logfile=test17.log

2.根据空间大小，表大小，判断是否需要加数据文件

查看表大小
select owner,SEGMENT_NAME,bytes/1024/1024 M from dba_segments where SEGMENT_TYPE=‘TABLE’ and owner=‘WLH’;

查看多张表大小
select owner,SEGMENT_NAME,bytes/1024/1024/1024 G from dba_segments where SEGMENT_TYPE=‘TABLE’ and owner=‘WLH’ and SEGMENT_NAME in (‘AC01’,‘AC02’,‘AC20’,‘AB01’,‘AB02’,‘AE10’,‘AC43’,‘AC82’);

查看表空间位置
Set linesize 300
Col file_name for a50
Select file_name,tablespace_name from dba_data_files;

表空间使用率
col tablespace_name format a8
col status format a7
col extent_management format a5
col segment_space_management format a6
col contents format a9
select tpsname,status,mgr,maxsize,c_userd,max_used from
(
SELECT d.tablespace_name tpsname,d.status status,
d.segment_space_management mgr, d.contents type,
TO_CHAR(NVL(trunc(A.maxbytes / 1024 / 1024), 0),‘99G999G990’) maxsize,
TO_CHAR(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0),‘990D00’) c_userd,
TO_CHAR(NVL((a.bytes - NVL(f.bytes, 0)) / a.maxbytes * 100, 0),‘990D00’) max_used
FROM sys.dba_tablespaces d,
(SELECT tablespace_name,sum(bytes) bytes,SUM(case autoextensible when ‘NO’ then BYTES when ‘YES’ then MAXBYTES else null end ) maxbytes
FROM dba_data_files GROUP BY tablespace_name) a,
(SELECT tablespace_name,SUM(bytes) bytes, MAX(bytes) largest_free FROM dba_free_space GROUP BY tablespace_name) f
WHERE d.tablespace_name = a.tablespace_name AND d.tablespace_name = f.tablespace_name(+)
)
where max_used>0
order by max_used desc;

添加文件
Alter tablespace XXX add datafile ‘XXX’ size 1g autoextend on

3.创建分区表，以及一些其他操作

创建分区表
create table wlh (mydate date,id int) partition by range (mydate)
(
PARTITION p_2019 VALUES LESS THAN (TO_DATE(‘2019-12-1’, ‘yyyy-mm-dd’)),
PARTITION p_2020 VALUES LESS THAN (TO_DATE(‘2020-12-1’, ‘yyyy-mm-dd’)),
PARTITION p_2021 VALUES LESS THAN (TO_DATE(‘2021-12-1’, ‘yyyy-mm-dd’)),
PARTITION p_2022 VALUES LESS THAN (TO_DATE(‘2022-12-1’, ‘yyyy-mm-dd’))
);

–创建主键
alter table test_part add constraint test_part_pk primary key (ID) using INDEX;

创建索引
create index test_part_create_time on TEST_PART (create_time);

插入
insert into sys.wlh values(to_date(‘1987-04-19 07:06:00’,‘yyyy-mm-dd hh24:mi:ss’),3);

查
select * from sys.wlh partition(p_2020);

Select count(*) from sys.wlh partition(p_2021);

4.插入数据

采用并行直接路径插入
insert /*+ append parallel(p,10) / into test_p p select /+ parallel(n,10) */ * from test n;

Eg.
insert /*+ append parallel(p,10) / into test_p p select /+ parallel(n,10) */ * from test n;

查询test中全部，插入test_p中，p和n是test_p和test的别名，parallel的并行度为10，append为insert快的参数。

append 增加数据的时候 不会检查HWM中是否有空闲块,会直接往HWM之上一个新块当中插入数据,所以一定要批量插入,要不然每一条数据就会增加一个新块 十分浪费空间.

5.替换表名

将原始表的表名更改成一个备份名字
SQL> alter table test rename to test_bak;

Table altered.

将新的分区表的表名更改成原始表名
SQL> alter table test_p rename to test;

Table altered.

6.创建需要的索引

添加索引，注意都为全局性质的

create index test_index on test(id);

7.检查，老的表暂时保留

检查数据是否完整，无误则开启应用。保险起见老的数据保留一个月，确定无误后将表删除，释放空间。