Re: How to determine oracle user size?(B

From: joel garry(B <joel-garry_at_home.com>
Date: Thu, 19 Feb 2009 11:23:33 -0800 (PST)
Message-ID: <82243877-d40a-4b0d-bd98-ae52f5d19c4a_at_r37g2000prr.googlegroups.com>



On Feb 19, 7:05 am, Ming$BGl(B <l..._at_tech-trans.com> wrote:
> On 2$B7n(B19$BF|(B, $B2<8a(B12$B;~(B47$BJ,(B, Ram <krishna..._at_gmail.com> wrote:
>
>
>
>
>
> > On Feb 19, 7:15 am, Ming$BGl(B <l..._at_tech-trans.com> wrote:
>
> > > Hi
>
> > > Any SQL statement can find theOracleuser/ownersizewhen more than
> > > oneuserin aOracleinstance? Thx!
>
> > > Ming
>
> > Hi,
>
> > select sum(bytes/1024/1024)"size" from dba_segments where
> > owner='&owner';
>
> > Regards
>
> > RAM
>
> RAM,
>
> I executed the your sql statement but no result acquired. Any idea?
>
> Ming

I note that simply cutting and pasting the command gives no results, but subsequently entering / it asks for the username, which must be entered in caps, unless you've created a user in mixed case (as some non-Oracle tools or special use of quotes can do). You'll also get an ORA-942 error if you don't have access to dba_segments, perhaps the reasone for the SYSDBA comment by Paulie.

Whether this accurately conveys the size of the user is debatable, you need to specify for what purpose. For example, the command says one of my users is 45525.6875, EM says 45,527.1MB, while exporting the user is only 34G. That users tablespace is 46,200MB. Do I need to freak out that I'm about to run out of space? I hope it is obvious that there is insufficient information here to answer such a question. (And in fact, EM tells me I can get gigabytes back if I shrink certain objects, but then I don't get it back. Looking at it this way, anyways, which brings up the important points, that it is difficult to evaluate free space in blocks exactly, not trivial to evaluate free space in tablespaces, and users may not control how much space they are really using.).

jg

--
_at_home.com is bogus.
http://www.datacenterknowledge.com/the-gallery-of-exploding-servers/
Received on Thu Feb 19 2009 - 13:23:33 CST

Original text of this message