orawin.info

Well now here’s an odd thing. In an otherwise frankly insulting article supposedly about visa fraud our old friend Don Burleson refers to Oracle Corporations salary survey for Oracle professionals which apparently shows US DBAs earning $97k on average whilst DBA staff in the rest of the world were close to about half of that salary. In a global economy this seems more than a little unlikely. However there are some pretty good reasons to take the salary survey with more than a little salt. As any graduate of a mathematical discipline (including computer science and the like) ought to know drawing statistical conclusions from survey data is notoriously difficult since the sample sizes tend to be small and the population non random. In this case as well it’s a cause for some concern that the source of the survey is a company looking to promote its own training.

In this case Oracle don’t publish the raw survey data – which is a shame – and they quote different figures for the responses to the survey which isn’t encouraging. On the front page we see

The survey was open to all Oracle Certified Associates (OCA), Professionals (OCPs), Masters (OCMs) and Experts (OCEs) worldwide as well as those not currently certified. This salary survey features data compiled from 2,655 respondents in more than 60 countries.

Whereas on the salary by region page we see

We had respondents from over 95 countries worldwide

This immediately suggests to us that the sample size could be quite small for a number of nations (on average taking the smaller number that’s 44 respondents per country, taking the figure of 95 its 27). It is of course likely that the figures for the US, UK, India, Germany,Japan etc may be quite large with other countries poorly represented. because Oracle don’t publish the data set we don’t know.

In addition there are some quite interesting breakdowns on the Oracle Website. In EMEA for example just 31% of respondents work for companies that employ more than 5,000 people, in the US that figure is over 50%. It’s not unreasonable to suppose that candidates who work at larger organisations with more employees, more databases and more money likely earn larger salaries than those who work at smaller ones.

Similarly there’s an oddity in the EMEA figures regarding experience where we are expected to believe that on starting out (0-2 years) an administrator can expect a salary of $43k, but after that first job move, obtaining certifications and showing their aptitude (3-4 years) the salary average drops to just $34k.

These sorts of oddities make me rather suspicious of the pretty graphs on the Oracle education site, but probably the killer for me is the fact that (like other governments around the world) the US Govt maintains statistics on employment. You can find these at http://www.bls.gov/oes/current/oes_nat.htm#15-0000 where we have a nice category for Database Administrators and an estimated mean salary of $75k. The breakdown of these figures is almost certainly publicly available.

I’m in the later stages of implementing Oracle Enterprise Manager 11g for a customer. Right now there are rather too many metric collection errors for either myself or the customer to be truly happy with. There is remarkably little other than this post by Oracle’s Werner.de.Guyter on how to deal with these. Unfortunately whilst Werner’s post is definitely the place to start, in practice life isn’t always quite so simple.

As the Metric Collection error page in EM suggests Metric Collection errors occur usually when there is a misconfiguration error on the target. Strictly however Oracle Enterprise Manager metric collection errors represent a failure by a management agent to collect a defined metric correctly. This error will remain until the metric is successfully collected. In the meantime no metric information will be available for Enterprise Manager to use. It should be obvious that the way to “clear” a metric collection error is to resolve the issue causing the error and to recollect the metric correctly.

 Metric Collection errors are generally down to the following reasons:

• Configuration Error as described above
• Temporary Collection Errors
• Oracle Bugs

 Details about metric collection errors are available in the Enterprise Manager repository table MGMT_CURRENT_METRIC_ERRORS  which reports the detailed error message and other useful information to help resolve the error.

UPDATE: As pointed out in the comments (thanks Rich) the table contains the raw data on the error. The view MGMT$METRIC_ERRORS_CURRENT is a better starting point. In fact the report I usually use is

select
  target_type
, target_name
, metric_name
, coll_name
, collection_timestamp
, error_message
from
  MGMT$METRIC_ERROR_CURRENT
ORDER BY collection_timestamp desc;

In general configuration errors – for example missing or incorrect passwords can be resolved by selecting the “Monitoring Configuration” link at the foot of the home page for the target being monitored. In some cases the error message shown in the report will indicate a misconfiguration in the target itself – for example an empty opmn.xml file in an HTTP Server Oracle Home. In such cases the fix is to correct the product misconfiguration.

