asked on

ORA-01722: invalid number - Oracle 9.2.0.8

Need to fix this query so it will not error out...............

select org_name, substr(segment_name,-3), bytes/1048576 meg
from dba_segments, org_list
where tablespace_name like '%ORG%'
and to_number (substr(segment_name,-3)) = org_id;

gives

ERROR:
ORA-01722: invalid number

awking00

My suspicion is that the last three characters of all of the segment_names are not numeric. You might try -
... and substr(segment_name,-3)) = to_char(org_id);

awking00

One too many parentheses.
... and substr(segment_name,-3) = to_char(org_id);

bkreynolds48

ASKER

awking00,
Thanks - is there a way to subtotal the bytes from each org ---
table (bytes), index (bytes)
In this same script?
would a group by org name work?

ASKER CERTIFIED SOLUTION

Naveen Kumar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Naveen Kumar

also if you want it only for TABLES, then use the below :

select org_name, substr(segment_name,-3), sum(bytes)/1048576 meg
from dba_segments, org_list
where tablespace_name like '%ORG%'
and substr(segment_name,-3) = to_char(org_id)
and segment_type ='TABLE' -- if you want to change, then put INDEX for indexes
group by org_name, substr(segment_name,-3);

select org_name, sum(bytes)/1048576 meg
from dba_segments, org_list
where tablespace_name like '%ORG%'
and substr(segment_name,-3) = to_char(org_id)
and segment_type ='TABLE' -- if you want to change, then put INDEX for indexes
group by org_name;

bkreynolds48

ASKER

awesome - thanks so much