How to set Schema level preference setting

Suppose you want to set up method_opt preference for the schema, then the below procedure can be done to set it up

The DBMS_STATS.SET_SCHEMA_PREFS procedure is used to set the statistics preferences of all the tables owned by the specified owner name.

DBMS_STATS.SET_SCHEMA_PREFS (
ownname IN VARCHAR2,
pname IN VARCHAR2,
pvalue IN VARCHAR2)

Example

exec DBMS_STATS.SET_SCHEMA_PREFS('SCOTT', 'method_opt','for all indexed column only');

This procedure will take time depending on the number of tables in the schema. It sets this preference setting at the table level for all the tables

Other Examples

exec DBMS_STATS.SET_SCHEMA_PREFS('SCOTT', 'degree','4');
exec DBMS_STATS.SET_SCHEMA_PREFS('SCOTT', 'estimate_percent','10');

How to check the setting

We can check the setting using the get_prefs procedure. we can give any table name and it will provide the value

SELECT DBMS_STATS.GET_PREFS('STALE_PERCENT', 'SCOTT', 'EMP') FROM   DUAL;

Suppose we want to delete the settings, then the below can be used

exec DBMS_STATS.DELETE_SCHEMA_PREFS('SCOTT', 'degree');

I hope you find this information helpful and useful

Related Articles