In some cases – most notably in the case of the SOA management pack - metric collections are configured to collect data whilst the target is down. In such cases the metrics will often error precisely due to the unavailability of the Target. In this case a potential fix might be to set the CollectWhenDown property of the metric in the $AGENT_HOME/sysman/admin/metadata/<target_type>.xml configuration file to false. Doing so is unsupported by Oracle and so should be considered only in cases where metric errors are obscuring the overall health status of the Oracle infrastructure that is being monitored. A supported fix is to disable the metric collection entirely.

At the moment I have come across 2 candidates for Oracle Bugs which I am working with Oracle Support

 1)     When collecting LDAP information about the ldap database Oracle returns 2 identical rows one for each instance. This causes the metric collection to fail with repeating key error. This has been worked around by modifying the SQL that collects the metric to reurn unique records. 

2)     An SSO target running on a clustered database has been discovered without a database – this leads to a number of metric collection errors. Unfortunately it is not possible to manually enter SSO Database details via the enterprise manager interface for this target type  

Forcing a metric collection and upload

Once the condition has been resolved then the issue should be resolved at the next metric collection. In some cases this maybe as many as 24 hours away. To force a metric collection – and to check that the fix has been effective follow the below procedure:

1)     Determine Target Name

2)     Determine Target Type

3)     Determine Collection Name

These can be determined by logging onto the server and running

$AGENT_HOME/bin/emctl status agent scheduler (|grep part of target name)

 or from the Target Name, Target Type and Coll Name columns in the MGMT_CURRENT_METRIC_ERRORS table

 The collection can be manually run by issuing

$AGENT_HOME/bin/emctl control agent runCollection target_name:target_type collection_name

$AGENT_HOME/bin/emctl upload agent

I follow @oracledatabase on Twitter for obvious reasons. They tweeted a “case study” last week on the use of Advanced Compression to save money. You can find the case study here The end customer migrated from MSSQL to Oracle for a low terabytes size datawarehouse. Unfortunately we don’t get details of the old hardware or setup, but we do discover that the new hardware consists of a 16 processor AIX system and that 1.5tb of the available 2.75tb disk space is used. (and that a 2:1 compression ratio is achieved so we get a current saving in disk space of 1.5tb approximately).

The tweet chooses to major on “Customer migrates from #ms_sql_server and gains cost savings with #Oracle Advanced Compression.”  Cost savings are indeed mentioned in the white paper, although it is difficult to see that a 2:1 compression ratio is likely to significantly outperform NTFS compression which can of course be used transparently with the old technology, and in fact there are strong indications that the driver was strategic rather than cost sensitive.

I don’t particularly have any beef with the case study, though it isn’t the strongest case study I’ve ever seen. I do have a beef with the cost savings argument. The Advanced Compression option costs $156,000 for 16 processors for the first year which works out at approximately $100k per terabyte saved. That sort of sum of money will buy you an extraordinary amount of storage. In addition you’ll be paying $34k per year each year to offset against the reduced storage administration time needed each year. I’d suggest that if you are spending $34k per year on storage management time for a 1.5tb database then you’ve got something badly wrong.

This is just a short update on the prerequisites, especially firewall port requirements that you will need to ensure agent deployments in Enterprise Manager Grid Control work correctly. This is not well documented in the install manual. Chapter 9 of the advanced installation guide contains the basic requirements.
It however misses out some basics. This method of deployment does the following:

1) ssh and ping the target and run some prerequisite checks.
2) scp the install across
3) ssh across and run the install
4) do target discovery
5) setup secure comms.

Consequently the following needs to be allowed through your firewalls.
ssh, ping, oms to agent comms, agent to oms comms on both the secure and unsecured upload ports.

In addition we found that you’ll need to edit userPaths.properties in $EM_DOMAIN/sysman/prob/resources for the correct Linux paths to sudo and rsh etc to be picked up.

Sometimes I wonder about our industry. We seem so keen to promote silver bullets and technology solutions to what are at heart human problems. This blog post by an excellent technologist at Oracle rather illustrates the problem for me.

