APEX application to display tablespace usage of databases registered in an Oracle Enterprise Manager installation

 

This is an APEX application (created with APEX 23.2) to present tablespace usage data, retrieved from your OEM 13c. It uses spaceUsed, bytesFree and pctUsed metrics to display graphs about Used (GB) space, Free (GB) space and Used (%).

First of all, you will need to create three views under your APEX workspace schema (tbs_usage_objs.sql). If your APEX installation lies in the same database with your OEM installation, you will need to give to your APEX workspace schema/user the necessary SELECT privileges on the appropriate SYSMAN objects (MGMT$TARGET_TYPE, MGMT$METRIC_DAILY, etc). If your APEX installation is in another database than your OEM installation, then create a database link from your APEX workspace schema/user to the OEM's SYSMAN and make the appropriate changes to the views and procedure's definitions.

Finally, import in your APEX workspace the application (f1002.sql).

Shrink a datafile, when ALTER TABLESPACE COALESCE is not working.

In a 11.2.0.3 database, we tried to reclaim space from USERS tablespace.
We faced a problem.
We use the following query to the the end of a particular USERS' datafile [file_id=72, filename=/oradb/SBLCCPRD/oradata4/users03.dbf]

SELECT
        A.FILE_ID,
        A.BLOCK_ID,
        A.BLOCK_ID + A.BLOCKS - 1   END_BLOCK,
        A.BLOCKS,
        --ROUND(A.BLOCKS*B.BLOCK_SIZE/1024/1024,2) MBYTES,
        A.BLOCKS*B.BLOCK_SIZE "SIZE",
        A.OWNER,
        A.SEGMENT_NAME,
        A.PARTITION_NAME,
        A.SEGMENT_TYPE
    FROM
        DBA_EXTENTS A, DBA_TABLESPACES B
    WHERE
        A.FILE_ID=:A AND A.TABLESPACE_NAME=B.TABLESPACE_NAME
UNION ALL
SELECT
        A.FILE_ID,
        A.BLOCK_ID,
        A.BLOCK_ID + A.BLOCKS - 1   END_BLOCK,
        A.BLOCKS,
        --ROUND(A.BLOCKS*B.BLOCK_SIZE/1024/1024,2) MBYTES,
        A.BLOCKS*B.BLOCK_SIZE "SIZE",
        'free'          OWNER,
        'free'          SEGMENT_NAME,
        NULL            PARTITION_NAME,
        NULL            SEGMENT_TYPE
    FROM
        DBA_FREE_SPACE A, DBA_TABLESPACES B
    WHERE
        A.FILE_ID=:A AND A.TABLESPACE_NAME=B.TABLESPACE_NAME
    ORDER BY 2 DESC;

We can see there were 5 contiguous free extents of 64 Kbytes each, total 320 Kbytes.

   

FILE_ID	BLOCK_ID	END_BLOCK	BLOCKS	SIZE	OWNER	SEGMENT_NAME	PARTITION_NAME	SEGMENT_TYPE
72	1614952	1614959	8	65536	free	free
72	1614944	1614951	8	65536	free	free
72	1614936	1614943	8	65536	free	free
72	1614928	1614935	8	65536	free	free
72	1614920	1614927	8	65536	free	free
72	1614912	1614919	8	65536	AMARGARI	CX_OTE_LOV_BACKUP_06052014		TABLE
72	1614904	1614911	8	65536	AMARGARI	CX_OTE_LOV_BACKUP_06052014		TABLE
72	1614896	1614903	8	65536	EFOTEINOP	CX_OTE_LOV_0106		TABLE
...	...	...	...	...	...	...	...	...

We tried to coalesce the tablespace, but it did not. The free extents did not get merged to one of 320 Kbytes.

SQL> alter tablespace users coalesce;
Tablespace altered. 

This is the datafile's size in bytes:

SELECT FILE_NAME, BYTES
FROM DBA_DATA_FILES
WHERE FILE_ID=72;

FILE_NAME	BYTES
/oradb/SBLCCPRD/oradata4/users03.dbf	13229744128

When we tried to shrink the datafile, even by just 8 Kbytes, we got ORA-03297:

SQL> ALTER DATABASE DATAFILE 72 RESIZE 13229735936;
ALTER DATABASE DATAFILE 72 RESIZE 13229735936
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value

The solution was to purge the tablespace's recycle bin, even if there were no recycled objects near the end of the datafile. Furthermore, before I begin all this procedure, I had purged the database's recycle bin, using "PURGE DBA_RECYCLEBIN".

SQL> PURGE TABLESPACE USERS;
Tablespace purged.

SQL> ALTER DATABASE DATAFILE 72 RESIZE 13229735936;
Database altered.

   

FILE_ID	BLOCK_ID	END_BLOCK	BLOCKS	SIZE	OWNER	SEGMENT_NAME	PARTITION_NAME	SEGMENT_TYPE
72	1614920	1614951	32	262144	free	free
72	1614912	1614919	8	65536	AMARGARI	CX_OTE_LOV_BACKUP_06052014		TABLE
72	1614904	1614911	8	65536	AMARGARI	CX_OTE_LOV_BACKUP_06052014		TABLE
72	1614896	1614903	8	65536	EFOTEINOP	CX_OTE_LOV_0106		TABLE
...	...	...	...	...	...	...	...	...

ORA-01653, ORA-01658, ORA-01688, ORA-03233 even when there is free space in the tablespace.

ORA-01653: Unable to extend table %s.%s by string in tablespace %s
ORA-01658: Unable to create INITIAL extent for segment in tablespace %s
ORA-01688: Unable to extend table %s.%s partition %s by %s in tablespace %s
ORA-03233: Unable to extend table %s.%s subpartition %s by %s in tablespace %s
ORA-01654: Unable to extend index %s.%s by %s in tablespace %s
ORA-01683: Unable to extend index %s.%s partition %s by %s in tablespace %s
ORA-03234: Unable to extend index %s.%s subpartition %s by %s in tablespace %s

You may get the above errors, even if the is a lot of free space in the tablespace of the segment to be extended. This is because that free space is very fragmented.
When a segment asks for a new extent, the database will try to fit it in an equal or larger free extent. It will not fit it in more than one smaller free extents.
So, for instance, it the next extent to be created will be 2 MB and the largest available free extent is 1MB, you will get the above errors, even if the tablespace has gigabytes of total free space.

To check if this is the case, run the following query, which displays the free extents of a tablespace ordered by their size descending.

SELECT  C.NAME "DATAFILE NAME",
        A.FILE_ID "DATAFILE #",
        A.BLOCK_ID "BLOCK #",
        A.BLOCKS,
        ROUND(A.BLOCKS*B.BLOCK_SIZE/1024/1024,2) "MBYTES"
    FROM
        DBA_FREE_SPACE A,
        DBA_TABLESPACES B,
        V$DATAFILE C
    WHERE
        A.TABLESPACE_NAME = '[TABLESPACE NAME]'
        AND A.TABLESPACE_NAME = B.TABLESPACE_NAME
        AND C.FILE# = A.FILE_ID
ORDER BY 4 DESC;

If this is the case, you need to create larger free extents by reorganizing the tablespace or to add a new datafile, which at the moment it is created is a continuous free extent by itself.