Ora-30372 and what to do further

The error emerges at an mview creation. In my particular case this was an mview on a synonym, which indicated a remote view. The remote view is a subject of an access policy.
The whole issue is described on Oracle Support in the article Ora-30372: Fine Grain Access Policy Conflicts With Materialized View (Doc ID 604046.1).

Small tip: moving statistics

Here there is a small experiment. A colleague of mine stated that loading statistics through impdp is a horrible thing in comparison to simple export and import with the dbms_stats package.

begin
-- on the source
dbms_stats.create_stat_table('','','');
dbms_stats.export_database_stats('','','');

-- on the target
-- first impdp stats table
dbms_stats.import_database_stats('','','');
end;
/

I give it a try and I am very glad of the results. The main problem with exporting and importing the statistics with the Data Pump is the operation duration - using dbms_stats will take a fraction of time spent on the export and import with the Data Pump technology.

11g and sys.aud$

A short and relevant article on the maintenence of the audit structures (SYS.AUD$).
Also important news on usage of DBMS_AUDIT_MGMT, which may be used instead of the procedure from the previous link.

FIle watcher (external link)

A good article on the them at Eddie Awad's Blog.

On transparent switchover (external link)

Nice article by M.Klier on transparent switchover.

SQL*Plus and useful settings

SQL*Plus has a plenty of settings, but there are few really useful for me.
For formatting the output

• PAGESIZE - this controls the numbers of rows displayed between displaying headers. Usually I set it to 1000 (which means that I have got headers only once) and for some scripts for 0 (which means no headers at all)
• LINESIZE - this controls the line width in characters in which a row is fit in. Default 80 fits to default terminal settings. The best shape of data for me is the model 1 row per line, so I often modify this to get such result
• TRIMOUT - for cutting off unnecessary spaces filling lines
• COL FOR a - for formatting character columns
• COL FOR 999999 - for formatting number columns
• LONG - for setting how much of LOBs to show on the display - very useful especially with DBMS_METADATA.GET_DDL calls
• HEAD - for turning on and off the headers - important for scripts, when we want some values from within the database - then sqlplus -s /nolog with CONNECT command

For testing purposes

• FEEDBACK - for turning on and off a message at the end of display summarizing the number of affected rows or PL/SQL command status (SUCCESS|FAILURE)
• TIME - enables time in sqlprompt, which may be used as a marker for script performance
• TIMING - returns information about performance time for operations

For scripts creating scripts

• SQLPROMPT - cool for identifying terminals, when one works on plenty of environments (especially when some of them are production ones), but even cooler as "---> " when it comes to the creation of SQL scripts - prompt messages in the spool are seen as comments

For executing scripts

• TERMOUT - this works only if one calls commands from underlying script - very important for long-running scripts with plenty of output with enabled spooling, when on terminal output we need only command and information about success or error messages.
• SPOOL - for logging
• TRIMSPOOL - useful especially with connection to wide LINESIZE - in order to remove unnecessary spaces (with them logs become very large)
• SERVEROUTPUT - for turning on|off printing from PL/SQL to screen
• ECHO - turns on|off the lines with replaced &variables

FAILED_LOGIN_ATTEMPTS and DoS

From time to time I used to think about what is the purpose in setting FAILED_LOGIN_ATTEMPTS in profile for schema used by application (Login issues).
But now I am more convinced that it is good to set this setting also for applications.

Lately I was called to look at a database, which was meant to be used in series of application tests. The main problem was that the performance was so poor that it was ridiculous to try to run with any load at all. After a longer while I logged on and the first observation was that a lot of sessions waited on library cache lock. There was no sql_id for those sessions and the set of those waiting sessions continuously (though slowly) changed - I mean here new sids. No other activity actually.

After further investigation I realize what were those sessions with no sql_id - I assumed these were connection tries from an application server with wrong password set. In order to be sure I set another profile for all the accounts and after a while all problems have gone and the database returned to usable state.
So what really happened was the following:

• there were few hosts with application servers on board with some applications and the database - all those nodes created an integrated test environment
• there was a request for the database refresh and new password were applied
• passwords were changed to the new values, but partially - for 2 kinds of data sources (i.e. connecting to 2 schemas) there were old passwords left intact
• with start of application servers and applications deployed on them there show the connections to the database
• after a while the following situation established - there were so many the connection tries with wrong password that the library cache lock was continuously grabbed by one of those tries apparently in order to check password
• when I enabled the new profile with FAILED_LOGIN_ATTEMPTS set to 10, the 2 accounts were locked, but the rest of the database was available

As a conclusion it seems that connection to locked accounts do not need to acquire library cache lock (and probably any other blocking mechanisms service by shared pool structures) and thus does not put to the database time much, so it is clear that throwing the locked account exceptions is much more reliable than the wrong login or password exception. So (at least for 11g) the setting of the FAILED_LOGIN_ATTEMPTS parameter in a profile even though opens a schema for the DoS type attack, but secures the rest of the database - of course still there is no problem to lock all the accounts with the proper number of connection attempts with wrong password. But I suppose that the human error is more common than a malicious user and simple mistake in a password value may lead to unavailability of all the accounts on the database, which distributes the failure to all the applications dependent on any of the database accounts.