=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= SEARCHDATABASE.COM | Database Developer Tips February 27, 2002
Essential tools and advice for the enterprise database developer: http://searchDatabase.techtarget.com/tips/ =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= IN THIS ISSUE | Table of Contents 1. DATABASE DEVELOPER TIPS - Oracle: Finding ASCII representations of integers via SQL - SQL Server: A stored procedure to export/import data using BCP - Oracle: SQL*PLUS file finder for Windows - Oracle: What SQL is running and who is running it? - More recent tips from members 2. IMPORTANT ANNOUNCEMENTS AND LINKS - Ask the Expert: Oracle, SQL Server, DB2 questions answered - Online Event: Don Burleson on Oracle 9i tuning - Featured Topic: Tuning Oracle ___________________________________________________________________ ********************** Sponsored by: Sybase *********************** Learn More About the Market Leading Embedded Database There's an art to hiding genius in plain sight. And we've hidden a lot. SQL Anywhere(R) Studio 8.0 is self-tuning, self-administering and always ready to serve. For a free download or evaluation CD click here. http://ad.doubleclick.net/clk;3830722;6845045;i?http://www.ianywhere.com/hiddenonline ___________________________________________________________________ ___________________________________________________________________ THIS WEEK'S TIPS: =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= FINDING ASCII REPRESENTATIONS OF INTEGERS VIA SQL | by Karen Porter You may sometimes want to include a carriage return or other character into an SQL statement. Single tics are especially confusing due to breaking them on and off to be effective. This tip shows you how to use the CHR() function to write single tics into SQL. If you forget what integer is required in the CHR() function, you can use the script to determine what integer you should use. >> CLICK for the full tip... http://www.searchDatabase.com/tip/1,289483,sid13_gci804491,00.html =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= A STORED PROCEDURE TO EXPORT/IMPORT DATA USING BCP | by Eli Leiba Here is a SQL Server stored procedure that can be run from a master database to export or import a table to/from a given file path+name from/to a given database. >> CLICK for the full tip... http://www.searchDatabase.com/tip/1,289483,sid13_gci804489,00.html =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= SQL*PLUS FILE FINDER FOR WINDOWS | by Dusan Djuric You are in an Oracle SQL*Plus session in Windows. Somewhere on your machine is the one script you need, but where? The script name is something like my*query.sql, but you're not sure. What do you do? Here's a handy way to find it fast. >> CLICK for the full tip... http://www.searchDatabase.com/tip/1,289483,sid13_gci799189,00.html =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= WHAT SQL IS RUNNING AND WHO IS RUNNING IT? | by Rohit Sinha Many times you may need to find what SQL is currently running and which user is running it. The SQL in this tip runs on Oracle versions 7 and 8.1.7 and gives the SID, Serial#, SQL text, and the Oracle and OS user running it. The SID and serial number information may be used to kill the particular session if required. Rohit has the script saved as whatdousers.sql and just types @whatdousers at the SQL prompt. >> CLICK for the full tip... http://www.searchDatabase.com/tip/1,289483,sid13_gci798210,00.html __________________________________________________________________ MORE RECENT TIPS FROM SEARCHDATABASE MEMBERS: =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= ESTIMATE TABLE SIZE USING COMMAND EXTENSIONS | by Johnny Agsalog http://www.searchDatabase.com/tip/1,289483,sid13_gci803193,00.html This SQL Server batch file demonstrates the power of both OSQL and Windows command extensions. It is used to calculate the size of user tables -- very useful in big databases. QUERY THE PROCESS ID AND SQLTEXT AREA | by Geoff Heaton http://www.searchDatabase.com/tip/1,289483,sid13_gci801865,00.html Here's how to view the details and I/O stats per session in Oracle. You can then use one of the SIDs to query any SQL held in the SQLAREA. The output is spooled to a file. CONVERTING AN ADJACENCY LIST MODEL TO A NESTED SET | by Joe Celko http://www.searchDatabase.com/tip/1,289483,sid13_gci801943,00.html One way of representing trees in SQL is to show them as nested sets. Since SQL is a set-oriented language, this is a better model than the usual adjacency list approach you see in most text books. Here is a detailed article, with sample code, about converting between the two models. ORACLE ALERT LOG MINER | by Bill Robillard http://www.searchDatabase.com/tip/1,289483,sid13_gci801900,00.html This is an extensive C program that extracts lines from the Oracle alert log for a specified date (or date range). The lines extracted depend on the entries in a 'pattern' file. Included is the syntax and an example of a pattern file and a sample command. Now also available in Java! 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 COMPAQ DISK-ON-KEY! http://searchdatabase.techtarget.com/tipsSubmit/1,289485,sid13,00.html ___________________________________________________________________ ***********QUALIFY TO ATTEND OUR FREE WINDOWS CONFERENCE ********** Don't miss our Windows Decisions conference May 8-10 at the Hilton Chicago Hotel. Attend and discover: ** How to succeed with Active Directory ** Hidden tactics to lower your TCO ** Best practices for Windows 2000 systems management ** End-to-end network administration strategies ** How to solve your top 10 Windows interoperability problems ** Much more... View full session info and apply today for FREE attendance at http://ad.doubleclick.net/clk;3903337;5058249;m?http://www.windowsdecisions.com/ ___________________________________________________________________ ___________________________________________________________________ IMPORTANT MESSAGES AND LINKS: =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= ASK THE EXPERT | This week's toughest database questions answered Oracle: Cursors: %type and %rowtype explained http://www.searchDatabase.com/ateQuestionNResponse/0,289625,sid13_cid449533_tax285650,00.html Oracle: SQL Plus won't run trigger http://www.searchDatabase.com/ateQuestionNResponse/0,289625,sid13_cid449872_tax285650,00.html DB2: DB2 390 optimizer and bind variables http://www.searchDatabase.com/ateQuestionNResponse/0,289625,sid13_cid449477_tax285651,00.html SQL: Returning number of rows to a REF CURSOR http://www.searchDatabase.com/ateQuestionNResponse/0,289625,sid13_cid449514_tax285649,00.html SQL: Selecting and comparing data from two different databases http://www.searchDatabase.com/ateQuestionNResponse/0,289625,sid13_cid449487_tax285649,00.html Hundreds more expert answers here: http://searchdatabase.techtarget.com/ateExperts/0,289622,sid13,00.html =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= LIVE EXPERT Q&A | Self-tuning in Oracle 9i Who: Don Burleson When: February 27, 2002 at 12:30 PM EST (17:30 GMT) TOMORROW! Join renowned Oracle guru Don Burleson, author of "Oracle High Performance SQL Tuning," in this discussion about tuning Oracle 9i for maximum performance. Learn about I/O tuning and RAM tuning and ask Don your toughest questions! >> CLICK: http://searchdatabase.techtarget.com/onlineEvents/0,289675,sid13,00.html =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= TUNING ORACLE | SearchDatabase Featured Topic A slow database is not only annoying to users, it can also affect your company's bottom line. This collection of Oracle tuning tips and advice will help you achieve maximal speed and reliability for your Oracle system. >> CLICK for our current Featured Topic... http://searchdatabase.techtarget.com/featuredTopic/0,290042,sid13_gci803739,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. If you no longer wish to receive this newsletter simply reply to this message with "REMOVE" in the subject line. Or, visit http://searchDatabase.techtarget.com/register and adjust your subscriptions accordingly. If you choose to unsubscribe using our automated processing, you must send the "REMOVE" request from the email account to which this newsletter was delivered. Please allow 24 hours for your "REMOVE" request to be processed.
