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.

Procedure to check if the filesystems can accommodate a database's autoextensible datafiles

The following procedure will sum the space still to be claimed by the autoextensible datafiles per filesystem and compare those sums will the filesystems' free space. Be aware, this assumes there are no more than one database's autoextensible datafiles per filesystem. Otherwise, you may try to use database links to gather all required information in one database and get an aggregated result.

First of all, we need to retrieve information about the filesystems in our server, so since this is a Linux server, we will use the "df" command. In the home directory [/home/oracle], create a df.sh:

#!/bin/sh
/bin/df -Pl

And make it executable:

chmod u+x df.sh

Execute it:

Filesystem    1024-blocks Used Available Capacity Mounted on
/dev/mapper/rootvg00-lvol_root  18368380 5043772 12391540 29%  /
tmpfs     1961068  683460 1277608  35%  /dev/shm
/dev/sda1    198337  73094 115003  39%  /boot
/dev/mapper/rootvg00-lvol_home  2064208  137952 1821400  8%  /home
/dev/mapper/rootvg00-lvol_tmp  4128448  139296 3779440  4%  /tmp
/dev/mapper/rootvg00-lvol_var  8260812  3771552 4069628  49%  /var
/dev/mapper/rootvg00-lvol_vartmp 4128448  227884 3690852  6%  /var/tmp
/dev/mapper/OracleVG-lvbinaries  11123304 6210032 4348232  59%  /orabin
/dev/mapper/OracleVG-lvredo2  444276  177541 243798  43%  /redo2
/dev/mapper/OracleVG-lvredo1  444276  177541 243798  43%  /redo1
/dev/mapper/OracleVG-lvarch  21778916 35900 20644052 1%  /orarch
/dev/mapper/OracleVG-lvdata  9079280  7751656 866416  90%  /oradata
/dev/mapper/vgoradata2-lvoradata2 20428668 6516652 12874296 34%  /oradata2

For instance, we can see /oradata filesystem has 866416 Kbytes of unused space.

Now, we need to make this information available to our database.
We create a directory to point to our home directory:

CREATE OR REPLACE DIRECTORY HOME_DIR AS '/home/oracle';

And an external table to query this information:

CREATE TABLE FILESYSTEMS
(FSNAME VARCHAR2(100), BLOCKS NUMBER, USED NUMBER, AVAILABLE NUMBER, CAPACITY VARCHAR2(5), MOUNT VARCHAR2(100))
ORGANIZATION EXTERNAL
(TYPE ORACLE_LOADER
 DEFAULT DIRECTORY HOME_DIR
 ACCESS PARAMETERS
 (RECORDS DELIMITED BY NEWLINE
  NOLOGFILE
  PREPROCESSOR
  HOME_DIR:'df.sh'
  SKIP 1
  FIELDS TERMINATED BY WHITESPACE LDRTRIM)
 LOCATION
 (HOME_DIR:'df.sh'));

Let's use it:

SELECT * FROM FILESYSTEMS;

   

FSNAME	BLOCKS	USED	AVAILABLE	CAPACITY	MOUNT
/dev/mapper/rootvg00-lvol_root	18368380	5043772	12391540	29%	/
tmpfs	1961068	683460	1277608	35%	/dev/shm
/dev/sda1	198337	73094	115003	39%	/boot
/dev/mapper/rootvg00-lvol_home	2064208	137952	1821400	8%	/home
/dev/mapper/rootvg00-lvol_tmp	4128448	139296	3779440	4%	/tmp
/dev/mapper/rootvg00-lvol_var	8260812	3772356	4068824	49%	/var
/dev/mapper/rootvg00-lvol_vartmp	4128448	227952	3690784	6%	/var/tmp
/dev/mapper/OracleVG-lvbinaries	11123304	6210100	4348164	59%	/orabin
/dev/mapper/OracleVG-lvredo2	444276	177541	243798	43%	/redo2
/dev/mapper/OracleVG-lvredo1	444276	177541	243798	43%	/redo1
/dev/mapper/OracleVG-lvarch	21778916	197192	243798	1%	/orarch
/dev/mapper/OracleVG-lvdata	9079280	7751656	866416	90%	/oradata
/dev/mapper/vgoradata2-lvoradata2	20428668	6516652	12874296	34%	/oradata2

We create one more table [FILESYSTEMS_INFO], identical to FILESYSTEMS, plus 2 more columns: TO_BE_USED, where we will sum the space still to be claimed by the autoextensible datafiles from DBA_DATA_FILES and DBA_TEMP_FILES, and OK, which will get value 'YES' if TO_BE_USED<AVAILABLE and 'NO' otherwise.

CREATE TABLE FILESYSTEMS_INFO
(FSNAME VARCHAR2(100), MOUNT VARCHAR2(100), BLOCKS NUMBER, USED NUMBER, AVAILABLE NUMBER, CAPACITY VARCHAR2(5), TO_BE_USED NUMBER, OK VARCHAR2(3))
TABLESPACE USERS;

 We run the following PL/SQL code:

DECLARE

BEGIN
EXECUTE IMMEDIATE 'TRUNCATE TABLE FILESYSTEMS_INFO';

INSERT INTO FILESYSTEMS_INFO(FSNAME, MOUNT, BLOCKS, USED, AVAILABLE, CAPACITY, TO_BE_USED, OK)
SELECT FSNAME, MOUNT, BLOCKS, USED, AVAILABLE, CAPACITY, 0, 'YES'
FROM FILESYSTEMS;

UPDATE FILESYSTEMS_INFO B SET (B.TO_BE_USED, B.OK)=
(SELECT NVL(SUM(A.MAXBYTES - A.USER_BYTES)/1024, 0), CASE WHEN SUM(A.MAXBYTES - A.USER_BYTES)/1024 >= B.AVAILABLE THEN 'NO' ELSE 'YES' END
FROM (SELECT FILE_NAME, AUTOEXTENSIBLE, MAXBYTES, USER_BYTES FROM DBA_DATA_FILES UNION SELECT FILE_NAME, AUTOEXTENSIBLE, MAXBYTES, USER_BYTES FROM  DBA_TEMP_FILES) A
WHERE A.AUTOEXTENSIBLE = 'YES'
AND INSTR(A.FILE_NAME, B.MOUNT||'/', 1, 1) = 1);

COMMIT;

END;
/

Now, we query FILESYSTEMS_INFO:

SELECT MOUNT, ROUND(AVAILABLE/1024/1024, 2) "AVAILABLE GB", ROUND(TO_BE_USED/1024/1024, 2) "TO BE USED GB", OK
FROM FILESYSTEMS_INFO;

   

