Newbie needs help to determine if additional datafiles are needed

[jrowland]

Hi, Am getting confused trying to figure out if we need to add additional
datafiles to tablespaces PODDATA and PODINDEX even though it seems that
the Used bytes for a tablespace is no where near the Max_bytes setting.
When running script that prints out tablespace size statistics it shows the following:

Size(Mg) Used(MG) Free(MG)
PODDATA 3,743 3,743 (100%) 0 (0%)
PODINDEX 2,081 2,081 (100%) 0 (0%)

When I query the table dba_data_files I get the following info which shows:

FILE NAME BYTES MaxBytes.
----------------------------------- -------------- --------------

E:\ORADATA\MOMD\DATA\MOMD_D.DBF 3,924,346,880 8,589,930,496
E:\ORADATA\MOMD\DATA\MOMD_I.DBF 2,182,211,584 8,589,930,496

I am confused why it shows no free space but the Used Byte Size is no
where near the MaxBytes Size.
The settings via Storage Manager for PODDATA and PODINDEX are as follows:

TableSpaces
PODDATA Initial-10240K Next 10240K Max Value2147483645 K
PODINDEX Initial-10240K Next 10240K Max Value 2147483645K

Datafiles:
PODDATA - MOMD_D.DBF File Size: 3832370 Auto Extend Increment 10K,
Max Extent 8388604
PODINDEX - MOMD_I.DBF File Size: 2131066K Auto Extend Increment 10K,
Max Extent: 8388604

Also performing the following give me:


As you may have determined, I am fairly new to using Oracle trying to support a application that
was developed by outside source but no longer supported by contractor.
How do I interpet the above results. Do I need to add additional datafiles?

[rsuri]

could you post the query here that is showing the used and free space ? I think something is wrong in that query.

For total space you should look into dba_data_files and for free space look into dba_free_space and do the maths.

There are sample scripts available on this forum under "Oracle Scripts" on the left side.

Here is a sample from there --

select
a.TABLESPACE_NAME,
a.total,nvl(b.used,0) USED,
nvl((b.used/a.total)*100,0) PCT_USED
from
(select TABLESPACE_NAME,
sum(bytes)/(1024*1024) total
from sys.dba_data_files
group by TABLESPACE_NAME) a,
(select TABLESPACE_NAME,bytes/(1024*1024) used
from sys.SM$TS_USED) b
where a.TABLESPACE_NAME=b.TABLESPACE_NAME(+);


Try this and see if this also shows the same result.

- Rajeev

[jrowland]

Here are the results from your script:

TABLESPACE_NAME TOTAL USED PCT_USED
------------------------------ ---------- ---------- ----------
PODDATA 3742.54883 3742.54688 99.9999478
PODINDEX 2081.11914 2081.11719 99.9999062
PODROLLBACK1 1089.74219 6.63085938 .608479643
PODROLLBACK2 1247.18359 4.0234375 .322601862
PODTEMP 115.003906 0 0
SYSTEM 5200.33203 5180.24219 99.6136815

Here is the output from quering dba_free_space:
TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_F
------------------------------ ---------- ---------- ---------- ---------- ----------
SYSTEM 1 2652331 20971520 10240 1
SYSTEM 1 8570 40960 20 1
SYSTEM 1 7810 51200 25 1
PODROLLBACK1 4 3462 1135589376 554487 4
PODROLLBACK1 4 347 133120 65 4
PODROLLBACK2 5 2062 1303545856 636497 5
PODTEMP 6 2 120588288 58881 6

The scripts i was using were:
rem set feedback off
set termout on
set pagesize 100
set linesize 75

spool c:\temp\quickcheck

--
-- ************* NAME/VERSIONS **************
--

set heading off
set verify off
column today NEW_VALUE p_currdate noprint

select TO_CHAR(SYSDATE,'fmMonth ddth, yyyy') today
from dual;



clear breaks
clear computes
clear columns

set heading off
column tablespace_name format a15 truncated
column file_name format a35 truncated
column bytes heading 'Bytes' format 999,999,999,999,999
column maxbytes heading 'MAXBYTES'format 999,999,999,999,999
column prcnt heading 'PERCENTAGE' format 999,999.9999
break on tablespace_name skip 1
compute sum label 'TOTAL' of bytes maxbytes on tablespace_name


select tablespace_name,file_name,bytes,maxbytes,(bytes/maxbytes)*100 prcnt from dba_data_files
order
by tablespace_name,file_name;

spool off
exit