Home » SQL & PL/SQL » SQL & PL/SQL » Max space I can use in my Schema
Max space I can use in my Schema [message #297800] Sun, 03 February 2008 17:43 Go to next message
florida
Messages: 82
Registered: April 2006
Member
I am trying to find out how big my Schema is in Oracle 9i.

This SQL seems to give the total amount of space that I have used in my Schema?
SELECT tablespace_name, owner
Sum(bytes)/1024/1024 AS total_size_mb
FROM dba_segments
WHERE owner = MYSCHEMANAME


How do I find the total size of what I can use in my Schema?
Re: Max space I can use in my Schema [message #297838 is a reply to message #297800] Sun, 03 February 2008 23:16 Go to previous messageGo to next message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
select df.tablespace_name,
sum(fs.bytes) free_space
from dba_data_files df,
dba_free_space fs
where df.tablespace_name = fs.tablespace_name
group by df.tablespace_name
/




regards,
Re: Max space I can use in my Schema [message #297855 is a reply to message #297800] Mon, 04 February 2008 00:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
This SQL seems to give the total amount of space that I have used in my Schema?

This is an invalid SQL, if you try it you get an error.
You have to add a "group by" clause.

Quote:
How do I find the total size of what I can use in my Schema?

Can you precise your question.
Do you want what you currently used? what you can still use given the tablespaces on which you have quota? what free space there are in the tablespaces you currently used? in the tablespace you have quotas?...
Many possible questions.

Regards
Michel
Re: Max space I can use in my Schema [message #298059 is a reply to message #297800] Mon, 04 February 2008 19:47 Go to previous messageGo to next message
florida
Messages: 82
Registered: April 2006
Member
Thanks, for your replies.

I need this: what free space there are in the tablespaces you currently used

I am not familiar with quotas and not sure what this means: in the tablespace you have quotas
Re: Max space I can use in my Schema [message #298099 is a reply to message #298059] Tue, 05 February 2008 00:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
dba_free_space gives free space in tablespaces
dba_segments gives tablespace each segment (and so user) uses.
Combine the 2.

Note: an outer join is necessary.

Regards
Michel
Re: Max space I can use in my Schema [message #298340 is a reply to message #298099] Tue, 05 February 2008 16:34 Go to previous messageGo to next message
florida
Messages: 82
Registered: April 2006
Member
Thanks
Re: Max space I can use in my Schema [message #298394 is a reply to message #298340] Wed, 06 February 2008 00:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks to post the query you find for furture readers.

Regards
Michel
Re: Max space I can use in my Schema [message #298407 is a reply to message #298394] Wed, 06 February 2008 01:11 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Michel Cadot wrote on Wed, 06 February 2008 07:47
Thanks to post the query you find for furture readers.

Regards
Michel


This doesn't make sense Michel. On the one hand you jump on everyone who spoonfeeds other people, on the other hand you want the original poster to publish the solution in order to help future visitors..
Re: Max space I can use in my Schema [message #298411 is a reply to message #298407] Wed, 06 February 2008 01:29 Go to previous message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I think if someone make the effort to search in the forum before posting then it is likely he already searched by himself (maybe I'm wrong).
Say it is a reward for those who search before posting. Smile

Regards
Michel
Previous Topic: PLS-00103
Next Topic: Problem in concectnation?
Goto Forum:
  


Current Time: Thu Dec 08 02:32:05 CST 2016

Total time taken to generate the page: 0.11678 seconds