Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Kailash.

Asked: March 15, 2003 - 3:19 pm UTC

Last updated: March 25, 2017 - 3:41 am UTC

Version: 8.1.7

Viewed 50K+ times! This question is

You Asked

Most of the DBA books on Oracle discuss that if a table has lot of empty blocks below the HWM, the best way to reorg is to do the following steps :

1. CREATE TABLE N AS select * from O
2. Drop O
3. Create Owith appropriate INITIAL_EXTENT
4. CREATE TABLE O AS SELECT * FROM N

My question is :

Qn 1.Whenever you do a FTS, the table reads all of the blocks below the HWM. So in this case all of the empty blocks below HWM in A will go into B as well. Is this right or is my understanding wrong?

Qn 2.If a FTS is performed, Oracle ignores any empty blocks it finds on it's way and considers only blocks with meaningful data.

Which one of the above questions is true? Qn 1 or Qn 2

Suppose Qn 1 is TRUE, then how table reorg is achieved by following steps 1 thru 4 given above? Please explain



and Tom said...

I would prefer

alter table T move;

But if your books really have those four steps -- it is time for a new book. At worst it would be:

create table N as select * from O;
drop O;
rename N to O;


and you would NEVER ever use initial/next or pctincrease since we all know that locally managed tablespace are the way to go and storage parameters should not be used....


IF I felt the serious burning desire to reorg it at all (which i almost never ever do since it is a fact that the table will just go back to the way it is all over again)...

if I full scan lots, maybe, index read -- nah.

q1) that would be wrong - the RESULTS of the full scan (the rows) are fed into the insert -- not the raw blocks

q2) Oracle does not ignore them, it reads them, processes them and returns any rows from them. No rows -- no output -- it is not ignored however.




Rating

  (65 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Storage Parameter

Lakshminarasimhan R, March 15, 2003 - 10:02 pm UTC

Hi Tom

You have mentioned above that

<
and you would NEVER ever use initial/next or pctincrease since we all know that
locally managed tablespace are the way to go and storage parameters should
not be used....
>

If we are not giving any storage parameter, table will automatically inherit Tablespace parameters isnt it. that means we need to give some general storage parameter (like initial, next) to Tablespace to propagate the same to tables?





Tom Kyte
March 16, 2003 - 9:43 am UTC

read about LOCALLY MANAGED TABLESPACES

and never use storage parameters again, be free from this arduous task.

You will either use

LMTs with uniform extent sizes -- initial=next and pctincrease=0 and the DBA decides what the extent size will be.

LMTs with system managed extent sizes -- the system decides automagically what the extent sizes will be.


both prevent fragmentation and since it matters not if a segment is in 1, 100, or 1000 extents -- you need not worry about that either (maxextents is always UNLIMITED in LMTs).



Any downside to LMT?

Chandru, March 17, 2003 - 10:55 pm UTC

I always hear great things about LMT. Is there any downside with regard to LMT? Any side effects that you know? Is LMT the perfect architecture that Oracle has evolved with respect to space management? Thanks.

Tom Kyte
March 18, 2003 - 7:35 am UTC

I have found no downsides.

More insight needed

Arun Gupta, April 30, 2003 - 12:51 pm UTC

Tom
I need more on when to use uniform extent size and when to use auto allocate, particulary for web based OLTP application.

What is the best method for converting from uniform allocation to auto allocate in 9i.

Thanks


Tom Kyte
April 30, 2003 - 7:03 pm UTC

do you know the size of the object (what it will be forever?)
do you know how fast the object should grow (for objects that grow over time)

if so, uniform is for you -- you would pick an extent size for the "static" object (that which you know the size of) that results in a number of extents that pleases you (no magic number there -- somewhere between 1 and 2 billion)

Or you would pick an extent size that caused the object to extend about once a month -- so you can watch and monitor it's growth over time.


Otherwise, you may as well use system allocated extents.

ALTER TABLE move is the "best" way to convert.

RECREATE INDEXES

Jack, May 15, 2003 - 7:25 pm UTC

Tom,
We bought a product and that was put into production longback. When we brought the product we have one ORDER_ITEMS table which was having composite indexes (one index with multiple columns). Right now we have about 15 million rows in this table. We have 7 indexes similar to this. We want to drop all these indexes and and want to create new indexes (as the existing composite indexes are becoming useless).
We would like to keep the downtime as min. as possible. This is a online production system. Is there a way we can approach this.
(This is a 8.17.4 oracle db and no near plans of moving to 9i). Highly appreciate your suggestion.

Tom Kyte
May 15, 2003 - 7:34 pm UTC

why are the existing composite indexes "becoming useless"



INDEX REBUID

jack, May 16, 2003 - 2:12 am UTC

Business requirements changed over the time and hence the where clauses are looking at different columns now (instead of the first column in the composite index).
pl.suggest a good practical way to do this

Tom Kyte
May 16, 2003 - 9:59 am UTC

that is not an index rebuild.

that is a change in the application.

You can build indexes "online"



ops$tkyte@ORA817DEV> create index t_idx on t(x) online;

Index created.
 

Well, sometimes...

Brent, May 16, 2003 - 10:33 am UTC

---
and you would NEVER ever use initial/next or pctincrease since we all know that
locally managed tablespace are the way to go and storage parameters should
not be used....
---

Well, that's not strictly true. I completely agree the LMT's are the way to go.

One case that comes to mind, though, is an application we use that creates the tables with large initial extents sizes. A lot of the these are lookup tables that are created with 2mb or larger initial extents, but will never grow beyond 64k. In this case an:

alter table my_table move tablespace my_tablespace;

... will retain the 2mb initial extent. You would need to specify an initial extent size in this case.


Tom Kyte
May 16, 2003 - 5:03 pm UTC

ahh but you broke the FIRST RULE.

You used a storage clause -- just like a lie leads to another lie, a storage clause leads to another storage clause.


so, if you NEVER use them, you, well, never use them ;)

Agreed, but...

Brent, May 16, 2003 - 5:13 pm UTC

The app was originally written for Oracle 7, and the scripts the vendor supplied creates the tables (a few dozen).

Usually I just edit them where I can, then run them... sometimes it's easier the other way around.

How to minimize downtime?

Robert, February 08, 2005 - 1:25 pm UTC

Tom,

(8.1.7.4 database)

To drastically speed up performance of a batch job, I am setting up a script to create a table in a SORTED state...

create table t2 as select * from t1 ORDER BY X;
drop table t1;
rename t2 to t1;
[then create all indexes on the new t1]

The step to create the new indexes is what is bugging me.
Is there a method whereby I can create this new table, somehow 'keeping' the old indexes?

In 'pseudo-code', this is what I am looking for....

alter table t1 move [order by x];
rebuild indexes.

Thanks,

Robert.

Tom Kyte
February 09, 2005 - 1:37 am UTC

well, are you sure you didn't want this table to be an IOT and hence "just be sorted all by itself"


but short of that, you would have to query the data dictionary to generically "find all of the old indexes", there is no mechanism for copying indexes over (they need new names and such).

(10gr2 will be giving an online redefine that includes the ability to order by during the redef and 10gr1 already copies the indexes over for us...)

Helpful

Robert, February 09, 2005 - 11:00 am UTC

Tom,

Thanks for the negative info that my original idea CANNOT be done (thus saving time considering it)... and for the positive info... to consider making this table an IOT... this table may indeed be a candidate for such a thing.

Thanks,

Robert.

table/index reorg,

sns, November 27, 2005 - 12:13 pm UTC

We are planning to automate the process of table/index reorg to defragment the segments.
The reason being, there are millions of records that gets deleted and inserted every day
(truncate option ruled out) causing lots of free blocks within the segments.
Last week a 100 Gig object after reorg came down to 40 Gig (60 Gig of free blocks).

Our tablespaces are LOCAL and some are AUTO and some are UNIFORM segment allocation.

The plan is, to create a tablespace about 30-40 gig as a staging area to store the objects
temporarily.

