Random Things: Volume #13 A brief respite for me...I get to work remotely this week.

Admittedly I have gotten into the habit, finally, of traveling, so it's a little strange to be home (never thought I would say that). There is no shortage of respect from me for those who travel all the time. It's a hard life.

OBIEE Performance
Our Usage Tracking reports are a tad slow and I've been looking into the logs trying to decipher them. Unlike the database which has a multitude of resources, OBIEE has hardly any. Christian did point me to this Mark Rittman article, which is good, but not great (not because the author is lacking...there just isn't that much to go on). rnm1978 suggested MOS, but I don't have access right now...besides, it seems to be having problems again anyway.

While I'm on the subject of performance and rnm1978, I should link up to this article which highlights 3 recent posts by Cary Millsap. All 3 are excellent reads and require your immediate attention.

Kate
The reason I am home this week is so that I can go to a Doctor's visit with Kate. We finally found a place where they might be able to help diagnose her. Most of her doctors have been more concerned with keeping her alive (healthy) and haven't worried too much about her developmental delays (still not talking...but she can sign "daddy"). Anyway, the place is called The Tridas Center and we're excited/nervous. Excited about finding her better help and nervous about the possibilities (more specifically, what a diagnosis would mean).

Labels: kate, obiee, random

  ¶ 2/08/2010 03:49:00 PM   2 Comments Links to this post

  PRIMARY KEY and NOT NULL I've seen this far too often. A table with a primary key (good) and a check constraint (NOT NULL) on the same column.

Stop doing it. Watch.

CREATE TABLE t
(
  id NUMBER
    CONSTRAINT pk_id PRIMARY KEY
);

SH@I_HAVE_NO_IDEA>INSERT INTO t ( id ) VALUES ( 1 );

1 row created.

Elapsed: 00:00:00.33
SH@I_HAVE_NO_IDEA>INSERT INTO t ( id ) VALUES ( NULL );
INSERT INTO t ( id ) VALUES ( NULL )
                              *
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SH"."T"."ID")

As HillbillyToad said,



It is better than no constraint, that's for sure. The heart was in the right place...

Labels: constraints, development, sql, wtf

  ¶ 2/03/2010 07:46:00 PM   13 Comments Links to this post

  TNS-12533: TNS:illegal ADDRESS parameters I kept receiving this error recently but until today, never bothered to investigate it. To get around it, I would just comment out the bottom half (where the problem was) of the tnsnames file.

Today I had enough.

I removed all but the top most entry, saved the file and did a tnsping <first_entry>.

OK (40 msec)

I added in the second entry, tnsping <second_entry>

OK (210 msec)

I added in the third entry

TNS-12533: TNS:illegal ADDRESS parameters

Removed it.

Success.

WTF?

 TESTING = 
  (DESCRIPTION = 
    (ADDRESS = 
      (PROTOCOL = TCP)
      (HOST = localhost)
      (PORT = 1521)
    )
    (CONNECT_DATA = 
      (SERVICE_NAME = TESTING)
    )
  )

It's probably difficult to see...maybe a picture would do better.



How about now?



Still don't see it?



I almost gave up as well.

This should help.



A freaking space? Really? 8+ years and this is the very first time I am seeing this...seems strange I haven't encountered this before.

Labels: debug, ORA-12533

  ¶ 2/02/2010 09:16:00 AM   1 Comments Links to this post

  Database Table Size I've always wondered how big a table is...up until recently I depended on the DBAs to retrieve such information for me.

Thanks to my good, and very helpful, friend, Mr. Thomas Roach, I no longer have to wait or bother the DBAs.

%_SEGMENTS contains a column called BYTES. Use this column to determine the size of your table, with just a little math.

SELECT segment_name, SUM( bytes ) / 1024 / 1024 mb
FROM user_segments
GROUP BY segment_name
ORDER BY 1
/

SEGMENT_NAME                           MB
------------------------------ ----------
BMP_DIVNBR_CUST                    13.375
BMP_DIVNBR_JOINFACT               37.0625
BMP_DIVNBR_PROD                    13.375
BMP_DIVNBR_SALES                  78.6875
CUST                                  940
DIV                                  .125
IDX_CUSTSKDIVNBR_SALES          2676.6875
IDX_PRODSKDIVNBR_SALES          2701.6875
JOIN_FACT                        4298.125
PIM                                   312
PK_DIVSK                             .125
PK_PIMSK                                8
PK_TMSK                               .25
PROD                            2274.4375
SALES                          116122.375
TIME                                 .875
UQ_CUSTSKDIVNBR_CUST               40.125
UQ_PRODSKDIVNBR_PROD                  144

There's a plethora of these scripts out in the wild...but I was originally inspired by helping Mr. Neil Kodner out back in November (which I refer to as the "missing" month). Read his take on it here.

Labels: dba, howto, sql

  ¶ 2/01/2010 11:47:00 AM   6 Comments Links to this post