This discussion has been locked.
You can no longer post new replies to this discussion. If you have a question you can start a new discussion

Unable to verify user defined collection

I am trying to create a UDC for the following sql:

select a.tablespace_name,
       round(a.bytes_alloc/(1024*1024*1024),0) "TOTAL ALLOC (GB)",
       round(a.bytes_alloc-b.tot_used/(1024*1024*1024),0) "FREE (GB)",
       round((nvl(b.tot_used,0)/a.bytes_alloc)*100,2) "% USED"
from ( select tablespace_name,
       sum(bytes) physical_bytes,
       sum(decode(autoextensible, 'YES',CASE WHEN maxbytes<bytes THEN bytes ELSE maxbytes END,'NO',bytes)) bytes_alloc
       from dba_data_files
       group by tablespace_name ) a,
     ( select tablespace_name, sum(bytes) tot_used
       from dba_segments
       group by tablespace_name ) b
where a.tablespace_name = b.tablespace_name (+)
and   a.tablespace_name not in (select distinct tablespace_name from dba_temp_files)
and   a.tablespace_name not like 'UNDO%';

 

Everytime I get tje following error for verification:

Collection verification failed: The following error occurred while retrieving the SQL query : ORA-00933: SQL-Befehl wurde nicht korrekt beendet

 

Whats wrong?

Parents
  • Hello j.sloot,

    please remove the semicolon at the end of the statement.
    I just tested it and without semicolon the verification was successful.

    I know it sounds strange as a proper ending of a SQL statement is the semicolon.
    If you want to have more background details why it was implemented like this, I can check internally. Let me know if I should do that for you.

    Regards
    Nicola
Reply
  • Hello j.sloot,

    please remove the semicolon at the end of the statement.
    I just tested it and without semicolon the verification was successful.

    I know it sounds strange as a proper ending of a SQL statement is the semicolon.
    If you want to have more background details why it was implemented like this, I can check internally. Let me know if I should do that for you.

    Regards
    Nicola
Children
No Data