Rem
Rem $Header: dbconfig.sql 22-jul-2005.16:48:03 ngade Exp $
Rem
Rem dbconfig.sql
Rem
Rem Copyright (c) 2005, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      dbconfig.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem      <short description of component this file declares/defines>
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    ngade       07/22/05 - ngade_bug-4498671
Rem    ngade       07/20/05  - 
Rem    ngade       02/14/05 -  make statspack optional install.
Rem    ngade       01/06/05 - ngade_db_config_sql
Rem    ngade       01/05/05 - Created
Rem

--  Parameters required:
 --------------------
-- 1. sys user password.
-- 2. dbsnmp password.
-- 3. Monitor username.
-- 4. Perfstat user password.
-- 5. Perfstat tablespace - please check ?/rdbms/admin/spdoc.txt for tablespace requirements.
-- 6. Temporary Tablespace.

-- RAC - For RAC database, run on only one instance.

SET ECHO ON
SET FEEDBACK 1
SET NUMWIDTH 10
SET LINESIZE 80
SET TRIMSPOOL ON
SET TAB OFF
SET PAGESIZE 100
SET SERVEROUTPUT ON

DEFINE dbm_user = 'DBSNMP';
DEFINE cinterval = 1
DEFINE snap_level = 6
DEFINE nothing_sql = '?/rdbms/admin/nothing.sql';

set serveroutput on
set verify off
define basic_install = &2
define dbm_password = &3
define dbm_passwowrd = &dbm_password
define perfstat_password = &4
define default_tablespace = &5
define temporary_tablespace = &6
define dbmon_sql = &7
define spset_sql= &8
define response_plb= &9


connect &1
grant connect to DBSNMP identified by &dbm_password;

declare
  sqlcmd varchar2(200);
begin
  sqlcmd := 'drop table dbconfig_table';
  execute immediate sqlcmd ;
  exception when others then null;
end;
/

create table dbconfig_table(db_version varchar2(20), dbsnmp_user int, basic_install int, statspack_installed int, dbm_installed int, install_resp int, statspack_configured int, perfstat_locked int);

