Database Reference
In-Depth Information
set_schema_prefs
sets the preferences for a specific schema.
•
set_table_prefs
sets the preferences for a specific table.
Note that the parameters
autostats_target
and
concurrent
can only be modified with the
set_global_prefs
procedure.
•
■
The procedures
set_database_prefs
and
set_schema_prefs
don't directly store preferences in the
data dictionary. instead, they're converted into table preferences for all objects
presently
available in the database or
schema at the time the procedure is called. in other words, only global and table preferences exist. The procedures
set_database_prefs
and
set_schema_prefs
are simple wrappers around the
set_table_prefs
procedure. This means
that global preferences will be used for new tables created after these two procedures have been called.
Caution
The following PL/SQL blocks show how to set different values for the
cascade
parameter. Note that
pname
is the
parameter name,
pvalue
is the parameter value,
ownname
is the owner, and
tabname
is the table name. Once again,
be careful because the order of the calls is capital in such a PL/SQL block. In fact, every call overwrites some of the
definition made by the previous call:
BEGIN
dbms_stats.set_database_prefs(pname => 'CASCADE',
pvalue => 'DBMS_STATS.AUTO_CASCADE');
dbms_stats.set_schema_prefs(ownname => 'SCOTT',
pname => 'CASCADE',
pvalue => 'FALSE');
dbms_stats.set_table_prefs(ownname => 'SCOTT',
tabname => 'EMP',
pname => 'CASCADE',
pvalue => 'TRUE');
END;
To get the current setting, the
get_prefs
function, which replaces the
get_param
function, is available. The
following query shows the effect of the setting performed on the previous PL/SQL blocks. Note that
pname
is the
parameter name,
ownname
is the owner name, and
tabname
is the table name. As you can see, depending on which
parameters are specified, the function returns the values at a specific level. This searching for preferences is carried
out as shown in Figure
8-9
:
SQL> SELECT dbms_stats.get_prefs(pname => 'cascade') AS global,
2 dbms_stats.get_prefs(pname => 'cascade',
3 ownname => 'SCOTT',
4 tabname =>'DEPT') AS dept,
5 dbms_stats.get_prefs(pname => 'cascade',
6 ownname => 'SCOTT',
7 tabname =>'EMP') AS emp
8 FROM dual;
GLOBAL DEPT EMP
----------------------- ----- ----
DBMS_STATS.AUTO_CASCADE FALSE TRUE
