ERROR at line 1:
ORA-01722: invalid number
My friend told me, He found error on script/program after he used with 10gR2, But no problem on 10gR1.
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.3.0
PL/SQL Release 10.1.0.3.0
CORE 10.1.0.3.0
TNS for Linux: Version 10.1.0.3.0
NLSRTL Version 10.1.0.3.0
SQL> SELECT a.Current_proc, b.Max_proc, ROUND(((b.Max_proc-a.Current_proc)/b.Max_proc)*100, 0) Alert_proc FROM (SELECT COUNT(*)Current_proc FROM sys.v_$process) a,(SELECT value Max_proc FROM sys.v_$parameter WHERE name = 'processes') b WHERE ROUND(((b.Max_proc-a.Current_proc)/b.Max_proc)*100,0) < 15;
no rows selected
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0
PL/SQL Release 10.2.0.3.0
CORE 10.2.0.3.0
TNS for Linux: Version 10.2.0.3.0
NLSRTL Version 10.2.0.3.0
SQL> SELECT a.Current_proc, b.Max_proc, ROUND(((b.Max_proc-a.Current_proc)/b.Max_proc)*100, 0) Alert_proc FROM (SELECT COUNT(*)Current_proc FROM sys.v_$process) a,(SELECT value Max_proc FROM sys.v_$parameter WHERE name = 'processes') b WHERE ROUND(((b.Max_proc-a.Current_proc)/b.Max_proc)*100,0) < 15;
SELECT a.Current_proc, b.Max_proc, ROUND(((b.Max_proc-a.Current_proc)/b.Max_proc)*100, 0) Alert_proc FROM (SELECT COUNT(*)Current_proc FROM sys.v_$process) a,(SELECT value Max_proc FROM sys.v_$parameter WHERE name = 'processes') b WHERE ROUND(((b.Max_proc-a.Current_proc)/b.Max_proc)*100,0) < 15
*
ERROR at line 1:
ORA-01722: invalid number
Query is multiplying a column with varchar2 data type with a number column. This may or may not succeed depending on the query plan. Oracle cannot guarantee plans to remain same across database versions. With new features in a new release query plans can always change.On 10gR1:----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 358 | 72 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 358 | 72 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 345 | 48 (0)| 00:00:01 |
|* 3 | FIXED TABLE FULL | X$KSPPI | 1 | 68 | 24 (0)| 00:00:01 |
|* 4 | FIXED TABLE FIXED INDEX| X$KSPPCV (ind:2) | 1 | 277 | 24 (0)| 00:00:01 |
|* 5 | VIEW | | 1 | 13 | 24 (0)| 00:00:01 |
| 6 | SORT AGGREGATE | | 1 | 26 | | |
|* 7 | FIXED TABLE FULL | X$KSUPR | 1 | 26 | 24 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("KSPPINM"='processes' AND "X"."INST_ID"=:B1)
4 - filter("X"."INDX"="Y"."INDX" AND (TRANSLATE("KSPPINM",'_','#') NOT LIKE '#%'
OR "KSPPSTDF"='FALSE'))
5 - filter(ROUND((TO_NUMBER("KSPPSTVL")-"A"."CURRENT_PROC")/TO_NUMBER("KSPPSTVL")*1
00,0)<15)>0)
On 10gR2:---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 371 | 1 (100)| 00:00:01 |
|* 1 | HASH JOIN | | 1 | 371 | 1 (100)| 00:00:01 |
| 2 | NESTED LOOPS | | 5 | 1515 | 0 (0)| 00:00:01 |
| 3 | VIEW | | 1 | 13 | 0 (0)| 00:00:01 |
| 4 | SORT AGGREGATE | | 1 | 26 | | |
|* 5 | FIXED TABLE FULL| X$KSUPR | 1 | 26 | 0 (0)| 00:00:01 |
|* 6 | FIXED TABLE FULL | X$KSPPCV | 5 | 1450 | 0 (0)| 00:00:01 |
|* 7 | FIXED TABLE FULL | X$KSPPI | 1 | 68 | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("X"."INDX"="Y"."INDX")
filter(TRANSLATE("KSPPINM",'_','#') NOT LIKE '#%' OR
"KSPPSTDF"='FALSE' OR BITAND("KSPPSTVF",5)>0)
5 - filter("INST_ID"=USERENV('INSTANCE') AND BITAND("KSSPAFLG",1)<>0)
6 - filter(ROUND((TO_NUMBER("KSPPSTVL")-"A"."CURRENT_PROC")/TO_NUMBER(
"KSPPSTVL")*100,0)<15)
7 - filter("KSPPINM"='processes' AND
"X"."INST_ID"=USERENV('INSTANCE') AND TRANSLATE("KSPPINM",'_','#') NOT
LIKE '##%')
That show, SQL PLAN was changed.
we might change SQL or something to make script/program work and (show old result)
SQL>select CURRENT_UTILIZATION Current_proc, INITIAL_ALLOCATION Max_proc, ROUND(((INITIAL_ALLOCATION-CURRENT_UTILIZATION)/INITIAL_ALLOCATION)*100, 0) Alert_proc from v$resource_limit where RESOURCE_NAME='processes' and ROUND(((INITIAL_ALLOCATION-CURRENT_UTILIZATION)/INITIAL_ALLOCATION)*100, 0) < 15;
no rows selected
That worked... Perhaps we can use other Ideas, But My point ... When we change Oracle version, we might necessary to change SQL Statement or something.
:(
How to Install Grid Control 11g? We can review on OEM document -> Oracle® Enterprise Manager Grid Control Basic Installation Guide and Step by step Installation of 11.1 Grid control [ID 1059516.1] (oracle support).
- OEM Grid Control https://hostname:7799/em/
However we can review Features.
Posts
