Move Data From Old Tables Into New?

Aug 19, 2010

I have to move data from old tables into new tables. I use a sqlplus script and execute multiple 'insert from select'. I thought the SQL statements are executed in sequence, one after the other. I run into this problem: Let's take this script for an example

insert into A (a,b,c,....
select a,b,c......
from X;
insert into B (a,b,....
select a,b,....
from A joined with Y;
commit;

After executing the script in our development DB

select count (*) from A
returns 200

When the script is executed in production, A table record count is 5

select count (*) from A joined with Y; (used in the second insert)

returns 200.

It seems that the two statements are executed at the same time and table A is not fully populated when the select in statement two is executed.I suspect this is due to parallel execution in production.

Will executing

ALTER SESSION DISABLE PARALLEL DML;
ALTER SESSION DISABLE PARALLEL QUERY ;

View 8 Replies


ADVERTISEMENT

PL/SQL :: How To Move Data Between Two Similar Tables

Jul 3, 2012

I have two tables with almost the same columns. Table A has 50 columns, Table B has 51 columns, which are defined exactly the same except one more column as LOAD_TIME.

Is there any easy way for me to move the data from Table A to B? the Extra column LOAD_TIME will be set as current system time. All I want is to avoid list all 50 column names. The following SQL has error ORA-00923 error.

INSERT INTO TB_B
SELECT *, sysdate from TB_A;

View 9 Replies View Related

Move Data From Physical StandBy To Data Warehouse?

Oct 1, 2012

We have this archicture:

OLPT DB --> OLPT DB (Physical Standby, active dataguard) --> Data warehouse DB

We only allowed to connect to OLPT DB (Physical Standby, active dataguard) from Data warehouse DB. If there is possibility to use some of Oracle "native" method of data extraction (replication) from OLPT DB (Physical Standby, active dataguard) to Data warehouse DB.

As far as I know we cannot create materialized view log in OLPT DB (Physical Standby, active dataguard) in order to do data replication, but maybe there is some others ways?

View 3 Replies View Related

SQL & PL/SQL :: How To Move Only Subset Of Data From One Database To Another

Mar 13, 2012

The requirement details are as below,

1) The database contains static as well as transactional data for telecom domain.

2) We have to move region-wise data from one database to another.

3) There are around 10 regions.

4) The region wise data extraction from source db is based on around 40 to 50 tables. Rest of the tables contains static data & it will not change.

5) The volume is around 1 million subscribers per region.

6) The migration is required because

a) The client is upgrading its base product which uses this database

b) There is a change in structure of static tables

c) Hardware upgrade

d) The client want to start with single region on new database & rest of the regions will be operated from old database.

7) Keep execution time to very minimum.

I am thinking to have solution as below,

1) Create destination database with upgraded db structure (as mentioned in 6b)

2) Create database links to access source db in destination db.

3) Write SQL queries to fetch data from all the respective tables for a specific region

4) Write separate PL/SQL blocks for each table to fetch data from source db & insert into respective table in destination db

a) Use FOR ALL & bulk collect to improve the performance

b) Divide table data into multiple chunks & execute parallel batches (around 10) to insert the data

5) Validate pre & post counts to verify the success of migration

View 6 Replies View Related

Windows :: Move Data From One DB To Other Without DBlink And Using Dot Net

Oct 30, 2012

I have database A and B , we are strictly restricted to not to create any DB links between these database. In database A based on complex logic we in a stored procedure we populate the data to table type..now dot net will take this table type data and insert the same in database B.

Question here :

1.When we try to bulk collect all the data into table type , i am getting error operand exceeds limit, but it is mandate to put all data in one short to table type as dot net will take that and insert into B database.
2.Is there any good logical method to achieve this in a simple way.

View 3 Replies View Related

SQL & PL/SQL :: Move Data From One Partition To Other In A Table

Jul 22, 2010

I want to move data from one partition to another partition in a same table.

Ex: Table A has Partitions P1 and P2. I want to move data from P1 to P2.

Is this is possible?

View 3 Replies View Related

PL/SQL :: Move Partition From One Tablespace To Other Along With Lob Data?

Jan 4, 2013

I have a range partitioned table with one lob column. Each partion is on a separate tablespace except two partitions which are on same tablespace. Now I want to move a partition from one tablespace to another tablespace along with lob data. By using a simple alter table move partition will also move the lob data or there is some special procedure to adopt.

View 9 Replies View Related

Forms :: To Move Data Block Detail

May 4, 2011

