Exclude table from gather stats job in Oracle

Suppose we did not want to include any table or index from the gather schema stats job.Some table are very big in size or archive tables we did not used in daily activity.So, Oracle does not provide anything to pass in exclude list as parameter in DBMS_STATS.GATHER_SCHEMA_STATS Package.
Note: don’t use FORCE=TRUE option in parameter it forcefully analyzed the stats.

Solution
We have two ways:
1. Lock the table stats for excluded from list of gather stats job.
2. Change the PUBLISH parameter for table to FALSE value with SET_TABLE_PREFS

1. Method: Lock the Table Example
We need to lock the table manually before going to execute the gather stats package. So it skip the locked object.

Following are the steps of example:
1. We login to TEST schema with credentials in SQLPLUS and get the list of all object present in TEST schema.

SQL> SELECT table_name,to_char(last_analyzed,'DD-MON-YYYY HH24:MI:SS') "LASTANALYZED" from user_tables;

TABLE_NAME      LASTANALYZED
--------------- ---------------
TESTPRIMARY
TESTING
TESTFOREIGN
PRIMARY_TEST
FOREIGN_TEST
TEST1

2. Skip the first two table from gather schema stats job.

SQL> exec dbms_stats.lock_table_stats('TEST','TESTPRIMARY');
PL/SQL procedure successfully completed.

SQL> exec dbms_stats.lock_table_stats('TEST','TESTING');
PL/SQL procedure successfully completed.


3. Now run the gather schema stats jobs for TEST schema.

SQL> exec dbms_stats.gather_schema_stats( ownname => 'TEST');
PL/SQL procedure successfully completed.


4. Check the last analyzed tables.

SQL> SELECT table_name,to_char(last_analyzed,'DD-MON-YYYY HH24:MI:SS') "LASTANALYZED" from user_tables;

TABLE_NAME      LASTANALYZED
--------------- -----------------------------
TESTPRIMARY
TESTING
TESTFOREIGN     04-MAR-2020 11:09:11
PRIMARY_TEST    04-MAR-2020 11:09:11
FOREIGN_TEST    04-MAR-2020 11:09:11
TEST1           04-MAR-2020 11:09:11

5. Check table locked with following command.

--If ALL returen in STATTYPE colum then its locked , If NULL the unlocked
select table_name,stattype_locked from user_tab_statistics


Note: First of all unlock the table for second Method test.

SQL> exec dbms_stats.unlock_table_stats('TEST','TESTPRIMARY');
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.unlock_table_stats('TEST','TESTING');
PL/SQL procedure successfully completed.


2. Method: SET_TABLE_PREFS Procedure to use PUBLISH parameter as FALSE
We can set the PUBLISH parameter as FALSE value will skip the gather stats job to PUBLISH latest gather stats in Dictionary.

1. Check the PUBLISH parameter for TABLE_NAME.

--DBMS_STATS.get_prefs('parameter','schema_name', 'table_name' )

SQL> SELECT DBMS_STATS.get_prefs('PUBLISH','TEST', 'TESTING' ) FROM dual;
DBMS_STATS.GET_PREFS('PUBLISH','TEST','TESTING')
-------------------------------------------------
TRUE


2. Change the PUBLISH to FALSE.

--EXEC dbms_stats.set_table_prefs('Schema_name', 'table_name', 'parameter', 'false');

SQL> exec dbms_stats.set_table_prefs('TEST', 'TESTING', 'PUBLISH', 'false');
PL/SQL procedure successfully completed.


3. Gather stats for testing.

SQL> exec dbms_stats.gather_schema_stats( ownname => 'TEST');
PL/SQL procedure successfully completed.


4. Check the stats for tables left.

SQL> SELECT table_name,to_char(last_analyzed,'DD-MON-YYYY HH24:MI:SS') "LASTANALYZED" from user_tables;

TABLE_NAME      LASTANALYZED
--------------- -----------------------------
TESTPRIMARY     04-MAR-2020 11:20:31
TESTING
TESTFOREIGN     04-MAR-2020 11:20:30
PRIMARY_TEST    04-MAR-2020 11:20:30
FOREIGN_TEST    04-MAR-2020 11:20:30
TEST1           04-MAR-2020 11:20:30