TABLE OF CONTENTS 1) POP QUIZ (answer at the end) 2) Did you know ... 3) Oracle Professional 4) SQL Tip 5) In the News 6) ANSWER TO THE POP QUIZ --------------------------------------------------------- 1) POP QUIZ (answer at the end) --------------------------------------------------------- What does ADDM stand for in Oracle Database 10g? --------------------------------------------------------- 2) Did you know ... --------------------------------------------------------- ... that there are quite a few new books on Oracle 10g in the market. Take Oracle Press as an example, they have books on Oracle 10g, covering topics such as SQL, and new features. Check them out at: http://shop.osborne.com/cgi-bin/oraclepress/ --------------------------------------------------------- 3) Oracle Professional --------------------------------------------------------- In the upcoming issue of Oracle Professional, Oracle expert Steven Feuerstein examines how to extend PL/SQL file I/O capabilities with Java. Gary Menchen discusses pipelined table functions. And if you’re interested in Oracle 10g’s regular expressions, then you should read Parin Jhaveri’s article on the topic. Here’s how you can access Oracle Professional: http://www.pinpub.com/op/ --------------------------------------------------------- 4) SQL Tip --------------------------------------------------------- This tip came from Dan Clamage (http://www.clamage.com/) and is useful for those who are still running Oracle 8i. A Junior DBA was having trouble shrinking a VARCHAR2 column from 10 to 7 bytes. He was planning to drop the table and completely rebuild it. I said, "There's a better way". Maybe not quicker, but at least with this approach he doesn't have to mess around with foreign keys, indexes, not null columns, and such. Here is what I sent him: Here is the script I used to change master_store.retail_number from 10 down to 7 chars. Note that the master_store table does not need to be dropped, nor do any relational constraints need to be disabled. Run each piece separately to make sure they work. -- new bag to capture existing data alter table master_store add (new_retail_number varchar2(7)) / -- trim data down and stuff into smaller bag -- if the data is shorter than 7 then just set it to the original update master_store set new_retail_number = NVL(SUBSTR(RETAIL_NUMBER,4,7), RETAIL_NUMBER) / -- pitch the old bag alter table master_store drop (retail_number) / -- old bag, reborn! alter table master_store add (retail_number varchar2(7)) / -- fill the "old" bag back up update master_store set retail_number = new_retail_number / -- reapply constraints upon it alter table master_store modify (retail_number not null) / -- rebuild index that was upon it -- NOTE you might need to Reverse DDL the index -- (as I did BEFORE dropping the column earlier) -- if you're on a different instance CREATE INDEX indx_retail_num ON master_store ( retail_number ASC ) PCTFREE 10 INITRANS 2 MAXTRANS 255 TABLESPACE xref_indx STORAGE ( INITIAL 532480 NEXT 655360 PCTINCREASE 0 MINEXTENTS 1 MAXEXTENTS 200 ) / -- pitch the used (now defunct) bag alter table master_store drop (new_retail_number) / -- done The reason I say this is good for pre-9i users is that in 9i, you can now issue an ALTER TABLE MODIFY command and reduce the size of a column as long as there’s no data in the table that is violating the new reduced column length. -------------------------------------------------------- 5) In the News -------------------------------------------------------- Tom Siebel is stepping down as CEO of Siebel Systems, a leader in the customer relationship management software market. IBM executive Mike Lawrie is joining Siebel as the new CEO, while Mr Siebel himself will remain chairman and employee of the company. Mr Siebel’s move is not unexpected, as he follows the lead of other companies to split the chairman and CEO roles. Nevertheless, the move is also seen as an attempt to inject new blood into the executive ranks. After all, Siebel has faced daunting challenges in recent years, resulting from the tech industry downturn, as well as increased competition from SAP, Oracle and saleforce.com. It will be interesting to see how the new CEO will turn around the company and regain its glory days. Find out more at: http://story.news.yahoo.com/news?tmpl=story&cid=509&ncid=738&e=11&u=/ap /20040503/ap_on_bi_ge/siebel_shake_up --------------------------------------------------------- 6) ANSWER TO THE POP QUIZ --------------------------------------------------------- ADDM stands for Automatic Database Diagnostic Monitor and is one of the key components of the new intelligent infrastructure in Oracle Database 10g. It is a self-diagnostic engine built right into the database kernel and automatically monitors the database in intervals of 30 minutes by default. ADDM doesn’t just identify problems, but it also reports how much impact each of the problems are having on overall system performance and how much benefit can be gained by resolving it. This helps DBAs and developers focus on fixing problems that have the biggest impact. The information collected in ADDM can be viewed in graphical form and details can be further drilled into using the new automatic advisors introduced in Oracle 10g.