Timeout problem

O

oshean

Member
I have a dblink from a view in one database to a view in another database. A select from this view takes about 80 seconds via a sqlplus session and it returns almost 300,000 rows.
When i try the select from pl/sql developer, I always get 0 rows returned.
This problem does not reproduce on TOAD,SQLPLUS or with Oracle SQL Developer, each of these products returns the correct number of rows.

Is there some timeout parameter that is stopping these rows being returned via PL/SQL Develepor.
I am using version 7.0.2 to connect to a 10.1 database.
Thanks
 
There is no timeout. To obtain some more diagnostic information, can you modify the shortcut and add the debugsql parameter? For example:

"C:\Program Files\PLSQL Developer\plsqldev.exe" debugsql

Reproduce the problem and send me the debug.txt file that is generated in the PL/SQL Developer directory.
 
Thanks Marco.

The output is :

19/07/2006 16:15:06 Application started
19/07/2006 16:15:06 InitPreferences
19/07/2006 16:15:06 Load C:\Program Files\PLSQL Developer\Preferences\Default = 0
19/07/2006 16:15:06 PreferenceSet.Load C:\Program Files\PLSQL Developer\Preferences\Default\Default.ini
19/07/2006 16:15:06 PreferenceSet.Load OK
19/07/2006 16:15:06 PreferenceSet.Save
19/07/2006 16:15:07 PreferenceSet filename = C:\Program Files\PLSQL Developer\Preferences\Default\Default.ini
19/07/2006 16:15:07 PreferenceSet.Save end
19/07/2006 16:15:07 Load C:\Program Files\PLSQL Developer\Preferences\eproffit = 0
19/07/2006 16:15:07 PreferenceSet.Load C:\Program Files\PLSQL Developer\Preferences\eproffit\default.ini
19/07/2006 16:15:07 PreferenceSet.Load OK
19/07/2006 16:15:07 PreferenceSet.Save
19/07/2006 16:15:07 PreferenceSet filename = C:\Program Files\PLSQL Developer\Preferences\eproffit\default.ini
19/07/2006 16:15:07 PreferenceSet.Save end
19/07/2006 16:15:07 PreferenceSet.Load C:\Program Files\PLSQL Developer\Preferences\eproffit\user.prefs
19/07/2006 16:15:07 PreferenceSet.Load OK
19/07/2006 16:15:07 InitPreferences end
19/07/2006 16:15:07 SortPreferenceSets
19/07/2006 16:15:07 - Default eproffit
19/07/2006 16:15:07 - System Default
19/07/2006 16:15:07 LoadPreferences
19/07/2006 16:15:07 LoadPreferences searching for default
19/07/2006 16:15:07 C:\Program Files\PLSQL Developer\Preferences\eproffit\default.ini
19/07/2006 16:15:07 LoadPreferences end
19/07/2006 16:15:07 FormCreate Logon.Execute
19/07/2006 16:15:12 TOracleSession PLSQLDevForm.Session Start Session.LogOn as cifhub@cifp
19/07/2006 16:15:14 TOracleQuery Query Start Query.Execute
SQL = select to_char(userenv('SESSIONID')) from dual
19/07/2006 16:15:15 TOracleQuery Query End (1 record processed)
Duration = 0.411
19/07/2006 16:15:15 TOracleSession PLSQLDevForm.Session End
Duration = 2.534
19/07/2006 16:15:15 LogonHistorySave start
19/07/2006 16:15:15 ChangeUserPref LogonHistory,
19/07/2006 16:15:15 ChangeUserPref DSA,
19/07/2006 16:15:15 ChangeUserPref end
19/07/2006 16:15:15 ChangeUserPref ColumnAssociations,
19/07/2006 16:15:15 ChangeUserPref end
19/07/2006 16:15:15 ChangeUserPref ImportAssociations,
19/07/2006 16:15:15 ChangeUserPref end
19/07/2006 16:15:15 ChangeUserPref BrowserFilters,
19/07/2006 16:15:15 ChangeUserPref end
19/07/2006 16:15:15 ChangeUserPref BrowserFolders,
19/07/2006 16:15:15 ChangeUserPref end
19/07/2006 16:15:15 ChangeUserPref SessionQueries,
19/07/2006 16:15:15 ChangeUserPref end
19/07/2006 16:15:15 ChangeUserPref ExternalTools,
19/07/2006 16:15:15 ChangeUserPref end
19/07/2006 16:15:15 ChangeUserPref Documents,
19/07/2006 16:15:15 ChangeUserPref end
19/07/2006 16:15:15 ChangeUserPref Reports,
19/07/2006 16:15:15 ChangeUserPref end
19/07/2006 16:15:15 LogonHistorySave end
19/07/2006 16:15:15 FormCreate Session.Connected
19/07/2006 16:15:15 FormCreate Logon.Executed -1
19/07/2006 16:15:15 FormCreate TDBNames.Make
19/07/2006 16:15:15 FormCreate SessionChanged
19/07/2006 16:15:15 TOracleQuery Query Start Query.Execute
SQL = select * from v$version
19/07/2006 16:15:15 TOracleQuery Query End (5 records processed)
Duration = 0.421
19/07/2006 16:15:15 SessionChanged SetDBAViews
19/07/2006 16:15:15 SessionChanged Authorizations.Load
19/07/2006 16:15:15 TOracleQuery $02931B40 Start Query.Execute
SQL = select grantee, name from
sys.plsqldev_authorization
where grantee in (user, 'PUBLIC')
or grantee in (select role from sys.session_roles)
19/07/2006 16:15:16 TOracleQuery $02931B40 End
Result = ORA-00942: table or view does not exist
Duration = 0.42
19/07/2006 16:15:16 SessionChanged EnableAuthorizedMenus
19/07/2006 16:15:16 TOracleSession $017C7010 Start Session.LogOn as cifhub@cifp
19/07/2006 16:15:18 TOracleQuery Query Start Query.Execute
SQL = select to_char(userenv('SESSIONID')) from dual
19/07/2006 16:15:18 TOracleQuery Query End (1 record processed)
Duration = 0.421
19/07/2006 16:15:18 TOracleSession $017C7010 End
Duration = 2.263
19/07/2006 16:15:18 SetSessionAction starts: Main session
19/07/2006 16:15:18 TOracleQuery $02931CA0 Start Query.Execute
SQL = select null from dual
19/07/2006 16:15:18 TOracleQuery $029319E0 Start Query.Execute
SQL = select object_name, object_type
from sys.user_objects o
where o.object_type in ('TABLE', 'VIEW', 'PACKAGE','TYPE', 'PROCEDURE', 'FUNCTION', 'SEQUENCE')
19/07/2006 16:15:19 TOracleQuery $02931CA0 End (1 record processed)
Duration = 0.41
19/07/2006 16:15:19 TOracleQuery $02931CA0 Start Query.Execute
SQL = begin sys.dbms_application_info.set_module('PL/SQL Developer', :action); end;
:ACTION = Main session
19/07/2006 16:15:19 TOracleQuery BytesPerCharacterQuery Start Query.Execute
SQL = select length(chr(2000000000)) l4, length(chr(2000000)) l3, length(chr(20000)) l2 from dual
19/07/2006 16:15:19 TOracleQuery $029319E0 End (100 records processed)
Duration = 0.571
19/07/2006 16:15:19 TOracleQuery $029319E0 Start Query.Next for up to 100 records
19/07/2006 16:15:19 TOracleQuery $029319E0 End (100 records processed)
Duration = 0.14
19/07/2006 16:15:19 TOracleQuery $029319E0 Start Query.Next for up to 100 records
19/07/2006 16:15:19 TOracleQuery BytesPerCharacterQuery End (1 record processed)
Duration = 0.421
19/07/2006 16:15:19 TOracleQuery $029319E0 End (100 records processed)
Duration = 0.14
19/07/2006 16:15:19 TOracleQuery $029319E0 Start Query.Next for up to 100 records
19/07/2006 16:15:19 TOracleQuery $029319E0 End (100 records processed)
Duration = 0.13
19/07/2006 16:15:19 TOracleQuery $029319E0 Start Query.Next for up to 100 records
19/07/2006 16:15:19 TOracleQuery $029319E0 End (96 records processed)
Duration = 0.141
19/07/2006 16:15:19 TOracleQuery $029319E0 Start Query.Execute
SQL = select s.synonym_name object_name, o.object_type
from sys.all_synonyms s,
sys.all_objects o
where s.owner in ('PUBLIC', user)
and o.owner = s.table_owner
and o.object_name = s.table_name
and o.object_type in ('TABLE', 'VIEW', 'PACKAGE','TYPE', 'PROCEDURE', 'FUNCTION', 'SEQUENCE')
19/07/2006 16:15:19 TOracleQuery $02931CA0 End
Duration = 0.832
19/07/2006 16:15:20 SetSessionAction ends
19/07/2006 16:15:20 ExecuteConnectScript starts
19/07/2006 16:15:20 ExecuteConnectScript ends
19/07/2006 16:15:20 TOracleQuery PLSQLDevForm.DynamicQuery Start Query.Execute
SQL = select value from v$nls_parameters where parameter = 'NLS_LENGTH_SEMANTICS'
19/07/2006 16:15:20 TOracleQuery $029319E0 End (100 records processed)
Duration = 0.44
19/07/2006 16:15:20 TOracleQuery $029319E0 Start Query.Next for up to 100 records
19/07/2006 16:15:20 TOracleQuery $029319E0 End (100 records processed)
Duration = 0.14
19/07/2006 16:15:20 TOracleQuery $029319E0 Start Query.Next for up to 100 records
19/07/2006 16:15:20 TOracleQuery PLSQLDevForm.DynamicQuery End (1 record processed)
Duration = 0.421
19/07/2006 16:15:20 SessionChanged LoadRecentObjects
19/07/2006 16:15:20 SessionChanged Notify children
19/07/2006 16:15:20 SessionChanged Logoff secondary sessions
19/07/2006 16:15:20 SessionChanged SessionMode = smDual
19/07/2006 16:15:20 SessionChanged EnableChildFunctions
19/07/2006 16:15:20 Toolbar.NavDisable
19/07/2006 16:15:20 SessionChanged
OracleHomeKey: SOFTWARE\ORACLE\KEY_OraClient10g_home1
OracleHomeDir: C:\Oracle\product\10.1.0\Client_1
Found: oci.dll
Using: C:\Oracle\product\10.1.0\Client_1\bin\oci.dll
19/07/2006 16:15:20 TOracleQuery $029319E0 End (100 records processed)
Duration = 0.141
19/07/2006 16:15:20 TOracleQuery $029319E0 Start Query.Next for up to 100 records
19/07/2006 16:15:20 TOracleQuery $029319E0 End (100 records processed)
Duration = 0.14
19/07/2006 16:15:20 TOracleQuery $029319E0 Start Query.Next for up to 100 records
19/07/2006 16:15:20 TOracleQuery $029319E0 End (100 records processed)
Duration = 0.15
19/07/2006 16:15:20 TOracleQuery $029319E0 Start Query.Next for up to 100 records
19/07/2006 16:15:20 SortPreferenceSets
19/07/2006 16:15:20 - Default eproffit
19/07/2006 16:15:20 - System Default
19/07/2006 16:15:21 TOracleQuery $029319E0 End (100 records processed)
Duration = 0.14
19/07/2006 16:15:21 TOracleQuery $029319E0 Start Query.Next for up to 100 records
19/07/2006 16:15:21 TOracleQuery $029319E0 End (100 records processed)
Duration = 0.14
19/07/2006 16:15:21 TOracleQuery $029319E0 Start Query.Next for up to 100 records
19/07/2006 16:15:21 Toolbar.NavDisable
19/07/2006 16:15:21 Toolbar.NavDisable
19/07/2006 16:15:21 TOracleQuery $029319E0 End (100 records processed)
Duration = 0.141
19/07/2006 16:15:21 TOracleQuery $029319E0 Start Query.Next for up to 100 records
19/07/2006 16:15:21 WMUpdatePopups starts
19/07/2006 16:15:21 LoadDesktop starts
19/07/2006 16:15:21 LoadDesktop ends
19/07/2006 16:15:21 WMUpdatePopups ends
19/07/2006 16:15:21 TOracleQuery $029319E0 End (100 records processed)
Duration = 0.15
19/07/2006 16:15:21 TOracleQuery $029319E0 Start Query.Next for up to 100 records
19/07/2006 16:15:21 TOracleQuery $029319E0 End (100 records processed)
Duration = 0.34
19/07/2006 16:15:21 TOracleQuery $029319E0 Start Query.Next for up to 100 records
19/07/2006 16:15:22 TOracleQuery $029319E0 End (100 records processed)
Duration = 0.191
19/07/2006 16:15:22 TOracleQuery $029319E0 Start Query.Next for up to 100 records
19/07/2006 16:15:22 TOracleQuery $029319E0 End (100 records processed)
Duration = 0.14
19/07/2006 16:15:22 TOracleQuery $029319E0 Start Query.Next for up to 100 records
19/07/2006 16:15:22 TOracleQuery $029319E0 End (100 records processed)
Duration = 0.14
19/07/2006 16:15:22 TOracleQuery $029319E0 Start Query.Next for up to 100 records
19/07/2006 16:15:22 TOracleQuery $029319E0 End (100 records processed)
Duration = 0.14
19/07/2006 16:15:22 TOracleQuery $029319E0 Start Query.Next for up to 100 records
19/07/2006 16:15:22 TOracleQuery $029319E0 End (100 records processed)
Duration = 0.141
19/07/2006 16:15:22 TOracleQuery $029319E0 Start Query.Next for up to 100 records
19/07/2006 16:15:22 TOracleQuery $029319E0 End (100 records processed)
Duration = 0.14
19/07/2006 16:15:22 TOracleQuery $029319E0 Start Query.Next for up to 100 records
19/07/2006 16:15:22 TOracleQuery $029319E0 End (100 records processed)
Duration = 0.13
19/07/2006 16:15:22 TOracleQuery $029319E0 Start Query.Next for up to 100 records
19/07/2006 16:15:23 TOracleQuery $029319E0 End (100 records processed)
Duration = 0.14
19/07/2006 16:15:23 TOracleQuery $029319E0 Start Query.Next for up to 100 records
19/07/2006 16:15:23 TOracleQuery $029319E0 End (100 records processed)
Duration = 0.14
19/07/2006 16:15:23 TOracleQuery $029319E0 Start Query.Next for up to 100 records
19/07/2006 16:15:23 TOracleQuery $029319E0 End (100 records processed)
Duration = 0.151
19/07/2006 16:15:23 TOracleQuery $029319E0 Start Query.Next for up to 100 records
19/07/2006 16:15:23 TOracleQuery $029319E0 End (11 records processed)
Duration = 0.14
19/07/2006 16:15:23 TOracleSession $017C7010 Start Session.LogOff
19/07/2006 16:15:23 TOracleSession $017C7010 End
Duration = 0.27
19/07/2006 16:15:48 Create TSQLForm
19/07/2006 16:15:48 SQLForm
19/07/2006 16:15:48 NavigationBookmarks.EnableButtons(Rebuild)
19/07/2006 16:15:48 NavigationBookmarks: Backward=0, Forward=0
19/07/2006 16:16:01 SQLForm Initializing...
19/07/2006 16:16:01 SetConnected starts
19/07/2006 16:16:01 SetConnected: Connect starts
19/07/2006 16:16:01 TOracleSession PLSQLDevForm.$017C72B0 Start Session.LogOn as cifhub@cifp
19/07/2006 16:16:03 TOracleQuery Query Start Query.Execute
SQL = select to_char(userenv('SESSIONID')) from dual
19/07/2006 16:16:03 TOracleQuery Query End (1 record processed)
Duration = 0.411
19/07/2006 16:16:04 TOracleSession PLSQLDevForm.$017C72B0 End
Duration = 2.263
19/07/2006 16:16:04 SetConnected: (Dis)connect ends
19/07/2006 16:16:04 SetSessionAction starts: Secondary session
19/07/2006 16:16:04 TOracleQuery $02932220 Start Query.Execute
SQL = select null from dual
19/07/2006 16:16:04 TOracleQuery $02932220 End (1 record processed)
Duration = 0.411
19/07/2006 16:16:04 TOracleQuery $02932220 Start Query.Execute
SQL = begin sys.dbms_application_info.set_module('PL/SQL Developer', :action); end;
:ACTION = Secondary session
19/07/2006 16:16:04 TOracleQuery BytesPerCharacterQuery Start Query.Execute
SQL = select length(chr(2000000000)) l4, length(chr(2000000)) l3, length(chr(20000)) l2 from dual
19/07/2006 16:16:04 TOracleQuery BytesPerCharacterQuery End (1 record processed)
Duration = 0.41
19/07/2006 16:16:05 TOracleQuery $02932220 End
Duration = 0.841
19/07/2006 16:16:05 SetSessionAction ends
19/07/2006 16:16:05 ExecuteConnectScript starts
19/07/2006 16:16:05 ExecuteConnectScript ends
19/07/2006 16:16:05 TOracleQuery $02932220 Start Query.Execute
SQL = select sid, serial# from v$session where audsid = userenv('SESSIONID')
19/07/2006 16:16:05 TOracleQuery $02932220 End
Result = ORA-00942: table or view does not exist
Duration = 0.411
19/07/2006 16:16:06 EnableOutput starts
19/07/2006 16:16:06 TOracleQuery PLSQLDevForm.EnableOutputQuery Start Query.Execute
SQL = begin
if :enable = 0 then
sys.dbms_output.disable;
else
sys.dbms_output.enable(:size);
end if;
end;
:ENABLE = 1
:SIZE = 10000
19/07/2006 16:16:06 TOracleQuery PLSQLDevForm.EnableOutputQuery End
Duration = 0.411
19/07/2006 16:16:06 EnableOutput done
19/07/2006 16:16:06 InitStatistics starts
19/07/2006 16:16:06 TStatistics.Init starts
19/07/2006 16:16:06 TStatistics.Init ends
19/07/2006 16:16:06 TOracleQuery $02932220 Start Query.Execute
SQL = select 'x' from dual
19/07/2006 16:16:07 TOracleQuery $02932220 End (1 record processed)
Duration = 0.411
19/07/2006 16:16:07 InitStatistics ends
19/07/2006 16:16:07 NavigationBookmarks.EnableButtons(Rebuild)
19/07/2006 16:16:07 NavigationBookmarks: Backward=0, Forward=0
19/07/2006 16:16:07 SQLForm Executing...
19/07/2006 16:16:07 SQLForm Executing...
19/07/2006 16:16:07 TSQLThread SQLThread.Execute
19/07/2006 16:16:07 TOracleQuery Query Start Query.Execute
SQL = begin :id := sys.dbms_transaction.local_transaction_id; end;
:ID = Null
19/07/2006 16:16:08 TOracleQuery Query End
Duration = 0.281
19/07/2006 16:16:08 TOracleQuery $029324E0 Start Query.Execute
SQL = select count(*) from n_v
19/07/2006 16:17:02 TOracleQuery $029324E0 End (1 record processed)
Duration = 54.408
19/07/2006 16:17:02 TOracleQuery SQLForm.TestQuery Start Query.Describe
SQL = SELECT * FROM n_v
19/07/2006 16:17:03 TOracleQuery SQLForm.TestQuery End
Duration = 1.022
19/07/2006 16:17:03 TOracleQuery Query Start Query.Execute
SQL = begin :id := sys.dbms_transaction.local_transaction_id; end;
:ID = Null
19/07/2006 16:17:03 TOracleQuery Query End
Duration = 0.14
19/07/2006 16:17:03 TOracleSession PLSQLDevForm.$017C72B0 Start Session.Rollback
19/07/2006 16:17:03 TOracleSession PLSQLDevForm.$017C72B0 End
Duration = 0.13
19/07/2006 16:17:03 TSQLThread SQLThread Postmessage
19/07/2006 16:17:03 TSQLForm.ThreadFinished
19/07/2006 16:17:03 TSQLThread SQLThread Suspend
19/07/2006 16:17:04 TSQLForm.ThreadFinished: SQLThread.Suspended
19/07/2006 16:17:04 SQLForm 1 row selected in 55.871 seconds
19/07/2006 16:17:04 TOracleQuery Query Start Query.Execute
SQL = begin :id := sys.dbms_transaction.local_transaction_id; end;
:ID = Null
19/07/2006 16:17:04 TOracleQuery Query End
Duration = 0.14
19/07/2006 16:17:04 NavigationBookmarks.EnableButtons(Rebuild)
19/07/2006 16:17:04 NavigationBookmarks: Backward=0, Forward=0
19/07/2006 16:17:04 TOracleQuery $02932380 Start Query.Execute
SQL = begin
sys.dbms_output.get_line(line => :line, status => :status);
end;
:LINE = Null
:STATUS = Null
19/07/2006 16:17:04 TOracleQuery $02932380 End
Duration = 0.27
19/07/2006 16:17:04 SQLForm 1 row selected in 55.871 seconds
19/07/2006 16:17:06 TOracleQuery $02932380 Start Query.Execute
SQL = select 'x' from dual
19/07/2006 16:17:07 TOracleQuery $02932380 End (1 record processed)
Duration = 0.41
 
You must log in or register to reply here.

Similar threads

L
  • Discussion Discussion
Accessing objects from another schema
Replies
2
Views
363
Lukasz Indeka
L
S
  • Discussion Discussion
Why Isn't the alias visual column selection not appearing when database links are used
Replies
1
Views
384
Marco Kalter
Marco Kalter
G
  • Discussion Discussion
Suggestion: Transform selected text in editor with (package) function call?
Replies
4
Views
478
Marco Kalter
Marco Kalter
I
  • Discussion Discussion
Multiple Connections' Recent List: Any ability to remove items?
Replies
2
Views
508
IFS
I
S
  • Discussion Discussion
Problem with program
Replies
1
Views
353
Marco Kalter
Marco Kalter
Back
Top