sqlplus / as sysdba
create tablespace PAR;
create user par identified by par;
grant dba to par;
創(chuàng)建測試表：
sqlplus par/par
create table lucifer(id number(8) PRIMARY KEY,
name varchar2(20) not null,
par_date date)
tablespace PAR;
comment on table lucifer is 'lucifer表';
comment on column lucifer.name is '姓名';
comment on column lucifer.par_date is '分區(qū)日期';
create index id_name on lucifer(name) tablespace par;
插入測試數(shù)據(jù)：
sqlplus par/par
begin
foriin0..24 loop
insert into lucifer values
(i,
'lcuifer_'|| i,
add_months(to_date('2021-1-1', 'yyyy-mm-dd'), i));
end loop;
commit;
end;
/
sqlplus / as sysdba
##查看主鍵select cu.* from user_cons_columns cu, user_constraints au where cu.constraint_name = au.constraint_name and au.constraint_type ='P' and au.table_name ='LUCIFER';
確認是否可以重定義，沒有主鍵用 rowid：
sqlplus / as sysdba
exec dbms_redefinition.can_redef_table('PAR', 'LUCIFER');
select row_movement from dba_tables where table_name='LUCIFER' and owner='PAR';select row_movement from dba_tables where table_name='LUCIFER_PAR' and owner='PAR';
5、收集表統(tǒng)計信息
為了確保數(shù)據(jù)準(zhǔn)確，開始前進行統(tǒng)計信息收集：
sqlplus / as sysdba
exec dbms_stats.gather_table_stats(ownname =>'PAR',tabname =>'LUCIFER',estimate_percent =>10,method_opt=>'for all indexed columns',cascade=>TRUE,degree =>'8');exec dbms_stats.gather_table_stats(ownname =>'PAR',tabname =>'LUCIFER_PAR',estimate_percent =>10,method_opt=>'for all indexed columns',cascade=>TRUE,degree =>'8');
6、開始在線重定義
sqlplus / as sysdba
EXEC DBMS_REDEFINITION.START_REDEF_TABLE('PAR','LUCIFER','LUCIFER_PAR');
sqlplus / as sysdba
ALTER index PAR.ID_NAME_PAR RENAME TO ID_NAME;
15、查看是否存在無效索引
sqlplus / as sysdba
SELECT owner index_owner, index_name, index_type,'N/A' partition_name,status,table_name,tablespace_name,
'alter index '||owner||'.'||index_name||' rebuild;' rebuild_index
FROM dba_indexes
WHERE status ='UNUSABLE'
UNION ALL
SELECT a.index_owner,a.index_name,b.index_type,a.partition_name,a.status,b.table_name,a.tablespace_name,
'alter index '||a.index_owner||'.'||a.index_name||' rebuild partition '||a.partition_name||' ;' rebuild_index
FROM dba_ind_partitions a, dba_indexes b
WHERE a.index_name = b.index_name
AND a.index_owner = b.owner
AND a.status ='UNUSABLE'
UNION ALL
SELECT owner index_owner,a.index_name,b.index_type,'N/A' partition_name,a.status,b.table_name,NULL,
'alter index '||a.index_owner||'.'||a.index_name||' rebuild subpartition '||a.subpartition_name||';' rebuild_index
FROM dba_ind_subpartitions a, dba_indexes b
WHERE a.index_name = b.index_name
AND a.index_owner = b.owner
AND a.status ='UNUSABLE';
16、檢查切換后是否開啟row_movement
sqlplus / as sysdba
select owner,table_name,row_movement from dba_tables where table_name in('LUCIFER','LUCIFER_PAR') and owner='PAR';
17、檢查無效對象
##無效對象編譯
sqlplus / as sysdba
@?/rdbms/admin/utlrp.sql
select'alter '||object_type||' '||owner||'.'||object_name||' compile;'
from dba_objects t
where t.status ='INVALID' order by 1;
18、收集統(tǒng)計信息
sqlplus / as sysdba
exec dbms_stats.gather_table_stats(ownname =>'PAR',tabname =>'LUCIFER',estimate_percent =>10,method_opt=>'for all indexed columns',cascade=>TRUE,degree =>'8');
19、插入測試數(shù)據(jù)
sqlplus par/par
begin
foriin100..124 loop
insert into lucifer values
(i,
'lcuifer_'|| i,
add_months(to_date('2021-5-1', 'yyyy-mm-dd'), i));
end loop;
commit;
end;
/
20、查詢分區(qū)表數(shù)據(jù)分布
sqlplus par/par
SELECT COUNT(*) FROM LUCIFER;
SELECT * FROM LUCIFER PARTITION(LUCIFER_P202101);
SELECT * FROM LUCIFER PARTITION(LUCIFER_P202201);
SELECT * FROM LUCIFER PARTITION(LUCIFER_MAX);
