Monitoring tablespace usage

 Here is the script to monitor space used by tablespaces of a database. This is especially useful when tablespaces are autoextend off. Note that we are doing left outer join with dba_free_space, this is useful when the tablespace is completely exhausted and does not have any extents showing in dba_free_space (I do not find any query on Internet that does left outer join to dba_free_space, so wrote this one).

select *

  from (select ts.TABLESPACE_NAME                                       Tablespace_Name,

               round( ( total_mb                   ) )                  Total_MB,

               round( ( total_mb - nvl(free_mb, 0) ) )                  Used_MB,

               round( ( nvl(free_mb, 0)            ) )                  Free_MB,

               round( ( total_mb - nvl(free_mb, 0) ) * 100 / total_mb ) Pecentage_Used

          from (select TABLESPACE_NAME, sum(BYTES) / (1024*1024) total_mb

                  from dba_data_files

                 group by TABLESPACE_NAME) ts,

               (select TABLESPACE_NAME, sum(BYTES) / (1024*1024) free_mb

                  from dba_free_space

                 group by TABLESPACE_NAME) fs

         where ts.TABLESPACE_NAME = fs.TABLESPACE_NAME(+))

 where TABLESPACE_NAME like '%' -- Filter as needed

 order by Pecentage_Used desc;  

How to find to which database I'm connected?

This is a simple question for a DBA. Use this query for it.

select name from v$database;

But a normal user can use this only if there is select_catalog_role or select privilege on v$database views. What if a user without select privilege on v$database wants to see the database name? Here are the queries that can be used

select dbms_standard.database_name() database from dual;

select sys_context('userenv', 'db_name') from dual;