作者 | JiekeXu

来源 |公众号 JiekeXu DBA之路（ID: JiekeXu_IT）

如需转载请联系授权 | (个人微信 ID：JiekeXu_DBA)

大家好，我是 JiekeXu,很高兴又和大家见面了,今天和大家一起来看看 Oracle 在线重定义（中），欢迎点击上方蓝字“JiekeXu DBA之路”关注我的公众号，标星或置顶，更多干货第一时间到达！

目录
    在线重定义测试实践
        查看用户权限并赋权
        创建测试表
        创建 16k 大小表空间
        插入表数据
            检查碎片率
            表碎片整理
        检查是否可以进行在线重定义
        创建一个临时分区表 test.interim
        开始重新定义过程
        终止在线重定义
        复制依赖对象
        可选择同步临时表
        完成重新定义
        查看完成后的新表
        分区表相关视图
    测试环境普通表改分区表示例

在线重定义测试实践

前面一篇已经介绍过了《Oracle 在线重定义(中)》相关基础知识及示例,这里则是接着第一篇继续进行在线重定义的介绍。

————————————————————————————微信公众号：JiekeXu DBA之路墨天轮：https://www.modb.pro/u/4347CSDN ：https://blog.csdn.net/JiekeXu腾讯云：https://cloud.tencent.com/developer/user/5645107————————————————————————————

查看用户权限并赋权

查看用户所具有的角色

select *  from dba_role_privs where grantee='TEST';

查询角色包含哪些权限：

select *  from role_sys_privs where  role='&role';

查询用户系统权限：

select  *  from dba_sys_privs where  grantee='&username';GRAN PRIVILEGE                                ADM COM INH---- ---------------------------------------- --- --- ---TEST ALTER SYSTEM                             NO  NO  NOTEST SELECT ANY TABLE                         NO  NO  NOTEST UNLIMITED TABLESPACE                     NO  NO  NOTEST CREATE SESSION                           NO  NO  NO

查询用户具有的表权限：

col PRIVILEGE for a15col GRANTEE for a15 col OWNER for a15select  *  from dba_tab_privs where  grantee='TEST';GRANTEE         OWNER           TABLE_NAME                     GRANTOR              PRIVILEGE       GRA HIE COM TYPE                     INH--------------- --------------- ------------------------------ -------------------- --------------- --- --- --- ------------------------ ---TEST            SYS             DBA_HIST_SQLTEXT               SYS                  SELECT          NO  NO  NO  VIEW                     NO

用户在线重定义需要的权限：

GRANT EXECUTE_CATALOG_ROLE TO TEST;GRANT CREATE TABLE,CREATE MATERIALIZED VIEW TO TEST;GRANT CREATE ANY TABLE,ALTER ANY TABLE,DROP ANY TABLE,LOCK ANY TABLE,SELECT ANY TABLE TO TEST;GRANT CREATE ANY TRIGGER,CREATE ANY INDEX TO TEST;

创建测试表

CREATE TABLE test.original(    col1 NUMBER PRIMARY KEY,   col2 VARCHAR2(10),   col3 CLOB,   col4 DATE)ORGANIZATION INDEX;

创建 16k 大小表空间

alter system set db_16k_cache_size = 16m;select file_id,file_name,bytes/1024/1024,AUTOEXTENSIBLE from dba_data_files  where tablespace_name='USERS';CREATE TABLESPACE testredeftbs DATAFILE '/u01/app/oracle/oradata/TESTOGG/testredef01.dbf' SIZE 500M   EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO BLOCKSIZE 16384;

插入表数据

DECLARE  V_CLOB CLOB;BEGIN   FOR I IN 0..999 LOOP      V_CLOB := NULL;      FOR J IN 1..1000 LOOP         V_CLOB := V_CLOB||TO_CHAR(I,'0000');      END LOOP;      INSERT INTO test.original VALUES(I,TO_CHAR(I),V_CLOB,SYSDATE+I);      COMMIT;   END LOOP;   COMMIT;END;/

DELETE FROM test.original WHERE (COL1/3) <> TRUNC(COL1/3);
Commit;select * from test.original WHERE COL1<=2;

检查碎片率

下表 SPACE_USAGE 过程参数  https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_SPACE.html#GUID-1115B610-8956-426F-B615-9118225F911F