We know at what time we have permission to do the reorgs (say between 2pm - 7pm everyday).

Using shell scripts and pl/sql procedure, the plan is to move the objects to the
staging tablespace and then move it back to its original tablespace.
If we move the tables or table partition,
then the indexes (local, global or non-partitioned) indexes will be rebuilt.

This process should identify the objects that are having lots of free blocks and pick the
one that has the most. It should stop doing reorg once the process hits 6.45pm.

We are planning to put this in "crontab" and run once a week.

Do you have any better plans to achieve this objective?
Also, how do you pick the objects that are having lots of free blocks? I tried your "show_space"
procedure, however it returned "no rows" after running for few minutes on a 1 terabyte database. I
expected some objects would be returned.

I did like this:

select * from
(select SEGMENT_NAME, SEGMENT_TYPE,FREE_BLOCKS,TOTAL_BLOCKS,UNUSED_BLOCKS
from table( show_space_for( '%','ROLLUP_SVCE','%','%' ) )
) order by free_blocks
/

where ROLLUP_SVCE is the schema name.

Tom Kyte
November 27, 2005 - 12:26 pm UTC

.... Last week a 100 Gig object after reorg came down to 40 Gig (60 Gig of free
blocks). ....

Ok, but does it grow back to 100gb - and stay there. Meaning are you getting any LONG TERM benefit from this or do you just do lots of work to get small, lots of work to get big, lots of work to get small, lots of work to get big again over and over and over and over.

Do you save disk space long term - or do you have times where lots of free space is there and then days later - not there.

Unless you are saving space in the long term - I'd ask "why"



You would be a candidate for the segment space advisor in 10g - it already automates what you are talking about automating. And it can use online segment shrinking (meaning the table is "reorganized" in place - rows at the bottom of the table are moved to the top and then we can redraw the high water mark at the end of the process - releasing space)

table/index reorg cont....

sns, November 27, 2005 - 5:12 pm UTC

Well, if we won't reorg the job fails (simple! no space in the tablespace). After reorg, the table shrink down significantly (depends on how often we do the reorg). It will definitely grow again. This concept of reorg in our work place is inevitable. The two reasons behind this is the kind of ETL we do and the management not providing extra disks for the database. I think even if we add 100 or 200 Gig for the existing 1 TB database, still the concept of reorg is inevitable.

In 10g, do you mean "alter table table_name shrink compact"?
What are the criteria to use the shrink command?

What is the speed? If we manually reorg, 100 Gig coming down to 40 Gig took 2-3 hours and another couple of hours for rebuilding index. Is it faster than manual reorg?

Also, how do you use the segment space advisor? What information does it provide?

Thanks,


Tom Kyte
November 27, 2005 - 6:27 pm UTC

but my point is

if you turn 100gb into 40gb and a week later it is back at 100gb, what have you done? besides burn alot of cycles?

have you saved *any* disk space? (no).




cont...

sns, November 28, 2005 - 11:02 am UTC

100 GB to 40 Gb and later back to 100 Gb and what I have done?
I think you did not get my point. When there is no space in tablespace, our ETL job fail. By reducing the size of the table from 100 gb to 40 gb, I got 60 gb of free space back in my tablespace which is good enough for my ETL job to continue. In the long term did I gain anything? Absolutetly not. So is it not necessary to do the reorg? Absolutely necessary in order to make our ETL job to run successfully.

So in our environment, it is absolutely necessary to do the reorg to get some free space back in our tablespaces.

I used the word "absolutely" three times above which all referenced to the keyword "reorg".

Thanks,



Tom Kyte
November 28, 2005 - 2:02 pm UTC

given the cost of disk (nothing) versus cost of our time (lots), still doesn't seem like the right approach.


You will hit the wall again soon, as the volume of data increases.

quest cont...

sns, December 01, 2005 - 10:38 am UTC

So what would be the right approach?

If we leave the table without reorg, in our case within a year, the table may be 500GB but the data may be 100 GB. If we continue in the way by buying more disks (since it costs less) then say after 5 years I can proudly say I worked on a database which is 5 TB but the actual data may be 1 TB.

5 years later buy more disks and continue.....



Tom Kyte
December 01, 2005 - 12:49 pm UTC

but the database does grow to 5tb doesn't it.

then you purge and shrink it down

then you fill it back up

then you purge and shrink it down



cont...

sns, December 01, 2005 - 2:26 pm UTC

That is exactly what I am doing. I am not waiting it grow to 5TB, but shrinking (reorging) quite often. In our case, we are removing the segment fragmentation when the tablespace is almost 100% full.

I think you finally agreed what I am doing.

Now a technical question: You advised in your previous reply to use segment advisor in 10g. How exactly it works? Does shrink command comes into picture here?

Let us forget about the purpose of reorging the tables/indexes. Within my SQL script, can I use "alter table t shrink compact" and later "alter table t shrink space" right after deletes and inserts?

Thanks,

Tom Kyte
December 02, 2005 - 10:32 am UTC

not really (that I agree), you are waging a continous battle.


the segment advisor works by looking for segments that have white space and then reorganizing them (shrink, rebuild, whatever).


You you can do anything yourself.

Question for "sns from austin,tx"

Logan Palanisamy, December 01, 2005 - 7:59 pm UTC

Why are your free blocks not reused when you insert later?

Looks like you are:

insert /*+ Append */ ..
delete .. where ..
insert /* + Append */
delete .. where ..

Only in the above case, your free blocks won't be used. If you do a simple insert and delete, and the amount of inserted data is about the same as the deleted data, you will reach a steady state.

That is what Tom has been pointing out.

no battle here,

sns, December 02, 2005 - 1:36 pm UTC

I was trying to explain the problem and sort of giving my inputs for your answer.

Answering the question posted by Logan Palanisamy

We are not using APPEND hint anywhere in our INSERT statement. We know APPENDinserts above the HWM and does not use the free blocks. But, practically, we are still having problems of lots of free blocks within the segments after doing deletes and inserts.

Also, Tom was mainly pointing to WHY doing reorg when anyway it will eventually fillup the space gained.

Thanks,

Tom Kyte
December 03, 2005 - 9:58 am UTC

I'd rather you did this:

create table new_table as select the rows to keep from old table; -- instead of delete

drop old_table
rename new_table old_table

insert /*+ append */ into old_table all of the new stuff

index it
grant it
constrain it


if you are going to do this at all - less steps.

cont...q\

sns, December 03, 2005 - 10:45 am UTC

Well, problem is we have 500-600 million records in the base table and we may delete 1-2 million each day and insert around 2-3 million.

Each table has around 4-6 indexes and creating them takes long hours.

Thanks,

Tom Kyte
December 03, 2005 - 10:59 am UTC

and shifting lots of rows with alter shrink is "fast"? seems like your indexes would be "wacked" here pretty bad - the delete hits them, the insert hits them, the shrink does the equivalent of a delete + insert again. you might well have more space in your indexes than you reclaim from your tables!

