Tips

Tips

Oracle and SQL

• How to connect to a pluggable database in Oracle Multitenant

  Once you've created pluggable databases in an Oracle Multitenant system, the next step is to connect applications to them so they can be used. Here's how to do that.  Continue Reading

• Creating an Oracle Multitenant container database and PDBs

  Database administrator Brian Peasland outlines how to create the container and pluggable databases that are at the heart of the Oracle Multitenant architecture.  Continue Reading

• Oracle 18c features give DBAs incremental database capabilities

  Oracle Database 18c is really another version of 12c Release 2 with a different name. It doesn't contain major new features, but it does expand functionality in various areas.  Continue Reading

• Using Oracle 12c Unified Auditing to set database audit policies

  Oracle Database 12c's built-in Unified Auditing feature streamlines the database auditing process, including creation and implementation of audit policies. Here's how that works.  Continue Reading

• Oracle APEX application tools ease development of database apps

  Oracle Application Express lets users build web applications largely through wizards. Consultant Robert Sheldon explains the process for using the Oracle APEX toolbox.  Continue Reading

• Using Oracle ADDM reports to resolve SQL performance problems: Step-by step guide

  Using Oracle ADDM is an effective way to resolve SQL performance problems, according to Brian Peasland. ADDM is his go-to tool for finding problem SQL statements.  Continue Reading

• How to perform Oracle Flashback Transaction Queries

  Read how Oracle Flashback Transaction Query can eliminate the need to use Log Miner to reconstruct database transactions.  Continue Reading

• How to use Oracle SQL CUBE for cross-tabulation

  Using the SQL CUBE function in Oracle SQL at the right time can cut down on how much querying you'll need to do.  Continue Reading

• Oracle Database 11g SQL Tuning

  One of the main strengths of SQL is that you don't have to tell the database exactly how to obtain the data requested. You simply run a query specifying the information you want, and the database software figures out the best way to get it. ...  Continue Reading

• Inside the Oracle 11g SQL Performance Advisor, part 1

  This preview from the forthcoming book "Oracle 11g New Features" describes the goals of holistic SQL tuning and the steps to perform automated SQL tuning with Oracle 11g's new SQL Performance Advisor feature.  Continue Reading

• Inside the Oracle 11g SQL Performance Advisor, part 2

  Part 2 of this excerpt from "Oracle 11g New Features" explains how to create a SQL Tuning Set (STS) to be used as input for the SQL Performance Advisor, Oracle 11g's new automatic SQL tuning feature.  Continue Reading

• Mike Ault's Oracle "good practices": Performance management and tuning

  Mike Ault offers "good practices" for Oracle performance management and tuning, including implementing proactive database monitoring, building in alert mechanisms and more.  Continue Reading

• Mike Ault's Oracle "good practices"

  In this six-part tip, Mike Ault offers Oracle "good practices" in categories including the Oracle environment; backup, failover and disaster recovery; system security; design; coding; and performance management and tuning.  Continue Reading

• Oracle 10g expert SQL tuning techniques

  This book excerpt focuses on real-world techniques for improving the speed of SQL queries with a focus on new Oracle 10g features. The topics include the new Oracle parameters that affect SQL performance, the use of hints to change SQL execution ...  Continue Reading

• SQL puzzles and answers: Finding equal sets

  A SQL puzzle in the December 1993 issue of Database Programming & Design magazine was to use a supplier and parts table to find pairs of suppliers who provide exactly the same parts. This is the same thing as finding two equal sets. How many ...  Continue Reading

• Accounts unused for X days

  Here's a script that shows accounts and number of days since last use. It assumes session auditing is enabled.  Continue Reading

• Performance tuning a RAC implementation

  The performance tuning methodology for an Oracle Real Application Clusters (RAC) configuration can be broadly categorized into these seven steps.  Continue Reading

• See who is logged into the database

  This script is used to see who is logged into the database.  Continue Reading

• Lessons learned: Integrating Oracle and SQL Server

  Whether you're trying to execute an Oracle stored procedure on SQL Server or move data from one DBMS to another, you'll find troubleshooting advice in this collection of short lessons by Kevin Kline, our resident SQL Server Monitoring/Administration...  Continue Reading

• Which Oracle session is blocking on which row

  This script will help a DBA see which session is blocking another and on which row.  Continue Reading

