tablespace full

Posted on 2014-04-04
Last Modified: 2014-04-04
How we find that tablespacespace is full..?
Question by:tonydba
LVL 23

Accepted Solution

David earned 500 total points
ID: 39978500
  TO_CHAR(SUM(NVL(fs.bytes,0))/1024/1024, '99,999,990.99') AS MB_FREE
  user_free_space fs,
  user_tablespaces ts,
  user_users us
  fs.tablespace_name(+)   = ts.tablespace_name
AND ts.tablespace_name(+) = us.default_tablespace

Substitute names as appropriate.
LVL 32

Expert Comment

ID: 39978509
The attached script will show the size, amount free, amount used, %used as well as some other info by each tablespace. I use it often to see if usage is gettting too large.
LVL 22

Expert Comment

by:Steve Wales
ID: 39978530
That query is only reporting on tablespaces that the user can see where the tablespace name is someone's default tablespace

Also (with apologies to dvz), it uses non Ansi standard joins - which I personally find non intuitive.
  TO_CHAR(SUM(NVL(fs.bytes,0))/1024/1024, '99,999,990.99') AS MB_FREE
  dba_free_space fs
  left join dba_tablespaces ts on fs.tablespace_name   = ts.tablespace_name

Open in new window

This gives all tablespaces (and requires you have access to the dba_ views as opposed to the user_ views).

I use a variation on that script for my own use:

set feedback off
set echo off
set linesize 165
set pagesize 500
set heading on
clear breaks
col fname heading "Filename" format a60
col ts heading "Tablespace|Name" format a15
col cb heading "Total|Current|File Size" format 999,999,999,999
col free heading "Potential|Bytes Free" like cb
col percentfree heading "% Free|of|Pot.|Total|Bytes" format 999
ttitle "Percentage Freespace by Tablespace"
set markup html on preformat on
select a.tablespace_name ts, totb cb, freeb free, freeb/totb*100 percentfree
from (select tablespace_name, sum(bytes) totb from dba_data_files group by tablespace_name) a,
(select tablespace_name, sum(bytes) freeb from dba_free_space group by tablespace_name) b
where a.tablespace_name = b.tablespace_name
order by a.tablespace_name

Open in new window

Instead of just showing space free, shows Tablespace name, size of tablespace, freespace and percent freespace.
LVL 13

Expert Comment

ID: 39978755
Technically, since the question is "How we find that tablespacespace is full" the answer is when you get a "ORA-01653: unable to extend table" error.

Author Closing Comment

ID: 39979321
Thanks good.

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Dataware house query tuning 9 64
Consolidating oracle query results to a single line 8 52
Query to identify changes between rows of two tables 8 37
selective queries 7 21
Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now