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

Thread: DB analysis

  1. #1
    Join Date
    Nov 2002
    Posts
    5

    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.

  2. #2
    Join Date
    Sep 2001
    Location
    Düsseldorf, Germany.
    Posts
    588

    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.

  3. #3
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    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'

  4. #4
    Join Date
    Nov 2002
    Posts
    5
    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.

  5. #5
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    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
  •  


Click Here to Expand Forum to Full Width