create indexes in parallel, no logging. create as many at a time as you can (have up to 2x the number of cpu's number of processes working on indexing)


up to you. the table segements should not continue growing forever, they would get to a size and more or less stay there.

How to reorganize a table that has long on it?

SP, December 03, 2005 - 6:06 pm UTC

We have a table, from one of the app server vendors, that still uses a long column and is heavily accessed with a lot of transactions going against it. We want to rebuild it. I tried DBMS_REDEFINITION but since it had long, it failed. "Create as select" doesn't work for the same reason. Looks like exp, imp and rename is the only viable option-- which means downtime. Are there any other options to rebuild the table online? DB version is 9i...

Tom Kyte
December 04, 2005 - 6:10 am UTC

not unless you are willing to convert the long to a clob.

why do you want to rebuild it?

To gain performance by reducing high water mark

SP, December 04, 2005 - 3:41 pm UTC

Thanks Tom for your reply.
The table doesn't have index other than the primary key. Once for some reason, the table grew to few thousand rows. The table usally have very few rows, not more than 50. So whenever the application does a delete/update/select it has to do full table scan and takes lot longer to finish. We could get around this by adding index, but the table is very write intensive and for this reason we don't want to throw any indexes...We hope to gain performance by lowering the high water mark.


Tom Kyte
December 04, 2005 - 3:45 pm UTC

gosh, a full scan on a table with longs?

what exactly is the size of the longest long?

and what is the query you are forced to full scan with???? seems we should really fix that - with a long especially????

table move

sandhu, February 07, 2006 - 2:09 pm UTC

I have table size of 50 Gb and I am planning to move it in same tablespace.
How much space will it take to move?

Tom Kyte
February 08, 2006 - 1:41 am UTC

somewhere between 0 and (lots and lots of gb, maybe 100's) above and beyond the 50gb it already takes.

There will be two copies of the table at some point.

If the 50gb table has zero rows - it'll take about 50gb (current table) + 0 gb (new table).

If the 50gb table is full - totally full - every block is 100% full and you have a high pctfree set - when you move it, the blocks will become almost empty and you'll need 50gb (current table) + (as much room as it takes for this packed table to become unpacked)


Why do you want to do this? What have you done to give you information that says "i must move this table to reorg it?"

newbie

A reader, August 17, 2006 - 3:58 pm UTC

very helpful

CTAS reorgs , Move doesn't?

A reader, December 13, 2006 - 3:26 pm UTC

I've recently had a tar open where someone stated
that CTAS will reorg a table but that MOVE will not, "necessarily". What do you make of this?

Tom Kyte
December 15, 2006 - 7:52 am UTC

they do not know that which they speak of.

Or they have a unique definition of reorganize that does not match our more conventional interpretation.

create table as select creates a new segment, reads data from source query and writes it into new segment.

alter table t move creates a new segment, reads......  well, it creates a new "data segment"


ops$tkyte%ORA9IR2> create table t as select * from all_users;

Table created.

ops$tkyte%ORA9IR2> select object_name, object_id, data_object_id from user_objects where object_name = 'T';

OBJECT_NAME                     OBJECT_ID DATA_OBJECT_ID
------------------------------ ---------- --------------
T                                   39203          39203

ops$tkyte%ORA9IR2> alter table t move;

Table altered.

ops$tkyte%ORA9IR2> select object_name, object_id, data_object_id from user_objects where object_name = 'T';

OBJECT_NAME                     OBJECT_ID DATA_OBJECT_ID
------------------------------ ---------- --------------
T                                   39203          39204



the table is "reorganized" during the move, blocks filled up (to pctfree), whitespace removed, all bits are moved from point A to point B. 

How to move tables from system tablespace to new tablespace in Oracle 7.2

Vinay Pandey, September 18, 2007 - 10:38 am UTC

I have database Oracle 7.2

All the user tables are existing in System Tablespace.
I have created a different tablespace with name USER.

I want to move all the user tables to this new ('USER')tablespace. Is it possible to do it through this command in Oracle 7.2

Alter table <tablename> move tablespace <new tablespace>

Please guide.
If this command is not allowed in Oracle 7
What are the options and which one is best?



Tom Kyte
September 18, 2007 - 4:49 pm UTC

your options are excessively limited.

However I would just let it be - you have a very very old thing there, do not change anything - you'll break it. Just let it run - there is no need to move it out of system.

What steps should I take to gain performance

Vinay Pandey, September 19, 2007 - 8:50 am UTC

Thanks for the guidance regarding move tables in new tablespace in Oracle 7.2

Further I look your guidance to upgrade it.
I am having server with configuration (This server has been configured in 1997 as per the requirement).

Processor P-1
Windows NT
RAM 128 MB
HDD 10 GB

database : Oracle 7.2
Application: D2K Forms 4.5 / Report 2.5

The issue is of performance.

Observations :-

1. All the tables are created in System tablespace.
No seperate tablespaces were created.
[Database size is 1GB]
==>(You have suggested not to move table into new tablespace)
What are other options to improve activities like insertion/ data import/ data export/ Query performance?

2.The SGA size is 20 MB out of 128 MB RAM.

==>What do you suggest how much portion of RAM (Physical memory) shall be used for SGA allocation?


3. Shall we run the current database (Oracle 7.2) or we should migrate to Oracle 9i.

--------------------

In a different server
P-IV
4 GB RAM
360 GB HDD
Oracle 10g

database size : 70 GB
SGA size : 1GB
Fixed Size 1.195 MB
Variable Size 456.000 MB
Database Buffers 560.000 MB
Redo Buffers 6.805 MB

or Pool wise (V$sgastat)

Not defined 568.000 MB
java pool 4.000 MB
streams pool 11.999 MB
shared pool 172.726 MB
large pool 4.000 MB

If we are having 4GB of RAM (physical memory) how much should be allocated for SGA. Is there any formula / way to set SGA allocation.

Vinay Pandey


Tom Kyte
September 19, 2007 - 1:05 pm UTC

1/2) tablespaces are not a performance tuning tool, they are for an administrator to organize things as they wish. It matters not that all things are in system performance wise.

You have a database that would fit on my USB pendrive, I cannot fathom a performance issue in this database.

well, hold on, windows nt running just by itself on a 128mb machine would be slow. You have decade old hardware, it isn't going to be fast. I think what needs to be corrected is the perception of what is "fast" here.

3) if you are going to do a technology upgrade, you would be planning on going to 10gr2 or 11gr1 - not 9i, 9i

sizing the sga is a function of the database use, it depends on the manner in which you use it, the amount of physical memory installed is an upper bound - not something you plug into a formula. the OS may have other limits (you don't say what you are running - if 32bit windows, you cannot make a very large SGA)

shifting partition tablespace

A reader, November 03, 2007 - 3:22 am UTC

Hi Tom,

We have serious production system performance issues.
Database: 9.2.0
usage: OLTP during day time and batch processing during night
Top shows only 70 mb free memory and we have a little less than 5 gb of disk space.
OLTP transactions (35 users) are waiting for around 20 minites/transaction and the batch jobs are taking more than 12 hours to finish instead of the benchmark of 5 hours every night, which the business is definitely not going to afford and this is deteriorating day by day.

We did the following last night:
1. set the optimizer mode to FIRST_ROWS_10 during day time and back to ALL_ROWS during night before the batch run
2. set the ...index_cost_adj parameter to 30 and ...index cache to 90, number of open cursors to 500 (from 900)
3. Analysed tables and then schema and restarted the DB server (hard reboot) and
4. today we encountered some performance gain, but not enough
5. we also identified 2 mostly used tables with (multi-million records) which are partitioned and using the common user tablespace, which is 90% occupied.
6. we are trying to shift the tablespace for the partitioned table's tablespace to another disk for IO improvements - the server is on RAID 5.0, will it help us

Further, given this situation, what else do you recommend for performance gain?
Tom Kyte
November 05, 2007 - 11:17 am UTC

... Top shows only 70 mb free memory and we have a little less than 5 gb of disk
space. ...

so what???!?!?!?! That you state that as if it were a relevant fact.... so what?

I expect top to report 0 free, operating systems understand that memory is not a bankable resource (eg: you cannot save it up), you either

a) use it
b) lose it

so, they use it (file system cache will always fill all available memory as you use your disks, by design, this is what is supposed to happen)



you need to

a) stop
b) backup
c) figure out where your bottlenecks are


moving files, guessing, throwing darts at a board - none of this will do a thing for you.

and 99.99999999% of all performance gains are to be had by looking at the APPLICATION, not the database.

99.99999999%

A reader, November 06, 2007 - 12:40 am UTC

Hi Tom,

I liked that "99.99999999%"

Anyways, i just wanted to make sure that as a part of DBA activity we have done as expected.
we have taken the tkprof for the application events and analysing the queries. do you think creating partitions for a huge table which has 8 db of data would help? do we need to fine tune the application with the hint for it or just creating partitions will help?

Thanks again.
Tom Kyte
November 06, 2007 - 9:31 am UTC

