Solved

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

Posted on 2006-06-16
12
34,674 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
  • 5
  • 5
  • 2
12 Comments
 
LVL 19

Expert Comment

by:actonwang
Comment Utility
>>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
Comment Utility
>> 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
Comment Utility
0
 
LVL 16

Expert Comment

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

Author Comment

by:spinbains
Comment Utility
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
Comment Utility
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:spinbains
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
>>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
Comment Utility
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Via a live example, show how to take different types of Oracle backups using RMAN.

728 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now