Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Creating Tables In Oracle

Posted on 2013-11-18
20
Medium Priority
?
354 Views
Last Modified: 2014-02-10
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
Comment
Question by:jjmekkattil
  • 10
  • 9
20 Comments
 
LVL 14

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 39656680
Basically you do a owner-related export and then you import into your target schema (fromuser/touser)...
Where exactly do you encounter problems?!
0
 

Author Comment

by:jjmekkattil
ID: 39656708
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
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39656859
>>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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 

Author Comment

by:jjmekkattil
ID: 39657267
>>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
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39657271
>>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
 

Author Comment

by:jjmekkattil
ID: 39657375
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
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39657399
>>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
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39657401
I should add that exp/imp will also create all indexes and constraints.  The manual method is just tables and data.
0
 

Author Comment

by:jjmekkattil
ID: 39657435
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
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39657457
>> 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
 

Author Comment

by:jjmekkattil
ID: 39657469
OK, I want to do an export and import what is the proper syntax?
0
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 1500 total points
ID: 39657491
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
 

Author Comment

by:jjmekkattil
ID: 39657528
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
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39657559
>>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
 

Author Comment

by:jjmekkattil
ID: 39657574
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
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39657593
>>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
 

Author Comment

by:jjmekkattil
ID: 39659433
Ok, I resolved that issue what would be the proper syntax for the import of the .dmp file
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39659463
I posted it above.

re: http:#a39657491
0
 

Author Comment

by:jjmekkattil
ID: 39659768
Would this work:

d:\>imp schemaB/password fromuser=schemaA touser=schemaB file=D:\schemaA_file.dmp full=Y log=schemaB_log statistics=none
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39659784
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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses

886 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question