1

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

2 Answers 2

2

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).

1

One point to be wary of: tables (also indexes, materialised views ..) are allocated more space as you INSERT data into those tables. When you DELETE, that space is only freed inside those allocations. Meaning that the space will be reused when you insert new rows into that same table. But it will not be reused by other objects (other tables for example).

The same is true for indexes: in addition, indexes always grow due to the regular balancing (leading to node splits and more node creation). Indexes also never shrink due to deletions.

If you want to free space by removing rows from a table, so other objects (tables, indexes) can reuse that space ... well, you cannot. The only exception is if you want to empty a table totally. In this case, use:

TRUNCATE TABLE my_big_table;

instead of

DELETE FROM my_big_table;
COMMIT;

Watch out: you cannot ROLLBACK a TRUNCATE operation! So make sure you pick the right table in the right database and schema!

TRUNCATE has the effect of releasing all space allocated to that table, making it available to everyone. DELETE only wipes rows off the blocks, only making that space available to the same table.

If you want to retain data in your table, then after deleting the rows you no longer need, create a new table from the remaining rows, drop the original table, rename the new table to the original name and add all the constraints and indexes back. This will make the space used by the old table available for other tables or indexes.

This will NOT make space available on the file system. Meaning that your tablespace will still occupy the same space. If you need to make space available at that level, you can do so by shrinking the data files that correspond to that tablespace:

ALTER DATABASE DATAFILE 'your file name' RESIZE n GB;

That however only allows you to shrink the file by removing the unused blocks at the end, i.e. down to the high watermark. It is enough to have one small table just using one block at the end of the datafile to make it unshrinkable. You can find out how much you can shrink a datafile using this:

-- Check possible shrinkage
select file_name, hwm, blocks total_blocks, blocks-hwm+1 shrinkage_possible
from dba_data_files a,
     ( select file_id, max(block_id+blocks) hwm
         from dba_extents
        group by file_id ) b
where a.file_id = b.file_id;

Note that, as Phill explained, you are not allowed to do those things as a regular user. Only a DBA can do those. So best is to approach your DBA with your space issues.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.