导航：首页 > 数据库 >

碎片较多的表重组

发表于：2025-01-21 作者：千家信息网编辑

千家信息网最后更新 2025年01月21日，首先查询出数据库中需要重组的表：SELECT d.name,d.dbid,p.*,p.curr_mb-p.net_mb as save_mb,round((p.CURR_MB-p.NET_MB)/p.

千家信息网最后更新 2025年01月21日碎片较多的表重组首先查询出数据库中需要重组的表：
SELECT d.name,d.dbid,p.*,p.curr_mb-p.net_mb as save_mb,round((p.CURR_MB-p.NET_MB)/p.CURR_MB*100) AS PERCENTAGE
FROM
(SELECT t.owner owner,SUBSTR(t.TABLE_NAME,1,50) TABLE_NAME,t.tablespace_name,t.NUM_ROWS,t.AVG_ROW_LEN ROWLEN,
ROUND(BLOCKS*8192/1024/1024,0) CURR_MB,
ROUND(((t.AVG_ROW_LEN) * t.NUM_ROWS/1024/1024 )/(1-t.pct_free/100),0) NET_MB
FROM DBA_TABLES t WHERE table_name not like 'BIN$%' and NUM_ROWS IS NOT NULL AND PARTITIONED = 'NO' AND(IOT_TYPE != 'IOT' OR IOT_TYPE IS NULL)) p,v$database d
WHERE
CURR_MB !=0 AND
OWNER NOT IN ('SYS','SYSMAN') AND
round((p.CURR_MB-p.NET_MB)/p.CURR_MB*100)>20 and
p.curr_mb-p.net_mb>1024
ORDER BY 10,11

本次测试环境，假定查出SH用户下的COSTS表需要重组，可以使用如下方法对表进行重组：
SELECT default_tablespace from dba_users where username='SH';
SELECT BLOCKS, EMPTY_BLOCKS,TABLESPACE_NAME FROM DBA_TABLES WHERE TABLE_NAME ='COSTS' and OWNER='SH';
SELECT sum(bytes)/1024/1024 from dba_free_space group by tablespace_name having tablespace_name='USERS';

SELECT INDEX_NAME,INDEX_TYPE,TABLE_NAME,TABLE_TYPE, TABLESPACE_NAME,status,DEGREE from dba_indexes where TABLE_NAME='COSTS' and table_owner='SH';
select TABLE_NAME, CONSTRAINT_NAME, CONSTRAINT_TYPE,STATUS,DEFERRED,VALIDATED,INDEX_NAME from DBA_CONSTRAINTS WHERE TABLE_NAME ='COSTS' and OWNER='SH';
SELECT * from dba_dependencies where name='COSTS' AND OWNER='SH';
SH.COSTS
检查是否可以重定义
BEGIN
DBMS_REDEFINITION.CAN_REDEF_TABLE('SH','COSTS',
DBMS_REDEFINITION.CONS_USE_ROWID);
END;
/

创建int表
create table SH.COSTS_XXXX
(
prod_id NUMBER not null,
time_id DATE not null,
promo_id NUMBER not null,
channel_id NUMBER not null,
unit_cost NUMBER(10,2) not null,
unit_price NUMBER(10,2) not null
)
partition by range (TIME_ID)
(
partition COSTS_X122995 values less than (TO_DATE(' 1996-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace EXAMPLE
pctfree 0
initrans 1
maxtrans 255,
partition COSTS_XX12996 values less than (TO_DATE(' 1997-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace EXAMPLE
pctfree 0
initrans 1
maxtrans 255,
partition COSTS_XXH1_1997 values less than (TO_DATE(' 1997-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace EXAMPLE
pctfree 0
initrans 1
maxtrans 255,
partition COSTS_XH2_1997 values less than (TO_DATE(' 1998-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace EXAMPLE
pctfree 0
initrans 1
maxtrans 255,
partition COSTS_XQ1_1998 values less than (TO_DATE(' 1998-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace EXAMPLE
pctfree 0
initrans 1
maxtrans 255
storage
(
initial 8M
next 1M
minextents 1
maxextents unlimited
),
partition COSTS_XQ2_1998 values less than (TO_DATE(' 1998-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace EXAMPLE
pctfree 0
initrans 1
maxtrans 255
storage
(
initial 8M
next 1M
minextents 1
maxextents unlimited
),
partition COSTS_XQ3_1998 values less than (TO_DATE(' 1998-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace EXAMPLE
pctfree 0
initrans 1
maxtrans 255
storage
(
initial 8M
next 1M
minextents 1
maxextents unlimited
),
partition COSTS_XQ4_1998 values less than (TO_DATE(' 1999-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace EXAMPLE
pctfree 0
initrans 1
maxtrans 255
storage
(
initial 8M
next 1M
minextents 1
maxextents unlimited
),
partition COSTS_XQ1_1999 values less than (TO_DATE(' 1999-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace EXAMPLE
pctfree 0
initrans 1
maxtrans 255
storage
(
initial 8M
next 1M
minextents 1
maxextents unlimited
),
partition COSTS_XQ2_1999 values less than (TO_DATE(' 1999-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace EXAMPLE
pctfree 0
initrans 1
maxtrans 255
storage
(
initial 8M
next 1M
minextents 1
maxextents unlimited
),
partition COSTS_XQ3_1999 values less than (TO_DATE(' 1999-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace EXAMPLE
pctfree 0
initrans 1
maxtrans 255
storage
(
initial 8M
next 1M
minextents 1
maxextents unlimited
),
partition COSTS_XQ4_1999 values less than (TO_DATE(' 2000-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace EXAMPLE
pctfree 0
initrans 1
maxtrans 255
storage
(
initial 8M
next 1M
minextents 1
maxextents unlimited
),
partition COSTS_XQ1_2000 values less than (TO_DATE(' 2000-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace EXAMPLE
pctfree 0
initrans 1
maxtrans 255
storage
(
initial 8M
next 1M
minextents 1
maxextents unlimited
),
partition COSTS_XQ2_2000 values less than (TO_DATE(' 2000-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace EXAMPLE
pctfree 0
initrans 1
maxtrans 255
storage
(
initial 8M
next 1M
minextents 1
maxextents unlimited
),
partition COSTS_XQ3_2000 values less than (TO_DATE(' 2000-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace EXAMPLE
pctfree 0
initrans 1
maxtrans 255
storage
(
initial 8M
next 1M
minextents 1
maxextents unlimited
),
partition COSTS_XQ4_2000 values less than (TO_DATE(' 2001-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace EXAMPLE
pctfree 0
initrans 1
maxtrans 255
storage
(
initial 8M
next 1M
minextents 1
maxextents unlimited
),
partition COSTS_XQ1_2001 values less than (TO_DATE(' 2001-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace EXAMPLE
pctfree 0
initrans 1
maxtrans 255
storage
(
initial 8M
next 1M
minextents 1
maxextents unlimited
),
partition COSTS_XQ2_2001 values less than (TO_DATE(' 2001-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace EXAMPLE
pctfree 0
initrans 1
maxtrans 255
storage
(
initial 8M
next 1M
minextents 1
maxextents unlimited
),
partition COSTS_XQ3_2001 values less than (TO_DATE(' 2001-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace EXAMPLE
pctfree 0
initrans 1
maxtrans 255
storage
(
initial 8M
next 1M
minextents 1
maxextents unlimited
),
partition COSTS_XQ4_2001 values less than (TO_DATE(' 2002-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace EXAMPLE
pctfree 0
initrans 1
maxtrans 255
storage
(
initial 8M
next 1M
minextents 1
maxextents unlimited
),
partition COSTS_XQ1_2002 values less than (TO_DATE(' 2002-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace EXAMPLE
pctfree 0
initrans 1
maxtrans 255,
partition COSTS_XQ2_2002 values less than (TO_DATE(' 2002-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace EXAMPLE
pctfree 0
initrans 1
maxtrans 255,
partition COSTS_XQ3_2002 values less than (TO_DATE(' 2002-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace EXAMPLE
pctfree 0
initrans 1
maxtrans 255,
partition COSTS_XQ4_2002 values less than (TO_DATE(' 2003-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace EXAMPLE
pctfree 0
initrans 1
maxtrans 255,
partition COSTS_XQ1_2003 values less than (TO_DATE(' 2003-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace EXAMPLE
pctfree 0
initrans 1
maxtrans 255,
partition COSTS_XQ2_2003 values less than (TO_DATE(' 2003-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace EXAMPLE
pctfree 0
initrans 1
maxtrans 255,
partition COSTS_XQ3_2003 values less than (TO_DATE(' 2003-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace EXAMPLE
pctfree 0
initrans 1
maxtrans 255,
partition COSTS_XQ4_2003 values less than (TO_DATE(' 2004-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace EXAMPLE
pctfree 5
initrans 1
maxtrans 255
);

开始redefine
BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE
(uname=>'SH',
orig_table=>'COSTS',
int_table=>'COSTS_XXXX',
options_flag=>DBMS_REDEFINITION.CONS_USE_ROWID);
end;
/

拷贝依赖对象
DECLARE
num_errors PLS_INTEGER;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('SH','COSTS','COSTS_XXXX',
DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, num_errors);
END;
/

检查错误
select object_name,base_table_name,ddl_txt from DBA_REDEFINITION_ERRORS;
SELECT INDEX_NAME,INDEX_TYPE,TABLE_NAME,TABLE_TYPE, TABLESPACE_NAME,status,DEGREE from dba_indexes where TABLE_NAME='COST_XXXX' and table_owner='SH';
select TABLE_NAME, CONSTRAINT_NAME, CONSTRAINT_TYPE,STATUS,DEFERRED,VALIDATED,INDEX_NAME from DBA_CONSTRAINTS WHERE TABLE_NAME ='COST_XXXX' and OWNER='SH';

进行同步
BEGIN
dbms_redefinition .sync_interim_table('SH','COSTS','COSTS_XXXX');
END;

结束redef
BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE('SH','COSTS','COSTS_XXXX');
END;
/

SELECT INDEX_NAME,INDEX_TYPE,TABLE_NAME,TABLE_TYPE, TABLESPACE_NAME,status,DEGREE from dba_indexes where TABLE_NAME='COSTS' and table_owner='SH';
select TABLE_NAME, CONSTRAINT_NAME, CONSTRAINT_TYPE,STATUS,DEFERRED,VALIDATED,INDEX_NAME from DBA_CONSTRAINTS WHERE TABLE_NAME ='COSTS' and OWNER='SH';

SELECT INDEX_NAME,INDEX_TYPE,TABLE_NAME,TABLE_TYPE, TABLESPACE_NAME,status,DEGREE from dba_indexes where TABLE_NAME='COSTS_XXXX' and table_owner='SH';
select TABLE_NAME, CONSTRAINT_NAME, CONSTRAINT_TYPE,STATUS,DEFERRED,VALIDATED,INDEX_NAME from DBA_CONSTRAINTS WHERE TABLE_NAME ='COSTS_XXXX' and OWNER='SH';

收集统计信息
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SH',
tabname => 'COSTS',
estimate_percent => 30,
method_opt => 'for all columns size repeat',
degree => 7,
cascade=>TRUE
);
END;
/

删除int表
drop table SH.COSTS_XXXX;

很赞哦！