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
10-24-2001, 10:12 PM
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.
10-25-2001, 06:10 AM
I am using Oracle 7.3.4.
I still do'n get how to separate a users objects into diff TBS?
10-25-2001, 07:02 AM
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.
10-25-2001, 08:20 AM
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).
10-25-2001, 08:28 AM
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.
10-25-2001, 09:40 AM
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.
10-26-2001, 02:25 AM
i think you can straight away give a rebuild index to recreate them on the new tablespace.