辨别文件名出错

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