DBA skills
sanjai, December 09, 2001 - 11:44 am UTC
Very well explained.
Good one
Patrick, December 09, 2001 - 12:08 pm UTC
Thanks Tom, good list to view!
More specific for 9i
Fan, December 09, 2001 - 12:46 pm UTC
Tom,
My original question is not clear enough.
I would like to know the most important 'new' features 9i has for DBA. And as a trend, what 'old' features from 7/8/8i will gradually be phased out?
December 09, 2001 - 1:09 pm UTC
No, I understood your question -- sorry you didn't understand my answer. I don't believe there is any list of "important features you need to know in order to be a DBA". 9i has HUNDREDS of new features. They are all equally relevant in different contexts.
Automatic segment space management -- relevant in OLTP. Perhaps meaningless in DSS with weekly loads.
Block level recovery with RMAN, totally relevant if you use rman, run in archivelog mode. Meaningless otherwise.
Multi-block size tablespaces -- totally relevant if you transport tablespaces between databases with different blocksizes frequently. Maybe not so relevant otherwise.
My point was -- get a good overall understanding of everything that is in there, read the docs, understand it. What you read today may have NO relevance to what you do today but tomorrow -- you might find a need for it and if you were not even aware of its existence, it might as well NOT exist.
I do not believe there is a list of "you must know this", rather you want a understanding of all there is to know, know how to find more information on it, know how to apply it.
I don't have such a list.
The only major "features" being trended out are
o dictionary managed tablespaces
o rule based optimizationo connect internal and svrmgrl have disappeared. AS SYSDBA and sqlplus replace
them
You hit the nail on the Head
sriram, December 09, 2001 - 8:56 pm UTC
Practical and Enlighting Views.
VERY RIGHT
Ganesh Aditham, December 09, 2001 - 10:11 pm UTC
It is for me this way "KEEP YOUR FUNDAMENTALS RIGHT AND NOBODY CAN SHAKE YOU"
Thanks Tom for the good answer
Thats just superb!
Srimal, December 10, 2001 - 3:20 pm UTC
Hi Tom,
The explanation that you have provided is just superb!
Some more skills
Arup Nanda, December 10, 2001 - 4:28 pm UTC
Hi Tom,
I don't want to budge in your responses but I thought I could contribute a little in the discussion of DBA interviews. I do take a lot of DBA interviews, and here is a sampling of the questions I generally ask (in addition to the oned you mentioned).
1. You are the new DBA at a shop with several varieties of end user tools you have not even heard of; and a development manager walks in on your first day with a menacing look and words that shake the earth, "the database is slow". Tell me where you would start looking and how.
Most people start with sar and vmstat, I gently prod them to a more meaningful answer. There is no single "best" answer, but I look for the analysis skills of the candidate, how logical his or her reasoning skills are and, most important, is he/she just beats around the bush or logically identify potential trouble areas and eliminate possibilities.
2. I never ask anyone, novice or advanced, two things - definition and syntax. The fact that the candidate knows or does not know these does not prove anything. I ask them to explain the concept in whatever way they feel comfortable, e.g. describing an incident or stating "for example". Syntax, with the exception of a few, is not meant to be written to the hard disk called brain. The manuals are always there. However, you must know what options you have and how you use them, the exact syntax not relevant.
3. When interviewing DBA candidates for a specific task, I generally ask an open ended question on the ideas to resolve the problem without too much reference on the constraining factors. For example, I recently interviewed candidates for a shop doing migration. I asked the candidate a loose, open ended questions - what are the various methods of moving data from one system to the other? Then I prod them on pros and cons of each. This tells me volumes on teh depth of experience of the candidates.
Hope this helps.
Arup
DBA skill set trend
Rajendra Prasad, January 15, 2002 - 5:02 am UTC
Thanks Tom
Given Good mist to everybody to be come a dba, is a prime most factor.
Concepts manual and Whiteboard
A reader, August 26, 2002 - 2:48 am UTC
How can we make
"Write Me On That Whiteboard How Oracle Works"
a part of the Mother manual?
August 26, 2002 - 8:00 am UTC
It is already done -- the picture and all of the verbage is exactly what "Part I What is Oracle?" is all about - the picture is in the section "Memory Structure and Processes" as is most of the verage in that chapter!!
Thats true but...
A reader, August 26, 2002 - 9:11 am UTC
what is missing there is how all these work together like "Redo and Rollback" page 150 - 153 from your book.
I feel DBA job is not here to stay
A reader, August 26, 2002 - 3:55 pm UTC
Excellent response! As always!
Unfortunately I feel the market's direction is reducing DBA jobs. I would not place any bet for DBA jobs!In fact we already see demise of Oracle DBAs!
Just wanted to warn to people who dream about DBA jobs (seems a few dont understand why) :-)
August 26, 2002 - 4:08 pm UTC
You are kidding right? I see no drop in the need for qualified smart people. Whilst the nitty gritty parts of the job are "easier" then in the past -- the job is broader, there is MORE to support (workspace mgmt, text, xml db, java in the db, etc etc etc). some things that were hard are easier but overall there is more to manage. Net effect: you still need qualified, smart people.
A reader, August 26, 2002 - 4:31 pm UTC
The problem with (workspace mgmt, text, xml db, java in
the db, etc etc etc etc (and a long list) is... where are they used? Probably 10% of shops (1% in Spain, in fact many OSS Spain dont even know about these nice features)
Also in many big shops third party software are used such as SAP, Peoplesoft, Siebel (we should have a look at Siebel pl/sql stored procs, so sad :D ), Clarify (Clarify pl/sql functions so sad too :D ), JD Edwards, Vantive, Telco Billing system such as Kenan/Arbor/BSCS/OPSC (I work in Telecom area so I can only list software used in this area) and the problem is none of these software actually exploits RDBMS features. Some of them just got certified on 8.1.7 for 10~11 months and sadly none use 8.1.7 features! They still run on the database as we were in Dinosaur age (Oracle 7?) And everytime we tried to use some brillant features or try to optimize some pl/sql proc/functions/packs we have to ask the software providers first and normally we get a no :-)
Or may be I work in big shop not sure but that's the truth over here, many nice features none used.... not even when they are suggested due to restriction placed by third party software providers. Also Oracle is making the software more self-manageable due to market pressure and it's the direction is heading to
Andrew Schonberger, August 27, 2002 - 1:51 am UTC
>> still need qualified, smart people <<
Tom,
you've made my day. It feels good to read these pages. As the ancient Greeks used to say, "may the Oracle speak from your mouth".
Apologies for the pun. But the analogy is not far-fetched, since we are talking about a system of beliefs here, and at stake is our professional future. I also apologize to those readers who haven't necessarily heard about the Oracle of Delphi. With globalization, we might pick up similar terms from other cultures.
Sadly, the opposite school of thought is also gaining ground in the IT industry. Namely, the emphasys on repeatable processes, and the horror of relying on the skills of any single individual. As Oracle licenses don't come cheap, it's not surprising management wants predictable outcomes. But I was glad to see Oracle Inc. has addressed the problem, by making available free copies for developers. Anyone who wants to learn Oracle can do so, and I've been a beneficiary of this approach.
Andrew
DBA skill set trend
Shailesh, August 27, 2002 - 3:31 am UTC
Thanks Tom.
You explained things in much simple way. I am agree with your opinion. The people who are afraid of decreasing demand of Oracle DBA, needs to understand that Law of demand and supply is going to work in each and every area. When supply will be more then definately demand will come down.
One thing is there, that there can't be increase supply of good DBA in short time. Since to be good DBA , he needs practical exposure, which will take some time. By doing some crash courses like programmers, DBA can't have expertise.
Regards,
Shailesh
DBA skill set trend
Hien, March 28, 2003 - 8:49 am UTC
Tom,
Thanks.
After reading this thread I think I need to go back my text books again. It helps me in reviewing my skills and where I am now, in terms of knowing Oracle.
great!
Reader, May 16, 2003 - 12:42 pm UTC
Tom, you have provided a great analysis of what makes a dba an acceptable DBA. I have heard people who hire DBAs say that knowledge of PL/SQL is not needed for a DBA who manages production database. I strongly disagree with that. What do you say about this? I think the DBA without an adequate (home much though?) knowledge in PL/SQL may not learn much as a functioning DBA. There is a common feeling that PL/SQL is needed only for those who develop applications to work with Oracle database. Any comments?
Thanks.
May 16, 2003 - 5:11 pm UTC
I don't see how they could do their job without at least a rudimentary knowledge of it.
DBA without pl/sql
A reader, May 16, 2003 - 6:37 pm UTC
I have seen a lot "DBA" without pl/sql skills. The only thing they can do is to do system level cold backup.
When dealing with performance, the only thing they can do is vmstat or hit/miss ration. They cannt help anything on application architecture design and application level tunning.
Can I run something by you?
Arun Mathur, June 18, 2003 - 9:36 am UTC
Tom,
I agree wholeheartedly with the comment you made below:
"People skills are perhaps more important (at least AS important) as technical skills."
I see you've dealt with various situations, an I thank you for sharing those experiences. My question to you is this:
Some businesses may for example, assign one technical lead that oversees all operations of the dept. The manager basically tells the lead, "Do X,Y, and Z, and you have 5 people under you to get it done." I've been in situations before where the technical lead doesn't have the people skills you mention, and I was wondering how to deal with these types of people? I've seen two opposite extremes: One where people constantly clash/shout with the lead, and another extreme where people just stop caring and simply do what they're told, whether it's right or not.
Any thoughts on this is greatly appreciated.
Keep up the great work.
Arun
PL/SQL needed by DBAs
Rajendra Majali, June 27, 2003 - 10:27 am UTC
What you are saying about need of knowledge of PL/SQL for DBAs is right, but if you look at OCP curiculam, you will find that till 8i there was somthing related to PL/SQL, but from 9i there is only sql and not pl/sql for DBA. Does this mean oracle it self suggests that for DBA pl/sql is not that needed?
June 27, 2003 - 12:07 pm UTC
You'd have to ask the OCP people I guess. I'm not in anyway affiliated with that program.
Great Info
Brian, June 27, 2003 - 12:51 pm UTC
Along the same lines, do you care what the person's original background is in? For example, if I wanted to transition to a assistant DBA or developer role from a finance role, what would I have to do to show that I can perform the job well?
Would being able to answer those questions you gave be a good launch point?
What's the best way to obtain those five years of experience?
June 27, 2003 - 1:35 pm UTC
no, don't care about the persons background (me, I was a math major who hadn't touched a computer except for some cobol classes in college -- I found a job as an "entry level programmer/analyst")
Not sure what to tell you as far as career advice goes. You'd be "entry level", a step back in they pay scale perhaps.
dba skill set trend
Adolph, June 27, 2003 - 4:40 pm UTC
Tom,
While I would cherish to have your cubicle (or office ?) right across from mine, I would dread meeting you for a job interview.
Now some math questions...
Robert, June 27, 2003 - 8:15 pm UTC
>>.....I was a math major who hadn't
>>touched a computer except for some cobol classes in
So can sometimes take a break from this stuff & ask you math questions like "differential equations"..."linear algebra"... ? man I gave'em all back to the professors...
DBA skill set trend
Poonam, June 28, 2003 - 7:09 am UTC
Hi Tom
Information provided by you is really very useful
I would like to know one thing
If a fresher wants to get in for DBA career what are the skills that you would look in for an entry level DBA.
How can the person pursue a career in DBA
would be thankful if you can provide this information
Regards
Poonam
June 28, 2003 - 9:20 am UTC
I'd be looking for a smart, personable person -- for entry level.
they have to be smart.
they have to be likeable.
after that, its all about training.
About that PL/SQL issue...for Rajendra
Tarry Singh, June 28, 2003 - 8:52 am UTC
Well,
Database administration in itself is not that "duh" kind of job. Learning pl/sql will be very advantageous to your dba career but you'd be helpful and learn in pl/sql if...
[1] you have a development team building an application/modelling your db design etc(most of the times an organization buys a ready made application, if you're working for such a shop(who developed it) then it's indeed helpful , if not then it's no point...
[2] DBA job has been made rather easy with tons of those packages(supplied packages). You'd be able to find practically everthing there...and that's loads of pl/sql.
But bottom line is that good/strong pl/sql knowledge will help you become a better dba.
sql server dba vs oracle dba
Reader, June 28, 2003 - 1:37 pm UTC
I met someone recently with 6 years of experience as SQL server DBA. This person has been asked by her management to undergo Oracle DBA training with Oracle University as she may be asked to manage some oracle databses in the near future. I was in class (9i DBA Fund 2 by Oracle Education) with this person and this "expert" sql server DBA was saying in the class that she knows everything already and the only reason she is in class was because she was forced to attend. She also was saying that being a sql server dba for more than 6 years is adequate enough to manage oracle databases as she need not have to learn anything special about Oracle dba tasks. I always thought as myself being an Oracle dba with more than 3 years experience that I still have so much to learn about Oracle as it is a ocean. I don't think being a sql server dba would qualify someone to manage oracle databases without any training either inhouse or at Oracle Education? Any comments? Thanks.
June 28, 2003 - 8:29 pm UTC
She is in for a shock, as much of a shock as you would be if it were going the other way.
Ask her to
a) draw a picture of Oracle on the whiteboard and describe how the components work together.
b) quick -- what is in a control file and why is it there, whats important about them.
c) how is it that Oracle can have transactions that generate many times the amount of redo then we have configured on disk. I mean, I can run a transaction that generates 100meg of redo yet only have 10m of redo configured. What is the mechanism that allows me to do that (and as a bonus tell me "why is it relevant that an Oracle dba knows the answer to this")
d) ok -- you need to move your system datafiles from /d01 to /d02 -- how do you do that?
e) what is the architecture of a LMT and why are they superior to a DMT and tell me -- how do you know which you are using?
She will know in 5 years exactly how much she did not know 5 years ago. Only she'll spend the first 4 years cursing because "this Oracle thing isn't just like SQLServer, why not". Hope she takes some notes in class.
Poooh...for my houston friend
Tarry, June 28, 2003 - 3:45 pm UTC
Well noone(besides Tom here) will be able to answer that question better than me.
I'm working 2 weeks only as a sql server dba(after having 3 odd years of exp on oracle since 734>9i) and I am getting bored. I mean it. And I'm not saying I know everything on sql server. Well I've gone thru all the routines dbcc's most of them. I'm going through all the *.chm files(bol)although they're far far inferior compared to oracle's online manuals or pdf's(for printing)And what is all that the sql server dba does is
dbcc checkdb('dbname', noindex) if something's wrong. The guy who I am replacing create's simplw sql's thru the em(you know right click table, design..) I cringe at that view alone.
How I got the job, my boss has been an oracle dba. We'll I'm trying to do my best, I've reverse engineerd the db and have asked for my own independent test environment...
Well I do have my latest oracle db running at home to "play" when I'm back from work.
Go figure.;)
TJ Mitra, June 29, 2003 - 12:16 am UTC
Hello Tom
As we have graduated from Oracle 6 to Oracle 9i, our quality of DBA work has greatly improved. What used to take long time ( like export/import - replaced by alter table move, alter index rebuild online etc) or required an outage of the database ( for changing db_block_buffer - replaced by online modification of db_cache_size ) is now a history. For these very reasons and for introduction of many advanced features, the Oracle DBAs these days can contribute in a far more effective & intelligent way towards better database management & administration. Thanks to Oracle for bringing quality in the RDBMS technology.
DBA ROLE
friend, June 01, 2004 - 1:45 am UTC
ITS REALLY VERY NICE CONVERSATION AND again " Thanx to TOM to be in this world"
Tom Pleas ehelp me to understand the concept of workspace amangement
EXEC DBMS_WM.EnableVersioning('SCOTT.EMP');
select * from scott.emp_lt;
SQL> select object_name from all_objects where owner='SCOTT';
OBJECT_NAME
------------------------------
AUX_EMP
BONUS
DEPT
EMP
EMP_AP1$
EMP_AP2$
EMP_AUX
EMP_BASE
EMP_BPKC
EMP_CONF
EMP_DIFF
EMP_LOCK
EMP_LT
EMP_MW
EMP_PKC
EMP_PKD
EMP_PKDB
EMP_PKI$
LT_AD_1
LT_AU_1
OVM_DELETE_1
OVM_INSERT_1
OVM_UPDATE_1
PK_DEPT
PK_EMP
SALGRADE
26 rows selected.
SQL> EXEC DBMS_WM.DisableVersioning('SCOTT.EMP');
PL/SQL procedure successfully completed.
SQL> select object_name from all_objects where owner='SCOTT';
OBJECT_NAME
------------------------------
BONUS
DEPT
EMP
PK_DEPT
PK_EMP
SALGRADE
altruistic/sensible DBAs ... a rare breed?
Gabe, June 01, 2004 - 11:12 am UTC
<quote>how do you make it so the developers can use TKPROF efficiently</quote>
That DBA position must still be open then ...
<quote>when are the times it is valid to run a database in NOARCHIVELOG mode</quote>
Just curious Â… what percent of your candidates get the incrementally loaded DW answer?
As for ... <quote>the ones that describe how a TEST instance [can be run in NOARCHIVELOG mode] ... can stay</quote> ... I thought you advocate having the TEST (maybe even DEV) instance/database an exact replica of PROD?
By "TEST", you mean that _play_ (fully controlled environment) instance/database you recommend each developer to have on their own machine, isn't it?
Thanks
June 01, 2004 - 2:57 pm UTC
more databases run in archivelog mode in a DW environment than do not... technlogies such as data guard and the users demand that "it be available" override the no archivelog mode in most cases.
In many cases, test not being in archivelog mode is OK cause the same amount of redo would be generated in either case. For load testing, they should be identical 100%. Only direct write operations that could bypass redo generation in noarchive log would raise concern (like Creates, insert /*+ append */ and so on), but most systems don't use them (they are warehousing type features).
DBA with little application knowldge
abc, June 04, 2004 - 7:49 am UTC
Tom
What will be your word on this?
Good DBA with less application knowldge can survive or ???
June 04, 2004 - 10:39 am UTC
survive -- sure. as long as they can restore the database, they could survive (thats the ONLY thing they cannot mess up, we can fix everything else after the fact but if they cannot restore -- ouch)
be really good, totally succesful, fully beneficial, be the person kept when someone has to be let go --- probably not.
I have a dream ...
A reader, June 04, 2004 - 2:47 pm UTC
What exactly is “application knowledge”?
Being able to do the backup and the restore is the only thing the DBA needs to know in order to survive Â… sprinkle some certifications, plaster them on the office wall, business cards and e-mail signature and youÂ’ll thrive.
If Tom or someone half as competent is your boss then Â… well, you donÂ’t need to worry about Â… <quote>be[ing] the person kept when someone has to go</quote> Â… read this thread from the beginning Â… you wonÂ’t even get in. BTW, about being let go Â… (except for the backup/restore thing) that would have more to do with personality clashes with your boss rather that your technical/business acumen.
My whole idea of “early retirement” is about becoming a F/T DBA … interesting enough to keep the brain going … plenty of excuses to avoid the meeting with the latest motivational MBA and/or the young technologist ready to solve all the IT problems with the latest software paradigm.
;)
June 04, 2004 - 3:08 pm UTC
application knowledge to me means
o you understand what is running in your database
o and why it is
o and who it is important to
o and how important it is to the business
o and you can administer it (eg: patch it, upgrade it, install it, configure it to some extent)
for example.
Did enjoy the thread (even if not technical) Â… thanks.
A reader, June 04, 2004 - 4:37 pm UTC
Your definition makes sense of course … although I cannot help but wonder if there is any DBA out there not to claim to have the “application knowledge”!
My rhetorical question was more about what “abc” meant it to be (the “exactly” part):
1. Oracle development knowledge Â… hey, lots of survivors out there!
2. A specific Application knowledge Â… like Siebel or SAP or Â…
3. Company business knowledge in general Â… well, everybody claims this one!
What I see … Oracle DBA + any Siebel/OracleApps/SAP/PeopleSoft/etc – development exposure ==> job for life … pain to deal with (many times sending an email to the DBA is like sending it to /dev/null)
Generals lead the army Â… but the army is run by the sergeants with the keys to the warehouse. No amount of leadership or courage will help you if the sergeant doesnÂ’t keep the ammo/fuel/food/water coming. But I digress. IÂ’m just hoping I would do better should I ever be in a DBA position (my "early retirement" ;)
<<
it would be a privilege
bob, June 04, 2004 - 6:01 pm UTC
Math major huh? Scientists and engineers surely make the best dbas/developers. Too bad we have so many geography/liberal arts folks in the field that found they couldn't make a living writing travel guides and somehow stumbled into a dba job.
It would be a privilege to be in front of your white board to draw the processes and memory structures of Oracle. I have trouble believing a DBA would set foot in your office without at least this understanding. While I say that, I personally have met many a dba that couldn't even tell you what "SGA" stood for let alone what it contained. This explains the blank face you describe.
With regards to experience vs. no experience. I'd rather a college educated, motivated, intelligent, analytic minded engineer/scientist with little to no experience over an experienced high school graduate. The only thing important is commitment to education and learning. I have met many "experienced" folks whose experience and knowledge is rapidly aged out by new features and technology. These same individuals are quickly overtaken by their faster learning technically educated newbie co-workers.
June 04, 2004 - 9:14 pm UTC
scientist, engineers, HISTORY MAJORS (believe it or not), people with critical thinking -- analytical skills.
(i have a liberal arts degree by the way. at the University of Pittsburgh, I was in the college of Arts and Sciences -- 100% liberal arts ;)
I don't care what they studied in college, not really relevant...
RE: Too bad...
Mark A. Williams, June 04, 2004 - 6:56 pm UTC
RE: Too bad we have so many geography/liberal arts folks in the field...
I have not had the same experiences myself.
As a Sociology major and French minor (both Liberal Arts degrees of course) I don't think that my 10+ years of Oracle experience are diminished by the degree(s) I chose to pursue. Most of what I do on a daily basis as a Production DBA has precious little to do with computer science or mathematics. Perhaps my experiences are not typical though...
I have never attempted to write a travel guide (though I am currently authoring a book) and sure didn't stumble into being a DBA.
- Mark
=======================================
Mark A. Williams
Author of the forthcoming Apress title:
Professional .NET Oracle Programming
to mark:
bob, June 04, 2004 - 10:03 pm UTC
Sorry you missed the engineering pride humor in my remark. It wasn't intended to take away from your successful career. I was just jealous that you probably had a good time in college traveling abroad to study the behavior of the French. That was in stark contrast to me and most of my engineering classmates who didn't even have a language course requirement and very few of which had any knowledge of appropriate human behavior. Night after night, in an underground computer lab diminishes social skills greatly. I hope I didn't offend you or anyone else. Have a good weekend.
Hi Bob
Mark A. Williams, June 04, 2004 - 10:13 pm UTC
Hi Bob,
And I should not read AskTom when I am in a bad mood for reasons that have nothing to do with technology, etc!
I do believe that I can understand what you are saying tho!
In any case, no offense taken, and it has been a long time since I was last in France - but I wouldn't mind a return trip :)
Have a great weekend there too,
- Mark
June 04, 2004 - 10:19 pm UTC
"hi bob" - as I recall, we used to do a shot or a big swig of beer in college when that came on the TV (bob newhart show -- but I date myself maybe)...
sorry -- couldn't resist with all of this college talk.
whats happening above
abc, June 05, 2004 - 2:54 am UTC
Respected Tom
Whats going on this thread Maths expert huh????
Whats does it mean? There is something wrong with Bob.....
Restore of database means??
U mean Backup and recovery scenarios right??
June 05, 2004 - 8:52 am UTC
Bob read the entire thread, we discussed college majors up a couple reviews...
There is nothing wrong.
Restore is the one thing a DBA cannot get wrong. (note: i did not say backup, I mean "recovery", "restoration". I don't want backup experts -- I want recovery experts :)
I mean the ability to recover the database regardless of what happens -- fire, storm, spilt coffee on the server, bad disk, errant "rm -rf *", whatever.
restore
friend, June 05, 2004 - 9:39 am UTC
Tom
I am confidient about Rman restoration,Recovery scenarios so it means i can survive as weak in application coding
RE: but I date myself maybe...
Mark A. Williams, June 05, 2004 - 10:57 am UTC
> but I date myself maybe...
Well, we are only 7 months apart, so what does that mean for me!?!? :)
- Mark
new hire questions
Donald Kunecke, June 05, 2004 - 12:37 pm UTC
Tom your comments are well understood. I have interviewed so many DBA's that can practically quote bible and verse of the ORACLE manual. However, they simply can not understand the big picture as to WHY things do or must happen. As you pointed out, the ability to recover is so important. I would rather hire someone who can analyze the situation, develop a plan of action than one who can tell me the syntax of every statement. I would rather learn where the books are for syntax that I do not use often, and learn the who, what, where,when and how of the big picture.
Note from an old codger
Kevin Brady, June 28, 2004 - 7:04 pm UTC
Been in IT since it was called data processing...going on 26 years...first programming language was NEAT/3...I learned early on that there was no substitute for learning about the business your company is in...the technology takes care of itself if you are the least bit competent and resourceful...have had the pleasure of working with Oracle since '97 and have embarked on getting my OCP just 'cuz its a personal goal...my keys to success (and I feel I have been successful) have been:
1) Make sure you know that what you say works, works
2) Make sure you know where to get the answers if you don't know
3) Listen to your customer - end-user, developer, manager, 3rd party vendor, whomever
4) Keep a sense of humor and perspective about your role
5) Get intimate with the data in the databases you manage - you WILL bail the developers, and as an extension, your company out early and often
Thanks, Tom and to all your contributors for this valuable resource.
support
abc, July 21, 2004 - 3:50 am UTC
I am getting the following error message while running a script in TOAD ver 7.2.0.0 with Oracle ver 9.2.0.5.0
-20000-ORA-20000: ORU-10028: line length overflow, limit of 255 chars per line
See the following snippet of codeÂ… PLZ SEE THE PORTION IN PINK BELOW AND THE ERROR MSG IN BLACK ABOVE ....:
DECLARE
TYPE dtype_tab is table of xhl_dm_tab_monthend_statuses.cust_account_id%TYPE;
dist dtype_tab;
dist1 dtype_tab;
in_num_dist_id xhl_dm_tab_monthend_statuses.cust_account_id%TYPE :=101;
-- in_num_dist_id :=101;
lv_qry1 varchar2(4096);
lv_qry1 := 'SELECT CUST_ACCOUNT_ID from XHL_DM_TAB_MONTHEND_STATUSES where DIST_SUBTYPE ='||''''||
fetch_business_rulesA_record .FROM_SUBTYPE|| ''''||
' AND CUST_ACCOUNT_ID =NVL('||in_num_dist_id||', XHL_DM_TAB_MONTHEND_STATUSES.CUST_ACCOUNT_ID) AND ' ||
fetch_business_rulesA_record .volumetype_new ||' >= :a AND ' ||
fetch_business_rulesA_record .volumetype_new ||' < NVL(:b, '||
fetch_business_rulesA_record .volumetype_new ||
' +1) AND ((ORDER_MONTH = addmonths(sysdate,-1) AND '||
fetch_business_rulesA_record.unit_value ||
'= 1) OR ('||
fetch_business_rulesA_record.unit_value ||
' <>1 AND ORDER_MONTH BETWEEN addmonths(sysdate,-1) AND addmonths(sysdate,-'||
fetch_business_rulesA_record.unit_value ||
'))' ;
dbms_output.put_line (lv_qry1);
unable to find any description of the error in he oracle documentation also
July 21, 2004 - 8:05 am UTC
pink? anyway -- doesn't the message sort of say it all?
limit of 255 chars per line</code>
http://docs.oracle.com/docs/cd/B10501_01/appdev.920/a96612/d_outpu2.htm#1000446 <code>
also discusses this.
create or replace procedure p ( p_str in varchar2 )
is
l_str long := p_str;
begin
loop
exit when l_str is null;
dbms_output.put_line( substr( l_str, 1, 250 ) );
l_str := substr( l_str, 251 );
end loop;
end;
/
use p instead.
This is what I was searching for....
Rohan, July 23, 2004 - 2:34 am UTC
Hi Tom...
I got the answer I was seeking for a long time now...Of all the individuals to whom I asked this question or questions related to this topic, no one, not even Oracle DBA's tht I know could provide an explanation as comprehensive as you have. I am pursuing an OCP. I would also like to know what are the auxillary skill sets or knowledge required for a DBA at the entry level. I'm a software tester aspiring to be a DBA.Also more advice from you for entry level DBA's would be very helpful and highly appreciated...
Regards
Rohan
July 23, 2004 - 8:34 am UTC
auxillary skill sets for an entry level (eg: no experience required dba) would be an awesome personality, friendly, eager, wanting to learn, willing to take the time it will take to learn.
Regarding Auxillary skillsets....
Rohan Pradhan, July 26, 2004 - 1:08 am UTC
Hi Tom,
Thanks for the reply...I do believe I have the above stated traits and the perseverance...but what I really wanted to know about is the auxillary technical skillsets required...eg. There is a belief that a DBA should have good knowledge of networks and networking...or that it is easier to be a DBA if the individual has been a programmer...If the above are true then to what extent and how would this affect my chances of pursuing a career as a DBA considering that I'm neither a programmer nor am I into networks.
Regards
Rohan
July 26, 2004 - 7:26 am UTC
depends on whom you are interviewing with. there are no universal "auxillary skillsets"
Interesting ....
Reader, July 26, 2004 - 11:01 am UTC
I was recently rejected by a panel saying that I was TOO TECHNICAL for such a senior position and they preferred to have a Sr DBA / Sr Data Architect with more of a managerial expr .
It was very interesting to know that managerial skills are more important in a Skilled profession .I have worked with managers who have not kept themselves upto date and are still preaching the old theory of tuning by looking at the hit ratios .
I am now tempted to write an article which will have examples of having a technically weak manager .
But, I do not know how to prove that you have some managerial skills .
Any thoughts ...
Dave, July 27, 2004 - 4:08 am UTC
Management skills arent always needed. its just that the company you applied with wanted management skills.
There is a difference
abc
adc, July 28, 2004 - 6:19 am UTC
Hi Tom
is it true that dont create synonyms for mviews?
July 28, 2004 - 8:28 am UTC
is it true that I personally don't? sure
can you? yes.
should you? maybe.
are they done by default? no.
comment to reader who was rejected.....
Reader, July 28, 2004 - 10:01 am UTC
apparently they saw something in your resume that interested
them enough to call you in for an interview.
so..something is invariably different from what is stated
on your resume and what transpired during the interview.
to "Reader" on "Reader from US" re: Management etc...
Writer, July 28, 2004 - 11:48 am UTC
That is just *one* of the possible conclusions...
dba? I think not.
A reader, July 28, 2004 - 1:10 pm UTC
I just got done with a round of interviews for a junior DBA position we have open. Several of the candidates who claimed to have DBA experience couldn't explain the difference between truncate and delete.
Scary.
to Writer
Reader, July 28, 2004 - 5:07 pm UTC
my comment wasn't meant in a negative connotation.
it was based on the fact that the panel told the reader
who was rejected that he/she was TOO TECHNICAL.
therefore it is safe to conclude what was it about the
resume that led the panel to believe the applicant
had all the required skills and background to warrant
an interview based on the demands and qualifications of the
position that they then felt otherwise after the interview.
The reader seemed surprised by the panel's response
for more "managerial" skills however the panel must have
thought the reader had some background in this area to warrant an interview.
Database Architect
shgoh, July 29, 2004 - 4:12 am UTC
Dear Tom,
Any requirements to become a good database architect ? What is their major role in this line of service and are they different with DB administrator or Apps database administrator ? Thanks
Rgds
Goh
July 29, 2004 - 11:45 am UTC
I'd say lots of real world experience on both the functional and technical side are the biggest attributes you need to have.
A DBA runs software.
A data architect helps design software.
DB Infra architect
Shgoh, July 30, 2004 - 12:00 am UTC
Dear Tom,
What about DB infra architect? Am i right to say that this role is more like design the x-tier architecture for new application development, setting up the database instance, database sizing, implement RAC & DC, failover, backup and recover strategy, upgrade 10G...Thanks.
Rgds
Goh
July 30, 2004 - 7:41 am UTC
depends on your needs, not sure that "setting up the instance", "implement RAC" would necessarily fall into that personally -- architect designs, dba implements.
ARCHIVELOG mode
Richard, July 30, 2004 - 7:54 am UTC
You wrote:
when are the times it is valid to run a database in NOARCHIVELOG mode
(the ones that say "all databases can run that way" are removed
the ones that describe how a TEST instance or some data warehouses that are
incrementally loaded can stay)
Actually, I work for a company that runs a data warehouse in NOARCHIVELOG mode (always), with incremental data loads. But (and it's a big but), if we were to perform a database change of any kind during normal working hours, surely ARCHIVELOG mode would be a must, else we could lose work we'd just carried out. What is your view on this?
July 30, 2004 - 4:42 pm UTC
so they do full backups after each incremental load? hope so.
anything done AFTER any backup is data you are willing to totally lose and will lose some day.
bob, July 30, 2004 - 10:31 am UTC
I just read an article in the WSJ that discussed MSFT hiring practices. They said Bill thought they were actually competiting with Goldman Sachs for talent. It suggested that Bill thought IQ was the single quality they wanted most in a candidate for software development and many high IQ folks pursued the investment banking because of the wealth potential. I just thought was an interesting insight into hiring practices for techies.
A reader, August 04, 2004 - 2:59 pm UTC
Management Skill- Do nothing but demand people for work.
Polish juniors and the upper management. Good organizer and have lot of people skills
That's the quality of a management skill.
DBA and 10g
kiro, August 05, 2004 - 3:08 am UTC
Tom
Do you think that all these new integrated monitoring tools in 10g will kill DBA position?
August 05, 2004 - 12:49 pm UTC
yeah, sure, absolutely.
because you know, your systems
o have been getting smaller (your disk use is shrinking, your databases getting much smaller over time)
o supporting fewer and fewer users (less people are relying on these systems)
o are running fewer applications, we hardly have but one or two key applications anymore
o are allowed to be down anytime, for unknown durations of time
Oh wait, I got that backwards, our systems are
o growing and growing and growing
o with more users everyday
o and tons more applications accessing it
o and downtime -- that is something that gets us "replaced" quickly if you know what I mean
-- but oh, the size of the staff -- keep it the same.
These features allow us to keep our heads above water.
Now, the DBA that thinks "my job is to come in, sit and watch these 5 databases all day" - that DBA might be "looking for a new position". The database can a some degree start to watch itself, whilst we are off doing everything else we need to do.
Awesome explanation
friend, October 20, 2004 - 3:43 pm UTC
Now a days I am workingon CLearcase :(
Krishna, March 31, 2011 - 5:21 pm UTC
Hi Tom,
I have just completed my post graduation, and now i am looking forward to build my carrer as ORACLE DBA.
So kindly let me know how will I start my carrer as ORACLE DBA?
I would be gratefull to you
Thanks and regards,
Krishna
archivelogmode
A reader, October 27, 2011 - 10:31 pm UTC
There were two comments above that suggested some cases where ARCHIVELOGMODE is not needed (i.e data warehouse).
we have an analytics client applications that runs a 10 hour nightly job and purges data from oracle and reloads new fresh data. Would ARCHIVELOGMODE be meaningless in this situation too?
October 28, 2011 - 7:03 am UTC
answer this:
if you suffer a media failure (disk error) would you
a) want to restore a backup to get going again.
b) rebuild the database from scratch (unless it was just a datafile with indexes in it, then you could just rebuild the index).
If you answer NO to (a), you do not need archivelog mode.
The old problem
DJB, October 28, 2011 - 7:22 am UTC
'I would hire a high school graduate
with 5 years of experience before I hired a college graduate with 1 year of experience
and an OCP'
The old IT job problem - can't get a job without experience, can't get experience without a job.
your comment on redo
reader, October 28, 2011 - 8:08 pm UTC
In the above thread, you mentioned "c) how is it that Oracle can have transactions that generate many times the amount of redo then we
have configured on disk. I mean, I can run a transaction that generates 100meg of redo yet only
have 10m of redo configured. What is the mechanism that allows me to do that"
Can you kindly throw some more light on this please? How does oracle do it? Also, can you mindly point to any reference to the Oracle documentation related to this?
October 29, 2011 - 11:28 am UTC
basically,
there is more than one log file - always (say two 5mb redo logs for 10mb total)
when one of the log files fills up, we start checkpointing in the background the blocks protected by that redo.
before we reuse that redo log file - we make sure that all of the protected blocks are on disk first. So, before we reuse that first 5mb redo log file - all of the affected blocks would be written to disk - and therefore - we would not need the redo anymore (we don't have to redo those changes - we have recorded them to disk already).
therefore, we can have a transaction that generates hundreds of mb's of redo - even with just 10mb of redo log configured.
You want to read:
http://docs.oracle.com/docs/cd/E11882_01/server.112/e25789/toc.htm to understand the concepts of the database...
dba
sam, October 29, 2011 - 9:26 pm UTC
Tom:
I thought that transctions stay in the redo logs (online and archive) even after you write it to disk. Was i wrong? Does oracle erase them from redo log after it writes to disk. My original thinking was that oracle checks the SCN in the last baokup made and then start running transactions in redo logs with higher number.
Do you normally have a full time bouncer when you do the job interviews since you seem to have shown many people the door.
Dont you care for someone looks or loyalty?
I disagree with you on the epxerience. I have seen people with 10-20 years experience who are total nightmares with zero productivity. it does not really mean a lot. we have some people spending 80% of their time talking BS and when they do something, we would be better off without it.
you will never know a person until you work with him for a few months.
on personality side, the IT industry is full of jerky people. everyone will be acting nice in the interview.
October 31, 2011 - 11:08 am UTC
archive - yes, online - no. I just described it in some detail above sam. I've written it in painstaking detail in my books.
Do you normally have a full time bouncer when you do the job interviews since
you seem to have shown many people the door.
I don't actually, 50% of the people out there are above average. think about that.
Dont you care for someone looks or loyalty?
eh? looks have nothing to do with it. And if I wanted blind loyalty - I'd get a dog. I'd prefer someone who can think for themselves and debate their way out of a box.
I disagree with you on the epxerience.
fine, so be it. you'd rather have the nice looking, blindly loyal person that has read the book. great - goody for you. Sam.
I don't understand you at all.
Ved, October 31, 2011 - 11:52 am UTC
Tom,
I think Sam didn't get your point on experience. You meant someone who is grounded with basics and over the years has just build upon that.. someone who is having an open mind and heart to embrace and adapt change.
There are 'experienced' people who have risen out of sheer luck, loyalty and age in an organization.. and doing the same thing over and over again. Sam's been unfortunate to work with this kind of 'experienced' people.
October 31, 2011 - 12:20 pm UTC
And Sam would promote and hire these people from my reading of Sam. he seems to approve of looks and loyalty over and above the ability to actually do stuff.
You know, experience.
If I interviewed two people - and all things held constant - they were the same otherwise - I would take the experienced person over the book certified person.
Critical reading counts -some people don't do it. If someone was to read the initial response above and came to Sam's conclusions - well so be it...
Someone with experience that cannot do the rest of what I wrote ( communicate, explain, tell us how things work ) would not get hired. It isn't just experience - no one ever said it was.
I'm not sure that Sam always reads what I've written. He reads a tiny bit of it - stops - draws his conclusion and makes assumptions from that. If he had read the initial response, he wouldn't have written what he did above. It wasn't all about experience, not by far.
DBA
sam, October 31, 2011 - 12:26 pm UTC
Tom:
I was reading your statement
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
o draw me a picture of Oracle on that white board. Show me you know what processes/threads there are, what their jobs are. Describe all of the files involved in the database, what they do, what they mean. Show how they interact.
8 out of 10 people give me a blank start on this as if I'm kidding.
1 out of 10 people do it totally wrong
maybe, just maybe, 1 out of 10 people do it and do it right.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
This tells me 5% of the DBA can pass your test because you are even saying *maybe* 1 out of 10.
But now you are saying 50% are above average???
Ved was right. I was not fortunate to work with smart and experienced people. Most were dummies. Most of my oracle learning is from you or self-taught.
oracle training
A reader, September 04, 2012 - 7:08 pm UTC
Hi Tom,
I wanted to teach basic SQL/PLSQL programming to students who are finishing school or just finished school for a fee. Do i need to have any oracle license for teaching in a commercial way? I would not be developing any commercial software or anything like that.
Thanks!
September 10, 2012 - 7:24 pm UTC
yes you would be developing commercial software - you'd be making a living off of it, doesn't get much more commercial than that
You can use XE - express edition - for free. It should more than meet your needs.