Square DBA – Oracle DBA Talk

ORA-01555 Snapshot too old

Do you ever see the Oracle error “ORA-01555 Snapshot too old (Rollback has been overwritten)”? It is a pretty common error, but one I don’t see much anymore.

ORA-01555 error means the rollback segment that your long running query needed has been overwritten. Today I was helping out a friend who has been getting this error trying to execute a delete command. I directed him to a pretty good article on the subject over at orafaq.com. Take a look at the article, it has a lot of good information on properly dealing with the UNDO tablespace.

Oracle DBA Interview Questions

I had a friend ask me for some very basic Oracle DBA interview questions. I thought I would share.

1. Explain database instance ?

A database instance (server) is a set of memory structures and background processes that access a set of database files.  The memory structures are used to store most queried data from database. This helps us to improve database performance by decreasing the amount of I/O performed against data file.  The process can be shared by all users.

2. What is parallel server?
Multiple instances accessing the same database (Only in Multi-CPU environments).

3. What is Schema ?
The set of objects owned by user account is called the schema

4. What is an Index ? How it is implemented in Oracle Database ?
An index is a database structure used by the server to have direct access of a row in a table.An index is automatically created when a unique or primary key constraint clause is specified in create table command

5. What is a clusters? Explain
Group of tables physically stored together because they share common columns and are often used together is called Clusters.

6. What is a cluster key ?
The related columns of the tables are called the cluster key. The cluster key is indexed using a cluster index and its value is stores only once for multiple tables in the cluster.

7. What are the basic element of an oracle Database ?
It consists of one or more data files one or more control files two or more redo log files.  The database contains multiple users/schemas one or more rollback segments one or more tablespaces Data dictionary tables User objects (tables,indexes,views etc)  The server that access the database consists of

SGA (Database buffer, Dictionary Cache Buffers, redo log buffers,Shared SQL pool)
SMON
PMON
LGWR
DBWR
ARCH
CKPT
RECO
Dispatcher
User process with associated PGA

8. What is deadlock ? Explain.
Two processes waiting to update the rows of a table which are locked by the other process then deadlock arises.
In a database environment this will often happen because of not issuing proper row lock commands. Poor design of front-end application may cause this situation and the performance of server will reduce drastically.
These locks will be released automatically when a commit/rollback operation performed or any one of this processes being killed externally.

9. What is SGA ?
The System Global Area in a Oracle database is the area in memory to facilitates the transfer of information between users. It holds the most recently requested structural information about the database.

10. What is Shared SQL pool ?
The data dictionary cache is stored in an area in SGA called the Shared SQL Pool. This will allow sharing of parsed SQL statements among concurrent users.

11. What is meant by Program Global Area (PGA) ?
It is area in memory that is used by a Single Oracle User process.

12. What is a data segment ?
Data segment are the physical areas within a database block in which the data associated with tables and clusters are stored.

13. What are the factors causing the reparsing of SQL statements in SGA ?
Due to insufficient Shared SQL pool size

Difference between Active Dataguard and Logical Standby

What is the difference between Active Dataguard, and the Logical Standby implementation of 10g dataguard?

Here is an interview question I ran across on Ask Tom that I thought was perfect.
Active dataguard is mostly about the physical standby.

• Use physical standby for testing without compromising protection of the production system. You can open the physical standby read/write – do some destructive things in it (drop tables, change data, whatever – run a test – perhaps with real application testing). While this is happening, redo is still streaming from production, if production fails – you are covered.
• Use physical standby for reporting while in managed recovery mode. Since physical standby supports all of the datatypes – and logical standby does not (11g added broader support, but not 100%) – there are times when logical standby isn’t sufficient.
• It also permits fast incremental backups when offloading backups to a physical standby database

Poor performance using public synonyms

I have heard the argument that says don’t use public synonyms if you have a large user base.

Public Synonyms are a good thing for most of us, they are not evil. I use public synonyms, but I do not over use them. Use them when you need them, but don’t make a public synonyms for every darn object in your schema.

