Create Many Tables, Constraints and Data (April 2003)
By Daniel Norwood

This function allows users to create 4,000 tables, 32,000 foreign key constraints, and 16 million rows of data for testing purposes.

Click Here to view the script.

Killing the Oracle DBMS_JOB (April 2003)
By James F. Koopmann

Take control of Oracle's queue with a step by step approach to getting rid of those pesky DBMS_JOBs.

Let's face it, Oracle's job scheduling facility is a wonderful tool for scheduling Oracle related jobs without having to maintain a cron job on Unix or an AT job in windows. It is also very robust and reliable. It is that very reliability and robustness that gives many of us our problems.

If you have any form of jobs running on your system, you will at one time or another come across the issue of a run-away job that just doesn't seem to want to end. Or maybe you will try and shutdown the database only to find out that it is waiting to complete a job. I would like to offer some help in the management of those job queues when they just don't seem to want to end or go away.

Click Here to view the article.

Create DDL for all Profiles in a Database (May 2003)
By Anand Subramaniam

This utility is a script to generate DDL for all profiles in a database as well as their underlying functions. This script uses the UTL_FILE feature of PL/SQL. To run this script, users should have the "UTL_FILE_DIR" parameter configured in the init.ora file.

Click Here to view the script.
Click Here to view the output script.

Offensive Runaways - Defensive DBAs - II (May 2003)
By Shankar Govindan

In my first paper, I discussed what prompted me to write a script to track long running programs that were consuming CPU in an Oracle Application environment. In this paper we will look at what happens when a session process becomes a runaway in a Data warehouse environment and wrecks your parallel queries.

Click Here to view the paper.

More Examples of Interpreting Wait Events to Boost System Performance
Part 1: What are Wait Events? (May 2003)
By Roger Schrag & Terry Sutton

In early 2002 Roger Schrag published a paper entitled, "Interpreting Wait Events to Boost System Performance." That paper began with a definition of wait events and techniques for collecting wait event information, and then went on to discuss four examples of how wait event data had been used to solve real-life performance problems. The impetus for writing that paper had been that, while many resources were available that listed the wait event v$ views, we were not aware of any publication that actually explained how to use the wait event interface to solve problems or boost system performance.

As we write this paper in early 2003, we still are not aware of any such publications. Now Roger Schrag has teamed up with fellow Oracle DBA Terry Sutton to document more examples of how Oracle's wait event interface can be used to solve real-world problems. In essence, this paper is a sequel to last year's paper. If you haven't read the first one, you may view it at http://www.dbspecialists.com/presentations.html. You can read the two papers in either order.

In part one of this paper, we will define what wait events are. If you have read the previous paper or are already familiar with the wait event interface, we still encourage you to read this section as we've added some enhancements and new details regarding Oracle 9i.

Click Here to view the paper.

Displaying Tablespace Usage with a Graph (June 2003)
By Andrew Simkovsky

The ts_used.sql displays the total space and allocated space for all of the tablespaces in a database. The output includes a bar graph representing the percentage full for each tablespace, and also displays which tablespaces are offline.

Click Here to view the script.

More Examples of Interpreting Wait Events to Boost System Performance
Part 2: Collecting Wait Event Information (June 2003)
By Roger Schrag & Terry Sutton

In part one of this paper, (found in the May 2003 issue), we defined wait events. In part two of this paper, we will explain how to collect wait event information. If you have read the previous paper or are already familiar with the wait event interface, we still encourage you to read this section as we've added some enhancements and new details regarding Oracle 9i.

Click Here to view the paper.

Sharing Oracle 9iAS Experiences!!! (June 2003)
By Pradeep Navalkar

This paper aims to provide a background of our systems, and share some of our experiences with the Oracle 9iAS product. Note that this paper is not intended to fully describe the Oracle 9iAS product.

Click Here to view the paper.

More Examples of Interpreting Wait Events to Boost System Performance
Part 3: How Wait Event Information Can be Used to Boost System Performance (July 2003)
By Roger Schrag & Terry Sutton

