Solved

tablespace full

Posted on 2014-04-04
5
341 Views
Last Modified: 2014-04-04
How we find that tablespacespace is full..?
0
Comment
Question by:tonydba
5 Comments
 
LVL 23

Accepted Solution

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

Substitute names as appropriate.
0
 
LVL 32

Expert Comment

by:awking00
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.
Tablespace-Free.txt
0
 
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.
SELECT
  ts.tablespace_name,
  TO_CHAR(SUM(NVL(fs.bytes,0))/1024/1024, '99,999,990.99') AS MB_FREE
FROM
  dba_free_space fs
  left join dba_tablespaces ts on fs.tablespace_name   = ts.tablespace_name
GROUP BY
  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.
0
 
LVL 13

Expert Comment

by:magarity
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.
0
 

Author Closing Comment

by:tonydba
ID: 39979321
Thanks good.
0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
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.
Via a live example, show how to take different types of Oracle backups using RMAN.

813 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

10 Experts available now in Live!

Get 1:1 Help Now