-
get rid of extents on table and index
Hi there. I have the following problem. I have a table and an index with too many extents due to a delete problem in this table, which is cured now. Now I only need to get rid of the extents. I was thinking to export the table and index, delete the table and index and import the table and index again. There are no constraints on this table whatsoever. Can someone tell me if this is the correct way, maybe add some parameters with it? Thnx!
-
You can use
.. import/export
.. create table as select ...
All will work fine.
Regards
Last edited by jovery; 07-10-2003 at 05:54 AM.
Jim
Oracle Certified Professional
"Build your reputation by helping other people build theirs."
"Sarcasm may be the lowest form of wit but its still funny"
Click HERE to vist my website!
-
So, in other words, using the create table as select, I should do this twice? because first I need to get the data to a temptable, drop the old table and create the new table from the temptable, since it should have the same name? After that I can create the index and all will be fine?
-
Depending on your version you should be able to
Code:
CREATE TABLE newtable AS SELECT * oldtable;
DROP oldtable;
ALTER TABLE newtable RENAME TO oldtable;
Recreate you indexes.
HTH
Jim
Oracle Certified Professional
"Build your reputation by helping other people build theirs."
"Sarcasm may be the lowest form of wit but its still funny"
Click HERE to vist my website!
-
If you are on 8.1+, you can use ALTER TABLE MOVE for the table and ALTER INDEX REBUILD for the index. For example:
Code:
system@nt817.us> create table xyz (x number(10), y date, z varchar2(20)) storage (minextents 20);
Table created.
Elapsed: 00:00:00.80
system@nt817.us> select segment_name, count(*) from dba_extents
2 where segment_name = 'XYZ'
3 group by segment_name
4 /
SEGMENT_NAME COUNT(*)
------------------------------ ----------
XYZ 22
Elapsed: 00:00:00.00
system@nt817.us> alter table xyz move storage (minextents 1);
Table altered.
Elapsed: 00:00:00.21
system@nt817.us> select segment_name, count(*) from dba_extents
2 where segment_name = 'XYZ'
3 group by segment_name
4 /
SEGMENT_NAME COUNT(*)
------------------------------ ----------
XYZ 3
Elapsed: 00:00:00.00
system@nt817.us> create unique index xyz_uk1 on xyz(x) storage (minextents 20);
Index created.
Elapsed: 00:00:00.01
system@nt817.us> select segment_name, count(*) from dba_extents
2 where segment_name = 'XYZ_UK1'
3* group by segment_name
system@nt817.us> /
SEGMENT_NAME COUNT(*)
------------------------------ ----------
XYZ_UK1 20
Elapsed: 00:00:00.00
system@nt817.us> alter index xyz_uk1 rebuild storage (minextents 1);
Index altered.
Elapsed: 00:00:00.40
system@nt817.us> select segment_name, count(*) from dba_extents
2 where segment_name = 'XYZ_UK1'
3 group by segment_name
4 /
SEGMENT_NAME COUNT(*)
------------------------------ ----------
XYZ_UK1 1
Elapsed: 00:00:00.10
Jeff Hunter
-
You can also try the following:
ALTER TABLE MYTABLE DEALLOCATE UNUSED KEEP
32K;
and
ALTER INDEX MYINDEX DEALLOCATE UNUSED KEEP
32K;
NOTE: the 32K forces deallocate unused space at the end of the table.
"The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|