The British security services, rather embarrasingly, hired a spook who turned out to be guided by voices in his head. He set about obtaining information on how the security services work and tried to sell it to the Dutch.

The parting shot from the blog

Of course this story could have been very different if those documents had been protected with an information rights management solution like Oracle’s. Oracle IRM is a perfect technology to allowing national security agencies to protect their most valuable data

Of course the story would have been different. He’d have used non-technological techniques. Or different technological techniques. IRM is a very clever and very useful technology, but when your problem is essentially a human resources one – “How on earth did we get the recruitment so wrong” – then encrypting documents is hardly going to change the fundamental problem. Sadly it may, and likely will, lead people to have over-confidence in their security.

Backward Compatability is a very big technological driver. Especially for software companies like Oracle that have customers with a large installed base of users who rely on expensive functionality that they have purchased. When companies invest in software it is usually for very good business reasons and they expect that software to carry on doing at least what it did in the previous version as time progresses.

In 2006 I argued (www.niall.litchfield.dial.pipex.com/ManageabilityManifesto.pdf) that Oracle Corp had removed some existing functionality from Standard Edition users of the database product by making certain features that had previously been available in a management pack for SE users (and the base product for that matter).  I started a petition (that had very little effect, the relevant product manager said they preferred to hear direct from customers – presumably in sales meetings – and not from users and advocates of the product ) and generally made a bit of a fuss.

Four years later and the same team have committed a similar error again. If you visit http://www.oracle.com/oms/enterprisemanager11g/webcast-067871.html you can hear Charles Phillips and others talking about Oracle Enterprise Manager 11g. What’s frustrating for me is that some of what is described (for example the management packs for Oracle E-Business Suite described in BreakOut Session 1 @8:33 , but especially 10:14-12:44) exists in the 10g product, but will not install on and is not certified for 11g. I can’t speak to whether an upgraded installation will work correctly, but I’m not encouraged. It turns out that this subject was covered on Stephen Chan’s excellent blog at http://blogs.oracle.com/stevenChan/2010/06/oem_11g_amp_acmp_plans.html. It rather looks as if to get features that were working in 10g Enterprise Manager, you’ll have to wait for a future release of the management pack – and moreover that possibly this project has only just started.

One of the nice things about conferences such as ODTUG http://odtugkaleidoscope.com/technicalsessions.html is the networking and ideas exchange that they facilitate. One of the nice things about Twitter is that it allows people who aren’t there to catch up on these two conference aspects. Even @doug_conference who doesn’t use Twitter recognizes this. Anyway, @alexgorbachev commented on Tom Kyte’s presentation at odtug

#odtug quote of the day “overhead of instrumenting your code is negative” paraphrased by me but based on Tom Kyte

Now I wasn’t there, but I have heard Tom say that instrumentation isn’t an overhead because it is information that you require. In that sense I agree with the comment and Alex’s paraphrase. However most people consider overhead to mean something like

Overhead sometimes describes the amount of processing time the installation of a particular feature will add to the amount already required by the program. WhatIs.com

In this sense I disagree with Alex, and it was in this sense that I replied to him.

I disagree, it is positive, but only for code that doesn’t matter.

That is to say where you have uninstrumented code that already completes the required task in an adequate timeframe then instrumenting that code that will add some execution time. This is in the second sense  ’overhead’. The reason I say that that code doesn’t matter is that it is code that you shouldn’t be tuning (yet) anyway. If your code achieves it’s task in its expected time frame then it is efficient enough.

The key reason I suspect that Tom said whatever it was that he did is in the, more usually encountered in financial accounting, following definition of overhead

The ongoing administrative expenses of a business which cannot be attributed to any specific business activity, but are still necessary for the business to function InvestorWords.com

In finance terms these tend to be things like rent,insurance and so on. In software terms instrumentation fits this definition beautifully. It doesn’t directly contribute to achieving any specific code outcome. it’s there so that, should it be required,  then the desirable goal of tuning specific tasks can be achieved. As in a company without insurance you can operate without it and there is an extra cost to having it. When the time comes however it is invaluable. As with the financial world overheads like instrumentation should be made as efficient as possible, they shouldn’t be skipped.

