/
install_sesspack.sql
81 lines (66 loc) · 2.53 KB
/
install_sesspack.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
-- Copyright 2018 Tanel Poder. All rights reserved. More info at http://tanelpoder.com
-- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms & conditions.
--------------------------------------------------------------------------------
--
-- Author: Tanel Poder
-- Copyright: (c) http://www.tanelpoder.com
--
-- Notes: This software is provided AS IS and doesn't guarantee anything
-- Proofread before you execute it!
--
--------------------------------------------------------------------------------
spool sesspack_install.log
prompt
prompt =================================================================================
prompt
prompt Welcome to the Semi-Automatic Workload Repository installer!
prompt
prompt This script will create some SAWR tables and SESSPACK package in specified schema
prompt
prompt Refer to README.txt for usage and further information
prompt
prompt (c) Tanel Poder http://www.tanelpoder.com
prompt
prompt =================================================================================
prompt
prompt
prompt You must be already connected to your target database as SYS or any other user
prompt who can grant privileges on some V$ objects for preparing SAWR user
prompt
-- this section is for initializing couple of "preprocessor" variables which create
-- different PLSQL based on target database version
column sesspack_v9 noprint new_value version_9_enable
column sesspack_v10 noprint new_value version_10_enable
with SQ as (
select substr(
substr(banner, instr(banner, 'Release ')+8),
1,
instr(substr(banner, instr(banner, 'Release ')+8),'.')-1
) db_version
from v$version
where rownum = 1
)
select
case when db_version = '9' then '--' else '/*' end sesspack_v9,
case when db_version != '9' then '--' else '/*' end sesspack_v10
from sq;
-- end of preprocessor initialization
-- Defines for non-interactive installation
define spuser="&1"
define sppassword="&2"
define spconn="@&3"
-- Uncomment for interactive installation
--define spuser="SESSPACK"
--define spconn=""
--accept spuser default &spuser prompt "Specify the schema for SAWR and SESSPACK installation [&spuser]: "
--accept sppassword prompt "Specify the password for &spuser user: " hide
--accept spconn prompt "Enter connect string PREFIXED WITH @ if installing into remote database [&spconn]: "
@@prepare_user.sql
connect &spuser/&sppassword&spconn
@@install_sesspack_schema.sql
@@install_sesspack_packages.sql
@@install_grants_syns.sql
prompt Installation completed.
prompt Currently connected as &spuser&spconn....
prompt
spool off