Schema Size

From: Norman Dunbar <Norman.Dunbar_at_lfs.co.uk>
Date: Thu, 7 Nov 2002 11:10:04 -0000
Message-ID: <E2F6A70FE45242488C865C3BC1245DA702E4BFEC@lnewton.leeds.lfs.co.uk>

-----Original Message-----
From: ermin_at_dzinic.com (Ermin) [mailto:ermin_at_dzinic.com] Subject: Re: Schema Size

>> By the way I have another question, regarding the dba_ts_quotas view,
>> in that view I can see only few schemas, not all schemas created on
my
>> Oracle Istance. Do you maybe know why this is happening? Altough, I'm
>> able to query every single schema with user_ts_quotas view(of course
>> when I'm logged in as that user/schema). ?????????

I'm not sure - I've never noticed this in my own databases, but the source code for DBA_TS_QUOTAS is shown below, so I presume if you have users who have entries in SYS.TSQ$ where the maxblocks columns is set to zero, then they won't show up. Mind you, this is just filtering out users where you have specifically said 'alter user xxx quota 0 on yyyy' from the results, but if your users have not been given any quota on any tablespaces then they will not appear in the output of DBA_TS_QUOTAS.

Cheers,
Norman.

CREATE OR REPLACE FORCE VIEW SYS.DBA_TS_QUOTAS (TABLESPACE_NAME, USERNAME, BYTES, MAX_BYTES, BLOCKS,  MAX_BLOCKS)
AS
SELECT ts.NAME, u.NAME,

       q.blocks * ts.BLOCKSIZE,
       DECODE(q.maxblocks, -1, -1, q.maxblocks * ts.BLOCKSIZE),
       q.blocks, q.maxblocks

FROM sys.tsq$ q, sys.ts$ ts, sys.USER$ u

WHERE q.ts# = ts.ts#
  AND q.USER# = u.USER#
  AND q.maxblocks != 0;

Received on Thu Nov 07 2002 - 05:10:04 CST