Statistics on Dictionary tables fails

Toad for Oracle
1

When trying to take statistics on Data Dictionary Options it fails with ORA-01756 - quoted string not properly terminated .. This was always a bug in all Toad Versions .. Currently on 13.1.0.78

Please fix..

2

On the same version, I go to Database->Optimize->"Analyze All Objects". I then go to the "Schemas and Database" tab, and choose "Data Dictionary". Before clicking the green "Go" (play) button, I go to the menu and choose "Database"->"Spool SQL"->"Spool SQL to Screen". When I hit the Go button, it collects the stats and dumps this SQL to my screen:

----------------------------------
-- Session: ME@MYDB
-- Timestamp: 08:18:57.712
BEGIN
  SYS.DBMS_STATS.GATHER_DICTIONARY_STATS (
     Granularity       => 'DEFAULT'
    ,Options           => 'GATHER STALE'
    ,Estimate_Percent  => NULL
    ,Method_Opt        => 'FOR ALL COLUMNS SIZE 1'
    ,Degree            => 4
    ,Cascade           => TRUE
    ,No_Invalidate  => FALSE);
END;

If you enable the Spool SQL to Screen, what does it show for you?

Thanks,
Rich

1 Like
3

I see the problem if the "Use DBMS_Stats.Get_Prefs" option is selected.

4

Hi,

I get the following :

-- Session: SYS@AVDBMIGPPSRV
-- Timestamp: 16:41:10.518
DECLARE
SQLText Varchar2(4000);
DegreeValue Varchar2(64);
EstimatePercentValue Varchar2(64);
MethodOptValue Varchar2(64);
BEGIN
DegreeValue := SYS.DBMS_STATS.GET_PREFS('DEGREE'');
EstimatePercentValue := SYS.DBMS_STATS.GET_PREFS('ESTIMATE_PERCENT'');
MethodOptValue := '''' || SYS.DBMS_STATS.GET_PREFS('METHOD_OPT'') || '''';
SQLText := 'BEGIN' || CHR(10) ||
' SYS.DBMS_STATS.GATHER_DICTIONARY_STATS (' || CHR(10) ||
' Granularity => ''DEFAULT''' || CHR(10) ||
' ,Options => ''GATHER STALE''' || CHR(10) ||
' ,Estimate_Percent => ' || EstimatePercentValue || CHR(10) ||
' ,Method_opt => ' || MethodOptValue || CHR(10) ||
' ,Degree => ' || DegreeValue || CHR(10) ||
' ,Cascade => TRUE' || CHR(10) ||
' ,No_Invalidate => FALSE);' || CHR(10) ||
'END;';
execute immediate(SQLText);
END;
Error: ORA-01756: quoted string not properly terminated

5

Hi

You got a point there.. if I unclick the option "Use DBMS_Stats.Get_Prefs" from all the options then it works :slight_smile:

6

Fixed for next beta.

1 Like