| ||
|
The Monthly Newsletter of the RevealNet Pipeline
Communities |
www.revealnet.com | June 2001 |
All articles have been reprinted with the written consent of their respective authors.
Oracle Database Re-Creation Script
By Emil R. Augustine
The purpose of this script is to reverse engineer the createdb.sql from a running database. It is useful when a database has to be recreated. (During database migrations using export/import or database reorganizations, for example.) The output of this script (with a few minor touch ups) would be the only script required to recreate the entire database minus the data, of course. Click Here for the script.
Win a Free Cap! Donate useful code to the Pipeline Library, and we will mail you a "stylin" canvas/embroidered RevealNet baseball cap if your utility is published in the newsletter. Just submit your code archive to cwhite@revealnet.com.
Managing Row-Level Security in Oracle 8i
By Michael R. Ault, TUSC
Author Michael R. Ault has joined TUSC in their new Atlanta office. Mike is the author of several Database Administration books for John Wiley & Sons publishing and the Coriolis Group. He is also a major content provider for RevealNet's Knowledge Base for Oracle Administration and a SYSOP for RevealNet's "DBA Pipeline". Mike's books can be purchased from the Illuminations Bookstore and wherever Oracle books are sold. Congratulations, Mike!
New in Oracle8i is the concept of row level access restriction. For years DBAs have requested some form of conditional grant where access to specific rows can be easily restricted or granted based on user or group membership. Oracle has finally given DBAs the functionality of conditional grants in the form of row level security. The DBMS_RLS package is only available with the Enterprise edition of Oracle8i. Click Here for the paper.
Free Book Offer
 | Get