set serverout on size 1000000declarep_fs1_bytes number;p_fs2_bytes number;p_fs3_bytes number;p_fs4_bytes number;p_fs1_blocks number;p_fs2_blocks number;p_fs3_blocks number;p_fs4_blocks number;p_full_bytes number;p_full_blocks number;p_unformatted_bytes number;p_unformatted_blocks number;begindbms_space.space_usage(segment_owner => 'TEST',segment_name => 'ORIGINAL',segment_type => 'TABLE',fs1_bytes => p_fs1_bytes,fs1_blocks => p_fs1_blocks,fs2_bytes => p_fs2_bytes,fs2_blocks => p_fs2_blocks,fs3_bytes => p_fs3_bytes,fs3_blocks => p_fs3_blocks,fs4_bytes => p_fs4_bytes,fs4_blocks => p_fs4_blocks,full_bytes => p_full_bytes,full_blocks => p_full_blocks,unformatted_blocks => p_unformatted_blocks,unformatted_bytes => p_unformatted_bytes);dbms_output.put_line('FS1: blocks = '||p_fs1_blocks);dbms_output.put_line('FS2: blocks = '||p_fs2_blocks);dbms_output.put_line('FS3: blocks = '||p_fs3_blocks);dbms_output.put_line('FS4: blocks = '||p_fs4_blocks);dbms_output.put_line('Full blocks = '||p_full_blocks);end;/FS1: blocks = 0FS2: blocks = 2FS3: blocks = 0FS4: blocks = 0Full blocks = 11

FS1表明有 0 个数据块具有 0%-25% 的空闲空间，FS2 表明有 2 个数据块具有 25%-50% 的空闲空，间FS3 表明有 0 个数据块具有 50%-75% 的空闲空间，FS4 表明有 0 个数据块具有 75%-100% 繁荣空闲空间，FULL 表明有 11个满的数据块。

--分区表碎片查看col TABLE_OWNER for a30col TABLE_NAME for a30col PARTITION_NAME for a30col TABLESPACE_NAME for a30select TABLE_OWNER,TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,NUM_ROWS,LAST_ANALYZED from DBA_TAB_PARTITIONS where TABLE_OWNER='TEST';
set serveroutput ondeclarev_unformatted_blocks number;v_unformatted_bytes number;v_fs1_blocks number;v_fs1_bytes number;v_fs2_blocks number;v_fs2_bytes number;v_fs3_blocks number;v_fs3_bytes number;v_fs4_blocks number;v_fs4_bytes number;v_full_blocks number;v_full_bytes number;begindbms_space.space_usage ('TEST', 'ORIGINAL', 'TABLE PARTITION', v_unformatted_blocks,v_unformatted_bytes, v_fs1_blocks, v_fs1_bytes, v_fs2_blocks, v_fs2_bytes,v_fs3_blocks, v_fs3_bytes, v_fs4_blocks, v_fs4_bytes, v_full_blocks, v_full_bytes, 'PAR1');dbms_output.put_line('Unformatted Blocks = '||v_unformatted_blocks);dbms_output.put_line('FS1 Blocks = '||v_fs1_blocks);dbms_output.put_line('FS2 Blocks = '||v_fs2_blocks);dbms_output.put_line('FS3 Blocks = '||v_fs3_blocks);dbms_output.put_line('FS4 Blocks = '||v_fs4_blocks);dbms_output.put_line('Full Blocks = '||v_full_blocks);end;/

表碎片整理

通过碎片整理来收缩空间，调整高水位线，方法比较简单，首先需要启用行移动，然后 alter table …… shrink space 即可完成，期间不会阻塞 DML 操作，可能时间会很久，建议业务低峰期间操作。

alter table prod.T_ZDW_DOWN_SYNC_REC enable row movement;alter table prod.T_ZDW_DOWN_SYNC_REC shrink space cascade;alter table prod.T_ZDW_DOWN_SYNC_REC disable row movement;

除了使用 shrink space 外，还有截断表，move 表，导入导出。但是 truncate 表直接清理数据，一般情况下不可取；alter table t move; move 表会使索引失效，移动完需要 rebuild 重新建索引，移动表时对数据行的 rowid 有所变更，而索引中又包含了 rowid, 故 move 表会使索引失效。最后导出导入数据时，不能有新数据进入，这点也不太友好，故此推荐 shrink space 。

