作者 | JiekeXu
来源 |公众号 JiekeXu DBA之路（ID: JiekeXu_IT）
如需转载请联系授权 | (个人微信 ID：JiekeXu_DBA)
大家好，我是 JiekeXu,很高兴又和大家见面了,今天和大家一起来看看 Oracle 在线重定义（中），欢迎点击上方蓝字“JiekeXu DBA之路”关注我的公众号，标星或置顶，更多干货第一时间到达！
目 录
在线重定义测试实践
查看用户权限并赋权
创建测试表
创建 16k 大小表空间
插入表数据
检查碎片率
表碎片整理
检查是否可以进行在线重定义
创建一个临时分区表 test.interim
开始重新定义过程
终止在线重定义
复制依赖对象
可选择同步临时表
完成重新定义
查看完成后的新表
分区表相关视图
测试环境普通表改分区表示例
在线重定义测试实践
前面一篇已经介绍过了《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<=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 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 = 11FS1表明有 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';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 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之路】，第一时间一起学习新知识！————————————————————————————
公众号：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 数据文件并重启数据库还有救吗？
2687
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
