Solved

Creating Tables In Oracle

Posted on 2013-11-18
20
341 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 13

Expert Comment

by:Alexander Eßer [Alex140181]
Comment Utility
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
Comment Utility
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 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>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
 

Author Comment

by:jjmekkattil
Comment Utility
>>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 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>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
Comment Utility
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 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>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 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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
Comment Utility
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 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>> 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:jjmekkattil
Comment Utility
OK, I want to do an export and import what is the proper syntax?
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
Comment Utility
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
Comment Utility
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 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>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
Comment Utility
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 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>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
Comment Utility
Ok, I resolved that issue what would be the proper syntax for the import of the .dmp file
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
I posted it above.

re: http:#a39657491
0
 

Author Comment

by:jjmekkattil
Comment Utility
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 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
Set up a test case and try it.

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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

743 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now