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

Thread: #extents > maxextents ??

  1. #1
    Join Date
    May 2001
    Posts
    22

    Question

    hi,

    I would like to know how many extents are currently used in my tablespace ?
    (to avoid :
    ORA-01653: unable to extend table/index xxxx by xx in tablespace xxx")

    I know, it seems simple but I've a doubt ,now....

    so , I tried :

    SQL> select sum(extents) from dba_segments where tablespace_name = 'TEST_DATA';

    COUNT(*)
    ----------
    242
    242, ok .

    but see the follow query :

    SQL> select TABLESPACE_NAME,MAX_EXTENTS from dba_tablespaces where tablespace_name='TEST_DATA';

    TABLESPACE_NAME MAX_EXTENTS
    ------------------------------ -----------
    TEST_DATA 50

    I don't understand this ?? :(

    total extents used in my tbsp(242) > max_extent (50) :( :(

    How is it possible ??



    Thanks a lot.

    have a nice day

    Vincent

    ps:
    thanks all people answered to 'Gayatri' in the 'Role of DBA' thread

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    The storage parameters defined at table/index level always override tablespace level.

  3. #3
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204
    Tablespace storage values are just default values for the objects created therein.
    "I do not fear computers. I fear the lack of them." Isaac Asimov
    Oracle Scirpts DBA's need

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by Louis
    SQL> select TABLESPACE_NAME,MAX_EXTENTS from dba_tablespaces where tablespace_name='TEST_DATA';

    TABLESPACE_NAME MAX_EXTENTS
    ------------------------------ -----------
    TEST_DATA 50

    I don't understand this ?? :(

    total extents used in my tbsp(242) > max_extent (50) :( :(

    How is it possible ??
    50 up there does not mean that your tablespace will only accept 50 extents in it. It means that each table/index in that tablespace can have up to 50 extents (unless othervise specified in the segment storage parameters). So even if you never specify MAXEXTENTS for your tables and indexes, you can easily have thousands of extents in you tablespace which have MAXEXTENTS limited to 50....
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  5. #5
    Join Date
    May 2001
    Posts
    22

    Thumbs up

    thanks for that precisions


    [Edited by Louis on 04-12-2002 at 03:11 AM]

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