Query Oracle dictonary for tablespace available space.
select tablespace_name,used_space*8,tablespace_size*8,used_percent from dba_tablespace_usage_metrics where tablespace_name=’<tbsp_name>’
Query Oracle dictionary for tablespace related informations.
select * from dba_tablespaces where tablespace_name='SAMPLE_DAT'
Query Oracle dictionary for data file(s) informations.
select * from dba_data_files where tablespace_name='SAMPLE_DAT'
Add new datafile into existing tablespace
alter tablespace av_target_dat add datafile '/mountpoint_name/oradata/db_name/tablespace_name_01.dbf' size 500M;
Resize datafile
alter database datafile '/mountpoint_name/oradata/db_name/tablespace_name_02.dbf' resize 100M;
Datafile Maintenance: Rename datafile name
Query Oracle dictionary for segments in particular tablespace
select * from dba_segments where tablespace_name='SAMPLE_DAT'
Query Oracle dictionary for extents comprising the segments in a particular tablespaces
select * from dba_extents where segment_name='TABLE_ABC' and tablespace_name='SAMPLE_DAT'
* dba_xxxx view above can be replaced by user_xxxx if you do not have system privilege and you will only see objects/informations related to your login schema