运维首页 > 数据运维 > Oracle 在线重定义（中）

Oracle 在线重定义（中）

2024年 1月 22日 141.9k 0

测试实践

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

分区表相关视图

  1. 显示当前用户可访问的所有分区表信息﹕
         ALL_PART_TABLES
  2. 显示当前用户所有分区表的信息﹕
         USER_PART_TABLES
  3. 显示表分区信息 显示数据库所有分区表的详细分区信息﹕
         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';

  1. 显示当前用户可访问的所有分区表的详细分区信息﹕
         ALL_TAB_PARTITIONS
  2. 显示当前用户所有分区表的详细分区信息﹕
         USER_TAB_PARTITIONS
  3. 显示子分区信息 显示数据库所有组合分区表的子分区信息﹕
         DBA_TAB_SUBPARTITIONS
  4. 显示当前用户可访问的所有组合分区表的子分区信息﹕
         ALL_TAB_SUBPARTITIONS
  5. 显示当前用户所有组合分区表的子分区信息﹕
         USER_TAB_SUBPARTITIONS
  6. 显示分区列 显示数据库所有分区表的分区列信息﹕
         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
————————————————————————————

大白菜程序猿
数据运维
0 0

相关文章

Oracle如何使用授予和撤销权限的语法和示例
Awesome Project: 探索 MatrixOrigin 云原生分布式数据库
下载丨66页PDF，云和恩墨技术通讯（2024年7月刊）
社区版oceanbase安装
Oracle 导出CSV工具-sqluldr2
ETL数据集成丨快速将MySQL数据迁移至Doris数据库

发布评论

大白菜程序猿

这个人很懒，什么都没有留下～

文章8480 评论0