Tips

Tips

• Basic Oracle space monitoring scripts

  To keep your Oracle databases running 24x7, you need some basic space monitoring scripts. Here are three useful ones.  Continue Reading

• Find out which next extents will fail

  This script will give you information about which Oracle next extents are going to fail.  Continue Reading

• Change table ownership and properties in less than a minute

  This Oracle procedure cuts the time to move a large table and its properties to less than a minute.  Continue Reading

• Determine used and free space in a tablespace

  Use this script to determine total size, used space, free space and percentage of used and free space in all tablespaces.  Continue Reading

• Estimate or monitor the time to build an index

  This code will help to estimate the time needed to build a large index or monitor the progress of the index.  Continue Reading

• 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

• Getting the last row from a table

  How to get the last row of a table when you don't know how many rows there are.  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

• 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

• Getting the last inserted sequence value

  This function will return the last generated sequence value for a given table.  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 who is executing what command using DECODE

  Find who is executing what command using DECODE.  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

• Secure import/export user

  Avoid the security problem of putting passwords in script files for privileged users.  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

• How to get the length of a CLOB column

  This tip is very useful if you need to check the length of a CLOB column in an Oracle table.  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

• Optimizing database performance, part 2: Denormalization and clustering

  Author Craig Mullins continues his series about 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

• 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

• Keep track of dropped objects

  This procedure gives information about the objects being dropped from an Oracle database.  Continue Reading

• Log switches per day/hour

  View the amount of archived redo logs per day and hour.  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

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

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

• Enable all disabled constraints

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

• Display a number in words

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

• Compile all invalid objects

  Script to compile all invalid dependent objects at once.  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

• How to query using special characters in Oracle

  How to query using special characters in Oracle.  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

• Optimal size of rollback segments

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

• Tuning the database cache hit ratio

  A few quick Oracle database cache hit ratio tuning tips.  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

• 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

• 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

• 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

• A stored procedure to send email

  A stored procedure to send an email message using the Oracle utl_smtp package.  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

• 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

• Change the SQL prompt

  Edit Glogin.Sql to reflect which database you are in.  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

• Replace NULL values

  Convert NULL values to whatever you want with this simple command.  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

• The database development life cycle

  Describes the four common approaches for building database applications: waterfall, rapid prototyping, incremental, and 4GL.  Continue Reading

• Database uptime

  This query calculates and displays database uptime.  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

• Use of the NVL2 function

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

• Alternatives to the relational database model

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

• Do you have audit files?

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

• Removing duplicate records by using Oracle's ROWID

  Explains how a table of records can be de-duped by using ROWID column in Oracle.  Continue Reading