partitioning a table will either

a) speed up some query
b) not affect the runtime
c) make it run many times slower


partitioning is just a tool, like indexing. think about your query and what would happen if you partitioned the data.


if you have access to Expert Oracle Database Architecture, I go over this - partitioning can sometimes, in certain cases, decrease the runtime of certain classes of queries.

Not a silver bullet.

No silver bullet for performance

Duke Ganote, November 07, 2007 - 12:46 pm UTC

demonstration of negative performance impact of over-partitioning:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:73703838188924#73882557016033

Table Reorganizations

GS, July 23, 2008 - 1:28 am UTC

Hi Tom,

Just a quick note to summarize what I have gathered from this thread, since it very much relates to my environment. Our database is about 500G and is growing rapidly. Also, I have been reading various documents on net on this issue, it would be great if you can confirm my thoughts.

1) For the sake of "temporarily" reducing/reclaiming size of database (tables, indexes etc), we should not as a "regular practice" use "shrink space/compact" commands, since the space gains/reclaims will soon be filled up again by oracle. Hence in few days time, we will have the same table with same size again ready for another "reclaiming excercise". Hence there is no real need of object reorganization (shrink space etc) using segment advisor for sole purpose of space reduction.

2) In order to serve the same purpose of space reduction, we should also not do "alter index .... rebuild online" as a "regular practice" since I have read on this site itself, that index tries to balance itself, i.e. even if we get our free space back, the object will try and get back to its previuos state (and hence its previous size).

3) There is no other way to get space back from object which should be used as "regular practice"

I want to clarify my thoughts on this, as I can read so many practices by DBAs to do this as a weekly task.

One final query, as a one off thing if we execute this "shrink space/compact" command on a huge table - partitioned/or unpartitioned, then I have noticed that there is huge resource consumption (CPU etc, too many archive logs generating), is that normal ? Are there quick ways to reduce these CPU or number of logs generated in a session for a particular maintaince task like this. Can you please guide me to a quick set of commands which can accomplish that (using anything e.g. Resource manager, resource limits or any other way which you think is most correct).

Many thanks for your time.

Best regards
Tom Kyte
July 24, 2008 - 10:04 am UTC

1) it depends.

if you load the table using direct path loads (insert /*+ APPEND */, sqlldr direct=y, etc), then the space reclaimed by shrinking would be "real" (direct path operations do not reuse existing allocated space under the high water mark).

but in most cases, yes, this is stated accurately.

2) for most indexes - yes.

consider an index on last name - you will be putting in data "randomly", it goes everywhere all across the leaf nodes - you insert some A's, some K's and some Z's haphazardly. That one doesn't need this COALESCE (think coalesce, not rebuild...)

consider an index on a date populated by sysdate, consider that you never delete from the underlying table. this will never need to be coalesce either.

consider an index on a date populated by sysdate whereby you delete MANY, but not all of some of the old rows. You'll never insert a "small" (old) date again, so the left hand side of the index might become "sparse". This is the type of b*tree index that might benefit from a coalesce over time - and how often is purely a matter of how rapidly you delete the old values, causing the spareness.


3) I don't know what you mean, you have rebuild, you have dbms_redefinition, you have coalesce, you have shrink, you have move - you have many ways?


"huge resource consumption".... think about this - if you do something 'big' (you said "big table"), think big - yes, it will use CPU, it will generate archives.

when you shrink, we need to be able to redo that operation in the event of media failure, you get what you ask for there - we need everything needed to replay that operation.

Table Reorg

GS, July 27, 2008 - 5:30 am UTC

Hi Tom,

Your answer is as I wanted (i.e. precise / to the point).

1) In my case there are no direct path loads (insert /*+ APPEND */, sqlldr direct=y, etc) on the "big" tables which I am referring to. Hence as advised in my case, if I use "shrink space/compact", the space gain would not be "real" and hence not advised to be done as a "regular excercise".

2) Yes I understand. Again in our case we do not delete any rows from those tables (and hence indexes). So, I can take that since MANY rows (actually none) are not getting deleted, so I will not benefit from COALESC (or rebuild online) in "long term space reduction".

3) Yes I understand what you mean here.

Now I also understand huge resource consumption reason in these operations on big tables.

If you can just confirm on points (1) and (2), if they are ok, that would be great.

Many thanks again for your time, Tom.

Regards,

GS

Tom Kyte
July 29, 2008 - 10:37 am UTC

1 and 2 sound right to me.

pb, August 01, 2008 - 2:53 am UTC

Hi...
I have one confusion,
"if you perform insert and delete operation then there is no need to reorganize table, because when you reorganize table it become of smaller size but again after sometime it get its original big size.">>
But after delete if we not reorganize the table then oracle scan all the block up to HWM, which decrease performance than why we not to go for a reorganization?
I don't have Index on the table. and if I create index on table then I have to ensure that it will do unique scan of index if I not go for a reorg. Am i right or wrong, I am confuse?
Tom Kyte
August 03, 2008 - 1:42 pm UTC

the "scan up to the high water mark" only applies if you.....

full scan frequently

otherwise, so what. And if the table is large and you full scan frequently, maybe we need to look at that.

But even so, if you "insert and delete" - meaning, the table doesn't have lots of empty blocks or near empty blocks because you INSERT.....


No, index unique scan would not be a requirement, just "index acces" would be. Unique is not relevant there.


But, if you delete frequently and find you need to reorganize frequently - you want to look at another way to do what you are doing.

Perhaps you meant to truncate.
Perhaps you meant to use partitioning to facilitate the purge.

Tables candidate for Reorg

Subbu, November 12, 2008 - 12:04 am UTC

How to find the tables/indexes that are good candidates for Reorg in 9i/10gR2 ? I am looking for SQL script to check this list of objects. I do not want to use any tools to find the candidate.


Tom Kyte
November 12, 2008 - 10:33 am UTC

then consider yourself done, finished.


Subbu, November 12, 2008 - 1:00 pm UTC

Tom

Can you show us how to check the tables that are good candidates for REORG through sql script (run via sqlplus).
Tom Kyte
November 13, 2008 - 4:48 pm UTC

tell you what

you define what characteristics would make a table be in need of reorg

and then we'll tell you how to 'find them'


they are as rare as one eyed albino kangaroo's.

Table reorg

Subbu, November 13, 2008 - 8:24 pm UTC

Tom,

I have to reorg for the following situation not on regular basis but whenever space crunch occurs:

1. Need to reclaim space due to large number of deletes from tables (white spaces).

2. To resize datafile smaller to make more room for the file system. SAN space allocation to mount point takes longer to get approval. So need to reorg some tables with lot of white spaces.
Tom Kyte
November 18, 2008 - 5:38 pm UTC

1) use partitioning, then you can just truncate and not have to worry about it. If you reorganize regularly, ask "why" and then look for a way to permanently organize the data to avoid that.

2) that means you must be dropping lots of stuff - or reorganizing. You could avoid this altogether using partitioning again. Drop old partition, drop tablespace containing old partition - done.

TABLE SIZE INCREASED by 1%....

lalu....., February 24, 2009 - 11:26 pm UTC

Hi Tom,

I have come across a typical scenarion.
I performed a reorg on a 50GB table with "alter table x move tablespace y"..and happend successfully.

But after the reorg i found the size of table increased by arround 1%.

No changes in the PCTincrease/free value or the extent sizes!And it happened for this table only.

Thanks.
lalu.....
Tom Kyte
February 25, 2009 - 6:28 am UTC

one of three things will happen when you reorganize a segment

a) it will be smaller
b) it will stay the same
c) it will grow.


All this means (your 1% growth) is that that table was "very utilized" and when you re-organized it, it became LESS utilized, you reintroduce the pctfree - and made the table larger.

eg: you inserted data - this filled table up to pctfree on each block. you updated data - made it grow (it now exceeds pctfree). you reorg and reintroduce the pctfree - it will 'grow'.

