How To Check Tablespace Size Dynamically

------------------------------------------------------------------------
 How To Check Tablespace Size Dynamically 
------------------------------------------------------------------------

If you want to check Tablespace Size then use Below query.

 select tablespace_name,file_name,bytes/1024/1024 MB from DBA_DATA_FILES;

 If you want to check Tablespace Size of particular tablespace then use below Query

 select tablespace_name,file_name,bytes/1024/1024 MB from DBA_DATA_FILES where tablespace_name='USERS';

 If you do not want to mention tablespace name again and again for different tablespaces then use Dynamic query for it

 select tablespace_name,file_name,bytes/1024/1024 MB from DBA_DATA_FILES where tablespace_name='&VALUE';

 Here i have used '&' symbol ahead of VALUE ,by using it it will ask for tablespace name at run time.

 If you do not want to write query again and again for tablespace size make it as a saved notepad file with .sql extension

 Name of .sql file is Tablespace_size.sql .

 Content of file :

col tablespace_name for a25
col file_name for a40
select tablespace_name,file_name,bytes/1024/1024 MB from DBA_DATA_FILES where tablespace_name='&VALUE';

Here

col tablespace_name for a25
col file_name for a40

used for getting output in readable form.

To run it from sqlplus just use @filelocation.file_name.sql

example : SQL> @Tablespace_Size.sql;

after invoking script , it asks for tablespace name ,then enter name in capital form or use UPPER function before '&VALUE' like

select tablespace_name,file_name,bytes/1024/1024 MB from DBA_DATA_FILES where tablespace_name=UPPER('&VALUE');

Here you can easily check that either you use Tablespace Name in small letters or capital you will get the desired result.