-
Insert in append mode
Hi folks,
i have 2 tables named F571226, F751226_10march in two different schema testdta, backupdta respectively
Note: F571226 doesnt have any data in testdta schema, both have same columns names
Now my requirement is to insert data into F571226 from F571226_10march
but when i try to insert by below command it taking too time and failed
insert /*+ APPEND PARALLEL*/ into testdta.F571226 value select * from backupdta.F571226_10march14
shall i specify each field (columns name) instead of specifying source table (backupdta.F571226_10march) in the above syntax?. source table has got around 25000000 rows
please correct my syntax if it is wrong
insert /*+ APPEND PARALLEL */ nologging into testdta.F571226 value select col1, col2,col3,col4,col5 ................... from backupdta.F571226_10march.
thanks & regards
-
Unless the table is partitioned, I don't see how the parallel hint will help. Since the destination table is empty,
you might want to get the DDL for the indexes and constraints, drop them then do the insert without parallel.
You can then create the indexes and constraints.
If the indexes and constraints are in place during the insert ever row needs to be checked to see if it is violating
a constraint and each index needs to be updated for each row.
-
Originally Posted by younusdba
... and failed...
Could you please share the returned error stack, please?
Originally Posted by younusdba
shall i specify each field ...
No, both tables have the same structure therefore no need to specify column by column.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
Originally Posted by younusdba
Hi folks,
i have 2 tables named F571226, F751226_10march in two different schema testdta, backupdta respectively
Note: F571226 doesnt have any data in testdta schema, both have same columns names
Now my requirement is to insert data into F571226 from F571226_10march
but when i try to insert by below command it taking too time and failed
insert /*+ APPEND PARALLEL*/ into testdta.F571226 value select * from backupdta.F571226_10march14
shall i specify each field (columns name) instead of specifying source table (backupdta.F571226_10march) in the above syntax?. source table has got around 25000000 rows
please correct my syntax if it is wrong
insert /*+ APPEND PARALLEL */ nologging into testdta.F571226 value select col1, col2,col3,col4,col5 ................... from backupdta.F571226_10march.
thanks & regards
You can try using Datapump (impd) with TABLE_EXISTS_ACTION and REMAP_SCHEMA import parameters -
http://www.oraclesql-plsql.com/Datapump.html
Hope this would help
-
You can try using Datapump (impdp) with TABLE_EXISTS_ACTION and REMAP_SCHEMA import options -
http://www.oraclesql-plsql.com/Datapump.html
Hope this solves your issu
-
CTAS is the way.
Originally Posted by younusdba
Hi folks,
i have 2 tables . . . F571226 doesnt have any data
. . . E t c . . .
Try CTAS:
Code:
DROP TABLE Testdta.F571226 CASCADE CONSTRAINTS;
CREATE TABLE Testdta.F571226
NOLOGGING
AS
SELECT *
FROM Backupdta.F571226_10march;
"The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|