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
 
Search WWH ::




Custom Search