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

• 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

• See who is logged into the database

  This script is used to see who is logged into the database.  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

• Monitoring rollback progress

  This statement will help you determine how long a rollback will take and how much of it is completed.  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

• Update a referenced column in a MERGE statement

  Here's how to update a referenced column in a MERGE statement.  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

• 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

• 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

• 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

• 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

• 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

• 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

• 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

• Refresh value of a global variable only after commit

  Refresh a value of a global variable only after a commit.  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

• Deciding how to handle in-doubt transactions

  This tip can help you decide how to handle in-doubt transactions.  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

• 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

• 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

• 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

• 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

• 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

• 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

• 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

• 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

• 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

• 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

• 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

• Change the SQL prompt

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

• Replace NULL values

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

• Comparing the contents of two tables with SQL

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

• Renaming foreign keys

  A script that renames foreign keys from system-assigned constraint names to more intelligible names.  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

• Use of the NVL2 function

  Oracle has a function--NVL2--that checks the existence of NOT NULL. Here's the syntax.  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

• 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

• UNION, EXCEPT, and INTERSECT clauses

  Here's a way for SELECTs to be combined to create more powerful queries.  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

• 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