jump to navigation

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…).

Missing information in SQL*Plus May 19, 2011

Posted by mwidlake in development.
Tags:
13 comments

By preference I like to work against the databse using SQL*Plus. GUIs are fine and they are much better for developing code than using sql*plus and notepad (or vi or whatever ascii text editor floats your boat). However, for finding things out quickly and also storing what I find (via spool files) then sql*plus is much better. As screens have got larger, I have used a larger – and wider – sql*plus session to show more information at one time.
But I have been having an issue with SQL*Plus not showing some information. I lose information on the right hand side of the screen.

This is on the V10 client on Windows by the way, I’ve seen it on windows XP to Vista.

Look at the below. It is quite a wide output and I have had to increase the standard 80 character linesize from 80 up to 120:

{BTW if you are looking at this in a small browser winder, you might have to click on the screen shot to open it up in it’s own window, to see what I mean – it is quite wide, that is the point of the post – it should just fit in my over-wide blog layout :-)}

set linesize 120

AUDIT_PROGRAM            entry_dt ENTRY_TS     AUDIT_TEXT                                                    aud_val1   aud_val2
------------------------ -------- ------------ -----------------------------------------------------
MM_STATS_GATHER          19-05-11 04.46.39.599 Index EVE_CAT_USER_FK_I Partition EVE_I_TOP                          0        100
MM_STATS_GATHER          19-05-11 04.46.39.571 Index EVE_IDX Partition EVE_I_TOP                                    0        100
MM_STATS_GATHER          19-05-11 04.46.39.556 Index EVE_AGR_CNTRPY_FK_I Partition EVE_I_TOP                        0        1
MM_STATS_GATHER          19-05-11 04.46.39.519 Index EVE_CAT_BANK_FK_I Partition EVE_I_TOP                          0        100
MM_STATS_GATHER          19-05-11 04.46.38.846 Table EVENTS Partition EVE_D_TOP                                     0        100
MM_STATS_GATHER          19-05-11 04.46.32.497 Index EVE_CAT_USER_FK_I Partition EVE_I_2011_07                  4,962       
MM_STATS_GATHER          19-05-11 04.46.20.787 Index EVE_IDX Partition EVE_I_2011_07                          437,500         
MM_STATS_GATHER          19-05-11 04.46.09.939 Index EVE_AGR_CNTRPY_FK_I Partition EVE_I_2011_07               91,
MM_STATS_GATHER          19-05-11 04.45.58.613 Index EVE_CAT_BANK_FK_I Partition EVE_I_2011_07                441,2
MM_STATS_GATHER          19-05-11 04.45.44.534 Table EVENTS Partition EVE_D_2011_07                           927,950          
MM_STATS_GATHER          19-05-11 04.45.41.207 Index EVE_CAT_USER_FK_I Partition EVE_I_2011_04                  6,324       
MM_STATS_GATHER          19-05-11 04.45.22.638 Index EVE_IDX Partition EVE_I_2011_04                          771,840         
MM_STATS_GATHER          19-05-11 04.45.17.024 Index EVE_AGR_CNTRPY_FK_I Partition EVE_I_2011_04              168,
MM_STATS_GATHER          19-05-11 04.45.05.463 Index EVE_CAT_BANK_FK_I Partition EVE_I_2011_04                848,0
MM_STATS_GATHER          19-05-11 04.44.55.474 Table EVENTS Partition EVE_D_2011_04                         1,625,100          
MM_STATS_GATHER          19-05-11 04.44.47.100 Index EVE_CAT_USER_FK_I Partition EVE_I_2011_01                    461       
MM_STATS_GATHER          19-05-11 04.44.23.829 Index EVE_IDX Partition EVE_I_2011_01                          800,020         
MM_STATS_GATHER          19-05-11 04.44.03.673 Index EVE_AGR_CNTRPY_FK_I Partition EVE_I_2011_01              158,

Note that for line 8 onwards there is no value for aud_val2. But then, some of the numbers in aud_val1 also look odd. But I’m getting output for some of the lines…

I am in fact getting some of the lines truncated. It just seems to be a bug in the V10 sql*plus client and it might pass you by as the first few lines usually come out “full length”. This truncation only happens if you increase the linesize.

