Experiments in Geekery

I'm working with a production database which is configured with shared servers for the web applications that use it, and end users should only ever connect with a dedicated server (though should != does). When users connect with the "basic" mechanism in SQL Developer instead of the TNS that we've set up, this can sometimes lead to their session essentially blocking an entire shared server for themselves.  This isn't a huge issue as there's plenty of resource to spare, but it makes my ASH reports look ridiculous - 90% Virtual Circuit Wait isn't uncommon and it's all because of these users. I've taken to using this query to identify the culprits and killing their sessions when they're idle:  select     n.SID   , n.SERIAL#   , s.name   , s.status   , n.LOGON_TIME   , n.username   , n.OSUSER   , w.event   , n.WAIT_TIME_MICRO/1000000 "Seconds" from     v$shared_server s   , v$circuit c   , v$session n ...

Here, as I mentioned before, are the scripts I'm using to automate thin cloning.  See the original posts here ( http://philsumner.blogspot.com/2014/06/clonedbpl-art-of-cloning-database.html ) and here ( http://philsumner.blogspot.com/2014/06/clonepl-thin-provisioning-of-oracle.html ). I welcome comments on these - I'm aware they're not perfect.  For my purposes, they do the job that I need them to do.  In doing this process I've already spotted various bits and pieces that should probably be changed.  Also please be aware that the conversion from script -> HTML hasn't been painless, so there may very well be mistakes around redirection that I haven't caught.  Use with care. I put these scripts here as references for not only myself, but as something that others may find useful. They're fairly well commented, I think.  If you have any questions, comment here or email me at blog -at- philsumner.co.uk I should also add that I modified a copy of clon...

Today I was asked if it was possible to import data for a specific table from a data pump export file, which was taken from when a table had a different structure to what it has now. e.g. when the export was taken, there were 10 columns.  Now there are 11. I thought that was simple, and I was sure I'd heard of this being done before.  Simply import using the "CONTENT=DATA_ONLY" parameter.  Right? Apparently not: Centos-/home/oracle/demo> impdp DEMO_USER/yyyyyyy directory=INBOUND dumpfile=demo_data.dmp logfile=impdpdemo.log content=data_only tables=xxxxxxxx Import: Release 10.2.0.4.0 - 64bit Production on Tuesday, 08 July, 2014 14:32:20 Copyright (c) 2003, 2007, Oracle.  All rights reserved. Connected to: Oracle Database 10g Release 10.2.0.4.0 - 64bit Production Master table "DEMO_USER"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded Starting "DEMO_USER"."SYS_IMPORT_TABLE_01":  xxxxxxxx/******** directory=INBOUND dumpfile...

One of the problems with Data Guard is that even after switching/failing over to the standby database, all clients need to be reconfigured to communicate with the new primary. The simplest way to achieve this (if using a centralised tnsnames.ora ), is to modify the file and  add the line " (FAILOVER=YES) ",  and either multiple ( DESCRIPTION=...) or (ADDRESS=...)  lines, one for each of the standby systems, in the order that the client should attempt connections . This tells the client to try each address in turn until one succeeds. Doing so provides basic failover functionality within Oracle Net, and will probably function correctly most of the time.  Though be aware - because both primary and standby can share the same instance name (e.g. the same SID, therefore the same connection string), there is potential for this simple scheme to go wrong. An example in an active data guard environment (where the standby is open read only): a temporary network issue cau...