Usefull indexes

Do we actually need such indexes?

select owner,table_name,index_name 
  from all_indexes 
 where distinct_keys < 2
   and num_rows > 100;

Ezconnect

Today I noticed the reason why I have not bothered to use ezconnect method with sqlplus connections.

sqlplus system/oracle@localhost/orcl

I do not like to reveal my passwords on screen if possible.

sqlplus system@localhost/orcl

ERROR:
ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA

Additional quotes are needed with ezconnect, when I do not want to write the password on command line.

sqlplus system@\"localhost/orcl\"
Enter password:

or

sqlplus /nolog
SQL> conn system@"localhost/orcl"
Enter password:
Connected.

Seems like 11.2 client does not need NAMES.DIRECTORY_PATH= (ezconnect) line in sqlnet.ora anymore to use the method. 10.1, 10.2 and 11.1 version client installations need that line there.

extended audit_trail

Want to audit issued sql_text and binds in 11g database?

Documentation says that audit_trail parameter may have values

Parameter type String
Syntax AUDIT_TRAIL = { none | os | db [, extended] | xml [, extended] }

Trying

SQL> alter system set audit_trail='db, extended' scope=spfile;
alter system set audit_trail='db, extended' scope=spfile
*
ERROR at line 1:
ORA-00096: invalid value db, extended for parameter audit_trail, must be from
among extended, xml, db_extended, false, true, none, os, db

Uups, the documented one did not work. I will try out the first suggested one.

SQL> alter system set audit_trail='extended' scope=spfile;
alter system set audit_trail='extended' scope=spfile
*
ERROR at line 1:
ORA-32017: failure in updating SPFILE
ORA-00096: invalid value EXTENDED for parameter audit_trail, must be from among
NONE | OS | DB | DB,EXTENDED | XML | XML,EXTENDED

Uups, Oracle did it again. DB,EXTENDED seems a bit closer to the one documented.

SQL> alter system set audit_trail='DB,EXTENDED' scope=spfile;
alter system set audit_trail='DB,EXTENDED' scope=spfile
*
ERROR at line 1:
ORA-00096: invalid value DB,EXTENDED for parameter audit_trail, must be from
among extended, xml, db_extended, false, true, none, os, db

Back to the original error message. Yet another quess...

SQL> alter system set audit_trail='db_extended' scope=spfile;

System altered.

Yes I found it. After reboot sql_text and binds are collected.

Should the documentation say

Parameter type String
Syntax AUDIT_TRAIL = { none | true | false | os | db | db_extended | xml }

No. The problem is that issuing a String parameter as a quoted 'string'.

The correct way to do documented way

SQL> alter system set audit_trail=db, extended scope=spfile;

System altered.

This way also the new xml, extended is accepted, if someone likes it that way. Also some old (10.1) and alternative ways are also accepted. Although we should use only the documented ones.

SQL> alter system set audit_trail=true scope=spfile;
SQL> alter system set audit_trail=false scope=spfile;
SQL> alter system set audit_trail=db_extended scope=spfile;
SQL> alter system set audit_trail=db,extended scope=spfile;
SQL> alter system set audit_trail=db,  extended scope=spfile;
...

So minor issue, but should the ORA-00096 error messages be updated? Maybe. Compared to other product error messages the information Oracle gives in those are mostly understandable.

Anoying SQL Developer

Yet another new installation of SQL Developer. I am writing SQL. The query includes ANALYTIC functions not AGGREGATE ones. SQL Developer hits and adds some group by clause somewhere in between my clause. Getting rid of that.

Tools

Preferences...

Code editor

Completion Insight

unselect Autogenerate GROUP BY clause.

OK

I so wish this would be the default.

Lean

Just had a pleasure to participate a session with Jim Coplien. His earlier presentation about the main issues seems to be found also online. Need to listen that again later. MVC and DCI. Model and data in both the part closest to the storage seems to be the first words to name software architectures. Maybe also the Lean Architecture book to be read.