Home » SQL & PL/SQL » SQL & PL/SQL » Copying Large amounts of Data Effectively and Efficiently
Copying Large amounts of Data Effectively and Efficiently [message #262988] Tue, 28 August 2007 13:36 Go to next message
hdogg
Messages: 93
Registered: March 2007
Member
I need to copy large amounts of data from one table to the other...

What's the best way to do this?

ie... could i find a way to break into batches???

do i do an insert statement and have it chew away for hours?
Re: Copying Large amounts of Data Effectively and Efficiently [message #262994 is a reply to message #262988] Tue, 28 August 2007 13:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
insert/select in append and parallel mode is the fastest way.

Regards
Michel
Re: Copying Large amounts of Data Effectively and Efficiently [message #262999 is a reply to message #262994] Tue, 28 August 2007 13:58 Go to previous messageGo to next message
hdogg
Messages: 93
Registered: March 2007
Member
One issue...

The table i am copying from has view only access.... how do i make both parallel?

So....

if view1 has jobcode and table2 has jobcode

would this work properly?

ALTER VIEW view1 PARALLEL (DEGREE 10); 
ALTER TABLE table2 PARALLEL (DEGREE 10); 
ALTER SESSION ENABLE PARALLEL DML; 
INSERT +append INTO table2
   SELECT jobcode FROM view1; 
COMMIT; 




Re: Copying Large amounts of Data Effectively and Efficiently [message #263011 is a reply to message #262999] Tue, 28 August 2007 14:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Try:
insert /*+ APPEND PARALLEL(10) */ INTO TABLE1
select /*+ PARALLEL(10) */ * from VIEW2;

Regards
Michel
Re: Copying Large amounts of Data Effectively and Efficiently [message #263031 is a reply to message #262988] Tue, 28 August 2007 16:36 Go to previous messageGo to next message
hdogg
Messages: 93
Registered: March 2007
Member
That command works on a test table tried.... however.... how could i modify that to include only 3 fields?

also, does the destination table have to have the same data types as the source table?...if so, i have no way of knowing what the view data types are
Re: Copying Large amounts of Data Effectively and Efficiently [message #263067 is a reply to message #263031] Wed, 29 August 2007 00:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
how could i modify that to include only 3 fields?

List the fields in insert and select clause.
Quote:
does the destination table have to have the same data types as the source table?...

Otherwise there are conversions and if there is no possible implicit conversion there is an error.
Note: don't use implicit conversion, always use explicit one in your select, statement.
Quote:
i have no way of knowing what the view data types are

"desc view"

Regards
Michel


Re: Copying Large amounts of Data Effectively and Efficiently [message #263081 is a reply to message #262988] Wed, 29 August 2007 00:53 Go to previous messageGo to next message
thani_oracle
Messages: 44
Registered: August 2007
Location: Bangalore
Member

Hi
What Michel has given that is effective Way.....

Write insert/select statement

Regards,
Thani.....
Re: Copying Large amounts of Data Effectively and Efficiently [message #263294 is a reply to message #263081] Wed, 29 August 2007 11:00 Go to previous messageGo to next message
hdogg
Messages: 93
Registered: March 2007
Member
Are you talking about the traditional format of the insert and select statement?

I tried modifying it to

insert /*+ APPEND PARALLEL(10) */ COLUMN1 [/B]INTO TABLE1
select /*+ PARALLEL(10) */ COLUMN1 from VIEW2;


... It won't even execute on that and yells at me.


I still can't figure out how to select the fields, so i created a view of the view ie PSEUDO_VIEW

insert /*+ APPEND PARALLEL(10) */ INTO TABLE1
select /*+ PARALLEL(10) */  from PSEUDO_VIEW;


The query was chewed on for a long time and eventually came up with a "compilation error".


I have put tons of time researching in this, and am stumped....
Re: Copying Large amounts of Data Effectively and Efficiently [message #263299 is a reply to message #263294] Wed, 29 August 2007 11:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Copy and paste your screen with errors, we can't now what's error it is.

Regards
Michel
Re: Copying Large amounts of Data Effectively and Efficiently [message #263304 is a reply to message #262988] Wed, 29 August 2007 11:30 Go to previous messageGo to next message
hdogg
Messages: 93
Registered: March 2007
Member
This statement work fine... copying a field :

insert /*+ APPEND PARALLEL(10) */ INTO TABLE1
select /*+ PARALLEL(10) */ COLUMN1 from TABLE2;


So the sql syntax is right....

But it throws in error on this:
insert /*+ APPEND PARALLEL(10) */ INTO JC
select /*+ PARALLEL(10) */ JOB_CODE from GSI.PCTCOMP_ACC_VW


It hangs for 10 to 15 minutes and then displays:

Quote:
Error starting at line 1 in command:
insert /*+ APPEND PARALLEL(10) */ INTO JC
select /*+ PARALLEL(10) */ JOB_CODE from GSI.PCTCOMP_ACC_VW
Error report:
SQL Error: ORA-06540: PL/SQL: compilation error
ORA-06553: PLS-123: program too large
ORA-06512: at "GSI.PCTCOMP_PKG", line 1781
ORA-06512: at "GSI.PCTCOMP_PKG", line 2028
ORA-06512: at "GSI.PCTCOMP_DISC_PKG", line 12
ORA-06512: at "GSI.PCTCOMP_DISC_PKG", line 64
06540. 00000 - "PL/SQL: compilation error"
*Cause: A pl/sql compilation error occurred.
However, the user generally will not see this error message.
Instead, there will be accompanying PLS-nnnnn error messages.
*Action: See accompanying PLS-nnnnn error messages.

Re: Copying Large amounts of Data Effectively and Efficiently [message #263306 is a reply to message #263304] Wed, 29 August 2007 11:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
PLS-123: program too large

It seems your view is not a simple view but use packages and the overall is too large.
Fix that.

By the way, when you hide important things like that you won't get accurate answers. You surely get answer that will not work for you.
Too bad!
In addition, you waste our time.

Regards
Michel
Re: Copying Large amounts of Data Effectively and Efficiently [message #263307 is a reply to message #263306] Wed, 29 August 2007 12:00 Go to previous message
hdogg
Messages: 93
Registered: March 2007
Member
I guess this is my issue.....

The "godfather" only gave me rights to that view. So basically.... can this not be done with having the dba modify the granted view?
Previous Topic: help about Utl_smtp
Next Topic: how can i count the columns in a table?
Goto Forum:
  


Current Time: Sat Dec 10 20:42:05 CST 2016

Total time taken to generate the page: 0.13744 seconds