Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: segment fragmentation

RE: segment fragmentation

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Thu, 9 Sep 2004 12:42:51 -0700
Message-ID: <B5C5F99D765BB744B54FFDF35F60262109F87A14@irvmbxw02>


Oracle 8.0 had alter table move partition, but not alter table move, which was introduced in 8.1. In 8.1 alter table move will delete statistics. In 8.0 alter table move partition will not delete statistics.


Example showing that in Oracle 8.0 an alter table move partition will not delete statistics

SQL> select * from v$version ;
BANNER



Oracle8 Enterprise Edition Release 8.0.6.0.0 - Production PL/SQL Release 8.0.6.0.0 - Production
CORE Version 4.0.6.0.0 - Production
TNS for Solaris: Version 8.0.6.0.0 - Production NLSRTL Version 3.3.3.0.0 - Production

SQL> select table_name || ' (' || partition_name || ')' as object,   2 num_rows, blocks, empty_blocks, last_analyzed, tablespace_name   3 from user_tab_partitions
  4 where table_name = 'T' and partition_name = 'TP1' ; OBJECT NUM_ROWS BLOCKS EMPTY_BLOCKS LAST_ANALYZED TABLESPACE_NAME
-------- --------- --------- ------------ -------------------- ------------------------------
T (TP1) 550 10 54 2004/09/09 12:34:40 USERS SQL> alter table t move partition tp1 tablespace data storage (initial 16M) ; Table modifiée.

SQL> select table_name || ' (' || partition_name || ')' as object,   2 num_rows, blocks, empty_blocks, last_analyzed, tablespace_name   3 from user_tab_partitions
  4 where table_name = 'T' and partition_name = 'TP1' ; OBJECT NUM_ROWS BLOCKS EMPTY_BLOCKS LAST_ANALYZED TABLESPACE_NAME
-------- --------- --------- ------------ -------------------- ------------------------------
T (TP1) 550 10 54 2004/09/09 12:34:40 DATA SQL> analyze table t partition (tp1) compute statistics ; Table analysée.

SQL> select table_name || ' (' || partition_name || ')' as object,   2 num_rows, blocks, empty_blocks, last_analyzed, tablespace_name   3 from user_tab_partitions
  4 where table_name = 'T' and partition_name = 'TP1' ; OBJECT NUM_ROWS BLOCKS EMPTY_BLOCKS LAST_ANALYZED TABLESPACE_NAME
-------- --------- --------- ------------ -------------------- ------------------------------
T (TP1) 550 8 8186 2004/09/09 12:34:41 DATA  


Example showing that in Oracle 8.1 an alter table move will delete statistics

SQL> select * from v$version ;
BANNER



Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production PL/SQL Release 8.1.7.0.0 - Production
CORE 8.1.7.0.0 Production
TNS for Solaris: Version 8.1.7.0.0 - Production NLSRTL Version 3.4.1.0.0 - Production

SQL> select table_name as object,
  2 num_rows, blocks, empty_blocks, last_analyzed, tablespace_name   3 from user_tables
  4 where table_name = 'T' ;
OBJECT NUM_ROWS BLOCKS EMPTY_BLOCKS LAST_ANALYZED TABLESPACE_NAME
-------- --------- --------- ------------ -------------------- ------------------------------
T 4454 69 5 2004/09/09 12:39:32 USERS SQL> alter table t move tablespace example1 storage (initial 16M) ; Table modifiée.

SQL> select table_name as object,
  2 num_rows, blocks, empty_blocks, last_analyzed, tablespace_name   3 from user_tables
  4 where table_name = 'T' ;
OBJECT NUM_ROWS BLOCKS EMPTY_BLOCKS LAST_ANALYZED TABLESPACE_NAME
-------- --------- --------- ------------ -------------------- ------------------------------

T                                                              EXAMPLE1



--

To unsubscribe - mailto:oracle-l-request_at_freelists.org&subject=unsubscribe To search the archives - http://www.freelists.org/archives/oracle-l/ Received on Thu Sep 09 2004 - 14:38:47 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US