Lack of Index and Constraint Comments November 24, 2011
Posted by mwidlake in Architecture, database design, development.Tags: Architecture, data dictionary, design, documentation, sql*plus
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: sql*plus
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: data dictionary, sql*plus
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>
