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 |