RE: segment fragmentation
- From: "Jacques Kilchoer" <Jacques.Kilchoer@xxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 9 Sep 2004 12:42:51 -0700
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@xxxxxxxxxxxxx&subject=unsubscribe
To search the archives - //www.freelists.org/archives/oracle-l/
Other related posts:
