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.