Convert multiple Oracle tables (same structure) into one - repost

Hi, I'm very interested helping you optimizing your database, I have more then 7 years of experience in SQL and database administration, modeling, optimizing and manipulating, please do not hesitate to check my portfolio, there is some samples of databases modeling and database assignments. I've checked your requirement I can do this job in the deadline without a problem, because I've done many projects like this. Please send me all other details if you have about your database, so we can start asap. I'm available, and can start immediately. Looking forward to working with you. Thank you for your consideration. Regards

The word: schmozza Interesting project: I prefer to work on the virtual machine, so I can see and use the best options for your database version (Is 11g? Is partitioned, compress, and so on?). I will test and provide you scripts. The only problem I cannot upload you such a big file via ssh, if you need the virtual machine back (but I can upload it on google drive or mega from office). Anyway I can download it in a few hours with no problems. I will produce a SQL script. You can start it also from bash. Best regards, Luca.

Hello, Have to say that based on what you`ve described it shouldn`t take longer than a day to create an anonymous PL/SQL block (can be stored as a script in linux, which you can run at any time) that will analyse the oracle dictionary in order to dynamically create and populate the consolidated table. Will be glad if you can: 1. clarify what needs to be done with the original tables at the end of the process? 2. I will parametrise, but what kind of naming convention should we use for the consolidated tables? 3. how to react if the consolidated table is already present? 4. do we want to maintain SITE table content based on the Site/Location values available in the 'distributed' tables? 5. do you need/want to use the table partitioning for the consolidated table (partition by Site)? 6. choose me as an implementor 7. provide me the database dump and the version of the database used 8. provide additional requirement clarifications if required 9. benefit form the solution and use me as a contact to solve any of the questions you have regarding Oracle database as this is my core skill and one of the favourite tools And yeah.. schmozza :) Sincerely yours, Dmitry Yaroshenko

Hi, schmozza Did your problem was resolved ? I am an Oracle Certified DBA and I think it could be done using materialized view. Please consider my bid and I will solve your problem.

schmozza 1. Add SITE_id column in SITE table. 2. Create a new table including all the columns of "site" table and site_id column. site_id is foreign key referring to SITE table. 3. insert into all the data into the new table. I need more details about all the table. Could you give all the scripts of creating tables? I can create a script for you to do that.

Hi, I have more than 8 years of experience of executing projects like same. Please take a look at my portfolio ... Send me pm to discuss more ... Regards, Mayank

Dear MR, My name is Mohamed Fatim and I am working as Oracle developer for 4 year and I have the experience to do this job. I see that we can use PL/SQL language and SQL to do this job. First I 'll see the structure for both tables (Site & Redundant) tables to check if I need to add a new columns or not, and check if there a right PK constraints to establish the FK constraint. After that I 'll collect all the tables data in one table and make FK relation. Now I need to check the 'schmozza' to Start Work. Thanks a lot.

Hello, I am Computer Engineer and doing PhD in software engineering. I am working as Oracle developer since 2007. Code : schmozza I have worked on several databases and have managed their data. From your project description, I understand that your have site wise multiple tables and you need to merge data into a single table. Before going to provide solution, I want to ask few questions: How many applications you are using for data insertion is this table? How many records inserted in this table per day? You do not need to provide me all data. Only provide me table structure and few rows of each table. OR Provide me access to your test system. Most Probable Solution: - Merge Data into a New Table ( using oracle procedure and keep updated with triggers or procedures ) - Change application behavior, so that application will only use new table for data insertion/ update and deletion.

Hi, I will do it at 1 day via remote or VPN connection, since you mentioned it's 700MB of data wont take long time.

schmozza :-) Hello, I have 14 years experience in Oracle development. Could you please send me the schema and database dump. I would propose that I create the table with the additional Site_Id column and then populate the table using a PL/SQL procedure. I have a question regarding the way the denormalised tables are populated......if this is based on an application then I could put triggers on them to automatically populate the new normalised table. A better approach would be to modify the existing application to populate and retrieve from the new table. Please let me know if I can be of any further assistance. Many thanks Christopher

