Posted: 7/21/2015 11:34:21 PM EDT
|
Looking for help with a question.
I have two tables tablename_2013, around 20 gigs in size (compressed) and tablename_2014, around 20 gigs in size (compressed) they have the same columns / data types. I'd like to combine them into a single table. Normally I'd do: create table tablename as( select * from tablename_2013 union all select * from tablename_2014 ) ... but I haven't been allocated enough space to let all three (tablename, tablename_2013, tablename_2014) exist together. Is there any way to combine them together and replace 2013 and 2014 in the process so I just have the final combined table and not the two separate tables? |
|
Quoted:
Why not just create a view instead? I don't have the correct permissions to create views. (but I can create tables  )
Getting things changed with our DBA group is like dealing with the DMV. Ultimately, I need to take the combined file, and export it to a tab delimited text file, sorted on a few columns that will result in mixing the two tables together. So I was thinking to create the combined table, then run select * From tablename order by 3,6,7 with my query tool to dump the file. I tried running select * From (select * from tablename_2013 union all select * from tablename_2014) a order by 3,6,7 but ended up getting a message about the server being out of temp space. I've been told that if I create a combined table, then run "dbms_stats.gather_Table_Stats('SCHEMA','TABLENAME') that it will let the export query run without the space problems. |
|
Why not INSERT into table 1 the results of a SELECT * from table 2? Then drop 2? Or even better, use MERGE (upsert).
Are you getting an ORA-1652? Talk to the DBAs because you're probably not the only person having the issue. ETA: Just use MERGE. And tell your DBA to get his shit in order. |
|
that would probably work query both tables truncate them on the server insert with sqlldr then sort/query into my final txt file |
|
Quoted:
Why not INSERT into table 1 the results of a SELECT * from table 2? Then drop 2? Or even better, use MERGE (upsert). Are you getting an ORA-1652? Talk to the DBAs because you're probably not the only person having the issue. ETA: Just use MERGE. And tell your DBA to get his shit in order. |
|
Quoted:
Why not INSERT into table 1 the results of a SELECT * from table 2? Then drop 2? Or even better, use MERGE (upsert). Are you getting an ORA-1652? Talk to the DBAs because you're probably not the only person having the issue. ETA: Just use MERGE. And tell your DBA to get his shit in order. What's the syntax for merge? I have a request for more space in to the DBA but they're SLOW. I can't remember what the ORA error number was. I'll generate it again and find out, but it takes a while. |
|
Quoted:
What's the syntax for merge? I have a request for more space in to the DBA but they're SLOW. I can't remember what the ORA error number was. I'll generate it again and find out, but it takes a while. Quoted:
Quoted:
Why not INSERT into table 1 the results of a SELECT * from table 2? Then drop 2? Or even better, use MERGE (upsert). Are you getting an ORA-1652? Talk to the DBAs because you're probably not the only person having the issue. ETA: Just use MERGE. And tell your DBA to get his shit in order. What's the syntax for merge? I have a request for more space in to the DBA but they're SLOW. I can't remember what the ORA error number was. I'll generate it again and find out, but it takes a while. When in doubt, seek Burleson. http://www.dba-oracle.com/oracle_tips_rittman_merge.htm 
|
|
Quoted:
Quoted:
Quoted:
Why not INSERT into table 1 the results of a SELECT * from table 2? Then drop 2? Or even better, use MERGE (upsert). Are you getting an ORA-1652? Talk to the DBAs because you're probably not the only person having the issue. ETA: Just use MERGE. And tell your DBA to get his shit in order. What's the syntax for merge? I have a request for more space in to the DBA but they're SLOW. I can't remember what the ORA error number was. I'll generate it again and find out, but it takes a while. When in doubt, seek Burleson. http://www.dba-oracle.com/oracle_tips_rittman_merge.htm http://www.travel-golf.org/images/gun_223_colt_mt_6700.jpg http://www.travel-golf.org/images/thompson_centerfire_contender_armor_alloy_pistol.jpg awesome! I always love when my google searches turn up a link on his website, because they're usually good answers. now I like the guy even more. |
|
Quoted:
awesome! I always love when my google searches turn up a link on his website, because they're usually good answers. now I like the guy even more. His company's forum is also helpful: http://www.dbaforums.org/oracle/ A good place to research when trying to figure out problems... it's best to lurk & search there. Smart folks that know their stuff and they try to keep it free of nonsense. Especially nonsense of the "low-skill outsourced guy from Bangalore looking for American experts to do his job for him" variety. Just a hint, you may want to suggest that your DBAs read that forum, too. 
|
|
Quoted:
His company's forum is also helpful: http://www.dbaforums.org/oracle/ A good place to research when trying to figure out problems... it's best to lurk & search there. Smart folks that know their stuff and they try to keep it free of nonsense. Especially nonsense of the "low-skill outsourced guy from Bangalore looking for American experts to do his job for him" variety. Just a hint, you may want to suggest that your DBAs read that forum, too.
Quoted:
Quoted:
awesome! I always love when my google searches turn up a link on his website, because they're usually good answers. now I like the guy even more. His company's forum is also helpful: http://www.dbaforums.org/oracle/ A good place to research when trying to figure out problems... it's best to lurk & search there. Smart folks that know their stuff and they try to keep it free of nonsense. Especially nonsense of the "low-skill outsourced guy from Bangalore looking for American experts to do his job for him" variety. Just a hint, you may want to suggest that your DBAs read that forum, too.
"low-skill outsourced guy from Bangalore" you just described our DBAs |
Win a FREE Membership!
Sign up for the ARFCOM weekly newsletter and be entered to win a free ARFCOM membership. One new winner* is announced every week!
You will receive an email every Friday morning featuring the latest chatter from the hottest topics, breaking news surrounding legislation, as well as exclusive deals only available to ARFCOM email subscribers.