In part one of this paper, (found in the May 2003 issue), we defined wait events. In part two of this paper (found in the June 2003 issue), we explained how to collect wait event information. If you have read the previous paper or are already familiar with the wait event interface, we still encourage you to read this section as we've added some enhancements and new details regarding Oracle 9i. In part three of this paper we'll move on to new examples of how wait event information paved the way to solving real-world performance problems. Our feeling is that the more examples of wait event usage you are exposed to, the better position you'll be in to put the wait event interface to work for you when you most need it.

Click Here to view the paper.

Encryption and DBMS_OBFUSCATION_TOOLKIT (July 2003)
By Bob Mycroft

There are times when data in your system is highly sensitive and it is required that the data is encrypted. Oracle supplies a package called DBMS_OBFUSCATION_TOOLKIT, which allows us to encrypt and decrypt data by applying one of two encryption algorithms and an encryption key. This article should be of interest to both the DBA and developer, especially when designing a new schema where some data is seen to be particularly sensitive.

Click Here to view the paper.

Create a Data Mart or Data Warehouse Time Dimension (August 2003)
By Steven Hauser

A datamart or data warehouse will usually need a time dimension to use for date queries that look at periods, weeks, etc. Here is an example of how to generate one from the basic date entry and the Oracle date functions. The same idea can be used in DB2, Informix, Postgresql, etc.

Click Here to view the script.

So You Want to Use Oracle's SPFILE (August 2003)
By James F. Koopmann

Oracle's traditional method of storing parameters in the INIT.ORA file has given way to a more robust method - the server parameter file (SPFILE). Learn what truly works in converting and maintaining your parameters without ever having to use an editor again.

Click Here to view the article.

Script to Query on Current Transactions and Table Locks (September 2003)
By Jeff Hunter

This script will query on current transactions and all table locks. The script is split into two parts: (1) A summary of all table locks and (2) the transactions (along with I/O stats) that are happening.

Click Here to view the script.

Microstate Response-time Performance Profiling, Part 1 (September 2003)
By Danisment Gazi Unal

Getting optimum performance for mission-critical Oracle systems is an extremely complex task. For senior Oracle DBAs, measuring internal services waits within an Oracle database is a critical aspect of advanced response-time profiling. The accuracy of measurements is an important factor in all types of research. However, there are no 100-percent accurate measurements in Nature; there are always some distortions in the measurements. How can a method be accepted as an accurate method? The answer depends on the impact of measurement errors.

The goal of this article is to adapt microstate accounting, which is a more accurate measurement technique provided by OS (operating system) vendors, to database management systems (DBMSs). Part 1 of the article outlines the current measurement techniques available and the errors that can occur with them. Part 2 will discuss how to correct these errors and introduces the theoretical approach of Microstate Response-time Performance Profiling.

Click Here to view the paper.

End Backup Script (October 2003)
From Think-Forward.com

IF a hot backup script fails for some reason, the tablespaces will still be in hot backup mode until an ALTER TABLESACE END BACKUP command is issued.

This script generates and runs an END BACKUP script based upon those database which are currently flagged as ACTIVE in the v$backup table.

Click Here to view the script.

Helper Tables (October 2003)
By Kevin Meade

