OraFAQ Forum: Server Administration

Home » RDBMS Server » Server Administration » tablespace (Oracle 9i)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

tablespace [message #474593]

Tue, 07 September 2010 07:50

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.

Report message to a moderator

Re: tablespace [message #474594 is a reply to message #474593]

Tue, 07 September 2010 07:53

cookiemonster
Messages: 13962
Registered: September 2008
Location: Rainy Manchester

Senior Member

I usually use Tom Kyte's maxshrink script, found here

Report message to a moderator

Re: tablespace [message #474595 is a reply to message #474593]

Tue, 07 September 2010 07:54

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

Report message to a moderator

Re: tablespace [message #474602 is a reply to message #474595]

Tue, 07 September 2010 08:31

Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...

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

Previous Topic:	Whole Database Report !
Next Topic:	network encryption

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Jan 12 09:36:38 CST 2025