Solved

Defragment 9i Oracle Tablespace using alter table move, alter index rebuild, alter table move lob

Posted on 2006-06-16
12
34,728 Views
Last Modified: 2011-08-18
Using Oracle 9.2.0.5 Enterprise Edition:

I have a fragmented tablespace. I have created a new tablespace.
I have used dba_extents to create queries and scripts to identify segment_types of table, index and lobsegments.
I have executed the following in this order:
1) alter table [table_name] move tablespace [new tablespace] nologging;
2) alter index [index_name] rebuild tablespace [new tablespace] nologging;
3) alter table [table_name] move lob ([lobsegment_name]) store as (tablespace [new tablespace]) nologging;.

As the scripts are processing  I can see the new tablespace increase in size/use and the former tablespace decrease i
n size/use.

At the end there is still about 200M left in the former tablespace and I can query dba_extents to identify the segment_name, segment_type.
When I executed the following: drop [former tablespace_name] including contents and datafiles;
the former tablespace dropped including the remaining 200M but nearly all the contents dropped from the new tablespace as well.

1) Why isn't everything being moved from the former tablespace to the new tablespace?
2) Why did the objects now existing int he new tablespace drop when I dropped the former tablespace including contents and datafiles;

I'm able to duplicate everytime. I back up the database before the re-org above, execute the scripts, then restore the database prior to the re-org.


0
Comment
Question by:spinbains
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
  • 2
12 Comments
 
LVL 19

Expert Comment

by:actonwang
ID: 16925203
>>1) Why isn't everything being moved from the former tablespace to the new tablespace?

      You must leave sth out there in your old tablespace by accident. To check if anything left in old tablespace, you can use:

      select segment_name from dba_segments where tablespace_name = '<old ts name>';

      to check if anything is still there.
       
     
0
 
LVL 19

Expert Comment

by:actonwang
ID: 16925206
>> 2)Why did the objects now existing int he new tablespace drop when I dropped the former tablespace including contents and datafiles;
     if you just move index or lob segment to a new tablespace and keep table object in old tablespace, it will happen.
     so make sure you move everything over from old tablespace to new tablespace.

     Here is the article you might find very useful to make your reorg easier:

http://asktom.oracle.com/pls/ask/f?p=4950:8:14623854673229129399::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:47812348053


acton
0
 
LVL 16

Expert Comment

by:MohanKNair
ID: 16926646
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 16

Expert Comment

by:MohanKNair
ID: 16926647
Also see this link
Automated Table/Index Reorganization In Oracle8i
http://www.dbazine.com/oracle/or-articles/hordila1
0
 

Author Comment

by:spinbains
ID: 16935473
In reply to actonwang 6/16/06; Per my original entry, I am moving item in this order 1) table 2) index 3) lob
0
 
LVL 19

Expert Comment

by:actonwang
ID: 16935582
the order is good (actually not very important).

did you run :

select segment_name from dba_segments where tablespace_name = '<old ts name>';

to check if anything left on your old tablespace?

also you can use same kind of query to make sure you transfer all objects to the new segment.

acton
0
 

Author Comment

by:spinbains
ID: 16935993
To Actonwang:

I've restored from the backup and have re-executed the 1) Move table 2) Rebuild Index. I'me re-executing the 3) alter table move lob and will let you know what remains in the original tablespace.

To all that sent info via links: I'd found and read most of these before. However, they don't mention too much about LOB's. I did want to know that I also came across the ORA-28650: Primary index on an IOT cannot be rebuilt which is also mentioned in the articles.
0
 

Author Comment

by:spinbains
ID: 16937932
I wanted to give everyone an update. I'm now at the place where all tables, indexes, lobindexes and lobsegments reside in the new tablespace. The only object remaining is the IOT mentioned above - when I execute "alter index metastorm.IDX_EFOLDERID rebuild tablespace bains nologging;" I rec'd the ORA-28650.
0
 
LVL 19

Expert Comment

by:actonwang
ID: 16939069
you can not rebuild index on pk of IOT. What you need to do with IOT table is:

alter table <iot table name> move tablespace <new ts> overflow tablespace <ts>;

index segment will be moved to new ts and rebuilt.

hope it helps.

Acton
0
 

Author Comment

by:spinbains
ID: 16943129
Hello Actonwang;

The IOT is called metastorm.edi_tracker_834update.

I executed the following:

alter table metastorm.edi_tracker_834udpate move tablespace bains overflow tablespace bains;

rec'd ORA-01429: Index-Oriented Table: no data segment to store overflow row-pieces.
0
 
LVL 19

Accepted Solution

by:
actonwang earned 250 total points
ID: 16943332
>>alter table metastorm.edi_tracker_834udpate move tablespace bains overflow tablespace bains;

just use this (you don't need overflow):

alter table metastorm.edi_tracker_834udpate move tablespace bains;
0
 

Author Comment

by:spinbains
ID: 16943671
Hello Actonwang,

I'm going to export the IOT metastorm.edi_tracker_834update, drop the tablespace metastorm including contents and datafiles, and create new metastorm tablespace move tables, indexes, lobsegments to new metastorm tablespace and then import metastorm.edi_tracker_834update. And see how that works.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question