MOUNT	AVAILABLE GB	TO BE USED GB	OK
/	11,82	0	YES
/dev/shm	1,22	0	YES
/boot	0,11	0	YES
/home	1,74	0	YES
/tmp	3,6	0	YES
/var	3,88	0	YES
/var/tmp	3,52	0	YES
/orabin	4,15	0	YES
/redo2	0,23	0	YES
/redo1	0,23	0	YES
/orarch	19,57	0	YES
/oradata	0,83	0,63	YES
/oradata2	12,28	35,09	NO

/oradata filesystem is OK, because it has 0,83 GBytes of free space and all autoextensible datafiles in it could claim 0,63 GBytes maximum.
On the other hand, /oradata2 has 12,28 GBytes of free space and 35,09 GBytes could be claimed. We should decrease one or more autoextensible datafiles' MAXSIZE.

RAC: AWR File IO Stats report

The following query, basically, shows you the same information you get from the File IO Stats section of an AWR Report.
In an AWR Roport this information is sorted by tablespace name and file name and if you have thousands of datafiles, you'll have a hard time to figure out which of them have, for instance, the highest reads.
With this query, you can get your information sorted by any column you want:

  SELECT SNAP1.INSTANCE_NUMBER "INSTANCE",
         SNAP1.FILENAME "DATAFILE",
         SNAP1.TABLESPACE_NAME "TABLESPACE",
         SNAP1.CONTENTS "CONTENTS",
         ROUND (SNAP1.BYTES / 1024 / 1024, 2) "SIZE MB",
         SNAP2.PHYRDS - SNAP1.PHYRDS "PHYSICAL READS",
         ROUND ((SNAP2.PHYRDS - SNAP1.PHYRDS) / (SNAP2.SECS - SNAP1.SECS), 2) "AVERAGE READS/SEC",
         DECODE (SNAP2.PHYRDS - SNAP1.PHYRDS, 0, 0, ROUND ((SNAP2.PHYBLKRD - SNAP1.PHYBLKRD) / (SNAP2.PHYRDS - SNAP1.PHYRDS), 2)) "AVERAGE BLOCKS/READ",
         DECODE (SNAP2.PHYRDS - SNAP1.PHYRDS, 0, 0, ROUND (((SNAP2.READTIM - SNAP1.READTIM) * 10) / (SNAP2.PHYRDS - SNAP1.PHYRDS), 2)) "AVERAGE READ (MS)",
         SNAP2.PHYWRTS - SNAP1.PHYWRTS "PHYSICAL WRITES",
         ROUND ((SNAP2.PHYWRTS - SNAP1.PHYWRTS) / (SNAP2.SECS - SNAP1.SECS), 2) "AVERAGE WRITES/SEC",
         DECODE (SNAP2.PHYWRTS - SNAP1.PHYWRTS, 0, 0, ROUND ((SNAP2.PHYBLKWRT - SNAP1.PHYBLKWRT) / (SNAP2.PHYWRTS - SNAP1.PHYWRTS), 2)) "AVERAGE BLOCKS/WRITE",
         DECODE (SNAP2.PHYWRTS - SNAP1.PHYWRTS, 0, 0, ROUND (((SNAP2.WRITETIM - SNAP1.WRITETIM) * 10) / (SNAP2.PHYWRTS - SNAP1.PHYWRTS), 2)) "AVERAGE WRITE (MS)",
         SNAP2.WAIT_COUNT - SNAP1.WAIT_COUNT "BUFFER WAITS",
         DECODE (SNAP2.WAIT_COUNT - SNAP1.WAIT_COUNT, 0, 0, ROUND (((SNAP2.TIME - SNAP1.TIME) * 10) / (SNAP2.WAIT_COUNT - SNAP1.WAIT_COUNT), 2)) "AVERAGE BUFFER WAIT (MS)"
    FROM (SELECT A.INSTANCE_NUMBER,
                 A.FILENAME,
                 D.TABLESPACE_NAME,
                 D.CONTENTS,
                 E.BYTES,
                 TO_NUMBER(TO_CHAR(S.END_INTERVAL_TIME, 'SSSSS')) "SECS",
                 A.PHYRDS,
                 A.PHYWRTS,
                 A.WAIT_COUNT,
                 A.PHYBLKRD,
                 A.PHYBLKWRT,
                 A.READTIM,
                 A.WRITETIM,
                 A.TIME
            FROM DBA_HIST_FILESTATXS A,
                 V$TABLESPACE C,
                 DBA_TABLESPACES D,
                 DBA_DATA_FILES E,
                 DBA_HIST_SNAPSHOT S
           WHERE     A.SNAP_ID = :BEGIN_SNAP
                 AND A.TS# = C.TS#
                 AND C.NAME = D.TABLESPACE_NAME
                 AND A.SNAP_ID = S.SNAP_ID
                 AND A.INSTANCE_NUMBER = S.INSTANCE_NUMBER
                 AND A.FILENAME = E.FILE_NAME
          UNION
          SELECT A.INSTANCE_NUMBER,
                 A.FILENAME,
                 D.TABLESPACE_NAME,
                 D.CONTENTS,
                 E.BYTES,
                 TO_NUMBER(TO_CHAR(S.END_INTERVAL_TIME, 'SSSSS')) "SECS",
                 A.PHYRDS,
                 A.PHYWRTS,
                 A.WAIT_COUNT,
                 A.PHYBLKRD,
                 A.PHYBLKWRT,
                 A.READTIM,
                 A.WRITETIM,
                 A.TIME
            FROM DBA_HIST_TEMPSTATXS A,
                 V$TABLESPACE C,
                 DBA_TABLESPACES D,
                 DBA_TEMP_FILES E,
                 DBA_HIST_SNAPSHOT S
           WHERE     A.SNAP_ID = :BEGIN_SNAP
                 AND A.TS# = C.TS#
                 AND C.NAME = D.TABLESPACE_NAME
                 AND A.SNAP_ID = S.SNAP_ID
                 AND A.INSTANCE_NUMBER = S.INSTANCE_NUMBER
                 AND A.FILENAME = E.FILE_NAME) SNAP1,
         (SELECT A.INSTANCE_NUMBER,
                 A.FILENAME,
                 D.TABLESPACE_NAME,
                 D.CONTENTS,
                 E.BYTES,
                 TO_NUMBER(TO_CHAR(S.END_INTERVAL_TIME, 'SSSSS')) "SECS",
                 A.PHYRDS,
                 A.PHYWRTS,
                 A.WAIT_COUNT,
                 A.PHYBLKRD,
                 A.PHYBLKWRT,
                 A.READTIM,
                 A.WRITETIM,
                 A.TIME
            FROM DBA_HIST_FILESTATXS A,
                 V$TABLESPACE C,
                 DBA_TABLESPACES D,
                 DBA_DATA_FILES E,
                 DBA_HIST_SNAPSHOT S
           WHERE     A.SNAP_ID = :END_SNAP
                 AND A.TS# = C.TS#
                 AND C.NAME = D.TABLESPACE_NAME
                 AND A.SNAP_ID = S.SNAP_ID
                 AND A.INSTANCE_NUMBER = S.INSTANCE_NUMBER
                 AND A.FILENAME = E.FILE_NAME
          UNION
          SELECT A.INSTANCE_NUMBER,
                 A.FILENAME,
                 D.TABLESPACE_NAME,
                 D.CONTENTS,
                 E.BYTES,
                 TO_NUMBER(TO_CHAR(S.END_INTERVAL_TIME, 'SSSSS')) "SECS",
                 A.PHYRDS,
                 A.PHYWRTS,
                 A.WAIT_COUNT,
                 A.PHYBLKRD,
                 A.PHYBLKWRT,
                 A.READTIM,
                 A.WRITETIM,
                 A.TIME
            FROM DBA_HIST_TEMPSTATXS A,
                 V$TABLESPACE C,
                 DBA_TABLESPACES D,
                 DBA_TEMP_FILES E,
                 DBA_HIST_SNAPSHOT S
           WHERE     A.SNAP_ID = :END_SNAP
                 AND A.TS# = C.TS#
                 AND C.NAME = D.TABLESPACE_NAME
                 AND A.SNAP_ID = S.SNAP_ID
                 AND A.INSTANCE_NUMBER = S.INSTANCE_NUMBER
                 AND A.FILENAME = E.FILE_NAME) SNAP2
   WHERE     SNAP1.FILENAME = SNAP2.FILENAME
         AND SNAP1.INSTANCE_NUMBER = SNAP2.INSTANCE_NUMBER