I’ve just taken a quick look at the Calendar and it appears to be 2010. However I’m still seeing, and being asked, to refresh a number of test databases using schema mode exports. This can present a challenge because there are a number of database objects that typically aren’t created by a schema mode import. In addition a number of the applications I see utilize various schemas and so there can be dependency issues when imports are done. I thought it might be useful therefore to set out my approach to this problem.

The list of objects that are not created by a schema mode import is as follows:

• Tablespaces
• Profiles
• Database Links
• Public Synonyms
• Roles

In addition if the users are dropped and recreated we also need the following information

• User information including password and tablespaces
• Role Grants
• System Grants
• Object Grants

Since Oracle 9i we can take advantage of the supplied package DBMS_METADATA to obtain all this information from the source database. I therefore run the script shown below to generate 2 scripts at the source, one to be run before the schema mode import (handily called preImport.sql) and one to be run after the import has finished. This script needs to be run as a privileged user (I usually use SYSTEM). The generated scripts may need to be edited for pathnames for tablespaces if the tablespaces needed do not exist on the target. The sequence then becomes

• drop the affected users
• run the preImport.sql
• run the import
• run postImport. sql

These days however I tend to recommend moving to datapump wherever possible which has the following advantages.

1. It’s faster
2. It’s resumable
3. It doesn’t require a dumpfile at all if you use the network option.

/*

Script to create sql scripts
for a user mode export to succeed

Requirements
Execute on dbms_metadata,utl_file
Create any directory

Version 1.0
Niall Litchfield 8/7/2008
based on extract_schema schema compare utility 2003.

*/

accept prescript default 'preimport.sql' prompt 'Enter Filename for preimport ddl [preimport.sql] '
accept sqldir default '/u02/support/dba/sql' prompt 'Enter directory on db server to write ddl file to [c:\scripts]'
accept postscript default 'postimport.sql' prompt 'Enter Filename for post import ddl [postimport.sql] '

set verify off

create or replace directory scripts
as
'&sqldir';

declare
preScriptHandle UTL_FILE.FILE_TYPE;
postScriptHandle UTL_FILE.FILE_TYPE;
DDL clob;
strDDL varchar2(32767);
line varchar2(2000);
NO_GRANT_FOUND exception;
OBJECT_NOT_FOUND exception;
PRAGMA EXCEPTION_INIT(NO_GRANT_FOUND,-31608);
PRAGMA EXCEPTION_INIT(OBJECT_NOT_FOUND,-31603);

begin
preScriptHandle := UTL_FILE.FOPEN('SCRIPTS','&prescript','w');
postScriptHandle := UTL_FILE.FOPEN('SCRIPTS','&postscript','w');

/*+ set session transforms */

DBMS_METADATA.SET_TRANSFORM_PARAM(
DBMS_METADATA.SESSION_TRANSFORM,
'STORAGE',
false);

DBMS_METADATA.SET_TRANSFORM_PARAM(
DBMS_METADATA.SESSION_TRANSFORM,
'SQLTERMINATOR',
true);

/*+ get tablespace details */
utl_file.put_line(preScriptHandle,'-----------------------------');
utl_file.put_line(preScriptHandle,'----- Start Tablespaces -----');
utl_file.put_line(preScriptHandle,'-----------------------------');

for t in (select tablespace_name from dba_tablespaces where tablespace_name not in ('SYSTEM','SYSAUX')
and contents not in ('UNDO','TEMPORARY')) loop
DDL := dbms_metadata.get_ddl('TABLESPACE',t.tablespace_name);
strDDL := trim(dbms_lob.substr(DDL,32767));
utl_file.put_line(preScriptHandle,strDDL);
end loop;
utl_file.put_line(preScriptHandle,'-----------------------------');
utl_file.put_line(preScriptHandle,'------ End Tablespaces ------');
utl_file.put_line(preScriptHandle,'-----------------------------');
BEGIN

/*+ Profiles */

utl_file.put_line(preScriptHandle,'-----------------------------');
utl_file.put_line(preScriptHandle,'------ Start Profiles -------');
utl_file.put_line(preScriptHandle,'-----------------------------');

for pr in (select distinct profile from dba_profiles) loop -- multiple rows per profile
DDL := dbms_metadata.get_ddl('PROFILE',pr.profile);
strDDL := trim(dbms_lob.substr(DDL,32767));
utl_file.put_line(preScriptHandle,strDDL);
end loop;
exception
when OBJECT_NOT_FOUND THEN NULL; -- no profile information extracted.
WHEN OTHERS THEN
utl_file.fclose(preScripthandle);
utl_file.fclose(postScripthandle);
RAISE;
end;

utl_file.put_line(preScriptHandle,'-----------------------------');
utl_file.put_line(preScriptHandle,'------- End Profiles --------');
utl_file.put_line(preScriptHandle,'-----------------------------');

/*+ Public Database Links */
utl_file.put_line(postScriptHandle,'-----------------------------');
utl_file.put_line(postScriptHandle,'------ Start db links -------');
utl_file.put_line(postScriptHandle,'-----------------------------');

for dbl in (select db_link from dba_db_links where owner = 'PUBLIC') loop
DDL := dbms_metadata.get_ddl('DB_LINK',dbl.db_link,'PUBLIC');
strDDL := trim(dbms_lob.substr(DDL,32767));
utl_file.put_line(postScriptHandle,strDDL);
end loop;
utl_file.put_line(postScriptHandle,'-----------------------------');
utl_file.put_line(postScriptHandle,'------- End db links --------');
utl_file.put_line(postScriptHandle,'-----------------------------');

/*+ Public synonyms */

utl_file.put_line(postScriptHandle,'-----------------------------');
utl_file.put_line(postScriptHandle,'------ Start of Synms -------');
utl_file.put_line(postScriptHandle,'-----------------------------');

for syn in (select synonym_name from dba_synonyms where owner = 'PUBLIC' and table_owner not in (
'ANONYMOUS'
, 'CTXSYS'
, 'DBSNMP'
, 'DIP'
, 'DMSYS'
, 'EXFSYS'
, 'LBACSYS'
, 'MDDATA'
, 'MDSYS'
, 'MGMT_VIEW'
, 'ODM'
, 'ODM_MTR'
, 'OLAPSYS'
, 'ORDPLUGINS'
, 'ORDSYS'
, 'OUTLN'
, 'PERFSTAT'
, 'SI_INFORMTN_SCHEMA'
, 'SYS'
, 'SYSMAN'
, 'SYSTEM'
, 'TRACESVR'
, 'TSMSYS'
, 'WKPROXY'
, 'WKSYS'
, 'WK_TEST'
, 'WMSYS'
, 'XDB'
)) loop
DDL := dbms_metadata.get_ddl('SYNONYM',syn.synonym_name,'PUBLIC');
strDDL := trim(dbms_lob.substr(DDL,32767));
utl_file.put_line(postScriptHandle,strDDL);
end loop;
utl_file.put_line(postScriptHandle,'-----------------------------');
utl_file.put_line(postScriptHandle,'------- End of Synms --------');
utl_file.put_line(postScriptHandle,'-----------------------------');

/*+ Roles */
utl_file.put_line(preScriptHandle,'-----------------------------');
utl_file.put_line(preScriptHandle,'------ Start of Roles -------');
utl_file.put_line(preScriptHandle,'-----------------------------');

