SQL & PL/SQL

Hi,

I want to drop some schemas in a database I have for testing purposes, as I only have 25GB remaining on my disk. All the schemas I want to drop use the same tablespace, let's call it TEST_TBS.

I have dropped the users using the command:

DROP USER <USER> CASCADE;

Then, I used the following script to check the tablespace usage:

SELECT
    df.tablespace_name AS "Tablespace",
    df.file_name AS "Datafile",
    df.bytes / 1024 / 1024 AS "Size_MB",
    SUM(fs.bytes) / 1024 / 1024 AS "Free_MB",
    (df.bytes - SUM(fs.bytes)) / 1024 / 1024 AS "Used_MB",
    ROUND(((df.bytes - SUM(fs.bytes)) / df.bytes) * 100, 2) AS "Pct_Used"
FROM
    dba_data_files df,
    dba_free_space fs
WHERE
    df.file_id = fs.file_id(+)
    AND df.tablespace_name = 'TEST_TBS'
GROUP BY
    df.tablespace_name,
    df.file_name,
    df.bytes
ORDER BY
    df.tablespace_name,
    df.file_name;

Based on the results, I attempted to resize the datafiles using the command:

ALTER DATABASE DATAFILE '/data/oradata/test_tbs01.dbf' RESIZE 20G;

However, I have only gained 7GB of space (new disk space is now 32GB). I find it illogical that after dropping three schemas, I only gained 7GB.

Is there any operation I can perform to gain more space?