Apps and Tablespace

[dominic]

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

[Raminder]

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.

[dominic]

I am using Oracle 7.3.4.
I still do'n get how to separate a users objects into diff TBS?

[Raminder]

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.

[reydp]

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).

[hrishy]

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

[reydp]

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.

[vinit]

i think you can straight away give a rebuild index to recreate them on the new tablespace.