Apps and Tablespace
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Apps and Tablespace

  1. #1
    Join Date
    Sep 2001
    Posts
    2
    Hello
    I have 3 Apps that shares same TBS and on the other hand the indexes and tables are in the same TBS too.
    Could you give me some steps to accomplish this? I never had to do this.:
    1.Every Apps and its own TBS
    2.Indexes and their associated tables on different TBS


    Thanks


  2. #2
    Join Date
    Aug 2001
    Location
    Waterloo, On
    Posts
    547
    If you are using Oracle 8i, you can move tables of each application in to its own tablespace by

    ALTER TABLE name MOVE TABLESPACE new_tablespace;

    You have to then rebuild indexes

    ALTER INDEX idxname REBUILD TABLESPACE indxtablespace;

    If you don't have 8i (or above), you have to use export and import to reorganize your database.

    Raminder Singh

    Oracle Certified DBA: Oracle 8i, 9i


    Mail me at raminderahluwalia@rediffmail.com.

  3. #3
    Join Date
    Sep 2001
    Posts
    2
    I am using Oracle 7.3.4.
    I still do'n get how to separate a users objects into diff TBS?

  4. #4
    Join Date
    Aug 2001
    Location
    Waterloo, On
    Posts
    547
    Export your tables. Drop tables and associated indexes.
    Create Tablespaces and Users correctly.
    Recreate tables (only structure) in appropriate tablespaces.
    Import tables 'from user' and 'to user' with rows=y and ignore=y to import data from your export dump.
    Recreate indexes.

    Raminder Singh

    Oracle Certified DBA: Oracle 8i, 9i


    Mail me at raminderahluwalia@rediffmail.com.

  5. #5
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857
    Hi Raminder,
    I am just not sure if "from user" and "to user" were already available in release 7.3.4.(correct me if I'm wrong).

  6. #6
    Join Date
    Jan 2001
    Posts
    2,828

    Talking

    hello

    Yes from user and touser are keywords in the Oracle 7.3 release .for more info you can type exp with the help option at your os level.

    regards
    Hrishy

  7. #7
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857
    Thanks hrisky.

    To make life more easier to Dominic follow this:
    1. make a full backup.
    login as dba
    2. create 3 new tablespaces for APPS DATA and another
    3 tablespace for APPS INDEX.
    svrmgrl> create tablespace tbs_APPS1_dat datafile
    '/u01/oradata/PRD/appsdat101.dbf' size 100M
    autoextend on online;
    svrmgrl> create tablespace tbs_APSS1_idx datafile
    '/u03/oradata/PRD/appsidx101.dbf' size 80M
    autoextend on online;
    3. create three users to own the 3 new schema.
    svrmgrl> create user apps1usr identified by apps1pwd
    default tablespcae tbs_apps1_dat
    temporary tablespace temp .......blah..
    (don't forget to grant at least the select privs to apps1usr from original apps user)
    login as apps1usr
    4. create the table structure only.
    svrmgrl> create table table1
    as select * from oldapps1usr.table1
    where rownum = 0;

    Do this for all the tables owned by old apps users.

    5. EXPORT the old apps user schema.
    $ exp userid=oldapps1usr/oldapps1pwd buffer=81920 file=apps1.dmp grants=y constraints=y indexes=y rows=y

    6. IMPORT to the new apps user schema
    $ imp userid=apps1usr/apps1pwd buffer=81920 file=apps1.dmp grants=y indexes=y rows=y fromuser=oldapps1usr touser=apps1usr ignore=y

    Then there goes your new 3 schemas with new tablespaces object resides.

    Note: check for triggers that does not belong to the 3 users and yet dependent on old apps user. You have to recreate this triggers after you done the IMPORT process.
    svrmgrl> select trigger_name from dba_triggers
    where table_owner = 'OLDAPPSUSR' and owner <> 'OLDAPPSUSR';
    You should do this before the above instructions.

    Good luck.

  8. #8
    Join Date
    Oct 2000
    Posts
    467
    i think you can straight away give a rebuild index to recreate them on the new tablespace.
    Vinit

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