The database filenames are stored in the table dba_data_files
SQL> describe dba_data_files;
Name NULL? Type
---------------- ----- ---------------------------
FILE_NAME VARCHAR2(513)
FILE_ID NUMBER
TABLESPACE_NAME VARCHAR2(30)
BYTES NUMBER
BLOCKS NUMBER
STATUS VARCHAR2(9)
RELATIVE_FNO NUMBER
AUTOEXTENSIBLE VARCHAR2(3)
MAXBYTES NUMBER
MAXBLOCKS NUMBER
INCREMENT_BY NUMBER
USER_BYTES NUMBER
USER_BLOCKS NUMBER
ONLINE_STATUS VARCHAR2(7)
We can see the files datasize in MB with:
SQL> select FILE_NAME,TABLESPACE_NAME,BYTES/1024/1024 Mbytes,AUTOEXTENSIBLE from dba_data_files order by TABLESPACE_NAME;
We can use the tables dba_data_files and dba_free_space to know the size and the free space of the tablespaces:
SQL> select a.TABLESPACE_NAME,
round(a.BYTES/(1024*1024),2) mbytes_total,
round(b.BYTES/(1024*1024),2) mbytes_free,
round(b.largest/(1024*1024),2) largest,
round(((a.BYTES-b.BYTES)/a.BYTES)*100,2) percent_used
from
(
select TABLESPACE_NAME,
sum(BYTES) BYTES
from dba_data_files
group by TABLESPACE_NAME
)
a,
(
select TABLESPACE_NAME,
sum(BYTES) BYTES ,
max(BYTES) largest
from dba_free_space
group by TABLESPACE_NAME
)
b
where a.TABLESPACE_NAME=b.TABLESPACE_NAME;
If we want to add 10GB to a tablespace then:
ALTER TABLESPACE APP_LOG_INDX ADD DATAFILE '/u04/oradata/XXXXX/APP_log_indx_10.dbf' SIZE 10000M AUTOEXTEND OFF;
For more info please visit:
Changing Datafile Size
You still can have problems with the disk usage due to the archived logs.
But you can easily remove them with the rman tool:
$ rman TARGET /
RMAN> delete archivelog all;