Tips
Tips
-
Oracle vs. SQL Server: Why Oracle wins
In this comparison of Oracle vs. SQL Server, Oracle comes out on top. Continue Reading
-
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
-
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