Oracle Database

We can transfer statistics between servers allowing consistent execution plans between servers with varying amounts of data. This is possible using the DBMS_STATS package Here are the steps to do for a table Step 1. Create the stat table EXEC DBMS_STATS.CREATE_STAT_TABLE(‘SCOTT,’STATTAB’, ‘TOOLS’); Step 2 Export table statistics to statistics table. EXEC DBMS_STATS.EXPORT_TABLE_STATS(OWNNAME=>’SCOTT’,TABNAME=>’EMP’, STATTAB=>’STATTAB’,STATID=>’EXP_1′,STATOWN=>’SCOTT); Step 3 …

How to transfer statistics between databases in Oracle Read More »

We can import statistics from the user statistics table identified by stattab and stored in the data dictionary. Basically, you can import the statistics which you exported sometime before to the use statistics table. To import statistics in Oracle, you can use the DBMS_STATS package. This package provides several procedures and functions for managing database statistics. …

How to import statistics in Oracle Read More »

To export statistics in Oracle, you can use the DBMS_STATS package. This package provides several procedures and functions for managing database statistics. Here are the basic steps: (1) Connect to your database as a user with the necessary privileges (such as SYS or SYSTEM). (2) create a stat table to hold the statistics EXEC DBMS_STATS.CREATE_STAT_TABLE(‘SCOTT’,’STATTAB’); …

How to export statistics in Oracle Read More »

Sometimes you want to drop the histogram of a particular table and dont want to generate a histogram for a particular column in the table. let’s see how we can achieve it 11G and the above solution First of all, you can delete the existing histograms using the below command BEGIN dbms_stats.delete_column_stats( ownname=>’SCOTT’, tabname=>’TEST’, colname=>’UNIQ_ID’, …

How do I drop the histogram on a column and prevent to generate in future Read More »

Suppose you want to remove the optimizer preference you set on the schema, table, or database, then the below procedure can be used to do that How to delete table level Optimizer preference The DBMS_STATS.DELETE_TABLE_PREFS procedure is used to delete the statistics preferences of the specified table in the specified schema. Example exec DBMS_STATS.DELETE_TABLE_PREFS ( …

How to delete Optimizer preference Read More »