c:userdata>type collect_all_db_account_info.sql
create or replace procedure collect_all_db_account_info is
cursor c_db_link is
select host from user_db_links;
begin
for r_link in c_db_link loop
execute immediate 'insert into perfadm.all_db_account_info select distinct v
d.dbid db_id, vd.name db_name, d.username, d.password,d.account_status, d.defaul
t_tablespace,d.temporary_tablespace, d.profile, d.created, p.sysdba,p.sysoper, s
ysdate collect_date from v$database@'||r_link.host||' vd, dba_users@'||r_link.ho
st||' d, v$pwfile_users@'||r_link.host||' p where p.username(+) = d.username';
commit;
end loop;
commit;
exception
when no_data_found then
null;
when others then
-- consider logging the error and then re-raise
raise;
end collect_all_db_account_info;
/
c:userdata>wrap iname=collect_all_db_account_info.sql oname=wcollect_all_db_account_info.plb
pl/sql wrapper: release 9.2.0.6.0- production on fri jul 14 15:52:18 2006
copyright (c) oracle corporation 1993, 2001. all rights reserved.
processing collect_all_db_account_info.sql to wcollect_all_db_account_info.plb
c:userdata>type wcollect_all_db_account_info.plb
create or replace procedure collect_all_db_account_info wrapped
0
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
3
7
9200000
1
4
0
12
2 :e:
1collect_all_db_account_info:
1cursor:
1c_db_link:
1host:
1user_db_links:
1r_link:
1loop:
1execute:
1immediate:
1insert into perfadm.all_db_account_info select distinct vd.dbid db_id, vd.nam+
1e db_name, d.username, d.password,d.account_status, d.default_tablespace,d.te+
1mporary_tablespace, d.profile, d.created, p.sysdba,p.sysoper, sysdate collect+
1_date from v$database@:
1||:
1 vd, dba_users@:
1 d, v$pwfile_users@:
1 p where p.username(+) = d.username:
1commit:
1no_data_found:
1others:
1raise:
0
0
0
62
2
0 9a b4 55 6a a0 f4 b4
bf c8 a0 ac a0 b2 ee ac
d0 e5 e9 bd b7 11 a4 b1
91 :2 a0 37 :2 a0 6e 7e :2 a0 6b
b4 2e 7e 6e b4 2e 7e :2 a0
6b b4 2e 7e 6e b4 2e 7e
:2 a0 6b b4 2e 7e 6e b4 2e
11e 11d a0 57 a0 b4 e9 b7
a0 47 a0 57 a0 b4 e9 b7
a0 4f b7 a6 9 a0 53 a0
62 b7 a6 9 a4 a0 b1 11
68 4f 1d 17 b5
62
2
0 3 17 18 1c 20 24 35
36 39 3d 41 42 46 47 4e
4f 53 59 5e 63 65 71 75
77 7b 7f 83 85 89 8d 92
95 99 9d a0 a1 a6 a9 ae
af b4 b7 bb bf c2 c3 c8
cb d0 d1 d6 d9 dd e1 e4
e5 ea ed f2 f3 f8 fd 101
105 10a 10e 10f 114 116 11a 121
125 12a 12e 12f 134 136 13a 13c
13e 13f 144 1 148 14c 14f 151
152 157 15b 15f 161 16d 171 173
174 17d
62
2
0 b 0 :2 1 4 b 0 :2 4
:2 c 16 11 16 11 :3 5 :5 4 7
11 1b 3 5 d 17 116 118
:2 11f :2 17 123 125 :2 17 136 138 :2 13f
:2 17 143 145 :2 17 15a 15c :2 163 :2 17
167 169 :2 17 :7 5 1b 7 :6 3 1
8 5 16 :2 3 :2 8 :2 5 f :2 3
1 5 :7 1
62
4
0 1 0 :2 1
:2 2 0 :2 2 :9 3
:5 2 :4 6 :23 7 :5 8
6 9 6 :5 a
4 c d :3 c
:2 e :2 10 :3 e b
11 :7 1
17f
4
:3 0 1 :a 0 5d
1 :8 0 2 :2 0
5d 1 3 :2 0
2 :3 0 3 :a 0
2 13 :5 0 6
9 0 7 :3 0
4 :3 0 3 5
:3 0 5 d :2 0
f :5 0 b e
0 10 :6 0 11
:2 0 14 6 9
15 0 5b 7
15 17 14 16
:6 0 13 :7 0 15
6 :3 0 3 :3 0
7 :3 0 18 19
8 :3 0 9 :3 0
a :4 0 b :2 0
6 :3 0 4 :3 0
20 21 0 9
1f 23 :3 0 b
:2 0 c :4 0 c
25 27 :3 0 b
:2 0 6 :3 0 4
:3 0 2a 2b 0
f 29 2d :3 0
b :2 0 d :4 0
12 2f 31 :3 0
b :2 0 6 :3 0
4 :3 0 34 35
0 15 33 37
:3 0 b :2 0 e
:4 0 18 39 3b
:3 0 3c :4 0 3d
:2 0 44 f :3 0
41 42 :2 0 43
f :5 0 40 :2 0
44 1b 46 7
:3 0 1b 44 :4 0
4c f :3 0 49
4a :2 0 4b f
:5 0 48 :2 0 4c
1e 5c 10 :4 0
4f 21 51 23
50 4f :2 0 59
11 :3 0 12 :5 0
56 25 58 27
57 56 :2 0 59
29 :2 0 5c 1
:3 0 2c 5c 5b
4c 59 :6 0 5d
:2 0 1 3 5c
60 :3 0 5f 5d
61 :8 0
2e
4
:3 0 1 a 1
c 1 12 2
1e 22 2 24
26 2 28 2c
2 2e 30 2
32 36 2 38
3a 2 3e 43
2 46 4b 1
4e 1 4d 1
55 1 53 2
51 58 1 13
1
4
0
60
0
1
14
3
3
0 1 1 0 0 0 0 0
0 0 0 0 0 0 0 0
0 0 0 0
6 1 2
1 0 1
18 3 0
0
/
c:userdata>
c:userdata>sqlplus /nolog
sql*plus: release 9.2.0.6.0 - production on fri jul 14 15:53:36 2006
copyright (c) 1982, 2002, oracle corporation. all rights reserved.
sql> conn perfadm@perfdb226
enter password:
connected.
sql> drop procedure collect_all_db_account_info;
procedure dropped.
sql> @wcollect_all_db_account_info.plb
procedure created.
sql>
|
