jump to navigation

Friday Philosophy – The Tech to Do What You Need Probably Exists Already November 30, 2012

Posted by mwidlake in Friday Philosophy, Perceptions.
Tags: , , ,
4 comments

How many of you have read the Oracle Concepts manual for the main version you are working on?

This is a question I ask quite often when I present and over the last 10 years the percentage number of hands raised has dropped. It was always less than 50%, it’s been dropping to more like 1 in 10 and Last year (at the UKOUG 2011 conference) was the nadir when not a single hand was raised. {Interestingly I asked this at the Slovenian User Group 3 months ago and something like 40% raised their hand – impressive!}.

Why do I feel this is important? Well, do you know all the technology solutions available across just the core RDBMS with no cost options?. No, you don’t, you (and I) really don’t. If you read the concepts manual, even just skimming it, you will be reminded of a whole load of stuff you have only dim memories of and perhaps you will even see some features that passed you by when they were first introduced.

Of course, you would need to read a few more manuals to get the full picture, such as the PL/SQL Packages and Types References, as so much good stuff is introduced via built-in packages, and the SQL Language Reference, as SQL has been extended quite a lot over the last couple of versions. Dull reading indeed but I’d estimate that if you read those three you would be aware of 90%+ of the Oracle technologies that are available to you out-of-the-box and considerably more than all but a handful of Oracle Experts. You’d know more than I as (a) I have not skimmed the PL/SQL one for years and (b) I have a rotten memory.

My point it, you can’t consider using Oracle technologies you don’t know about or remember – and they could be just what you need to fix the problem you see in front of you.

I’ll give a couple of examples.

Problem, physical IO is too high, your storage system is bottle-necked.
Answers, Reducing Physical IO:
-First up, Index Organized tables. Some of you will be aware that I am very keen on IOTs and the reason I am is that I’ve used them to physically group data that the application needed to select over and over again. It can make a massive improvement to that sort of system. They are rarely used.
-Clustered Tables. Even less used, in fact has anyone reading this used them in anger in the last 10 years? Great for situations where you need parent-children or parent-children-type-1+parent-children-type-2 data. I confess, I have not used them in anger for years.
-Move table (and order them as you do it!) and re-build indexes to remove “dead” space. This one got a bad name, especially the index rebuilds, as people were doing it needlessly without appreciating what the intention was, but now I hardly ever see it done – even when it is of benefit.
-Compress your tables and indexes. With normal Oracle compress (no need for HCC). In tests I’ve done I pretty much always see a drop in physical IO and run time. Being candid, I can’t remember doing any tests and NOT seeing an improvement but I usually only test when I expect and improvement and I don’t want to give anyone the impression it will always help.

All the above were available in Oracle 7 or 8 and all have improved over the versions.

Problem, you want to carry out some long,complex data processing in PL/SQL and if a step fails, be able to handle it and carry on.
Answers:
– Savepoints. You can rollback to a savepoint, not just to the last commit. In this way you can break the task into chunks and, if something towards the end fails, you roll back one step (or several, your choice) and call an alternative routine to handle the exception.
– Autonomous Transactions. You want to record that an error occurred but not fail the original action or not save anything it has done to date. An autonomous transaction runs in it’s own sub-session and commits in it do not effect the calling session.
– Temporary Tables. You can put your working information in them as you progress and if you need to bomb out (or some evil DBA kills your session for running too long) the temp table contents just disappear. No clean up needed.

Maybe the above is not so fair, I have not been a proper PL/SQL developer for a while now, but I hardly see the above used. Especially Savepoints. I can’t remember not having savepoints available (hmm, maybe Oracle 6) and Autonomous Transactions and Temporary tables are Oracle 8 (I thought Temporary tables might be 9 but Tim Hall’s OracleBase says 8)

Another thing I have noticed over the years is that so often I will read up on some oracle feature I know little about, only for it to come up in the next few weeks! There is a psychological aspect to this, that we only remember these “coincidences” and not the more common situations where we read up on something which does not subsequently come up before we forget about it, but I think that it is also that we tend to use only a few solutions to solve the problems we see and adding another solution to our list means the chances are high it will be suitable for something soon.

OK, so it would help us all to read the manuals (or other Oracle technical books) more. Now the big problem is finding the time.

Friday Philosophy – Whatever Happened to Run Books? July 27, 2012

