Home » RDBMS Server » Server Administration » global temporay table does not work! (oracle ,12.1.0.2,aix 7.1)
global temporay table does not work! [message #667055] Wed, 06 December 2017 03:06 Go to next message
lzfhope
Messages: 69
Registered: July 2006
Member
HI,ALL
I HAVA A RAC db ,WITH 3 NODES ON AIX 7.1
The rac db is a cdb,including 5 pdbs ,which names are pdb1,pdb2,pdb2,pdb4,pdb5.
At The Beginning,I cound use global temporary table(on commit preserve) to store data from remote db,sqls such as following:
  decalre
    vn_cnt pls_integer;
  begin
    insert /*+append */ into gt_performance(c1,c2) select c1,c2 from t_performance@remotedb;
    commit;
    insert into t_performance(c1,c2) select .... from gt_performance where ... group ...;
    vn_cnt:=sql%rowcount;
    dbms_output.put_line(vn_cnt);
    commit;
  
Now,it does not work,the output is zero,which is impossible,becase the there are many rows in remote table.
no message in alert log!
But,if block changed like below:
  decalre
    vn_cnt pls_integer;
  begin
    insert /*00+append */ into gt_performance(c1,c2) select c1,c2 from t_performance@remotedb;   --disable append
    --commit;  --disable commit
    insert into t_performance(c1,c2) select .... from gt_performance where ... group ...;
    vn_cnt:=sql%rowcount;
    dbms_output.put_line(vn_cnt);
    commit;
  
Then,the output would be correct--vn_cnt>0.

thanks!
Re: global temporay table does not work! [message #667060 is a reply to message #667055] Wed, 06 December 2017 03:33 Go to previous messageGo to next message
Michel Cadot
Messages: 65255
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Not an answer to the question but why don't you make the computation in the remote site and send only the result instead of using a stage table?
insert into t_performance(c1,c2) select ... from gt_performance@remotedb where ... group ...;
Re: global temporay table does not work! [message #667064 is a reply to message #667055] Wed, 06 December 2017 04:09 Go to previous messageGo to next message
John Watson
Messages: 7186
Registered: January 2010
Location: Global Village
Senior Member
When you created the global temporary table, what did you specify for the ON COMMIT clause? The behaviour you describe fits what is expected if you left it on default.
Re: global temporay table does not work! [message #667079 is a reply to message #667055] Wed, 06 December 2017 08:01 Go to previous messageGo to next message
joy_division
Messages: 4803
Registered: February 2005
Location: East Coast USA
Senior Member
lzfhope wrote on Wed, 06 December 2017 04:06

insert /*00+append */ into gt_performance(c1,c2) select c1,c2 from t_performance@remotedb; --disable append
--commit; --disable commit
Commenting out the APPEND has no effect, it is the COMMIT as John said. Commit will delete your rows if you created the GTT as "on commit delete rows" or left it out.

Sorry to repeat John's response, but I just wanted to add that the append hint had no effect.
Re: global temporay table does not work! [message #667082 is a reply to message #667079] Wed, 06 December 2017 08:54 Go to previous messageGo to next message
Michel Cadot
Messages: 65255
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
I cound use global temporary table(on commit preserve)
Re: global temporay table does not work! [message #667089 is a reply to message #667082] Wed, 06 December 2017 21:08 Go to previous messageGo to next message
lzfhope
Messages: 69
Registered: July 2006
Member
Today,i found that init parameter "temp_undo_enabled" is set to true,which is the only changed parameter recently!

Have a try,restored it to "false"!
as a result,global temporary table(on commit preserve) could still hold data after commiting tranaction which retrieved data from remote db!
--
Maybe ,this is a bug!



Re: global temporay table does not work! [message #667092 is a reply to message #667089] Thu, 07 December 2017 01:29 Go to previous messageGo to next message
Michel Cadot
Messages: 65255
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Have a look at MOS note: Unexpected Behavior Of Global Temporary Table When Temp_undo_enabled set to True and Using DB Links In Database 12c (Doc ID 2169650.1)

In short, the answer in the last but one point of Restrictions on Temporary Tables.

Now, please, answer my question.

[Updated on: Thu, 07 December 2017 01:29]

Report message to a moderator

Re: global temporay table does not work! [message #667130 is a reply to message #667082] Fri, 08 December 2017 12:44 Go to previous message
joy_division
Messages: 4803
Registered: February 2005
Location: East Coast USA
Senior Member
Michel Cadot wrote on Wed, 06 December 2017 09:54

Quote:
I cound use global temporary table(on commit preserve)
Oh dear, I completely missed that and my mind must be going because I don't even recall the original post mentioning RAC or CDB.
Previous Topic: Oracle 12c (12.1.0.2 SE) together with Windows 10 "Fast Startup" option
Next Topic: which one should be install first
Goto Forum:
  


Current Time: Fri Dec 15 12:20:01 CST 2017

Total time taken to generate the page: 0.01978 seconds