Oracle 在线重定义（中）-每日运维

测试实践

前面一篇已经介绍过了《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;

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

表碎片整理

通过碎片整理来收缩空间，调整高水位线，方法比较简单，首先需要启用行移动，然后 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;

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

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 开始。

复制依赖对象

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

查看完成后的新表

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

分区表相关视图

显示当前用户可访问的所有分区表信息﹕
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','OPUS') DDL_SQL FROM DUAL;

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

select min(create_time) from OPUS.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
————————————————————————————

Oracle 在线重定义（中）

测试实践

查看用户权限并赋权

创建测试表

创建 16k 大小表空间

插入表数据

表碎片整理

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

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

开始重新定义过程

终止在线重定义

复制依赖对象

可选择同步临时表

完成重新定义

查看完成后的新表

分区表相关视图

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

相关文章

发布评论取消回复

大白菜程序猿

LeaferJS 1.0 重磅发布：强悍的前端 Canvas 渲染引擎

微软中国 9 月起只能用 iPhone 工作

Oracle存储异常的管理与解决

Oracle 在线重定义（中）

测试实践

查看用户权限并赋权

创建测试表

创建 16k 大小表空间

插入表数据

表碎片整理

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

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

开始重新定义过程

终止在线重定义

复制依赖对象

可选择同步临时表

完成重新定义

查看完成后的新表

分区表相关视图

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

相关文章

发布评论 取消回复

发布评论取消回复