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!
Course Of The Month
2 days, 12 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Oracle data migration from 1 environment to other
Posted on 2011-09-07
Experts,
I have a set of tables for which the data needs to copied to another environment.
Here are the key points:
1. I do not want to copy the entire data, it will be driven by a set of instructions from screen and related master and detail tables (around 20 in number) we should be able to migrate to another environment.
2. We can safely assume its plain insert and there will not be any violation of constraints
3. This process should be done seamlessly as its repetitive process (done from screen ref point 1) , via a stored procedure or something better - experts i need your suggesstion here
4. There will not be any provision for DB Links, ideally because we want to demonstrate there is no overhead except the same table structure should reside in the environments.
5. Dont mind if its takes time in loading the data, but it should ideally be done in background when user initiates action from screen and may be reads config files to find target db, without invoking command prompt or any process that can be called from java.
6. Do not want to have third party tools
I have a set of tables for which the data needs to copied to another environment.
Here are the key points:
1. I do not want to copy the entire data, it will be driven by a set of instructions from screen and related master and detail tables (around 20 in number) we should be able to migrate to another environment.
2. We can safely assume its plain insert and there will not be any violation of constraints
3. This process should be done seamlessly as its repetitive process (done from screen ref point 1) , via a stored procedure or something better - experts i need your suggesstion here
4. There will not be any provision for DB Links, ideally because we want to demonstrate there is no overhead except the same table structure should reside in the environments.
5. Dont mind if its takes time in loading the data, but it should ideally be done in background when user initiates action from screen and may be reads config files to find target db, without invoking command prompt or any process that can be called from java.
6. Do not want to have third party tools
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
4-
4
9 Comments
Active today
With those restrictions, the only thing I can think of was mentioned in your tags for the question: data pump.
Assuming you have already considered it have you discounted it or are you just looking for possible alternatives?
Assuming you have already considered it have you discounted it or are you just looking for possible alternatives?
This activity is called Integration/Interfacing one application with other.
Flow can be like this
1) 1st environment application, in the form / sreen, 1 group window need to be available.
That group consists of those 20 tables list. And individual or group click option should be available. By clicking on this, an export of these tables to .CSV should be performed based on the partial selection of data. For this, in the code, Oracle SQL commands can be used to generated the .CSV output.
2) .CSV file should be generated in a folder which is NFS mounted(this is a network mount in unix systems to avoid FTP. But it has its own security/technical related good and bad. Consult System Admin)
So with this, no need to separately transfer the files to 2nd environment. Seamless.
3) 2nd Environment application Screen/Form. 1 group window or individuval table window should be available along with import button. When clicked, in the background, it should use sqlloader or insert commands to load the data. This should be handled in the code.
Note : Formatting of the .CSV file should also be handled to import. It need to be aligned properly so that import will be successfully completed.
Scope of work : Developer/Sql programmer,Application Owner who understands the application and is useful to developer to complete this interfacing.
Clue : In Oracle ERP environment, this approach is popular with concurrent request
Flow can be like this
1) 1st environment application, in the form / sreen, 1 group window need to be available.
That group consists of those 20 tables list. And individual or group click option should be available. By clicking on this, an export of these tables to .CSV should be performed based on the partial selection of data. For this, in the code, Oracle SQL commands can be used to generated the .CSV output.
2) .CSV file should be generated in a folder which is NFS mounted(this is a network mount in unix systems to avoid FTP. But it has its own security/technical related good and bad. Consult System Admin)
So with this, no need to separately transfer the files to 2nd environment. Seamless.
3) 2nd Environment application Screen/Form. 1 group window or individuval table window should be available along with import button. When clicked, in the background, it should use sqlloader or insert commands to load the data. This should be handled in the code.
Note : Formatting of the .CSV file should also be handled to import. It need to be aligned properly so that import will be successfully completed.
Scope of work : Developer/Sql programmer,Application Owner who understands the application and is useful to developer to complete this interfacing.
Clue : In Oracle ERP environment, this approach is popular with concurrent request
Active today
Yes.
Oracle Data Pump is made up of the following distinct parts:
•The command-line clients expdp and impdp
These client make calls to the DBMS_DATAPUMP package to perform Oracle Data Pump operations (see "PL/SQL Packages").
•The DBMS_DATAPUMP PL/SQL package, also known as the Data Pump API
This API provides high-speed import and export functionality.
•The DBMS_METADATA PL/SQL package, also known as the Metadata API
This API, which stores object definitions in XML, is used by all processes that load and unload metadata.
http://download.oracle.com/docs/cd/E11882_01/server.112/e16508/cncptdba.htm#CNCPT1277
Oracle Data Pump is made up of the following distinct parts:
•The command-line clients expdp and impdp
These client make calls to the DBMS_DATAPUMP package to perform Oracle Data Pump operations (see "PL/SQL Packages").
•The DBMS_DATAPUMP PL/SQL package, also known as the Data Pump API
This API provides high-speed import and export functionality.
•The DBMS_METADATA PL/SQL package, also known as the Metadata API
This API, which stores object definitions in XML, is used by all processes that load and unload metadata.
http://download.oracle.com/docs/cd/E11882_01/server.112/e16508/cncptdba.htm#CNCPT1277
@slightwv:
I have to look into the documentation and the link you provided. Thanks a lot.
Just a quick question as I have to submit the approach as soon as I can.
I want expdp and impdp to be FULLY controlled from PL/SQL program. Is it possible?
Thanks a lot
I have to look into the documentation and the link you provided. Thanks a lot.
Just a quick question as I have to submit the approach as soon as I can.
I want expdp and impdp to be FULLY controlled from PL/SQL program. Is it possible?
Thanks a lot
Active today
The docs will confirm this but I think datapump does everything inside the database no matter what initiates it.
The command line actually creates a database job internal to the database.
This means you can do things like close down the window and reconnect to an existing job from another window to check status.
I've never used the PL/SQL APIs but I believe you can do everything you need to do with them.
The command line actually creates a database job internal to the database.
This means you can do things like close down the window and reconnect to an existing job from another window to check status.
I've never used the PL/SQL APIs but I believe you can do everything you need to do with them.
what matters to me is invoking. If datapump can be done in PL/SQL, I am more than happy.
I do not want to check the status because, the number of records are few < 10,000 also, I do not care about the time it takes to export or import.
I can write SQL statements on target table in another environment to see if its ready for next operation
Again, everything should be in PL/SQL without any calls which invokes command line utility
I do not want to check the status because, the number of records are few < 10,000 also, I do not care about the time it takes to export or import.
I can write SQL statements on target table in another environment to see if its ready for next operation
Again, everything should be in PL/SQL without any calls which invokes command line utility
Active today
Sorry if I confused you with my last post.
I was trying to point out that datapump does everything inside the database no matter how you access it.
>> If datapump can be done in PL/SQL
Yes.
I was trying to point out that datapump does everything inside the database no matter how you access it.
>> If datapump can be done in PL/SQL
Yes.
Featured Post
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!
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
This post first appeared at Oracleinaction (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself).
I will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Via a live example, show how to take different types of Oracle backups using RMAN.
Suggested Courses
Course of the Month2 days, 12 hours left to enroll
Earn Certification
Certified Information Systems Auditor (CISA)
$99.00$89.10
695 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.