Alter Table... Move 1

[dbrs4me]

Hello,
I have a table that has grown, as they do. Over time it has had to create extents for itself. The following SQL:

SELECT SEGMENT_NAME,TABLESPACE_NAME,EXTENTS,BLOCKS FROM DBA_SEGMENTS where segment_name = 'table-name';

tells me that the table has had to extend itself 260 times. I think we could gain some performance if I use the Alter Table... Move command to get this table so it doesn't have so many extents. In an Oracle manual, they have the example of:

ALTER TABLE hr.admin_emp MOVE
STORAGE ( INITIAL 20K
NEXT 40K
MINEXTENTS 2
MAXEXTENTS 20
PCTINCREASE 0 );

I am wondering what would be the best values to enter for this table and where to find the information. I would think a new, larger initial size would be in order or I will have just as many extents as before. Along those lines I would also want to change the size of the next extent? Just trying to make an informed decision. Of course I am on a time frame as my manager told me about this last night and we have an outage on Monday. Thanks in advance for any help.

Charlie

 

[SantaMufasa]

Charlie,

You can issue this command to determine the current total size of your hr.admin_emp table:

select sum(bytes)
  from dba_segments
 where owner = 'HR'
   and segment_name = 'ADMIN_EMP';

You can then use that value (or something even larger) to set the value of INITIAL.

Since you are using Oracle 10g, the best solution, by far, is to us LOCALLY-MANAGE TABLESPACES (LMT), in which you don't even need to bother with INITIAL and NEXT extent values. LMT are far superior to traditional methods of object management.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[dbrs4me]

Thank you Santa,

It looks like the tablespace is locally managed already. With that in mind, do I need alter the table or is that unnecessary?

Charlie

 

[Beilstwh]

Unnecessary, let the database manage the tablespace

Bill
Lead Application Developer
New York State, USA