Sometimes we get a message from DB13 “Check DB” operaion like ;

BR0970W Database administration alert – level: WARNING, type:
TABLESPACE_FULL, object: PSAPSR3DB, value: 95.46% (> 95%)

This message means that , tablespace PSAPSR3DB‘s usage size over the systems threshold value of %95

Let’s check the datafiles size for tablespace PSAPSR3DB

SQL >select FILE_ID,BYTES/1024/1024,FILE_NAME from dba_data_files
where TABLESPACE_NAME =’PSAPSR3DB’;

Make a decision for “which datafile can we resize?”,

Also don’t forget to check physical storage location for this operation.

For this example i would like to choose datafile number of 53 for RESIZE operation which is under drive “I”

I would like to resize this datafile to 25600MB with following SQL command ;

SQL>alter database datafile ‘I:\ORACLE\SID\SAPDATA5\SR3DB_7\SR3DB.DATA7′ resize 25600M;

Now we can check the file size of the datafile

SQL>select FILE_ID,BYTES/1024/1024,FILE_NAME from dba_data_files where TABLESPACE_NAME =’PSAPSR3DB’ and FILE_ID = 53;