sqlplus

PowerDesigner Annoyances 1

Submitted by dave on Sat, 12/22/2012 - 09:16

Yes, I tagged it with a '1' because I already know there are more to come :)

I created a notebook on this site to collect information about PowerDesigner. My first entry deals with basic script generation options for Oracle and how to get better scripts that are more error-free when the are generated by PowerDesigner.

You can check it out here:
http://ba6.us/PowerDesigner-Basic-Gen-Script-Settings

-Dave

Tags: 
PowerDesigner
oracle
DDL
scripting
sqlplus

Parallel Processing Shell Script for SQLPLUS...

Submitted by dave on Thu, 06/21/2012 - 12:17

Just a quick note to mention I created a notebook page on my site covering the Parallel Processing Shell Script and how to use it to run multiple SQLPlus sessions in parallel.

The page is available here.

-Dave

Tags: 
sqlplus
performance
shell
bash
parallel

RecreateDBLinks.sql

Submitted by dave on Mon, 06/04/2012 - 14:22

Purpose:
RecreateDBLinks.sql is a script that will extract DB Links DDL (non SYS or SYSTEM links) and output a script that can be used to recreate those links at a later time or on a different database. It handles dealing with encrypted passwords and with logging on as the owninguser to create private database links.
It is presumed that non DBAs should not have CREATE DATABASE link. You can change this behavior by commenting out the REVOKE lines near the end of the script.
Source:

Tags: 
sqlplus
database links
DDL

Using Proxy Connections with SQL Developer or SQLPLUS

Submitted by dave on Tue, 09/13/2011 - 15:04

When trying to debug for another user sometimes it is helpful to be able to log in as that user. In the past magic was done by admins by storing the encrypted passwords, setting to a new temporary password, logging in, and then resetting the old encrypted password back.

Luckily modern Oracle versions we have the concept of a Proxy user. This allows connecting as one user but masquerading as another user (once proper permissions have been granted of course).

So far I have done this with SQL Developer and SQLPLUS, instructions are below for a test case.

Tags: 
sqlplus
SQL Developer
Proxy

Shell Script to Run a SQLPLUS against all databases running on a server...

Submitted by dave on Tue, 12/29/2009 - 14:33

#!/usr/bin/bash
#---------------------------------------------------------------------------
# Written by : David Mann @ http://ba6.us
# Script Name : alldatafiles.sh
# Description : Provides list of all datafiles for each running database on
# on a machine
#---------------------------------------------------------------------------

ORATAB=/etc/oratab
echo "INSTANCE_NAME, FILE_NAME"

# Step through running instances
ps -ef | grep ora_smon_ | grep -v grep | cut -b61-70 | while read LINE
do
# Assign the ORACLE_SID
ORACLE_SID=$LINE

Tags: 
sqlplus
shell script

Returning Error Codes from SQLPLUS

Submitted by dave on Thu, 04/19/2007 - 06:42

There may come a time when you need to return an error code from SQLPlus, either to a calling batch file, shell script, or Perl script.

SQLPlus has a WHENEVER directive available for handling errors it encounters. This command controls the behavior of SQLPlus when an OS or SQL error occurs. There are many options for this behavior.

More after the jump…

SQL errors include errors thrown by a single command entered into SQLPlus or an error raised by a PL/SQL block:

Tags: 
oracle
sqlplus

SQLPLUS and the Ant build tool...

Submitted by dave on Thu, 03/29/2007 - 18:55

I've been looking for ways to automate running of Oracle SQLPlus PL/SQL scripts.Of course there are always batch and shell scripts but I'm looking for something less OS specific.Our Java build process is controlled by Apache Ant. It would be nice to include running of SQL scripts in this process. Here are some tools to help integrate SQLPLUS with ANT.

SQL Task
This task is best for executing bits of literal SQL. This task doesn't know anything about SQLPlus features such as formatting, variables, spooling, etc.

Tags: 
sqlplus
ant

Using SQLPLUS to generate scripts…

Submitted by dave on Thu, 08/10/2006 - 16:29

Tired of updating scripts by searching and replacing until your fingers fall off? Have a SQL statement you have to run against every table in your schema?

Next to Regular Expressions, this is one of the most useful tools in my arsenal.

Common uses for this are to write SQL statements that can work from a list of objects in the database:

Tags: 
oracle
sqlplus
Subscribe to sqlplus