tablespace [message #474593] |
Tue, 07 September 2010 07:50 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
ajaykumarkona
Messages: 399 Registered: August 2010
|
Senior Member |
|
|
Hi Experts,
We got the error
ORA-01652: unable to extend temp segment by 8192 in tablespace INDX.
For that we raised a request the DBA team.
They sent the following output
Tname Tsize Tused(MB) TFree(MB) PUsed PFree
DATA 718248 695287 22961 97 3
IDX 1488 1165 323 78 22
UNDO 14002 13472 530 96 4
INDX 302035 297989 4046 99 1
USERS 650 547 103 84 16
SYSTEM 3800 1931 1869 51 49
SYSAUX 4762 2825 1937 59 41
TOOLS 9 0 9 1 99
Please give me a query to get this output.
I want to find howmuch tablespace size,howmuch tablespace is used and howmuch tablespace is free for each tablespace in the database .
Please help me.
|
|
|
|
Re: tablespace [message #474595 is a reply to message #474593] |
Tue, 07 September 2010 07:54 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
adit_me1
Messages: 49 Registered: October 2007 Location: BANGALORE
|
Member |
|
|
select a.tablespace_name, Total_Space_in_GB,free_Space_in_GB,datafiles# from
(
select tablespace_name,round(sum(bytes)/1024/1024/1024,2) Total_Space_in_GB
from dba_data_files
group by tablespace_name
)a,
(
select tablespace_name,round(sum(bytes)/1024/1024/1024,2) free_Space_in_GB from dba_free_space
group by tablespace_name
)b,
(
select tablespace_name,count(*) datafiles#
from dba_data_files
group by tablespace_name
)c
where a.tablespace_name = b.tablespace_name
and b.tablespace_name = c.tablespace_name
and a.tablespace_name = c.tablespace_name
|
|
|
Re: tablespace [message #474602 is a reply to message #474595] |
Tue, 07 September 2010 08:31 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68675 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
"c.datafiles#" can be calculated in the "a" and avoid then 2 scan of dba_data_files and keep only "a".
Some tablespaces may have no free_space, so you have to outer join with "b".
Note that to be able to create an object you must have a minimum of contiguous space (which depends on your tablespace type and create objects statement), so the total is irrelevant.
Regards
Michel
[Updated on: Tue, 07 September 2010 08:33] Report message to a moderator
|
|
|