Home » SQL & PL/SQL » SQL & PL/SQL » exchange partition help
icon5.gif  exchange partition help [message #208833] Tue, 12 December 2006 05:55 Go to next message
prasanna_anbu
Messages: 34
Registered: November 2006
Location: india
Member
i have 24 tables in one schema A and 1 table with 24 partition in another schema B i want to trasfer the 24 tables data from schema A to 1 table which is in schema B,please help me on this with an example using exchang partitions.
Re: exchange partition help [message #208837 is a reply to message #208833] Tue, 12 December 2006 06:04 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
Found this in that useful set of reference works known as the Oracle manuals:

ALTER TABLE sales
EXCHANGE PARTITION feb97 WITH TABLE sales_feb97
WITHOUT VALIDATION;

Re: exchange partition help [message #208839 is a reply to message #208837] Tue, 12 December 2006 06:07 Go to previous messageGo to next message
prasanna_anbu
Messages: 34
Registered: November 2006
Location: india
Member
this query is useful for load in one partititon but i want load the data dynamically for all partitions
Re: exchange partition help [message #208841 is a reply to message #208833] Tue, 12 December 2006 06:10 Go to previous message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
Write some code to loop through the tables and exchange them with the partitions one by one. You can use dynamic SQL for this.

procedure p_swap_partition(p_temp_table_name in user_tables.table_name%type, 
                           p_indexes in boolean) is
v_sql               varchar2(5000);
v_swap_partition    all_tab_partitions.partition_name%type := NULL;
begin
   v_swap_partition := f_get_partn_name; 
   v_sql := 'ALTER TABLE OPL_SRC_FEED_DATA EXCHANGE PARTITION '||v_swap_partition||' WITH TABLE '||p_temp_table_name;
   if p_indexes then
      v_sql := v_sql ||' INCLUDING INDEXES';
   end if;
   --dbms_Output.put_line(v_sql);
   execute immediate v_sql;
end p_swap_partition; 

[Updated on: Tue, 12 December 2006 06:11]

Report message to a moderator

Previous Topic: Help needed
Next Topic: Can we access Enterprise Manager of 10g server from 9i client?
Goto Forum:
  


Current Time: Sat Dec 10 14:33:16 CST 2016

Total time taken to generate the page: 0.11319 seconds