RE: Reorganizing tables

[BALA,PRAKASH (HP-USA,ex1)]

Title: RE: Reorganizing tables

[EMAIL PROTECTED]> select tablespace_name, initial_extent, next_extent, extent_management from dba_tablespaces
  2  where tablespace_name in ('TAB128K02', 'TAB4M02');

 

TABLESPACE_NAME                INITIAL_EXTENT NEXT_EXTENT EXTENT_MAN
------------------------------ -------------- ----------- ----------
TAB4M02                               4194304     4194304 LOCAL
TAB128K02                              131072      131072 LOCAL

 

[EMAIL PROTECTED]> create table test tablespace tab128k02 as select * from dba_objects;

 

Table created.

 

[EMAIL PROTECTED]> select tablespace_name, initial_extent, next_extent from dba_tables where table_name='TEST';

 

TABLESPACE_NAME                INITIAL_EXTENT NEXT_EXTENT
------------------------------ -------------- -----------
TAB128K02                              131072      131072

 

[EMAIL PROTECTED]> alter table test move tablespace TAB4M02;

 

Table altered.

 

[EMAIL PROTECTED]>  select tablespace_name, initial_extent, next_extent from dba_tables where table_name='TEST';

 

TABLESPACE_NAME                INITIAL_EXTENT NEXT_EXTENT
------------------------------ -------------- -----------
TAB4M02                                131072     4194304

We are on 8.1.7.4 on HP-UX 11.0

 

 

-----Original Message-----
From: Wolfgang Breitling [mailto:[EMAIL PROTECTED]
Sent: Monday, March 24, 2003 16:09
To: Multiple recipients of list ORACLE-L
Subject: RE: Reorganizing tables

Re "I do not use the 'alter table ... move ...' command since it retains
the old extent size for the very 1st extent in the new tablespace."

If you have LMT with uniform size and you move a table "up", each extent,
including the first will be of the uniform size. There is no "retaining the
old extent size". Eeven if you move "down", all extents, including the
first, will be of the uniform size, you just get enough initially to cover
whatever is requested for initial - which is why all my tables have an
initial  2K, next 2K storage clause. That leaves it entirely to the LMT to
allocated the necessary extents.

Alternately, you can specify a storage clause with a different, fitting
initial extent in the move command.

At 11:34 AM 3/24/2003 -0800, you wrote:
>Hi Dennis,
>
>Let me try to answer part of question#1. We only deal with warehouse
>applications. So there are only inserts and updates.
>
>All tablespaces are LMTs and I use 3 extent sizes (128K, 4M and 32M).
>
>I do not mix the staging (insert and truncate) tables and the normal tables
>in the same tablespace.
>
>Once I month, I run a job for tables in 128K and 4M tablespaces to see
>whether I need to promote them to a higher extent size. If so, I export,
>drop, recreate and import in a new tablespace. I do not use the 'alter table
>... move ...' command since it retains the old extent size for the very 1st
>extent in the new tablespace.

Wolfgang Breitling
Centrex Consulting Corporation
http://www.centrexcc.com

                         ********************

This email communication is intended as a private communication for the sole use of the primary addressee and those individuals listed for copies in the original message. The information contained in this email is private and confidential and if you are not an intended recipient you are hereby notified that copying, forwarding or other dissemination or distribution of this communication by any means is prohibited.  If you are not specifically authorized to receive this email and if you believe that you received it in error please notify the original sender immediately.  We honour similar requests relating to the privacy of email communications.

Cette communication par courrier �lectronique est une communication priv�e � l'usage exclusif du destinataire principal ainsi que des personnes dont les noms figurent en copie.  Les renseignements contenus dans ce courriel sont confidentiels et si vous n'�tes pas le destinataire pr�vu, vous �tes avis�, par les pr�sentes que toute reproduction, tout transfert ou toute autre forme de diffusion de cette communication par quelque moyen que ce soit est interdit.  Si vous n'�tes pas sp�cifiquement autoris� � recevoir ce courriel ou si vous croyez l'avoir re�u par erreur, veuillez en aviser l'exp�diteur original imm�diatement.  Nous respectons les demandes similaires qui touchent la confidentialit� des communications par courrier �lectronique.