MONITORING TABLEPSACE SIZE

https://dbissues.blogspot.com/2018/06/monitoring-tablepsace-size.html

1:) To check total size of table space in "MB";
select tablespace_name,sum(bytes)/1024/1024 "total size in mb" from dba_data_files

group by tablespace_name;


2:) To check free space in table space in "MB";
select tablespace_name,sum(bytes)/1024/1024 "free space in mb" from dba_free_space

group by tablespace_name; 

3:) You can check the single datafile full space by the following command;
select tablespace_name,file_id,file_name,sum(bytes)/1024/1024 "total size in mb" from dba_data_files

where file_id=6

group by tablespace_name,file_id,file_name; 

4:) You can check the single datafile free space by the following command;

select tablespace_name,file_id,sum(bytes)/1024/1024 "size in MB" from dba_free_space

where file_id=6

group by tablespace_name,file_id; 

5:)To add datafile in tables pace USERS;
alter table space users add datafile '/u01/app/oracle/oradata/orcl/users03' size 2m; 

6:) To drop datafile from tablespace USERS;
alter tables pace users drop datafile '/u01/app/oracle/oradata/orcl/users02.dbf';