This post describes some basic Oracle database management issues database administrators might be confronted with. It is about space management and differences observed while monitoring the available space from several point of views (tablespace/datafile) in Grid Control 11g. It also relates and explains an issue (ORA-01652) observed on pre 11.2 Oracle releases (targets) concerning the tablespace/datafile space management and the recycle bin.
First of all, a simple example: let’s check the used and available space in a tablespace (e. g. a Grid 11g repository database):
Let’s focus on the MGMT_AD4J_TF tablespace which is not automatically extensible (“Auto Extend” : NO) since the unique datafile has been set on “autoextend off”:
SQL> alter database datafile '/u01/oradata/GRIDREP/mgmt_ad4j.dbf' autoextend off;
Database altered.
As for the tablespace, the datafile, has of course also 24.3 MB of used size (see the “datafile” view in the grid below):
In order to see the space allocation, you simply need to create a table “NEW_TABLE” with the following statement, connected as “SYSTEM” (simply join two existing tables/views in order to create a “significant” table):
SQL> create table new_table tablespace MGMT_AD4J_TS as select * from v$parameter, user_tables;
Table created.
As expected, the used space at datafile and tablespace level climbed up to 47.3 MB (focus on MGMT_AD4J_TS):
The datafile view reports the same value.
If we now drop the table “NEW_TABLE” we will see the used space which will reduce at tablespace level:
SQL> drop table new_table;
Table dropped.
We go back to 23.4 MB used for MGMT_AD4J_TS:
However at datafile level, the Grid Control still shows 47.3 MB of used size:
Oracle is not giving the space back to the datafile?
No, in fact, the table NEW_TABLE has not been removed but was put in the reclycle bin:
SQL> select SEGMENT_NAME,TABLESPACE_NAME,BYTES from dba_segments where segment_name like 'BIN%';
SEGMENT_NAME TABLESPACE_NAME BYTES
------------------------------ --------------- ---------
BIN$p4pKihyEmcXgQLQKRoE88A==$0 MGMT_AD4J_TS 24117248
SQL> select OBJECT_NAME,ORIGINAL_NAME,TS_NAME,CAN_PURGE,SPACE from recyclebin where OBJECT_NAME = 'BIN$p4pKihyEmcXgQLQKRoE88A==$0';
OBJECT_NAME ORIGINAL_NAME TS_NAME CAN SPACE
---------------------------------------------------------------
BIN$p4pKihyEmcXgQLQ.... NEW_TABLE MGMT_AD4J_TS YES 2944
The segment for the recycle bin object is in the tablespace MGMT_AD4J_TS itself.
Now, let’s create a much bigger object in the tablespace and observe the tablespace/datafile used space. The following “create table” statement creates such a “big” table:
SQL> create table new_table_2 tablespace MGMT_AD4J_TS as select * from v$parameter, dba_tables;
Table created.
As expected, the used space at tablespace level climbed up to 384.3 MB:
We have a slight difference with the space reported at datafile level, since the datafile still contains the recycle bin object of the removed table (“NEW_TABLE”), therefore the consumption is 407.3 MB and not 384.3 as for the tablespace:
We can now delete this large object (with “drop table”) and observe the used space. At tablespace level the space has been given back (as free space):
At datafile level, we are still using 407.31 MB!
This is due to the Recycle Bin segments:
SQL> select SEGMENT_NAME,TABLESPACE_NAME,BYTES from dba_segments where segment_name like 'BIN%';
SEGMENT_NAME TABLESPACE_NAME BYTES
-------------------------------------------------------------
BIN$p4pKihyMmcXgQLQKRoE88A==$0 MGMT_AD4J_TS 377487360
BIN$p4pKihyJmcXgQLQKRoE88A==$0 MGMT_AD4J_TS 24117248
How does Oracle handle this situation?
Can we create a new large object in this tablespace which will be 377 MB big, even if 143 MB only are available in the datafile (but 525MB are free at tablespace level)?
Let’s try.
SQL> create table new_table_3 tablespace MGMT_AD4J_TS
as select * from v$parameter, dba_tables;
Table created.
As you can see, the object has been successfully created.
What happened with the Recycle bin? Since Oracle had to create the tablespace with this new object, it decided to automatically purge the recycle bin during the creation of the new table to get the required space, therefore we have no more objects in the recycle bin:
SQL> select SEGMENT_NAME,TABLESPACE_NAME,BYTES
from dba_segments where segment_name like 'BIN%';
no rows selected
Bug causing ORA-01652
Note however that in Oracle releases before 11.2 (any release might be affected), a bug occurred in this situation (tablespace full filled with Recycle Bin segments). The creation of new segments was not prevented but the following error occurred in the alert.log file:
ORA-01652: unable to extend temp segment by 8 in tablespace TBS1
Mon Jul 4 16:25:40 2011
Errors in file /u00/app/oracle/admin/DB1/udump/chtx2_ora_27533.trc:
ORA-01652: unable to extend temp segment by 8 in tablespace TBS1
Mon Jul 4 16:34:47 2011
This bug is referenced by the Oracle Support Note 6977045.8. If you are confronted with this bug, you have to follow one of these workarounds:
- Turn off the recycle bin by setting the Oracle init parameter “recyclebin = off” in the spfile
or
- Purge the recycle bin regularly, for instance each day or week using the “purge recyclebin” SQL statement
Note that, unfortunately, Grid Control 11g has no possibility to control the size of the recycle bin and does not even have a metric to monitor the recycle bin size.
Post Views: 3,536
![Thumbnail [60x60]](https://www.dbi-services.com/blog/wp-content/uploads/2022/08/YAN_web-min-scaled.jpg)
![Thumbnail [90x90]](https://www.dbi-services.com/blog/wp-content/uploads/2022/08/DWE_web-min-scaled.jpg)
![Thumbnail [90x90]](https://www.dbi-services.com/blog/wp-content/uploads/2023/09/SGR_web-2.jpg)