Archive

Archive for the ‘Database’ Category

Oracle Client Trace Configuration

April 11th, 2017 Comments off

The purpose of this configuration is to troubleshoot Oracle client issues that might be affecting the connectivity between an application and the Oracle database server.

You can insert the following lines inside the oracle client sqlnet.ora file after fixing the path to the directories


Read more…

Categories: Database Tags:

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:

Apologies for the Downtime

May 2nd, 2014 Comments off

Dear All,

Accept my apologies for the long outage that caused the website to be unavailable.

Regards,

St0ma

Categories: Database 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:

ALTER SYSTEM SET DB_KEEP_CACHE_SIZE=1024M SCOPE=BOTH;

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.
Example:
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:

mysqldump all databases on mysql

February 20th, 2011 2 comments

I was wondering for a few minutes if I can easily backup all databases created by users on a mysql database server. I was trying to automate the process regardless of users creating new databases without my knowledge. I was aware of the convention under which mysql stores the separate databases on the filesystem so I gave it a try.
Fist I tried with few ‘ls’ commands to retrieve the folder names which respectively referred to the database names in the mysql server. On FreeBSD here is the command I used:

[root@bsdbox08 ~]# find /var/db/mysql/ -type d
/var/db/mysql/
/var/db/mysql/mysql
/var/db/mysql/db1
/var/db/mysql/performance_schema
/var/db/mysql/db2

Here I decided not to mysqldump the mysql database and the performance_schema so I changed the command slightly by adding sed and grep to it.

Read more…

Categories: Database, Linux, mysql Tags:

Oracle CTXSYS LEXER issues

January 17th, 2011 Comments off

Hello,

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:
Connected.
SQL> DROP INDEX USER5.INDX71;
Index dropped.
SQL>CREATE INDEX USER5.INDX71 ON USER5.APP_NOTES (DETAIL_TEXT) INDEXTYPE IS CTXSYS.CONTEXT;
REATE INDEX USER5.INDX71 ON USER5.APP_NOTES (DETAIL_TEXT) INDEXTYPE IS CTXSYS.CONTEXT;
*
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
ORA-06512: at “CTXSYS.TEXTINDEXMETHODS”, line 364

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: , , ,