DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: tablespace space

  1. #1
    Join Date
    Jul 2006
    Posts
    195

    tablespace space

    I created a tablespace like this:

    CREATE TABLESPACE "PRI_TABLES"
    DATAFILE '/oracle/data/pri/pri_table.dbf' SIZE 50M
    autoextend on next 50M maxsize 500M
    LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

    As you can see I use the maxsize parm.

    When I run the following query:

    set pagesize 80
    set linesize 80
    col tablespace_name for a18
    COL total for 999,999 heading "MB|Total"
    COL free for 999,999 heading "MB|Free"
    COL max_free for 999,999 heading "MB|Extent|MaxFree"
    col used_pct for 990.99 heading "Used|percnt"
    col free_cnt for 9999 heading "Free|Extent|Count"

    select a.tablespace_name,round(100-b.free/a.total*100,2) Used_pct,
    a.total,b.free,b.max_free, b.free_cnt from (select tablespace_name,sum(bytes)/1024/1024 total
    from dba_data_files group by tablespace_name) a,
    (select tablespace_name, sum(bytes)/1024/1024 free, max(bytes)/1024/1024 max_free, count(bytes) free_cnt from dba_free_space
    group by tablespace_name) b where a.tablespace_name=b.tablespace_name;

    I get this output:

    MB Free
    Used MB MB Extent Extent
    TABLESPACE_NAME percnt Total Free MaxFree Count
    ------------------ ------- -------- -------- -------- ------
    SYSTEM 54.50 400 182 182 2
    PRI_TABLES 54.38 50 23 22 4

    I would like "MB Total" to reflect maxsize I told it to grow too instead of the
    initial size I set it too. Where would I find that information (maxsize)?

    My rational is that I really said the TS should be 500MB but I am only using
    50M at a time until I need the space.

    If somebody can point me in the right direction or alter my query I would
    appreciate it.

    Thanks in advance to all who answer.

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    The information you are looking for is stored in MAXBYTES column if and only if column AUTOEXTENSIBLE=YES

    For future reference, when posting code please wrap it up in code-tags so to make it readable... Go Advanced... user "#" code tags, like...
    Code:
    select   tablespace_name
            ,file_name
            ,bytes
            ,autoextensible
            ,maxbytes
    from    dba_data_files
    where   rownum < 10;
    Last edited by PAVB; 08-19-2010 at 09:43 PM. Reason: typo
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

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