Tips

Tips

• Random number between two integers

  This function generates a random number between any given two integers.  Continue Reading

• Select every nth record

  This query will select every nth record in a table.  Continue Reading

• Automatic coalesce of all tablespaces

  This Korn shell script will coalesce all tablespaces.  Continue Reading

• Role of brick and blade servers in the data center

  Bricks, blades not a cure-all, but they offer power, save space  Continue Reading

• Use autonomous transactions to record to logs

  Use autonomous transactions to record to logs regardless of the state of the parent transaction.  Continue Reading

• Determining the hit ratio of Oracle databases

  This script is useful for determining the hit ratio of your Oracle database.  Continue Reading

• Find who is executing what command using DECODE

  Find who is executing what command using DECODE.  Continue Reading

• Getting the last inserted sequence value

  This function will return the last generated sequence value for a given table.  Continue Reading

• Five myths of data mining

  Many successful companies have discovered is that the myths that have grown up around data mining are just that: myths.  Continue Reading

• Browsing OS files from TOAD or SQL*Plus

  This tip describes the process required to browse operating system files from a PC application like TOAD or SQL*Plus.  Continue Reading

• Find what each Oracle process is doing

  Here is a handy script that will correlate the operating system process with the Oracle process.  Continue Reading

• Truncate tables using PL/SQL

  This tip truncates data from tables using PL/SQL for Oracle8i.  Continue Reading

• Refresh value of a global variable only after commit

  Refresh a value of a global variable only after a commit.  Continue Reading

• Tablespace monitor for autoextend

  Tablespace monitor for autoextend  Continue Reading

• Accessing non-Oracle databases using stored procedures and JDBC

  This tip demonstrates how to connect to non-Oracle databases from within Oracle stored procedures using JDBC.  Continue Reading

• Most efficient way to remove duplicate rows

  This script uses a hash join, the most performance efficient way of joining huge tables, to find duplicate rows.  Continue Reading

• Script to determine OS process ID for timed-out sessions

  This script helps you to find out the Unix PID for the timed out sessions.  Continue Reading

• Enable/disable foreign key constraints

  This script is useful for automatically enabling or disabling all the foreign keys in a schema.  Continue Reading

• Optimizing database performance, part 3: Compression, page size, and more

  Author Craig Mullins continues his series on optimizing the performance of any database management system.  Continue Reading

• Extracting the DDL from a .dmp file

  Instead of using the long syntax of an import, you can try a text search on the .dmp file to extract the DDL.  Continue Reading

• A report of all users, grants and privileges

  This SQL creates a report of all users and their grants, roles and privileges.  Continue Reading

• Optimizing database performance, part 1: Partitioning and indexing

  Craig Mullins introduces the techniques for optimizing the performance of any database.  Continue Reading

• Deciding how to handle in-doubt transactions

  This tip can help you decide how to handle in-doubt transactions.  Continue Reading

• Optimizing database performance, part 2: Denormalization and clustering

  Author Craig Mullins continues his series about optimizing the performance of any database.  Continue Reading

• Compressing Oracle exports on the fly

  Here is how to create compressed Oracle export files on the fly.  Continue Reading

• Determining transactions per second

  This code can approximate the number of transactions per second by looking at the number of SCN changes per second.  Continue Reading

• Database and Listener startup check

  The following procedure explains the procedure to be followed to check if databases and listeners are up and running.  Continue Reading

• Unix script to gather tablespace information

  This Unix script is used to gather tablespace information, including space, used_space and fragmentation.  Continue Reading

• Last DDL time by tablespace

  There are times when one may need to know when the last DDL was performed on a particular tablespace. Here's how.  Continue Reading

• Find tablespaces that are in backup mode

  This statement can be used by DBAs to instantly find out all the tablespaces in backup mode.  Continue Reading

• Finding and deleting duplicate rows from a table

  This tip will tell you how to find duplicate rows in a table and delete them.  Continue Reading

• Hiding the Oracle password

  Hide the password when logging in via SQL*Plus.  Continue Reading

• How to find VARCHAR2 columns that contain only numbers

  Check if a VARCHAR2 column contains a valid number using a pure SQL statement.  Continue Reading

• Log switches per day/hour

  View the amount of archived redo logs per day and hour.  Continue Reading

