This Blog I am trying to explain when and how to attempt deframentation :
1. Defrag when:
a. Application is mostly doing FTS (mostly in Data-warehouse and hybrid databases,)
b. Application drops & rebuilds Indexes. Defrag will cut down on Index rebuild time.
In theory, de-fragmentation is normal wear and tear for a active database tables and if indexes are rebuilt for very active database tables at a regular intervals, de-fragmentation of tables probably wouldn't be required ever. For such databases, if application allows downtime, once in a while rebuild of objects greater than 30%(or 20%) fragmented could be attempted, more of schedule maintenance than anything else. Remember priority is application up-time, performance not de-fragmentation so decision not be taken lightly.
Note: FTS(Full Table Scan) should be avoided for big tables as much as possible. Creates pressure on I/O, CPU, TEMP, Memory(PGA) basically resources(all may not apply always). Recommend statistics monitoring and relevant index utilization/creation.
Plenty of queries are available online to find out fragmentation: Here is one, filter as you see fit:
col Percentage for 999
col TOTAL_SIZE for a15
col ACTUAL_SIZE for a15
col FRAGMENTED_SPACE for a15
select /* Objects to identify defrag candidates */ owner,table_name,blocks,num_rows,avg_row_len,round(((blocks*8/1024)),2)||'MB' "TOTAL_SIZE",
round((num_rows*avg_row_len/1024/1024),2)||'Mb' "ACTUAL_SIZE",
round(((blocks*8/1024)-(num_rows*avg_row_len/1024/1024)),2) ||'MB' "FRAGMENTED_SPACE",
((round(((blocks*8/1024)),2) - round((num_rows*avg_row_len/1024/1024),2))/round(((blocks*8/1024)),2))*100 "Percentage"
from all_tables WHERE Owner NOT IN (
'SYS', 'OUTLN', 'SYSTEM', 'CTXSYS', 'DBSNMP',
'LOGSTDBY_ADMINISTRATOR', 'ORDSYS',
'ORDPLUGINS', 'OEM_MONITOR', 'WKSYS', 'WKPROXY',
'WK_TEST', 'WKUSER', 'MDSYS', 'LBACSYS', 'DMSYS',
'WMSYS', 'OLAPDBA', 'OLAPSVR', 'OLAP_USER',
'OLAPSYS', 'EXFSYS', 'SYSMAN', 'MDDATA',
'SI_INFORMTN_SCHEMA', 'XDB', 'ODM')
and round(((blocks*8/1024)-(num_rows*avg_row_len/1024/1024)),2)>500 <==greater than 500MB
order by 9;
Pre-created: EMP1 is partitioned table with few million rows.
Experiment 1: Deallocate space.
alter table &TAB deallocate unused space;
***Begin
06:41:27 SQL> Alter table scott.emp1 enable ROW MOVEMENT;
Table altered.
06:42:16 SQL> select count(1) from scott.emp1 partition(sys_P775);
COUNT(1)
----------
552903
Elapsed: 00:00:00.06
06:46:07 SQL> delete from scott.emp1 partition(sys_P775) where rownum<400000;
399999 rows deleted.
Elapsed: 00:00:57.41
06:47:57 SQL> commit;
06:48:01 SQL> select sum(bytes)/power(1024,2) from dba_segments where segment_name='EMP1';
SUM(BYTES)/POWER(1024,2)
------------------------
137
Elapsed: 00:00:00.29
06:48:10 SQL> Alter table scott.emp1 shrink space compact;
Table altered.
Elapsed: 00:01:10.52
06:49:31 SQL> select sum(bytes)/power(1024,2) from dba_segments where segment_name='EMP1';
SUM(BYTES)/POWER(1024,2) <=No change
------------------------
137
06:52:45 SQL> alter table scott.emp1 deallocate unused;
Table altered.
Elapsed: 00:00:00.19
06:55:24 SQL> select sum(bytes)/power(1024,2) from dba_segments where segment_name='EMP1';
SUM(BYTES)/POWER(1024,2) <====Worked
------------------------
132.1875
Elapsed: 00:00:00.02
07:25:17 SQL> select sum(bytes)/power(1024,2) from dba_segments where segment_name='EMP1';
SUM(BYTES)/POWER(1024,2)
------------------------
132.1875
Elapsed: 00:00:00.01
07:25:24 SQL> Select count(1) from scott.emp1;
COUNT(1)
----------
1535166
Elapsed: 00:00:04.60
07:27:18 SQL> delete from scott.emp1 partition(sys_P774) where rownum<400000;
276458 rows deleted.
Elapsed: 00:00:44.08
07:28:20 SQL> commit;
Commit complete.
Elapsed: 00:00:00.01
07:28:35 SQL> ---genrating ddl
07:29:34 SQL> set heading off;
set echo off;
Set pages 999;
set long 90000;07:29:38 SQL> 07:29:38 SQL> 07:29:38 SQL>
07:29:39 SQL> select dbms_metadata.get_ddl('TABLE','EMP1','SCOTT') from dual;
CREATE TABLE "SCOTT"."EMP1"
( "EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"
PARTITION BY HASH ("EMPNO")
(PARTITION "SYS_P771" SEGMENT CREATION DEFERRED
TABLESPACE "USERS"
NOCOMPRESS ,
PARTITION "SYS_P772" SEGMENT CREATION IMMEDIATE
TABLESPACE "USERS"
NOCOMPRESS ,
PARTITION "SYS_P773" SEGMENT CREATION IMMEDIATE
TABLESPACE "USERS"
NOCOMPRESS ,
PARTITION "SYS_P774" SEGMENT CREATION IMMEDIATE
TABLESPACE "USERS"
NOCOMPRESS ,
PARTITION "SYS_P775" SEGMENT CREATION IMMEDIATE
TABLESPACE "USERS"
NOCOMPRESS ,
PARTITION "SYS_P776" SEGMENT CREATION IMMEDIATE
TABLESPACE "USERS"
NOCOMPRESS ,
PARTITION "SYS_P777" SEGMENT CREATION IMMEDIATE
TABLESPACE "USERS"
NOCOMPRESS ,
PARTITION "SYS_P778" SEGMENT CREATION IMMEDIATE
TABLESPACE "USERS"
NOCOMPRESS ) ENABLE ROW MOVEMENT
Elapsed: 00:00:08.59
07:30:47 SQL> "SYS_P777" SEGMENT CREATION IMMEDIATE
TABLESPACE "USERS"
NOCOMPRESS ,
PARTITION "SYS_P778" SEGMENT CREATION IMMEDIATE
TABLESPACE "USERS"
NOCOMPRESS ) ENABLE ROW MOVEMENT
07:33:26 SQL> exec DBMS_REDEFINITION.CAN_REDEF_TABLE(UNAME => 'SCOTT',TNAME => 'EMP1',OPTIONS_FLAG =>DBMS_REDEFINI TION.CONS_USE_ROWID);
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.20
07:37:15 SQL> ----Creating interim table
07:40:47 SQL> -----creating interim table
07:40:59 SQL> CREATE TABLE SCOTT.EMP1_interim
( "EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"
PARTITION BY HASH ("EMPNO")
(PARTITION "SYS_P771" SEGMENT CREATION DEFERRED
TABLESPACE "USERS"
NOCOMPRESS ,
PARTITION "SYS_P772" SEGMENT CREATION IMMEDIATE
TABLESPACE "USERS"
NOCOMPRESS ,
PARTITION "SYS_P773" SEGMENT CREATION IMMEDIATE
TABLESPACE "USERS"
NOCOMPRESS ,
PARTITION "SYS_P774" SEGMENT CREATION IMMEDIATE
TABLESPACE "USERS"
NOCOMPRESS ,
PARTITION "SYS_P775" SEGMENT CREATION IMMEDIATE
TABLESPACE "USERS"
NOCOMPRESS ,
PARTITION "SYS_P776" SEGMENT CREATION IMMEDIATE
TABLESPACE "USERS"
NOCOMPRESS ,
PARTITION "SYS_P777" SEGMENT CREATION IMMEDIATE
TABLESPACE "USERS"
NOCOMPRESS ,
PARTITION "SYS_P778" SEGMENT CREATION IMMEDIATE
TABLESPACE "USERS"
NOCOMPRESS );07:41:33 2 07:41:33 3 07:41:33 4 07:41:33 5 07:41:33 6 07:41:33 7 07:41:33 8 07:41:33 9 07:41:33 10 07:41:33 11 07:41:33 12 07:41:33 13 07:41:33 14 07:41:33 15 07:41:33 16 07:41:33 17 07:41:33 18 07:41:33 19 07:41:33 20 07:41:33 21 07:41:33 22 07:41:33 23 07:41:33 24 07:41:33 25 07:41:33 26 07:41:33 27 07:41:33 28 07:41:33 29 07:41:33 30 07:41:33 31 07:41:33 32 07:41:33 33 07:41:33 34 07:41:33 35 07:41:33 36 07:41:33 37 07:41:33 38
Table created.
Elapsed: 00:00:00.90
07:41:36 SQL>
exec DBMS_REDEFINITION.START_REDEF_TABLE(UNAME => 'SCOTT',orig_table=> 'EMP1',int_table=> 'EMP1_INTERIM',col_mapping => NULL,options_flag =>DBMS_REDEFINITION.CONS_USE_ROWID);
07:41:54 SQL>
PL/SQL procedure successfully completed.
Elapsed: 00:00:48.06
07:42:42 SQL> 07:42:42 SQL> 07:42:42 SQL>
07:42:45 SQL> exec DBMS_REDEFINITION.SYNC_INTERIM_TABLE(UNAME => 'SCOTT',orig_table=> 'EMP1',int_table=> 'EMP1_INTERIM');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.10
07:43:48 SQL> 07:43:48 SQL>
07:43:52 SQL> exec DBMS_REDEFINITION.FINISH_REDEF_TABLE(UNAME => 'SCOTT',orig_table=> 'EMP1',int_table=> 'EMP1_INTERIM');
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.77
07:44:41 SQL> select sum(bytes)/power(1024,2) from dba_segments where segment_name='EMP1';
128 <====Reduced from previous allocation
Elapsed: 00:00:00.25
07:48:23 SQL> ---Use abort if redef fails for any reason.
07:49:36 SQL> ---exec dbms_redefinition.abort_redef_table(UNAME => 'SCOTT',orig_table=> 'EMP1',int_table=> 'EMP1_INTERIM');
07:49:42 SQL> ----------------Done Enjoy-----
Experiment 3: Alter .... Move
08:22:37 SQL> select 'alter table '||table_owner||'.'||table_name||' move partition '||PARTITION_NAME||' tablespace '||tablespace_name||' compress update indexes;'
from dba_tab_partitions where table_name='EMP1' and table_owner='SCOTT'; 08:23:01 2
alter table SCOTT.EMP1 move partition SYS_P771 tablespace USERS compress update indexes;
alter table SCOTT.EMP1 move partition SYS_P772 tablespace USERS compress update indexes;
......More there.
8:23:02 SQL> alter table SCOTT.EMP1 move partition SYS_P771 tablespace USERS compress update indexes;
alter table SCOTT.EMP1 move partition SYS_P772 tablespace USERS compress update indexes;
Table altered.
Elapsed: 00:00:00.18
08:23:09 SQL>
Table altered.
Elapsed: 00:00:01.21
08:23:10 SQL>
Table altered.
1. Defrag when:
a. Application is mostly doing FTS (mostly in Data-warehouse and hybrid databases,)
b. Application drops & rebuilds Indexes. Defrag will cut down on Index rebuild time.
In theory, de-fragmentation is normal wear and tear for a active database tables and if indexes are rebuilt for very active database tables at a regular intervals, de-fragmentation of tables probably wouldn't be required ever. For such databases, if application allows downtime, once in a while rebuild of objects greater than 30%(or 20%) fragmented could be attempted, more of schedule maintenance than anything else. Remember priority is application up-time, performance not de-fragmentation so decision not be taken lightly.
Note: FTS(Full Table Scan) should be avoided for big tables as much as possible. Creates pressure on I/O, CPU, TEMP, Memory(PGA) basically resources(all may not apply always). Recommend statistics monitoring and relevant index utilization/creation.
Plenty of queries are available online to find out fragmentation: Here is one, filter as you see fit:
col Percentage for 999
col TOTAL_SIZE for a15
col ACTUAL_SIZE for a15
col FRAGMENTED_SPACE for a15
select /* Objects to identify defrag candidates */ owner,table_name,blocks,num_rows,avg_row_len,round(((blocks*8/1024)),2)||'MB' "TOTAL_SIZE",
round((num_rows*avg_row_len/1024/1024),2)||'Mb' "ACTUAL_SIZE",
round(((blocks*8/1024)-(num_rows*avg_row_len/1024/1024)),2) ||'MB' "FRAGMENTED_SPACE",
((round(((blocks*8/1024)),2) - round((num_rows*avg_row_len/1024/1024),2))/round(((blocks*8/1024)),2))*100 "Percentage"
from all_tables WHERE Owner NOT IN (
'SYS', 'OUTLN', 'SYSTEM', 'CTXSYS', 'DBSNMP',
'LOGSTDBY_ADMINISTRATOR', 'ORDSYS',
'ORDPLUGINS', 'OEM_MONITOR', 'WKSYS', 'WKPROXY',
'WK_TEST', 'WKUSER', 'MDSYS', 'LBACSYS', 'DMSYS',
'WMSYS', 'OLAPDBA', 'OLAPSVR', 'OLAP_USER',
'OLAPSYS', 'EXFSYS', 'SYSMAN', 'MDDATA',
'SI_INFORMTN_SCHEMA', 'XDB', 'ODM')
and round(((blocks*8/1024)-(num_rows*avg_row_len/1024/1024)),2)>500 <==greater than 500MB
order by 9;
Pre-created: EMP1 is partitioned table with few million rows.
Experiment 1: Deallocate space.
alter table &TAB deallocate unused space;
***Begin
06:41:27 SQL> Alter table scott.emp1 enable ROW MOVEMENT;
Table altered.
06:42:16 SQL> select count(1) from scott.emp1 partition(sys_P775);
COUNT(1)
----------
552903
Elapsed: 00:00:00.06
06:46:07 SQL> delete from scott.emp1 partition(sys_P775) where rownum<400000;
399999 rows deleted.
Elapsed: 00:00:57.41
06:47:57 SQL> commit;
06:48:01 SQL> select sum(bytes)/power(1024,2) from dba_segments where segment_name='EMP1';
SUM(BYTES)/POWER(1024,2)
------------------------
137
Elapsed: 00:00:00.29
06:48:10 SQL> Alter table scott.emp1 shrink space compact;
Table altered.
Elapsed: 00:01:10.52
06:49:31 SQL> select sum(bytes)/power(1024,2) from dba_segments where segment_name='EMP1';
SUM(BYTES)/POWER(1024,2) <=No change
------------------------
137
06:52:45 SQL> alter table scott.emp1 deallocate unused;
Table altered.
Elapsed: 00:00:00.19
06:55:24 SQL> select sum(bytes)/power(1024,2) from dba_segments where segment_name='EMP1';
SUM(BYTES)/POWER(1024,2) <====Worked
------------------------
132.1875
Elapsed: 00:00:00.02
Experiment 2: Defrag using DBMS_REDEFINATION.
06:55:34 SQL> ----------------Redefination07:25:17 SQL> select sum(bytes)/power(1024,2) from dba_segments where segment_name='EMP1';
SUM(BYTES)/POWER(1024,2)
------------------------
132.1875
Elapsed: 00:00:00.01
07:25:24 SQL> Select count(1) from scott.emp1;
COUNT(1)
----------
1535166
Elapsed: 00:00:04.60
07:27:18 SQL> delete from scott.emp1 partition(sys_P774) where rownum<400000;
276458 rows deleted.
Elapsed: 00:00:44.08
07:28:20 SQL> commit;
Commit complete.
Elapsed: 00:00:00.01
07:28:35 SQL> ---genrating ddl
07:29:34 SQL> set heading off;
set echo off;
Set pages 999;
set long 90000;07:29:38 SQL> 07:29:38 SQL> 07:29:38 SQL>
07:29:39 SQL> select dbms_metadata.get_ddl('TABLE','EMP1','SCOTT') from dual;
CREATE TABLE "SCOTT"."EMP1"
( "EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"
PARTITION BY HASH ("EMPNO")
(PARTITION "SYS_P771" SEGMENT CREATION DEFERRED
TABLESPACE "USERS"
NOCOMPRESS ,
PARTITION "SYS_P772" SEGMENT CREATION IMMEDIATE
TABLESPACE "USERS"
NOCOMPRESS ,
PARTITION "SYS_P773" SEGMENT CREATION IMMEDIATE
TABLESPACE "USERS"
NOCOMPRESS ,
PARTITION "SYS_P774" SEGMENT CREATION IMMEDIATE
TABLESPACE "USERS"
NOCOMPRESS ,
PARTITION "SYS_P775" SEGMENT CREATION IMMEDIATE
TABLESPACE "USERS"
NOCOMPRESS ,
PARTITION "SYS_P776" SEGMENT CREATION IMMEDIATE
TABLESPACE "USERS"
NOCOMPRESS ,
PARTITION "SYS_P777" SEGMENT CREATION IMMEDIATE
TABLESPACE "USERS"
NOCOMPRESS ,
PARTITION "SYS_P778" SEGMENT CREATION IMMEDIATE
TABLESPACE "USERS"
NOCOMPRESS ) ENABLE ROW MOVEMENT
Elapsed: 00:00:08.59
07:30:47 SQL> "SYS_P777" SEGMENT CREATION IMMEDIATE
TABLESPACE "USERS"
NOCOMPRESS ,
PARTITION "SYS_P778" SEGMENT CREATION IMMEDIATE
TABLESPACE "USERS"
NOCOMPRESS ) ENABLE ROW MOVEMENT
07:33:26 SQL> exec DBMS_REDEFINITION.CAN_REDEF_TABLE(UNAME => 'SCOTT',TNAME => 'EMP1',OPTIONS_FLAG =>DBMS_REDEFINI TION.CONS_USE_ROWID);
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.20
07:37:15 SQL> ----Creating interim table
07:40:47 SQL> -----creating interim table
07:40:59 SQL> CREATE TABLE SCOTT.EMP1_interim
( "EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"
PARTITION BY HASH ("EMPNO")
(PARTITION "SYS_P771" SEGMENT CREATION DEFERRED
TABLESPACE "USERS"
NOCOMPRESS ,
PARTITION "SYS_P772" SEGMENT CREATION IMMEDIATE
TABLESPACE "USERS"
NOCOMPRESS ,
PARTITION "SYS_P773" SEGMENT CREATION IMMEDIATE
TABLESPACE "USERS"
NOCOMPRESS ,
PARTITION "SYS_P774" SEGMENT CREATION IMMEDIATE
TABLESPACE "USERS"
NOCOMPRESS ,
PARTITION "SYS_P775" SEGMENT CREATION IMMEDIATE
TABLESPACE "USERS"
NOCOMPRESS ,
PARTITION "SYS_P776" SEGMENT CREATION IMMEDIATE
TABLESPACE "USERS"
NOCOMPRESS ,
PARTITION "SYS_P777" SEGMENT CREATION IMMEDIATE
TABLESPACE "USERS"
NOCOMPRESS ,
PARTITION "SYS_P778" SEGMENT CREATION IMMEDIATE
TABLESPACE "USERS"
NOCOMPRESS );07:41:33 2 07:41:33 3 07:41:33 4 07:41:33 5 07:41:33 6 07:41:33 7 07:41:33 8 07:41:33 9 07:41:33 10 07:41:33 11 07:41:33 12 07:41:33 13 07:41:33 14 07:41:33 15 07:41:33 16 07:41:33 17 07:41:33 18 07:41:33 19 07:41:33 20 07:41:33 21 07:41:33 22 07:41:33 23 07:41:33 24 07:41:33 25 07:41:33 26 07:41:33 27 07:41:33 28 07:41:33 29 07:41:33 30 07:41:33 31 07:41:33 32 07:41:33 33 07:41:33 34 07:41:33 35 07:41:33 36 07:41:33 37 07:41:33 38
Table created.
Elapsed: 00:00:00.90
07:41:36 SQL>
exec DBMS_REDEFINITION.START_REDEF_TABLE(UNAME => 'SCOTT',orig_table=> 'EMP1',int_table=> 'EMP1_INTERIM',col_mapping => NULL,options_flag =>DBMS_REDEFINITION.CONS_USE_ROWID);
07:41:54 SQL>
PL/SQL procedure successfully completed.
Elapsed: 00:00:48.06
07:42:42 SQL> 07:42:42 SQL> 07:42:42 SQL>
07:42:45 SQL> exec DBMS_REDEFINITION.SYNC_INTERIM_TABLE(UNAME => 'SCOTT',orig_table=> 'EMP1',int_table=> 'EMP1_INTERIM');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.10
07:43:48 SQL> 07:43:48 SQL>
07:43:52 SQL> exec DBMS_REDEFINITION.FINISH_REDEF_TABLE(UNAME => 'SCOTT',orig_table=> 'EMP1',int_table=> 'EMP1_INTERIM');
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.77
07:44:41 SQL> select sum(bytes)/power(1024,2) from dba_segments where segment_name='EMP1';
128 <====Reduced from previous allocation
Elapsed: 00:00:00.25
07:48:23 SQL> ---Use abort if redef fails for any reason.
07:49:36 SQL> ---exec dbms_redefinition.abort_redef_table(UNAME => 'SCOTT',orig_table=> 'EMP1',int_table=> 'EMP1_INTERIM');
07:49:42 SQL> ----------------Done Enjoy-----
Experiment 3: Alter .... Move
08:22:37 SQL> select 'alter table '||table_owner||'.'||table_name||' move partition '||PARTITION_NAME||' tablespace '||tablespace_name||' compress update indexes;'
from dba_tab_partitions where table_name='EMP1' and table_owner='SCOTT'; 08:23:01 2
alter table SCOTT.EMP1 move partition SYS_P771 tablespace USERS compress update indexes;
alter table SCOTT.EMP1 move partition SYS_P772 tablespace USERS compress update indexes;
......More there.
8:23:02 SQL> alter table SCOTT.EMP1 move partition SYS_P771 tablespace USERS compress update indexes;
alter table SCOTT.EMP1 move partition SYS_P772 tablespace USERS compress update indexes;
Table altered.
Elapsed: 00:00:00.18
08:23:09 SQL>
Table altered.
Elapsed: 00:00:01.21
08:23:10 SQL>
Table altered.
8:23:02 SQL> alter table SCOTT.EMP1 move partition SYS_P771 tablespace USERS compress update indexes;
alter table SCOTT.EMP1 move partition SYS_P772 tablespace USERS compress update indexes;
alter table SCOTT.EMP1 move partition SYS_P773 tablespace USERS compress update indexes;
alter table SCOTT.EMP1 move partition SYS_P774 tablespace USERS compress update indexes;
alter table SCOTT.EMP1 move partition SYS_P775 tablespace USERS compress update indexes;
alter table SCOTT.EMP1 move partition SYS_P776 tablespace USERS compress update indexes;
alter table SCOTT.EMP1 move partition SYS_P777 tablespace USERS compress update indexes;
alter table SCOTT.EMP1 move partition SYS_P778 tablespace USERS compress update indexes;
Table altered.
Elapsed: 00:00:00.18
08:23:09 SQL>
Table altered.
Elapsed: 00:00:01.21
08:23:10 SQL>
Table altered.
No comments:
Post a Comment