declare
  sqlcmd varchar2(200);
  sqloutput varchar2(200);
  basic_install number;
  db_version varchar2(20);
  statspack_installed number;
  dbm_installed number;
  install_resp number;
  statspack_configured number;
  perfstat_locked number;
  dbsnmp_user number;

  function is_db_mounted
  return number is
    sqloutput number := 0;
    sqlcmd varchar2(200);
    begin
      sqlcmd := 'select count(1) from v$instance WHERE status=''MOUNTED''';
      execute immediate sqlcmd into sqloutput;
      dbms_output.put_line('db_mounted_count: ' || sqloutput);
      if sqloutput > 0 then
        return 1;
      else
        return 0;
      end if;
      exception when others  then
        dbms_output.put_line('is_db_mounted : exception :-( '|| to_char(sqlcode) || '=>' || sqlerrm);
        return 0;
  end is_db_mounted;

  function check_dbm_installed
    return number is
      sqlcmd varchar2(256);
      sqloutput number := 0;
    begin
     sqlcmd := 'SELECT decode(count(1), ''0'', ''0'', ''1'') FROM sys.dba_objects WHERE object_type = ''PACKAGE BODY'' AND owner = ''DBSNMP''  AND object_name in (''MGMT_RESPONSE'')';
     execute immediate sqlcmd into sqloutput;
     dbms_output.put_line('dbm_installed: ' || sqloutput);
     if sqloutput > 0 then
       return 1;
     else
       return 0;
     end if;
     exception when others  then
       dbms_output.put_line('check_dbm_installed : exception :-( '|| to_char(sqlcode) || '=>' || sqlerrm);
     return 0;
  end check_dbm_installed;

  function check_statspack_installed
    return number is
      sqlcmd varchar2(256);
      sqloutput number := 0;
    begin 
     sqlcmd := 'SELECT 1  FROM all_tables WHERE table_name = ''STATS$SNAPSHOT'' and owner = ''PERFSTAT''';
     execute immediate sqlcmd into sqloutput;
     dbms_output.put_line('statspack_installed: ' || sqloutput);

     if sqloutput > 0 then
       return 1;
     else
       return 0;
     end if;
     exception
       when no_data_found then
         dbms_output.put_line('check_statspack_installed : NO DATA FOUND');
         return 0;
       when others  then
         dbms_output.put_line('check_statspack_installed : exception :-( '|| to_char(sqlcode) || '=>' || sqlerrm);
     return 0;
  end check_statspack_installed;

  function check_statspack_configured
    return number is
      sqlcmd varchar2(256);
      sqloutput number := 0;
    begin
     sqlcmd := 'select snap_level from stats$statspack_parameter where dbid = (select dbid from v$database) and instance_number = (select instance_number from v$instance)';
     execute immediate sqlcmd into sqloutput;
     dbms_output.put_line('statspack_configured: ' || sqloutput);

     if sqloutput <= 6 then
       return 1;
     else
       return 0;
     end if;
     exception
       -- when table_view_does_not_exist then
         -- dbms_output.put_line('check_statspack_configured : Table or View does not exist.');
         -- return 0;
       when others  then
         dbms_output.put_line('check_statspack_configured : exception :-( '|| to_char(sqlcode) || '=>' || sqlerrm);
         return 0;
  end check_statspack_configured;

  function check_perfstat_locked
    return number is
      sqlcmd varchar2(256);
      sqloutput number := 0;
    begin
     sqlcmd := 'select 1 from sys.dba_users where upper(username) = ''PERFSTAT'' and account_status like ''%LOCKED%''';
     execute immediate sqlcmd into sqloutput;
     dbms_output.put_line('perfstat_locked: ' || sqloutput);

     if sqloutput > 0 then
       return 1;
     else
       return 0;
     end if;
     exception
       when no_data_found then
         dbms_output.put_line('check_perfstat_locked : NO DATA FOUND.');
         return 0;
       when others  then
         dbms_output.put_line('check_perfstat_locked : exception :-( '|| to_char(sqlcode) || '=>' || sqlerrm);
     return 0;
  end check_perfstat_locked;

  function check_db_version
    return varchar2 is
      sqlcmd varchar2(256);
      sqloutput varchar2(200);
     begin
       sqloutput := '10.0.0';
       sqlcmd := 'select version from v$instance';
       execute immediate sqlcmd into sqloutput;
       dbms_output.put_line('current db_version: ' || sqloutput);
       return sqloutput;
     exception when others then
       dbms_output.put_line('check_db_version: Cannot get db version :-( '|| to_char(sqlcode) || '=>' || sqlerrm);
       return sqloutput;
  end check_db_version;


  function check_dbsnmp_user
    return number is
      sqlcmd varchar2(256);
      sqloutput number := 0;
    begin
     sqlcmd := 'select count(1) from sys.dba_users where username=''DBSNMP''';
     execute immediate sqlcmd into sqloutput;
     dbms_output.put_line('dbsnmp_user_count: ' || sqloutput);

     if sqloutput > 0 then	
       return 1;
     else
       return 0;
     end if;
     exception
       when no_data_found then
         dbms_output.put_line('check_dbsnmp_user : NO DATA FOUND.');
         return 0;
       when others  then
         dbms_output.put_line('check_dbsnmp_user : exception :-( '|| to_char(sqlcode) || '=>' || sqlerrm);
     return 0;
  end check_dbsnmp_user;

  function validate_parameters 
    return number is
    sqlcmd varchar2(200);
    sqloutput varchar2(200);
    basic_install varchar2(200);
  begin
    sqlcmd := 'SELECT DECODE (upper(''&basic_install''), ''DEF'', ''1'',''&basic_install'') FROM dual';
    execute immediate sqlcmd into basic_install;
    dbms_output.put_line('BASIC INSTALL: ' || basic_install);
    if basic_install = '1' then return 1;
    else
      sqlcmd := 'SELECT DECODE (upper(''&temporary_tablespace''), ''DEF'', ''n'',''&temporary_tablespace'') FROM dual';
      execute immediate sqlcmd into sqloutput;
      if sqloutput = 'n' then 
        raise_application_error( -20001, 'Temporary tablespace cannot be DEFAULT VALUE');
      end if; 
      sqlcmd := 'SELECT DECODE (upper(''&default_tablespace''), ''DEF'', ''n'',''&default_tablespace'') FROM dual';
      execute immediate sqlcmd into sqloutput;
      if sqloutput = 'n' then 
        raise_application_error( -20001, 'PERFSTAT tablespace cannot be DEFAULT VALUE');
      end if; 
      sqlcmd := 'SELECT DECODE (upper(''&perfstat_password''), ''DEF'', ''n'',''&perfstat_password'') FROM dual';
      execute immediate sqlcmd into sqloutput;
      if sqloutput = 'n' then 
        raise_application_error( -20001, 'PERFSTAT password cannot be DEFAULT VALUE');
      end if; 
      return 0;
    end if; 
   end validate_parameters;


    begin
      basic_install := validate_parameters();
      db_version := check_db_version();
      if (basic_install = 1) then
        statspack_installed := 1;
        statspack_configured := 1;
        perfstat_locked := 1;
      else
        statspack_installed := check_statspack_installed();
        perfstat_locked := check_perfstat_locked();
        if(db_version like '1%') then
          statspack_configured := 1;
          statspack_installed := 1;
        else
          statspack_configured := check_statspack_configured();
        end if;
      end if;
      if(db_version like '9%') then
        dbm_installed := check_dbm_installed();
        install_resp := 0;
      else
        install_resp := 1;
        if(db_version like '1%') then
          dbm_installed := 1;
        else
          dbm_installed := 0;
        end if;
      end if;
      dbsnmp_user := check_dbsnmp_user();
      sqlcmd := 'insert into dbconfig_table values('''|| db_version || ''',  '|| dbsnmp_user|| ', ' || basic_install ||', '|| statspack_installed ||', ' ||dbm_installed||', '||install_resp||', '|| statspack_configured ||', '|| perfstat_locked ||')';
      dbms_output.put_line('sqlcmd: ' || sqlcmd);
      execute immediate sqlcmd;
    end;
/
select * from dbconfig_table;

Rem =====================================================================
Rem 1. PRE-CONFIGURE
Rem =====================================================================

SET ECHO ON
SET FEEDBACK 1
SET NUMWIDTH 10
SET LINESIZE 80
SET TRIMSPOOL ON
SET TAB OFF
SET PAGESIZE 100
SET SERVEROUTPUT ON

variable script varchar2(100);
column script new_val SCRIPT_TO_RUN;

declare
    sqlcmd varchar2(200);
    sqloutput varchar2(200);
    db_version varchar2(200);
  begin
    ------------------------
    -- GET DATABASE VERSION
    ------------------------
    :script := '&nothing_sql';
    sqlcmd := 'select db_version from dbconfig_table';
    execute immediate sqlcmd into db_version;
    dbms_output.put_line('-------');
    dbms_output.put_line('-- GETTING DATABASE VERSION ');
    dbms_output.put_line('-------');

    dbms_output.put_line('db_version: ' || db_version);
   
    if(db_version like '9%' or db_version like '8%') then 
      ------------------------
      -- CHECK IF DBSNMP USER EXISTS. ELSE CREATE ONE.
      ------------------------
      dbms_output.put_line('-------');
      dbms_output.put_line('--  CHECKING IF DBSNMP USER EXISTS ');
      dbms_output.put_line('-------');
      sqlcmd := 'select dbsnmp_user from dbconfig_table';
      execute immediate sqlcmd into sqloutput;

      if sqloutput = '0' then
        dbms_output.put_line('User DBSNMP does not exist. Creating DBSNMP user...');
        :script := '?/rdbms/admin/catsnmp.sql';
      else
        dbms_output.put_line('User DBSNMP exists');
      end if;
      ------------------------
      -- GRANT PRIVILEGES
      ------------------------
      if db_version like '9%' then
        dbms_output.put_line('-------');
        dbms_output.put_line('--  GRANTING PRIVILEGES FOR 9i DATABASE');
        dbms_output.put_line('-------');
        sqlcmd := 'GRANT SELECT ANY DICTIONARY, OEM_MONITOR, ANALYZE ANY to &dbm_user';
        execute immediate sqlcmd;
      else 
        dbms_output.put_line('-------');
        dbms_output.put_line('--  GRANTING PRIVILEGES FOR 8i DATABASE');
        dbms_output.put_line('-------');
        sqlcmd := 'GRANT SELECT_CATALOG_ROLE, OEM_MONITOR to &dbm_user';
        execute immediate sqlcmd;

        sqlcmd := 'grant select on sys.ts$ to OEM_MONITOR';
        execute immediate sqlcmd;

        sqlcmd := 'grant select on sys.seg$ to OEM_MONITOR';
        execute immediate sqlcmd;

        sqlcmd := 'grant select on sys.user$ to OEM_MONITOR';
        execute immediate sqlcmd;

        sqlcmd := 'grant select on sys.obj$ to OEM_MONITOR';
        execute immediate sqlcmd;

        sqlcmd := 'grant select on sys.sys_objects to OEM_MONITOR';
        execute immediate sqlcmd;

        sqlcmd := 'grant select on sys.file$ to OEM_MONITOR';
        execute immediate sqlcmd;

        sqlcmd := 'grant select on sys.attrcol$ to OEM_MONITOR';
        execute immediate sqlcmd;

        sqlcmd := 'grant select on sys.clu$ to OEM_MONITOR';
        execute immediate sqlcmd;

        sqlcmd := 'grant select on sys.col$ to OEM_MONITOR';
        execute immediate sqlcmd;

        sqlcmd := 'grant select on sys.ind$ to OEM_MONITOR';
        execute immediate sqlcmd;

        sqlcmd := 'grant select on sys.indpart$ to OEM_MONITOR';
        execute immediate sqlcmd;

        sqlcmd := 'grant select on sys.indsubpart$ to OEM_MONITOR';
        execute immediate sqlcmd;

        sqlcmd := 'grant select on sys.lob$ to OEM_MONITOR';
        execute immediate sqlcmd;

        sqlcmd := 'grant select on sys.lobfrag$ to OEM_MONITOR';
        execute immediate sqlcmd;

        sqlcmd := 'grant select on sys.partobj$ to OEM_MONITOR';
        execute immediate sqlcmd;

        sqlcmd := 'grant select on sys.tab$ to OEM_MONITOR';
        execute immediate sqlcmd;

        sqlcmd := 'grant select on sys.tabpart$ to OEM_MONITOR';
        execute immediate sqlcmd;

        sqlcmd := 'grant select on sys.tabsubpart$ to OEM_MONITOR';
        execute immediate sqlcmd;

        sqlcmd := 'grant select on sys.undo$ to OEM_MONITOR';
        execute immediate sqlcmd;
      end if;

      ------------------------
      -- ALTER JOB QUEUE PROCESS
      ------------------------
      -- Get value of job_queue_processes
      -- set this only if need to install statspack
      sqlcmd := 'select basic_install from dbconfig_table';
      execute immediate sqlcmd into sqloutput;
      if sqloutput = '1' then 
        dbms_output.put_line('-------');
        dbms_output.put_line('--  BASIC INSTALL - SKIP PRE-CONFIGURE ---');
        dbms_output.put_line('-------');
      else
        sqlcmd := 'select statspack_installed from dbconfig_table';
        execute immediate sqlcmd into sqloutput;
        if sqloutput = '0' then
          dbms_output.put_line('-------');
          dbms_output.put_line('--  ALTER JOB QUEUE PROCESS');
          dbms_output.put_line('-------');
          sqlcmd := 'SELECT value FROM SYS.V_$PARAMETER WHERE name = ''job_queue_processes''';
          execute immediate sqlcmd into sqloutput;

          dbms_output.put_line('job_queue_processes: ' || sqloutput);
          -- Modify job_queue_processes
          if sqloutput <= 0 then
            dbms_output.put_line('SETTING VALUE OF job_queue_processes = 2');
            if db_version like '8%' then
               sqloutput := null;
            else
              -- check if using spfile
              sqlcmd := 'select value from v$parameter where name = ''spfile''';
              execute immediate sqlcmd into sqloutput;
              dbms_output.put_line ('spfile: ' || sqloutput);
            end if;
  
            if sqloutput is null then
              sqlcmd := 'alter system set job_queue_processes = 2';
            else
              dbms_output.put_line('*** The job_queue_processes value has been changed in the memory only. If you shut down the database, the value will revert. In order for the new value to persist, you must set job_queue_processes = 2 in your initialization parameter (init.ora) file. *** ');
              sqlcmd := 'alter system set job_queue_processes = 2 SCOPE=BOTH';
            end if;
            execute immediate sqlcmd;
          end if;
          else
            dbms_output.put_line('-------');
            dbms_output.put_line('--  STATSPACK ALREADY INSTALLED. SKIP ALTER JOB QUEUE PROCESS');
            dbms_output.put_line('-------');
        end if;
      end if; -- end of basic_install
      else
      if db_version like '1%' then
        dbms_output.put_line ('Database is greater than or equal to 10g - NEED NOT INSTALLL ANY PACKAGES');
      else
        dbms_output.put_line ('UNRECOGNISED DATABASE VERSION - NOT INSTALLING ANY PACKAGES');
      end if;
    end if;
  end;