for rl in (select role from dba_roles where role not in (
'CONNECT'
, 'RESOURCE'
, 'DBA'
, 'SELECT_CATALOG_ROLE'
, 'EXECUTE_CATALOG_ROLE'
, 'DELETE_CATALOG_ROLE'
, 'EXP_FULL_DATABASE'
, 'IMP_FULL_DATABASE'
, 'LOGSTDBY_ADMINISTRATOR'
, 'AQ_ADMINISTRATOR_ROLE'
, 'AQ_USER_ROLE'
, 'DATAPUMP_EXP_FULL_DATABASE'
, 'DATAPUMP_IMP_FULL_DATABASE'
, 'GATHER_SYSTEM_STATISTICS'
, 'RECOVERY_CATALOG_OWNER'
, 'SCHEDULER_ADMIN'
, 'HS_ADMIN_ROLE'
, 'GLOBAL_AQ_USER_ROLE'
, 'OEM_ADVISOR'
, 'OEM_MONITOR'
, 'WM_ADMIN_ROLE'
, 'JAVAUSERPRIV'
, 'JAVAIDPRIV'
, 'JAVASYSPRIV'
, 'JAVADEBUGPRIV'
, 'EJBCLIENT'
, 'JMXSERVER'
, 'JAVA_ADMIN'
, 'JAVA_DEPLOY'
, 'CTXAPP'
, 'XDBADMIN'
, 'XDB_SET_INVOKER'
, 'AUTHENTICATEDUSER'
, 'XDB_WEBSERVICES'
, 'XDB_WEBSERVICES_WITH_PUBLIC'
, 'XDB_WEBSERVICES_OVER_HTTP'
, 'SPATIAL_WFS_ADMIN'
, 'ORDADMIN'
, 'OLAPI_TRACE_USER'
, 'OLAP_XS_ADMIN'
, 'OLAP_DBA'
, 'CWM_USER'
, 'OLAP_USER'
, 'WFS_USR_ROLE'
, 'SPATIAL_CSW_ADMIN'
, 'CSW_USR_ROLE'
, 'WKUSER'
, 'MGMT_USER'
, 'SPREPORT_ROLE'
, 'OWB$CLIENT'
, 'OWB_DESIGNCENTER_VIEW'
, 'OWB_USER'
, 'APEX_ADMINISTRATOR_ROLE')
)loop
DDL := dbms_metadata.get_ddl('ROLE',rl.role);
strDDL := trim(dbms_lob.substr(DDL,32767));
utl_file.put_line(preScriptHandle,strDDL);
end loop;

utl_file.put_line(preScriptHandle,'-----------------------------');
utl_file.put_line(preScriptHandle,'------- End of Roles --------');
utl_file.put_line(preScriptHandle,'-----------------------------');

/*+ User Information */
for u in
(select username from dba_users where username not in
( 'ADAMS'
, 'ANONYMOUS'
, 'BLAKE'
, 'CLARK'
, 'CTXSYS'
, 'DBSNMP'
, 'DIP'
, 'DMSYS'
, 'EXFSYS'
, 'HR'
, 'JONES'
, 'LBACSYS'
, 'MDDATA'
, 'MDSYS'
, 'MGMT_VIEW'
, 'ODM'
, 'ODM_MTR'
, 'OE'
, 'OLAPSYS'
, 'ORDPLUGINS'
, 'ORDSYS'
, 'OUTLN'
, 'PERFSTAT'
, 'PM'
, 'QS'
, 'QS_ADM'
, 'QS_CB'
, 'QS_CBADM'
, 'QS_CS'
, 'QS_ES'
, 'QS_OS'
, 'QS_WS'
, 'SCOTT'
, 'SH'
, 'SI_INFORMTN_SCHEMA'
, 'SYS'
, 'SYSMAN'
, 'SYSTEM'
, 'TRACESVR'
, 'TSMSYS'
, 'WKPROXY'
, 'WKSYS'
, 'WK_TEST'
, 'WMSYS'
, 'XDB'
) ) loop

utl_file.put_line(preScriptHandle,'-----------------------------');
utl_file.put_line(preScriptHandle,'-- Start of User '||u.username);
utl_file.put_line(preScriptHandle,'-----------------------------');

begin
-- GET USER info
DDL := dbms_metadata.get_ddl('USER',u.username);
strDDL := trim(dbms_lob.substr(DDL,32767));
utl_file.put_line(preScriptHandle,strDDL);
end;

-- grants
utl_file.put_line(postScriptHandle,'-----------------------------');
utl_file.put_line(postScriptHandle,'----- Start Role Grnts ------');
utl_file.put_line(postScriptHandle,'-----------------------------');