• Enable all disabled constraints

  Here's an Oracle script that enables all disabled constraints.  Continue Reading

• Copy data from one database (SID) to another database

  Copy data from one database (SID) to another database.  Continue Reading

• Display a number in words

  Here is how to display a number in words in Oracle.  Continue Reading

• Removing duplicate tuples

  How to remove duplicate tuples using the DELETE command in SQL*Plus.  Continue Reading

• Monitoring daily archive log switches: Number, size and frequency

  This script gives you the daily number of redo log switches, archive log disk space needed, and average log switches/hour.  Continue Reading

• Find the total number of opened cursors for any particular user

  How to find the total number of opened cursors for any particular user.  Continue Reading

• Automatically delete archive logs

  How to automatically delete archive logs every so many days, for databases running in archivelog mode?  Continue Reading

• How to run two or more databases in different time zones on the same machine

  How to run two or more databases in different time zones on the same Unix machine.  Continue Reading

• Tuning the database cache hit ratio

  A few quick Oracle database cache hit ratio tuning tips.  Continue Reading

• Optimal size of rollback segments

  This script displays the optimal size of rollback segments created in Oracle databases.  Continue Reading

• How to modify the snapshot structure without dropping the table

  Here's a script to modify the structure of the snapshot for Oracle replication without dropping and recreating the snapshot.  Continue Reading

• Identify missing FK indexes and create indexes to avoid deadlocks

  A script to identify missing foreign key indexes and automatically create appropriate ones.  Continue Reading

• How to convert milliseconds to the proper date format

  How to convert milliseconds in Oracle to the proper date format.  Continue Reading

• Check digit function

  Have a check-digit in id-columns, especially if values are entered manually.  Continue Reading

• Allow a user to kill certain sessions without giving them the alter system privilege

  This procedure will give the user the ability to kill most Oracle sessions without giving them the alter system privilege.  Continue Reading

• 13 reasons why normalized tables help your business

  Data independence & adaptability go hand in hand, and the normalized data schema is critical to reaching these objectives.  Continue Reading

• How to find carriage returns in data

  CARRIAGE RETURNS basically screw up records and create problems while processing them. Here's how to find them.  Continue Reading

• Get EXPLAIN PLAN without running the query

  There are times when one needs to see the path that Oracle will choose for a particular query WITHOUT actually running it.  Continue Reading

• How to avoid 'mutating table' errors

  A "mutating table" error can be avoided by writing a procedure/function that declares the pragma autonomous_transaction.  Continue Reading

• An alternative to SQL INSERT statements

  Inserts rows into any table in the users schema without the labour of having to construct the usual SQL insert statement.  Continue Reading

• Oracle alert log miner

  A little C program that extracts lines from the alert log for a specified date (or date range).  Continue Reading

• Checking internal concurrent manager status

  How to check which process belongs to which database by comparing the parent process with the Internal Manager process.  Continue Reading

• Converting an adjacency list model to a nested set model

  Another way of representing trees in SQL is to show them as nested sets. Here's how.  Continue Reading

• SAN vs. NAS: What's the difference?

  Understanding the classes of data access before implementing SAN or NAS.  Continue Reading

• Append date to file name for automated jobs

  This Oracle script will automatically add the date to a flat file name.  Continue Reading

• Total record count per schema

  This script counts the number of records in all the tables in a particular schema in a particular instance.  Continue Reading

• Change the SQL prompt

  Edit Glogin.Sql to reflect which database you are in.  Continue Reading

• Identify the user and session ID for a UNIX process

  This quick process identifies the Oracle user and session ID of a UNIX process that is using up a large amount of CPU.  Continue Reading

• What SQL is running and who is running it?

  A script to determine what SQL is running, who is running it, and other information.  Continue Reading

• Shutting down all of your Oracle databases

  Here's an easy way to shut multiple databases down immediately.  Continue Reading

• Identifying the Oracle datafile MAXSIZE

  A simple way of identifying the maximum size to which a datafile can grow before switching to the next datafile.  Continue Reading

• Tracking database tablespace growth

  These scripts will allow tracking of growth for an Oracle database, and flag possible error conditions before they occur.  Continue Reading

• When should you rebuild an index

  Some commands and methods for rebuilding an index.  Continue Reading