/
select :script script from dual;
@@&SCRIPT_TO_RUN

connect &1

SET FEEDBACK 1
SET NUMWIDTH 10
SET LINESIZE 80
SET TRIMSPOOL ON
SET TAB OFF
SET PAGESIZE 100
SET SERVEROUTPUT ON
 
Rem =====================================================================
Rem 2. CONFIGURE DBSNMP USER
Rem =====================================================================

variable script varchar2(100);
column script new_val SCRIPT_TO_RUN;

declare
  sqlcmd varchar2(200);
  db_mounted varchar2(200);
  dbm_installed varchar2(200);
begin
  dbms_output.put_line('-------');
  dbms_output.put_line('--  CONFIGURE DBSNMP USER ');
  dbms_output.put_line('-------');
  :script := '&nothing_sql';

  sqlcmd := 'select dbm_installed from dbconfig_table';
  execute immediate sqlcmd into dbm_installed;
  dbms_output.put_line('dbm_installed: ' || dbm_installed);
  if(dbm_installed = '1') then
    dbms_output.put_line('User DBSNMP already exists.');
  else
    dbms_output.put_line('Configuring DBSNMP user...');
   :script := '&dbmon_sql';
  end if;
end;
/
select :script script from dual;
@@&SCRIPT_TO_RUN

