=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= SEARCHDATABASE.COM | Database Administrator Tips April 3, 2002
Essential tools and advice for the enterprise DBA: http://searchDatabase.techtarget.com/tips/ =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= ___________________________________________________________________ *************** FREE WINDOWS DECISIONS CONFERENCE ***************** WHO SHOULD ATTEND: IT professionals who want to gain focused expertise on Active Directory, security, licensing, manageability, interoperability and more. Plus strategies to make the smartest Windows technical and purchasing decisions to maximize ROI. WHEN: May 8-10, 2002. WHERE: Hilton Chicago Hotel. HOW: Qualify for FREE attendance directly at http://ad.doubleclick.net/clk;3903337;5058249;m?http://www.windowsdecisions.com/ ___________________________________________________________________ IN THIS ISSUE | Table of Contents 1. DATABASE ADMINISTRATOR TIPS - SQL Server: Re-indexing to improve query performance - Oracle: Move multiple tables belonging to a particular schema - Oracle: How to avoid "mutating table" errors - SQL Server: Kill blocking connections - Oracle: Dynamic time sequence query - More recent tips from members 2. IMPORTANT ANNOUNCEMENTS AND LINKS - Against the Grain: SQL's relational infidelity, and more... - Ask the Expert: SQL Server, DB2, & Oracle questions answered - Featured Topic: XML and databases ___________________________________________________________________ THIS WEEK'S TIPS: =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= RE-INDEXING TO IMPROVE QUERY PERFORMANCE | Jyoti Bhatia Often when large updates are carried out against tables you may experience poor query performance in terms of response time. This requires rebuilding the indexes which can be done in either of the following two ways... >> CLICK for the full tip... http://www.searchDatabase.com/tip/1,289483,sid13_gci809887,00.html =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= MOVE MULTIPLE TABLES BELONGING TO A PARTICULAR SCHEMA | Jai Tanwar If you are running a number of schemas under one service -- which is usually the case -- and you need to move all the tables belonging to a particular schema to a new tablespace, then this script is for you. >> CLICK for the full tip... http://www.searchDatabase.com/tip/1,289483,sid13_gci809877,00.html =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= HOW TO AVOID "MUTATING TABLE" ERRORS | Pravesh Gupta Sometimes you may need to do an insert or update in the same table on which your trigger fires, resulting in a "Mutating Table" error. This can be avoided by writing a procedure, function or package that declares the pragma autonomous_transaction. >> CLICK for the full tip... http://www.searchDatabase.com/tip/1,289483,sid13_gci799188,00.html =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= KILL BLOCKING CONNECTIONS | Carl F. Federl Many client-server applications do not close their database connections when terminated abnormally or should a user power off their client. This can result in un-committted database updates blocking other database connection updates. This stored procedure will kill the blockers that have not issued a database command in the specified number of seconds. >> CLICK for the full tip... http://www.searchDatabase.com/tip/1,289483,sid13_gci813648,00.html =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= DYNAMIC TIME SEQUENCE QUERY | Dusan Djuric Suppose you need to see a full (10 minutes, half-hour, one hour, etc) time map for a given date with the user_objects changes counter. And you need to do this in one SELECT statement, with no additional tables, views, packages, procedures, or functions. Is it possible? Yes, it is! Here's how. >> CLICK for the full tip... http://www.searchDatabase.com/tip/1,289483,sid13_gci799185,00.html __________________________________________________________________ MORE RECENT TIPS FROM SEARCHDATABASE MEMBERS: =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= ORACLE EXCEPTION HANDLING | by Jeffrey Toth http://www.searchDatabase.com/tip/1,289483,sid13_gci811973,00.html Demonstrates how to use nested blocks and exception handlers to control the flow of execution when exceptions are raised. ODBC IP CONNECTION TROUBLESHOOTING | by Tim Fenner http://www.searchDatabase.com/tip/1,289483,sid13_gci804490,00.html A tip on how to resolve MDAC and connection issues in SQL Server 7 & 2000. NOTIFY ORACLE APPLICATION USERS | by Shankar Govindan http://www.searchDatabase.com/tip/1,289483,sid13_gci799186,00.html Here is a script that spools the Mail ID's of Oracle Application users and mails them whatever you want to notify them about. EXECUTE A STATEMENT AGAINST A DYNAMIC LIST | by Hector Mejia http://www.searchDatabase.com/tip/1,289483,sid13_gci809889,00.html Need to execute a statement that performs an action validating against a list of values, but the list changes from one run to the other? Or perhaps the list is built dynamically within a stored procedure? Here's how. Hundreds more free tech tips submitted by members: http://searchdatabase.techtarget.com/tips/0,289484,sid13_tax281808,00.html Share your knowledge, submit a tip: win a CENDYNE CD-RW DRIVE http://searchdatabase.techtarget.com/tipsPrize/0,289492,sid13_prz805991_cts805990,00.html ___________________________________________________________________ IMPORTANT MESSAGES AND LINKS: =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= AGAINST THE GRAIN | Contrarian views by Fabian Pascal In this month's column, Fabian expounds on these hot button topics: * why lobby DBMS vendors to make better products * data fundamentals education vs. training * the failure of OO when applied to the DBMS * GUIs vs. database functionality * SQL's relational infidelity * the lack of support for constraint inheritance in SQL >> CLICK here for the column ... http://www.searchDatabase.com/tip/1,289483,sid13_gci812997,00.html =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= ASK THE EXPERT | This week's tough database questions answered SQL: Using CASE to obtain conditional SUMs http://www.searchDatabase.com/ateQuestionNResponse/0,289625,sid13_cid458223_tax289340,00.html DB2: Error calling a stored procedure from a trigger http://www.searchDatabase.com/ateQuestionNResponse/0,289625,sid13_cid458709_tax285651,00.html Oracle: Specifying and checking the rollback segment in PL/SQL http://www.searchDatabase.com/ateQuestionNResponse/0,289625,sid13_cid457812_tax289342,00.html SQL Server: Database not completing restore process, remaining in loading mode http://searchdatabase.techtarget.com/ateQuestionNResponse/0,289625,sid13_cid456123_tax289343,00.html Hundreds more free expert answers here: http://searchdatabase.techtarget.com/ateExperts/0,289622,sid13,00.html =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= XML AND DATABASES | SearchDatabase Featured Topic XML began as a simple markup language for defining and sharing documents containing structured data. Then came query features and database integration. Our tips and advice will help you understand and use XML with your DBMS. >> CLICK for our current Featured Topic... http://searchdatabase.techtarget.com/featuredTopic/0,290042,sid13_gci761146,00.html >> CLICK for previous Featured Topics... http://searchdatabase.techtarget.com/featuredTopics/0,290043,sid13,00.html ___________________________________________________________________ *********************SEARCHDATABASE CONTACTS*********************** TIP MAILBOX, (mailto:[EMAIL PROTECTED]) >> Submit your original tip here. TIM DICHIARA, Site Editor (mailto:[EMAIL PROTECTED]) >> Send your feedback and original articles. SARA CUSHMAN, Assistant Editor (mailto:[EMAIL PROTECTED]) >> Send your White Papers and favorite Web sites. ED PARRY, News Editor (mailto:[EMAIL PROTECTED]) >> Send your product announcements and poll ideas. TOM CLICK, Sales (mailto:[EMAIL PROTECTED]) >> Sponsor this or any other TechTarget newsletter. ___________________________________________________________________ **********************ABOUT THIS NEWSLETTER************************ Created by TechTarget (http://www.techtarget.com) TechTarget - The Most Targeted IT Media Copyright 2002, All Rights Reserved. To unsubscribe from 'Database Administrator Tips,' simply reply to this e-mail with REMOVE (all caps) within the Body or Subject or go to http://searchDatabase.techtarget.com/register, log in to edit your profile, click on the link to Edit email subscriptions, and uncheck the box next to the newsletter you wish to unsubscribe from. When finished, click "Save Changes to My Profile."
