Friday, April 13, 2012

DBMS_UTILITY.ANALYZE_SCHEMA

I read about dbms_utility recently and decided to give it a try. According to the documentation for Oracle 10.2, the analyze_schema procedure is obsolete (it says you should use dbms_stats.gather_schema_stats instead, which seems reasonable). However, when I tried dbms_utility.analyze_schema on that version, it seemed to work OK. I will try to look at some other dbms_utility subprograms in future posts:

SQL> create user andrew
  2  identified by reid
  3  default tablespace users
  4  quota unlimited on users
  5  /
 
User created.
 
SQL> grant create session, create table to andrew
  2  /
 
Grant succeeded.
 
SQL> conn andrew/reid
Connected.
SQL> create table table_list as
  2  select * from all_tables
  3  /
 
Table created.
 
SQL> select last_analyzed from user_tables
  2  where table_name = 'TABLE_LIST'
  3  /
 
LAST_ANAL
---------
 
 
SQL> exec dbms_utility.analyze_schema('ANDREW', 'ESTIMATE');
 
PL/SQL procedure successfully completed.
 
SQL> select last_analyzed from user_tables
  2  where table_name = 'TABLE_LIST'
  3  /
 
LAST_ANAL
---------
11-APR-12
 
SQL>

No comments: