Skip navigation.

Grumpy old DBA

Syndicate content
Updated: 1 day 4 hours ago

adding NOT NULL columns to an existing table ... implications make me grumpy

Sat, 2014-04-12 07:56
This is DBA basics 101 in the oracle world but well also something that we grumpy DBA types forget from time to time.  We have an existing table in a schema that is populated with data.  Something like this say:

create table dbaperf.has_data ( column_one varchar2(10) not null, column_two number(10) not null);

insert into dbaperf.has_data(column_one, column_two) values('First row',13);
insert into dbaperf.has_data(column_one, column_two) values('Another',42); commit;

Now you need to add another column that is also NOT NULL.  Chris Date not happy the vendor implementations of the relational model allow null columns.  Be aware of any potential NULL columns in rows and handle them carefully ( IS null / IS not null ) to avoid messing up results.

But anyhow we are going to add in a new column that is NOT NULL.

How easy that is to do against an Oracle table depends on whether one is also supplying a DEFAULT value for the new column.  If you do not supply DEFAULT value what happens here?

 alter table dbaperf.has_data add ( column_three char(1) NOT NULL );

You get: ORA-01758: table must be empty to add mandatory (NOT NULL) column

To get around that you have to do this in three steps:
  • Add in the new column
  • Populate all the new columns with a value ( data migration )
  • Make the column NOT NULL
alter table dbaperf.has_data add ( column_three char(1) );

update dbaperf.has_data set column_three = 'X' where column_one = 'First row';
update dbaperf.has_data set column_three = 'X' where column_one = 'Another';

alter table dbaperf.has_data modify ( column_three NOT NULL );

Things get easier if you do this with a DEFAULT clause on the new column.  The problem is of course some columns have a reasonable default value others may not get any agreement for a default value.  A min and a max type column probably can have an easy default others not so much.

alter table dbaperf.has_data add ( column_four number(21,2) default 0 NOT NULL );

All of this discussion side steps the implications of adding a new column to a large existing table or partitioned table and fragging up the blocks ... that is a little beyond 101 for now.
Categories: DBA Blogs

another month almost gone ... another presentation done at NEOOUG

Fri, 2014-03-28 17:07
Geez this month really just flew by it started with my presentation at Hotsos 2014.  Pretty well attended waiting for final evaluation information.  Then work work work ...

This Friday ( today 3/28/2014 ) I did my Hotsos presentation again but this time at my local user group.  I added "just three more slides" to try to give some additional information on PGA and program connections to people aka developers coming in cold to this area. 

Somehow the three additional slides caused me to take an additional twenty minutes to deliver this information.  Lucky I was not given the hook ( sometimes it helps to be president ).  It was kind of funny even though I had just delivered the presentation at the beginning of the month I found myself looking at some of the slides that came up thinking "oh that is out of order here ( no it was not )" or even worse "oh geez what am I trying to connect with on this slide" ... yikes!

Registrations are starting to roll in for GLOC 2014 but the month of April is the critical one.  We would like to increase attendance by 33% percent ... time will tell!
Categories: DBA Blogs

some days it is harder to be grumpy than others ...

Tue, 2014-03-18 17:14
The official notification is out and the nomination that was submitted to recognize me as an Oracle Ace has been approved.  This is certainly an honor and along with my continued role at NEOOUG I am hoping to leverage this status to continue to help grow our user group and our yearly conference Great Lakes Oracle Conference ( GLOC )

Speaking of that our abstract selection has been finalized and you can see all of our great presentations and workshops from the link above.  We will have a session schedule out very soon.

Essentially we have four tracks DBA : Developer : Applicationss : BI/DW and each track has at least 1 session for each time slot and the DBA track having 2 sessions concurrently.

This conference is a great value with top presenters.  We have worked hard this year to give a stronger/deeper set of Applications and BI/DW offerings.  I could start rambling about our plans for next year but probably best to focus on 2014 for now!

The Grumpy Old DBA hopes to see everyone in Cleveland!

