| buffer_pool_statistics [message #512008] |
Thu, 16 June 2011 09:58  |
 |
ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
|
Senior Member |
|
|
Hi...
This is Just to know .. Am I missing something here ?
ind> select banner from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
5 rows selected.
ind> select name,(1-(physical_reads/(db_block_gets+consistent_gets)))*100
2 from gv$buffer_pool_statistics
3 where db_block_gets+consistent_gets > 0;
select name,(1-(physical_reads/(db_block_gets+consistent_gets)))*100
*
ERROR at line 1:
ORA-00911: invalid character
Dev> select banner from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for Linux: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production
5 rows selected.
Dev> select name,(1-(physical_reads/(db_block_gets+consistent_gets)))*100
2 from gv$buffer_pool_statistics
3 where db_block_gets+consistent_gets > 0;
NAME (1-(PHYSICAL_READS/(DB_BLOCK_G
-------------------- ------------------------------
DEFAULT 47.897072
DEFAULT 96.6248649
2 rows selected.
SQL> select banner from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL> select name,(1-(physical_reads/(db_block_gets+consistent_gets)))*100
2 from gv$buffer_pool_statistics
3 where db_block_gets+consistent_gets > 0;
NAME (1-(PHYSICAL_READS/(DB_BLOCK_GETS+CONSISTENT_GETS)))*100
-------------------- --------------------------------------------------------
DEFAULT 99.6141505
SQL>
Quote:ORA-00911: invalid character
anyone with this kind of buggy Behavior previous ?
Sriram
[Updated on: Thu, 16 June 2011 12:23] Report message to a moderator |
|
|
|
| Re: buffer_pool_statistics [message #512011 is a reply to message #512008] |
Thu, 16 June 2011 10:23  |
cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
You're using a weird - symbol. It's not the standard one.
It's ascii code is:
SQL> SELECT ASCII('') FROM dual;
ASCII('')
----------
49837
And you see that orafaq doesn't like it either - it's invisble in the code tags but you can copy and paste it. Same happens in your example above.
The proper minus symbol is of course:
SQL> SELECT ASCII('-') FROM dual;
ASCII('-')
----------
45
SQL>
|
|
|
|
|
|
|
|
|
|
| Re: buffer_pool_statistics [message #512016 is a reply to message #512014] |
Thu, 16 June 2011 10:32 |
cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Interestingly (or maybe not to someone who understands character sets better than I do) dump gives a different answer again:
SQL> select dump('(1(') from dual;
DUMP('(1(')
------------------------------
Typ=96 Len=5: 40,49,194,173,40
If I'm reading that right it thinks it's two characters.
|
|
|
|
|
|
|
|
| Re: buffer_pool_statistics [message #512020 is a reply to message #512008] |
Thu, 16 June 2011 10:42 |
 |
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
And how come your select statement and the column in output are different?
The select statement is,
Dev> select name,(1(physical_reads/(db_block_gets .....
and output is
NAME (1-(PHYSICAL_READS/(DB_BLOCK_G
There must be some problem with the copy/paste of sql/session you are using.
It is supposed to be
as correctly shown in output.
[Updated on: Thu, 16 June 2011 10:43] Report message to a moderator |
|
|
|
|
|
| Re: buffer_pool_statistics [message #512029 is a reply to message #512021] |
Thu, 16 June 2011 11:23 |
cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Or more likely the tool running the query, or something else inbetween, did the conversion.
If I run the query in sqlplus run from windows on my PC on a 11.1.0.7 DB I get the ORA-00911. Run the same query on sqlplus on the unix server via putty (same DB obviously) then it works. Looks like putty converted the character to chr(45)
|
|
|
|
|
|
|
|
| Re: buffer_pool_statistics [message #512032 is a reply to message #512012] |
Thu, 16 June 2011 12:18 |
|
ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
|
Senior Member |
|
|
Michel Cadot wrote on Thu, 16 June 2011 20:55You have a buggy minus character with chr(240) instead of chr(45).
Regards
Michel
ind> select banner from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
5 rows selected.
ind> select ((select value from NLS_DATABASE_PARAMETERS where parameter = 'NLS_LANGUAGE')||'_'||
2 (select value from
3 NLS_DATABASE_PARAMETERS where parameter = 'NLS_TERRITORY')||'.'||
4 (select value from NLS_DATABASE_PARAMETERS where parameter = 'NLS_CHARACTERSET')) from dual;
((SELECTVALUEFROMNLS_DATABASE_PARAMETERSWHEREPARAMETER='NLS_LANGUAGE')||'_'||(SELECTVALUEFROMNLS_DAT
----------------------------------------------------------------------------------------------------
AMERICAN_AMERICA.WE8MSWIN1252
1 row selected.
ind> select ascii('-') from dual;
ASCII('-')
----------
45
1 row selected.
sriram
[Updated on: Thu, 16 June 2011 12:42] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: buffer_pool_statistics [message #512062 is a reply to message #512046] |
Thu, 16 June 2011 14:31 |
 |
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
There is NO Oracle error, JUST your query that contains a BAD character.
Now you copy and paste with the correct minus character and so it works.
You seem to have understand nothing to the previous posts.
I repeat the error was in your query, the error was in your query.
BUT now the whole topic seems nuts because you updated your first query with a correct minus character instead of the original one.
It is the third time you wrapped a topic in this way. You are on a dangerous downward path. Moderator privileges are not for this.
Regards
Michel
[Edit: Add the 2 last sentences.]
[Updated on: Thu, 16 June 2011 14:42] Report message to a moderator |
|
|
|
| Re: buffer_pool_statistics [message #512077 is a reply to message #512062] |
Thu, 16 June 2011 17:07 |
cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Let me get this straight:
1) Siram posts a query with an invalid character error. Said error points to an invalid character.
2) Both me and Michel point out that it's an invalid character.
3) Further I point out that some programs will automatically convert it to the correct minus symbol. Which would be the most likely explanation for why Siram doesn't get it on every DB.
4) Siram insists that the character he used really was chr(45) despite the fact that:
a) The minus symbol in the original version of the original post was a different character.
b) the error message pointed to that character (gee I wonder why)
5) Siram updates his original post to replace the character that actually caused the error with one that wouldn't.
Siram - what were you thinking?
|
|
|
|
| Re: buffer_pool_statistics [message #512083 is a reply to message #512062] |
Thu, 16 June 2011 21:49 |
|
ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
|
Senior Member |
|
|
Michel Cadot wrote on Fri, 17 June 2011 01:01There is NO Oracle error, JUST your query that contains a BAD character.
Now you copy and paste with the correct minus character and so it works.
You seem to have understand nothing to the previous posts.
I repeat the error was in your query, the error was in your query.
BUT now the whole topic seems nuts because you updated your first query with a correct minus character instead of the original one.
It is the third time you wrapped a topic in this way. You are on a dangerous downward path. Moderator privileges are not for this.
Regards
Michel
[Edit: Add the 2 last sentences.]
Yes..I copied Mahesh Query.But My 10g DBR2 has a correct character Executed that manually not just copy and paste.
So its not the Case of chr(45).
Quote:In the standard WE8 (MSWIN1252 or ISO8859P1, p15...) character set it is a chr(240).
Both 11R2 and 10R2 same machine with same char set.
Quote: BUT now the whole topic seems nuts because you updated your first query with a correct minus character instead of the original one.
When mahesh updated with
Quote:There must be some problem with the copy/paste of sql/session you are using.
It is supposed to be
Then I have seen that this forum suppressed it.
And the fact is No one try to click on Edit the post (Just to see not for modify).
That is Just for the correct visibility of my post as you are trying to do some dump for '(1(' instead of '(1-('
SQL> select dump('(1(') from dual;
DUMP('(1(')
------------------------------
Typ=96 Len=5: 40,49,194,173,40
.
Quote:It is the third time you wrapped a topic in this way. You are on a dangerous downward path. Moderator privileges are not for this.
I am doing My moderator work By making the correct visibility of the query for the members.
This is similar to disable smileys in any of the forum posts.
form converts that 8) to a smiley 
This WHY I have used Pre tags not code tags on my next messages.
My intention is not to confuse members replying to me thats why I have edited. clear now?.
And I AM using Moderator privileges correctly.
@ cookie
Thanks for the summary 
Quote:Both me and Michel point out that it's an invalid character
Yes ! The error message it self saying its an invalid char.
and Michel and you pointed that I have chr(240) instead of chr(45) I understood this.
But i have manually enter query for ascii('-') on both versions [ not copied '-' from this site/Forum]
Quote:Further I point out that some programs will automatically convert it to....
I Have executed that on the same machine using "sqlplus" not sql * plus.No SSH clients involved here.
Quote:Siram updates his original post to replace the character that actually caused the error with one that wouldn't
I Just mentioned it above!.I have n`t modified any result of the original post. code tags suppressed it so on my next replies I have used pre tags.
have you people Observed Why Mahesh got that error on the same machine and same db(I hope it is ) ?
@ Michel
Once again saying ...
I am not modifying any result of My first Original post.Mahesh Observed it corectly on his first reply
Quote:There must be some problem with the copy/paste of sql/session you are using. .
If you try to copy or edit My Original post now,it will show you two '-' char.Try that.
If you can answer to my questions then you are always welcome.
Of course error Occurred at my test server so no problem I can
trace and find it in any other manner.
@ Mahesh
Thank you so much for your last and first reply.
You see both are working now.You pointed me in a right way.Thank you.
Regards
Sriram
|
|
|
|
| Re: buffer_pool_statistics [message #512089 is a reply to message #512083] |
Thu, 16 June 2011 23:27 |
|
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Once more you are insincere.
Quote:I have n`t modified any result of the original post
No one says that, we said you modified your QUERY.
Quote:And I AM using Moderator privileges correctly.
WRONG! Modifying the key point of removing your post after others read and answer them because you no more like it is BAD usage of moderator privileges. Standard users are not allowede to do so BECAUSE we don't want such things to happen.
Quote:If you try to copy or edit My Original post now,it will show you two '-' char.Try that.
Your post now modified is then unavailable for any investigation as it is just fake.
The rest of your post is just blablabla to duck, cookiemonster correctly wrapped up the topic.
Regards
Michel
[Updated on: Thu, 16 June 2011 23:29] Report message to a moderator |
|
|
|
|
|
|
|
| Re: buffer_pool_statistics [message #512151 is a reply to message #512083] |
Fri, 17 June 2011 04:20 |
cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
ramoradba wrote on Fri, 17 June 2011 03:49
Quote: BUT now the whole topic seems nuts because you updated your first query with a correct minus character instead of the original one.
When mahesh updated with
Quote:There must be some problem with the copy/paste of sql/session you are using.
It is supposed to be
Then I have seen that this forum suppressed it.
Becuase it can't display chr(49837) or chr(240)
ramoradba wrote on Fri, 17 June 2011 03:49
And the fact is No one try to click on Edit the post (Just to see not for modify).
Know that for a fact do you? I did click on edit. Not that you can see that character in the edit window either. But it is there, you can tell because:
a) If you copy and paste the whole thing into something else the symbol appears.
b) If you use the cursor keys to scroll past it takes two presses to get past that point - so there's a character there.
ramoradba wrote on Fri, 17 June 2011 03:49
That is Just for the correct visibility of my post as you are trying to do some dump for '(1(' instead of '(1-('
SQL> select dump('(1(') from dual;
DUMP('(1(')
------------------------------
Typ=96 Len=5: 40,49,194,173,40
That was a dump of the string from the original version of your query. The 194,173 in the output is the chr(49837) character:
SQL> SELECT DUMP(CHR(49837)) FROM dual;
DUMP(CHR(49837))
--------------------
Typ=1 Len=2: 194,173
SQL> select chr(49837) from dual;
CH
--
SQL>
ramoradba wrote on Fri, 17 June 2011 03:49
Quote:It is the third time you wrapped a topic in this way. You are on a dangerous downward path. Moderator privileges are not for this.
I am doing My moderator work By making the correct visibility of the query for the members.
This is similar to disable smileys in any of the forum posts.
form converts that 8) to a smiley
a) As LF points out code tags don't do smiley conversions.
b) You removed a character that would cause the error.
ramoradba wrote on Fri, 17 June 2011 03:49
My intention is not to confuse members replying to me thats why I have edited. clear now?.
And I AM using Moderator privileges correctly.
You removed the character we commented on. That will confuse people.
ramoradba wrote on Fri, 17 June 2011 03:49
have you people Observed Why Mahesh got that error on the same machine and same db(I hope it is ) ?
Yes but I'm not sure how he managed it. My 10.2.0.1 has no problem with a lack of spaces:
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 10g Release 10.2.0.1.0 - 64bit Production
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for IBM/AIX RISC System/6000: Version 10.2.0.1.0 - Productio
NLSRTL Version 10.2.0.1.0 - Production
SQL> select name,(1-(physical_reads/(db_block_gets+consistent_gets)))*100
2 from gv$buffer_pool_statistics
3 where db_block_gets+consistent_gets > 0;
NAME
------------------------------------------------------------
(1-(PHYSICAL_READS/(DB_BLOCK_GETS+CONSISTENT_GETS)))*100
--------------------------------------------------------
DEFAULT
91.3852186
SQL>
It does have a problem with dodgy minus symbols:
SQL> select name,(1(physical_reads/(db_block_gets+consistent_gets)))*100
2 from gv$buffer_pool_statistics
3 where db_block_gets+consistent_gets > 0;
select name,(1(physical_reads/(db_block_gets+consistent_gets)))*100
*
ERROR at line 1:
ORA-00911: invalid character
SQL>
However that problem only manifests itself on my windows sqlplus, because when I run sqlplus on the server via putty and copy and paste the query putty converts the chr(49837) to chr(45) for me.
You posted a query with a symbol that will give the error you got.
What are the odds on you getting that error at that point for a reason other than the obvious one?
|
|
|
|
| Re: buffer_pool_statistics [message #512154 is a reply to message #512151] |
Fri, 17 June 2011 04:26 |
cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Actually I take one thing back. You didn't remove the dodgy character. You added a normal dash next to it.
I see what you were trying to do.
However I think you should have commented the post in question to explain exactly what you did.
And actually I think you just shouldn't have bothered since mine and Michel's subsequent posts make it fairly clear that there is an invisble character there.
There's enough confusion on this thread as it is without back-editing.
The simple fact that both me and Michel missed what you did there should be proof enough that your actions confused things further rather than make them clearer.
|
|
|
|
|
|
|
|
| Re: buffer_pool_statistics [message #512197 is a reply to message #512186] |
Fri, 17 June 2011 09:16 |
cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Mahesh Rajendran wrote on Fri, 17 June 2011 13:46
The one that failed is exact copy of yours.
I edited the other. Spaces around Minus had nothing to do with it. There were some issues with the characters around ( and minus.
For the sake of clarity, that was a copy and paste of Sriram's ammended version of the query.
The orginal just had a chr(49837) between the 1 and the bracket. That character looks like a minus in some editors but isn't.
Orafaq doesn't display it but does store it.
So when you copy and paste it into an editor that recognizes the character it is displayed.
Sriram went and updated his post to add a real minus symbol - chr(45) - next to the chr(49837). So here in orafaq his query looks like it just has one minus symbol, but when you paste it into other things you suddenly see two. As illustrated above.
Remove the chr(49837) and the query works. Leave it in and you get ORA-00911: invalid character.
Unless you paste it into something that converts chr(49837) into chr(45).
At that point you will get a different error as two chr(45)s together are a comment:
SQL> select name,(1--(physical_reads/(db_block_gets+consistent_gets)))*100
from gv$buffer_pool_statistics
where db_block_gets+consistent_gets > 0; 2 3
from gv$buffer_pool_statistics
*
ERROR at line 2:
ORA-00907: missing right parenthesis
SQL>
Above was done via putty which converts chr(49837) to chr(45) as previously stated.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
