In Oracle, all data needs to be located in TABLESPACES. These tablespaces are organize in datafiles, which can either bei autoextend or have a fixed size.
List all TABLESPACES and free/used memory
To find out all the Tablespaces and how much is free, use the following SQL as system user:
col "Tablespace" for a22
col "Used MB" for 99,999,999
col "Free MB" for 99,999,999
col "Total MB" for 99,999,999
select df.tablespace_name "Tablespace",
totalusedspace "Used MB",
(df.totalspace - tu.totalusedspace) "Free MB",
df.totalspace "Total MB",
round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace))
"Pct. Free"
from
(select tablespace_name,
round(sum(bytes) / 1048576) TotalSpace
from dba_data_files
group by tablespace_name) df,
(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name
from dba_segments
group by tablespace_name) tu
where df.tablespace_name = tu.tablespace_name ;
List all datafiles and extend ome of them
select * from V$DATAFILE;
select file_name, (bytes/1024/1024) as mbytes, autoextensible, (maxbytes/1024/1024) as maxmbytes from dba_data_files where tablespace_name = 'INDEX_FED';
and to enlarge a datafile, do this:
alter database datafile '/u02/oradata/DCHFED01/INDEX_FED_01.dbf' autoextend on maxsize 8000m;
More information about this can be found in the Oracle docs (make sure the docs are for the same version of Oracle that you use):
- https://docs.oracle.com/cd/B28359_01/server.111/b28310/dfiles003.htm#ADMIN11425
- https://docs.oracle.com/cd/B28359_01/server.111/b28310/tspaces014.htm#ADMIN11410