-
DB analysis
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.
-
Re: DB analysis
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..
Code:
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
Last edited by Sameer; 11-29-2002 at 06:07 AM.
-
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'
-
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.
-
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|