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.检查，老的表暂时保留
检查数据是否完整，无误则开启应用。保险起见老的数据保留一个月，确定无误后将表删除，释放空间。