Posted by mwidlake in Friday Philosophy.
Tags: ,
3 comments

I realised recently that it is many years since I saw what used to be called a Run Book or System Log Book. This was a file – as in a plastic binder – with sheets of paper or printouts in it about a given system. Yes, this was a while back. It would often also have diagrams {occasionally drawn by hand on scraps of paper – so that would be the database ERD then}, hand-written notes and often the printed stuff would have scribbles against it.

{BTW I asked a colleague if he remembered these and when he said he did, what he used to call them – “err, documentation???”. Lol}

There was one book per key system and you could tell if a system was key (that is, Production, or a development system where a large development manager would punch you in the eye for losing anything, or any system the DBAs wanted) as it had a run book. It held information that was important about the system and, although you could look up most of it when logged onto the system itself, was useful to grab and just check something. However, it was vital if you had to recover the system.

Being a DBA-type, the run books I used to see and use were database focused. The front page would have the SID, name, host name (and even the spec of the host), version, tnsnames info, block size, backup strategy and schedule and, very importantly, the system owner. Yes, the big guy who would be upset if you lost the system. In there you would have printouts of the tablespaces, datafiles and sizes, the backup script, users (and passwords, very often), reference data tables, filesystem layout, OS user details and anything else
needed to recover the system.

This was an evolving and historical set of data. I mentioned above that you would have maybe scraps of paper from when a design session had come up with an alteration to the system. Corrections would often be done by hand. When you printed off the tablespace sizes on Monday, you did not throw the old one away but just added the new one, so you had information about the growth of the DB going back in time. Once in a while you might thin out the set but you kept say one a month.

It was actually that which got me to thinking about runbooks. At a site recently one of the DBAs was asking me if I knew of a screen in OEM that showed the growth of space used over time and my immediate thought was “well look in the run book” {I was very tired that day and losing my grip on reality}. Not being able to find a screen for what he wanted and knowing the data in OEM/AWR was only going back a month anyway, I suggested a simple spreadsheet that he could maintain. With the run book you could flip to the printouts of tablespace sizes, grab a piece of paper and do something lo-tech like this:

This would take less time than firing up Excel, typing the figures in, getting the graph wrong 3 times and then printing it out. Though if you had to go show Managers how the data was growing, you invested that time in making it pretty {why do high level managers insist on “pretty” when what they really want is “informative”?}

So why have Run Books gone {and does anyone out there still use them, in physical or electronic format}? It certainly seemed standard practice across IT in the 80’s and 90’s. I suspect that the reason is that most of the information that used to go into them is now available via online GUI admin tools and looking at them is actually faster than going and grabbing a physical book. Besides, if your DBA or Sys Admin team is split between UK, India and Australia, where do you keep a physical book and allow everyone to check it? I have vague memories of electronic Run Book applications appearing but they never seemed to get traction.

That is one of the drawbacks of using GUI admin tools. No, this is not just some tirad by a bitter old lag against GUI tools – they are generally a massive improvement on the old ways – but they are not perfect. Most of them only hold a short history and printing out the data is often tricky or impossible. All you can really do is screen dumps. No one has those little scripts for listing out basic information anymore {except us bitter old lags} as they have GUIs to do all that and, heck, I can’t go printing off a load of stuff on paper and sticking it in a binder – that is so 20th century!

Maybe I’m being unfair and OEM has a “run book” section I have simply never seen – but I’ve never seen it. If it is/was there, how many people would use it?
I do miss the Run Book though. Especially the ease with which I could look up all those passwords…

Oracle documentation on a Kindle January 18, 2012

Posted by mwidlake in publications.
Tags: ,
7 comments

I recently bought myself a Kindle – the keyboard 3G version. Keyboard as I know I will want to add notes to things and the 3G version for no better reason than some vague idea of being able to download things when I am away from my WiFi.

So, how about getting Oracle documentation onto it? You can get the oracle manuals as PDF versions (as opposed to HTML) so I knew it was possible and that others have done so before. A quick web search will show a few people have done this already – one of the best posts is by Robin Moffat.

Anyway, this is my take on it.

1) Don’t download the PDF versions of the manuals and then just copy them onto your kindle. It will work, but is not ideal. PDF files are shown as a full page image in portrait mode and parts are unreadable. Swap to landscape mode and most text becomes legible and you can zoom in. In both modes there is no table of contents and none of the links work between sections. All you can do is step back and forth page by page and skip directly to pages, ie goto page 127. This is not so bad actually as quite often the manual states the page to go to for a particular figure or topic.

