Toad World® Forums

DBMS_STATS Package in Toad Data Point?

I am relatively new to the Toad Data Point software package. I am more used to Toad for Oracle Developers.

The question I had is actually simple…can Toad Data Point execute DBMS_STAT packages?

i.e. GATHER_TABLE_STATS, etc…

I need this as a function of my job and if I can’t perform this task using TDP…I might have to switch back. Any help would be appreicated.

We don’t have a right click action off of a table to update the stats. But if you have the privileges to execute the DBMS_STAT package then you can do this in the SQL Editor.

What would be the syntax for running DBMS_STATS ?

This command works in Oracle SQL Developer but in Toad returns Invalid SQL Statement.

exec dbms_stats.gather_table_stats(ownname=>‘HM_PROD’,tabname=>‘p100812_po_stmt’,estimate_percent=>‘10’);

I’ve run into this before - the issue is that EXEC is a short-cut in SQL*Plus as well as other tools that mimic it’s functionality, and it actually wraps your statement in BEGIN / END;

However, Toad Data Point doesn’t. Go figure.

To run your statement, change it to:

BEGIN

dbms_stats.gather_table_stats(ownname=>‘HM_PROD’,tabname=>‘p100812_po_stmt’,estimate_percent=>‘10’);

END;

THANKS! I really didn’t expect a reply from such an old thread. I’ll give it a spin.

John Bentley

-----Original Message-----

From: N.B. [mailto:bounce-NB@toadworld.com]

Sent: Thursday, August 18, 2016 7:21 PM

To: toaddatapoint@toadworld.com

Subject: RE: [Toad Data Point - Discussion Forum] DBMS_STATS Package in Toad Data Point?

I’ve run into this before - the issue is that EXEC is a short-cut in SQL*Plus as well as other tools that mimic it’s functionality, and it actually wraps your statement in BEGIN / END; However, Toad Data Point doesn’t. Go figure.

To run your statement, change it to:

BEGIN

dbms_stats.gather_table_stats(ownname=>‘HM_PROD’,tabname=>‘p100812_po_stmt’,estimate_percent=>‘10’);

END;

View this message online or reply to this message (www.toadworld.com/…/62181.)

Toad Data Point - Discussion Forum