Helper tables remind me of Calculus class. Remember Calculus (I applaud anyone who truly understood this stuff cause I just didn't get it). But I do remember one thing vividly that I did get and that I use all the time these days (even though it applied to a different venue). I remember those Calculus problems that were either very difficult to solve or not solvable at all. Then I remember the idea of transformation where in, the professor did something seemingly useless that in turn changed the whole problem into something way simple. For example, he would add 0 or divide by 1 (maybe both), neither operation of which could possibly change the answer (hey the identity matrix). But he would do it in two steps in order to introduce two canceling but very real and opposing values to different parts of an equation. In the end he was just adding "non-relevant" information to the process. But doing so let him play with the pieces of the puzzle in order to solve his problem.

Using helper tables in SQL is like adding 0 or dividing by 1 in math. The table in and of itself looks pretty useless (some might even say stupid). Using one won't change what you want to do, but it will change the way you can do it, by giving you access to data you didn't have readily available at first. Let us take an example in point.

Click Here to view the article.

Microstate Response-time Performance Profiling, Part 2 (November 2003)
By Danisment Gazi Unal

Getting optimum performance for mission-critical Oracle systems is an extremely complex task. For senior Oracle DBAs, measuring internal services waits within an Oracle database is a critical aspect of advanced response-time profiling. The accuracy of measurements is an important factor in all types of research. However, there are no 100-percent accurate measurements in Nature; there are always some distortions in the measurements. How can a method be accepted as an accurate method? The answer depends on the impact of measurement errors.

The goal of this article is to adapt microstate accounting, which is a more accurate measurement technique provided by OS (operating system) vendors, to database management systems (DBMSs). Part 1 of the article outlined the current measurement techniques available and the errors that can occur with them. Part 2 discusses how to correct these errors and introduces the theoretical approach of Microstate Response-time Performance Profiling.

Click Here to view the paper.

Script to Dump Contents of a BLOB Column (December 2003)
By Jeff Hunter

This script can be used to dump the contents of a BLOB column. The user will be prompted for the OWNER, TABLE_NAME, and COLUMN_NAME for the BLOB column to read from. The anonymous PL/SQL block will write the contents of the BLOB to a file named using the format:

OWNER_TABLE_COLUMN_<counter>.out

Click Here to view the script.

Tuning Database Buffer Cache (December 2003)
By Vikash Varma

Buffer pool or buffer cache is a memory structure within Oracle SGA that is used for caching data blocks in the memory. Accessing data from the memory is significantly faster than accessing data from disk. The goal of block buffer tuning is to efficiently cache frequently used data blocks in the buffer cache (SGA) and provide faster access to data.

Find out how to tune oracle database buffer cache beyond buffer hit ratio. This article presents a step-by-step guide for tuning and efficiently utilizing database block buffer.

Click Here to view the article.

Script to Show SGA Pools (January 2004)
By Philippe de Saint Aignan

This script shows the different SGA POOLS, then sums up the UGAs & PGA. The percent of the shared_pool represents the percent of the allocations / total shared pool memory. For UGA & PGA pools, the latter column tries to evaluate the UGA & PGA sizes with the largest number of sessions on.

Click Here to view the script.

Optimizing Oracle 10g on Linux using Automated Storage Management (January 2004)
By Bert Scalzo

I often react to new Oracle releases like Steve Martin acted in the movie "The Jerk" when the new phone books arrive. I cannot help but to get excited about all the new technology that Oracle routinely delivers. But with the release of Oracle 10g, that enthusiasm cannot be overstated. And while this Oracle version delivers numerous nifty new features, we’ll examine just one, Automated Storage Management (ASM), which makes Linux database disk space management a snap.

Click Here to view the article.

9i RAC: Manual Backup and Recovery (January 2004)
By Shankar Govindan

When we plan to move a large database or a heavily used OLTP database to a cluster setup, to get enough mileage from the horizontal scaling of 9i RAC, we normally have lots of questions about how we are going to handle the various database maintenance issues that are traditionally done and how we are going to set up the same as well as what changes or tools need to be in place to handle RAC.

In this paper we are going to look at one of the important administration jobs, the backing up of a 9i RAC. We will also look at how we can recover from a simple data file loss or from the other extreme, a disaster recovery.

Click Here to view the article.

Script to Catch Sample Wait Parameters (February 2004)
By Steve Adams

It is difficult to catch buffer busy waits and in particular the wait parameters, when querying V$SESSION_WAIT because the waits are sometimes transient despite being very frequent. One approach is to query V$SESSION_WAIT multiple times within a single query. This prolongs the query execution time and greatly increases your chances of catching sample wait parameters without resorting to tracing. The following script illustrates this technique, which is of general usefulness in quite a number of situations.

Click Here to view the script.

Writing Better SQL Using Regular Expressions (February 2004)
By Alice Rischert

A new feature of Oracle Database 10g vastly improves your ability to search and manipulate character data. This feature, regular expressions, is a notation for describing textual patterns. It has long been available in many programming languages and a number of UNIX utilities.

Oracle's implementation of regular expressions comes in the form of various SQL functions and a WHERE clause operator. If you are unfamiliar with regular expressions, this article can give you a glimpse into this new and extremely powerful yet seemingly cryptic capability. Readers already familiar with regular expressions can gain an understanding of how to apply this functionality within the context of the Oracle SQL language.

Click Here to view the article.

Database & User Connection Information (March 2004)
By Michael Selvaggio

The attached anonymous PL/SQL block is useful to determine information about the connected user and to which database they are connected to. It makes use of the UTL_INADDR package which can return the IP address of any machine name or the name for a given IP address. This avoids having to start a host session to run a ping or nslookup. The first call to UTL_INADDR uses the IP address retrieved from SYS_CONTEXT for the connected user and returns the machine name. The next 2 calls pass no parameters and get the Database Server name and IP address.

set serveroutput on
set verify off

declare

v_gname varchar2(256);

begin
 select global_name into v_gname from global_name;
 dbms_output.put_line('User: '||user);
 dbms_output.put_line('Database: '||v_gname);
 dbms_output.put_line('Client IP: '||
 sys_context('USERENV','IP_ADDRESS'));
 dbms_output.put_line('Client Name: '||
 utl_inaddr.get_host_name(sys_context('USERENV','IP_ADDRESS')));
 dbms_output.put_line('DB Server IP: '||utl_inaddr.get_host_address);
 dbms_output.put_line('DB Server Name: '||utl_inaddr.get_host_name);
end;
/

Click Here to view the sample output.

Tuning PGA_AGGREGATE_TARGET in Oracle 9i (March 2004)
By Brian Peasland

Oracle 9i introduced the PGA_AGGREGATE_TARGET parameter to help better manage session working areas in a session’s Program Global Area (PGA). This paper discusses available methods to help tune this new Oracle 9i parameter.

Click Here to view the article.

Check Password (April 2004)
By Dieter Oberkofler

This function allows to check if a user/password combination is valid on a given server. This is usually not possible from within PL/SQL and Oracle does not offer any build-in mechanism or workaround to do so.

Click Here to view the script.

High Availability Using Transparent Application Failover On Oracle Real Application Clusters (April 2004)
By Murali Vallath

94% availability of a football player indicates that the player has missed one game of a season. However, 99.97% availability of a system indicates a downtime of 2.5 hours in a year. Today’s business requirements are to meet 99.99% or 99.999% availability, which indicates .5 hours or no downtime. To meet these high availability requirements with no downtime in a year, the factor critical for success is for systems to provide for auto failover when one participating system fails. If this does not occur, when a system or a participating node fails from a cluster, then a considerable amount of time will be used in migrating the user from the failed node to another, (reestablishing the database connection, re-executing the query, user having to browse through the screen to his/her last view etc).

Click Here to view the article.

BUGBUSTER - V$SQL_PLAN causes ORA-07445 (May 2004)
By Garry Robinson

Affects Versions: 9.2.0.1.0, 9.2.0.2.0, 9.2.0.3.0

Fixed in: 9.2.0.4.0

Symptoms:

Using a WHERE clause on queries against the V$SQL_PLAN table causes sessions to disconnect.

SQL> select * from v$sql_plan where address='000000038DAEB370';
select * from v$sql_plan where address='000000038DAEB370'
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel

A glance in the alert.log shows an ORA-07445 error and a trace file left in user_dump_dest. Here is an extract from the trace file:

*** 2003-09-16 11:35:57.019
*** SESSION ID:(25.3760) 2003-09-16 11:35:57.010
Exception signal: 11 (SIGSEGV), code: 1 (Address not mapped to object), 
                  addr: 0x1f8, PC: [0x101648efc, 0000000101648EFC]
*** 2003-09-16 11:35:57.024
ksedmp: internal or fatal error
ORA-07445: exception encountered: core dump [0000000101648EFC] [SIGSEGV] 
           [Address not mapped to object] [0x0000001F8] [] []
Current SQL statement for this session:
select * from v$sql_plan where address='000000038DAEB370'

Workaround

You can workaround this problem by creating a temporary version of the V$SQL_PLAN table and excluding the FILTER_PREDICATES column which is a VARCHAR2(4000). Cut and paste the code provided and use the temporary table SQL_PLAN_FIX in place of V$SQL_PLAN.

Click Here to view the script.

9i RAC: Cloning to a Single Instance (May 2004)
By Shankar Govindan

Cloning is a process that most DBAs are aware of that needs to be done often to keep the production database from being vandalized by then developers and UAT’s. A production database needs to be cloned to test or development or training databases, so that the Application development and any customization can be tested and validated, before porting to production. User acceptance tests are not done and signed off after you go production, but by testing on a copy or clone of production at a point in time. Some sites need to clone often where data integrity and validation is important, whereas others need to be cloned once in few weeks. Either way, cloning is done to test upgrades or when patches are tested on a production copy before applying it to production. So, knowledge of cloning process is a must in any DBA’s job profile.

In this paper we are going to look at one of the important administration jobs, the cloning of a 9i RAC. We will look at how we could clone a 9iRAC database which can have any number of nodes (4 is the limit I think on Solaris and depends on the OS and cluster volume managers) to a single instance. When I say a single instance, it is still going to be a single 9iRAC instance for a RAC database and not a non-RAC database. To down grade after a clone, look up the downgrade document in Metalink.

Click Here to view the article.

Fine-Grained Auditing for Real-World Problems (May 2004)
By Arup Nanda

Traditional Oracle Database auditing options let you track the actions users perform on objects at the macro level—for example, if you audit for SELECT statements on a table, you can track who selected data from the table. However, you don't know what they selected. With data-manipulating statements— such as INSERT, UPDATE, or DELETE—you can capture any changes by using triggers or by using the Oracle LogMiner utility to analyze the archived logs. Because simple SELECT statements are non-data-manipulating, they neither fire a trigger nor go into archived logs that you can mine later, so these two techniques fall short where SELECT statements are concerned.

Oracle9i Database introduced a new feature called fine-grained auditing (FGA), which changed all that. This feature lets you audit individual SELECT statements along with exact statements issued by users. In addition to simply tracking statements, FGA provides a way to simulate a trigger for SELECT statements by executing a code whenever a user selects a particular set of data. In this three-part series of articles, I'll explain how you can use FGA for solving real-life problems. This first installment focuses on building a basic FGA system.

Note: This article was previously published on OTN. Copyright 2004 Oracle Corporation. All rights reserved.

Click Here to view the article.

Fine-Grained Auditing for Real-World Problems, Part 2 (June 2004)
By Arup Nanda

In the Part 1 of this series, I described how to build a Fine-Grained Auditing (FGA) system to audit select statements, capturing the statements depending on certain conditions—a task impossible in regular auditing or though the use of DML triggers and the Log Miner tool. In addition to creating an audit trail, we also saw how we can make it execute a user-defined procedure when the conditions for audit are met.

In this installment, we will explore advanced FGA concepts such as application user models and the reconstruction of data viewed by the user.

Note: This article was previously published on OTN. Copyright 2004 Oracle Corporation. All rights reserved.

Click Here to view the article.

Transparent Clustered Database Failover Using JAVA (June 2004)
By Sudhir Movva & Murali Vallath

The best failover is the one that no one notices. Unfortunately, even though Oracle has been architected to recover very quickly, failures can severely disrupt users by dropping connections to the database. There various kinds of user operations against the database, INSERT, UPDATES, DELETS and SELECTS. Off these various types of operations, by implementing TAF with out making any application changes, only SELECT statements are failed over. Well this is good for a data warehouse operations, were most of the operations are SELECT queries. However in the real world, operations against the database are not limited to just SELECT queries, DML operations (INSERT, UPDATE and DELETE) are critical and failover without moving such operations transparently does not provide a true user friendly environment.

Click Here to view the article.

Fine-Grained Auditing for Real-World Problems, Part 3 (July 2004)
By Arup Nanda

In the previous two installments of this series, I introduced you to the concept of Fine-Grained Auditing (FGA) used to track select statements in Oracle9i Database and up. I also explained how to use the feature in complex environments such as within a web application via application contexts and client identifiers. These two articles have enough information for you to build a FGA setup for almost all types of database systems, however complex. In this third and final installment, I will explain the FGA enhancements Oracle Database 10g brings to the table.

Note: This article was previously published on OTN. Copyright 2004 Oracle Corporation. All rights reserved.

Click Here to view the article.

Supercharging the Pump (August 2004)
By Jonathan Gennick

New import and export features introduced in Oracle Database 10g, called Oracle Data Pump, represent a radical departure from the client/server approach to which database users have grown accustomed over the past several generations of Oracle Database. The server now runs export and import jobs. You can load or unload massive amounts of data quickly using parallelization, and you can adjust the degree of parallelism on the fly. Export and import jobs are now restartable, so a failure doesn't necessarily mean starting over. The API is exposed and easy to use; it's simple to create an import or export job from PL/SQL. And once started, such jobs run in the background, but you can check status and make modifications, from anywhere, using the client utilities.

Note: This article was previously published on OTN. Copyright 2004 Oracle Corporation. All rights reserved.

Click Here to view the article.

Resolving Oracle Latch Contention (September 2004)
By Guy Harrison

This white paper presents an overview of how the Oracle RDBMS uses latches to protect shared memory, the typical causes of and solutions to latch contention, and summarizes some research conducted at Quest Software that suggests that manipulating the (now) undocumented parameter “_spin_count” can be effective in relieving otherwise intractable latch contention problems.

Click Here to view the article.

Skip Scan Index Access (October 2004)
By Jeff Hunter

In Oracle, releases before Oracle9i, the Cost Based Optimizer (CBO) would only use a composite index (sometimes referred to as a concatenated index) if you referenced the index prefix column in the WHERE clause of the SQL statement. If the SQL statement omitted the index prefix column of the composite index, the CBO would not use the index.

One solution would be to simply create another index on the non-prefixed index columns in order for Oracle to use an index for faster access to the data. This method, however, may add considerable overhead when Oracle had to update several indexes during DML operations. A new feature included with Oracle9i called Skip Scan Index Access allows the Oracle CBO to use a composite index even when the index prefix column in a SQL statement has been omitted. Using this new feature, you will not have to create an additional index to provide faster access to your data. This article provides a brief introduction to Skip Scan Index Access and how it can improve the response time of your queries.

Click Here to view the article.

Tablespace Usage Script (November 2004)
By Andrew Simkovsky

This script will help determine tablespace usage in a database.

Click Here to view the script.

Resumable Import Sessions (November 2004)
By Andrew Simkovsky

It is very tough for a DBA to determine the size of the data that would get populated into each tablespace of the database when he/she imports the data from the dump file(s) given by another DBA. While importing from export dump files, DBAs used to be un sure if the import would succeed without any “lack of space issue” in the respective tablespaces, prior to the release of oracle 9i. To get rid of this issue, Oracle has introduced a method that enables the Import session to withstand for a stipulated time without terminating the session as such. This way, the DBA can skip the space related problems during the Import session.

Click Here to view the article.

Creating A Cascade Update Set of Tables (December 2004)
By Mike Ault

Many times we have the situation where a master table may be updated and dependent tables are left without a link back to the master. In this situation a cascade update option would be a nice one to have. Unfortunately Oracle Corp. doesn't provide this capability in a native manner so a PL/SQL option must be developed.

An example of a need for a cascade update would be in the situation where a dependent table could be dependent on one or more tables. For example, there may be two types of customer, one who has bought from us and we have marketing information for and another that is new to us, may buy from us, but may go with another vendor. If there are dependent tables (such as an interaction log that tracks phone calls to and from customers) it would be nice to be able to switch the dependencies from our new customer table to our established customer table.

Click Here to view the article.

Procedure to Identify SQL Statement (January 2005)
Extracted from the DBA Job Responsibilities Conference, Quest Oracle Pipetalk Forum

Assume the following scenario: The production database is in India. One user is accessing an application from the US. The query he used to execute everyday in 2/3 seconds, is now taking more than a hour since this morning. You, as a DBA, can not get a screenshot of that user's machine. The user is unable to tell you which application he is running, or which SQL query is creating the problem.

Solution: From V$SESSION, you can use his machine name to find his session ID (sid). Using that sid, you can use the attached procedure to pull out the SQL statement.

Click Here to view the script.

Data Loading with External Tables (January 2005)
By Brian Peasland

This paper shows how Oracle 9i’s new External Tables can significantly improve data loading time for large amounts of data. This paper can be used by Database Administrators, Application Developers, and Project Leaders.

Currently, I am working on a project that needs to load a large number of rows on a daily basis. Many on this project want to write an application that performs this load. I am of the opinion that one would be better served to use Oracle’s tools specifically designed to load large amounts of data, rather than write our own application. This paper was written to show the differences between an application loading data from a file compared to Oracle’s built in tools to accomplish the same thing. As we will see, the applications to load the data do not compare to the speed of Oracle’s utilities.

Click Here to view the paper.

Report on Redo Log Contention (February 2005)
By Jeffrey Hunter

This script is used to report on overall redo log contention for the instance since the instance was last started.

Click Here to view the script.

Creating CSV Files from Tables (March 2005)
By Mike Selvaggio

This script is a low cost utility for creating a CSV file from an Oracle Table. It creates a compressed CSV file, the DDL needed to create the table and a sample SQL*Loader control file. It handles most character, number and date datatypes and can easily be modified if needed.

Click Here to view the script.

Compressed Composites (Oracle 10g Compression) Explained (March 2005)
By Mark Rittman

If you've an interest in the OLAP Option and you've read some of my recent articles on the new features in Oracle 10g OLAP, you've probably seen a feature called "compression" mentioned. In this DBAZine article I described compression as "a novel form of cube compression, which promises to both enhance query performance (by retrieving fewer blocks of data for a given logical amount of data) and drastically reduce batch loading and aggregation times, saving disk space on the way. Compression in Oracle 10g OLAP is more about improving performance and scalability than saving disk space (although that’s a nice side effect). The net result of this is that, in a given batch window, Oracle 10g OLAP can now load and aggregate more data than before, and for a given amount of disk, can store more information than before. This, plus big advances in scalability internally around areas such as very big composites, makes Oracle 10g OLAP potentially a very effective platform when building particularly large cubes." So how does this compression feature actually work?

Click Here to view the paper.

Compiling Triggers (April 2005)
By Material Dreams

This procedure compiles all triggers in the specified schema. This process only takes place in the server and therefore is usually is much faster then other spooling based scripts.

Click Here to view the script.

Average Write Time to Online Redo Logs (May 2005)
By Tim Gorman

This SQL*Plus script is a query to show average write time to online redo logs on a day-by-day as well as an hour-by-hour basis over time, using data in the STATSPACK repository.

Click Here to view the script.

Graceful Failover and Failback Procedures, in Non-Dataguard Environments (May 2005)
By Brian Keating

This document describes the procedures to run “graceful” failovers and failbacks of hot standby databases, in environments that are not using Dataguard. A “graceful” failover is one that does not require databases to be opened with the “resetlogs” option – and as a result, graceful failbacks do not require the primary database to be rebuilt (i.e., they do not require the standby database’s datafiles to be copied to the primary server.) Graceful failovers and failbacks are also known as “switchovers” and “switchbacks”.

Click Here to view the paper.

Setting the OPTIMIZER_INDEX_COST_ADJ Parameter (June 2005)
By Tim Gorman

This SQL*Plus script is used to provide more usable information when deciding how to set the parameter OPTIMIZER_INDEX_COST_ADJ parameter.

Click Here to view the script.

Protect from Prying Eyes: Encryption in Oracle 10g, Part 1 (June 2005)
By Arup Nanda

Simply put, encryption is the art of disguising data. Suppose the combination to my wall safe is 37529. Because I am not very good at remembering numbers, I keep on forgetting it. So I come up an idea — I write it down on a piece of paper and tape it on the safe! There it is — it’s there when I need it, and I don’t have to worry about forgetting it. What a great idea!

But, there is a slight problem. If a burglar happens to see it, there is no need for him to try to crack the code — it’s all there in plain sight! So, I change the strategy a little bit — I decided to disguise the combination or alter it in such a way that only I know how it was altered. For instance, I may decide to reverse the digits; the number becomes 92573, which is on the piece of paper. The burglar will see the number 92573, which is not the safe combination. He will not be able to guess the correct one since he does not know how it was altered in the first place.

Click Here to view the article.

Protect from Prying Eyes: Encryption in Oracle 10g, Part 2 (July 2005)
By Arup Nanda

In Part 1 of this article, we discussed some of the basics of data encryption, including what is encryption, encryption algorithms, what type of encryption scheme to use when, and more. Now, let’s take a look at the other side of encryption: decryption and its elements.

The DECRYPT function in the Crypto package provides the reverse of the encryption. Let’s decrypt the value we encrypted in part 1 of this article. Since the encryption value is in RAW, we can write the encryption and decryption in one session and pass the encrypted value. For this, we have defined a SQL*Plus variable named enc_val, as shown in line 3 in the following code. Lines 4 through 21 show the piece of code used to perform encryption of an input value called “ConfidentialData” and the rest of lines show the decryption of the encrypted value in the variable enc_val.

Click Here to view the article.

Case Study: Using “On Servererror” Triggers to Work Around ORA-4031s (August 2005)
By Brian Keating

One of the new features in Oracle 10g is “automated shared memory management”, or “ASMM”. When this feature is enabled, the instance, itself, will dynamically increase and decrease four of the SGA structures, in response to the current workload in the instance. One of main benefits of this feature is that it will (hopefully) minimize the possibility of ORA-4031 errors in the instance – because this feature will presumably increase the memory available to the shared pool automatically, before the onset of an ORA-4031.

Click Here to view the article.

Optimize Oracle 10g on Linux: non-RAC ASM vs. LVM (September 2005)
By Bert Scalzo

It's been over a year since my first and very enthusiastic ASM article titled "Optimizing Oracle 10g on Linux Using Automated Storage Management", still currently available at:

• www.quest.com/landing/default.aspx?Id=66
• www.quest-pipelines.com/newsletter-v5/0104_A.htm
• www.oracle.com/technology/oramag/webcolumns/2003/techarticles/scalzo_asm.html

Since then, quite a lot has changed in terms of the software technologies now available. The software technology landscape has so extensively changed as to reopen the entire ASM debate. In my first ASM paper I simply assumed either people would be utilizing ASM or not – without considering RAC usage ramifications. What numerous people have told me this past year at shows, conferences and on-site visits is that while ASM makes obvious sense for RAC environments, they also desired to know whether ASM was in fact a viable alternative for non-RAC environments. Specifically, does ASM perform as well as Linux file systems using a Logical Volume Manager?

Of course that's a challenge far too enticing to pass up – especially when there are tools like Quest's Benchmark Factory that make such tests trivial. So on to the races!

Click Here to view the article.

Internal Implementation of Oracle Locks (Enqueue) (September 2005)
By Harish Kalra

Oracle server uses locks to provide concurrent access to shared resources whereas it uses latches to provide exclusive and short-term access to memory structures inside the SGA. Latches also prevent more than one process to execute the same piece of code, which other process might be executing. Latch is also a simple lock, which provides serialize and only exclusive access to the memory area in SGA. Oracle doesn’t use latches to provide shared access to resources because it will increase CPU usage. Latches are used for big memory structure and allow operations required for locking the sub structures. Shared resources can be tables, transactions, redo threads, etc. Enqueue can be local or global. If it is a single instance then enqueues will be local to that instance. There are global enqueus also like ST enqueue, which is held before any space transaction can be occurred on any tablespace in RAC. ST enqueues are held only for dictionary-managed tablespaces. These oracle locks are generally known as Enqueue, because whenever there is a session request for a lock on any shared resource structure, it's lock data structure is queued to one of the linked list attached to that resource structure (Resource structure is discussed later).

Click Here to view the article.

For questions or comments on this site: webmaster@quest-pipelines.com
All content Copyright © Quest Software, Inc. All rights reserved.