SEARCHDATABASE.COM | Database Administrator Tips June 26, 2002 =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= FROM OUR SPONSOR:
Free Database Health Check http://searchDatabase.com/r/0,,4217,00.htm?freehealthcheck =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= IN THIS ISSUE | Table of Contents 1. DATABASE ADMINISTRATOR TIPS - SQL Server: In-line functions in SQL Server 2000 - SQL Server: Avoiding the mouse in Enterprise Manager - Oracle: Monitoring daily archive log switches - Oracle: Hide a user password - Oracle: Tip for installing Oracle 8i on Solaris - More recent tips from members 2. IMPORTANT ANNOUNCEMENTS AND LINKS - Free webcast: Developing very large databases with SQL Server - June training special: SQL data management - Ask the Experts: You tough database questions answered - Featured Topic: Object databases: Pros, cons - DB2 tips wanted! ___________________________________________________________________ **************** Sponsored by: Quest Software ********************* Are your end-users feverish about database response time? Increase performance and availability with a FREE health check from the database-tuning gurus at Quest Software! With Quest's renowned real-time and historical diagnostics - plus Quest's industry-leading resolution tools - Quest's FREE health check is good for what ails your database. Click here to register today! http://searchDatabase.com/r/0,,4217,00.htm?freehealthcheck ___________________________________________________________________ ___________________________________________________________________ THIS WEEK'S TIPS: =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= IN-LINE FUNCTIONS IN SQL SERVER 2000 | Mark Oldfield Functions are one of the most under-appreciated new features of SQL Server 2000. It is definitely worthwhile creating a "tool-kit" of commonly used functions to use in each new project. This tip includes a couple of scalar functions that can save lots of unnecessary temp table creating and cursor usage. >> CLICK for the full tip... http://www.searchDatabase.com/tip/1,289483,sid13_gci834313,00.html =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= AVOIDING THE MOUSE IN ENTERPRISE MANAGER | Rac Coons Quick tip: When you want to run a query in SQL Server Query Analyzer, you can press the "F5" key, but there is no equivalent in Enterprise Manager. However, you can press the Windows Popup Menu button and then press the "R" button to pop up the context menu and select "Run" (as well as the other functions offered). >> CLICK for the online version of this tip... http://www.searchDatabase.com/tip/1,289483,sid13_gci834311,00.html =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= MONITORING DAILY ARCHIVE LOG SWITCHES | Guillaume Henon Putting an Oracle database in archive log mode has a drawback: disk space usage. Moreover, the redolog switch is a costly action that causes additional disk and CPU overhead. The following script gives you in one shot the daily number of redolog switchse, archive log disk space needed, and average number of log switches per hour. >> CLICK for the full tip... http://www.searchDatabase.com/tip/1,289483,sid13_gci824587,00.html =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= HIDE A USER PASSWORD | "Database Person" Many times when running jobs through cron (on Unix boxes) it is required that you hide the password of the Oracle user from showing up when the ps command is run at the operating system level. There are various methods to do this, but here is the foolproof method. >> CLICK for the full tip... http://www.searchDatabase.com/tip/1,289483,sid13_gci834317,00.html =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= TIP FOR INSTALLING ORACLE 8I ON SOLARIS | Mohsin Jamil Qureshi Oracle 8i for Solaris is on two CDs, but the first CD does not eject unless you close the installer. Here is a quick work-around. >> CLICK for the full tip... http://www.searchDatabase.com/tip/1,289483,sid13_gci834314,00.html __________________________________________________________________ MORE RECENT TIPS FROM SEARCHDATABASE MEMBERS: =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= EXTRACTING FIELD INFORMATION FROM SQL SERVER | Kenneth Moser http://www.searchDatabase.com/tip/1,289483,sid13_gci831498,00.html Ever wonder how to generate a Word document containing all the tables, their fields, datatypes, length, nullable or not, and their primary keys? Here's how to do it in tabular form, automatically, with a single T-SQL command. SENDING MAIL FROM SQL SERVER USING ANY SMTP SERVER | Brian Ferguson http://www.searchDatabase.com/tip/1,289483,sid13_gci827377,00.html This set of stored procedures will 1) send e-mail to a comma delimited list of e-mails (7000 character maximum), 2) optionally carbon copy a comma delimited list of e-mails (7000 character maximum), and 3) provide support for a 7000 character body and 255 character subject using any SMTP server. AUTOMATICALLY DELETE ARCHIVE LOGS | Alex Ivascu http://www.searchDatabase.com/tip/1,289483,sid13_gci826569,00.html Here is how to automatically delete archive logs every so many days, for Oracle databases running in archivelog mode. This was tested on a Windows 2000 Server running Oracle 8.1.7.x. HOW TO QUERY USING SPECIAL CHARACTERS IN ORACLE | Amit Prashant http://www.searchDatabase.com/tip/1,289483,sid13_gci827190,00.html A short tip demonstrating how to query an Oracle database with special characters, using the percentage (%) symbol as an example. LIST THE KEPT STATUS FOR PL/SQL OBJECTS | Robert R. Blizard http://www.searchDatabase.com/tip/1,289483,sid13_gci826634,00.html This SQL*Plus script lets you list the KEPT status for PL/SQL objects (packages, procedures, functions, and triggers) in your database. You can show objects with a KEPT status of YES, NO, or both, and select the schema to show using standard Oracle wildcards. The script also lists the number of loads and executions for each object. AUTOMATICALLY DELETE ARCHIVE LOGS | Alex Ivascu http://www.searchDatabase.com/tip/1,289483,sid13_gci826569,00.html Here is how to automatically delete Oracle archive logs every so many days, for Oracle databases running in archivelog mode. This was tested on a Windows 2000 Server running Oracle 8.1.7.x. FIND THE TOTAL NUMBER OF OPENED CURSORS | Harish Kapgate http://www.searchDatabase.com/tip/1,289483,sid13_gci831494,00.html When developing complex Oracle applications, it is often neccessary to see the total number of opened cursors. With this simple method, you can find the total number of opened cursors for any particular user. 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 BOSTON ACOUSTICS SPEAKERS: http://searchdatabase.techtarget.com/tipsPrize/0,289492,sid13_prz831133_cts831128,00.html ___________________________________________________________________ IMPORTANT MESSAGES AND LINKS: =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= DEVELOPING VERY LARGE SQL SERVER DATABASES | Free Webcast TODAY, June 26th at 1 pm (EDT), Brian Goldstein, head of Microsoft's SQL Server Scalability Team, will be discussing the VLDB projects that they've done, including best practices and lessons learned. Join us for this free Webcast and take advantage of a rare opportunity to get your VLDB and SQL Server questions answered... >> CLICK to pre-register... http://searchdatabase.techtarget.com/onlineEvents/0,289675,sid13,00.html >> CLICK for 60+ previous Webcasts... http://searchdatabase.techtarget.com/onlineEventsTranscripts/0,289709,sid13,00.html =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= *********** June Training Special: SQL Data Management ************ Save 25% when you order the course "SQL Data Management" on CD-ROM. Learn SQL commands for manipulating data and commands to define tables, views and indexes. June special -- get this course for only $75. http://learn.serebra.com?s=33&I=FC480&m=db610nl&p=c&t=o =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= ASK THE EXPERT | This week's tough database questions answered SQL Server: Is there a way to shrink the transaction log? http://www.searchDatabase.com/ateQuestionNResponse/0,289625,sid13_cid477150_tax292506,00.html DB2: Do old tablespace configuration standards still apply? http://www.searchDatabase.com/ateQuestionNResponse/0,289625,sid13_cid477166_tax285651,00.html SQL: Inserting a row in a particular place http://www.searchDatabase.com/ateQuestionNResponse/0,289625,sid13_cid477128_tax289342,00.html DB2: TIMESTAMP is the only way to enter dates in DB2 http://www.searchDatabase.com/ateQuestionNResponse/0,289625,sid13_cid476808_tax285651,00.html Oracle: Installing Oracle software on home PC for training http://www.searchDatabase.com/ateQuestionNResponse/0,289625,sid13_cid477152_tax289342,00.html More expert answers here: http://searchdatabase.techtarget.com/ateExperts/0,289622,sid13,00.html =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= OBJECT DATABASES: PROS & CONS | SearchDatabase Featured Topic The ODBMS debate continues to rage. Are ODBMSs "regressive and fundamentally-flawed" or the wave of the XML-ified future? Can relational and object databases co-exist? Our experts weigh in with their analyses inside... >> CLICK for our current Featured Topic... http://searchdatabase.techtarget.com/featuredTopic/0,290042,sid13_gci834548,00.html >> CLICK for previous Featured Topics... http://searchdatabase.techtarget.com/featuredTopics/0,290043,sid13,00.html =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= DB2 TIPS NEEDED | SearchDatabase Tip of the Month Contest Attention DB2 DBAs and developers! Share your knowledge, help your peers -- submit a time-saving DB2 tech tip or script. The highest-rated tip in June will win a set of BOSTON ACOUSTICS COMPUTER SEAKERS, worth $120! >> CLICK for more info... http://searchdatabase.techtarget.com/tipsPrize/0,289492,sid13_prz831133_cts831128,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. ELLEN O'BRIEN, 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 'DBA 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."
