Oracle SQL

SQL> exec dbms_stats.delete_schema_stats('HR');

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.31
SQL> begin
  2    for r in (select table_name
  3              from dba_tables t
  4              where t.owner='XTENDER'
  5              and table_name like 'TMP%'
  6    )loop
  7       dbms_stats.delete_table_stats('XTENDER',r.TABLE_NAME);
  8     end loop;
  9  end;
 10  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.31
SQL> select
  2    owner
  3   ,table_name
  4   ,num_rows
  5   ,last_analyzed
  6  from dba_tab_statistics t
  7  where owner='XTENDER' and table_name like 'TMP%'
  8        or owner='HR';

OWNER                          TABLE_NAME                       NUM_ROWS LAST_ANALYZED
------------------------------ ------------------------------ ---------- -------------------
HR                             COUNTRIES
HR                             DEPARTMENTS
HR                             EMPLOYEES
HR                             JOBS
HR                             JOB_HISTORY
HR                             LOCATIONS
HR                             REGIONS
XTENDER                        TMP
XTENDER                        TMP1
XTENDER                        TMP_CONTENT
XTENDER                        TMP_DATA
XTENDER                        TMP_DOCUMENT
XTENDER                        TMP_EXCH

13 rows selected.

Elapsed: 00:00:00.11
SQL> col dt new_value dt
SQL> select sysdate dt from dual;

DT
-------------------
2013-01-27 00:30:21

1 row selected.

Elapsed: 00:00:00.00
SQL> DECLARE
  2        filter_lst  DBMS_STATS.OBJECTTAB := DBMS_STATS.OBJECTTAB();
  3      BEGIN
  4        filter_lst.extend(2);
  5        filter_lst(1).ownname := 'XTENDER';
  6        filter_lst(1).objname := 'TMP%';
  7        filter_lst(2).ownname := 'HR';
  8        DBMS_STATS.GATHER_SCHEMA_STATS(NULL, obj_filter_list => filter_lst,
  9                                       options => 'gather');
 10      END;
 11  /

PL/SQL procedure successfully completed.

Elapsed: 00:03:16.89
SQL> select owner,table_name
  2  from dba_tables t
  3  where t.LAST_ANALYZED>timestamp'&dt'+0;

OWNER                          TABLE_NAME
------------------------------ ------------------------------
XTENDER                        TT
XTENDER                        TR_LOG
XTENDER                        IOT1
...[skipped 171 rows]...
XTENDER                        DEPARTMENTS

175 rows selected.

Elapsed: 00:00:01.04