Very easy to reproduce:
ops$tkyte%ORA10GR2> create table t as select * from all_objects;

Table created.

ops$tkyte%ORA10GR2> update t set object_name = substr(object_name || 'xxx',1,30);

50079 rows updated.

ops$tkyte%ORA10GR2> commit;

Commit complete.

ops$tkyte%ORA10GR2> exec show_space( 'T' );
Unformatted Blocks .....................               0
FS1 Blocks (0-25)  .....................               0
FS2 Blocks (25-50) .....................               0
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................               0<b>
Full Blocks        .....................             688</b>
Total Blocks............................             768
Total Bytes.............................       6,291,456
Total MBytes............................               6
Unused Blocks...........................              60
Unused Bytes............................         491,520
Last Used Ext FileId....................               4
Last Used Ext BlockId...................          22,537
Last Used Block.........................              68

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> alter table t move;

Table altered.

ops$tkyte%ORA10GR2> exec show_space( 'T' );
Unformatted Blocks .....................               0
FS1 Blocks (0-25)  .....................               0
FS2 Blocks (25-50) .....................               0
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................               0
<b>Full Blocks        .....................             703</b>
Total Blocks............................             768
Total Bytes.............................       6,291,456
Total MBytes............................               6
Unused Blocks...........................              45
Unused Bytes............................         368,640
Last Used Ext FileId....................               4
Last Used Ext BlockId...................          22,921
Last Used Block.........................              83

PL/SQL procedure successfully completed.


Excellent......

A reader, February 26, 2009 - 3:40 am UTC

Thanks a ton a such a beautiful example.

selective reorg

A reader, June 15, 2009 - 9:06 am UTC

Hi,

Tom is it possible to do selective online re-org by any method. I need to selective data in a new table. When I try to do with DBMS_REDEFINITION, it does sync a whole data. Is there any way of doing selective online re-org.

create table qsn_app.dept_1 as select * from scott.dept;

alter table qsn_app.dept_1 add constraint dept_pk primary key (deptno);

select * from dept_1;

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON



create table qsn_app.dept_2 as select * from qsn_app.dept_1 where deptno in (10,20);


exec dbms_redefinition.start_redef_table (uname => 'QSN_APP',orig_table => 'DEPT_1', int_table => 'DEPT_2');

exec dbms_redefinition.sync_interim_table('QSN_APP', 'DEPT_1', 'DEPT_2');

exec dbms_redefinition.finish_redef_table('QSN_APP', 'DEPT_1', 'DEPT_2');

select * from dept_1;

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

Thanks,
Deepak


A reader, June 29, 2009 - 11:15 am UTC

Can we do a online re-org with DBMS_REDEFINATION with only selective records? For example a table contains 10 partitions and I want to do online re-org with DBMS_REDEFINATION and the final table should contain only 5 partitions. Would that be possible?
Tom Kyte
July 06, 2009 - 6:29 pm UTC

well, your "for example" is poorly worded for sure. I think what you mean is

I want to apply a where clause during the dbms_redefinition so that I can selectively pull records into the new structure based on a predicate.


Let me answer this "a table currently has 10 partitions and after the online reorg the final table should contain only 5 partitions". The answer to that is YES, of course, you have table t1 with 10 partitions, just create table t2 with 5 partitions and redefine away.

But that isn't what you seem to be saying - you want to select data from 5 partitions of the 10 partition table T1 and only move them.

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:17309172009134
read that, dbms_redefinition does not support a where clause, but you can do it "yourself"


but probably, what you want to do, is just drop 5 of the 10 partitions (or truncate or exchange them with empty tables to archive them) and then online redefine what is left - if you really need to reorganize at all (probably, you do not)

A reader, July 09, 2009 - 2:29 am UTC

Thank you very much Tom. That is what I wanted to ask. My example was poorly worded for sure. Sorry about that.


dependencies of reorganisation

ajit, May 26, 2010 - 8:18 am UTC

i have gone through the discussion, but i have a few questions about the reorg process, my situation is such.
i have a database on 9.2.0.8, there is a table x of size 200 GB, the row count of the table has been halved using delete but when i query user_segments i find the size to be 200GB,
To find the real size i used avg_row_len*num_rows, by which i found the size to be 78GB, I have collected stats for the table and the database is using CBO.Is this calculation right.
The second question is what are the dependencies of reorg process like, it will first load the data into temp tablespace so does the temp tablespace need to have 200GB of free space for me to accomplish this or is my understanding wrong on the dependencies of reorg process.
Regards
Ajit
Tom Kyte
May 26, 2010 - 8:58 am UTC

... i have a database on 9.2.0.8, there is a table x of size 200 GB, the row count
of the table has been halved using delete but when i query user_segments i find
the size to be 200GB,
...

because delete never releases space back from the segment - the blocks will still belong to the segment - they will likely be on the freelists (bitmap or otherwise) for the segment - and are available for reuse - but will remain owned by that segment.



...To find the real size i used avg_row_len*num_rows, by which i found the size to
be 78GB, I have collected stats for the table and the database is using CBO.Is
this calculation right.
...

which of course is way way WAY underestimated since you haven't taken block overhead, row overhead or anything into consideration... It'll be bigger. Do something like this:

create table temp as select * from big_table where rownum <= 100000;

and then look at the number of blocks used by temp, That'll tell you how much space you need for about 100,000 rows. Now multiply that up. That'll likely be closer to reality.


In order to answer the second bit - you would need to tell us the approach you are taking, you have alter table t move and dbms_redef.

If you use alter table t move, you need space for the old table and the new copy at the same time. When the move completes, we'll drop the old table - but you need space for both to exist simultaneously right there at the end.

Then you need to rebuild every index - which means you'll have two of each index in turn as you rebuild them.


It has nothing to do with the temp tablespace for the alter table t move - we do not use the temp tablespace - rather we allocate space for the new table as temporary extents in the permanent tablespace and convert those temporary extents into permanent extents at the end of the process.

You might need temp for your index rebuilds as we'll have to full scan, sort and write them out.


Online reorg - partitioned tables

S Banerjee, June 01, 2010 - 2:53 pm UTC

Hi Tom

I am doing an online reorg using the redefinition package..I have not seen any specific documentation or maybe i am not clear on this - what is the behavior when after i start my reorg and before it completes (ie the table flip at the end of the process) someone comes in and updates on the source table which causes a new partition to get added ? So, essentially during the reorg process the table structure has changed by adding a new partition. These tables and indexes are like a few hundred GB.


Thanks !

S
Tom Kyte
June 08, 2010 - 9:11 am UTC

.... (ie the table flip at the
end of the process) someone comes in and updates on the source table which
causes a new partition to get added ? ....

well, if you are trying to reorganize into a completely homogeneous data structure (the same exact layout) - that'll be a "do not do that" sort of operation. One would presume this new partition will contain data that maps to no existing partition in your temporary table - that would be a problem. This is a "big deal" sort of thing and would need to be coordinated with the other work taking place in the database.

ops$tkyte%ORA11GR2> CREATE TABLE t1
  2  (
  3    dt  date,
  4    x   int,
  5    y   varchar2(30),
  6    constraint t1_pk primary key(x)
  7  )
  8  PARTITION BY RANGE (dt)
  9  (
 10    PARTITION part1 VALUES LESS THAN (to_date('01-jan-2008','dd-mon-yyyy')) ,
 11    PARTITION part2 VALUES LESS THAN (to_date('01-jan-2009','dd-mon-yyyy'))
 12  )
 13  /

Table created.

ops$tkyte%ORA11GR2> CREATE TABLE t2
  2  (
  3    dt  date,
  4    x   int,
  5    y   varchar2(30),
  6    constraint t2_pk primary key(x)
  7  )
  8  PARTITION BY RANGE (dt)
  9  (
 10    PARTITION part1 VALUES LESS THAN (to_date('01-jan-2008','dd-mon-yyyy')) ,
 11    PARTITION part2 VALUES LESS THAN (to_date('01-jan-2009','dd-mon-yyyy'))
 12  )
 13  /