A proposal has not yet been provided

A proposal has not yet been provided

schmozza, I am a senior oracle dba, with 8 years of experience. Currently working as a senior oracle dba at Pepsico. I can write a PL/SQL script to move all data into one table. I will also create foreign key constraint if not created already. Database dump is enough for me, there is no point in transferring VMware image. One question about dump file - is it compressed? Or 700mb file is an output of exp / expdp command? Please let me know about database version you are using so I can prepare development environment in advance.

A proposal has not yet been provided

schmozza Hi there! A few questions: 1. What naming convention did you use for the site data tables? (does each "data" table name include the site ID? If so, it makes it easier to parse the site ID and populate the site column.) (please include a couple examples of table names for site data) 2. Do you want foreign key constraints on the new site_data table? (So that records from the site table cannot be deleted without cascading site_data, for example) 3. How often will you be merging the site data? Once? Daily/Weekly/etc? Just curious since you requested that the script can be run at any time. 4. Can you please share the schemas (or SQL you used to create them) for the site table and one of the existing data tables? Other thoughts: I do not think it will be necessary to send me the actual data. Just the schemas will do. I can create some sample data to test the merge. I would use PL/SQL to do this, meaning it would run fine from any oracle client (unix/windows/whatever). I could provide a template bash script if you need it, you would just have to edit it to plugin your user/pass/tns name. Plan: 1. Get schema for SITE and DATA tables from client (you!). 2. Create schema in my Oracle test DB. 3. Create some sample data in each table (reusable script). 4. Create SQL script to merge the tables; use SQL for DDL (creating the new site_data table) and PL/SQL for DML (importing data into site_data from site_XXX_data tables). 5. Test SQL script. 6. Send script to you for test.

"schmozza"...Hello I understood the requirement...it would take 2 Days for me to complete it... We can do that by creating a master table(have a foreign key constraint with sites table) and copy the data from all the different site tables to master by using plsql procedure and invoke that in ksh file. ex: create master_Table as select * from site_<n> where 1 = 2; Add foreign key. for i in site_1 ...site_n loop insert into master_table select * from site_<i> table; commit; end loop; please let me know if you require further information.

schmozza Senior database administrator on (Microsft SQL Server,Sybase & Oracle) i will use PL/SQL but firstable i need to see structure of database in order to decide how i will combine them /

Having Strong Experience in Oracle SQL & PL/SQL Programming with Unix Shell Scripting. In Depth Knowledge in DWH. Worked for Prestigious Clients GE,PGI,Knape & Voght,Deautch Bank,BAE Systems.

schmozza My skill : oracle lead in ford it (client). experienced in handling heavy batch PL SQL JOBS. Please find the logic for your requirement in PL SQL. 1) Remove the existing parent - child relation ship between master table (SITE) and child tables (each site table) 2) Create data model for new child table (the child site table which will hold all the site details) 3) Create the parent key , foreign key relationship between parent ( SITE table and child table created in step 2) 4) Select all the records in the existing child tables (table for each site) using SET operator (union) example: select column 1 , column 2 from <childsite1> union select column 1 , column 2 from <childsite1> etc 5) create a cursor in PL SQL block for the query in step 4). 6) Fetch each record in the cursor and insert the record in the new child table(the table in step 2). 7) In the PLSQL block catch the exception and store the exception in a error table (we have to create an error table to hold the site name , measurement details). The reason to store the record in the error table is to identify which record has error and what is the cause of the error. 8) Log the error as mentioned in step 7 and pick up the next record from the cursor and insert into the new child table. 9) once all the records are processed,read the error table, make corrections and rerun the PLSQL block for the records that were error out. 10) Using DBMS_JOB package the procedure is scheduled to run.