Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 34817
  • Last Modified:

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

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
spinbains
Asked:
spinbains
  • 5
  • 5
  • 2
1 Solution
 
actonwangCommented:
>>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
 
actonwangCommented:
>> 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
 
MohanKNairCommented:
0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
MohanKNairCommented:
Also see this link
Automated Table/Index Reorganization In Oracle8i
http://www.dbazine.com/oracle/or-articles/hordila1
0
 
spinbainsAuthor Commented:
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
 
actonwangCommented:
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
 
spinbainsAuthor Commented:
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
 
spinbainsAuthor Commented:
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
 
actonwangCommented:
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
 
spinbainsAuthor Commented:
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
 
actonwangCommented:
>>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
 
spinbainsAuthor Commented:
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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 5
  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now