Two system tablespaces

[sarahanand]

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.

[pando]

there arent two tablespaces, there are two datafiles

[julian]

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?

[jmodic]

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....

[sarahanand]

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

[mike9]

From the Oracle Doc:

"DBA_FREE_SPACE lists the free extents in all tablespaces"

I guess this will answer your first question.

[Thomasps]

"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

select TABLESPACE_NAME from dba_tablespaces where TABLESPACE_NAME='SYSTEM';

Still you are getting two, please raise iTar to oracle.


Good luck

[stecal]

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

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???

[stecal]

This is from a database out of the box (from what OUI creates):

  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??

[nagarjuna]

Originally posted by stecal
This is from a database out of the box (from what OUI creates):

  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.