General
cancel
Showing results for 
Search instead for 
Did you mean: 

how to schema import in other user schema

SOLVED
Go to solution
Asif_8
Regular Advisor

how to schema import in other user schema

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



5 REPLIES
Piergiacomo Perini
Trusted Contributor
Solution

Re: how to schema import in other user schema

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
Trusted Contributor

Re: how to schema import in other user schema

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
Honored Contributor

Re: how to schema import in other user schema

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
Honored Contributor

Re: how to schema import in other user schema

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
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Asif_8
Regular Advisor

Re: how to schema import in other user schema

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