Oracle 在线重定义（中）

原创 JiekeXu All China Database Union 2024-01-22

1678

在线重定义测试实践

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

————————————————————————————
微信公众号：JiekeXu DBA之路
墨天轮：https://www.modb.pro/u/4347
CSDN ：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  NO
TEST SELECT ANY TABLE                         NO  NO  NO
TEST UNLIMITED TABLESPACE                     NO  NO  NO
TEST CREATE SESSION                           NO  NO  NO

查询用户具有的表权限：

col PRIVILEGE for a15
col GRANTEE for a15 
col OWNER for a15
select  *  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;

图片.png

创建 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;

图片.png

插入表数据

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;
/

图片.png

DELETE FROM test.original WHERE (COL1/3) <> TRUNC(COL1/3);

Commit;
select * from test.original WHERE COL1<=2;

图片.png

检查碎片率

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

图片.png

set serverout on size 1000000
declare
p_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;
begin
dbms_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 = 0
FS2: blocks = 2
FS3: blocks = 0
FS4: blocks = 0
Full blocks = 11

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

--分区表碎片查看
col TABLE_OWNER for a30
col TABLE_NAME for a30
col PARTITION_NAME for a30
col TABLESPACE_NAME for a30
select TABLE_OWNER,TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,NUM_ROWS,LAST_ANALYZED from DBA_TAB_PARTITIONS where TABLE_OWNER='TEST';

set serveroutput on
declare
v_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;
begin
dbms_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;
/

图片.png

表碎片整理

通过碎片整理来收缩空间，调整高水位线，方法比较简单，首先需要启用行移动，然后 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 1000
col frag format 999999.99
col owner format a30;
col table_name format a30;
col frag for a20
select 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;

图片.png

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

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;
/

图片.png

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

图片.png

复制依赖对象

DECLARE
num_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;
/

图片.png

查看完成后的新表

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

图片.png

分区表相关视图

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

col TABLE_OWNER for a12
col TABLE_NAME for a28
col 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';

显示当前用户可访问的所有分区表的详细分区信息﹕
ALL_TAB_PARTITIONS
显示当前用户所有分区表的详细分区信息﹕
USER_TAB_PARTITIONS
显示子分区信息显示数据库所有组合分区表的子分区信息﹕
DBA_TAB_SUBPARTITIONS
显示当前用户可访问的所有组合分区表的子分区信息﹕
ALL_TAB_SUBPARTITIONS
显示当前用户所有组合分区表的子分区信息﹕
USER_TAB_SUBPARTITIONS
显示分区列显示数据库所有分区表的分区列信息﹕
     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.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_YEAR2022 VALUES LESS THAN (to_date('2022-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 0
SELECT 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;
/

复制依赖对象

DECLARE
num_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之路
CSDN ：https://blog.csdn.net/JiekeXu
墨天轮：https://www.modb.pro/u/4347
腾讯云：https://cloud.tencent.com/developer/user/5645107
————————————————————————————
图片.png

最后修改时间：2024-01-27 23:51:18

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

1人已赞赏

文章被以下合辑收录

JiekeXu DBA之路（共222篇）

JiekeXu：来自西北，90 后，获得 11g OCP、11g OCM 认证，墨天轮 MVP，墨天轮 2021 十大突出贡献人物之一，此专栏记录一个菜鸟 DBA 学习成长之路，欢迎关注公众号 JiekeXu DBA之路，一起学习进步。

Oracle 在线重定义（中）

在线重定义测试实践

查看用户权限并赋权

创建测试表

创建 16k 大小表空间

插入表数据

检查碎片率

表碎片整理

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

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

开始重新定义过程

终止在线重定义

复制依赖对象

可选择同步临时表

完成重新定义

查看完成后的新表

分区表相关视图

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

文章被以下合辑收录

评论