Simon Krenger

On a few test databases, test managers often need to preserve certain states in the database. This is why we use daily datapump scripts to create exports for archival.

It is important to note that such scripts are never a replacement for a proper RMAN backup, but an easy way to preserve multiple states of a database and reuse data where applicable.

This batch file uses the expdp tool provided by Oracle and the 7-zip archiver to compress the exports for archival. The export tool itself creates a full export of the whole database (full=y). Also, the flashback_time parameter is specified to get a consistent export.

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> 

Argh! Some kind of wild character sequence appears on the command line. A quick search shows that this is a common problem. One thing that regularly happens is that you enter a SELECT statement that returns too many rows. Because the terminal buffer only contains a certain number of rows, your query disappears and you have to retype it.

One thing DBAs regularly do is to gather information on the schemas in the database for reporting. So we want to know how many rows there are in the tables of the schema “SIMON”. When doing so, one is tempted to query the DBA_TABLES view:

SQL> SELECT table_name, tablespace_name, num_rows
  2  FROM dba_tables WHERE owner='SIMON' ORDER BY num_rows DESC;

TABLE_NAME                     TABLESPACE_NAME                  NUM_ROWS
------------------------------ ------------------------------ ----------
HUGE_TABLE                     DZ_DATA01                         8227990
[..]

However, if we query the table directly, we get a different result:

SQL> SELECT count(*) FROM simon.HUGE_TABLE;

  COUNT(*)
----------
   8230310

Why is that so? When reading through the description of the ALL_TABLES view, one finds the following note:

Well, I was quite busy before the holidays, but here is another post I just keep for my reference.

For each database, I believe it is important to automate database shutdown and database startup. This way, in case of an emergency, a systems administrator can start and stop database services without the need for a database administator. Oracle provides an excellent article on this topic, but the Oracle documentation is quite generic. So I hereby provide a step-by-step guide for Red Hat Enterprise Linux (RHEL).

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>

To change the prompt, append the following lines to your $ORACLE_HOME/sqlplus/admin/glogin.sql:

When deploying a new database, one thing I usually do is to set up a logrotate configuration for the Oracle Listener log. The Oracle Listener logs every connection he makes and when using an Application Server to interface the database, this file can grow quite a bit. So we need to make sure that we properly rotate this log and compress the old logs.

To achieve this, I usually create the following configuration for logrotate:

I just noticed that every time I need to create a password file, I have to look up the Oracle documentation for the correct syntax of the ORAPWD arguments. So this post is mainly for my own reference and contains the proper syntax for UNIX and Windows systems as well as an example for each system.

UNIX syntax:

After upgrading a database from 10.2.0.1 to 10.2.0.5, I was unable to start the Oracle Enterprise Manager. Whenever I tried to do so, the log showed that it failed to start Database Control. Even when I deconfigured the Enterprise Manager, deleted the repository and started from scratch, I still ran into the same problem. In this post, I will describe the symptoms I encountered and also provide a solution.

On one database, I noticed that SQL*Plus did not show the heading of the columns when I ran a query. The result was this:

SQL> select instance_name,status from v$instance;
mydb          OPEN

Even when entering “set heading on” didn’t change anything. I then investigated and found out that someone had changed the $ORACLE_HOME/sqlplus/admin/glogin.sql file (SQL*Plus runs this file on startup) and added the following lines:

SET LINESIZE 150
SET PAGESIZE 0

While this is quite nice and replaced the suboptimal default values, this was the cause for my problems mentioned above. Changing the line “SET PAGESIZE 0” to “SET PAGESIZE 1000” solved the problem and now, the query shows up the way I wanted:

SQL> select instance_name,status from v$instance;

INSTANCE_NAME    STATUS
---------------- ------------
mydb             OPEN

I keep this post mainly for my own reference, since I needed to do this multiple times already.

Edit the file sqldeveloper.conf in the bin/ directory of your SQL Developer installation and add the following lines to change the language to English:

AddVMOption -Duser.language=en
AddVMOption -Duser.country=US

Source