10g freezes PL/SQL Developer

[Shane Maitland]

Just wondering if anyone else has had this problem.

I'm using PL/SQL Developer 6.0.4.906 on an XP workstation with 8.1.7 client accessing a 10g database. Everything seems to work fine except if I click on the browser to open a list of Tables, Packages, etc. When I do this, developer seems to freeze (it does open after about 10 - 15 minutes). Works fine if I connect to a 8 database though.

 

[Marco Kalter]

This indicates a problem with the dictionary views (specifically sys.all_objects). Perhaps you can verify the where clause and order by clause of your current filter, and check what happens if you perform the resulting query on sys.all_objects? For example:

select o.owner, o.object_name
from sys.all_objects o
where o.object_type = 'PACKAGE'
order by o.owner, o.object_name

 

[webdba]

I am having the same problem against a 10g DB.

I can run the suggested query from SQL*PLUS with no problems.

If I run the query without the "where" and "order by" clause it works fine.

If I run the query with the where clause it runs fine for about 600 records and then hangs and if I break out of it and display the error stack it displays the code below.

It seems to be hanging up on a specific schema's security priv's.

This is just a guess so please don't assume I know what I am talking about.

This could also be related to the version of the OCI.DLL

---------------------------------------------

create or replace function xdb.xdb$ExtName2IntName
(schemaURL IN VARCHAR2, schemaOwner IN VARCHAR2 := '')
return varchar2 authid current_user deterministic
is external name "EXT2INT_NAME" library XMLSCHEMA_LIB with context
parameters (context, schemaURL OCIString, schemaOwner OCIString,
return INDICATOR sb4, return OCIString);

-----------------------------

create or replace package body sys.xml_schema_name_present as

function is_schema_present(objname in varchar2,
userno in number) return number as

sel_stmt VARCHAR2(4000);
tmp_num NUMBER;

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;

/* schema found */
IF (tmp_num > 0) THEN
RETURN 1;
END IF;

sel_stmt := ' select count(*) '||
' from xdb.xdb$schema s ' ||
' where bitand(to_number(s.xmldata.flags, ''xxxxxxxx''), 16) = 16 ' ||
' and xdb.xdb$Extname2Intname(s.xmldata.schema_url,s.xmldata.schema_owner) = :1 ' ||
' and s.xmldata.schema_url ' ||
' not in (select s2.xmldata.schema_url ' ||
' from xdb.xdb$schema s2, user$ u2 ' ||
' where u2.user# = :2 ' ||
' and u2.name = s.xmldata.schema_owner) ';

EXECUTE IMMEDIATE sel_stmt INTO tmp_num USING objname, userno;

/* schema found */
IF (tmp_num > 0) THEN
RETURN 1;
END IF;

RETURN 0;
END;

end xml_schema_name_present;

 

[webdba]

I just did a bit more poking around and found that it definitly relates to the priv's on a schema.

It seems to throw out a boat anchor when it hits the XDB schema in our 10g installation.

If I alter the suggested query and then exclude the XDB schema everything works just fine.

Here was the altered query that ran in 2.4 seconds vs the 790 seconds when the XDB schema was not excluded.

--------------------------------

select o.owner, o.object_name
from sys.all_objects o
where o.object_type = 'PACKAGE'
and o.owner 'XDB'
order by o.owner, o.object_name

 

[webdba]

More poking around.... I added the filter:

owner 'XDB' to the Browser window and all problems with hanging up went away for Tables, Views, Procedures.