Rem =====================================================================
Rem  3. INSTALL DBSNMP USER
Rem =====================================================================

connect &1

SET FEEDBACK 1
SET NUMWIDTH 10
SET LINESIZE 80
SET TRIMSPOOL ON
SET TAB OFF
SET PAGESIZE 100
SET SERVEROUTPUT ON
 
variable script varchar2(100);
column script new_val SCRIPT_TO_RUN;


declare
  sqlcmd varchar2(200);
  install_resp varchar2(200);
  dbm_installed varchar2(200);

begin
  dbms_output.put_line('-------');
  dbms_output.put_line('--  INSTALL DBSNMP USER ');
  dbms_output.put_line('-------');
  :script := '&nothing_sql';

  sqlcmd := 'select dbm_installed from dbconfig_table';
  execute immediate sqlcmd into dbm_installed;
  dbms_output.put_line('dbm_installed: ' || dbm_installed);
    
  if(dbm_installed = '1') then
    dbms_output.put_line('Database monitor related objects are already installed');
  else
    sqlcmd := 'select install_resp from dbconfig_table';
    execute immediate sqlcmd into install_resp;
    dbms_output.put_line('install_resp: ' || install_resp);
    if(install_resp = '1') then null;
      else
        dbms_output.put_line('Installing Database Monitor related objects...');
        :script := '&response_plb';
    end if;
  end if;
