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

Thread: temp tablespace max extent reached

  1. #1
    Join Date
    Jun 2001

    Red face

    Can some tell me if I'm right or not and wht I need to do if not.

    my temp,system and user tablespace for a database has reached it's max extent and running 0 now. do I need to increase teh maxextent or how do I go about it.




    accutually, I have this much space right now 2097152 and it showing as initial and next_extent and min_extent is 1, it looks like no maxextent was set for this tablespace because I query dba_tablespaces and all the 3 production database that I manage has 0 as their maxextent. Do you think I am ok.



    [Edited by ybadejo on 11-20-2001 at 09:57 AM]
    smiles off

  2. #2
    Join Date
    Nov 2000
    New Delhi, India
    Try 'n increase the tablespace size


  3. #3
    Join Date
    Sep 2001
    Yeah sure increase the maxextens, just make sure the tablespace has enough room to grow inside the datafile.

    If the datafile reached its limit then the tablespace cant grow no matter what

    select nvl(b.tablespace_name,nvl(a.tablespace_name,'UNKNOWN')) name
    , kbytes_alloc kbytes
    , kbytes_alloc-nvl(kbytes_free,0) used
    , nvl(kbytes_free,0) free
    , ((kbytes_alloc-nvl(kbytes_free,0))/kbytes_alloc)*100 pct_used
    , nvl(largest,0) largest
    from (select sum(bytes)/1024 Kbytes_free
    , max(bytes)/1024 largest
    , tablespace_name
    from dba_free_space
    group by tablespace_name) a
    , (select sum(bytes)/1024 Kbytes_alloc
    , tablespace_name
    from dba_data_files
    group by tablespace_name) b
    where a.tablespace_name (+) = b.tablespace_name
    order by 1

    Run that and see how much room the tablespace has to grow inside the datafile.

    That will show you your tablespaces and how much there is spare at the moment.

    select df.tablespace_name
    , df.file_name
    , df.bytes/1024 total_size_KB
    , nvl(fr.bytes/1024,0) free_space_KB
    , df.bytes/1024/1024 total_size
    , nvl(fr.bytes/1024/1024,0) free_space
    , ((df.bytes-nvl(fr.bytes,0))/df.bytes)*100 pct_used
    from (select sum(bytes) bytes
    , file_id
    from dba_free_space
    group by file_id) fr
    , dba_data_files df
    where df.file_id = fr.file_id(+)
    and DECODE(UPPER('&&ts_name'),NULL,'x',df.tablespace_name) like DECODE(UPPER('&&ts_name'),NULL,'x',UPPER('&&ts_name'))
    order by 1, df.file_id

    Will show the datafile usage.

  4. #4
    Join Date
    Sep 2001
    Makati, Philippines
    you might have used all available space in your tablespace
    and the autoextend was set to OFF.
    alter database datafile '/u01/oradata/PRD/system01.dbf' autoextend on;

    Or your table itself has to be reconfigure in its storage def.
    alter table TABLENAME storage(maxextents 99999);

  5. #5
    Join Date
    Sep 2001
    Fort Smith
    you have sm$ts_free, sm$ts_avail, sm$ts_used tables owned by sys. union them by joining them on tablespace_name you get all the data you wanted..later you can have modifications to the sql so that the output is as you wanted.

    select a.tablespace_name,
    round(a.bytes/1024/1024) allotted_mb,
    round(u.bytes/1024/1024) used_mb,
    round(f.bytes/1024/1024) free_mb
    from sys.sm$ts_avail a,
    sys.sm$ts_used u,
    sys.sm$ts_free f
    where a.tablespace_name = u.tablespace_name
    and u.tablespace_name = f.tablespace_name
    and f.tablespace_name = a.tablespace_name

  6. #6
    Join Date
    Jun 2001

    Unable to Extend in Temp tablespace.

    thanks to you all for your reply, now I have another problem with my temp tablespace. I have 2129920 as the amxnext extent and I have 1302528 as the max free space and I got an error in my alert log that unable to extend in tablespace Temp and I went ahead to add a datafile to the Temp giving 827K size.
    Now my question is I still have a warning sign although I checked my alert log and there is no error but when I do this query
    select max(bytes) from dba_free_space it gives me this 1302528 and when I do select sum(bytes) from dba_free_space it gives me the total of all the sizes in all the datafiles, what am I doing wrong or should I just watch for a while and see what happens.


    smiles off

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