set lines 200 pages 1000col frag format 999999.99col owner format a30;col table_name format a30;col frag for a20select a.owner,       a.table_name,       a.num_rows,       a.avg_row_len,       round(a.avg_row_len * a.num_rows / 1024 / 1024, 2) real_bytes_MB,       round(b.seg_bytes_mb, 2) seg_bytes_mb,       decode(a.num_rows,0,100,(1 - round(a.avg_row_len * a.num_rows / 1024 / 1024 / b.seg_bytes_mb,2)) * 100) || '%' frag_percent  from dba_tables a,       (select owner, segment_name, sum(bytes / 1024 / 1024) seg_bytes_mb          from dba_segments         group by owner, segment_name) b where a.table_name = b.segment_name   and a.owner = b.owner   --and a.owner not in ('SYS','SYSTEM','OUTLN','DMSYS','TSMSYS','DBSNMP','WMSYS','EXFSYS','CTXSYS','XDB','OLAPSYS','ORDSYS','MDSYS','SYSMAN')   and a.owner in ('OGG','PROD')   and a.table_name='T_ZDW_DOWN_SYNC_REC'   and decode(a.num_rows,0,100,(1 - round(a.avg_row_len * a.num_rows / 1024 / 1024 / b.seg_bytes_mb,2)) * 100) > 50 order by b.seg_bytes_mb desc;

检查是否可以进行在线重定义

BEGIN  DBMS_REDEFINITION.CAN_REDEF_TABLE(    uname        => 'test',    tname        => 'original',    options_flag => DBMS_REDEFINITION.CONS_USE_PK);END;/

创建一个临时分区表 test.interim

CREATE TABLE test.interim(     col1 NUMBER,    col3 TIMESTAMP,    col4 CLOB,    col5 VARCHAR2(3))    LOB(col4) STORE AS SECUREFILE (NOCACHE FILESYSTEM_LIKE_LOGGING)    PARTITION BY RANGE (COL1) (       PARTITION par1 VALUES LESS THAN (333),       PARTITION par2 VALUES LESS THAN (666),       PARTITION par3 VALUES LESS THAN (MAXVALUE))   TABLESPACE testredeftbs   ROW STORE COMPRESS ADVANCED;

开始重新定义过程

BEGIN  DBMS_REDEFINITION.START_REDEF_TABLE(     uname        => 'test',     orig_table   => 'original',     int_table    => 'interim',     col_mapping  => 'col1 col1, TO_TIMESTAMP(col4) col3, col3 col4',     options_flag => DBMS_REDEFINITION.CONS_USE_PK);END;/

终止在线重定义

BEGIN  DBMS_REDEFINITION.abort_redef_table (       uname        => 'test',         orig_table   => 'original',         int_table    => 'interim');END;/

由于 SQLPLUS 对时间格式化的不兼容性而出错，故需 abort 终止，然后使用 PLSQL 开始。

复制依赖对象

DECLAREnum_errors PLS_INTEGER;BEGIN  DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(    uname            => 'test',     orig_table       => 'original',    int_table        => 'interim',    copy_indexes     => DBMS_REDEFINITION.CONS_ORIG_PARAMS,     copy_triggers    => TRUE,     copy_constraints => TRUE,     copy_privileges  => TRUE,     ignore_errors    => TRUE,     num_errors       => num_errors);END;/

可选择同步临时表

BEGIN   DBMS_REDEFINITION.SYNC_INTERIM_TABLE(    uname       => 'test',     orig_table  => 'original',    int_table   => 'interim');END;/

完成重新定义

BEGIN  DBMS_REDEFINITION.FINISH_REDEF_TABLE(    uname       => 'test',     orig_table  => 'original',    int_table   => 'interim');END;/

查看完成后的新表

select count(*) from original partition(par3);

分区表相关视图

显示当前用户可访问的所有分区表信息﹕
ALL_PART_TABLES
显示当前用户所有分区表的信息﹕
USER_PART_TABLES
显示表分区信息显示数据库所有分区表的详细分区信息﹕
DBA_TAB_PARTITIONS

col TABLE_OWNER for a12col TABLE_NAME for a28col PARTITION_NAME for a15 select TABLE_OWNER,TABLE_NAME,PARTITION_NAME,num_rows,last_analyzed from DBA_TAB_PARTITIONS where TABLE_OWNER='T2_OS';
select partition_name from DBA_TAB_PARTITIONS where table_owner='SYS' and TABLE_NAME='USER_LOG';

4.显示当前用户可访问的所有分区表的详细分区信息﹕
ALL_TAB_PARTITIONS