To fix it, you need tell the “windows” sql*plus window how big your output now is. Click on the options and then environment menu items at the top left of the screen:

Change the SQL*Plus environment

Now set the screen buffer width to a bit more than it was, say by one character (and whilst you are at it, why not increasre the buffer length to 2000, the memory required is peanuts on today’s machines). I actually tend to set it to 140 to anticipate some of my more verbose scripts.

Alter the screen buffer width to one or two characters more

And confirm that you are happy to “truncate the buffer” – whoever coded this did not bother to check if you alter the values up or down – you can ignore this message if you are simply increasing the buffers.

Accept the prompt about truncation

Now when I run my sql statement I see the full output {Again, click on the image to see it’s full length to fully see how the problem is now fixed}:

AUDIT_PROGRAM            entry_dt ENTRY_TS     AUDIT_TEXT                                                    aud_val1   aud_val2
------------------------ -------- ------------ ------------------------------------------------------- -------------- ----------
MM_STATS_GATHER          19-05-11 04.46.39.599 Index EVE_CAT_USER_FK_I Partition EVE_I_TOP                          0        100
MM_STATS_GATHER          19-05-11 04.46.39.571 Index EVE_IDX Partition EVE_I_TOP                                    0        100
MM_STATS_GATHER          19-05-11 04.46.39.556 Index EVE_AGR_CNTRPY_FK_I Partition EVE_I_TOP                        0        100
MM_STATS_GATHER          19-05-11 04.46.39.519 Index EVE_CAT_BANK_FK_I Partition EVE_I_TOP                          0        100
MM_STATS_GATHER          19-05-11 04.46.38.846 Table EVENTS Partition EVE_D_TOP                                     0        100
MM_STATS_GATHER          19-05-11 04.46.32.497 Index EVE_CAT_USER_FK_I Partition EVE_I_2011_07                  4,962        100
MM_STATS_GATHER          19-05-11 04.46.20.787 Index EVE_IDX Partition EVE_I_2011_07                          437,500          5
MM_STATS_GATHER          19-05-11 04.46.09.939 Index EVE_AGR_CNTRPY_FK_I Partition EVE_I_2011_07               91,395         20
MM_STATS_GATHER          19-05-11 04.45.58.613 Index EVE_CAT_BANK_FK_I Partition EVE_I_2011_07                441,240          5
MM_STATS_GATHER          19-05-11 04.45.44.534 Table EVENTS Partition EVE_D_2011_07                           927,950          2
MM_STATS_GATHER          19-05-11 04.45.41.207 Index EVE_CAT_USER_FK_I Partition EVE_I_2011_04                  6,324        100
MM_STATS_GATHER          19-05-11 04.45.22.638 Index EVE_IDX Partition EVE_I_2011_04                          771,840          5
MM_STATS_GATHER          19-05-11 04.45.17.024 Index EVE_AGR_CNTRPY_FK_I Partition EVE_I_2011_04              168,840          5
MM_STATS_GATHER          19-05-11 04.45.05.463 Index EVE_CAT_BANK_FK_I Partition EVE_I_2011_04                848,060          5
MM_STATS_GATHER          19-05-11 04.44.55.474 Table EVENTS Partition EVE_D_2011_04                         1,625,100          1
MM_STATS_GATHER          19-05-11 04.44.47.100 Index EVE_CAT_USER_FK_I Partition EVE_I_2011_01                    461        100
MM_STATS_GATHER          19-05-11 04.44.23.829 Index EVE_IDX Partition EVE_I_2011_01                          800,020          5
MM_STATS_GATHER          19-05-11 04.44.03.673 Index EVE_AGR_CNTRPY_FK_I Partition EVE_I_2011_01              158,600          5

SID stays same, AUDSID alters June 24, 2010

Posted by mwidlake in internals.
Tags: ,
2 comments

In a couple of recent posts I described what AUDSID is and how you check out what you SID and AUSID are.

Well, your SID may be a little more stable than you think. In the below output you can see that as I use the SQL*Plus connect command to change users my AUDSID increases, new values being selected from the dictionary sequence, but my SID stays the same.

