Free space of tablespace deleting table content (rows)

oracleoracle-12ctablespaces

I have a theorical question, last week we have found this error in one database. The error has been given by our app when trying to save some WS data.

ORA-01691: unable to extend lob segment RS2.SYS_LOB0000501482C00010$$ by 128 in tablespace RS2QDDA1

In absence of the dba who has de sysdba rights we have a discussion :

If we delete data of some tabla stored in this tablespace, the tablespace will have free space ? And could be back to work for the application.

We questions that because we configure a purge batch to delete old rows from some tables, but it fails in execution time throwing the same error message defined before ORA-01691: unable to extend lob segment RS2.SYS_LOB0000501482C00010$$ by 128 in tablespace RS2QDDA1

Best Answer

Without understanding the nature of the data contained within this object contains, it is difficult to offer solid advice.

If this were a regular table, then yes; you would delete rows from it and thereby release space within the Tablespace.
The fact that a delete statement results in more data being written to this object makes me think it is non-standard; perhaps an "Auditing" feature of some sort that records everything - including deletions. That means your only option is to give the Tablespace more room to work with, by adding more datafiles to it or by extending the underlying disk space.

Thinking about performing either operation without your DBA?

My recommendation: Don't.

Very little about Oracle database is as straightforward as we would like it to be, so you need someone who knows what they're doing (and knows what to do when it goes wrong).

Furthermore, you shouldn't even be able to!

If your DBA has given you sufficient privileges to even attempt this operation, then they need their head examining, having little or no concept about database Security (or, most generously, a highly over-developed sense of Trust).

Related Solutions

ORA-01654: unable to extend index by 128 in tablespace

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 great post 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...

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?
You're not specifying a tablespace when creating the index and it's not using the one you think, 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.

Oracle IMPDP job ORA-30036 unable to extend segment by 8 in undo tablespace ‘UNDOTBS1’

Ensure that the default undo tablespace has enough capacity.

Based on your output, I see 3 tablespaces with 2GB of size. Perhaps one of them is the default assigned UNDO and auto extend is off.

Another prediction is that the default undo could be another tablespace with a different name, the query executed does not indicate the default undo but rather checks the tablespaces named with 'UNDO'.

You can check the default undo tablespace using the below query

select value from v$parameter where name like 'undo_tablespace';

Best Answer

Related Solutions

ORA-01654: unable to extend index by 128 in tablespace

Oracle IMPDP job ORA-30036 unable to extend segment by 8 in undo tablespace ‘UNDOTBS1’

Related Question