end;
/
connect DBSNMP/&dbm_password
select :script script from dual;
@@&SCRIPT_TO_RUN

-- grant privileges.
connect &1
declare
  sqlcmd varchar2(200);
  install_resp varchar2(200);
begin
    sqlcmd := 'select install_resp from dbconfig_table';
    execute immediate sqlcmd into install_resp;
    dbms_output.put_line('install_resp: ' || install_resp);
    if(install_resp = '1') then null;
    else
      sqlcmd := 'grant EXECUTE on DBSNMP.mgmt_response to OEM_MONITOR';
      execute immediate sqlcmd;
    end if;
end;
/

Rem =====================================================================
Rem 4. INSTALL STATSPACK
Rem =====================================================================

connect &1
set serveroutput on

SET FEEDBACK 1
SET NUMWIDTH 10
SET LINESIZE 80
SET TRIMSPOOL ON
SET TAB OFF
SET PAGESIZE 100
SET SERVEROUTPUT ON

variable script varchar2(100);
column script new_val SCRIPT_TO_RUN;

declare
  statspack_installed varchar2(200);
  sqloutput varchar2(200);
  sqlcmd varchar2(200);

begin
  :script := '&nothing_sql';

  sqlcmd := 'select basic_install from dbconfig_table';
  execute immediate sqlcmd into sqloutput;
  if (sqloutput = '1') then 
    dbms_output.put_line('-------');
    dbms_output.put_line('--  BASIC INSTALL - SKIP INSTALL STATSPACK ---');
    dbms_output.put_line('-------');
  else
    dbms_output.put_line('-------');
    dbms_output.put_line('--  INSTALL STATSPACK ');
    dbms_output.put_line('-------');

  sqlcmd := 'select statspack_installed from dbconfig_table';
  execute immediate sqlcmd into statspack_installed;
  dbms_output.put_line('statspack_installed: ' || statspack_installed);

  if(statspack_installed = '1') then
    dbms_output.put_line('statspack is already installed.');
  else
    dbms_output.put_line('Installing statspack..');
    :script := '?/rdbms/admin/spcreate.sql';
  end if;
 end if; -- end of basic_install