• The art of SQL: Recognizing classic SQL patterns

  In this chapter from his book "The Art of SQL," Stephane Faroult examines the nine most common situations encountered when accessing data in a properly normalized database.  Continue Reading

• SQL*Plus does not support command history on Linux

  This tip demonstrates how to change SQL*Plus so that it supports command history on Linux.  Continue Reading

• Monitoring rollback progress

  This statement will help you determine how long a rollback will take and how much of it is completed.  Continue Reading

• Resource-intensive SQL

  This simple SQL script will help you find the most resource-intensive SQL in the database.  Continue Reading

• Shortest way to convert money numbers into words

  This Oracle code provides an efficient solution for numeric money conversion intoto words.  Continue Reading

• Tracking the progress of long-running queries

  This tip helps you identify all currently long-running queries in the database and shows the status of each query.  Continue Reading

• Defense tactics for SQL injection attacks

  The rate of application intrusions continues to rise, and many result from SQL injection attacks. Fortunately, SQL injection holes are relatively simple to defend against.  Continue Reading

• Update a referenced column in a MERGE statement

  Here's how to update a referenced column in a MERGE statement.  Continue Reading

• SQL generation vs. Oracle stored procedures

  An ITKnowledge Exchange member had a question about which option to choose: SQL generation or stored procedures. Fellow Oracle techies helped Steve.  Continue Reading

• Guide to Oracle freeware and shareware

  This list of free and low-cost tools and utilities will help Oracle DBAs and developers do your job better.  Continue Reading

• How to control DDL commands in your database

  This tip offers a mechanism to control unauthorized DDL commands against Oracle databases.  Continue Reading

• Purge statspack snapshots to avoid data growth

  This script offers an efficient way to purge Statspack snapshots to avoid data growth.  Continue Reading

• Oracle SQL and index internals: And if you can't change the code...

  The following is the tenth part of a 12-part series on Oracle10g CBO internals and SQL tuning optimization.  Continue Reading

• Oracle SQL and index internals: Index rebuilds

  The following is the 11th part of a 12-part series on Oracle10g CBO internals and SQL tuning optimization.  Continue Reading

• Oracle SQL and index internals: Know thy database

  The following is the sixth part of a 12-part series on Oracle10g CBO internals and SQL tuning optimization.  Continue Reading

• Oracle SQL and index internals: EXPLAIN and understand

  The following is the seventh part of a 12-part series on Oracle10g CBO internals and SQL tuning optimization.  Continue Reading

• Oracle SQL and index internals: Tune the code/database for better performance

  This tip, excerpted from "Oracle SQL and index internals" by Kimberly Floss, identifies options for tuning specialists.  Continue Reading

• Fun with SQL

  SearchOracle.com contributor Jeff Hunter provides answers to some of your basic SQL questions.  Continue Reading

• Oracle SQL and index internals: Identify resource-intensive code

  This tip, excerpted from "Oracle SQL and index internals" by Kimberly Floss, will help you ferret out bad SQL.  Continue Reading

• Oracle SQL and index internals: New performance views to identify problem SQL

  This tip, excerpted from "Oracle SQL and index internals" by Kimberly Floss, identifies four steps to identifying performance problems in an Oracle database.  Continue Reading

• HTML from SQL

  Don't want to give developers access to your databases? This script can help you push them updates via HTML instead.  Continue Reading

• Identify the 'blockers' and the 'waiters'

  This script will help you to determine what is holding a lock and what is waiting for it.  Continue Reading

• Find unnecessary duplicate indexes on a table

  This script will generate a drop index script which can be used to drop redundant indexes on all tables except those owned by user SYS and SYSTEM.  Continue Reading

• Merging datasets with NULL values

  A MERGE may appear to work properly, but it's important you check your target dataset to make sure it does. This tip explains why and how.  Continue Reading

• Find primary key index

  This tip may be used to locate the index name for a corresponding primary key constraint.  Continue Reading

• Find long-running queries

  Get read, write times and file directories from your Oracle database to determine long-running queries.  Continue Reading

• How to work with deferred constraints

  Here's how to defer a unique key constraint until the end of the transaction.  Continue Reading

• Get useful details about a table with one query

  With this code, one can get all the details of a table just by passing the table name as a parameter to the query.  Continue Reading