Geez oh man I read through briefly the pages in Oracle Ace agreement on how to display this logo hope I did not mess up my first attempt!  I can just see already "Grumpy Old DBA please report to the principals office immediately" ( hope not keep your fingers crossed for me ha ha )!
Categories: DBA Blogs

is this old school ... using dbms_shared_pool keep to pin plsql code in shared pool?

Fri, 2014-03-14 09:05
These days with the shared pool area getting so large many people ( including me ) tend to think of reserving/pinning plsql code into the shared pool as an old school "probably no longer necessary" approach.  Of course many 11g systems are now running with 4 gb shared pools ( and much more sometimes ).

In my recent hotsos session I polled the attendee's and ( kind of as expected ) confirmed that most people are not doing this any more.

However after I finished someone came up and recommended that I add in as material to my presentation taking a look at an oracle provided toolkit ( PIND ) that accomplishes this for you.  He noted that oracle ebiz suite deployments are perhaps particularly significant in maybe still needing this kind of setup.  ( So this information has been added to my presentation ).

Of course dbms_shared_pool.keep is the way to do it yourself and no shortage of web posts about doing this stuff yourself.

If you want to look at this oracle toolkit look at oracle doc ids 301171.1 and 311689.1
Categories: DBA Blogs

final version of my Hotsos 2014 presentation

Sat, 2014-03-08 12:38
This is the "really really" final version because it was updated and some additional references added even after the presentation was delivered.

Hoping to do it at Open World 2014 so ... I guess this is the early version of that presentation eh?

You can find it here: Three approaches to shared pool monitoring Hotsos 2014
Categories: DBA Blogs

So you think you have disabled ASMM/AMM oracle automatic SGA sizing behavior?

Sat, 2014-03-08 12:34
I polled my session attendee's at Hotsos 2014 ( Three approaches to shared pool monitoring ) and asked them who was using automatic stuff versus who was doing it all manually?

My guess is that there were about 100 people in the room and it looked like automatic "won" but not by much.  Maybe 55 percent automatic and 45 manual ... so there are still a whole bunch of people nervous about using that functionality.

My presentation was updated ( after delivering it ) to add in some additional doc id's and warnings ( both pro and con ) about manual memory settings.

Specifically worth noting is the un obvious fact that EVEN IF you have disabled oracle from doing SGA resizing operations automatically ... well it may do them anyhow.  Makes me a little grumpy eh?

So you can set SGA_TARGET to zero ( and/or MEMORY_TARGET to zero ) and still have oracle make decisions for you?  It turns out that after some point you have to set yet another hidden underscore parameter to tell the oracle software "yeah thanks but I really really mean it" ...


Oracle doc id 1269139.1 ... a pretty good writeup here ... really turn off ammasmm
Categories: DBA Blogs

managed to make it to Dallas sunday morning ... Hotsos 2014 is rolling

Mon, 2014-03-03 19:01
It was a little dicey sunday morning with weather in Ohio but I caught very early flight and made it down to Dallas well before noon.  Lots of turbulence in the airplane ride and bounced around some but all in all not bad.  Lots of people traveling ran into hitches one way or another but by the end of the day Monday most everyone seems to be here now.

Even better I had pretty good turnout for my session maybe even close to 1/2 the attendees dunno exactly but room was pretty full.  Presentation went pretty well only blanked out once and then jumped into next bullet point.  Actually all in all for as many times as I had practiced this thing and thought I was going to fail at various points I think it went over pretty well.

Managed one really cheesy comparison of the shared pool to a picture that I will probably get in trouble for eventually.

Besides some of the people I already knew I have met several other nice speakers and now much more relaxed with my presentation over with.  Even managed to update my presentation slightly with some material from this morning Kerry Osborne and Tim Gorman.
Categories: DBA Blogs

Pretty close to getting out speaker notifications for GLOC 2014 ...

Thu, 2014-02-27 18:51
Our official notifications should be out by mid week ( next Wednesday ) and we appreciate all of the people submitting abstracts.  Thank everyone of you very much from all of us involved in the conference.

