oracle dbms常用方法,DBMS_STATS常用方法（收集oracle信息）

–收集数据库信息

EXEC DBMS_STATS.gather_database_stats;

EXEC DBMS_STATS.gather_database_stats(estimate_percent => 15);

–收集schema信息

EXEC DBMS_STATS.gather_schema_stats(‘SCOTT’);

EXEC DBMS_STATS.gather_schema_stats(‘SCOTT’, estimate_percent => 15);

–收集表信息

EXEC DBMS_STATS.gather_table_stats(‘SCOTT’, ‘EMPLOYEES’);

EXEC DBMS_STATS.gather_table_stats(‘SCOTT’, ‘EMPLOYEES’, estimate_percent => 15);

–收集index信息

EXEC DBMS_STATS.gather_index_stats(‘SCOTT’, ‘EMPLOYEES_PK’);

EXEC DBMS_STATS.gather_index_stats(‘SCOTT’, ‘EMPLOYEES_PK’, estimate_percent => 15);

–删除收集信息

EXEC DBMS_STATS.delete_database_stats;

EXEC DBMS_STATS.delete_schema_stats(‘SCOTT’);

EXEC DBMS_STATS.delete_table_stats(‘SCOTT’, ‘EMPLOYEES’);

EXEC DBMS_STATS.delete_index_stats(‘SCOTT’, ‘EMPLOYEES_PK’);

–创建备份收集信息表

begin

dbms_stats.create_stat_table(USER,stattab => ‘STAT_TABLE’);

end;

–备份收集信息

BEGIN

dbms_stats.export_table_stats(USER,tabname => ‘FEI_T’,stattab => ‘STAT_TABLE’);

END;

–删除收集信息

BEGIN

DBMS_STATS.delete_table_stats(USER,tabname => ‘FEI_T’);

END;

–导入收集信息

BEGIN

dbms_stats.IMPORT_TABLE_STATS(USER,’FEI_T’,stattab => ‘STAT_TABLE’);

END;

–说明：

当前用户可以使用user代替用户名

分析表相关对象信息cascade => true