-
Notifications
You must be signed in to change notification settings - Fork 10
/
Copy pathasm_queries.sql
123 lines (119 loc) · 5.43 KB
/
asm_queries.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
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
-- generate sql to delete all asm files for a given database
column a format a120
set linesize 120
select 'alter diskgroup ' || gname || ' drop file ''+' || gname || sys_connect_by_path(aname, '/') || ''';' a
from ( select b.name gname, a.parent_index pindex, a.name aname,
a.reference_index rindex , a.system_created, a.alias_directory
from v$asm_alias a, v$asm_diskgroup b
where a.group_number = b.group_number
)
where alias_directory = 'N'
and system_created = 'Y'
start with (mod(pindex, power(2, 24))) = 0
and rindex in
( select a.reference_index
from v$asm_alias a, v$asm_diskgroup b
where a.group_number = b.group_number
and (mod(a.parent_index, power(2, 24))) = 0
and a.name = '&DATABASENAME'
)
connect by prior rindex = pindex;
-- generate sql to delete all the asm files of a given type for a given database
column a format a120
set linesize 120
select 'alter diskgroup ' || gname || ' drop file ''+' || gname || sys_connect_by_path(aname, '/') || ''';' a
from ( select b.name gname, a.parent_index pindex, a.name aname,
a.reference_index rindex , a.system_created, a.alias_directory,
c.type file_type
from v$asm_alias a, v$asm_diskgroup b, v$asm_file c
where a.group_number = b.group_number
and a.group_number = c.group_number(+)
and a.file_number = c.file_number(+)
and a.file_incarnation = c.incarnation(+)
)
where alias_directory = 'N'
and system_created = 'Y'
and file_type = '&FILETYPE'
start with (mod(pindex, power(2, 24))) = 0
and rindex in
( select a.reference_index
from v$asm_alias a, v$asm_diskgroup b
where a.group_number = b.group_number
and (mod(a.parent_index, power(2, 24))) = 0
and a.name = '&DATABASENAME'
)
connect by prior rindex = pindex;
-- generate sql to delete all the asm files except of a given type for a given database
column a format a120
set linesize 120
select 'alter diskgroup ' || gname || ' drop file ''+' || gname || sys_connect_by_path(aname, '/') || ''';' a
from ( select b.name gname, a.parent_index pindex, a.name aname,
a.reference_index rindex , a.system_created, a.alias_directory,
c.type file_type
from v$asm_alias a, v$asm_diskgroup b, v$asm_file c
where a.group_number = b.group_number
and a.group_number = c.group_number(+)
and a.file_number = c.file_number(+)
and a.file_incarnation = c.incarnation(+)
)
where alias_directory = 'N'
and system_created = 'Y'
and file_type != '&FILETYPE'
start with (mod(pindex, power(2, 24))) = 0
and rindex in
( select a.reference_index
from v$asm_alias a, v$asm_diskgroup b
where a.group_number = b.group_number
and (mod(a.parent_index, power(2, 24))) = 0
and a.name = '&DATABASENAME'
)
connect by prior rindex = pindex;
-- generate a list of all the asm files / directories / aliasses for a given database
column full_alias_path format a75
column file_type format a15
select concat('+'||gname, sys_connect_by_path(aname, '/')) full_alias_path,
system_created, alias_directory, file_type
from ( select b.name gname, a.parent_index pindex, a.name aname,
a.reference_index rindex , a.system_created, a.alias_directory,
c.type file_type
from v$asm_alias a, v$asm_diskgroup b, v$asm_file c
where a.group_number = b.group_number
and a.group_number = c.group_number(+)
and a.file_number = c.file_number(+)
and a.file_incarnation = c.incarnation(+)
)
start with (mod(pindex, power(2, 24))) = 0
and rindex in
( select a.reference_index
from v$asm_alias a, v$asm_diskgroup b
where a.group_number = b.group_number
and (mod(a.parent_index, power(2, 24))) = 0
and a.name = '&DATABASENAME'
)
connect by prior rindex = pindex;
-- generate a list of all the asm files of a given type for a given database
column full_alias_path format a70
column file_type format a15
select concat('+'||gname, sys_connect_by_path(aname, '/')) full_alias_path,
system_created, alias_directory, file_type
from ( select b.name gname, a.parent_index pindex, a.name aname,
a.reference_index rindex , a.system_created, a.alias_directory,
c.type file_type
from v$asm_alias a, v$asm_diskgroup b, v$asm_file c
where a.group_number = b.group_number
and a.group_number = c.group_number(+)
and a.file_number = c.file_number(+)
and a.file_incarnation = c.incarnation(+)
)
where alias_directory = 'N'
and system_created = 'Y'
and file_type = '&FILETYPE'
start with (mod(pindex, power(2, 24))) = 0
and rindex in
( select a.reference_index
from v$asm_alias a, v$asm_diskgroup b
where a.group_number = b.group_number
and (mod(a.parent_index, power(2, 24))) = 0
and a.name = '&DATABASENAME'
)
connect by prior rindex = pindex;