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
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.
I am using Oracle 7.3.4.
I still do'n get how to separate a users objects into diff TBS?
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.
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).
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.
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.
i think you can straight away give a rebuild index to recreate them on the new tablespace.