Table reorganization with more fragments
First, query the tables in the database that need to be reorganized:
SELECT d.name.dbid.meme. Currentmbwp. Networkmb as save_mb,round ((p.CURR_MB-p.NET_MB) / p.CURR_MB*100) AS PERCENTAGE
FROM
(SELECT t.owner owner,SUBSTR (t.TABLECTONAMEMAL 1pje 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))
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
In this test environment, assuming that the COSTS table under SH users needs to be reorganized, you can use the following methods to reorganize the table:
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 Universe 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
Check whether it can be redefined
BEGIN
DBMS_REDEFINITION.CAN_REDEF_TABLE ('SH','COSTS'
DBMS_REDEFINITION.CONS_USE_ROWID)
END
/
Create an int table
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 (10dint 2) not null
Unit_price NUMBER (10dint 2) not null
)
Partition by range (TIME_ID)
(
Partition COSTS_X122995 values less than (TO_DATE ('1996-01-01 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 SYYYY-MM-DD HH24:MI:SS',' NLS_CALENDAR=GREGORIAN'))
Tablespace EXAMPLE
Pctfree 5
Initrans 1
Maxtrans 255
);
Start redefine
BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE
(uname= > 'SH'
Orig_table= > 'COSTS'
Int_table= > 'COSTS_XXXX'
Options_flag= > DBMS_REDEFINITION.CONS_USE_ROWID)
End
/
Copy dependent object
DECLARE
Num_errors PLS_INTEGER
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS ('SH','COSTS','COSTS_XXXX'
DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, num_errors)
END
/
Check for errors
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'
Synchronize
BEGIN
Dbms_redefinition .sync _ interim_table ('SH','COSTS','COSTS_XXXX')
END
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'
Collect statistics
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
/
Delete the int table
Drop table SH.COSTS_XXXX
