Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 363
  • Last Modified:

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.
0
jjmekkattil
Asked:
jjmekkattil
  • 10
  • 9
1 Solution
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
Basically you do a owner-related export and then you import into your target schema (fromuser/touser)...
Where exactly do you encounter problems?!
0
 
jjmekkattilAuthor Commented:
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?
0
 
slightwv (䄆 Netminder) Commented:
>>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;
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
jjmekkattilAuthor Commented:
>>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.
0
 
slightwv (䄆 Netminder) Commented:
>>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?
0
 
jjmekkattilAuthor Commented:
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_name
0
 
slightwv (䄆 Netminder) Commented:
>>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:
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;
/

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
I should add that exp/imp will also create all indexes and constraints.  The manual method is just tables and data.
0
 
jjmekkattilAuthor Commented:
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?
0
 
slightwv (䄆 Netminder) Commented:
>> 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.
0
 
jjmekkattilAuthor Commented:
OK, I want to do an export and import what is the proper syntax?
0
 
slightwv (䄆 Netminder) Commented:
Should be something like this should be the minimum(uses classic exp/imp not datapump):

exp schemaA/password owner=schemaA file=schemaA.dmp

then:
imp schemaB/password fromuser=schemaA touser=schemaB file=schemaA.dmp


You should test this on some smaller schemas just to confirm.
0
 
jjmekkattilAuthor Commented:
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
0
 
slightwv (䄆 Netminder) Commented:
>>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?
0
 
jjmekkattilAuthor Commented:
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?
0
 
slightwv (䄆 Netminder) Commented:
>>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/
0
 
jjmekkattilAuthor Commented:
Ok, I resolved that issue what would be the proper syntax for the import of the .dmp file
0
 
slightwv (䄆 Netminder) Commented:
I posted it above.

re: http:#a39657491
0
 
jjmekkattilAuthor Commented:
Would this work:

d:\>imp schemaB/password fromuser=schemaA touser=schemaB file=D:\schemaA_file.dmp full=Y log=schemaB_log statistics=none
0
 
slightwv (䄆 Netminder) Commented:
Set up a test case and try it.

I'm not sure about the FULL parameter with FROMUSER and TOUSER.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 10
  • 9
Tackle projects and never again get stuck behind a technical roadblock.
Join Now