Oracle笔记: Statspack安装使用

    俗话说，工欲善其事，必先利其器。做数据库性能分析，也要有一款好的工具。statspack就是oracle自带的一个强大并且免费的性能分析工具。
    安装statspack需要用户具有sysdba的权限。首先以sysdba登陆，为statspack创建一个单独的tablespace。
    Sql代码
    1.SQL> create tablespace perfstat datafile 'D:\oracle\oradata\epcit\data_file\PERFSTAT.DBF' size 2G;
    2.
    3.Tablespace created.
    SQL> create tablespace perfstat datafile 'D:\oracle\oradata\epcit\data_file\PERFSTAT.DBF' size 2G;
    Tablespace created.
    然后运行spcreate.sql。安装 statspack所需要的sql文件都位于%oracle_home%\RDBMS\ADMIN目录下。为了使用方便，可以把这个路径加入到环境变量 sqlpath中（set sqlpath=%oracle_home\RDBMS\ADMIN）。
    Sql代码
    1.SQL> @spcreate
    2.
    3.Choose the PERFSTAT user's password
    4.-----------------------------------
    5.Not specifying a password will result in the installation FAILING
    6.
    7.Enter value for perfstat_password: password
    8.
    9.Enter value for default_tablespace: perfstat
    10.Using tablespace PERFSTAT as PERFSTAT default tablespace.
    11.
    12.Enter value for temporary_tablespace: temp
    13.Using tablespace temp as PERFSTAT temporary tablespace.
    14.
    15… Creating PERFSTAT user
    16… Installing required packages
    17… Creating views
    18… Granting privileges
    19.
    20.NOTE:
    21.SPCUSR complete. Please check spcusr.lis for any errors.
    22.
    23.SQL> --  Build the tables and synonyms
    24.SQL> connect perfstat/&&perfstat_password
    25.Connected.
    26.SQL> @@spctab
    27.
    28.Using perfstat tablespace to store Statspack objects
    29.
    30… Creating STATS$SNAPSHOT_ID Sequence
    31… Creating STATS$… tables
    32.
    33.NOTE:
    34.SPCTAB complete. Please check spctab.lis for any errors.
    35.
    36.SQL> --  Create the statistics Package
    37.SQL> @@spcpkg
    38.
    39.Creating Package STATSPACK…
    40.Package created.
    41.
    42.Creating Package Body STATSPACK…
    43.Package body created.
    44.
    45.NOTE:
    46.SPCPKG complete. Please check spcpkg.lis for any errors.
    SQL> @spcreate
    Choose the PERFSTAT user's password
    -----------------------------------
    Not specifying a password will result in the installation FAILING
    Enter value for perfstat_password: password
    Enter value for default_tablespace: perfstat
    Using tablespace PERFSTAT as PERFSTAT default tablespace.
    Enter value for temporary_tablespace: temp
    Using tablespace temp as PERFSTAT temporary tablespace.
    … Creating PERFSTAT user
    … Installing required packages
    … Creating views
    … Granting privileges
    NOTE:
    SPCUSR complete. Please check spcusr.lis for any errors.
    SQL> --  Build the tables and synonyms
    SQL> connect perfstat/&&perfstat_password
    Connected.
    SQL> @@spctab
    Using perfstat tablespace to store Statspack objects
    … Creating STATS$SNAPSHOT_ID Sequence
    … Creating STATS$… tables
    NOTE:
    SPCTAB complete. Please check spctab.lis for any errors.
    SQL> --  Create the statistics Package
    SQL> @@spcpkg
    Creating Package STATSPACK…
    Package created.
    Creating Package Body STATSPACK…
    Package body created.
    NOTE:
    SPCPKG complete. Please check spcpkg.lis for any errors.
    安装过程中statspack会提示输入用户密码，默认表空间和临时表空间。如果想在silent mode下安装，则可以事先设置这些变量。
    Sql代码
    1.SQL> define perfstat_password='password'
    2.SQL> define default_tablespace='perfstat'
    3.SQL> define temporary_tablespace='temp'
    SQL> define perfstat_password='password'
    SQL> define default_tablespace='perfstat'
    SQL> define temporary_tablespace='temp'
    安装完成了。