2) Do download the MOBI format of the manuals you want, if available. Oracle started producing it’s manuals in Mobi and Epub format last year. I understand that Apple’s .AZW format is based on .MOBI (Mobipocket) format. As such text re-flows to fit the screen of the Kindle. I’ve checked a few of the DBA_type manuals for V10 and V11 and Mobi files seem generally available, but not a couple I checked for 10.1. If there is no Mobi, you can still revert to downloading the PDF version.

3) You cannot download a set of manuals in this format and you won’t see an option to download an actual manual in MOBI format until you go into the HTML version of the document.

I can understand that it would be a task for someone in Oracle to go and create a new downloadable ZIP of all books in these formats or, better still, sets to cover a business function (like all DBA-type books and all developer-type books), but it would be convenient.
Anyhow, go to OTN’s documentation section, pick the version you want and navigate to the online version of the manual.

Here I go to the 11.2 version – note, I’m clicking on the online set of manuals, not the download option.


Select the HTML version of the document you want, in this case I am grabbing a copy of the performance tuning guide. As you can see, this is also where you can choose the PDF version of the manual

Once the first page comes up, you will see the options for PDF, Mobi and Epub versions at the top right of the screen (see below). I wonder how many people have not realised the manuals are now available in new ebook formats, with the option only there once you are in the manual itself?

I’ve already clicked the Mobi link and you can see at the bottom left of the screen-shot, it has already downloaded {I’m using Chrome, BTW}. Over my 4Mb slightly dodgy broadband connection it took a few seconds only.

4) I don’t like the fact that the files are called things like E25789-01.mobi. I rename them as I move them from my download directory to a dedicated directory. You then attach up your kindle to your computer and drag the files over to the kindle’s “documents” folder and, next time you go to the main menu on the kindle, they will appear with the correct title (irrespective of you renaming them or not)

5) If you download the PDFs I would strongly suggest you rename these files before you move them to the kindle as they will come up with that name. I have a booked called e26088 on my kindle now – which manual is that? {don’t tell me, I know}. I have not tried renaming the file on the kindle itself yet.

6) You don’t have to use a PC as an intermediate staging area, you can directly download the manuals to your kindle, if you have a WiFi connection. Go check out chapter 6 of the kindle user guide 4th edition for details, but you can surf the web on your kindle. Press HOME, then MENU and go down to EXPERIMENTAL. click on “Launch Browser” (if you don’t have wireless turned on, you should get prompted). I’d recommend you flick the kindle into landscape mode for this next bit and don’t expect lightning fast response. If it does not take you to the BOOKMARKS page, use the menu button to get there and I’d suggest you do a google search for OTN to get to the site. Once there navigate as described before. When you click on the .Mobi file it should be downloaded to your kindle in a few seconds. Don’t leave the page until it has downloaded as otherwise the download will fail.

There you go, you can build up whatever set of oracle manuals you like on your ebook or kindle and never be parted from them. Even on holiday…

I’ve obviously only just got going with my Kindle. I have to say, reading manuals on it is not my ideal way of reading such material. {story books I am fine with}. I find panning around tables and diagrams is a bit clunky and the Kindle is not recognising the existence of chapters in the Oracle Mobi manuals, or pages for that matter. However, the table of contents works, as do links, so it is reasonably easy to move around the manual. Up until now I’ve carried around a set of Oracle manuals as an unzipped copy of the html download save to a micro-USB stick but some sites do not allow foreign USB drives to be used. I think I prefer reading manuals on my netbook to the kindle, but the kindle is very light and convenient. If I ever get one of those modern smart-phone doo-dahs, I can see me dropping the netbook in favour of the smartphone and this kindle.

Of course, nothing beats a big desk and a load of manuals and reference books scattered across it, open at relevant places, plus maybe some more stuff on an LCD screen.

Lack of Index and Constraint Comments November 24, 2011

Posted by mwidlake in Architecture, database design, development.
Tags: , , , ,
10 comments

Something I’ve just reminded myself of is that under Oracle you cannot add a comment on an index or a constraint. You can only add comments on tables, views, materialized views, columns of those object types and a couple of esoteric things like Operators, Editions and Indextypes.

