Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am not a DBA but have to resolve this issue.

There are 2 permanent tablespaces in my Oracle 9i database. One for the tables and the other for the Indexes other than the Temporary and UNDO tablespaces

Last week I got the exception

ORA-01654: unable to extend index <name of the index> by 128 in tablespace <name of the Index tablespace>.

I got the tablespace size increased considerably. But still I am getting this error.

I deleted a lot of data thinking that would free-up some space. But that’s of no use.

Will shrinking the tablespace or re-sizing the datafiles of the permanent tablespace resolve this issue?

Also, the statistics hasn’t been gathered since March. Will gathering the latest statistics help me in any way?

share|improve this question

migrated from stackoverflow.com Jan 11 '12 at 13:45

This question came from our site for professional and enthusiast programmers.

1  
what's the size of the datafile? Is autoextend enabled? –  Sathya Jan 11 '12 at 8:46
    
@Sathya, autoextend can be evil unless you've got a lot of room on the disks. Also, depending on whether the disks are SSDs. If they're not then you want to spread the tablespace datafiles over multiple disks to aid parallelism, which autoextend will not do automatically. –  Ben Jan 11 '12 at 8:54
    
I am also facing similar issue... I too am not a DB person or a DBA... But, would like to understand why this issue is coming up? Can it be because a query is not properly tuned/optimized? Or because we have some dirty code that is inserting more than required data into DB? –  user15592 Nov 24 '12 at 8:23
    
Hello SGD and welcome to DBA.SE. This is not a forum, if you have a question of your own, please use the Ask Question button at the top, and make sure that you've searched beforehand for similar questions and provide enough detail for your specific case. Only post an answer if you're actually answering the question asked. –  Mat Nov 24 '12 at 11:40

2 Answers 2

You can reclaim some space on your largest tables with:

ALTER TABLE xxx ENABLE ROW MOVEMENT
ALTER TABLE xxx SHRINK SPACE
ALTER TABLE xxx DISABLE ROW MOVEMENT

Note that you will probably have to rebuild some objects after that.

share|improve this answer
    
Thanks Benoit for the statements.:) –  Savitha Jan 11 '12 at 9:28

Deleting data will not make any difference as Oracle will have kept the space ready to be re-filled. The top space usage in a table is known as the high water mark. Tom Kyte has a really good (unsurprisingly) post here about it.

You reduce the high water mark by rebuilding the table:

alter table my_table_name move

If you're still getting the error after increasing the size of the tablespace there are a couple of possible reasons...

  1. The index is too big even for the newly increased tablespace. How big is the table and what are the combined size of the columns you're indexing?
  2. You're not specifying a tablespace when creating the index and it's using a different one, whatever the default tablespace for your schema is.

The answer to 1 to is add more space.

To find out your default tablespace: select * from dba_users where username = 'MY_USER'. If this is not your index tablespace you have to specify it when creating the index.

create index i_my_table 
    on my_table ( my_column ) 
       tablespace my_index_ts
       < other options >

Yes, if you're in 9i it's definitely worth collecting statistics as it doesn't collect them automatically when enough changes have been made to the table - it does in later versions. Use dbms_stats.gather_table_stats and only do this after rebuilding the table.

Unfortunately in Oracle you can't reduce the size of a tablespace. To use this option you'd have to re-create the tablespace, but smaller, and move everything to it.

share|improve this answer
    
Thanks for enlightning me on this.I don't have the DBA rights. Now,I can ask for the changes depending on this explanation and get it done.I shall do that and update the thread.:) –  Savitha Jan 11 '12 at 9:25

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.