• Using INITCAP in Oracle and SQL Server

  A well-annotated stored procedure showing how to use the Oracle funciton INITCAP in both Oracle and SQL Server.  Continue Reading

• Faster alternative to "NOT IN" queries

  There is a faster alternative to using "NOT IN" queries. Here's how.  Continue Reading

• Calculating the number of working days in a month with SQL

  Here is a way to calculate the number of working days in a given month & year with SQL.  Continue Reading

• How and why to use CUME_DIST

  How to use Oracle's cumulative distribution function.  Continue Reading

• Monitor the import or export of an Oracle database

  A script that will allow a DBA to see how many rows were imported or exported and is especially useful with big tables.  Continue Reading

• Calling Java stored procedures from triggers

  Coupling triggers and Java-stored procedures can facilitate constraint or application requirements.  Continue Reading

• Comparing the contents of two tables with SQL

  A SQL script that compares the contents of two tables.  Continue Reading

• Restricting particular IP addresses from Oracle databases

  Have you ever wanted to control whether or not particular IP addresses can connect to your Oracle database? Here's how.  Continue Reading

• Renaming foreign keys

  A script that renames foreign keys from system-assigned constraint names to more intelligible names.  Continue Reading

• VBA database class modules

  A short introduction to using VBA class modules in database application development.  Continue Reading

• Minimizing deadlocks in SQL Server

  How best to detect and resolve deadlocks in SQL Server.  Continue Reading

• SELECT DISTINCT queries and derived tables

  How to overcome performance problems related to SELECT DISTINCT queries.  Continue Reading

• Selecting alternate records

  Use this tip to find alternate or even/odd records.  Continue Reading

• Disable all foreign keys in the user's schema

  This script shows how to disable all foreign keys in the user's schema.  Continue Reading

• Database uptime

  This query calculates and displays database uptime.  Continue Reading

• Alternatives to the relational database model

  The five alternatives to the relational database model are reviewed.  Continue Reading

• Use of the NVL2 function

  Oracle has a function--NVL2--that checks the existence of NOT NULL. Here's the syntax.  Continue Reading

• Do you have audit files?

  Automatic audit files for Oracle on Unix platforms steal disk space.  Continue Reading

• Compiling object dependencies

  Keep track of your Oracle database object changes with this script that automates the process.  Continue Reading

• How to find longest running transaction in Sybase

  This tip allows you to access the table that tracks server transactions.  Continue Reading

• Creating an INDEX on primary key columns before creating a primary key

  Better to create an INDEX on primary key columns before creating a primary key.  Continue Reading

• What makes a good database?

  Find out the fundamentals of what makes a good database.  Continue Reading

• UNION, EXCEPT, and INTERSECT clauses

  Here's a way for SELECTs to be combined to create more powerful queries.  Continue Reading

• JDBC: Setting your AS/400 library list

  Use JDBC objects to define and run standard SQL statements to select and update AS/400 data files.  Continue Reading

• Trees in SQL

  A set-oriented method for representing trees in SQL that runs orders of magnitude faster than the adjacency list method.  Continue Reading

• Four methods for designing a data warehouse/data mart environment

  There are (at least) four different ways to design your data warehouse/data mart environment. Learn how to choose your design and what to consider when designing your data warehouse in this tip.  Continue Reading

• Why data warehousing projects fail

  No one can guarantee a DW project won't fail, but with a little planning, you can avoid some of the common roadblocks.  Continue Reading

• Review your Oracle & HP MC/ServiceGuard configuration

  Review your configuration of Oracle and MC/ServiceGuard to avoid headaches.  Continue Reading

• Using SQL to calculate nearby ZIP codes

  This tip shows how to use SQL to calculate nearby ZIP codes.  Continue Reading

• Adding database redundancy to reduce SQL complexity, increase performance, and reduce your workload

  When designing database schemas, place multi-valued attributes into a separate table.  Continue Reading

• Creating an artificial sort order using the Oracle DECODE function

  Use the DECODE function to change the sorting of a query's data.  Continue Reading

• Managing the WHERE clause for better query performance

  This tip discusses the effects of bad WHERE clauses.  Continue Reading

• The associative model of data

  Read about this advancement beyond the relational model.  Continue Reading