Home » RDBMS Server » Server Administration » Recreate tables with new storage parameters
icon5.gif  Recreate tables with new storage parameters [message #173136] Fri, 19 May 2006 10:02 Go to next message
demanlov
Messages: 4
Registered: May 2006
Location: Indiana
Junior Member
I have a situation where I need to reorganize a Tablespace that contains about 100 tables in it. To imporve performance, I need to recreate the Tables with different storage parameters including the Initial extent size. Since I did not create this schema originally, I do not have a file to use to create the tables, indexes, etc. Is there a way, other than typing it out, of recreating the tables including the indexes and giving them different storage parameters including initial extent size?

Thanks!!! Question
Re: Recreate tables with new storage parameters [message #173140 is a reply to message #173136] Fri, 19 May 2006 10:48 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Are you not using LMT?
Re: Recreate tables with new storage parameters [message #173167 is a reply to message #173136] Fri, 19 May 2006 15:33 Go to previous messageGo to next message
demanlov
Messages: 4
Registered: May 2006
Location: Indiana
Junior Member
Not sure what LMT is.
Re: Recreate tables with new storage parameters [message #173189 is a reply to message #173167] Sat, 20 May 2006 01:10 Go to previous messageGo to next message
tarundua
Messages: 1080
Registered: June 2005
Location: India
Senior Member
LMT --Locally Managed Tablespace

Refer the following article

http://www.orafaq.com/node/3
Re: Recreate tables with new storage parameters [message #173445 is a reply to message #173136] Mon, 22 May 2006 08:46 Go to previous messageGo to next message
demanlov
Messages: 4
Registered: May 2006
Location: Indiana
Junior Member
OK, Sorry for being slow. The answer is no. The tables are not locally managed. This database has been around for several years. I believe it precedes the advent of LMTs and it has never been converted.
Re: Recreate tables with new storage parameters [message #173492 is a reply to message #173445] Mon, 22 May 2006 13:17 Go to previous messageGo to next message
faiz_hyd
Messages: 294
Registered: February 2006
Senior Member
Hi

What is the Version of Oracle , ! very old ?--
Do you Toad or any other Tool which help in extracting the scripts of table creation


Thanks

[Updated on: Mon, 22 May 2006 13:21]

Report message to a moderator

Re: Recreate tables with new storage parameters [message #173837 is a reply to message #173492] Wed, 24 May 2006 15:08 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Be sure to read this first: http://www.oracle.com/technology/deploy/availability/pdf/defrag.pdf

you MUST rebuild indexes after moving tables becuase all the rowids change.

CLOB, BLOB columns aren't trivial to move - not sure if there's an easy way...
create table tst1(pk number, col1 clob, col2 clob);
create index tst1_ix on tst1(pk);

alter table tst1 move tablespace users STORAGE (INITIAL 1M NEXT 1M);
alter index tst1_ix rebuild tablespace users STORAGE (INITIAL 1M NEXT 1M);

select object_name, object_type, created from user_objects where created > sysdate -1/24/60;
...

select segment_name, segment_type, tablespace_name 
from user_segments 
where segment_name in ('SYS_LOB0000067258C00002$$', 'SYS_LOB0000067258C00003$$', 'TST1')

SEGMENT_NAME              SEGMENT_TYPE   TABLESPACE_NAME
TST1                      TABLE          USERS
SYS_LOB0000067258C00002$$ LOBSEGMENT     TST_DATA
SYS_LOB0000067258C00003$$ LOBSEGMENT     TST_DATA

Previous Topic: General purpose auditing of all application generated sqls
Next Topic: conversion from dictionary managed tablespace to Local
Goto Forum:
  


Current Time: Fri Sep 20 12:28:44 CDT 2024