the "Oracle DBA Checklists" Pocket reference FREE! Here's
how:
|
This concise pocket reference, authored by RevealNet and published by O'Reilly & Associates (76 pages) contains a series of easy-to-use checklists providing helpful reminders of the procedures Oracle DBAs must follow when performing common database administration tasks. In this book, you will find checklists for database management, installation and configuration and network management responsibilities.
Implementing STAR Joins
Sample topic from the Knowledge Base for
DB2 v2001.1
A star schema is sometimes used in decision support applications. It consists of a fact table and multiple dimension tables. The schema in the example has three dimension tables tables containing information on products, time, and location. The sales fact table must have an ID column for each dimension. In our example, the column names are S.PRODUCT, S.LOCATION, and S.TIME. The product dimension table can have information on city, state, country, etc. The time dimension table can have information on the month, quarter, year, etc. Click Here for the topic.
Lee Hayden & Robert Jans Join the DB2 Pipeline as SYSOPs
RevealNet is pleased to announce Lee Hayden and Robert Jans as the first official SYSOPs for the DB2 Pipeline - a free community for DB2 professionals worldwide. Lee has over 20 years of experience on IBM mainframes and other platforms. He has worked in various capacities from COBOL programmer to Application Project Manager to Technical Support Manager to Database Specialist. While Lee's experience covers many aspects of DB2 tuning and maintenance, he is particularly well versed in migration issues, third party tools, and multiple programming languages. He is also a moderator for mvshelp.com, a technical resource site for mainframe programmers. When not working with DB2, Lee collects and plays guitars, edits a newsletter for his local Mercedes-Benz club, and enjoys traveling with his wife Lynn, who is also a computing professional.
Robert has been in IT for about 13 years. He spent his early years as a programmer and contract programmer before becoming a DBA. Most of his programming has been various flavors of COBOL with some CICS, C, LISP and Powerbuilder thrown in. He became a DB2 DBA in December of 1995 starting on a small system using IBM's Image Plus imaging software. While there, he also supported a reporting warehouse on DB2 for AIX V2.1. He is also a member of the DBA team that recently won the DB2 Innovation award at IDUG 2001 in Orlando. When not working with DB2, Robert is an avid motorcyclist, spending as much time on his bike as possible. He also enjoys repairing cars, camping, and spending time with his family.
DB2 Pipeline SYSOPs Wanted
RevealNet is actively looking for 3-4 more SYSOPs to moderate the DB2 Pipeline. We are especially looking for professionals with UDB experience for OS/390, Unix and NT platforms. If you are an experienced DB2 professional, enjoy the challenge of volunteering and answering questions, please contact Michael Gilsbach at RevealNet (mgilsbach@revealnet.com). If you qualify, you will be given complete management access to the DB2 Pipeline, allowing you to easily moderate message traffic. Perks include free personal use of RevealNet's DB2 Knowledge Base and stylin' Pipeline SYSOP apparel. Our goal is to make the DB2 Pipeline the best support forum in the world for DB2 professionals.
| PL/SQL Best Practices: Solve
Problems Simply By Thomas Kyte,
Oracle Corporation, Oracle Magazine's "Ask Tom" column |
|
RevealNet is publishing a summer series on Oracle PL/SQL Best Practices. This month's feature article comes from Tom Kyte, Oracle Corporation. Tom has been a very active contributor to the Oracle community since 1993. His new book "Expert One-On-One Oracle" is due in bookstores this month. Tom also hosts the regular "Ask Tom" column for Oracle Magazine. Tom works with the Oracle Services Technology Group in Reston, Virginia.
There are always ways two ways to solve everything: the easy way and the hard way. Time and time again, I see people choosing the hard way. It is not always done consciously. More usually, it is done out of ignorance. They never expected the database to be able to do 'that'. I, on the other hand, expect the database to be capable of anything and only do it the 'hard' way (by writing it myself) when I discover it cannot do something. Click Here for the excerpt from Tom's new book.
Web
Applications and PL/SQL
By Dan Clamage, Clamage Computer
Consulting, RevealNet Pipeline SYSOP
Here at MSA (Management Science Associates), we're developing an internal web application using Cold Fusion and PL/SQL. Our design goals are to:
- Security -- The interactive user (the user logging into the web application) only has privileges to execute certain packages -- no other schema-level privileges are provided. The interactive user is not the schema owner of the tables being operated upon.
- Move All SQL into the database -- No SQL statements are allowed in the Cold Fusion application. Instead, packaged procedures encapsulate all database operations.
- Combine DML operations -- The web front-end collects a set of data for processing, and then submits it one time to a packaged procedure. This minimizes network roundtrips. We also want to make use of bulk DML operations by passing arrays of records to a PL/SQL packaged routine. Click Here for the paper.
Visit RevealNet at ODTUG in San Diego June 25-27!
Manual Replication Cleanup
By Michael R. Hotek, mssqlserver.com
Starting this month, the Pipeline Newsletter will include a feature article and monthly tip for Microsoft SQL Server professionals. Please contact Cam White (cwhite@revealnet.com) if you would like to submit articles or tips. Remember, if we decide to publish your submission, we'll send you a stylin' embroidered RevealNet cap!
One of the more common problems you will have to encounter at some point is when you disable replication, not all of the settings are cleaned up. This can cause problems if you want to drop a database or alter table structures.
As you might have figured out, the reason this tip even exists is that the wizards to disable replication are not perfect. The good news is that there isn't anything magical about replication. Replication is run entirely via a set of applications that are external to SQL Server. These applications are executed by the SQL Server Agent, Windows Synchronization Manager, or directly via some other means. The same functionality is performed when using the replication ActiveX controls. When executed, the replication agents or the ActiveX controls connect to the appropriate SQL Server and utilize data stored there to determine what to do. All you have to do is eliminate all of that data and turn off any associated settings. Click Here for the article.
"Tortured" English Seen in Businesses Around the World
This is a list of signs actually found in businesses around the world. Prepare yourself for a good laugh! Click Here to start the fun!
Spring 2001 Version of All RevealNet Knowledge Bases Now Available
The Spring 2001 updates of all RevealNet Knowledge Bases are now available for downloading. Hundreds of new topics have been added to our expert Knowledge Bases for Oracle and DB2 professionals. Download the latest 30-day trial versions and start solving problems and implementing solutions faster than ever before! Click on each product below for the latest information:
- Knowledge Base for Oracle Administration v2001.1
- Knowledge Base for Active PL/SQL Development v2001.1
- Formatter Plus v1.0
- Knowledge Base for DB2 v2001.1
- Developer's Toolkit
- Corporate Knowledge Pack
We would like to hear what you think about our products. Please take a moment to fill out a quick survey about our Knowledge Bases for Oracle Administration, PL/SQL Development and DB2. Click on one of the links below for the survey.
Oracle
Products Survey (Knowledge Bases for Oracle Administration and PL/SQL
Development)
DB2 Product Survey (Knowledge Base for
DB2)
TOAD
V7 Integrates RevealNet Formatting Products
Because everyone has different database editing and administration needs, Quest Software has created several TOAD configurations for you to choose from. Depending on which option you license, you will be entitled to RevealNet's PL/Formatter or Formatter Plus products. Click Here to see the new TOAD Configurations. Contact your Quest Software Representative for licensing and upgrade details.
DLT
Solutions Adds Formatter Plus, other RevealNet Products to the GSA Schedule
If you are a U.S. Government, State, or Municipal agency or Educational Institution, you qualify for GSA schedule pricing from DLT Solutions -- Oracle's largest value-added government reseller. GSA schedule pricing is the lowest price our products can legally be sold for. Several new product offerings, including Formatter Plus, the Developer's Toolkit, and the Corporate Knowledge Pack have been recently added. Multi-user, tiered discounts have also been introduced. If you qualify for GSA pricing, contact Cindy Clarkson (cindy.clarkson@dlt.com) at 703-708-9623 for a quotation.
Did you know RevealNet Knowledge Bases and Development Tools are tightly integrated with products from these companies? Click on each logo below for more information.
|
Software Developers |
Reseller Partners | ||||||
 |
 |
 |
 |
