Oracle – Empty v$asm_diskgroup with SQLPlus

oracleoracle-12coracle-asmsqlplus

I have an Oracle Database instance installed under an OL7. It is a 12c2.
Oracle GI also installed and my database has some files stored in ASM.

I am using sqlplus to connect to the database instance and query v$asm_diskgroup. My ORACLE_HOME points to the home of my database instance.

When I connect without specifying a "@CONNECTION" (e.g using: sqlplus user/name), then the view v$asm_diskgroup returns no rows.
When I connect specifying a @TNSNAME (e.g: sqlplus user/name@TNSNAME ), then the view v$asm_diskgroup returns data correctly.

Also, when I connect using sql developer, from another machine, the v$asm_diskgroup returns the rows.

Why are no v$asm_diskgroup rows returned when I use sqlplus without @CONNECTION, that is, the default logon?

Best Answer

You would need to connect to the ASM instance using a user ID that has the SYSASM system privilege:

connect scott as sysasm;

Balaz Papp's comment above is correct in the explanation why connected without a connect string is working. And his comment below is correct; any user could be granted query access to the view, when connected to the regular Oracle database instance.

Related Question