Re: Oracle data types & sizes

From: <yong321_at_yahoo.com>
Date: Mon, 15 Jan 2001 18:42:27 GMT
Message-ID: <93vgai$rnv$1@nnrp1.deja.com>

For your question 1, please take a look at my humble article at http://www.stormloader.com/yonghuang/computer/NumberInDatafile.txt

It's only only about positive integers at this moment.

For your question 2, DBA_FREE_SPACE tells you how much is free in each datafile. To get to the details about tables and users as to their usage, you have to look at user/all/dba_segments and do your own math. Or use this script from somebody other than me:

SET ECHO OFF

rem ************************************************************ ******
rem          DBA REPORTS
rem ************************************************************ ******
REM
REM     Program:        whohowmu.SQL
REM     Funtion:        List how much of the database each uses has

REM

rem ************************************************************ ******

prompt Print how much database space each user has set newpage0
ttitle center 'Database Usage by user and Tablespace'- right 'Page:' format 999 sql.pno skip skip break on owner skip 2
col K format 999,999,999 heading 'Size K' col ow format a24 heading 'Owner'
col ta format a30 heading 'Tablespace'
spool whohowmu.lst
set feedback off
@@time
set feedback 6

select  us.name                                 ow,
        ts.name                                 ta,
        sum(seg.blocks*ts.blocksize)/1024       K
from    sys.ts$ ts,
        sys.user$ us,
        sys.seg$ seg
where   seg.user# = us.user#
and     ts.ts# = seg.ts#

group by us.name,ts.name
/
prompt End of Report
spool off

Yong Huang
yong321_at_yahoo.com

In article <93u0a5$h7g_at_freepress.concentric.net>,   "Mike DiChiappari" <mike_at_mysticsoft.com> wrote:
> Two related questions:
>
> 1) How does Oracle store Numbers (integers in particular)? How much
 space
> (in bytes) does a Number(38,0) take up.
>
> 2) Is there a way to determine how much space in a datafile (or
 tablespace)
> is consumed by each user (or table)?
>
> Thanks,
> Mike

Sent via Deja.com
http://www.deja.com/ Received on Mon Jan 15 2001 - 12:42:27 CST