Two system tablespaces
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Two system tablespaces

  1. #1
    Join Date
    Apr 2003
    Posts
    24

    Two system tablespaces

    When i search the dba_free_space it shows two system tablespaces. Is it possible to have two system tablespaces?

    TABLESPACE_NAME BYTES BLOCKS
    ------------------------------ ---------- ----------
    SYSTEM 262144 32
    SYSTEM 4128768 504

    and what is the query to see the free extents for a specific segment.

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    there arent two tablespaces, there are two datafiles

  3. #3
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938

    Re: Two system tablespaces

    I find it strange, even odd to create/have two datafiles in the system tablespace. I have never seen a file in a system tablespace grow more than 400-500M.

    Probably with all the Oracle extras it can become 1G but even so: two datafiles?
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g,12c
    email: ocp_9i@yahoo.com

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    There probably aren't two datafiles there - I would say he simply queried:

    SELECT tablespace_name, bytes, blocks
    FROM dba_free_space
    WHERE tablespace_name = 'SYSTEM';

    and there happens to be two chuncks of free space in SYSTEM tablespace, hence he got two rows as a result....
    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
    Apr 2003
    Posts
    24
    It's not two datafiles.

    This was my query

    "select tablespace_name,bytes,blocks from dba_free_space where tablespace_name='SYSTEM';"

    and the result was..

    TABLESPACE_NAME BYTES BLOCKS
    ------------------------------ ---------- ----------
    SYSTEM 262144 32
    SYSTEM 4128768 504

  6. #6
    Join Date
    Mar 2002
    Posts
    534
    From the Oracle Doc:

    "DBA_FREE_SPACE lists the free extents in all tablespaces"

    I guess this will answer your first question.

  7. #7
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727
    "select tablespace_name,bytes,blocks from dba_free_space where tablespace_name='SYSTEM';"
    If you are using above query, there are chances. Fortunately you got only 2 now.

    To get one SYSTEM tablespace try this

    Code:
    select TABLESPACE_NAME from dba_tablespaces where TABLESPACE_NAME='SYSTEM';
    Still you are getting two, please raise iTar to oracle.


    Good luck
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

  8. #8
    Join Date
    May 2002
    Posts
    2,645
    Originally posted by Thomasps
    If you are using above query, there are chances.
    Fortunately you got only 2 now.

    To get one SYSTEM tablespace try this

    Code:
    select TABLESPACE_NAME from dba_tablespaces where TABLESPACE_NAME='SYSTEM';
    Still you are getting two, please raise iTar to oracle.
    You have no idea what you are talking about. Chances???

  9. #9
    Join Date
    May 2002
    Posts
    2,645
    This is from a database out of the box (from what OUI creates):
    Code:
      1  select tablespace_name,bytes, blocks
      2  from dba_free_space
      3* where tablespace_name='SYSTEM'
    SQL> /
    
    TABLESPACE_NAME                     BYTES     BLOCKS
    ------------------------------ ---------- ----------
    SYSTEM                             327680         40
    SYSTEM                            4128768        504
    
    SQL>
    Oh no! By what "chance" did I get two system tablespaces??

  10. #10
    Join Date
    Aug 2002
    Location
    Bangalore, India
    Posts
    405
    Originally posted by stecal
    This is from a database out of the box (from what OUI creates):
    Code:
      1  select tablespace_name,bytes, blocks
      2  from dba_free_space
      3* where tablespace_name='SYSTEM'
    SQL> /
    
    TABLESPACE_NAME                     BYTES     BLOCKS
    ------------------------------ ---------- ----------
    SYSTEM                             327680         40
    SYSTEM                            4128768        504
    
    SQL>
    Oh no! By what "chance" did I get two system tablespaces??
    You have two different chunks of freespaces. You will see SYSTEM tablespace being shown 10 times if you have 10 chunks of freespaces in SYSTEM tablespace.
    -nagarjuna

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