Table created.

ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> insert into t1 values ( to_date('01-jan-2008','dd-mon-yyyy')-1, 1, '1' );

1 row created.

ops$tkyte%ORA11GR2> insert into t1 values ( to_date('01-jan-2009','dd-mon-yyyy')-1, 2, '2' );

1 row created.

ops$tkyte%ORA11GR2> commit;

Commit complete.

ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> exec dbms_redefinition.can_redef_table( user, 'T1' );

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> exec dbms_redefinition.start_redef_table( user, 'T1', 'T2' );

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> alter table t1 add partition part3 values less than (to_date('01-jan-2010','dd-mon-yyyy'));

Table altered.

ops$tkyte%ORA11GR2> insert into t1 values ( to_date('01-jan-2010','dd-mon-yyyy')-1, 3, '3' );

1 row created.

ops$tkyte%ORA11GR2> select * from t1;

DT                 X Y
--------- ---------- ------------------------------
31-DEC-07          1 1
31-DEC-08          2 2
31-DEC-09          3 3

ops$tkyte%ORA11GR2> select * from t2;

DT                 X Y
--------- ---------- ------------------------------
31-DEC-07          1 1
31-DEC-08          2 2

ops$tkyte%ORA11GR2> exec dbms_redefinition.finish_redef_table( user, 'T1', 'T2' );
BEGIN dbms_redefinition.finish_redef_table( user, 'T1', 'T2' ); END;

*
ERROR at line 1:
ORA-42011: error occurred while completing the redefinition
ORA-12008: error in materialized view refresh path
ORA-14400: inserted partition key does not map to any partition
ORA-06512: at "SYS.DBMS_REDEFINITION", line 78
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1680
ORA-06512: at line 1
<b>
ops$tkyte%ORA11GR2> alter table t2 add partition part3 values less than (to_date('01-jan-2010','dd-mon-yyyy'));

Table altered.
</b>
ops$tkyte%ORA11GR2> exec dbms_redefinition.finish_redef_table( user, 'T1', 'T2' );

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> select * from t1;

DT                 X Y
--------- ---------- ------------------------------
31-DEC-07          1 1
31-DEC-08          2 2
31-DEC-09          3 3

ops$tkyte%ORA11GR2> select * from t2;

DT                 X Y
--------- ---------- ------------------------------
31-DEC-07          1 1
31-DEC-08          2 2
31-DEC-09          3 3




so, you'd need to account for that in your error handling.

Ask Something

Nilanjan Bhattacharya, June 13, 2010 - 3:57 am UTC

Can you tell me the reorganization of the tables in Finacle database?

TABLE REORG

Gautham, July 10, 2010 - 9:16 am UTC

Hi Tom,

Is there any way to re-org the clustered table without dropping the clusters. If so,Please help me in this...as all tables in my application has clusters.

Thanks
Tom Kyte
July 19, 2010 - 8:51 am UTC

you would typically need/want a new cluster to reorganize into.

or you can move the data OUT of the cluster, truncate the cluster and move it back in.

I would just create a new cluster and reorganize into it. If I truly needed to reorganize (I'd make sure of that first)

how table move works internally...

Rahul Sharma, February 14, 2011 - 9:57 pm UTC

Hi Tom,

As you advised to use -

alter table T move;

in my case i have a table 0f 17Gb in size and it is in STATSPACK tablespace which is having only 8GB free space.

In this case if I simply do "alter table T move;" then will it work or I need atleast 17GB free in STATSPACK tablespace?

As I do nt have 17GB free in any of the tablespaces, can you suggest how can i handle this reorg?

thanks a lot.


Tom Kyte
February 15, 2011 - 8:21 am UTC

In current releases, just use "alter table t shrink"

You won't even need to rebuild indexes.

You do have to be in an automatic segment space managed tablespace.


Alternatively - if you use the alter table t move, you would need enough space for the existing table (17g) and space for the new table (whose size I'm not aware of, it could be <17g, =17g, >17g - only you know....) at the same time.


Why are you reorganizing in the first place - usually, it is not necessary - so tell us WHY you are doing it and we'll help you from there

Reorging a composite partitioned table to just range

A reader, December 16, 2011 - 3:03 pm UTC

Tom - I've been giving the task of reorging a 230GB table wth 301 subpartitions by range, and then subpartitioned by hash for about 1000 partitions altogether. The purpose is to drop the hash supartitioning and redfine the table as range only (for reasons I do not know). I was thinking of using DMBS_redefinition - however that would require another 230GB of temporary storage, would it not? I'm being told there is no extra storage. What alternatives do I have?
Tom Kyte
December 16, 2011 - 6:02 pm UTC

do you need to do this online? can you have a downtime window?

Reorging table followup

A reader, December 17, 2011 - 12:56 pm UTC

If there has to be a downtime window, there has to be a downtime window. It hasn't been ruled out. Online would be preferable I think, but not absolutely necessary. Are there ways to do it either way?
Tom Kyte
December 18, 2011 - 5:01 am UTC

if you want to do it online, dbms_redefinition is the tool and yes, you'll have two copies of *everything*.

if you can do this offline, you'll

a) drop the existing indexes (this'll give you some space to work with)
b) create table as select or insert as select into a prebuilt table to reorganize the data in the table
c) drop the old table
d) reindex/grant/constraint/etc the new table


What if there is not enough room

A reader, December 18, 2011 - 9:36 am UTC

What if dropping the indexes does not supply me with 230GB of space that would be required to insert the data? Do I have to do it by separate partitions? Also, should I use an "append" hint on the insert as well?
Tom Kyte
December 18, 2011 - 10:20 am UTC

You could do it partitionwise. You could

a) create new empty table
b) create table tmp as select * from table - partition part1;
c) alter newtable exchange partition part1 with tmp
d) alter table oldtable drop partition part1;

repeat.

You can get a terabyte at best buy for under $100, your company seriously cannot scrape together this small amount of disk so you can do your job?


If you use insert as select, you would likely benefit from a direct path insert with appending - yes. You can set the segment nologging (alter table t nologging) before the operation to skip redo generation if you like as well.

Thank you Tom

A reader, December 18, 2011 - 11:35 am UTC

Thanks Tom.. I'm sure the leaders would tell me you can't buy SAN storage at Best Buy or something to that effect. Out of my hands anyway.

any way to remove composiite partitioning?

A reader, December 19, 2011 - 2:19 pm UTC

I know this question has you a bit played out since I'm getting silly arguments about disk space - but there is really now way to remove hash subpartitioning from a compositely range/hash table other than a reorg, is there?
Also, can you think of a performance rationale for removing hash subpartitiong in the first place? That may cross over into new question territory.. if it does I apologize.
Tom Kyte
December 19, 2011 - 5:43 pm UTC

but there is really now way to remove hash subpartitioning
from a compositely range/hash table other than a reorg, is there?


correct, every row must move from where it is to somewhere else.

Also, can you think of a performance rationale for removing hash subpartitiong
in the first place?


i can thinks of dozens - for each way - in favor of hashing it, and in favor of not hashing it.

It depends.

If I was to use an index to access the data in a range partition - I might well have to do N index range scans if the data was hash partitioned instead of 1 index range scan - for example.

A reader, December 27, 2011 - 8:41 am UTC

Hi Tom,

In batch process from last one week we have found that one process is taking one hour to open cursor previously it used to run for 5 mins only.This problem started when we did purging of cursor table.

Below is the DBA answer :


From our analysis too, the table XXXX is a good candidate for reorganization too since the actual size of the table (1.2 GB) is also less compared to the size occupied in the disk (5.8 GB). This would cause the index and table scans to read through all the deleted blocks thus causing a high number on reads.

Do you agree with this ???

Tom Kyte
December 27, 2011 - 9:00 am UTC

what is a "cursor table"


I agree that it would cause the FULL SCANS to read 5.8gb in order to read 1.2gb of real data.

