long running sql of plsqldev on 10gR2

[plsql_newbie]

I have problem with plsqldev and oracle10gR2 on AIX.
Next long running sql makes "max session exceeded" error. (My DB is set this parameter 200)

select count(*) from user$ u, xdb.xdb$schema s where u.user# = :1 and u.name = s.xmldata.schema_owner and (xdb.xdb$Extname2Intname(s.xmldata.schema_url, s.xmldata.schema_owner) = :2)

Increasing init parameter processes is not correct answer, I think.
It seems plsqldev's internal query.

Is there any way to solve this problem?
I want to make this sql faster or plsqldev don't query it.

thanks.

 

[Marco Kalter]

This is not a query that is generated directly by PL/SQL Developer. What happens if you perform the same long-running query in SQL*Plus?

 

[santunes]

Hi,
Same problem here, just after login into a 10gR2, two sessions are created and one of them keep active and running the command below forever.

select count(*) from user$ u, xdb.xdb$schema s where u.user#= :1
and u.name = s.xmldata.schema_owner
and (xdb.xdb$Extname2Intnames.xmldata.schema_url, s.xmldata.schema_owner) = :2)

Oracle: 10gR2,
SO: AIX 5.2
PLSQLdev:Version 6.0.6.947 (MBCS)

thanks,
Silvio Antunes

 

[Marco Kalter]

This query is not directly executed by PL/SQL Developer. It may be an internal Oracle query.

 

[David M]

This is not an Oracle generated query, we believe this is generate by PL/SQL developer.
We changed the user connection method to 'Shared Server' from 'Dedicated', however PL/SQL Developer still uses a 'Dedicated' connection (which is OK).Since this change we are getting the following SQL, which shows as a top session - We have to kill the process in order to end the session.

"select count(*) from user$ u, xdb.xdb$schema s where u.user# = :1 and u.name = s.xmldata.schema_owner and (xdb.xdb$Extname2Intname(s.xmldata.schema_url, s.xmldata.schema_owner) = :2)"

 

[Joachim Rupik]

Hello,

Please look into catxdbv.sql (in $ORACLE_HOME/RDBMS). The function is_schema_present
contains the following lines:

/.../

BEGIN

sel_stmt := ' select count(*) ' ||
' from user$ u, xdb.xdb$schema s ' ||
' where u.user# = :1 ' ||
' and u.name = s.xmldata.schema_owner ' ||
' and (xdb.xdb$Extname2Intname(s.xmldata.schema_url, s.xmldata.schema_owner) = :2)';

EXECUTE IMMEDIATE sel_stmt INTO tmp_num USING userno, objname;

/.../

It looks exactly like query mentioned by you.

They have placed also a comment before package body xml_schema_name_present create statement:

Rem ALL_OBJECTS depends on xml_schema_name_present .

When you look into ALL_OBJECTS definition you can find that for XML schema objects xml_schema_name_present.is_schema_present function is called.

Conclusion: if you do not like to see that query in sql cache do not query ALL_OBJECTS view anymore.