end;
/
select :script script from dual;
@@&SCRIPT_TO_RUN

Rem =====================================================================
Rem 5. CONFIGURE STATSPACK 
Rem =====================================================================

connect &1
set serveroutput on

SET FEEDBACK 1
SET NUMWIDTH 10
SET LINESIZE 80
SET TRIMSPOOL ON
SET TAB OFF
SET PAGESIZE 100
SET SERVEROUTPUT ON

variable script varchar2(100);
column script new_val SCRIPT_TO_RUN;

declare
  statspack_installed varchar2(200);
  statspack_configured varchar2(200);
  perfstat_locked varchar2(200);
  sqloutput varchar2(200);
  sqlcmd varchar2(200);
begin
  :script := '&nothing_sql';
   sqlcmd := 'select basic_install from dbconfig_table';
   execute immediate sqlcmd into sqloutput;
   if (sqloutput = '1') then 
      dbms_output.put_line('-------');
      dbms_output.put_line('--  BASIC INSTALL - SKIP CONFIGURE STATSPACK ---');
      dbms_output.put_line('-------');
   else
    dbms_output.put_line('-------');
    dbms_output.put_line('--  CONFIGURE STATSPACK ');
    dbms_output.put_line('-------');

    sqlcmd := 'select statspack_configured from dbconfig_table';
    execute immediate sqlcmd into statspack_configured;
    dbms_output.put_line('statspack_configured: ' || statspack_configured);

    if(statspack_configured = '1') then
      dbms_output.put_line('statspack is already configured.');
    else
      sqlcmd := 'select perfstat_locked from dbconfig_table';
      execute immediate sqlcmd into perfstat_locked;
      dbms_output.put_line('perfstat_locked: ' || perfstat_locked);

      dbms_output.put_line('Configuring statspack...');
      if(perfstat_locked = '1') then 
         dbms_output.put_line('Unlocking PERFSTAT user...'); 
         sqlcmd := 'alter user PERFSTAT identified by &perfstat_password account unlock';
         execute immediate sqlcmd;
      end if; 
     :script := '&spset_sql';
    end if; 
  end if; -- end of basic_install
