Views | Script hangs or becomes blank

When I try to display the script behind a view - i.e., click on the “Views | Script” tab, Toad would keep spinning. If I click on another tab, like “Data,” it would display the data without any issue. But when I click back to the “Script” tab, it would be blank. This only happens recently. What would be the cause? And any way to fix this? Thanks!

We’re getting the same thing except with roles only in 12.7.1.11 64bit talking to an 11.2.0.4 database. Everything is fine looking at a role until I hit the script tab. Then it spins up a CPU and won’t come back.

I had something similar happen a few versions ago and I think the problem was that it wasn’t detecting 11gr2 vs 12c correctly and trying to run 12c code instead of the 11gr2

Is this any role or a specific role?

when you say the CPU goes up and won’t come back, do you mean that the CPU never settles down or that it does settle down but the script is still blank?

If you turn on spool SQL (Main Menu -> Database -> Spool SQL -> Spool to Screen) you can see what queries we are executing and if they have any errors.

Any role. Looking at spotlight, the query grabs a CPU and doesn’t let go, last SQL in the detail box is unhelpful. Here’s the last thing in the spool file:

Session: BANINST1@PROD

Timestamp: 10:07:27.225

BEGIN

SYS.DBMS_APPLICATION_INFO.SET_MODULE(‘TOAD 12.7.1.11’, null);

END;

That doesn’t make sense to me.

This is the last query sent, and it seemed to complete just fine:


Session: BANINST1@PROD

Timestamp: 10:06:42.466

Select o.object_type, pp.*

from dba_objects o,

(Select p.grantee, p.owner, p.table_name, null column_name, p.privilege, p.grantable

from dba_tab_privs p, dba_roles r

where p.GRANTEE = r.role

and P.GRANTEE = :OneObjectName

UNION

Select p.grantee, p.owner, p.table_name, p.column_name, p.privilege, p.grantable

from dba_col_privs p, dba_roles r

where p.GRANTEE = r.role

and P.GRANTEE = :OneObjectName

) pp

where o.OWNER = pp.owner

and o.object_name = pp.table_name

and o.object_type in (‘TABLE’, ‘VIEW’, ‘SEQUENCE’, ‘PACKAGE’, ‘PROCEDURE’, ‘FUNCTION’, ‘SNAPSHOT’, ‘MATERIALIZED VIEW’, ‘DIRECTORY’, ‘LIBRARY’, ‘TYPE’, ‘OPERATOR’, ‘INDEXTYPE’, ‘EDITION’, ‘JOB’, ‘PROGRAM’, ‘SCHEDULE’)

order by pp.grantee, pp.owner, pp.table_name, pp.column_name

:OneObjectName(VARCHAR[9],IN)=‘DBFS_ROLE’

That’s the query we use to determine object privileges that were granted to the role. If it shows up in the spool sql output, then execution of the query has finished, but all rows may or may not have been fetched.

So it’s the CPU on the server that’s shooting up? At first, I thought you meant on the client. If it’s the server CPU that is going up, it seems like Oracle is having to do a lot of work execute the query and complete the fetch requests.

If you try that query in the editor (setting the value for the variable), does it run quickly and can you scroll down to fetch all rows?

Yes, the CPU spike is on the server, and no, that code is not finishing, 3 minutes and counting, no rows returned yet. I re-gathered fixed object stats just in case something got out of hand.

Try re-gathering data dictionary stats. That’s what we’re querying against here. You may want different options, but the script should look something like this:

BEGIN

SYS.DBMS_STATS.GATHER_DICTIONARY_STATS (

Granularity => ‘DEFAULT’

,Options => ‘GATHER’

,Estimate_Percent => SYS.DBMS_STATS.AUTO_SAMPLE_SIZE

,Method_Opt => ‘FOR ALL COLUMNS SIZE 1’

,Degree => SYS.DBMS_STATS.DEFAULT_DEGREE

,Cascade => FALSE

,No_Invalidate => FALSE);

END;

/

That would have been cool if it would have worked. I did run the query through the Oracle tuning advisor (OEM) and accepted a plan that works. I don’t know what setting would cause the optimizer not to be able to choose the correct plan, but it works now.

It doesn’t always work, but I think it’s a required step when this kind of thing happens. Glad you got it working. Oracle Tuning Advisor was the next thing I was going to suggest. :slight_smile:

I think I’m running into a problem with vendor supplied undocumented parameters required for their application because of some specific bugs. Some of these are quite old, so I’m checking to see if they can be safely removed now. Thanks for the help!