Archive for the ‘Oracle’ Category

SLES 11 SP1 Hangs on Boot with Oracle Listener

February 3rd, 2015 Comments off

Every once in a while you might experience boot issues if you have configured Oracle to automatically start the listener, instance and agent in the /etc/sysconfig/oracle.

There might be few reasons for this to happen.

In my case after some troubleshooting I noticed that the tnsnames.ora file was configured to use an IP address rather than hostname.

Read more…

Categories: Database, Linux, Oracle, SLES Tags:

Oracle DDL Generation using DBMS_METADATA.GET_DDL

August 26th, 2014 Comments off

I find it rather useful to extract and reuse DDL from existing systems.

This allows me to create objects with confidence depending on the system of extraction.

I recently had to recreate a couple of new tablespaces in line with the existing ones.

In the example below, the existing tablespace name is TAB_BIN_OBJ.

Read more…

Categories: Database, Oracle Tags:

Oracle Compile Invalid Object – PLS-00201: identifier ‘DBMS_LOCK’ must be declared

March 29th, 2013 3 comments

Just finished importing a complete oracle database dump with impdp.

Immediately after the import I checked for invalid objects and of course some were present.

In order to get the complete list of schema objects that were invalid I ran the following:


Read more…

Categories: Oracle Tags:

Oracle Database Supported Rleases for Linux

March 20th, 2013 Comments off

It usually takes me more than 10 minutes to get to the location of this page through metalink searches or just the search engines.

Now I will know that I have it stashed as a post!

Oracle Linux Supported Releases

Read more…

Categories: Database, Oracle Tags:

Oracle Check Content of Buffer Pool Keep (KEEP CACHE)

November 12th, 2012 Comments off

I tend to need configuration of KEEP_CACHE for static schema objects.
Keep Cache in Oracle is part of the Buffer Pool. Configuring Keep Cache is done with the following command as SYSDBA:


The above will allocate 1GB of SGA Memory to the Keep Cache.

Read more…

Categories: Database, Oracle Tags:

Generate SQL queries from Excel document

October 28th, 2011 1 comment

Hi All,
Long time no see, eh?!
That’s what happens when you start working for utilization targets..
When the business needs some Excel to be processed through a database in a certain manner I tend to save lots of time by generating the SQL queries automatically inside the Excel document.
You have an Excel document that depicts table structure inside your database. the Excel document contains the latest updated by the business version of some variables that should be updated inside your database. (Here many of you will ask the logical question, who does stuff in such a rigid and obsolete way? And I have nothing to say in my defense)
What I do is to pickup the important columns that form my query and generate in in a free columns on the right hand side.
="update table_name set colum_name_1="&F2&" where column_name_2='"&A2&"' and column_name_3='"&C2&"';"

For F2=5, A2=STRING1, C2=STRING2 the result will be the following:

update table_name set column_name_1=5 where column_name_2='STRING1' and column_name_3='STRING2';

Read more…

Categories: Database, mysql, Oracle Tags:

Oracle CTXSYS LEXER issues

January 17th, 2011 Comments off


I recently tried to troubleshoot an application issue that was related to a text index. The index itself appeared in valid state but there was obviously some kind of a problem with it.
I started with the following

SQL> connect sys as sysdba
Enter password:
Index dropped.
ERROR at line 1:
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-20000: Oracle Text error:
DRG-10700: preference does not exist: CTXSYS.DEFAULT_LEXER
ORA-06512: at “CTXSYS.DRUE”, line 160

There was something wrong with the CTXSYS schema and especially with the DEFAULT_LEXER.
I fixed this by reapplying the correct drdefXX.sql script for the database locale.

Read more…

Categories: Database, Oracle Tags: , , ,

Gather Schema and Index Statistics in Oracle

January 28th, 2010 2 comments

In this post I would like to draw your attention to the Optimizer in Oracle 10g.
The best place to start is the DBMS_STATS package reference page.

The DBMS_STATS package contains all the procedures required to maintain the optimizer statistics of any schema.
Once you get familiar with the documentation of the package, you will actually know which approach is best for your database and schemas. In my case I have to gather schema statistics once per week with a specific estimate_percent value and I have to gather schema index statistics every night. For this purpose I have created the following simple PL/SQL. I recommend if you create jobs and schedule them as appropriate.

-- Gathering Schema Statistics
execute dbms_stats.gather_schema_stats('SCHEMA1', estimate_percent=>39, cascade=>TRUE);
execute dbms_stats.gather_schema_stats('SCHEMA2', estimate_percent=>45, cascade=>TRUE);
-- Gathering Index Statistics
set serveroutput on
for ind in
(select object_name
from user_objects
where object_type='INDEX'
and object_name like 'YOUR_INDEX_PATTERN_HERE'
dbms_output.put_line('Gathering Index Statistics for '||ind.object_name||'.....');
dbms_stats.gather_index_stats('SHEMA1', ind.object_name , estimate_percent=>100);
dbms_output.put_line('Gathering Index Statistics for '||ind.object_name||' is Complete!');
end loop;

Read more…

Compile Invalid SYNONYM Oracle

January 11th, 2010 4 comments

Happy New Year!
Wish you all the great stuff you have been dreaming about to come true in 2010!

I haven’t posted for a long time but my excuse is the busy season at work. At the end of the day I am finding a minute to share a simple but useful hint on generating the COMPILE SQL for invalid PUBLIC SYNONYM objects.

Every time you drop and recreate an object such as a SEQUENCE, the PUBLIC SYNONYM for that objects invalidates (if such exists). In order to generate the COMPILE commands you could use SQL similar to the following two examples:

Read more…

Categories: Database, Oracle Tags: , , , ,

Fixing incorrect crs_stat

September 8th, 2009 Comments off

I was interested to find out how I can change the configuration of the oracle cluster nodeapps.
I had a problem with the listener running on both nodes.
The problem was an incorrect ACTION_SCRIPT=/u01/app/oracle/oracle/product/10.2.0/asm/bin/racgwrap
The path was incorrect due to previous installation that I did with the wrong path. This leads to unknown status in the application state as follows:

oracle@rac03:/u01/crs1020/crs/public> crs_stat -t
Name Type Target State Host
ora….SM1.asm application ONLINE ONLINE rac03
ora….03.lsnr application ONLINE UNKNOWN rac03
ora.rac03.gsd application ONLINE ONLINE rac03
ora.rac03.ons application ONLINE ONLINE rac03 application ONLINE ONLINE rac03
ora….SM2.asm application ONLINE ONLINE rac04
ora….04.lsnr application ONLINE UNKNOWN rac04
ora.rac04.gsd application ONLINE ONLINE rac04
ora.rac04.ons application ONLINE ONLINE rac04 application ONLINE ONLINE rac04

In order go fix this I exported the listener configuration by using the following for both nodes separately:

Read more…