It is generally a good idea to try an interact with your tables via packages. End users do not need to see the tables much of the time, especially in an OLTP environment. Public synonyms may still be used, but in most cases you will just want to use the package to do your work.

Even the database makes use of public synonyms when access many of your SYS packages. Like all things moderation is a good idea.

Sacramento Area Oracle Group

There are no Oracle user groups in the Sacramento, CA area. If any is interested in started one, let me know. There was a group years ago, but it looks like it has not been around for a while. Sac Francisco is just too far to drive.

Clean up phone numbers

I needed to clean up a list of phone numbers today. The phone numbers where in a varchar2 column in the database and were listed like this: (916) 555 -1212.

I knew I could used some instr and substr functions to cut the data out, but I wanted to use some of the regex functions found in Oracle 10G and above. This handy little piece of code simply removed all non numeric digits from a string.

select REGEXP_REPLACE(CALLNUMBER,'[^[:digit:]]', NULL)
from temp_cima where rownum < 11;

GTD Tasks in Outlook

As I have posted before I am always looking to streamline my to-do lists.  I think I have come up with the ultimate solution (at least for me).  Anything I use must sync with Outlook.  We use MS Exchange here at work.  People send me tasks through Outlook and I receive Outlook tasks from our MS Project server so I cannot get away from Outlook.  A great deal of my tasks come from emails I receive so again Outlook is central to creating tasks.

I try to follow a lot of the GTD methodologies.  Since a fair amount of tasks are based on emails I was looking for a way to streamline that process.  I created macros that help me classify emails on my to-do list.  I actually created 3 macros (Next actions, Waiting, & Someday).
These macros do the following:
1, Takes the items that is selected and creates a task
2. Attaches the selected item to the task
3. Sets the task’s category
4. Sets a reminder for certain tasks

Here are the instructions for creating the macros. (The directions are slightly different for Outlook 2007)

1. Start Outlook
2. Tools | Macros | Security
3. Choose “Medium”, which will prompt you on whether or not you want to run macros (VBA). You may need to restart Outlook at this point in order for that setting to take effect, I’m not sure.
4. Tools | Macros | Visual Basic Editor
5. Doubleclick on This Outlook Session on the left, which will open the code window on the right
6. In the code window, paste the code from the attached file (it is attached to this post)
7. View | Immediate to make the immediate window show up

Next I added these macros to my standard toolbar.

1. View | Toolbars | Customize
2. On the Commands tab, click Macros on the left-hand side
3. Drag the appropriate macro to the standard toolbar (say, next to the “Send/Receive“ button).
4. Right click on the button and change the Name field if you want to shorten the text or assign a different accelerator key or an image
5. Click Close

Now when I get an email (or any other Outlook item) I can click one of these new buttons and create a task with the item attached.

Stupid Errors

Stupid errors are always the most frustrating.  Today while running a SQL statement for an adhoc report I received a “ORA-12801: error signaled in parallel query server P050” followed by “ORA-01722: invalid number”.  I knew the invalid number error was the real culprit and not the parallel query error.  The statement runs for 15 minutes before erroring out so I am thinking the error occurs on some row in the results.  My SQL statement uses several functions, some Oracle functions and some created by myself.  So my first thought was one of my functions was not dealing with the data properly.  Looking at each function I could not find any issues.  My next step was going to be put my SQL into a procedure so I could handle the exception and find the problem row(s).  Then I spotted the problem.

Right there at the beginning of my SQL statement I wrote “select substr(summary_date, ‘YYYYMM’)”, instead of “select to_char(summary_date, ‘YYYYMM’)”.  Since this is not a syntax error I did not see it right away.  It also did not produce the error right away since the substr function is not applied until after the items in the where clause are evaluated.

In the end sometimes issues are so simple they are right in front of your face.  For the rest of today I will be telling myself to keep it simple stupid.

Oracle Podcasts

I am looking for a god Oracle podcast.  Do you know of any?  I did subscribe to one podcast fro OTN, but I am looking for others.  I spend about two hours in the car daily and normally devote that time to news, talk radio, and music.  I catch a couple of podcasts on photography and I thought an Oracle Podcast would be a good addition.