end;
/
whenever SQLERROR continue 
connect PERFSTAT/&perfstat_password
select :script script from dual;
@@&SCRIPT_TO_RUN

Rem =====================================================================
Rem 6. Lock PERFSTAT account
Rem =====================================================================

connect &1
set serveroutput on

SET FEEDBACK 1
SET NUMWIDTH 10
SET LINESIZE 80
SET TRIMSPOOL ON
SET TAB OFF
SET PAGESIZE 100
SET SERVEROUTPUT ON

declare
  statspack_installed varchar2(200);
  perfstat_locked varchar2(200);
  sqloutput varchar2(200);
  sqlcmd varchar2(200);
  db_version varchar2(200);

begin
   sqlcmd := 'select basic_install from dbconfig_table';
   execute immediate sqlcmd into sqloutput;
   if (sqloutput = '1') then 
      dbms_output.put_line('-------');
      dbms_output.put_line('--  BASIC INSTALL - SKIP LOCK PERFSTAT ACCOUNT ---');
      dbms_output.put_line('-------');
   else
    sqlcmd := 'select db_version from dbconfig_table';
    execute immediate sqlcmd into db_version;
    if (db_version like '1%') then null;
    else
      sqlcmd := 'select statspack_installed from dbconfig_table';
      execute immediate sqlcmd into statspack_installed;
      dbms_output.put_line('statspack_installed: ' || statspack_installed);
  
      sqlcmd := 'select perfstat_locked from dbconfig_table';
      execute immediate sqlcmd into perfstat_locked;
      dbms_output.put_line('perfstat_locked: ' || perfstat_locked);
  
      if(statspack_installed = '1' or perfstat_locked = '0') then
        dbms_output.put_line('Locking PERFSTAT user...');
        sqlcmd := 'alter user PERFSTAT password expire account lock';
        execute immediate sqlcmd;
      end if;
    end if;
  end if; -- end of basic_install
end;
/

Rem =====================================================================
Rem 7. CONFIGURE MONITOR USER
Rem =====================================================================

connect &1

SET FEEDBACK 1
SET NUMWIDTH 10
SET LINESIZE 80
SET TRIMSPOOL ON
SET TAB OFF
SET PAGESIZE 100
SET SERVEROUTPUT ON

declare
  sqlcmd varchar2(200);
  db_version varchar2(200);

begin
  sqlcmd := 'select db_version from dbconfig_table';
  execute immediate sqlcmd into db_version;
  if (db_version like '1%') then null;
  else
    dbms_output.put_line('-------');
    dbms_output.put_line('--  CONFIGURE MONITOR USER ');
    dbms_output.put_line('-------');
  
    sqlcmd := 'grant OEM_MONITOR to &dbm_user';
    execute immediate sqlcmd;
  end if;
end;
/
-------  DROP TABLE ------- 
connect &1
drop table dbconfig_table;

exit;