Oracle Database monitor tablespace script

With this script you can monitor tablespaces sizes in oracle database. This script returns next columns:

Tablespace - tablespace name

Size MB - currently allocated space of tablespace in megabytes

Free MB - free size from allocated space in tablespace in megabytes

Max MB - size of maximum space until tablespace will extend in megabytes

Maximum free space in MB - free space in tablespace until it will be full, this is real value for monitring because it is showing real free space in tablespace in megabytes

Used MB - what space is used frm allocated space by segments in tablespace in megabytes

Used % - percentage of used space in tablespace, this is also real monitoring value

Status - oracle offers best practice for tablespace used percentage values 85% - caution, 90% - alarm, 97% - critical, so this script is depend on this values.

(
select df.tablespace_name "Tablespace",
       round(df.tablespace_size / 1024 / 1024, 2) "Size MB",
       round(nvl(fs.free_space, 0) / 1024 / 1024, 2) "Free MB",
       round(df.tablespace_maxsize / 1024 / 1024, 2) "Max MB",
       round((df.tablespace_maxsize - (df.tablespace_size - round(nvl(fs.free_space, 0), 2)))
       / 1024 / 1024,2) "Maximum free space in MB",
       round((df.tablespace_size / 1024 / 1024 - fs.free_space / 1024 / 1024),2) "Used MB",     
       to_char(round((df.tablespace_size - round(nvl(fs.free_space, 0), 2)) /
                     df.tablespace_maxsize * 100,
                     2),
               '990D99') || ' %' "Used %",
       case
         when round((df.tablespace_size - round(nvl(fs.free_space, 0), 2)) /
                    df.tablespace_maxsize * 100,
                    2) > 95 then
          'CRITICAL - 95%'
         when round((df.tablespace_size - round(nvl(fs.free_space, 0), 2)) /
                    df.tablespace_maxsize * 100,
                    2) > 90 then
          'ALARM - 90%'
         when round((df.tablespace_size - round(nvl(fs.free_space, 0), 2)) /
                    df.tablespace_maxsize * 100,
                    2) > 85 then
          'CAUTION - 85%'
         else
          'OK < 85%'
       end "Status"
  from (select 
               tablespace_name,
               sum(bytes) tablespace_size,
               sum(decode(maxbytes, 0, bytes, null, bytes, maxbytes)) tablespace_maxsize
          from dba_data_files
         group by tablespace_name) df,
       (select 
               tablespace_name, sum(bytes) free_space
          from dba_free_space
         group by tablespace_name) fs
 where df.tablespace_name = fs.tablespace_name(+)
 )
UNION
(
select 
       ts.tablespace_name "Tablespace",
       round(ts.tablespace_size / 1024 / 1024, 2) "Size MB",
       round(nvl(tfs.free_space, 0) / 1024 / 1024, 2) "Free MB",
       round(ts.tablespace_maxsize / 1024 / 1024, 2) "Max MB",
       round((ts.tablespace_maxsize - (ts.tablespace_size - tfs.free_space))
       / 1024 / 1024, 2) "Maximum free space in MB",
       round((ts.tablespace_size / 1024 / 1024 - tfs.free_space / 1024 / 1024),2)
        "Used MB",
       to_char(round((ts.tablespace_size - tfs.free_space) /
                     ts.tablespace_maxsize * 100,
                     2),
               '990D99') || ' %' "Used %",
       case
         when round((ts.tablespace_size - tfs.free_space) /
                    ts.tablespace_maxsize * 100,
                    2) > 95 then
          'CRITICAL - 95%'
         when round((ts.tablespace_size - tfs.free_space) /
                    ts.tablespace_maxsize * 100,
                    2) > 90 then
          'ALARM - 90%'
         when round((ts.tablespace_size - tfs.free_space) /
                    ts.tablespace_maxsize * 100,
                    2) > 85 then
          'CAUTION - 85%'
         else
          'OK < 85%'
       end "Status"
  from (select 
               tablespace_name,
               sum(bytes) tablespace_size,
               sum(decode(maxbytes, 0, bytes, null, bytes, maxbytes)) tablespace_maxsize
          from dba_temp_files
         group by tablespace_name) ts,
       (select
               tablespace_name, free_space from dba_temp_free_space) tfs
 where ts.tablespace_name = tfs.tablespace_name
    )
order by 7 desc, 1 asc;

note :
If it'll work slow on your database, main reason for this is your recyclebin, if there is too many objects then dba_free_space view will work slow, solution for this is to purge dba_recyclebin

ORA-01950: no privileges on tablespace in Oracle database 12c

Recently I faced error ORA-01950: no privileges on tablespace during import schema in oracle database 12c R2, which was exported from 11g R2.
Cause: In 11g resource role implicitly contains unlimited tablespace privilege, but in 12c resource role doesn't contain unlimited tablespace privilege. Because my user have resource role and in 12c database it doesn't contain tablespace privilege we got :

ORA-01950: no privileges on tablespace USERS

Solution:

ALTER USER "user_name" quota unlimited on "tablespace_name";

or

grant unlimited tablespace to "user_name";

after that import run successfully.

ORA-55641: Cannot drop tablespace used by Flashback Data Archive

When I tried to drop tablespace appear next error:

SQL> drop tablespace FLASHBACK_DATA_ARCHVIE including contents and datafiles;
drop tablespace FLASHBACK_DATA_ARCHVIE including contents and datafiles
*
ERROR at line 1:
ORA-55641: Cannot drop tablespace used by Flashback Data Archive

this is because this tablespace is using for flashback data archive, for identify name of flashback data archive we can query:

select flashback_archive_name, status from dba_flashback_archive;
FLASHBACK_ARCHIVE_NAME         STATUS
------------------------------ -------
ANDO
fdba1

drop fdba1

SQL> drop flashback archive fdba1;
Flashback archive dropped.

after that we can drop tablespace

SQL> drop tablespace FLASHBACK_DATA_ARCHVIE including contents and datafiles;
Tablespace dropped.

ORA-29857: domain indexes and/or secondary objects exist in the tablespace

While dropping a tablespace it fails with error ORA-29857 like below.

SQL> drop tablespace example including contents and datafiles cascade constraints;
drop tablespace example including contents and datafiles cascade constraints
*
ERROR at line 1:
ORA-29857: domain indexes and/or secondary objects exist in the tablespace

The problem arises because there was an attempt to drop a tablespace which contains secondary objects and/or domain indexes.

Solution:

Drop the domain indexes on the tablespace. Also, find the domain indexes which created secondary objects in this tablespace and drop them. Then try dropping the tablespace.

SQL> select index_name from dba_indexes where index_type = 'DOMAIN' 
and tablespace_name = 'EXAMPLE';
no rows selected

which means it does not have any domain index. Next you should look for secondary objects.

select distinct owner from dba_tables where tablespace_name='EXAMPLE';
OWNER
------------------------------
HR
OE
PM
SH
IX

If you think you don't need these users you can try to delete users

SQL> drop user HR cascade;
User dropped.

SQL> drop user OE cascade;
User dropped.

SQL> drop user sh cascade;
User dropped.

SQL> drop user oe cascade;
User dropped.

SQL> drop user ix cascade;
User dropped.

after that we can delete tablespace

SQL> drop tablespace example including contents and datafiles;