Here is an example of adding comments to tables and columns:

set pause off feed off
drop table mdw purge;
create table mdw(id number,vc1 varchar2(10));
comment on table mdw is 'Martin Widlake''s simple test table';
comment on column mdw.id is 'simple numeric PK sourced from sequence mdw_seq';
comment on column mdw.vc1 is'allow some random text up to 10 characters';
--
desc user_tab_comments

 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 TABLE_NAME                                            NOT NULL VARCHAR2(30)
 TABLE_TYPE                                                     VARCHAR2(11)
 COMMENTS                                                       VARCHAR2(4000)

--
select * from dba_tab_comments where table_name='MDW'
/
OWNER                          TABLE_NAME                     TABLE_TYPE
------------------------------ ------------------------------ -----------
COMMENTS
----------------------------------------------------------------------------------------------------
MDW                            MDW                            TABLE
Martin Widlake's simple test table

select * from dba_col_comments where table_name='MDW'
order by column_name
/
OWNER                          TABLE_NAME                     COLUMN_NAME
------------------------------ ------------------------------ --------------
COMMENTS
----------------------------------------------------------------------------------------------------
MDW                            MDW                            ID
simple numeric PK sourced from sequence mdw_seq
MDW                            MDW                            VC1
allow some random text up to 10 characters
-- now to add a big comment so need to use the '-' line continuation character in sqlplus
--
comment on table mdw is 'this is my standard test table.-
 As you can see it is a simple table and has only two columns.-
 It will be populated with 42 rows as that is the solution to everything.'
/
select * from dba_tab_comments where table_name='MDW'
OWNER                          TABLE_NAME                     TABLE_TYPE
------------------------------ ------------------------------ -----------
COMMENTS
----------------------------------------------------------------------------------------------------
MDW                            MDW                            TABLE
this is my standard test table.  As you can see it is a simple table and has only two columns.  It w
ill be populated with 42 rows as that is the solution to everything.
--
/

Adding comments on tables, views and columns seems to have dropped out of fashion over the years but I think it is still a very useful feature of oracle and I still do add them (though I am getting a little slack about it myself over the last 3 or 4 years, which I must stop).

Comments are great, you can put 4000 characters of information into the database about each table, view and column. This can be a brief description of the object, a full explanation of what a column is to hold or even a list of typical entries for a column or table.

But you can’t add a comment on indexes or constraints. Why would I want to? Well, constraints and indexes should only be there for a reason and the reason is not always obvious from either the names of the columns or the name of the constraint or index, especially where you have a naming standard that forces you to name indexes and constraints after the columns they reference.

When you design a database, do a schema diagram or an ERD, you label your relationships between entities/tables. It tells you exactly what the relationship is. You might create an index to support a specific method of access or particular business function. You might alter the index in a way not immediately obvious to the casual observer, such as to allow queries that use the index to avoid having to visit the table. All of those things will, of course, be fully documented in the maintained project documentation in the central repository, available and used by all…

If I was able to add comments to constraints and indexes within the database then they would there. You move the system from one platform to the other, they are there. If for any wildly unlikely reason the central documentation lets you down, the information is always there in the database and easy to check. You may not be able to track down the original design documents but you have the database in front of you, so comments in that will persist and be very easy to find.

Lacking the ability to add comments on indexes and constraints, I have to put them at the table level, which I always feel is a kludge. I might actually raise an enhancement request for this, but as Oracle 12 is already nailed down, it will have to wait until Oracle 14. (A little bird told me Larry said there would be no Oracle 13…).

Friday Philosophy – PowerPoint Picture Pain July 21, 2011

Posted by mwidlake in Friday Philosophy, humour.
Tags: ,
16 comments

The title does not refer to the agony of sitting through a presentation with loads of pointless graphics and zip-all content. Rather, it encompasses the agony of trying to produce some decent graphical content for PowerPoint. Or Word or any other software-based method of presenting information. I think we generally had better presentations when we had overhead projectors or flip charts and I go “powerpoint naked” to present sometimes.

However, I do present quite often using the ubiquitous PowerPoint. I spend hours on my slides, both trying to put good textual content into them for both the actual presentation and also for anyone who downloads them later. I also spend ages trying to put together graphics as we humans are visual creatures and a good {let’s pause for a second and stress the word good} picture or diagram can help enormously. I did a little animated graphic a few years back to show how DNA is unzipped and duplicated, allowing the near perfect transmission of our DNA as cells divide. Modesty prevents me saying it was good but {drat, I just said it} but several people told me it really helped them understand something that text descriptions struggle to put across.