begin
ddl := DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT',u.username);
strDDL := trim(dbms_lob.substr(DDL,32767));
utl_file.put_line(postScriptHandle,strDDL);
exception
when NO_GRANT_FOUND THEN NULL;
WHEN OTHERS THEN
utl_file.fclose(preScripthandle);
utl_file.fclose(postScripthandle);
RAISE;
end;
utl_file.put_line(postScriptHandle,'-----------------------------');
utl_file.put_line(postScriptHandle,'------ End Role Grnts -------');
utl_file.put_line(postScriptHandle,'-----------------------------');

utl_file.put_line(preScriptHandle,'-----------------------------');
utl_file.put_line(preScriptHandle,'----- Start Sys Grnts ------');
utl_file.put_line(preScriptHandle,'-----------------------------');
begin
ddl := DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT',u.username);
strDDL := trim(dbms_lob.substr(DDL,32767));
utl_file.put_line(preScriptHandle,strDDL);
exception
when NO_GRANT_FOUND THEN NULL;
WHEN OTHERS THEN
utl_file.fclose(preScripthandle);
utl_file.fclose(postScripthandle);
RAISE;
end;
utl_file.put_line(postScriptHandle,'-----------------------------');
utl_file.put_line(postScriptHandle,'------ End Sys Grnts -------');
utl_file.put_line(postScriptHandle,'-----------------------------');

utl_file.put_line(preScriptHandle,'-----------------------------');
utl_file.put_line(preScriptHandle,'-- End of User '||u.username);
utl_file.put_line(preScriptHandle,'-----------------------------');
end loop;

utl_file.fclose(preScriptHandle);
utl_file.fclose(postScriptHandle);

end;
/

Starting with 11.2 which is now available for all the major platforms the behaviour of the CREATE TABLE statement has changed. Specifically, as introduced here, when creating a conventional table in a database created with the default options then the initial segment is not created until the first row is inserted into the table.

The good part of this is well described in the link above, specifically

The advantages of this space allocation method are the following:

•A significant amount of disk space can be saved for applications that create hundreds or thousands of tables upon installation, many of which might never be populated.

•Application installation time is reduced

In other words this is a very nice enhancement for applications like, for example, the Oracle E-Business Suite which typically take multiple hours to install and install a large number of objects that will never be used by any given customer.

I am however already aware of 2 potential downsides to this. The first downside will also apply to exactly the ERP type of application that the designers apparently had in mind for this feature. This is well described in Metalink Note 1050193.1 as follows:

The sequences created as default ( start with 1 increment by 1) are not staring with ’1′ when used in insert query script, Instead they are starting with 2 or 4.
This behaviour of sequences is seen in version Oracle11gR2, not seen in earlier versions

In other words if you specify a sequence and a start with value to generate a range of IDs to be used to create a primary key, the sequence will effectively not start with the “START WITH” value. This is likely a minor inconvenience but one to be aware of if you subscribe to the artificial key design paradigm.

The second is described in this forum thread and is a result of the new default behaviour only applying to the Enterprise Edition of Oracle. Should you create an 11.2 database using the default options and later wish to export the data and import into a standard edition database (We have a number of clients for whom this is not a hypothetical case, they’ve done exactly this in the past) then there isn’t currently a satisfactory method to do this for schemas of any size. The issue is compounded currently by the fact that the Enterprise only status of this feature is not correctly documented.

All in all I think I’d rather that the default value for the initialisation parameter DEFERRED_SEGMENT_CREATION was set to false with the option for people who regularly create schemas with large numbers of never to be used tables and for whom application software installation time is a significant concern to set it to TRUE. It just seems like one of those cases where adopting a new option as a default in the very first release in which it is available is a less than optimal idea.

I just stumbled across this fine example of the art of writing news articles when you clearly don’t understand the subject. The article has a date of 2 days ago on the front page, though curiously a September 2009 date on the article. The article purports to discuss the omotion feature of Oracle Rac One-Node. My highlights from the news article

While details from Oracle are sketchy, it’s a safe bet that Omotion performs instance relocation is  a fashion similar to it’s predecessors, Savantis Systems with their DB-Switch invention

Which I can’t help but read as “I don’t know how this works, but there is another product that sounds similar” and

For example, Oracle Omotion likely allows relocation to any OS with the same Endian format, including AIX, Linux, Solaris, or HP/UX.

The internet is a dangerous place folks, be careful out there.