{the below is on 10.2.0.3, no RAC involved, using the windows SQL*Plus client. All usernames, passwords and sids are not real, to protect the innocent}.

   1* SELECT USER,sys_context('USERENV','SESSIONID'),SYS_CONTEXT('USERENV','SID') FROM DUAL
TDB> 
TDB> /

USER        SYS_CONTEXT('USERENV','SESSIONID') SYS_CONTEXT('USERENV','SID')
----------- ---------------------------------- ----------------------------
DWPERF      253779                             524

-- change user, same instance 
TDB> CONNECT MDW/MDW@TDB
Connected.
-- {By the way, the information below, plus some I cut from the output, is from my logon.sql script}
you are utilising central dba scripts

altering the default date format to be dd-mon-yyyy hh24:mi
pause on, pages 24 head on recsep off

TDB> SELECT USER,sys_context('USERENV','SESSIONID'),SYS_CONTEXT('USERENV','SID') FROM DUAL
  2  /

USER        SYS_CONTEXT('USERENV','SESSIONID') SYS_CONTEXT('USERENV','SID')
----------- ---------------------------------- ----------------------------
MDW         253782                             524

-- The SID is exactly the same. The AUDSID has increased by 3. This is a quiet dev/test system but
-- I am not the only user and my session had been alive for about 30 minutes, 
-- so I would guess two other people logged on in that time
-- However, would you have predicted that the SID would remain the same?
-- I will connect back to the original user

TDB> CONNECT DWPERF/DWPERF@TDB
Connected.

you are utilising central dba scripts

altering the default date format to be dd-mon-yyyy hh24:mi
pause on, pages 24 head on recsep off

TDB> SELECT USER,sys_context('USERENV','SESSIONID'),SYS_CONTEXT('USERENV','SID') FROM DUAL
  2  /

USER        SYS_CONTEXT('USERENV','SESSIONID') SYS_CONTEXT('USERENV','SID')
----------- ---------------------------------- ----------------------------
DWPERF      253783                             524

-- The SID stays the same still and the AUDSID goes up by one as it is a new session. No other new
-- sessions in the minute or so since I last changed accounts.
--
-- SYS is a different beast to all other accounts, so if I connect as SYS, will it force a new SID to be used?

TDB> CONNECT SYS/GUESS@TDB AS SYSDBA
Connected.

you are utilising central dba scripts

altering the default date format to be dd-mon-yyyy hh24:mi
pause on, pages 24 head on recsep off
TDB> SELECT USER,sys_context('USERENV','SESSIONID'),SYS_CONTEXT('USERENV','SID') FROM DUAL
  2  /

USER        SYS_CONTEXT('USERENV','SESSIONID') SYS_CONTEXT('USERENV','SID')
----------- ---------------------------------- ----------------------------
SYS         4294967295                         524

-- That did surprise me. I thought that not only would I get an AUDSID set to that special value but
-- that Oracle would create a new session to ensure it was clean.
--
-- Maybe the persistence of the SID is some trick of SQL*Plus? It knows I have not really logged out
-- of the database and just clears pointers and memory constructs, so I will log into a second instance 
-- and back...

TDB> connect dwperf/dwperf@TDB2
Connected.

you are utilising central dba scripts

altering the default date format to be dd-mon-yyyy hh24:mi
pause on, pages 24 head on recsep off

TDB2> SELECT USER,sys_context('USERENV','SESSIONID'),SYS_CONTEXT('USERENV','SID') FROM DUAL;

USER        SYS_CONTEXT('USERENV','SESSIONID') SYS_CONTEXT('USERENV','SID')
----------- ---------------------------------- ----------------------------
DWPERF      233882                             489

1 row selected.

TDB2> connect dwperf/dwperf@TDB
Connected.

you are utilising central dba scripts

altering the default date format to be dd-mon-yyyy hh24:mi
pause on, pages 24 head on recsep off
TDB> SELECT USER,sys_context('USERENV','SESSIONID'),SYS_CONTEXT('USERENV','SID') FROM DUAL;
any key> 