{Hmm, OK, I’ll see if I can get this to work. Try this how-dna-duplicates powerpoint show. It shows how the two strands of DNA are “matched”. A, C, T and G are the 4 letters of the DNA and each one is associated with its “pair” in the second strand. A to T, T to A, C to G and G to C. Split the pair into two strands and you can make each a perfect copy of the original by adding back the pairs. Understand? Oh, and there are very, very, very rare copy mistakes, in this case a C is matched to a T rather than an A. Go on, fire up the slide.}

The reality is though that most of my presentations have very average slides. When you present for the UK Oracle User group they are good enough to solicit feedback from the audience and feed it back to the presenter. You get scored on topic, content, style, slides, charisma, sex appeal… The lowest part of my score is always, Always, ALWAYS the slides. This started getting on my nerves so one year I tried really hard on the slides. It was still my weakest point.

The thing is, I have never had a graphics team (let alone a whole marketing department) to help me out. Either I have worked for a company with no such thing or I have been a lone consultant doing what I can in my spare time. It is especially frustrating as large companies that do have access to teams of graphic artists seem to present with a large number of very pretty, very well drawn slides with zip-all content and the graphics add almost nothing to understanding (and we are back to the first line of this posting). In fact, I often find that slides with lots of pretty icons representing part of the technology stack with chunky arrows sweeping across them showing flow to be {cough} pointless to my understanding. One thing I have picked up from all those pretty pictures though is that a feeling of depth and texture is very effective in altering how a slide looks.

So, I decided for my posts on Index Organised Tables I would spend some time messing around with such things in PowerPoint. My Graphic of the Thread is:

I’m quite pleased with it, especially as in doing so I found out quite a bit about how to use the gradient fills, shadowing, 3D formating and rotating of simple shapes. It is certainly better than my previous stock graphic for an index:

I think most people would agree, it is an improvement. But is it two days worth of effort improvement. Yes, that is how long I spent messing around with the image options in PowerPoint to replace a flat, uninspiring green blob with 3 lines on it to represent an index into a nicely shaped, pseudo-3D image with a tad more information. *sigh*.

I guess I can now convert other flat pictures made up of boxes, triangles and lines into something that look a bit more like a professional diagram than something knocked up by a database geezer late one evening the day before the presentation. But if it does not help me produce a diagram that aids understanding rather than just presentation fluff, like those marketing slides I’ve just wasted a lot of time.

I tell you what though, having spent so long on that diagram, I’m going to use it to death :-)

COMMAND_TYPE Values December 10, 2009

Posted by mwidlake in internals.
Tags: , ,
18 comments

Follow-up post on finding most of the COMMAND_TYPES in the data dictionary is here and thanks to Christian Antognini who’s comment led me in that direction.

Spoiler – See end for getting a full list of COMMAND_TYPE values.

If you are in the habit of looking at V$SQLAREA and V$SQL to see what is going on in the database {for those of use who occasionally step out of the “GUI DBA Tool” environment} you will see a lot of stuff in there. I tend to look for the most demanding SQL so I am not interested in PL/SQL, I just want to see the SQL executed via it.

To do this, you can ignore those entires in V$SQL/V$SQLAREA with a “COMMAND_TYPE=47″ filter.

This COMMAND_TYPE is useful. 3=SELECT, the most common thing you probablly see and look at, 6=UPDATE, 7=DELETE. 50=EXPLAIN.

If you google/bing/whatever COMMAND_TYPE you get many hits that list of some of the commands, all looking very similar. But very partial lists.

So I decided to amalgamate these partial lists, verrify what COMMAND_TYPE links to what commands in the databases I have access to and publish this fuller, verified list. I quickly found some commands do not stay in the SGA after issuing, so I could not confirm them {“drop index” being one}.

I got this far:

-- mdw 10/12/09
-- check for cmd types not seen before, as lists on web are not complete
-- * = I have verified
col sql_txt form a60
select command_type,sql_id,substr(sql_text,1,60) sql_txt
from gv$sqlarea
where command_type not in (
 1 --  create table  *
,2 --  INSERT        *
,3 --  SELECT        *
,6 --  UPDATE        *
,7 --  DELETE        *
,9 --  create index  *
,11 -- ALTER INDEX   *
,26 -- LOCK table    *
,42 -- ALTER_SESSION (NOT ddl)
--two postings suggest 42 is alter session
,44 -- COMMIT
,45 -- rollback
,46 -- savepoint
,47 -- PL/SQL BLOCK' or begin/declare *
,48 -- set transaction   *
,50 -- explain           *
,62 -- analyze table     *
,90 -- set constraints   *
,170 -- call             *
,189 -- merge            *
)
and rownum < 20
/

Please feel free to add to it.

Or save yourself the bother and check out the list provided in the Oracle documentation. Not under the descriptions for V$SQL or V$SQLAREA, which would have been the obvious place Mr Larry Ellison thank you, but under V$SESSION. . Further, the fact that in the V$SESSION table the column is called just COMMAND and not COMMAND_TYPE does not assist in locating this information (don’t google COMMAND and ORACLE, you get many millions of hits…). Just click the below.

This is the full table of values and meanings.

(This is the listing for 10.2 and 11.1 is very similar).

But it does not include 189 – MERGE, so that is one up for practical testing than just reading the manual :-)

Back to the day job…

Friday Philosophy – A Comment on Comments September 25, 2009

Posted by mwidlake in development, internals.
Tags: , , ,
3 comments

This blog is not about blog comments. It’s about table and column comments in the data dictionary.

Some of you may well be going “huh?”. Others are probably going “Oh yes, I remember them?”. Table and column comments appear to be suffering the same fate as ERDs, who’s slow demise I bemoaned a couple of weeks ago. They are becoming a feature not known about or used by those with “less personal experience of history” {ie younger}.

It’s a simple principle, you can add a comment against a table or a column,up to 4000 characters. {you can also add comments against index types, materialized views and operators (huh?), at least in 10.2}.

comment on table widlakem.person is
'Test table of fake people for training purposes, approx 50k records'
Comment created.

select * from dba_tab_comments
where owner='WIDLAKEM'
and table_name = 'PERSON'

OWNER      TABLE_NAME                     TABLE_TYPE
---------- ------------------------------ -----------
COMMENTS
--------------------------------------------------------------------------
WIDLAKEM PERSON TABLE
Test table of fake people for training purposes, approx 50k records

--

comment on column widlakem.person.second_forename is
'null allowed, second or middle name. If more than one, delimited by / character'

select * from dba_col_comments
where owner='WIDLAKEM' and table_name = 'PERSON'
and column_name='SECOND_FORENAME'

OWNER      TABLE_NAME                     COLUMN_NAME
---------- ------------------------------ ------------------------------
COMMENTS
-------------------------------------------------------------------------------
WIDLAKEM PERSON SECOND_FORENAME
null allowed, second or middle name. If more than one, delimited by / character

