jjmekkattil
asked on
Creating Tables In Oracle
So I am creating a stored procedure where a particular schema (A) will look at another particular schema (B) and compare data and if there is any difference and if (A) created or deleted new records than it will create or delete that data in (B) but in order to do that I must first make sure that schema (B) has all the appropriate tables as in schema (A).
So in order to do that I needed to create the tables (found doing research on the internet) in schema (B) using EXP/IMP with FROMUSER=SCHEMA(A) & TOUSER=SCHEMA(B). I'm just not entirely sure the proper steps I need to take to make this work right any help on this would be appreciated.
So in order to do that I needed to create the tables (found doing research on the internet) in schema (B) using EXP/IMP with FROMUSER=SCHEMA(A) & TOUSER=SCHEMA(B). I'm just not entirely sure the proper steps I need to take to make this work right any help on this would be appreciated.
ASKER
well its more I need a step by step procedure:
So I open command prompt and type:
EXP SCHEMA(A)/PASSWORD FROMUSER=SCHEMA(A) TOUSER=SCHEMA(B)
would that copy over all tables into schema b as long as schema b has read rights?
So I open command prompt and type:
EXP SCHEMA(A)/PASSWORD FROMUSER=SCHEMA(A) TOUSER=SCHEMA(B)
would that copy over all tables into schema b as long as schema b has read rights?
>>So I open command prompt and type:
It would probably error. exp doesn't have a fromuser and touser parameter. That is only imp.
Now if you use those parameters as part of the import: schemaA's objects will be imported into schema as the new owner.
I'm also not sure why you are using exp/imp to create all the tables within the same database.
CTAS would be just as good:
create table schemaB.table1 as select * from schemaA.table1;
It would probably error. exp doesn't have a fromuser and touser parameter. That is only imp.
Now if you use those parameters as part of the import: schemaA's objects will be imported into schema as the new owner.
I'm also not sure why you are using exp/imp to create all the tables within the same database.
CTAS would be just as good:
create table schemaB.table1 as select * from schemaA.table1;
ASKER
>>I'm also not sure why you are using exp/imp to create all the tables within the same database.
Well is there an easier way to do an EXP/IMP of all tables where schemaB has read rights to particular tables in schemaA? There is enough tables to where doing them individually will take some time.
Well is there an easier way to do an EXP/IMP of all tables where schemaB has read rights to particular tables in schemaA? There is enough tables to where doing them individually will take some time.
>>Well is there an easier way to do an EXP/IMP
Easy is relative. exp/imp is likely the easiest but adds the extra step and the file on the file system.
>> There is enough tables to where doing them individually will take some time.
PL/SQL loop with execute immediate?
Easy is relative. exp/imp is likely the easiest but adds the extra step and the file on the file system.
>> There is enough tables to where doing them individually will take some time.
PL/SQL loop with execute immediate?
ASKER
Yes, easy is relative and you have helped me before and knowing that nothing really comes easy to me when it comes to Oracle.
So stating "PL/SQL loop with execute immediate" I kind of understand it would go through a list. Could you give me an example to go off of?
Would you suggest I just logon to sql using schemaB credential and do:
SQL> create table specific_table_name as select * schemaA.specific_table_nam
So stating "PL/SQL loop with execute immediate" I kind of understand it would go through a list. Could you give me an example to go off of?
Would you suggest I just logon to sql using schemaB credential and do:
SQL> create table specific_table_name as select * schemaA.specific_table_nam
>>Would you suggest I just logon to sql using schemaB credential and do:
Yes.
>>Could you give me an example to go off of?
This is untested but something like below as schema:
Yes.
>>Could you give me an example to go off of?
This is untested but something like below as schema:
begin
for i in (select table_name from all_tables where owner='SCHEMAA' ) loop
execute immediate 'create table ' || i.table_name || ' as select * from schemaA.' || i.table_name;
end loop;
end;
/
I should add that exp/imp will also create all indexes and constraints. The manual method is just tables and data.
ASKER
So the statement you placed above would I do that in SQLPLUS and once I do run it would it create all of schemaA tables into schemaB where schemaB has read access?
>>I should add that exp/imp will also create all indexes and constraints.
So should I do a exp schemaA?
>>I should add that exp/imp will also create all indexes and constraints.
So should I do a exp schemaA?
>> in SQLPLUS and once I do run it would it create all of schemaA tables into schemaB where schemaB has read access?
Yes.
>>So should I do a exp schemaA?
I cannot tell you what you should do because I don't fully understand your requirements.
I understand the basics: You basically want to create a living copy of schemaA that is refreshed in schemaB from time to time from some stored procedure.
If you want the copy to be as exact as you can get it, with all indexes, constraints, etc... then use export/import.
Yes.
>>So should I do a exp schemaA?
I cannot tell you what you should do because I don't fully understand your requirements.
I understand the basics: You basically want to create a living copy of schemaA that is refreshed in schemaB from time to time from some stored procedure.
If you want the copy to be as exact as you can get it, with all indexes, constraints, etc... then use export/import.
ASKER
OK, I want to do an export and import what is the proper syntax?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Couple of things I am assuming I would need to run the EXP command from the local database machine and not try to run it remotely.
Secondly, two databases reside on the same server would I need to specify which database I am referring to ex: exp schemaA/password@DB1 owner=schemaA file=schemaA.dmp
Nevermind, I specified the DB
Secondly, two databases reside on the same server would I need to specify which database I am referring to ex: exp schemaA/password@DB1 owner=schemaA file=schemaA.dmp
Nevermind, I specified the DB
>>I would need to run the EXP command from the local database machine and not try to run it remotely.
Classic exp/imp can be remote.
>>two databases reside on the same server
This is a new requirement. A schema and database are two completely different animals.
Please clarify: are you copying between two schemas or two databases?
Classic exp/imp can be remote.
>>two databases reside on the same server
This is a new requirement. A schema and database are two completely different animals.
Please clarify: are you copying between two schemas or two databases?
ASKER
Let me step back for a second, the server holds two databases however we are only messing with one of the two...but it does not matter it worked my next step is importing. One other thing I did get a few warnings stating
EXP-00003: no storage definition found for segment(0, 0)
I'm guessing it is nothing to worry about?
EXP-00003: no storage definition found for segment(0, 0)
I'm guessing it is nothing to worry about?
>>I'm guessing it is nothing to worry about?
Based on the following information, I would be concerned.
Check out the following note on Oracle Support:
OERR: EXP 3 "no storage definition found for segment(%lu, %lu)" (Doc ID 21599.1)
and:
http://exp-00003.ora-code.com/
Based on the following information, I would be concerned.
Check out the following note on Oracle Support:
OERR: EXP 3 "no storage definition found for segment(%lu, %lu)" (Doc ID 21599.1)
and:
http://exp-00003.ora-code.com/
ASKER
Ok, I resolved that issue what would be the proper syntax for the import of the .dmp file
ASKER
Would this work:
d:\>imp schemaB/password fromuser=schemaA touser=schemaB file=D:\schemaA_file.dmp full=Y log=schemaB_log statistics=none
d:\>imp schemaB/password fromuser=schemaA touser=schemaB file=D:\schemaA_file.dmp full=Y log=schemaB_log statistics=none
Set up a test case and try it.
I'm not sure about the FULL parameter with FROMUSER and TOUSER.
I'm not sure about the FULL parameter with FROMUSER and TOUSER.
Where exactly do you encounter problems?!