Wow this is going to be another outstanding conference.  Darn going to have to not accept a large number of quality presentations also.  We do not have the time or room to run all the sessions that we would like to present.  So my apologies for that ... our only good choice is to grow the conference even larger in the future!

Now me personally just hoping for a semi quiet weekend to finish final preparations for Dallas/Hotsos 2014 and also hoping to have no problems flying down there sunday morning!

Stay tuned for more news on GLOC 2014!
Categories: DBA Blogs

emergency monitoring and forcing a flush of the shared pool ... use with caution ONLY if really needed

Sat, 2014-02-22 13:11
Flushing the shared pool has an impact on systems forcing at least the re parsing of sql statements and new ( perhaps even changed ) execution plans.  Use with caution on any production environment and test/test before deploying anything like this.

The usual approach for a system suffering from shared pool fragmentation and/or 4031 is to identify sql not using bind variables and consider implementing ( after logon session based database trigger best ) CURSOR_SHARING = FORCE ... but at times even that may need to be supplemented with some emergency monitoring and flushing.

This code below needs to be looked at and tested in your environment ( test system first ) ... may need setup and grants put in place to get it operational.  It currently is hard coded to flush when less than 256 mb is available as free memory ... could be kicked off every minute by some kind of scheduler or cron job or database job.

   v_free_space_meg NUMBER;
   SELECT round(bytes/1024/1024,0) INTO v_free_space_meg FROM v$sgastat
    WHERE name = 'free memory' AND pool =  'shared pool';
   IF ( v_free_space_meg < 256 ) THEN
      EXECUTE IMMEDIATE 'alter system flush shared_pool';
   END IF;    
Categories: DBA Blogs

How many times can you practice and revise a presentation?

Fri, 2014-02-21 18:51
Well the obvious answers are "it depends" and "how far away is the actual presentation"?  For Hotsos 2014 I had the vast majority of the work done in early December 2013 ... not bad for a presentation not delivered until March 3 2014.

That was after a couple of series of revisions and after all this was based on a presentation originally done at Oracle Open World 2011.

It makes even an experienced and grumpy old Oracle professional more than a little nervous delivering something at Hotsos however.  After another series of changes and improvements in January I put it away for a while.  Just back at it again about 10 days ago.  I sent in Hotsos "yet another" final version but is it actually the final version ... heck no!

Last week on Wednesday I delivered a dry run of the presentation to several of my co-workers along with a couple of NEOOUG fellow board members.  Big thanks to Pete Dinin from Sherwin Williams for some great suggestions and comments.  He was typing away during the run ... I thought he was doing work related email ... but no ... comments and suggestions on the presentation.  So Pete as always I owe you!

Two more weekends before I actually do the presentation.  Going to alternate days when I do not look at it at all and other days when I run through the material and the slides.

Working on some dry DBA humor to get some laughs ... wish me luck!

Categories: DBA Blogs

linkedin endorsements make me ... grumpy ... sometimes

Tue, 2014-02-18 12:59
Well I don't really mind endorsements for skills that are in my current toolset.  It is a nice compliment to get endorsements from people that you know well or have work with or are working with.

On the other hand endorsements for things that you have never worked on in your life seem a little strange.  I now have several endorsements for Oracle HR ... never ever in my life touched that not even peoplesoft.  Even stranger is the recent endorsement in that from a system admin in my previous job where guess what ... we never ran that software there at all.

Just received another strange endorsement for SQL Server from someone that I do not remember how they are connected to me via linkedin.  I guess SQL Server is much improved these days ( needed to change obviously ) but wow have not touched anything on that platform since like 2000 and that was a 3 month project.

Categories: DBA Blogs

ora 600's always make me grumpy ... ORA-1555 / ORA-600 [ktbdchk1: bad dscn] #8895202

Sat, 2014-02-15 09:40
These days at least for me seeing an ORA 600 is a relative rare ( thank god ) occurrence.  They always raise your blood pressure sometimes to unhealthy levels.  Looking at one that at first glance hints at possible block corruption ... not good.