5.显示当前用户所有分区表的详细分区信息﹕
USER_TAB_PARTITIONS

6.显示子分区信息显示数据库所有组合分区表的子分区信息﹕
DBA_TAB_SUBPARTITIONS

7.显示当前用户可访问的所有组合分区表的子分区信息﹕
ALL_TAB_SUBPARTITIONS

8.显示当前用户所有组合分区表的子分区信息﹕
USER_TAB_SUBPARTITIONS

9.显示分区列显示数据库所有分区表的分区列信息﹕
     DBA_PART_KEY_COLUMNS
10.显示当前用户可访问的所有分区表的分区列信息﹕
     ALL_PART_KEY_COLUMNS
11.显示当前用户所有分区表的分区列信息﹕
     USER_PART_KEY_COLUMNS
12.显示子分区列显示数据库所有分区表的子分区列信息﹕
     DBA_SUBPART_KEY_COLUMNS
13.显示当前用户可访问的所有分区表的子分区列信息﹕
     ALL_SUBPART_KEY_COLUMNS
14.显示当前用户所有分区表的子分区列信息﹕
     USER_SUBPART_KEY_COLUMNS
15.自动创建分区示例

CREATE TABLE sys_yw.user_log(   user_id           VARCHAR2 (30),   session_id        NUMBER (8),   HOST              VARCHAR2 (30),   last_program      VARCHAR2 (48),   last_action       VARCHAR2 (32),   last_module       VARCHAR2 (32),   logon_day         DATE,   logon_time        VARCHAR2 (10),   logoff_day        DATE,   logoff_time       VARCHAR2 (10),   elapsed_minutes   NUMBER (8)) PARTITION BY RANGE(logon_day)  interval (numtoyMinterval (1,'YEAR'))       (PARTITION P_YEAR2023 VALUES LESS THAN (to_date('2023-12-31','yyyy-mm-dd')));

PARTITION BY RANGE (CREATE_TIME) INTERVAL (NUMTODSINTERVAL(1, ‘day’)) --设定通过字段’CREATE_TIME’来分区，自动创建间隔 1 天.

(partition part_t01 values less than(to_date(‘2023-08-19’, ‘yyyy-mm-dd’))); --初始建立一个分区，注意使用 less than的时候，日期设定为当天日期+1，代表今天的数据存储在当前分区。

测试环境普通表改分区表示例

BEGIN  DBMS_REDEFINITION.CAN_REDEF_TABLE(    uname        => 'OUS',    tname        => 'T_INVOICE_ATTRIBUTION',    options_flag => DBMS_REDEFINITION.CONS_USE_PK);END;/

创建临时分区表

使用 PLSQL 查看创建表的 SQL 语句或者使用如下 GET_DDL 获取建表语句

set long 9999 line 456 pages 0SELECT DBMS_METADATA.GET_DDL('TABLE','T_INVOICE_ATTRIBUTION','OUS') DDL_SQL FROM DUAL;

查看 create_time 列最小时间，以此作为初始分区

select min(create_time) from OUS.T_INVOICE_ATTRIBUTION_ABLE;

创建表

create table OUS.T_INVOICE_ATTRIBUTION_BAK(  sequence_no         NUMBER(18) not null,  pk_id               VARCHAR2(36) not null,  platform_code       VARCHAR2(500) not null,  business_invoice_id VARCHAR2(36) not null,  fk_invoice_id       VARCHAR2(36) not null,  product_code        VARCHAR2(36) not null,  supplier_code       VARCHAR2(36) not null,  supplier_name       VARCHAR2(200) not null,  counterparty_code   VARCHAR2(36) not null,  invoice_buy         VARCHAR2(500),  invoice_sell        VARCHAR2(500),  counterparty_name   VARCHAR2(200) not null,  finance_code        VARCHAR2(36) not null,  finance_name        VARCHAR2(200) not null,  head_finance_code   VARCHAR2(36),  head_finance_name   VARCHAR2(200),  invoice_type        VARCHAR2(36) not null,  invoice_code        VARCHAR2(36) not null,  invoice_number      VARCHAR2(36) not null,  invoice_date        DATE,  invoice_check_code  VARCHAR2(36),  invoice_file_id     VARCHAR2(36),  invoice_file_name   VARCHAR2(500),  financing_state     VARCHAR2(36) not null,  data_source         VARCHAR2(36),  fk_user_updat       VARCHAR2(36),  user_name_update    VARCHAR2(60),  update_time         DATE,  create_time         DATE not null,  fk_user_create      VARCHAR2(36),  user_name_create    VARCHAR2(60),  is_delete           CHAR(1) default '0' not null,  delete_time         DATE,  fk_user_delete      VARCHAR2(36),  user_name_delete    VARCHAR2(60),  batch_number        VARCHAR2(36) default '_')PARTITION BY RANGE (CREATE_TIME) INTERVAL (NUMTODSINTERVAL(1, 'day'))(PARTITION SYS_01 VALUES LESS THAN (to_date('2023-07-01','yyyy-mm-dd')));

