统计信息的管理(二)导出导入统计信息

1。schema统计信息的导出

DBMS_STATS.EXPORT_SCHEMA_STATS (
   ownname VARCHAR2,
   stattab VARCHAR2,
   statid  VARCHAR2 DEFAULT NULL,
   statown VARCHAR2 DEFAULT NULL);

SQL> BEGIN                              
   DBMS_STATS.CREATE_STAT_TABLE ('scott', 'scott_stats');  2 
  3  end;
  4  /
 
PL/SQL procedure successfully completed.
 
SQL> exec dbms_stats.export_schema_stats('scott','scott_stats');
 
PL/SQL procedure successfully completed.

2.schema统计信息的导入。

DBMS_STATS.IMPORT_SCHEMA_STATS (
   ownname       VARCHAR2,
   stattab       VARCHAR2,
   statid        VARCHAR2 DEFAULT NULL,
   statown       VARCHAR2 DEFAULT NULL,
   no_invalidate BOOLEAN DEFAULTto_no_invalidate_type(
                                    get_param('NO_INVALIDATE')),
   force         BOOLEAN DEFAULT FALSE);

（1）先删除原来的统计信息

SQL> exec DBMS_STATS.DELETE_SCHEMA_STATS ('scott');
 
PL/SQL procedure successfully completed.
 
SQL> select table_name,last_analyzed from dba_tables where wner='SCOTT';
 
TABLE_NAME                     LAST_ANAL
------------------------------ ---------
DEPT
EMP
BONUS
SALGRADE
PLAN_TABLE
T2
T1
SCOTT_STATS
T_PARTITION_LIST
TEST
 
10 rows selected.

(2)导入保存在表scott_stats里的统计信息。

SQL> exec DBMS_STATS.IMPORT_SCHEMA_STATS('scott',stattab=>'scott_stats');
 
PL/SQL procedure successfully completed.
 
SQL> select table_name,last_analyzed from dba_tables where wner='SCOTT';
 
TABLE_NAME                     LAST_ANAL
------------------------------ ---------
DEPT                           19-JAN-09
EMP                            19-JAN-09
BONUS                          19-JAN-09
SALGRADE                       19-JAN-09
PLAN_TABLE                     19-JAN-09
T2                             19-JAN-09
T1                             19-JAN-09
SCOTT_STATS
T_PARTITION_LIST               19-JAN-09
TEST                           05-JAN-09
 
10 rows selected.
 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10972173/viewspace-545174/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10972173/viewspace-545174/