/
wallet.sql
84 lines (62 loc) · 2.45 KB
/
wallet.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
82
83
84
--==============================================================================
-- GPI - Gunther Pippèrr
-- Desc: get the Wallet Settings of the DB
--
-- Must be run with dba privileges
-- Source see Step by Step Troubleshooting Guide for TDE Error ORA-28374 (Doc ID 1541818.1)
--==============================================================================
set linesize 130 pagesize 300
ttitle left "The Path to the Wallet" skip 2
column WRL_TYPE format a10 heading "WRL|Type"
column WRL_PARAMETER format a20 heading "Wallet|Params"
column STATUS format a10 heading "Status"
column WALLET_TYPE format a10 heading "Wallet|type"
column WALLET_ORDER format a9 heading "Status"
column FULLY_BACKED_UP format a9 heading "Backup"
column CON_ID format 999 heading "Con|ID"
column inst_id format 999 heading "Inst|ID"
select inst_id
, WRL_TYPE
,WRL_PARAMETER
,STATUS
,WALLET_TYPE
,WALLET_ORDER
,FULLY_BACKED_UP
,CON_ID
from gv$encryption_wallet
/
ttitle left "Get the Master Keys " skip 2
column key_id format a60
select key_id
,to_char(activation_time,'dd.mm.yyyy hh24:mi') as activation_time
from v$encryption_keys
/
column name format a40
column masterkeyid_base64 format a60
ttitle left "Get the Master Key for Tablespaces" skip 2
select name
,utl_raw.cast_to_varchar2( utl_encode.base64_encode('01'||substr(mkeyid,1,4))) || utl_raw.cast_to_varchar2( utl_encode.base64_encode(substr(mkeyid,5,length(mkeyid)))) masterkeyid_base64
FROM (select t.name, RAWTOHEX(x.mkid) mkeyid
from v$tablespace t
, x$kcbtek x
where t.ts#=x.ts#)
/
ttitle left "Get the Master Key for the Controlfile" skip 2
select utl_raw.cast_to_varchar2( utl_encode.base64_encode('01'||substr(mkeyid,1,4))) || utl_raw.cast_to_varchar2( utl_encode.base64_encode(substr(mkeyid,5,length(mkeyid)))) masterkeyid_base64
FROM (select RAWTOHEX(mkid) mkeyid
from x$kcbdbk)
/
ttitle left "Get the Master Key for Tables" skip 2
select mkeyid from enc$;
ttitle left "Witch Columns are encrypted?" skip 2
column owner format a15 heading "Owner"
column table_name format a15 heading "Table|Name"
column column_name format a15 heading "Column|Name"
column ENCRYPTION_ALG format a35 heading "Encryption|Algo"
select owner
,table_name
,column_name
,ENCRYPTION_ALG
from dba_encrypted_columns
/
ttitle off