ORDER BY 3, 2;

Also, AWR reports are per instance. It does not give you an option to get those metrics aggregated for all your instances.
The following query does that:

SELECT SNAP1.FILENAME "DATAFILE",
         SNAP1.TABLESPACE_NAME "TABLESPACE",
         SNAP1.CONTENTS "CONTENTS",
         ROUND (SNAP1.BYTES / 1024 / 1024, 2) "SIZE MB",
         SNAP2.PHYRDS - SNAP1.PHYRDS "PHYSICAL READS",
         ROUND ((SNAP2.PHYRDS - SNAP1.PHYRDS) / (SNAP2.SECS - SNAP1.SECS), 2) "AVERAGE READS/SEC",
         DECODE (SNAP2.PHYRDS - SNAP1.PHYRDS, 0, 0, ROUND ((SNAP2.PHYBLKRD - SNAP1.PHYBLKRD) / (SNAP2.PHYRDS - SNAP1.PHYRDS), 2)) "AVERAGE BLOCKS/READ",
         DECODE (SNAP2.PHYRDS - SNAP1.PHYRDS, 0, 0, ROUND (((SNAP2.READTIM - SNAP1.READTIM) * 10) / (SNAP2.PHYRDS - SNAP1.PHYRDS), 2)) "AVERAGE READ (MS)",
         SNAP2.PHYWRTS - SNAP1.PHYWRTS "PHYSICAL WRITES",
         ROUND ((SNAP2.PHYWRTS - SNAP1.PHYWRTS) / (SNAP2.SECS - SNAP1.SECS), 2) "AVERAGE WRITES/SEC",
         DECODE (SNAP2.PHYWRTS - SNAP1.PHYWRTS, 0, 0, ROUND ((SNAP2.PHYBLKWRT - SNAP1.PHYBLKWRT) / (SNAP2.PHYWRTS - SNAP1.PHYWRTS), 2)) "AVERAGE BLOCKS/WRITE",
         DECODE (SNAP2.PHYWRTS - SNAP1.PHYWRTS, 0, 0, ROUND (((SNAP2.WRITETIM - SNAP1.WRITETIM) * 10) / (SNAP2.PHYWRTS - SNAP1.PHYWRTS), 2)) "AVERAGE WRITE (MS)",
         SNAP2.WAIT_COUNT - SNAP1.WAIT_COUNT "BUFFER WAITS",
         DECODE (SNAP2.WAIT_COUNT - SNAP1.WAIT_COUNT, 0, 0, ROUND (((SNAP2.TIME - SNAP1.TIME) * 10) / (SNAP2.WAIT_COUNT - SNAP1.WAIT_COUNT), 2)) "AVERAGE BUFFER WAIT (MS)"
    FROM (  SELECT A.FILENAME,
                   D.TABLESPACE_NAME,
                   D.CONTENTS,
                   E.BYTES,
                   SUM (TO_NUMBER (TO_CHAR (S.END_INTERVAL_TIME, 'SSSSS'))) "SECS",
                   SUM (A.PHYRDS) "PHYRDS",
                   SUM (A.PHYWRTS) "PHYWRTS",
                   SUM (A.WAIT_COUNT) "WAIT_COUNT",
                   SUM (A.PHYBLKRD) "PHYBLKRD",
                   SUM (A.PHYBLKWRT) "PHYBLKWRT",
                   SUM (A.READTIM) "READTIM",
                   SUM (A.WRITETIM) "WRITETIM",
                   SUM (A.TIME) "TIME"
              FROM DBA_HIST_FILESTATXS A,
                   V$TABLESPACE C,
                   DBA_TABLESPACES D,
                   DBA_DATA_FILES E,
                   DBA_HIST_SNAPSHOT S
             WHERE     A.SNAP_ID = :BEGIN_SNAP
                   AND A.TS# = C.TS#
                   AND C.NAME = D.TABLESPACE_NAME
                   AND A.SNAP_ID = S.SNAP_ID
                   AND A.INSTANCE_NUMBER = S.INSTANCE_NUMBER
                   AND A.FILENAME = E.FILE_NAME
          GROUP BY A.FILENAME,
                   D.TABLESPACE_NAME,
                   D.CONTENTS,
                   E.BYTES
          UNION
            SELECT A.FILENAME,
                   D.TABLESPACE_NAME,
                   D.CONTENTS,
                   E.BYTES,
                   SUM (TO_NUMBER (TO_CHAR (S.END_INTERVAL_TIME, 'SSSSS'))) "SECS",
                   SUM (A.PHYRDS) "PHYRDS",
                   SUM (A.PHYWRTS) "PHYWRTS",
                   SUM (A.WAIT_COUNT) "WAIT_COUNT",
                   SUM (A.PHYBLKRD) "PHYBLKRD",
                   SUM (A.PHYBLKWRT) "PHYBLKWRT",
                   SUM (A.READTIM) "READTIM",
                   SUM (A.WRITETIM) "WRITETIM",
                   SUM (A.TIME) "TIME"
              FROM DBA_HIST_TEMPSTATXS A,
                   V$TABLESPACE C,
                   DBA_TABLESPACES D,
                   DBA_TEMP_FILES E,
                   DBA_HIST_SNAPSHOT S
             WHERE     A.SNAP_ID = :BEGIN_SNAP
                   AND A.TS# = C.TS#
                   AND C.NAME = D.TABLESPACE_NAME
                   AND A.SNAP_ID = S.SNAP_ID
                   AND A.INSTANCE_NUMBER = S.INSTANCE_NUMBER
                   AND A.FILENAME = E.FILE_NAME
          GROUP BY A.FILENAME,
                   D.TABLESPACE_NAME,
                   D.CONTENTS,
                   E.BYTES) SNAP1,
         (  SELECT A.FILENAME,
                   D.TABLESPACE_NAME,
                   D.CONTENTS,
                   E.BYTES,
                   SUM (TO_NUMBER (TO_CHAR (S.END_INTERVAL_TIME, 'SSSSS'))) "SECS",
                   SUM (A.PHYRDS) "PHYRDS",
                   SUM (A.PHYWRTS) "PHYWRTS",
                   SUM (A.WAIT_COUNT) "WAIT_COUNT",
                   SUM (A.PHYBLKRD) "PHYBLKRD",
                   SUM (A.PHYBLKWRT) "PHYBLKWRT",
                   SUM (A.READTIM) "READTIM",
                   SUM (A.WRITETIM) "WRITETIM",
                   SUM (A.TIME) "TIME"
              FROM DBA_HIST_FILESTATXS A,
                   V$TABLESPACE C,
                   DBA_TABLESPACES D,
                   DBA_DATA_FILES E,
                   DBA_HIST_SNAPSHOT S
             WHERE     A.SNAP_ID = :END_SNAP
                   AND A.TS# = C.TS#
                   AND C.NAME = D.TABLESPACE_NAME
                   AND A.SNAP_ID = S.SNAP_ID
                   AND A.INSTANCE_NUMBER = S.INSTANCE_NUMBER
                   AND A.FILENAME = E.FILE_NAME
          GROUP BY A.FILENAME,
                   D.TABLESPACE_NAME,
                   D.CONTENTS,
                   E.BYTES
          UNION
            SELECT A.FILENAME,
                   D.TABLESPACE_NAME,
                   D.CONTENTS,
                   E.BYTES,
                   SUM (TO_NUMBER (TO_CHAR (S.END_INTERVAL_TIME, 'SSSSS'))) "SECS",
                   SUM (A.PHYRDS) "PHYRDS",
                   SUM (A.PHYWRTS) "PHYWRTS",
                   SUM (A.WAIT_COUNT) "WAIT_COUNT",
                   SUM (A.PHYBLKRD) "PHYBLKRD",
                   SUM (A.PHYBLKWRT) "PHYBLKWRT",
                   SUM (A.READTIM) "READTIM",
                   SUM (A.WRITETIM) "WRITETIM",
                   SUM (A.TIME) "TIME"
              FROM DBA_HIST_TEMPSTATXS A,
                   V$TABLESPACE C,
                   DBA_TABLESPACES D,
                   DBA_TEMP_FILES E,
                   DBA_HIST_SNAPSHOT S
             WHERE     A.SNAP_ID = :END_SNAP
                   AND A.TS# = C.TS#
                   AND C.NAME = D.TABLESPACE_NAME
                   AND A.SNAP_ID = S.SNAP_ID
                   AND A.INSTANCE_NUMBER = S.INSTANCE_NUMBER
                   AND A.FILENAME = E.FILE_NAME
          GROUP BY A.FILENAME,
                   D.TABLESPACE_NAME,
                   D.CONTENTS,
                   E.BYTES) SNAP2
   WHERE SNAP1.FILENAME = SNAP2.FILENAME