|
 |
 |
|
 |
Oracle DBA Tip of the Month: | Choosing the Best Size for Net8 Data Blocks |
|
PL/SQL Tip of the Month: | Use of the New NVL2 Function |
|
DB2 Tip of the Month: | Using the CASE Statement to Produce a Summary Report |
|
SQL Server Tip of the Month: | Determining if a Column or Table Already Exists |
"Time to Slay Another Myth"
- Oracle DBA Pipeline
Conference: Oracle 8i
Pipeline SYSOP Mike Ault debunks conventional assumptions about statistical sampling techniques. Pipeliners share their thoughts. Click Here for the discussion. Spreadsheet included.
RevealNet's Pipeline Communities are rich in technical discussions. Each month, we highlight particular discussions of interest. Log on to "Pipe Talk" and share your ideas with your colleagues around the world.
RevealNet Pipeline SYSOP Team Formed
Since 1996, the RevealNet Pipelines have evolved into one of the largest and most heavily trafficked websites for Oracle technical issues, rivaling MetaLink and Usenet daily message volume.
The Pipetalk conferences are now moderated by a team of SYSOPS, including Michael R. Ault, Steven Feuerstein, Solomon Yakobson, Steve Cosner, Robert Freeman, Andrew Simkovsky, Daniel Clamage, James Padfield, Wolfgang Breitling and others. This team represents some of the most active and knowledgeable members of the Pipeline community. SYSOPs monitor daily message traffic, answer questions, and remove inappropriate posts and duplicate messages from the Pipeline conferences.
Win
a FREE CAP!
We love getting white papers, tips, articles, and code examples/archives from our readers around the world. Send your submission to cwhite@revealnet.com. If your article is published, we will mail you a "stylin'" canvas/embroidered RevealNet baseball cap as a gesture or our appreciation.
Our mission at RevealNet is to anticipate the daily responsibilities and challenges faced by database professionals. Our products help thousands of people solve problems and implement solutions every day. This newsletter is designed to help facilitate the sharing of information among database professionals. It is currently circulated to 17,000 readers.
Random Notes:
Stop by and see us at ODTUG June 25-27 in sunny San Diego. If you are active in Oracle development, this is the conference for you. The organizers of this conference put together an excellent experience for attendees, with a packed agenda of technical sessions followed by great nightlife. Get the scoop at www.odtug.com.
We would also like to welcome Warren Capps to our team of SYSOPs for our Oracle pipeline communities. Warren runs the Illuminations Bookstore, and is a very active volunteer and speaker at Oracle conferences and User Group meetings. Make sure to visit Illuminations for your Oracle book needs.
RevealNet Contact Info
RevealNet, Inc.
P.O. Box 5560
Rockville, MD 20855
Tel: 800-738-3254 Toll-Free US
Tel: 301-947-7744 International
Fax: 301-947-9610
Email: info@revealnet.com
RevealNet develops Knowledge Bases and Development Tools for Oracle and DB2 professionals. Our Knowledge Bases are designed to anticipate the daily challenges of DBAs and Developers - providing the background information, syntax and examples you need to apply solutions immediately. Our Oracle development tools help you write far less code, improve code maintainability and achieve development excellence.
About the Newsletter
This newsletter is distributed to RevealNet customers, prospects and friends who have subscribed to it from our website. If you would like to unsubscribe, please send a note to info@revealnet.com and we will make every effort to remove you from our list.
Subscribe a Friend!
Do you know someone who would like to receive the RevealNet Pipeline Newsletter? If so, please enter their email address in the box below and click SUBMIT.
Past Issues of the Pipeline Newsletter
|
|
|