What is best way to reorg large table 1

[Tison]

I have a tbale of 5548 rows = 445Mb
This table has over 1000 extents.
If I reorder it, what would be best Initial, Next extent allocations (currently 104K , 128K)

 

[jaggiebunnet]

In Oracle 9 you should be using locally managed tablespaces in which case you don't need to worry about reorganising a table or extent management.

 

[alexhu]

The extents should be the same size as others in the same tablespace - and as jaggiebunnet says in an LMT

Alex

 

[SantaMufasa]

Now that you know that you should use Locally Managed Tablespaces, do you know how to proceed to fix your problem? Here are the steps to convert to LMT:

1) Ensure there is enough empty disk space to duplicate your existing non-LMT tablespace.
2) Create a new, LMT tablespace that will replace the old non-LMT tablespace:

CREATE TABLESPACE <name> DATAFILE <'file_name'> SIZE <nnM>
AUTOEXTEND ON NEXT <nnM> MAXSIZE <nnnnM>
EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

3) Move all of your tables and indexes from the old (non-LMT) tablespace to the new (LMT) tablespace. The best/easiest method is to use a SQL script that creates a SQL script to do all your moving/rebuilding for you:

set echo off
set feedback off
accept old_tsname prompt "Enter the name of the old (source) tablespace: "
accept new_tsname prompt "Enter the name of the new (target) tablespace: "
set pagesize 0
set verify off
set trimspool on
spool TempMove&old_tsname..sql
prompt Set echo on
prompt set feedback off
select    'alter table '||owner||'.'||segment_name||' /* size: '||sum(bytes)||
    ' */ move parallel nologging tablespace &new_tsname;'
from dba_extents
where tablespace_name = upper('&old_tsname') and segment_type = 'TABLE'
group by owner,segment_name
order by sum(bytes) desc,owner, segment_name
/
select    'alter index '||owner||'.'||segment_name||' /* size: '||sum(bytes)||
    ' */ rebuild parallel tablespace &new_tsname;'
from dba_extents
where tablespace_name = upper('&old_tsname') and segment_type = 'INDEX'
group by owner,segment_name
order by owner, sum(bytes) desc, segment_name
/
prompt set feedback on
spool off
prompt
prompt Wrote 'TempMove&old_tsname..sql'
prompt
set pagesize 35

4) If you have any tables with LONG columns, they will not "MOVE"; you must export them, then import them. The following script does the following:
a) Creates scripted commands to export any remaining tables in the old tablespace that did not move as a result of the previous script. (Resulting script: "TempExp<old_tsname>.bat")
b) Creates scripted commands to drop the remaining tables from the old, non-LMT tablespace and to modify the DEFAULT TABLESPACE for users from the old tablespace to the new tablespace. (Resulting script: "TempDropLongs<old_tsname>.sql")
c) Creates scripted commands to import the objects dumped into "TempExp<old_tsname>.bat". (Resulting script: "TempImp<old_tsname>.bat")

set pagesize 0
set trimspool on
set linesize 500
set feedback off
set verify off
set echo off
accept tsname prompt "Enter the tablespace name that holds the tables with LONGs: "
accept newtsname prompt "Enter the new, target tablespace name: "
accept syspw prompt "Enter the password for SYS: "
spool TempExp&tsname..bat
select 'exp buffer=15000000 compress=n grants=y feedback=1000 consistent=y '
	||'file='
	||owner||'.'||segment_name||'.dump tables='||owner||'.'||segment_name
	||' userid=sys/&syspw@'||instance_name
from dba_extents, v$instance
where tablespace_name = upper('&tsname') and segment_type = 'TABLE'
group by owner,segment_name,instance_name
order by sum(bytes),owner,segment_name
/
spool off
prompt
prompt Wrote "TempExp&tsname..bat"
prompt
spool TempDropLongs&tsname..sql
prompt set echo on
prompt set feedback off
select 'drop table '||owner||'.'||table_name||' cascade constraints;'
from dba_tables
where tablespace_name = upper('&tsname')
order by owner,table_name
/
select 'alter user '||username||' default tablespace &newtsname;'
from dba_users
where default_tablespace = upper('&tsname')
order by username
/
spool off
prompt
prompt Wrote "TempDropLongs&tsname..sql"
prompt
spool TempImp&tsname..bat
select 'imp buffer=15000000 grants=y feedback=1000 full=Y file='
	||owner||'.'||segment_name||'.dump '
	||' userid=sys/&syspw@'||instance_name
from dba_extents, v$instance
where tablespace_name = upper('&tsname') and segment_type = 'TABLE'
group by owner,segment_name,instance_name
order by sum(bytes),owner,segment_name
/
spool off
set feedback on
set linesize 180
prompt
prompt 'Wrote "TempImp&tsname..bat"'
prompt

5) Successfully run script "TempExp<old_tsname>.bat".
Successfully run script "TempDropLongs<old_tsname>.sql".
Successfully run script "TempExp<old_tsname>.bat"
6) Drop the old, non-LMT tablespace.
7) From the operating system prompt, rm/erase files that supported the old, non-LMT tablespace.

So, to convert from non-LMT to LMT, it will take some work, but the work is very well worth it from a database-behaviour perspective. The reason I have the above scripts is because we felt that it was highly important to convert legacy tablespaces to the new, much-more highly efficient LMT tablespaces.

Mufasa
(aka Dave of Sandy, Utah, USA @ 17:10 (11Jun04) UTC (aka "GMT" and "Zulu"), 10:10 (11Jun04) Mountain Time)