SEARCHDATABASE.COM | Database Administrator Tips August 13, 2003 =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= FROM OUR SPONSOR:
Attention Oracle DBAs - Free Data Dictionary http://searchDatabase.com/r/0,,17007,00.htm?questsoftware =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= IN THIS ISSUE | Table of Contents 1. DATABASE ADMINISTRATOR TIPS - SQL Server: Data migration script for static tables - SQL Server: Use the firehose cursor - Oracle: Reorganize a table and release the unused space - More recent tips from members 2. IMPORTANT ANNOUNCEMENTS AND LINKS - Ask the experts: Your database questions answered - Featured Topic: Data management career tips ___________________________________________________________________ ******************* Sponsored by Quest Software ******************* Is your Oracle database running at full speed? Can you identify bottlenecks and code that needs tuning? Start down the path to Oracle database health today with a free, online Oracle data dictionary compliments of Quest Software. Download your free data dictionary now! http://searchDatabase.com/r/0,,17007,00.htm?questsoftware ******************************************************************* ___________________________________________________________________ THIS WEEK'S TIPS: =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= DATA MIGRATION SCRIPT FOR STATIC TABLES | Rajasekhar Sahitala Suppose that you have an application in which you have a table containing static data and you need this data whenever you recreate the database. Using this SQL Server script, you can create a batch of insert statements that you can keep in your database creation script and start using the database after the database creation without reloading any data. >> CLICK for the full tip... http://www.searchDatabase.com/tip/1,289483,sid13_gci915924,00.html =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= USE THE FIREHOSE CURSOR | Barrie Sosinsky SQL Server supports several types of cursors but one read-only, non-scrollable cursor that is specially optimized for fast performance and worthy of note is the firehose cursor. Here's a short introduction. >> CLICK for the full tip... http://www.searchDatabase.com/tip/1,289483,sid13_gci911716,00.html =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= A SIMPLE WAY TO REORGANIZE A TABLE AND RELEASE THE UNUSED SPACE This tip from member Debajit Mishra describes a simple solution to better manage the table space and performance during ETL operations in Oracle 9i. >> CLICK for the full tip... http://www.searchOracle.com/tip/1,289483,sid41_gci918141,00.html >> NOTE: All Oracle-related tech tips have moved! Click here: http://www.SearchOracle.com/tips ___________________________________________________________________ MORE RECENT TIPS FROM SEARCHDATABASE MEMBERS: RESEEDING IDENTITIES IN SQL SERVER | Baya Pavliashvili http://www.searchDatabase.com/tip/1,289483,sid13_gci913732,00.html Reseeding identities in IDENTITY columns are commonly used as primary keys for SQL Server tables. Here are some tips about reseeding identities and some pitfalls to avoid. JDBC: DATABASE PROGRAMMING WITH J2EE | Art Taylor http://www.searchDatabase.com/tip/1,289483,sid13_gci917202,00.html As a registered member of SearchDatabase.com, you're entitled to a complimentary copy of Chapter 1 from "JDBC: Database programming with J2EE" written by Art Taylor and published by Prentice Hall PTR. This chapter focuses on JDBC design, how Java works with relational databases, the JDBC API and more. HOW TO USE RESUMABLE SPACE ALLOCATION IN ORACLE 9I | Sanjay Gupta http://www.searchOracle.com/tip/1,289483,sid41_gci915006,00.html This tip discuss how to use a new feature in Oracle 9i called Resuamable Space Allocation. Before 9i, it was not possible to suspend a session when some space-related problem occured, so as a result, whole transactions got rolled back and you had to start your work all over again. GENERATING A DATABASE POPULATION ROUTINE | Baya Pavliashvili http://www.searchDatabase.com/tip/1,289483,sid13_gci913717,00.html Here is a SQL Server script that will populate your data model with data and stress test it prior to making it available to users. CHECK ALL ACTIVE PROCESSES, THE LATEST SQL, AND THE SQL HIT RATIO http://www.searchOracle.com/tip/1,289483,sid41_gci913363,00.html This quick script from member Rajesh George is useful for Oracle DBAs in the process of tuning and identifying slow SQL. It will provide information about the currently active sessions, the latest SQL, as well as the hit ratio. CREATING SERIAL NUMBER COLUMNS | Barrie Sosinsky http://www.searchDatabase.com/tip/1,289483,sid13_gci914811,00.html Auto-incrementing columns are important when you need serial numbers or key fields for joins. In DB2 for versions 7 and later you can add the IDENTITY clause to the CREATE TABLE statement to create such a column. Here's how. SPLIT DELIMITER-SEPARATED LISTS | Andrew Novick http://www.searchDatabase.com/tip/1,289483,sid13_gci911718,00.html Creating and parsing lists of items in a string is a frequent request. Here's a SQL Server UDF that shows you how to do it. 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 prize: http://searchdatabase.techtarget.com/tipsSubmit/1,289485,sid13,00.html ___________________________________________________________________ IMPORTANT LINKS: =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= YOUR TOUGH DATABASE QUESTIONS ANSWERED | Ask the Expert Oracle: Range or hash for partitions? http://www.searchOracle.com/ateQuestionNResponse/0,289625,sid41_cid545005_tax294551,00.html Oracle: Replicating 10,000 records daily to SQL Server http://www.searchOracle.com/ateQuestionNResponse/0,289625,sid41_cid545008_tax294551,00.html PL/SQL: Counts and percentages http://www.searchOracle.com/ateQuestionNResponse/0,289625,sid41_cid544378_tax294651,00.html DB2: Formatting TIMESTAMP data http://www.searchDatabase.com/ateQuestionNResponse/0,289625,sid13_cid544229_tax285651,00.html Data warehousing: Error 1067 when launching DB2 OLAP Server http://www.searchDatabase.com/ateQuestionNResponse/0,289625,sid13_cid542914_tax288372,00.html DB2: Knowing what columns/rows were modified http://www.searchDatabase.com/ateQuestionNResponse/0,289625,sid13_cid542942_tax285651,00.html SQL Server: Error with backup from Enterprise Manager http://www.searchDatabase.com/ateQuestionNResponse/0,289625,sid13_cid542632_tax285648,00.html DB2: Tuning update query with subselect http://www.searchDatabase.com/ateQuestionNResponse/0,289625,sid13_cid542946_tax285651,00.html More database expert answers here: http://searchdatabase.techtarget.com/ateExperts/0,289622,sid13,00.html Oracle database and application suite expert answers here: http://searchoracle.techtarget.com/ateExperts/0,289622,sid41,00.html =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= DATA MANAGEMENT CAREER TIPS | SearchDatabase.com Featured Topic Even in this dour economy, database administrators and developers continue to be in high demand -- and continue to be highly paid. The collection of tips and advice below will guide you along the path to becoming a database guru. >> CLICK for our current Featured Topic... http://searchdatabase.techtarget.com/featuredTopic/0,290042,sid13_gci881884,00.html >> CLICK for previous Featured Topics... http://searchdatabase.techtarget.com/featuredTopics/0,290043,sid13,00.html =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= ___________________________________________________________________ *********************SEARCHDATABASE CONTACTS*********************** TIM DICHIARA, Senior Site Editor (mailto:[EMAIL PROTECTED]) ROBYN LORUSSO, Site Editor (mailto:[EMAIL PROTECTED]) SARA CUSHMAN, Assistant Editor (mailto:[EMAIL PROTECTED]) ELLEN O'BRIEN, News Editor (mailto:[EMAIL PROTECTED]) ROB WESTERVELT, News Writer (mailto:[EMAIL PROTECTED]) TOM CLICK, Sales (mailto:[EMAIL PROTECTED]) CLARK MORLEY, Sales (mailto:[EMAIL PROTECTED]) ___________________________________________________________________ **********************ABOUT THIS NEWSLETTER************************ Created by TechTarget (http://www.techtarget.com) TechTarget - The Most Targeted IT Media Copyright 2003, All Rights Reserved. ___________________________________________________________________ ::::::::::::::::::::: ABOUT THIS NEWSLETTER :::::::::::::::::::::: This newsletter is published by TechTarget, the most targeted IT media. http://www.techtarget.com Copyright 2003 TechTarget. 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."
