how to schema import in other user schema

[Asif_8]

Hi !

I have 10g I want to export full schema into other user what is best pratices

1.exp userid=a/a file=a.dump log=a.log compress=n
2.drop all table from user b

3.imp file=a.dmp fromuser=a touser=b

I drop table by OEM what is a command to delete all table from schema b for imp

[Piergiacomo Perini · Accepted solution]

Hi Asif,

in order to get list of all table owned by user oracle "aaa" , try this :

select objects_name from all_objects where OWNER='aaa' and objects_type='TABLES'

hth
regards
pg

[Piergiacomo Perini]

i beg your pardon Asif, some errors in my previous post ; try this

select OBJECT_NAME from all_objects where OWNER='aaa' and object_type='TABLE';


it's good to catch the list!
regards
pg

[spex]

Hello Asif,

Many of these steps can be accomplished through OEM, if you are more comfortable using that product.

1) Copy user b's schema to b_temp.
2) Drop all segments in b_temp. Now you have an empty template for user b, that you can use for future exp/imp operations.
3) SQL> drop user b cascade;
4) Copy b_temp schema to b.
5) $ nohup un/pw exp owner=a ... &
6) $ nohup un/pw imp fromuser=a touser=b ... &
7) Check for schema a being hard-coded in any views, functions, procedures, etc., change to schema b, and recompile.

PCS

[Yogeeraj_1]

Hi Asif,

"what is a command to delete all table from schema b"

select 'drop table '||table_name||';'
from dba_tables
where owner='B';

you can spool the above sql to a file:
e.g.
spool dropb_tables.sql


then run the dropb_tables.sql

sql> @dropb_tables.sql

Note that due to referential integrity constraints, not all the tables may be dropped on the first run of the script. You may have to run the above script several times.

I would be much easier to drop the user and re-create it (granting all the required privileges of course!)

What version of Oracle Database are you running?

if you need any further assistance, please let us know.

kind regards
yogeeraj

[Asif_8]

Hi yogeeraj!

I am use 10g .. u also tell me following syntex is correct for exp and imp

exp userid=a/a file=a.dump log=a.log compress=n

imp file=a.dmp fromuser=a touser=b