ORDER BY 2, 1;

Procedure to automate datafiles' resizing taking into account filesystems' free space

I wanted to create a mechanism for our operators to be able to give more space to tablespaces, automatically, when there is a need to.
The operators don't know the datafiles' location and the filesystems' free space, so the only thing they will do is to run a schell script with the tablespace name as a parameter.

Unfortunately, oracle database does not store any information about the filesystems and their space usage, so if you are not careful you may end up with completely full filesystems.
We can only get this info, using OS commands like bdf and df, so to make it available to our database, we will create a text file in our server, containing the output of those commands and an external table in the database to read that file.

I created this example in HP-UX, so you will need to do a few modifications to make it run in Linux or other Unix.
For example, bdf is an HP-UX command and you should use df in other Unix/Linux.

First, create a directory pointing to the location, where our scripts and output files will be stored, e.g. /oradb/orabin/scripts:

CREATE DIRECTORY SCRIPT_DIR AS '/oradb/orabin/scripts';

Now, create the external table, called df, which reads /oradb/orabin/scripts/bdf.txt:

CREATE TABLE DF
                   (FILESYSTEM VARCHAR2(200),
                    KBYTES       NUMBER,
                    USED         NUMBER,
                    AVAIL        NUMBER,
                    USED_PER     VARCHAR2(20),
                    MOUNTED      VARCHAR2(100)
                   )
     ORGANIZATION EXTERNAL
     (
       TYPE ORACLE_LOADER
       DEFAULT DIRECTORY SCRIPT_DIR
       ACCESS PARAMETERS
       (
         RECORDS DELIMITED BY NEWLINE
         BADFILE SCRIPT_DIR:'bdf%a_%p.bad'
         LOGFILE SCRIPT_DIR:'bdf%a_%p.log'
         FIELDS TERMINATED BY ' '
         MISSING FIELD VALUES ARE NULL
       )
       LOCATION ('bdf.txt')
     )
     REJECT LIMIT UNLIMITED;

Our script will make some of the given tablespace's datafiles autoextensible, taking into account the filesystems' free space.
We discard any datafiles there are in filesystems with > 99% of used space.
For all others, we autoextend them to a size equal to:
CURRENT DATAFILE SIZE + 10% OF FILESYSTEM'S FREE SPACE
If the above size is greater than tablespace's block size * 4194302, then we set MAXSIZE UNLIMITED, to avoid to get an ORA error.
So, create file /oradb/orabin/scripts/extend.sql:

SET SERVEROUTPUT ON
SPOOL OUT.TXT

DECLARE
   V_STMT    VARCHAR2 (1000);
   V_BSIZE   NUMBER;

   CURSOR C1
   IS
      SELECT A.FILE_NAME,
             A.BYTES,
             B.AVAIL,
             (BYTES / 1024) + (AVAIL * 0.1) NEW_SIZE
        FROM DBA_DATA_FILES A, DF B
       WHERE     A.TABLESPACE_NAME = UPPER ('&1')
             AND SUBSTR (A.FILE_NAME, 1, INSTR (A.FILE_NAME, '/', -1, 2) - 1) = B.MOUNTED
             AND SUBSTR (B.USED_PER, 1, INSTR (B.USED_PER, '%') - 1) < 99;
BEGIN
   SELECT BLOCK_SIZE
     INTO V_BSIZE
     FROM DBA_TABLESPACES
    WHERE TABLESPACE_NAME = UPPER ('&1');

   FOR DFILE IN C1
   LOOP
      DBMS_OUTPUT.PUT_LINE ('Filename: ' || DFILE.FILE_NAME);
      DBMS_OUTPUT.PUT_LINE ('Size (KB): ' || DFILE.BYTES / 1024);
      DBMS_OUTPUT.PUT_LINE ('Avail (KB): ' || DFILE.AVAIL);

      IF DFILE.NEW_SIZE < V_BSIZE * 4194302 / 1024
      THEN
         V_STMT := 'ALTER DATABASE DATAFILE ''' || DFILE.FILE_NAME || ''' AUTOEXTEND ON NEXT 128M MAXSIZE ' || DFILE.NEW_SIZE || 'K';
      ELSE
         V_STMT := 'ALTER DATABASE DATAFILE ''' || DFILE.FILE_NAME || ''' AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED';
      END IF;

      DBMS_OUTPUT.PUT_LINE (V_STMT);
      EXECUTE IMMEDIATE V_STMT;
      DBMS_OUTPUT.PUT_LINE ('--------------------------');
   END LOOP;
END;
/

SPOOL OFF

Finally, we create the shell script /oradb/orabin/scripts/extend.sh, which creates the bdf.txt with the appropriate format for our external table. It also sends an email to the DBAs, so they get informed that the procedure run and which datafiles were affected.

#!/bin/sh
ORACLE_HOME=/oradb/orabin/app/product/db1
export ORACLE_HOME
ORACLE_SID=OTE
export ORACLE_SID

rm bdf*
rm original.txt
bdf|grep -v File|grep -v vol|grep -v odm|grep -v shared>original.txt
sed -e "s/  */ /g" original.txt > bdf.txt

cd /oradb/orabin/scripts
sqlplus -s /nolog << EOF
connect / as sysdba
@extend.sql $1
exit
EOF

cat OUT.TXT|mailx -s "Autoextend script run for $ORACLE_SID" name@ote.gr

Now, the only thing the operator has to do is run extend.sh with the tablespace name as a parameter:

extend.sh user_data

Calculate actual free space of datafiles and tablespaces

Let's say, we have a 10GB datafile, autoexendible to 20GB. Querying DBA_FREE_SPACE we see this datafile has 2GB free space.
The actual free space is MAX_SIZE - SIZE + FREE_SPACE, so for our example is 20GB - 10GB + 2GB = 12GB.
The following query will display this datafiles' free space [TOTAL FREE SPACE GB] per tablespaces.

  SELECT B.TABLESPACE_NAME,
         B.FILE_NAME,
         ROUND (B.BYTES / 1024 / 1024 / 1024, 2) "SIZE GB",
         ROUND (B.MAXBYTES / 1024 / 1024 / 1024, 2) "MAX SIZE GB",
         ROUND (SUM (A.BYTES) / 1024 / 1024 / 1024, 2) "FREE SPACE GB",
         CASE
            WHEN MAXBYTES = 0
            THEN
               ROUND (SUM (A.BYTES) / 1024 / 1024 / 1024, 2)
            ELSE
               ROUND (
                    ( (B.MAXBYTES - B.BYTES) + SUM (A.BYTES)) / 1024 / 1024 / 1024, 2)
         END
            "TOTAL FREE SPACE GB"
    FROM DBA_FREE_SPACE A, DBA_DATA_FILES B
   WHERE A.FILE_ID = B.FILE_ID AND B.TABLESPACE_NAME IN ('CBAM')
GROUP BY B.TABLESPACE_NAME,
         B.FILE_NAME,
         B.BYTES,
         B.MAXBYTES
ORDER BY 1, 2;

The output is:   

TABLESPACE_NAME	FILE_NAME	SIZE GB	MAX SIZE GB	FREE SPACE GB	TOTAL FREE SPACE GB
CBAM	/oradb/SOADB/oradata1/cbam.dbf	9.77	0	0	0
CBAM	/oradb/SOADB/oradata6/cbam14.dbf	19.53	19.53	0	0
CBAM	/oradb/SOADB/oradata6/cbam18.dbf	19.53	19.53	0.01	0.01
...	...	...	...	...	...
CBAM	/oradb/SOADB/oradata6/cbam27.dbf	12.5	25	11.92	24.42
CBAM	/oradb/SOADB/oradata7/cbam25.dbf	25	25	0.03	0.03
CBAM	/oradb/SOADB/oradata7/cbam28.dbf	12.11	24.41	11.8	24.1

This query will not return all tablespaces' datafiles, only those with at least a few KB of free space and ,thus, at least 1 row in DBA_FREE_SPACE.

Another query to sum this free space per tablespace:

  SELECT B.TABLESPACE_NAME,
         ROUND (SUM (B.BYTES) / 1024 / 1024 / 1024, 2) "SIZE GB",
         ROUND (SUM (B.MAXBYTES) / 1024 / 1024 / 1024, 2) "MAX SIZE GB",
         A.FREE_SPACE_GB "FREE SPACE GB",
         ROUND (
            (SUM (B.MAXBYTES - B.BYTES) / 1024 / 1024 / 1024) + A.FREE_SPACE_GB,
            2)
            "TOTAL FREE SPACE GB"
    FROM DBA_DATA_FILES B,
         (  SELECT TABLESPACE_NAME,
                   ROUND (SUM (BYTES) / 1024 / 1024 / 1024, 2) FREE_SPACE_GB
              FROM DBA_FREE_SPACE
             WHERE TABLESPACE_NAME IN ('CBAM')
          GROUP BY TABLESPACE_NAME) A
   WHERE     B.TABLESPACE_NAME IN ('CBAM')
         AND B.TABLESPACE_NAME = A.TABLESPACE_NAME
         AND B.MAXBYTES != 0
GROUP BY B.TABLESPACE_NAME, A.FREE_SPACE_GB
ORDER BY 1;

The output:

TABLESPACE_NAME	SIZE GB	MAX SIZE GB	FREE SPACE GB	TOTAL FREE SPACE GB
CBAM	284.58	309.38	23.86	48.66

Again, this query will not show you the tablespace's full size. In "SIZE GB" column are added the datafiles' sizes which have free space.

Tablespace growth and usage monitoring

We want to create a mechanism to monitor growth of our tablespaces and how much space is used in them. We will use DBA_TABLESPACE_USAGE_METRICS view and store its output in a table called HELPDESK.TABLESPACE_STATS.

If you use autoextendible datafiles, according to Oracle Note 455715.1:

Tablespace_size in DBA_TABLESPACE_USAGE_METRICS takes the maximum file size for autoextensible tablespace which corresponds to maxblocks in dba_data_files.

We create HELPDESK.TABLESPACE_STATS and its indexes:

CREATE TABLE HELPDESK.TABLESPACE_STATS
(
  TABLESPACE_NAME  VARCHAR2(30 BYTE),
  TABLESPACE_SIZE  NUMBER,
  USED_SPACE       NUMBER,
  USED_PERCENT     NUMBER,
  COLLECTION_DATE  DATE
)
TABLESPACE USERS
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          1M
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;


CREATE INDEX HELPDESK.DATE_IDX ON HELPDESK.TABLESPACE_STATS
(COLLECTION_DATE)
LOGGING
TABLESPACE USERS
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          1M
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


CREATE INDEX HELPDESK.NAME_IDX ON HELPDESK.TABLESPACE_STATS
(TABLESPACE_NAME)
LOGGING
TABLESPACE USERS
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          1M
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;

Now, schedule a job to run the following INSERT. I choose to run it once per week.

INSERT INTO HELPDESK.TABLESPACE_STATS (TABLESPACE_NAME,
                                       USED_SPACE,
                                       TABLESPACE_SIZE,
                                       USED_PERCENT,
                                       COLLECTION_DATE)
   SELECT A.TABLESPACE_NAME,
          A.USED_SPACE * B.BLOCK_SIZE,
          A.TABLESPACE_SIZE * B.BLOCK_SIZE,
          A.USED_PERCENT,
          SYSDATE
     FROM DBA_TABLESPACE_USAGE_METRICS A, DBA_TABLESPACES B
    WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME;

Let's see a scenario, where we have collected 3 weeks of data:

  SELECT TRUNC (COLLECTION_DATE) "DATE",
         TABLESPACE_NAME,
         ROUND (TABLESPACE_SIZE / 1024 / 1024, 2) "SIZE MB",
         ROUND (USED_SPACE / 1024 / 1024, 2) "USED SPACE MB",
         ROUND (USED_PERCENT, 2) "USED %"
    FROM HELPDESK.TABLESPACE_STATS
ORDER BY 1, 2;

   

DATE	TABLESPACE_NAME	SIZE MB	USED SPACE MB	USED %
02/07/2012	IDX	30	20	66.67
02/07/2012	SYSAUX	600	418.56	69.76
02/07/2012	SYSTEM	700	362.25	51.75
02/07/2012	TBL	40	22	55
02/07/2012	TEMP	20	0	0
02/07/2012	UNDOTBS1	245	3.25	1.33
02/07/2012	USERS	5	4	80
09/07/2012	IDX	30	25	83.33
09/07/2012	SYSAUX	600	418.56	69.76
09/07/2012	SYSTEM	700	362.25	51.75
09/07/2012	TBL	45	26	57.78
09/07/2012	TEMP	20	0	0
09/07/2012	UNDOTBS1	245	3.25	1.33
09/07/2012	USERS	5	4.8	96
16/07/2012	IDX	35	28.8	82.29
16/07/2012	SYSAUX	600	418.56	69.76
16/07/2012	SYSTEM	700	362.25	51.75
16/07/2012	TBL	45	35	77.78
16/07/2012	TEMP	20	0	0
16/07/2012	UNDOTBS1	245	3.25	1.33
16/07/2012	USERS	7	5.5	78.57

If we want to see the differences between two dates:

  SELECT LAST.TABLESPACE_NAME,
         ROUND ( (LAST.TABLESPACE_SIZE - FIRST.TABLESPACE_SIZE) / 1024 / 1024,
                2)
            "SIZE GROWTH MB",
         ROUND ( (LAST.USED_SPACE - FIRST.USED_SPACE) / 1024 / 1024, 2)
            "USED SPACE GROWTH MB",
         ROUND (LAST.USED_PERCENT - FIRST.USED_PERCENT, 2) "USED % GROWTH"
    FROM (SELECT TABLESPACE_NAME,
                 TABLESPACE_SIZE,
                 USED_SPACE,
                 USED_PERCENT
            FROM HELPDESK.TABLESPACE_STATS
           WHERE TRUNC (COLLECTION_DATE) = '16-JUL-12') LAST,
         (SELECT TABLESPACE_NAME,
                 TABLESPACE_SIZE,
                 USED_SPACE,
                 USED_PERCENT
            FROM HELPDESK.TABLESPACE_STATS
           WHERE TRUNC (COLLECTION_DATE) = '02-JUL-2012') FIRST
   WHERE LAST.TABLESPACE_NAME = FIRST.TABLESPACE_NAME
ORDER BY 1;

TABLESPACE_NAME	SIZE GROWTH MB	USED SPACE GROWTH MB	USED % GROWTH
IDX	5	8.8	15.62
SYSAUX	0	0	0
SYSTEM	0	0	0
TBL	5	13	22.78
TEMP	0	0	0
UNDOTBS1	0	0	0
USERS	2	1.5	-1.43

So, for instance, in 2 weeks, for tablespace TBL we gave 5 MB extra space, but 13 MB were additionally used, thus increasing its used percent by 22,78%.

OEM: "Tablespace Used (%)" alert is not triggered

There was an incident, where a tablespace filled completely, but I did not get any alert from OEM.

There is Oracle Note 403264.1:"Troubleshooting a Database Tablespace Used(%) Alert problem", where several issues about this alert are discussed.
In my case, there was no entry in DBA_OUTSTANDING_ALERTS,
although in DBA_TABLESPACE_USAGE_METRICS the particular tablespace had exceeded the thresholds I have set.

So, according to Section 4 in this note, this is probably caused by database bugs 7462407, 5245039.
Additionally, I have a "gap" in my v$tablespace.

Unfortunately, there are no patches for these bugs for Windows platforms, so I created a user defined metric to get these alerts.
The new metric was based on DBA_TABLESPACE_USAGE_METRICS, which is very nice, because it queries v$filespace_usage, which keeps track of any MAXSIZE value you may have set in your autoextended datafiles.
If it is set to UNLIMITED, then this value is equal to 32 GB for 8 KB block size (2^22-1 * 8 KB).

Keep in mind, columns referencing sizes in these 2 views are blocks.
So, if your tablespace has block size of 8 KB, multiply these values by 8 to get its size in KB.

Of course, there is no way for your database to know, if your filesystems will fill, e.g. if in your filesystem you have 10 GB of free space and an autoextended datafile in it with MAXSIZE 15 GB, you will not get any alert, even from the default OEM metric, when your filesystem's free space depletes.
You should use "Filesystem Space Available (%)" alert at host level, to check your filesystem's free space.

Move huge database's datafiles to new filesystems

Our eBS database is 14+ TB/2100+ datafiles big and we have to move all its datafiles to new filesystems, taking into account their physical reads, so no more than 1 heavily read datafile would be per filesystem.
We will be given 100 200GB new filesystems and we will leave 10GB empty in each, so only 190GB per filesystem will be used.
The old naming convention is "/oracleXXX/oradata/" and the new will be "/oraerpXXX/oradata".

A manual approach of this case is almost impossible to be implemented for a database of this size, even if we do not consider the "physical reads" clause.

To order our datafiles by physical reads (or writes) we can use the following script, which uses AWR snapshots (10g+):

SELECT A.FILENAME,
         D.TABLESPACE_NAME,
         D.CONTENTS,
         E.BYTES,
         NVL (SUM (A.PHYRDS), 0) READS,
         NVL (SUM (A.PHYWRTS), 0) WRITES
    FROM DBA_HIST_FILESTATXS A,
         V$TABLESPACE C,
         DBA_TABLESPACES D,
         DBA_DATA_FILES E
   WHERE     A.SNAP_ID = 15400   --LAST SNAPSHOT
         AND A.TS# = C.TS#
         AND C.NAME = D.TABLESPACE_NAME
         AND A.FILENAME = E.FILE_NAME
GROUP BY A.FILENAME,
         D.TABLESPACE_NAME,
         D.CONTENTS,
         E.BYTES
UNION
  SELECT A.FILENAME,
         D.TABLESPACE_NAME,
         D.CONTENTS,
         E.BYTES,
         NVL (SUM (A.PHYRDS), 0) READS,
         NVL (SUM (A.PHYWRTS), 0) WRITES
    FROM DBA_HIST_TEMPSTATXS  A,
         V$TABLESPACE C,
         DBA_TABLESPACES D,
         DBA_TEMP_FILES E
   WHERE     A.SNAP_ID = 15400   --LAST SNAPSHOT
         AND A.TS# = C.TS#
         AND C.NAME = D.TABLESPACE_NAME
         AND A.FILENAME = E.FILE_NAME
GROUP BY A.FILENAME,
         D.TABLESPACE_NAME,
         D.CONTENTS,
         E.BYTES
ORDER BY 5 DESC;

Or we may use a script reading GV$FILESTAT, GV$TEMPSTAT, which keep statistics since instance startup:

SELECT B.FILE_NAME,
         C.TABLESPACE_NAME,
         C.CONTENTS,
         B.BYTES,
         NVL (SUM (A.PHYRDS), 0) READS,
         NVL (SUM (A.PHYWRTS), 0) WRITES
    FROM GV$FILESTAT A, DBA_DATA_FILES B, DBA_TABLESPACES C
   WHERE A.FILE# = B.FILE_ID AND B.TABLESPACE_NAME = C.TABLESPACE_NAME
GROUP BY B.FILE_NAME,
         C.TABLESPACE_NAME,
         C.CONTENTS,
         B.BYTES
UNION
  SELECT B.FILE_NAME,
         C.TABLESPACE_NAME,
         C.CONTENTS,
         B.BYTES,
         NVL (SUM (A.PHYRDS), 0) READS,
         NVL (SUM (A.PHYWRTS), 0) WRITES
    FROM GV$TEMPSTAT A, DBA_TEMP_FILES B, DBA_TABLESPACES C
   WHERE A.FILE# = B.FILE_ID AND B.TABLESPACE_NAME = C.TABLESPACE_NAME
GROUP BY B.FILE_NAME,
         C.TABLESPACE_NAME,
         C.CONTENTS,
         B.BYTES
ORDER BY 5 DESC;                 --6 for WRITES

First, we create these tables:

CREATE TABLE HELPDESK.DISKS
(
   DISK_ID     NUMBER,
   SIZE_MAX    NUMBER,
   SIZE_USED   NUMBER,
   PATH_NAME   VARCHAR2 (513 BYTE)
)
TABLESPACE HELPDESK
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (INITIAL 128 K
         NEXT 128 K
         MINEXTENTS 1
         MAXEXTENTS UNLIMITED
         PCTINCREASE 0
         BUFFER_POOL DEFAULT)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;

CREATE TABLE HELPDESK.DFILES
(
   DISK_ID           NUMBER,
   NAME              VARCHAR2 (513 BYTE),
   NAME_NEW          VARCHAR2 (513 BYTE),
   BYTES             NUMBER,
   TABLESPACE_NAME   VARCHAR2 (30 BYTE),
   CONTENTS          VARCHAR2 (9 BYTE),
   READS             NUMBER,
   WRITES            NUMBER,
   BACKUP_COM        VARCHAR2 (2000 BYTE),
   ALTER_COM         VARCHAR2 (2000 BYTE)
)
TABLESPACE HELPDESK
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (INITIAL 128 K
         NEXT 128 K
         MINEXTENTS 1
         MAXEXTENTS UNLIMITED
         PCTINCREASE 0
         BUFFER_POOL DEFAULT)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;

GRANT ALL ON HELPDESK.DISKS TO SYSTEM;

GRANT ALL ON HELPDESK.DFILES TO SYSTEM;

In DISKS table, we will keep record of how much size of each new filesystem will be used (SIZE_USED).
In DFILES, we will get the new datafiles' location (NAME_NEW) and we will, automatically, create the commands needed for NetBackup to restore them to their new locations (BACKUP_COM) and the "ALTER DATABASE RENAME FILE [old datafile location] TO [new datafile location]" commands (ALTER_COM).

We create and run the following procedure:

CREATE OR REPLACE PROCEDURE SYSTEM.DISK_GROUPING
IS
   --ORDERING IN THESE CURSORS IS NEEDED, IF YOU WANT TO TAKE INTO ACCOUNT THE PHYSICAL READS
   CURSOR C1
   IS
        SELECT NAME, BYTES, TABLESPACE_NAME
          FROM HELPDESK.DFILES
      ORDER BY READS DESC;

   CURSOR C2
   IS
        SELECT DISK_ID,
               SIZE_MAX,
               SIZE_USED,
               PATH_NAME
          FROM HELPDESK.DISKS
      ORDER BY SIZE_USED;
BEGIN
   EXECUTE IMMEDIATE 'TRUNCATE TABLE HELPDESK.DISKS';

   FOR I IN 1 .. 100
   LOOP        --CREATE 100 DISKS WITH 190GB (204010946560 bytes) USABLE SPACE
      INSERT INTO HELPDESK.DISKS (DISK_ID,
                                  SIZE_MAX,
                                  SIZE_USED,
                                  PATH_NAME)
           VALUES (I,
                   204010946560,
                   0,
                   '/oraerp' || I || '/oradata/');
   END LOOP;

   EXECUTE IMMEDIATE 'TRUNCATE TABLE HELPDESK.DFILES';

   INSERT INTO HELPDESK.DFILES (NAME,
                                TABLESPACE_NAME,
                                CONTENTS,
                                BYTES,
                                READS,
                                WRITES)        --PUT DATAFILES IN DFILES TABLE
        SELECT B.FILE_NAME,
               C.TABLESPACE_NAME,
               C.CONTENTS,
               B.BYTES,
               NVL (SUM (A.PHYRDS), 0) READS,
               NVL (SUM (A.PHYWRTS), 0) WRITES
          FROM GV$FILESTAT A, DBA_DATA_FILES B, DBA_TABLESPACES C
         WHERE A.FILE# = B.FILE_ID AND B.TABLESPACE_NAME = C.TABLESPACE_NAME
      GROUP BY B.FILE_NAME,
               C.TABLESPACE_NAME,
               C.CONTENTS,
               B.BYTES;

   INSERT INTO HELPDESK.DFILES (NAME,
                                TABLESPACE_NAME,
                                CONTENTS,
                                BYTES,
                                READS,
                                WRITES)        --PUT TEMPFILES IN DFILES TABLE
        SELECT B.FILE_NAME,
               C.TABLESPACE_NAME,
               C.CONTENTS,
               B.BYTES,
               NVL (SUM (A.PHYRDS), 0) READS,
               NVL (SUM (A.PHYWRTS), 0) WRITES
          FROM GV$TEMPSTAT A, DBA_TEMP_FILES B, DBA_TABLESPACES C
         WHERE A.FILE# = B.FILE_ID AND B.TABLESPACE_NAME = C.TABLESPACE_NAME
      GROUP BY B.FILE_NAME,
               C.TABLESPACE_NAME,
               C.CONTENTS,
               B.BYTES;

   COMMIT;

   FOR DFILE IN C1
   LOOP
      FOR DISK IN C2
      LOOP
         IF DFILE.BYTES < DISK.SIZE_MAX - DISK.SIZE_USED
         THEN
            UPDATE HELPDESK.DFILES
               SET DISK_ID = DISK.DISK_ID,
                   NAME_NEW =
                      DISK.PATH_NAME
                      || SUBSTR (DFILE.NAME,
                                 INSTR (DFILE.NAME,
                                        '/',
                                        3,
                                        3)
                                 + 1)
             WHERE NAME = DFILE.NAME;

            UPDATE HELPDESK.DFILES
               SET BACKUP_COM = 'change ' || NAME || ' to ' || NAME_NEW,
                   ALTER_COM =
                         'ALTER DATABASE RENAME FILE '''
                      || NAME
                      || ''' TO '''
                      || NAME_NEW
                      || ''';'
             WHERE NAME = DFILE.NAME;

            UPDATE HELPDESK.DISKS
               SET SIZE_USED = SIZE_USED + DFILE.BYTES
             WHERE DISK_ID = DISK.DISK_ID;

            COMMIT;
            EXIT;
         END IF;
      END LOOP;
   END LOOP;
END DISK_GROUPING;
/

Now, querying DISKS, we see:

DISK_ID	SIZE_MAX	SIZE_USED	PATH_NAME
1	204,010,946,560	155,508,015,104	/oraerp1/oradata/
2	204,010,946,560	153,190,662,144	/oraerp2/oradata/
3	204,010,946,560	151,382,917,120	/oraerp3/oradata/
4	204,010,946,560	164,322,344,960	/oraerp4/oradata/
...	...	...	...
98	204,010,946,560	151,424,925,696	/oraerp98/oradata/
99	204,010,946,560	151,666,032,640	/oraerp99/oradata/
100	204,010,946,560	152,240,717,824	/oraerp100/oradata/

So, for instance, in the "/oraerp3/oradata/" 141GB of total 200GB will be used.

Querying DFILES (SELECT DISK_ID, NAME, NAME_NEW, READS FROM HELPDESK.DFILES ORDER BY DISK_ID, READS DESC;), we see:

DISK_ID	NAME	NAME_NEW	READS
1	/oracle89/oradata/ar_payment_schedules_all_011.dbf	/oraerp1/oradata/ar_payment_schedules_all_011.dbf	2,235,050,034
1	/oracle36/oradata/xxra_interfacex05.dbf	/oraerp1/oradata/xxra_interfacex05.dbf	26,396,759
1	/oracle8/oradata/user_data18.dbf	/oraerp1/oradata/user_data18.dbf	20,936,923
1	/oracle87/oradata/user_index81.dbf	/oraerp1/oradata/user_index81.dbf	19,653,078
1	/oracle78/oradata/ar_receivable_applications_all_007.dbf	/oraerp1/oradata/ar_receivable_applications_all_007.dbf	9,722,306
1	/oracle92/oradata/ax_sle_lines_078.dbf	/oraerp1/oradata/ax_sle_lines_078.dbf	7,687,651
1	/oracle78/oradata/czx26.dbf	/oraerp1/oradata/czx26.dbf	2.474.694
...	...	...	...

Observe how datafiles are spread in each filesystem, according to physical reads.

CAUTION: If you have, for instance, 2 or more datafiles, e.g. named "user_data18.dbf" in different old filesystems, you must check these datafiles will not be placed in the same new filesystem.
This procedure does not guarantee no overlaps will occur.