I disagree that it would have an impact on index scans since an index scan gets a rowid (file.block.slotOnBlock) - an address of a row - and goes right to the row.

If you full scan this table frequently - an "alter table t shrink space compact" and "alter table t shrink space" might be called for - yes.


but, that said, I doubt that is the cause of something taking 12 times longer. That sounds more like a plan change, the DBA should look at what the plan used to be and what the plan is currently - hopefully they have maintained an AWR history for such purposes (if not, you'll probably never know).

A reader, December 27, 2011 - 9:06 am UTC

Hi Tom,

Yes we have index on this table.But what i have not understand that it previously also with more data it used to finish in 5 minutes.So now if it reads empty block also does it impact hugely like this ???
Tom Kyte
December 27, 2011 - 9:45 am UTC

the fact you have an index doesn't mean anything - if the index is not being used.


So now if it
reads empty block also does it impact hugely like this ???


if you are using an index to access the table, the empty blocks are not accessed.

if you are using a full scan to access the table, the empty blocks ARE accessed.

So, tell us, do you index access or full scan this.


And - the answer will probably not be "table is having lots of empty blocks", but rather "my query plan has changed and is dramatically worse than before". I'd investigate what the plan used to be and what the plan currently is to see if that is the cause.

Reorg

A reader, December 28, 2011 - 10:57 am UTC

Hi Tom,

Can we find table by running some query to find out which table need reorg ?
Tom Kyte
December 29, 2011 - 10:57 am UTC

use the segment advisors provided in Oracle Enterprise manager.

Online redefinition for Tables

Suresh.R, February 21, 2012 - 1:57 pm UTC

Hi Tom,

Thanks for your support.
I have a query for redfining one table in my database.
Please find the below my db details.

Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
PL/SQL Release 9.2.0.8.0 - Production
"CORE 9.2.0.8.0 Production"
TNS for HPUX: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production

Table Name: TABLE_CONTEXT_INST
Tables size : 730GB
Num of rows: 32 crore records
Tables Structure:
--------------------
desc TABLE_CONTEXT_INST;
Name                           Null     Type                                                        ------------------------------ -------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
OBJID                          NOT NULL NUMBER                                                      DEV                                     NUMBER                                                      NAME                                    VARCHAR2(20)                                                        VALUE                                   CLOB()                                                          CONTEXT_INST2GROUP_INST                 NUMBER                                                      CONTEXT_INST2PROC_INST                  NUMBER                                                      6 rows selected


When i try to redfine the above table to reclaim space it got failed with below error.

22:52:49 SQL> exec dbms_stats.gather_table_stats(ownname=>'SA',tabname=>'TABLE_CONTEXT_INST_NEW',degree=>2,cascade=>TRUE);
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.81
22:52:51 SQL> exec dbms_redefinition.can_redef_table('SA','TABLE_CONTEXT_INST');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
22:52:51 SQL> exec dbms_redefinition.start_redef_table('SA','TABLE_CONTEXT_INST','TABLE_CONTEXT_INST_NEW');
BEGIN dbms_redefinition.start_redef_table('SA','TABLE_CONTEXT_INST','TABLE_CONTEXT_INST_NEW'); END;
*
ERROR at line 1:
ORA-12008: error in materialized view refresh path 
ORA-01555: snapshot too old: rollback segment number 6 with name "_SYSSMU6$" 
too small 
ORA-06512: at "SYS.DBMS_REDEFINITION", line 8 
ORA-06512: at "SYS.DBMS_REDEFINITION", line 146 
ORA-06512: at line 1 
Elapsed: 04:20:07.13

I have tried when the load was very less in db and triggered the script near to 23hrs.

Am in a need to reclaim space in db since SAN is not avilable.
Could you please help me to solve this issue?
Thanks a ton for your help in advance.

Regards
Suresh.R

Tom Kyte
February 23, 2012 - 12:13 am UTC

your undo retention setting would have to be set higher than the amount of time it takes to initially copy this data - for consistent read purposes.

If your sole goal is to 'reclaim space', why not just shrink the table? it is online, it won't require a lot of extra space (online redefine will need to have two tables, two of each and every index - for some period of time).

Online redefinition

Suresh.R, February 23, 2012 - 7:07 pm UTC

Thanks a ton Tom for your reply.
But i hope we cant use shrink space in Oracle 9i.
Could you please confirm shrink space is available in 9i?
Also it is safe to shrink this big table in online?
Kindly help us to proceed further.
Thanks very much for your support.

Regards
Suresh.R

Tom Kyte
February 23, 2012 - 8:15 pm UTC

sorry, did not check your version before I wrote that. shrink was new in 10g.

it would be safe to shrink a big table online, it can only be done online.


Online redefinition

Suresh.R, February 26, 2012 - 6:56 pm UTC

Thanks a Ton for your answers Tom.
It was very useful for me to proceed further.
Thanks a lot.

Regards
Suresh.R

Oracle Online redefinition

Suresh.R, March 17, 2012 - 7:11 pm UTC

Hi Tom,

Thanks for your support.
We have increased the undo retention setting from 03 hrs to 06 hrs but we are facing error still with table defragmentation(oracle online redefinition).
01:29:28 SQL> exec dbms_redefinition.can_redef_table('SA','TABLE_CONTEXT_INST');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
01:29:28 SQL> exec dbms_redefinition.start_redef_table('SA','TABLE_CONTEXT_INST','TABLE_CONTEXT_INST_NEW');
BEGIN dbms_redefinition.start_redef_table('SA','TABLE_CONTEXT_INST','TABLE_CONTEXT_INST_NEW'); END;

*
ERROR at line 1:
ORA-12008: error in materialized view refresh path 
ORA-01555: snapshot too old: rollback segment number 22 with name "_SYSSMU22$" 
too small 
ORA-06512: at "SYS.DBMS_REDEFINITION", line 8 
ORA-06512: at "SYS.DBMS_REDEFINITION", line 146 
ORA-06512: at line 1 


Elapsed: 07:22:36.23
08:52:04 SQL> 

My Undo tablespace and setting details are

select sum(bytes)/1024/1024/1024 as Size_in_GB from dba_data_files where tablespace_name like '%UNDO%';
64.64453125

select sum(bytes)/1024/1024/1024 as Size_in_GB from dba_data_files where tablespace_name ='UNDOTBS1';
30.41796875

select sum(bytes)/1024/1024/1024 as Size_in_GB from dba_data_files where tablespace_name ='UNDOTBS2';
34.2265625

select name,value,description from v$parameter where name ='undo_retention';
undo_retention 21600 undo retention in seconds

Could you please help us to resolve this issue as we are in need to defragment this table.

Thanks & Regards
Suresh.R

Tom Kyte
March 19, 2012 - 10:03 am UTC

make it longer.



Elapsed: 07:22:36.23



apparently, it takes longer than 6 hours. Your undo retention needs to be set longer than that first step will take to complete.


Sounds like you might want to consider partitioning this table for the future as well - you might consider doing that while you are redefining it.

What is your goal behind this reorganization??

Online redefinition

Suresh.R, March 19, 2012 - 10:52 am UTC

Hi Tom,

Thanks a ton for your help.
Goal behind this is to reclaim the space for future use since there will be delay in SAN addition in production.

Thanks & Regards
Suresh.R
Tom Kyte
March 19, 2012 - 9:01 pm UTC

why not alter table t shrink space?

Online redefinition

Suresh.R, April 02, 2012 - 9:19 am UTC

Thanks for your support Tom.
As i told early we are in Oracle 9.2.0.8 version and shrink space will not work here.

Regards
Suresh.R
Tom Kyte
April 03, 2012 - 6:20 am UTC

I only read the current comment, I do not look all over to see if I discussed/receive information from some one over a month ago.

Online Redefinition

Suresh.R, April 02, 2012 - 9:31 am UTC

Hi Tom,

We have tried increasing the UNDO retention from 6hrs to 8hrs but still failed with same error.

17:38:35 SQL> BEGIN dbms_redefinition.start_redef_table('SA','TABLE_CONTEXT_INST','TABLE_CONTEXT_INST_NEW'); END;

*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-01555: snapshot too old: rollback segment number 21 with name "_SYSSMU21$"
too small
ORA-06512: at "SYS.DBMS_REDEFINITION", line 8
ORA-06512: at "SYS.DBMS_REDEFINITION", line 146
ORA-06512: at line 1


Elapsed: 09:55:25.38

Looks like increase in undo retention will not solve the issue.
Could you please help us fixing the issue with alternative solution(If you have any)?

Please find the below in detail.

desc TABLE_CONTEXT_INST;
Name Null Type 
------------------------------ -------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
OBJID NOT NULL NUMBER 
DEV NUMBER 
NAME VARCHAR2(20) 
VALUE CLOB() 
CONTEXT_INST2GROUP_INST NUMBER 
CONTEXT_INST2PROC_INST NUMBER 

6 rows selected

select table_name, last_analyzed, num_rows from user_tables where table_name='TABLE_CONTEXT_INST';
Table_name - TABLE_CONTEXT_INST
Last_Analyzed - 31/03/2012 08:13:52
Num_Rows - 316628630

Table Size= 812780 MB(793.730 GB).

select name,value from v$parameter where name ='undo_retention';
Name - undo_retention
Value - 28800

Thanks & Regards
Suresh.R

Tom Kyte
April 03, 2012 - 6:23 am UTC

then, obviously, it takes longer than 8 hours - doesn't it?

Since you are 9 hours and 55minutes into it - answer: YES

Therefore, obviously 8 hours of undo retention - which is less then the time it takes to copy this table - is *not sufficient*

Looks like increase in undo retention will not solve the issue.


how the heck did you come to this incorrect conclusion? You've set the undo retention too low for what you are trying to do.

If you want to do the online redefine, increase your undo retention. Maybe temporarily set it to five days (maximum) - do the online redefine - set it back to what you want it to be.

Online redefinition

Suresh.R, April 03, 2012 - 9:04 am UTC

Thanks a lot for your support Tom.
It really helped a lot to proceed further but i would like to know whether do we have an any other option to defragment the table without an outage in Oracle 9.2.0.8 version - Could you please let me know if we have any other option?

Thanks & Regards
Suresh.R
Tom Kyte
April 03, 2012 - 1:10 pm UTC

nope, you do want to partition this table for the future, that is pretty much obvious here.

but if you want an online reorganization - you would set up enough undo spcae to allow dbms_redefinition to do its job.

Online Redef & Partition

Suresh.R, April 05, 2012 - 8:23 am UTC

Thanks a lot for your help Tom.
Again to create the partition for existing table without an outage we may need to go for online redefinition to copy the data after the partition table created- Kindly correct me if am wrong anywhere.

Thanks & Regards
Suresh.R
Tom Kyte
April 06, 2012 - 10:18 am UTC

if you don't want downtime, you would use dbms_redefinition, yes.

Online redefinition defragmentation

Suresh.R, May 06, 2012 - 12:19 pm UTC

Thanks a ton for your support Tom.
One question is that when i do online redefinition defragmentation the dependent views,Packages (all objects) is changing to invalid status.
Do i have any specific commands to avoid the dependent objects to invalid status.

Example:- After creating interim table, i use following to perform redefinition

exec dbms_redefinition.can_redef_table('SA','TABLE_RQST_PENDING');
exec dbms_redefinition.start_redef_table('SA','TABLE_RQST_PENDING','TABLE_RQST_PENDING_NEW');

CREATE INDEX "SA"."IDX_PENDING2RQST_INST" ON "SA"."TABLE_RQST_PENDING_NEW" ("PENDING2RQST_INST")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 20971520 NEXT 20971520 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "INDEX11" ;

CREATE INDEX "SA"."RQST_DIVIDER" ON "SA"."TABLE_RQST_PENDING_NEW" ("DIVIDER")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 20971520 NEXT 20971520 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "INDEX11" ;

exec DBMS_REDEFINITION.SYNC_INTERIM_TABLE('SA','TABLE_RQST_PENDING','TABLE_RQST_PENDING_NEW');
exec dbms_redefinition.finish_redef_table('SA','TABLE_RQST_PENDING','TABLE_RQST_PENDING_NEW');

After this all its dependent objects going to Invalid status.
Could you please help me to resolve this issue?
Many thanks.

Regards
Suresh.R
Tom Kyte
May 06, 2012 - 3:31 pm UTC

redefinition involves changing of the object identifier - there is a new object that the bits of code are dependent on. also, dbms_redefition can change the structure, names, pretty much anything (it can involve transformations).

It will do invalidations. Maybe less and less as time goes on - but in general, it has to invalidate.

Table re-organisation

Suresh.R, May 18, 2012 - 4:14 am UTC

Thanks a ton for your help Tom.
I have final question on defragmentation.
Could you please confirm whether defragmentation time taken depends on total number of records present on table?

Example:
--------
I have following:-

Table Name: TABLE_FLD_INST

Desc TABLE_FLD_INST;
---------------------
Name Null Type
------------------------------ -------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
OBJID NOT NULL NUMBER
DEV NUMBER
FLD_NAME VARCHAR2(128)
VALUE VARCHAR2(255)
IS_PENDING NUMBER
FLEX_OWNER_OBJID NUMBER
TYPE NUMBER

FLD2SVC_FLD NUMBER
FLD2RQST_INST NUMBER

9 rows selected

Num of rows - 800000000

Tables size includes Index & LOB - 400GB

Does it take much time depends on the number of records present on the table - Kindly confirm?
Many thanks.

Regards
Suresh.R
Tom Kyte
May 21, 2012 - 7:47 am UTC

Could you please confirm whether defragmentation time taken depends on total
number of records present on table?


let us not call is "defragmentation". Let us call is "reorganization"


It will be a function of the existing segment size and existing size of the rows. Not the number of rows by itself (the number of rows is a meaningless metric - it says nothing about the SIZE. I can have a table with 10 rows that is larger than your table with 1,000,000 rows).


If there had been 1,000,000,000 rows in the table - and now there is just one - there will be one row - but enough space allocated to hold 1,000,000,000 rows and we'll have to scan that space.

and then write one row.


If there had been 1,000,000,000 rows in the table and now there is 500,000,000 - we'll have to scan the space it took to hold 1,000,000,000 rows and then write 500,000,000 of them.... However much data that is depends on the row widths :)

Database reorg

parag, April 01, 2013 - 10:09 am UTC

Hi,

I want to resize the full DB which method should we use for expdp, online table redefination or move tables to another tablespace?

What is the advantage of the dbms_redefination over other two methods.

Thanks


Tom Kyte
April 22, 2013 - 12:53 pm UTC

define "resize full db"?


I don't know what it means to resize a database.


if you are saying "I'd like to make it as small as possible", then I would use alter move/alter rebuild to move everything in tablespace A to tablespace NEW_A and drop A afterwards. IF that cannot be done as an offline operation (the objects would be unavailable during the move) - then use dbms_redefinition to move them instead.


never take stuff out of the database and then try to put it back in - too big of a chance of messing something up and it'll be typically the slowest approach.

Is Stats collection required after Table reorg ?

Paul, March 23, 2017 - 10:07 pm UTC

We are planning to do offline reorg (alter table move/index rebuild) for selective partitioned tables.

After reorg completion, Can you Please advise if it is really required to gather stats on those tables/index (global stats & partition stats)?

If its advisable, any reason behind it ?
Connor McDonald
March 25, 2017 - 3:41 am UTC

I would recommend it because I imagine the *motivation* for the reorg to fundamentally change something about the structures (compression, reclaim space, re-order data etc).

Which means the resultant statistics would dramatically different.

If the resultant statistics were not expected to be different, I'd be pausing and asking myself if I need to do the reorg in the first place.

Thanks.

Paul, March 27, 2017 - 5:41 pm UTC

We are doing reorg to release space as the tables are heavily fragmented.