I loaded up a 450M data file into the database and found out that it was taking too much hard drive space. So when I did a delete from tablea; the data got deleted but the hard drive showed the same amount of space being occupied by the database and no less.
What could be the problem?
Im running oracle in a Linux box and do a df to find out the space remaining.
When creating tablespace in Oracle. The rdbms allocates the size of the tablespace given in disk space.
When creating a table in the segment of the tablespace , the rdbms allocates an extent inside the tablespace for the specific table. This extent will handle all of the table's needs (insert,delete except update) and the rdbms will allocate another extent if needed.
If you delete data from the table, you will not reduce the size of the datafile file for a certain tablespace.
if you want to reduce disk space simply drop a tablespace.
ok heres the catch.
For some odd reason I created the tables under the SYSTEM tablespace.
Could I get rid of the SYSTEM tablespace?
If yes how?
If not, could I try moving the tables to a different tablespace, would that help and how?
No you cannot remove the SYSTEM tablespace if you still want your database to function. You created those objects there because you didn't specify another tablespace on which to place those objects when you created them. First you will want to create a tablespace or tablespaces for the use of objects that do not belong to SYS. You will need to re-create your objects on the new tablespace and either export/import the data from the old to the new or insert/select from the old to the new. FYI deleting data from a table does not relinquish the space consumed by the table. To do so, you can drop the table as previously suggested, or you can truncate the table.