Database Reference
In-Depth Information
Figure 8-9.
In searching for preferences, table settings take precedence over global preferences
To get the global preferences without executing the
get_param
function several times, as just described in the
“The Legacy Way” section, it's possible to query the internal data dictionary table
optstat_hist_control$
. To get
preferences for tables, you can also run the following query. Notice that even if in the preceding PL/SQL block the
configuration was performed at the schema level, the
dba_tab_stat_prefs
view shows the setting:
SQL> SELECT table_name, preference_name, preference_value
2 FROM dba_tab_stat_prefs
3 WHERE owner = 'SCOTT'
4 AND table_name IN ('EMP', 'DEPT')
5 ORDER BY table_name, preference_name;
TABLE_NAME PREFERENCE_NAME PREFERENCE_VALUE
---------- --------------- ----------------
DEPT CASCADE FALSE
EMP CASCADE TRUE
To get rid of preferences, the
dbms_stats
package provides the following procedures:
•
reset_global_pref_defaults
resets the global preferences to the default values.
•
delete_database_prefs
deletes preferences at the database level.
•
delete_schema_prefs
deletes preferences at the schema level.
delete_table_prefs
deletes preferences at the table level.
The following call shows how to delete the preferences related to the
cascade
parameter for all tables currently
contained in the
scott
schema:
•
dbms_stats.delete_schema_prefs(ownname => 'SCOTT', pname => 'CASCADE')
To execute the procedures at the global and database levels, you need to have both the system privileges
analyze
any dictionary
and
analyze any
. To execute the procedures at a schema or table level, you need to be connected as
owner or have the system privilege
analyze any
.
Working with Global Temporary Tables
Up to and including version 11.2, for the global temporary tables, the
dbms_stats
package provides only the
gather_temp
parameter of the
gather_database stats
and
gather_schema stats
procedures. With that parameter, you can only