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.檢查，老的表暫時保留

檢查資料是否完整，無誤則開啟應用。保險起見老的資料保留一個月，確定無誤後將表刪除，釋放空間。