i made a master-deatil data block and when i completed all field on master-data block i can`t move to data block detail.

when i press (TAB OR ENTER) on last field, the cursor go back to first field.

View 3 Replies View Related

Server Utilities :: Move Data From One Schema To Another

Feb 7, 2013

I am trying to move data from one schema in database A to another schema in database B and only move data not others (procedure, view, function). What is the best way to do it? I am thinking database link then insert into select from database link. Is there a better way?

View 8 Replies View Related

Server Utilities :: How To Move All Data From One Tablespace To Another

Aug 23, 2012

I want to move all data from my users tablespace to anohter new tablespace i have created name test1

so how can i do this using expdp

i want to move all objects from this my users tablespace to this new tablespace

View 4 Replies View Related

SQL & PL/SQL :: Move Data From Varray Column To A Table

Mar 28, 2013

I have to move the data from a Varray column to a table.

Lets say I have created a Varray as described below,

create or replace TYPE "BT_TYPE" AS OBJECT (
R_ID NUMBER,
P_EVENT VARCHAR2(100))
/

create or replace TYPE "BT_VR" AS varray(20) of BT_TYPE
/

And I have a used this Varray as the column datatype in table

Create table BT_MASTER(
BT_ID_SEQNUMBER(5),
BT_DETAILBT_VR);

And this table contains data.

I want to move the data in the VARRAY column BT_DETAIL to another table. I have create a staging table BT_STG which contains a surrogate key column and the columns from the VARRAY. I am creating this staging table at run time.

Create Table BT_STG(
BT_STG_ID NUMBER(5),
R_ID NUMBER(5),
P_EVENT VARCHAR2(100)
);

One way to create this staging table is to query the data dictionary views to get the VARRAY object's columns, datatyeps and create it.

Is there any other simpler way by which I could create a table similar to a VARRAY/Object?

Something similar to,

create table test as select * from BT_VR

View 4 Replies View Related

Possible To Move Data At Storage Level Without Using Convert Command?

Apr 12, 2011

We are migrating database from HP to AIX. Data is all on SAN.I can do it through RMAN convert. What I want to know is if it is possible to move data at storage level without using Convert command?

View 2 Replies View Related

Client Tools :: Sql Developer Online Data Move

Dec 1, 2011

I am using SQL developer 2.1 to migrate tables from Sybase 12 database to oracle 11g. I have used online data move option for moving sybase data into oracle tables, but even after data move is completed not all rows have been moved from sybase tables to corresponding oracle tables. Some rows are missing but still there is no error message being displayed, how to find out what's going wrong.

View 3 Replies View Related

Best Tool / Method - Move Data In Real Time?

Sep 22, 2010

We have oracle 9i database named A with 2 schema A1 and A2.User enters data at A1 schema and the incremental data moved to A2 schema after some verfications performed by some scheduled job on A1 schema.

we want to move this incremental data to B1 schema of another oracle 9i database B at the real time when data gets entered to A2 schema of A oracle 9i database. We have access to A2 schema of A database and B1 schema of B database.way to do it or best practice to do this activity or if there is any third party tool or any available oracle utility to perform it?

Note: A2 schema of A database and B1 schema of B database has one to one mapping.We want to avoid using trigger on A2 schema, how data gets populated from A1 to A2 schema .

View 2 Replies View Related

Server Administration :: Move Indexes (around 300) From Data To Index Tablespace

May 18, 2011

I need to move my indexes (around 300) from data tablespace to index tablespace. What's the best way to do it?

View 9 Replies View Related

Data Guard :: Move BLOB Images In DB To BFILE To Reduce Time And Size Of Backup?

Jan 22, 2012

We are planning to move BLOB images in our DB to BFILE to reduce the time and size of backup.

So when we have a physical dataguard setup, can we configure data guard to copy the OS level BFILE's to the DR site?

View 2 Replies View Related

Maintain Large Tables / Cleanup Data From Our Tables

May 18, 2011

I have to cleanup data from our tables (Production Environment) that contain millions of rows. The question is apart from the solution of the partitioned tables what alternative recommended solution suggests Oracle?

To delete these tables by using a cursor PL/SQL block or to import all the database and in the tables that we want to remove the old rows to use the QUERY option of the data pump utility.

I have used both ways and i have to admit that datapump solution is much much faster than the deletion that suffers from I/O disk.The question again is which method from these two is more reliable and less risky for the health of the database.

View 5 Replies View Related

Implementation Where Data From DB2 Tables Are Moved To Oracle Tables

Sep 3, 2012

I came across an implementation where data from DB2 tables are moved to Oracle tables, for BI solutioning, using some oracle procedures called from MS SQL DTS packages which are scheduled jobs.Just being curious, can this be done using OWB or ODI rather than the above detour. I suppose there are some changes being done in those procedures before the data is being loaded into Oracle tables, can't this be done using OWB/ODI? Can it be scheduled too as jobs using OWB/ODI?

View 1 Replies View Related

SQL & PL/SQL :: Move Data From One Data To Another Database Using Database Link

Mar 19, 2010

I have written a Database procedure to move data from one data to another database using Database link. When i run that procedure on Sql it is running fine, but i use same procedure in a button in form using "When-Button-Pressed" trigger it generates error, error code is ORA-02055 and ORA-02067.

View 8 Replies View Related

Forms :: When User Change / Delete Any Record / Row In Forms Data Automatically Move To Other Table

Dec 25, 2011

when a user change or delete any record or row in forms data automatically move to other table because i want to compare old and new record.

View 8 Replies View Related

Oracle Data Pump - Export Data From Schemas Or Tables

Oct 11, 2012

I need to export only the data from schemas or tables, how to do that with Oracle Data Pump? when we use schemas parameter this export all schema, not only the data right?

View 7 Replies View Related

Data Warehouse - How To Insert / Access Data In The Tables

May 30, 2011

I created a data warehouse in oracle 10g n with three Dimension and one cube after that it crates 4 tables . How to use an insert sql statement to insert data in those tables n how to access them.

View 7 Replies View Related

SQL & PL/SQL :: Move Data From Old Partitions To Corresponding New Partitions?

Jul 1, 2010

Initially 2008 to 2010 and 2009 to 2011 partitions are created, After that two more new partitions are created 2008 to 2009 and 2009 to 2010.

How can we move data from old partitions to corresponding new partitions.

View 9 Replies View Related

Retrieving Data From 3 Or More Tables

Apr 12, 2008

I have a problem with my Oracle 9i SQL Query and I'm struggling to get it done.

I have three tables namely Student, Lease and Room and want to retrieve data from these three tables.

I want the Student name, the Lease details and the Room No from these tables.

The problem with my SQL query is that I get all the information from the tables except from the Room table, where in the column it show Room_No but the values are not displayed, the query is given below.

SELECT STUDENT.STU_FNAME, STUDENT.STU_LNAME, LEASE.LSE_NO, LEASE.LSE_DURATION, LEASE.LSE_STARTS, LEASE.LSE_ENDS, ROOM.ROOM_NO

FROM STUDENT

LEFT OUTER JOIN LEASE ON LEASE.STU_ID = STUDENT.STU_ID
LEFT OUTER JOIN ROOM ON ROOM.PLACE_NO = LEASE.PLACE_NO;

View 3 Replies View Related

Inserting Data Into Two Tables

Feb 15, 2009

I have two tables

table1
col1.....................................col2
primary key................ foreign key refer to col2 in tab2

table2

col1 ........................................... col2
foreign key refer to col1 in tab1 ............ primary key

now my question is how to insert data in to the two tables

View 2 Replies View Related

SQL & PL/SQL :: Comparing Data In Two Tables Row By Row

Sep 23, 2011

I have two databases.

one is "ora" it is a 8i version
2nd is "orcl" it is a 11g version

"Oracle" is the my local database. i wrote following program for comparing the row by row data in both the tables.
Q)Is it BEST practice? If not let me know the best practice to compare data in tables?
Q) If am not using the order by clause its giving me wrong output even though both the data tables has same data. WHY?

DECLARE
v_emptest1 EMP_TEST1%ROWTYPE;
v_emptest2 EMP_TEST1@ora%ROWTYPE;
v_suc_flg NUMBER := 0;
v_cnt1 NUMBER:=0;
[code].......

View 9 Replies View Related

SQL & PL/SQL :: Select Data From All Tables At Once

Sep 12, 2011

Is there any way i can select * from all the tables owned by particular schema at once.

View 22 Replies View Related

SQL & PL/SQL :: Comparing Two Tables Data

Mar 16, 2010

I want to do a comparision for the missing rows between two diffrent tables

TBL1 and TBL2 both with the same structure but with diffrent data some data is identical. though my data is huge i wanted to make sure the technique i am using

View 8 Replies View Related

SQL & PL/SQL :: Getting Data From Multiple Tables

Feb 11, 2011

What are some performance issues you might run into when having to query 3 or 4 tables at a time.

View 1 Replies View Related

SQL & PL/SQL :: How To Get All The Data From Child Tables

Jun 2, 2011

I have a requirement to get the data in "SQL" directly and should not use plsql/ can't redirect the output to a .sql file. I have a master table Main_tab which does have all "child table" names.

I want to build a query such that it will give all the data from child tables.

create table main_tab ( tab_name varchar(10));

insert into main_tab values ('one');
insert into main_tab values ('two');
insert into main_tab values ('three');
create table one ( a char);
insert into one values ('a')
create table two ( b char);
insert into two values ('b')
create table three ( c char);
insert into three values ('c');

Now I have got the query to combine all the table data using

select decode (rownum,1,'','union all ') || 'select * from ' || tab_name example from main_tab

but I am expecting the output to get as
a
b
c
which is the data from tables one,two and three respectively.

I don't want to save the above select data in to .sql and then execute it , instead I want to run it on "fly" in SQL itself. Is there such a possibility to do so?

View 12 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved