set linesize 145
set pagesize 1000
set trimout on
set trimspool on
Set Feedback off
set timing off
set verify off
Set Heading Off
Set Termout Off
create or replace function show_bad_file_name(x in varchar2) return varchar2
Is
c char(1);
V_wrong Varchar2(255);
i number;
begin
for i in 1..length(x) loop
select substr(x,length(x)-i+1,1) into c from dual;
if ascii(c) > 0 and ascii(c) < 46 then
V_Wrong := V_Wrong ||' '||c;
elsif ascii(c) > 58 and ascii(c) < 65 then
V_Wrong := V_Wrong ||' '||c;
elsif ascii(c) > 90 and ascii(c) < 92 then
V_Wrong := V_Wrong ||' '||c;
elsif ascii(c) > 92 and ascii(c) < 95 then
V_Wrong := V_Wrong ||' '||c;
elsif ascii(c) > 95 and ascii(c) < 97 then
V_Wrong := V_Wrong ||' '||c;
elsif ascii(c) > 122 and ascii(c) < 321 then
V_Wrong := V_Wrong ||' '||c;
end if;
end loop;
return V_Wrong;
end;
/
show error
Set Heading On
Set Termout On
prompt
prompt -- ----------------------------------------------------------------------- ---
prompt -- Tablespaces / Datafiles ---
prompt -- ----------------------------------------------------------------------- ---
clear breaks
break on tablespace_name
column tablespace_name format a15 wrap heading "Tablespace Name"
column file_name format a70 wrap heading "DataFile Name"
column file_id format 999 heading "File Id"
column Alloc format 99,999 heading "Alloc|(Mb)"
column wc format a15 wrap heading "Wrong|caracter in|DataFile Name"
(select
df.tablespace_name
, df.file_id file_id
, 'Start File_Name-->'||df.file_name||'<--End File_Name' file_name
, df.bytes/1048576 Alloc
, show_bad_file_name (df.file_name) wc
from
sys.dba_data_files df
where
show_bad_file_name (df.file_name) is not null
group
by df.tablespace_name
, df.file_id
, df.file_name
, df.bytes
)
Union
(select
dtf.tablespace_name
, dtf.file_id
, 'Start File_Name-->'||dtf.file_name||'<--End File_Name' file_name
, dtf.bytes/1048576 Alloc
, show_bad_file_name (dtf.file_name)
from
sys.dba_temp_files dtf
where
show_bad_file_name (dtf.file_name) is not null
group
by dtf.tablespace_name
, dtf.file_id
, dtf.file_name
, dtf.bytes
)
Order
By tablespace_name
;
Prompt
Set Heading Off
Set Termout Off
Drop function function show_bad_file_name;
Set Heading On
Set Termout On
输出:
-- ----------------------------------------------------------------------- --
-- Tablespaces / Datafiles --
-- ----------------------------------------------------------------------- --
Wrong
Alloc caracter in
Tablespace Name DataFile Name (Mb) DataFile Name
--------------- ---------------------------------------------------------------------- ------- ---------------
TEST Start File_Name-->/as/10g/oradata/INF/test1 .dbf 10
<--End File_Name