csqlplus: New website

Last week, I launched a new website for my csqlplus project.

What is csqlplus, you might ask. The csqlplus script is a wrapper for SQL*Plus and will allow you to query multiple databases at once, so bascially a SQL*Plus for clusters:

Often, Oracle database administrators need to run commands on multiple databases at once. With csqlplus, a wrapper for the sqlplus command, a DBA can quickly query multiple databases simultaneously.

Basically, csqlplus is a simple wrapper script to call sqlplus for all databases specified in a file.

You can download the script from the website www.csqlplus.org.

Parameters of Pluggable Databases

When working with the new multitenant architecture of Oracle 12c, one is often confronted with questions like “Ok, is this or that possible in the pluggable database? And where is that value stored?”. In this article, I want to show you where the parameters of the pluggable databases are stored. Hint: there is no SPFILE for a pluggable database!

Read the rest of this entry »

libsqlplus.so: cannot open shared object file: No such file or directory

Recently I had to install SQL*Plus on a Linux host. In order to do so, I downloaded the appropriate Oracle Instant Client packages from the Oracle site. For my installation, I chose the ZIP files. After I unzipped the client and tried to run sqlplus, I go the following error:

simon@pandora instantclient_11_2$ ./sqlplus
./sqlplus: error while loading shared libraries: libsqlplus.so: cannot open shared object file: No such file or directory

Read the rest of this entry »

SQL*Plus on Debian

In this article, I will describe the steps necessary to install Oracle SQL*Plus on a Debian host. I am using Debian 6.0.5 and will install the “Instant Client” package from Oracle (version 11.2.0.3). First, we will prepare the system for the installation, download the installation package, set all the necessary variables, start SQL*Plus and connect to an instance. So let’s get started…

Read the rest of this entry »

Oracle STATSPACK Quick Reference

Since most of our databases are not licensed with the Oracle Enterprise Manager Diagnostic Pack, we cannot use AWR (Automatic Workload Repository) and ADDM (Automatic Database Diagnostic Monitor). So we have to use the good old Oracle STATSPACK.

The goal of this article is to provide a quick reference for installing and maintaining STATSPACK for an Oracle database. It is based on the excellent “STATSPACK Survival Guide” of Akadia AG. If you need more information on STATSPACK, please refer to the Survival Guide or the Oracle documentation, since this article is only a really quick summary on how to install, configure and use STATSPACK. Read the rest of this entry »

Reuse last query in SQL*Plus on Linux

Ok, here is a quick trick that I just found out about earlier this week. I am currently in an Oracle Database 11g: Administration 11 course and learn many new things about database administration.

One thing you will definitely encounter when working with Linux is that it is not possible to scroll back though your SQL*Plus history with the arrow keys. If you try to do so, the following happens:

SQL> select owner, count(1) from dba_tables group by owner;
OWNER COUNT(1) ------------------------------ ---------- [..] SYS 673 SIMON 88
7 rows selected.
SQL> ^[[A " - rest of line ignored. SQL> command " SQL>

Read the rest of this entry »

Nicer SQL*Plus prompt

In the last weeks, I had a few projects involving Oracle Advanced Replication and immediately stumbled upon the problem of not knowing which user and which instance was on which prompt. Instead of constantly issuing “SHOW USER” and “SELECT * FROM global_name“, I decided to add a few lines to my glogin.sql.

So before investing precious time, I fired up Google and found that other people also had the idea of changing the SQL*Plus prompt. I found the template for my own glogin.sql here and modified it so it fits my needs.

Note that my script does not display the GLOBAL_NAME of the databse, but only the INSTANCE_NAME read from v$instance. This means the prompt is usually a lot shorter:

repadmin@kdb3:SQL>

Read the rest of this entry »