开始在线重定义

BEGIN  DBMS_REDEFINITION.START_REDEF_TABLE(     uname        => 'OUS',     orig_table   => 'T_INVOICE_ATTRIBUTION',     int_table    => 'T_INVOICE_ATTRIBUTION_BAK',     options_flag => DBMS_REDEFINITION.CONS_USE_PK);END;/

复制依赖对象

DECLAREnum_errors PLS_INTEGER;BEGIN  DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(    uname            => 'OUS',     orig_table       => 'T_INVOICE_ATTRIBUTION',    int_table        => 'T_INVOICE_ATTRIBUTION_BAK',    copy_indexes     => DBMS_REDEFINITION.CONS_ORIG_PARAMS,     copy_triggers    => TRUE,     copy_constraints => TRUE,     copy_privileges  => TRUE,     ignore_errors    => TRUE,     num_errors       => num_errors);END;/

可选择同步临时表

BEGIN   DBMS_REDEFINITION.SYNC_INTERIM_TABLE(    uname       => 'OUS',     orig_table  => 'T_INVOICE_ATTRIBUTION',    int_table   => 'T_INVOICE_ATTRIBUTION_BAK');END;/

完成重新定义

BEGIN  DBMS_REDEFINITION.FINISH_REDEF_TABLE(    uname       => 'OUS',     orig_table  => 'T_INVOICE_ATTRIBUTION',    int_table   => 'T_INVOICE_ATTRIBUTION_BAK');END;/

收集统计信息

exec dbms_stats.gather_schema_stats('OUS');exec dbms_stats.gather_table_stats('OUS','T_INVOICE_ATTRIBUTION');
BEGIN  DBMS_STATS.GATHER_TABLE_STATS(ownname          => 'OUS',                                tabname          => 'T_STATS',                                estimate_percent => 100,                                method_opt       => 'for all columns size auto',                                no_invalidate    => FALSE,                                degree           => 8,                                cascade          => TRUE);END;/

全文完，希望可以帮到正在阅读的你，如果觉得此文对你有帮助，可以分享给你身边的朋友，同事，你关心谁就分享给谁，一起学习共同进步~~~

欢迎关注我公众号【JiekeXu DBA之路】，第一时间一起学习新知识！以下四个地址可以
找到我，其他地址均属于盗版侵权爬取我的文章，而且代码格式、图片等均有错乱，不方
便阅读，欢迎来我公众号或者墨天轮地址关注我，第一时间收获最新消息。

欢迎关注我的公众号【JiekeXu DBA之路】，第一时间一起学习新知识！————————————————————————————
公众号：JiekeXu DBA之路
CSDN ：https://blog.csdn.net/JiekeXu
墨天轮：https://www.modb.pro/u/4347
腾讯云：https://cloud.tencent.com/developer/user/5645107
————————————————————————————

分享几个数据库备份脚本

一文搞懂 Oracle 统计信息

我的 Oracle ACE 心路历程Oracle 主流版本不同架构下的静默安装指南关机重启导致 ASM 磁盘丢失数据库无法启动Oracle SQL 性能分析(SPA)原理与实战演练

Oracle 11g 升级到 19c 需要关注的几个问题
Windows 10 环境下 MySQL 8.0.33 安装指南
SQL 大全（四）|数据库迁移升级时常用 SQL 语句
OGG|使用 OGG19c 迁移 Oracle11g 到 19C(第二版)
Oracle 大数据量导出工具——sqluldr2 的安装与使用
从国产数据库调研报告中你都能了解哪些信息及我的总结建议
使用数据泵利用 rowid 分片导出导入 lob 大表及最佳实践
在归档模式下直接 rm dbf 数据文件并重启数据库还有救吗？