DB analysis

[draakhs]

Hi all

Can someone tell me if when doing a backup the SYSTEM tablespace gets included in the backup and when it does can it be inserted in a new DB?

2nd is there a way to check where my indexes are stored? I know there are indexes in this DB and I have a tablespace named USER_IDX but it is empty. What I want to know is in which tablespace they are stored by using SQL-Navigator.

tnx.

[Sameer]

Originally posted by draakhs
Can someone tell me if when doing a backup the SYSTEM tablespace gets included in the backup and when it does can it be inserted in a new DB?

What do you mean inserted in new DB.. To clone new database using backup is possible.. But pluging into some other database is not possible

2nd is there a way to check where my indexes are stored? I know there are indexes in this DB and I have a tablespace named USER_IDX but it is empty. What I want to know is in which tablespace they are stored by using SQL-Navigator.

You can query USER_INDEXES, ALL_INDEXES, DBA_INDEXES views to see indexes..

SQL> select index_name, tablespace_name  from user_indexes where table_name = 'APOTHEKE';

INDEX_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
IN_APOTHEKE_KAMMER             ABDA_IDX
IN_APOTHEKE_PLZ                ABDA_IDX
IN_APOTHEKE_POSITION           ABDA_IDX
IN_APOTHEKE_VORBESTELL_ID      ABDA_IDX
KENNUNG_INDEX                  ABDA_IDX
PKAPOTHEKE                     ABDA_IDX

[davey23uk]

of course system tablespace is backed up, dont really wanna be losing that do you

and to find the tablespace name

select tablespace_name from user_indexes where index_name = 'XXX'

[draakhs]

ok but what I don't get then is

Oracle doesn't like putting user data in the SYSTEM tablespace.
But if something happens to the DB can you recover the user data out of the SYSTEM tablespace? or when you did a backup and you want to use the backup to restore the DB, is Oracle going to let you do this?

Why I ask is the following: where I work my predecessors used the SYSTEM tablespace to store the user data in it. Now I want to know if it is worth the trouble getting all the data out of it that doesn't belong there and create another tablespace for it or just leave it this way. From what I heard when something happens to the DB and you have things stored in the SYSTEM tablespace things can get hard to recover it out of it.

[JMac]

Best to move all non system datafiles out of the SYSTEM tablespace. Its just as volatile as any other tablespace so if you corrupt the tablespace because of problems with your user datafiles then you're in big trouble.

Also - if you ever need to take the user data tablespace offline for recovery or repair then you can't as you can't offline the SYSTEM tablespace.

Back it up before you attempt anything.

And back it up again immediately after successfully moving all non SYSTEM files into a seperate USERS tablespace.