• Identify tables having FKs on a given table's PK

  Frequently, you need to know what tables have foreign keys on a specified table's primary key. This stored procedure displays them.  Continue Reading

• Disable/enable FK constraints against a specified parent table

  This stored procedure simplifies the task of disabling/enabling Oracle foreign key constraints against the specified parent table.  Continue Reading

• Find the most I/O-intensive SQL statements

  This tip helps you in identifying expensive SQL statements running on your Oracle system.  Continue Reading

• Selectivity and composite indexes

  Badly ordered composite keys will not get the best out of indexing, but this script can help.  Continue Reading

• Insert statement generator

  This script generates INSERT statements from an existing table that can be used as a backup or source script for future data loads.  Continue Reading

• Extract CREATE scripts for foreign key constraints

  This tip is useful for extracting CREATE scripts for foreign key constraints belonging to the current user.  Continue Reading

• Compute a table's rowsize

  This script computes the average rowsize of an existing Oracle table to help you determine required table space requirements.  Continue Reading

• Check all active processes, the latest SQL, and the SQL hit ratio

  When your Oracle server becomes slow, use this SQL to find out all ACTIVE connections with hit ratio and SQL.  Continue Reading

• Build a servlet-based application that executes SQL statements against a database

  How to use a Java servlet, JSP, and a static Java class to interactively execute any standard SQL statement against a database that's running on a server.  Continue Reading

• Managing rollback segments

  Sizing rollback segments can be a difficult task, especially with new applications. Here are a few tips and a script to help out.  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

• Importing data into user-defined tablespaces

  Use this Oracle script to import data into your specified tablespace rather than into the system tablespace.  Continue Reading

• Random number between two integers

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

• Script to run large daily schema exports

  This is a script to use on AIX servers to run very large daily schema exports.  Continue Reading

• Select every nth record

  This query will select every nth record in a table.  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

• Use autonomous transactions to record to logs

  Use autonomous transactions to record to logs regardless of the state of the parent transaction.  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

• Joining tables in SQL queries

  Discusses how to join tables in SQL Server queries.  Continue Reading

• Script to include users in the PRODUCT_USER_PROFILE table

  This script generates a script that includes users in the table PRODUCT_USER_PROFILE table.  Continue Reading

• Refresh value of a global variable only after commit

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

• Control users' access to Oracle objects from SQL*Plus

  The PRODUCT_USER_PROFILE table can be used to control users' access to various Oracle objects from SQL*Plus.  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

• Keeping Optimizer statistics up to date

  This script can be scheduled to run regularly to monitor table usage and keep statistics on schema objects current.  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

• Deciding how to handle in-doubt transactions

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

• Simple query formatter

  Use this script to format the output of queries as one column_name = column_value per line.  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

• Querying external tables

  Without loading data into your database, you can query external tables directly from flat files.  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

• Using UTL_FILE to find files on the OS

  Use UTL_FILE to detect a file on the OS insead of writing a UNIX or DOS script.  Continue Reading

• Get current SQL from SGA

  This statement is used to retrieve the current SQL of a special sid (user) from SGA.  Continue Reading

• Oracle 9i MERGE statement explained

  MERGE is a new feature available in Oracle 9i. It is mainly useful in data warehousing applications. Here is how to use it.  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

• FOLD_STR function to break SQL at given lengths

  This script creates a function that breaks/folds a SQL string into multiple lines of a given length.  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

• Display a number in words

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

• Copy data from a query to a table in a local or remote database

  This is an easy method to copy data and its structure from a local or remote database using the SQL*Plus copy command & Net8.  Continue Reading

• Archive log switch history

  Track archive log switching by the day and by the hour.  Continue Reading

• Removing duplicate tuples

  How to remove duplicate tuples using the DELETE command in SQL*Plus.  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

• Fetching the Nth highest record

  Query to return the nth highest record.  Continue Reading

• Optimal size of rollback segments

  This script displays the optimal size of rollback segments created in Oracle databases.  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

• Dynamically change NLS_LANG

  Here is a simple way to open SQL*Plus windows in two databases, without the need to use the registry to change NLS_LANG.  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

• Change the font in the SQL*Plus GUI

  Here's how to change font and font size in SQL*Plus on Windows NT.  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