So you can stick basic information into the data dictionary where it will remain with the object until the object is dropped or the comment is updated. (You can’t drop a comment, you can only update it to ”:

>comment on table widlakem.person is ”;

It’s simple, it’s sensible, it’s solid.

And it seems to be dying out. In fact, I had stopped adding comments to my tables and columns as no one else seemed to bother. Probably as a consequence of them not being added, no one ever seemed to think to look at them to get hints about the database structure and table/column use.

But Raj sitting next to me is as old a hand at this game as myself and I “caught” him adding comments to the little schema we are working on together. Well, if he is bothering, so will I!

How about Oracle Corp? How do they manage on this front? After all, the Oracle Reference manual has all these short descriptions of tables and columns in the data dictionary {some helpful, some utterly unhelpful}:

select owner,count(*) from dba_tab_comments
where owner in ('SYS','SYSTEM','SYSMAN')
group by owner
OWNER        COUNT(*)
---------- ----------
SYSTEM            151
SYSMAN 472
SYS 3894

3 rows selected.

Heyyyy, nice Oracle.

select owner,table_name,comments
from dba_tab_comments
where owner in ('SYS','SYSTEM','SYSMAN')

OWNER      TABLE_NAME
---------- ------------------------------
COMMENTS
-----------------------------------------------------
SYS ICOL$

SYS CON$

SYS FILE$

SYS        UET$

SYS        IND$
SYSTEM     MVIEW_RECOMMENDATIONS
This view gives DBA access to summary recommendations
SYSTEM     MVIEW_EXCEPTIONS
This view gives DBA access to dimension validation results
SYSTEM     AQ$_QUEUE_TABLES
SYS        SEG$

SYS        COL$

SYS        CLU$
SYSTEM     SQLPLUS_PRODUCT_PROFILE

SYSTEM     PRODUCT_PRIVS

SYSTEM     HELP

SYSMAN MGMT_NOTIFY_QTABLE

SYSMAN AQ$MGMT_NOTIFY_QTABLE_S

Oh. Lots of blanks. Not so nice Oracle. No, scrub that, several lines are not blank, so Not a bad attempt Oracle.

Why all the blanks? Why have Oracle set blank comments? That’s because a blank table comment gets created when you create a table, and a blank column comment is created per column.

create table mdw_temp (col1 number);
Table created.

select * from dba_tab_comments where table_name = 'MDW_TEMP';
OWNER      TABLE_NAME                     TABLE_TYPE
---------- ------------------------------ -----------
COMMENTS
----------------------------------------------------------------------
WIDLAKEM   MDW_TEMP                       TABLE

1 row selected.

select * from dba_col_comments where table_name='MDW_TEMP';
OWNER      TABLE_NAME                     COLUMN_NAME
---------- ------------------------------ ---------------
COMMENTS
-------------------------------------------------------------------------
WIDLAKEM   MDW_TEMP                       COL1

1 row selected.

So what populated system-like comments do we have?

select owner,count(*) from dba_tab_comments
where owner in (‘SYS’,’SYSTEM’,’SYSMAN’)
and comments is not null
group by owner

OWNER COUNT(*)
———- ———-
SYSTEM 73
SYSMAN 15
SYS 944

OK, there are some, and as you can see below, some are more useful than others…

OWNER      TABLE_NAME
---------- ---------------
COMMENTS
----------------------------------------------------------------------------------------------------
SYS        SYSTEM_PRIVILEG
           E_MAP
Description table for privilege type codes.  Maps privilege  type numbers to type names
SYS        TABLE_PRIVILEGE
           _MAP
Description table for privilege (auditing option) type codes.  Maps privilege (auditing option) type
numbers to type names
SYS        STMT_AUDIT_OPTI
           ON_MAP
Description table for auditing option type codes.  Maps auditing option type numbers to type names
SYS        RESOURCE_MAP
Description table for resources.  Maps resource name to number
SYS        SESSION_PRIVS
Privileges which the user currently has set
SYS        SESSION_ROLES
Roles which the user currently has enabled.
SYS        ROLE_SYS_PRIVS
System privileges granted to roles
SYS        ROLE_TAB_PRIVS
Table privileges granted to roles
SYS        ROLE_ROLE_PRIVS
Roles which are granted to roles
Oracle_DatabaseInstance contains one entry for each Oracle Instance that is
centrally managed.  A Real Application Cluster has one entry for each of the
instances that manipulate it.  Instances of Oracle_DatabaseInstance are created
using the database instances that are known to the Oracle Enterprise Manager
repository.
SYS        DBA_AUDIT_OBJECT
Audit trail records for statements concerning objects, specifically: table, cluster, view, index, se
uence, [public] database link, [public] synonym, procedure, trigger, rollback segment, tablespace, r
ole, user
SYS        USER_AUDIT_OBJECT
Audit trail records for statements concerning objects, specifically: table, cluster, view, index, se
uence, [public] database link, [public] synonym, procedure, trigger, rollback segment, tablespace, r
ole, user
SYSMAN     ORACLE_DATABASESTATISTICS
Oracle_DatabaseStatistics provides current information about the statistics for
a database.  Database statistics pertain to the database and have the same
value regardless of the database instance that is used.
SYSMAN     ORACLE_DBINSTANCESTATISTICS
Oracle_DBInstanceStatistics contains statistics for a database instance.  These
are retrieved from the Oracle Managment Repository that is managing the
database upon request from a managment client.

If you don’t add comments to tables and comments, you will just have blank entries for them in the data dictionary.

So why not pop a few real comments in there, especially for any tables or comments where the name is not really telling you what that column or table is for? It’s free and easy, and it might just prove useful. And if you add them to your tables, I’ll add them to mine.

Follow

Get every new post delivered to your Inbox.

Join 158 other followers