connect as sysdba or dba ... not priviledged user

SQL Optimizer SQL Optimizer for Oracle
1

Hi,

When i want to login in SQL
Your current login to any database (as sysdba or dba priviledge) I get message:
does not have access to SYS.V_$SQLAREA
and SYS.V_$SQLTEXT_WITH_NEWLINES (or SYS.V_$SQLTEXT)
that are required to perform inspection.

I read that this might be connected with “O7_DICTIONARY_ACCESSIBILITY” parameter,

Regardless it is false when I connect with sysdba privilege it should not influence in any way.

Please help.

Mine conf:
Toad 11.5 DBA SUite
SQL Optimizer 8.6
Win XP SP3 x86
Oracle home 10.2.0.1 client (used as TOAD ORALE_HOME)
(attack database 10.2.0.5 x64 Windows)

Here is the output of SQL Tracker (when create a new connection with sys):

Timestamp: 6.9.2012 11:22:02

Successful logon (0x102bcd30): [SYS@HCPRO]

Timestamp: 6.9.2012 11:22:02

declare cursor NlsParamsCursor is SELECT * FROM nls_session_parameters;begin SELECT Nvl(Lengthb(Chr(16777216)), Nvl(Lengthb(Chr(65536)), Nvl(Lengthb(Chr(256)), 1))), Nvl(Lengthb(Chr(1)), 1) INTO :MaxCharLength, :MinCharLength FROM dual; for NlsRecord in NlsParamsCursor loop if NlsRecord.parameter = ‘NLS_DATE_LANGUAGE’ then :NlsDateLanguage := NlsRecord.value; elsif NlsRecord.parameter = ‘NLS_DATE_FORMAT’ then :NlsDateFormat := NlsRecord.value; elsif NlsRecord.parameter = ‘NLS_NUMERIC_CHARACTERS’ then :NlsNumericCharacters := NlsRecord.value; elsif NlsRecord.parameter = ‘NLS_TIMESTAMP_FORMAT’ then :NlsTimeStampFormat := NlsRecord.value; elsif NlsRecord.parameter = ‘NLS_TIMESTAMP_TZ_FORMAT’ then :NlsTimeStampTZFormat := NlsRecord.value; end if; end loop; SELECT NLS_CHARSET_ID(cast(:Charset as Varchar2(50))) INTO :CharsetId FROM dual;end;

MaxCharLength=[1]
MinCharLength=[1]
NlsDateLanguage=[‘AMERICAN’]
NlsDateFormat=[‘DD-MON-YY’]
NlsNumericCharacters=[’.,’]
NlsTimeStampFormat=[‘DD-MON-RR HH.MI.SSXFF AM’]
NlsTimeStampTZFormat=[‘DD-MON-RR HH.MI.SSXFF AM TZR’]
Charset=[‘AL16UTF16’]
CharsetId=[2000]

Elapsed time: 0.000

Timestamp: 6.9.2012 11:22:03

select /*+ RULE */ USER
from SYS.DUAL

Elapsed time: 0.016

Timestamp: 6.9.2012 11:22:03

Logout (0x102bcd30): [SYS@HCPRO]

Rg
Damir Vadas
damir.vadas@prouno.hr

Message was edited by: damir.vadas_531

2

Hi,

You are right that when you connect as sysdba, you should have privilege on your SYS objects. I try on my side but was not able to repeat the problem in version 8.6.0.

Can you please try log in to Toad using your user and connecting as sysdba, then try run this SQL to make sure that you can access the required view:

select * from SYS.V_$SQLAREA;

If you can select from SYS.V_$SQLAREA but SQL Optimizer still complain missing privilege on SYS.V_$SQLAREA when connecting with the same user using sysdba, then it would probably be a problem in SQL Optimizer. Please then contact Support to create a Support Case for you so we can follow up from there.

Thanks,
Alex