This bug 8895202 was fixed already in current environment but not "enabled" ( so thanks so much ... what use is a bug that is fixed but not enabled to be fixed ).  Apparently can happen in active data guard environment after switchover/switchback?

Looks like bad interaction of commit scn and itl scn in ( index blocks )?

Good news is ( rarely do 600's give you good news ) is can enable this dynamically ... scope=both ...

Although this fix is included in /, it has to be enabled by  setting "_ktb_debug_flags"=8;  Rediscovery Notes ORA-1555 / ORA-600 [ktbdchk1: bad dscn] / ktbGetDependentScn /  Dependent scn violations as itl has higher commit scn than block scn. This happens in a Physical Standby database after a switchover.  DBVERIFY (with fix of Bug 7517208) reports:   itl[<itl_id>] has higher commit scn(aaa.bbb) than block scn (xx.yy)   Page <Block#> failed with check code 6056  There is NO DATA CORRUPTION in the block. Workaround This fix is the workaround.  It doesn't prevent to have a higher ITL SCN than the commit scn (csc). With this fix if parameter _ktb_debug_flags = 8 the SCN is repaired when block is cleaned  out (eg: block update).  While blocks are not touched dbverify still reports 6056 errors Sometimes the fix may not repair the block and the index may need rebuilding.

Categories: DBA Blogs

speakers on the agenda for NEOOUG March 28 2014 meeting

Fri, 2014-02-07 11:32
We have two topics being presented and as usual great free food beginning at noon.  This should be a good mix of topics Java related for developers/dbas and Oracle database internals stuff for dbas/developers.

First up on the agenda is Scott Seighman a Principal Sales Consultant from Oracle Corporation.  Scott will be talking about Java 8 which is just around the corner from being released.

Specifically his presentation is: Java.Next: An Overview of Java 8

The March release of Java (8) introduced a variety of new features, including Lamda expressions, annotations and a new date/time API. We'll review these and other notable additions to the Java platform, plus provide code samples and demonstrations of the new features of Java 8.

The other presentation will be given by me ok it is the same one I am doing at Hotsos 2014 in early March in Dallas.

Three Approaches to Shared Pool Monitoring for Oracle Database Systems

The shared pool area in Oracle has become a huge memory area over the last ten years, and there is much more than SQL and execution plans held in the shared pool. This presentation will cover three approaches to gaining increased visibility into the contents of the shared pool: 1) using standard oracle views and diagnostics, 2) implementing an in-depth custom monitoring procedure, and 3) shared pool application SQL monitoring.

The approaches here were learned from the school of hard knocks and should be illuminating to many Developers and many DBAs. This presentation will include 12c relevant content.

Categories: DBA Blogs

Hotsos 2014 is just around the corner

Tue, 2014-02-04 08:51
Last year I arrived in Dallas on sunday and it was warm.  Running on monday it was in the hot range like 80+ degrees.  I was out there in shorts and tank top and there are these local runners with full set of equipment jackets and everything.  Wow I guess I was dying in the heat and these people still think it is cold?

Getting pretty excited about presenting again but time to start looking at and reviewing my presentation.  I vowed a while back to NOT add in any more slides but ... well ... there is one thing I discovered recently about how to view bind variable values for statements that are executing and are getting monitored by the Oracle real time monitoring capability ( which kicks in for sql statements executing 5 seconds or so ).  It not exactly directly related to "shared pool monitoring" but it kind of related to custom monitoring ... so darn I think it is going in there.

Need to practice my presentation in front of some of my local gang here also and take some comments from the peanut gallery.  Hope to see you down in Dallas!
Categories: DBA Blogs

Two great SQL Developer presentations by Jeff Smith at NEOOUG January 2014 meeting

Mon, 2014-01-27 18:55
Jeff Smith aka @thatJeffSmith presented two topics at our January 24th 2014 meeting.  A well attended and smooth delivery even in the face of some not so nice weather.  Cleveland in January and February well it's always somewhat unpredictable.

Here is the meeting setup information:

Jeff Smith is a Senior Principal Product Manager in the Database Tools group at Oracle. He works on the team that brings you SQL Developer, SQL Developer Data Modeler, APEX Listener, the Public Cloud database offering, and 11gR2 XE. Jeff has presented online and in-person at many of the top user groups around the world for the past decade including ODTUG, IOUG, RMOUG, NEOUG, Suncoast Oracle User Group, and more.

Two presentations ( with plenty of time for questions/discussion ):
1) What’s New in Oracle SQL Developer and SQL Developer Data Modeler v4.0
2) Oracle SQL Developer Tips and Tricks

You can download the powerpoints from here:

Presentation 1: Sql Developer Tips and Tricks

Presentation 2: Whats New in Oracle Sql Developer 4.0 and Data Modeler
Categories: DBA Blogs

how to read the values of bind variables for currently executing statements real time monitoring kicks in

Thu, 2014-01-23 18:19
As usual Tanel Poder has done an excellent job of writing up this approach.

Caveats I have tested this in and might have some issues earlier not quite sure but hey Tanel probably has this documented also.

This came in really handy recently looking at some SQL chewing up large amounts of LIO.

Here is Tanels writeup: bind variable sql monitor leading over to here ...

Categories: DBA Blogs

wow this looks interesting and not too far away world Information Architecture day 2014 ( Ann Arbor MI )

Sun, 2014-01-19 12:37
Just saw this posted ... hmm road trip time from Cleveland on Saturday Feb 15?  Only possibly dicey thing is driving/traveling on Saturday in mid February in the Midwest you can never predict what that is going to be like.  But hey 4 wheel drive in the Honda CRV so ... time to check schedule and see who else might want to attend.

See this link for the Ann Arbor event World Information Architecture Day 2014 Ann Arbor MI here is the overall site maybe there is an event planned near you WIAD 2014 ?

Categories: DBA Blogs

oracle security patch notification now out

Wed, 2014-01-15 20:02
Most people have seen this already but just in case oracle jan 2014 patch notification ...

From what I have seen there are huge differences company by company site by site how patching and testing of patching occurs.  That's a whole different blog entry though right?
Categories: DBA Blogs

Final reminder ... see Jeff Smith @thatJeffSmith Jan 24 2014 for NEOOUG quarterly meeting

Tue, 2014-01-14 07:40
Jeff Smith aka @thatJeffSmith
Jeff Smith is a Senior Principal Product Manager in the Database Tools group at Oracle. He works on the team that brings you SQL Developer, SQL Developer Data Modeler, APEX Listener, the Public Cloud database offering, and 11gR2 XE. Jeff has presented online and in-person at many of the top user groups around the world for the past decade including ODTUG, IOUG, RMOUG, NEOUG, Suncoast Oracle User Group, and more.

Two presentations ( with plenty of time for questions/discussion ):
1) What’s New in Oracle SQL Developer and SQL Developer Data Modeler v4.0
2) Oracle SQL Developer Tips and Tricks

Also NEOOUG January Business meeting topics: Financial report/Election of 2014 officers/GLOC 2014 conference update.

Please note: lunch at 12:15 meeting starts 1 pm ... EVENT REGISTRATION ends 11:00 am on Jan 20 2014

* * *
No day of event walk in ( apologies security procedures ).  You must register in advance to attend.

Complete information and registration information event registration :   
Categories: DBA Blogs

Join Alex Carlos Mauro Scott and speak at the Great Lakes Oracle Conference 2014

Sat, 2014-01-11 08:34
The Great Lakes Oracle Conference is May 12-14 2014 in Cleveland at CSU.  Beautiful campus environment and state of the art student center main ballroom.  Plus well of course the chance to speak at the city where the river caught on fire right ( long story ask me over a beer ). We have 1/2 day workshops on Monday ( Alex Gorbachev / ( Carlos Sierra and Mauro Pagano ) / Scott Spendolini ) and then main conference starts Tuesday with keynotes by Steven Feuerstein and Tom Kyte. Call for Abstracts is open now here:  Please consider joining us at a rapidly growing regional conference that Carol Dacko considers "One of the best choices for any Oracle Professional to attend".   
Categories: DBA Blogs