USER        SYS_CONTEXT('USERENV','SESSIONID') SYS_CONTEXT('USERENV','SID')
----------- ---------------------------------- ----------------------------
DWPERF      253785                             524

1 row selected.

-- Again I was surprised to see the same SID. The AUDSID has increased of course.
-- I do wonder if someone created a session whilst I did this test or if a new AUDSID values is selected
-- from the sequence and discarded by the SYS logon. I should test that...
--
-- What will happen if I log of SQL*Plus completely and log back in

TDB>exit


-- and now I log back in...
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jun 24 14:23:46 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options


you are utilising central dba scripts

altering the default date format to be dd-mon-yyyy hh24:mi
pause on, pages 24 head on recsep off
TDB> SELECT USER,sys_context('USERENV','SESSIONID'),SYS_CONTEXT('USERENV','SID') FROM DUAL;
any key> 

USER
------------------------------
SYS_CONTEXT('USERENV','SESSIONID')
--------------------------------------------------------------------------------
SYS_CONTEXT('USERENV','SID')
--------------------------------------------------------------------------------
DWPERF
253786
524

1 row selected. 
-- SAME ID!!!!

I must confess, I have never really looked into the persistance of SID before, I did think that Oracle would increment the SID with each and every logon and cycle around. However, it seems to me that the Oracle kernal keeps the constructs of a session around when that session logs out and re-uses it if another session is made quite soon afterwards. It makes sense, why destroy everything if you can re-use it soon after – it will speed up connections. As anyone who has had to deal with an oralce system with high session turnover knows, creating sessions is a very “heavy” task in Oracle. It seems lighter on 10 than 9 {and I am just going on “DBA Feel” when I say this, no concrete evidence} and I wonder if this re-use of SIDs is part of that. I should google it.

I am constantly guilty of starting a blog on one topic and drifting into another. So to keep up the pattern…

I had to edit my output to make the results of my check on USER, SID and AUDSID look OK in this posting. This is because, though your SID may be the same between SQL*Plus sessions, your column definitions ain’t. {If anyone thinks I should split this off to a dedicated BLOG post, just say so}

TDB> col user form a11
TDB> col SYS_CONTEXT('USERENV','SID') form a28
TDB> col SYS_CONTEXT('USERENV','SESSIONID') form a34

TDB> /
any key> 

USER        SYS_CONTEXT('USERENV','SESSIONID') SYS_CONTEXT('USERENV','SID')
----------- ---------------------------------- ----------------------------
DWPERF      253786                             524

-- col commands seem not to be persisted in SQL*Plus when you log onto a new session

TDB> connect mdw/mdw@TDB
Connected.

you are utilising central dba scripts

altering the default date format to be dd-mon-yyyy hh24:mi
pause on, pages 24 head on recsep off
TDB> SELECT USER,sys_context('USERENV','SESSIONID'),SYS_CONTEXT('USERENV','SID') FROM DUAL;
any key> 

USER
------------------------------
SYS_CONTEXT('USERENV','SESSIONID')
--------------------------------------------------------------------------------
SYS_CONTEXT('USERENV','SID')
--------------------------------------------------------------------------------
ODS
253795
524

1 row selected.

TDB>

-- You see?
-- An easier way to demo this is to check the defined variables and col settings with the
-- very useful, and often overlooked, naked  DEFINE and COL commands

TDB> define
DEFINE _DATE           = "24-JUN-2010 14:31" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "TDB" (CHAR)
DEFINE _USER           = "MDW" (CHAR)
DEFINE _PRIVILEGE      = "" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1002000100" (CHAR)
DEFINE _EDITOR         = "Notepad" (CHAR)
DEFINE _O_VERSION      = "Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options" (CHAR)
DEFINE _O_RELEASE      = "1002000300" (CHAR)
DEFINE V_NEWPRO        = "TDB" (CHAR)
TDB> col
SP2-0045: * no COLUMN defined
-- and just to show COL shows defined column definitions...
TDB> col eric form a30
TDB> col
COLUMN   eric ON
FORMAT   a30

TDB>
Follow

Get every new post delivered to your Inbox.

Join 161 other followers