Newbie needs help to determine if additional datafiles are needed
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Newbie needs help to determine if additional datafiles are needed

  1. #1
    Join Date
    Dec 1999
    Location
    Glynco, Ga 31524
    Posts
    4

    Cool

    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?

  2. #2
    Join Date
    Oct 2000
    Location
    Dallas:TX:USA
    Posts
    407
    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


    Rajeev Suri

  3. #3
    Join Date
    Dec 1999
    Location
    Glynco, Ga 31524
    Posts
    4

    Question Results from your script

    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









Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width