A short reminder – SQLException: Unsupported character set (add orai18n.jar to your classpath)

After upgrading one of our testing environments to a newer oracle version, some of our services started to return the following error:

SQLException: Unsupported character set (add orai18n.jar to your classpath)

From the error message wording, one could suspect that this is an character set but after running

SELECT VALUE FROM SYS.NLS_DATABASE_PARAMETERS WHERE PARAMETER = 'NLS_CHARACTERSET';

We found out that both databases return the same results.

Therefore, it is obvious that there is a version mismatch between the client and the server. And this message is Oracle’s way of telling you that you need to upgrade your client. Since we are using WebLogic, the process is more complicated.

As suggested, we tried adding the orai18n.jar to the classpath but to no avail.

In addition we discovered that all the problematic service select xmltype from the Database so we assumed that this issue could be solved by some kind of encoding workaround.
We tried converting the character set (convert(col1,’IW8ISO8859P8′,’UTF-8′); ) or tweaking the NLS settings (alter session set nls_territory) only to discover that the simple solution is to convert the xmltype to string and reconverting it back to xmltype

SELECT XMLELEMENT("RESULTS",
                    XMLELEMENT(  "Field",XMLATTRIBUTES(col_name_1 AS "name") ,col_value_1 ),
                    XMLELEMENT(  "Field",XMLATTRIBUTES(col_name_2 AS "name") ,col_value_2 )
 
                    )
 
INTO xml --  xmltype 
FROM (SELECT     
        'NAME1' AS col_name_1
        ,'VALUE1' AS  col_value_1
        ,'NAME2' AS col_name_2
        ,'VALUE2' AS  col_value_2 
 FROM dual   
        );
 
RETURN xmltype(to_clob(xml.getStringVal()));
Share on RedditEmail this to someoneShare on Google+Share on LinkedInShare on FacebookDigg thisTweet about this on Twitter

Logstash and Oracle – JDBC input settings

Lately, I began experimenting with elastic search, logstash and kibana. As a DBA, the first thing I wanted to try is Data base connectivity. Just like any new emerging technology, Oracle database is not the first priority on their implementation (and of course, documentation). There are much more examples for sexier (and free) databases like Postgresql or MySQL. As a result, there are only few examples and the configuaration is a real PITA.

While it is easy to find mysql database jdbc for logstash examples

INPUT {
  jdbc {
    jdbc_driver_library => "mysql-connector-java-5.1.36-bin.jar"
    jdbc_driver_class => "com.mysql.jdbc.Driver"
    jdbc_connection_string => "jdbc:mysql://localhost:3306/mydb"
    jdbc_user => "mysql"
    parameters => { "favorite_artist" => "Beethoven" }
    schedule => "* * * * *"
    statement => "SELECT * from songs where artist = :favorite_artist"
  }
}

It is much harder to find Oracle database jdbc logstash examples but I found something to start with at https://discuss.elastic.co/t/logstash-jdbc-input-oracle-settings/26996

jdbc {
      # The path TO our downloaded jdbc driver saved mine IN /bin, driver ojdbc6.jar also works. 
      #Best location can be /vendor folder
           jdbc_driver_library => "ojdbc7.jar"
      # ORACLE Driver Class
           jdbc_driver_class => "Java::oracle.jdbc.driver.OracleDriver"
      # ORACLE jdbc connection string TO our DATABASE, ORACLE  jdbc:oracle:thin:@hostname:PORT/SERVICE
           jdbc_connection_string => "jdbc:oracle:thin:@hostname:PORT/SERVICE"
      # The USER AND password we wish TO EXECUTE our statement AS
           jdbc_user => "DBUSERNAME"
           jdbc_password => "DBPASSWORD"
 
    # our SQL query
    statement => "select * from AWESOMETABLE"
 
    #optional extras I USE 
      TYPE => "Database"
      tags => ["awesome", "import"]
 }

Since I did not have ojdbc7.jar on my computer I chose to use ojdbc6.jar so my Oracle database jdbc logstash query came out like this:

INPUT {
jdbc {
    jdbc_connection_string => "jdbc:oracle:thin:@\\my-host:1521:my-sid"
    jdbc_user => "db-user"
    jdbc_password => "db-pwd"
    jdbc_validate_connection => TRUE
    jdbc_driver_library => "C:\path\to\ojdbc6.jar"
    jdbc_driver_class => "Java::oracle.jdbc.driver.OracleDriver"
    statement => "SELECT * from my-table"
}
}
output {
elasticsearch {
INDEX => "contacts"
document_type => "contact"
document_id => "%{uid}"
hosts => "localhost:9200"
}
}

Along the way, I encountered two error messages that are the reason for this post:
The first

” Error: java::oracle.jdbc.driver.OracleDriver not loaded. Are you sure you’ve included the correct jdbc driver in :jdbc_driver_library?
You may be interested in the ‘–configtest’ flag which you can use to validate logstash’s configuration before you choose to restart a running system. ”

Running the query with the ‘–configtest’ flag did not help much but eventually, it turned out to be a simple typo. The oracle driver was not loaded only because the call was
” java::oracle.jdbc.driver.OracleDriver ” instead of ” Java::oracle.jdbc.driver.OracleDriver “.
(this one was really annoying but I hope this will save you time I spent on it ).

Another message I got along the way was

Java::JavaSql::SQLRecoverableException: IO Error: Invalid connection string fo
rmat, a valid format is: “host:port:sid”

apparently, while all examples for the connection string are in the following format:
jdbc:mysql://localhost:3306/mydb
The valid format in my case (oracle client 11 on windows 7) was
jdbc:oracle:thin:@\\my-host:1521:my-sid

This should get you connected but if you still get

Java::JavaSql::SQLRecoverableException: IO Error: The Network Adapter could not establish the connection
Check the following:
The database and the listener are running.
The specified port in the code is the same port that the database is listening .
There is no firewall blocking
Good luck

Share on RedditEmail this to someoneShare on Google+Share on LinkedInShare on FacebookDigg thisTweet about this on Twitter

Can you move a table between schemas ?

Many reasons can raise the need to change a table owner or to move a table from one user (schema) to another . regardless of reason, one would expect that Oracle will provide an easy way to move a table to a different schema.

well, the short answer is NO . there is no direct method to change the table owner.

If you try something like

RENAME TABLE_NAME TO new_schema.TABLE_NAME ;

Oracle Will return ORA-01765: specifying table’s owner name is not allowed
While you can workaround this error by omitting the schema name. However, in our case, this is exactly what we want to do so it is still a problem.

Similarly, the ALTER TABLE command will not do the work as well.

ALTER TABLE source_schema.TABLE_NAME RENAME TO new_schema.TABLE_NAME;

While a different error message (ORA-14047: ALTER TABLE|INDEX RENAME May Not Be Combined With Other Operations. ) is returned when trying to use the alter table command to move the table to a different schema, the result is the same.

Another option to move a table to a different user is to simply copy the table using

CREATE TABLE new_schema.TABLE_NAME AS SELECT  * FROM  source_schema.TABLE_NAME;

but this will generate a lot of I/O , redo logs and might take a lot of time so it can be a solution only for small tables.
Likewise, Using export and import will require downtime.

While there is no direct method to move a table to a different schema, you can use
One of the less known advantages of partitioning to do the task. the ability to exchange partitions between tables across schema can help us achieve the goal.
Following is the process to move a table from one schema to another:
1) Create a partitioned copy of the table – you do this by using

SELECT dbms_metadata.get_ddl('TABLE','TABLE2MOVE') FROM dual;

if you want just the plain DDL without all the storage definition etc. you can run

     BEGIN
       dbms_metadata.set_transform_param dbms_metadata.session_transform,'SEGMENT_ATTRIBUTES',FALSE);
      END;

remember to add a partition clause at the end of the ddl

 CREATE TABLE "NEW_SCHEMA"."TABLE2MOVE" 
   (	"ID" NUMBER, 
	"TEXT" VARCHAR2(100)
   ) 
   partition BY hash (id) (partition p1);

2) Exchange the partition between the source and the target table

ALTER TABLE NEW_SCHEMA.table2move exchange partition p1 WITH TABLE SOURCE_SCHEMA.table2move;

3) That’s it! verify it by selecting from the source and target tables

         SELECT * FROM NEW_SCHEMA.table2move;
         SELECT * FROM  SOURCE_SCHEMA.table2move;

One thing to remember is that partitioning is an extra cost option on top of the enterprise edition license. so, if you get ORA-00439: feature not enabled: Partitioning. the option is not enabled. verify it with

SELECT * FROM v$option;
Share on RedditEmail this to someoneShare on Google+Share on LinkedInShare on FacebookDigg thisTweet about this on Twitter

PLS-00801: internal error [string]

apparently, pls-00801: internal error [phdcsql_print_kge_errors::parm pos] is one of the most annoying oracle errors. off course, phdcsql_print_kge_errors::parm pos is just an example that can be replcaed with any other undecipherable set characters.

Oracle’s official error description is:

Cause: This is a generic internal error that might occur during compilation or execution. The first parameter is the internal error number.

Action: Report this error as a bug to your Customer Support representative.

While it might be very tempting to contact Oracle Support, many users will prefer to try and solve the problem without opening a service request and waiting for an answer.
Basically, what this error means is that there is a problem with this query but Oracle is having problem describing the error. The result is a generic (and not really helpful ) error message.
Like any generic error description, many reasons can cause the problem. The only solution is trying to identify the problem yourself by trial and error.

Copy the query part of the code to a separate window and try to tweak it without changing the basics: change tables order, change aliases names, comment what you can or run it without variables. Even if this will not solve the problem, sometimes this can provide a more informative error message.
Using this method I have found several issues that caused pls-00801 internal error. I am writing a few in order to provide some list of things that can cause this error:

1) The query used a remote database using a db link and the database link user did not have proper permissions.
2) Extra not visible control characters that were added to the code during a problematic copy-paste from the web or from Word.
3) Extra characters that might have different meaning in oracle like:&,”,_ etc.
4) Extra spaces
5) Invalid objects on remote database
6) Several known Oracle bugs (search metalink for the error)
7) An error on a remote database can not be displayed.
8) Your entry here …

Of course, there are countless other reasons for pls-00801. If you encounter any other reason please share it in the comments to help others.

Share on RedditEmail this to someoneShare on Google+Share on LinkedInShare on FacebookDigg thisTweet about this on Twitter

Why is Oracle query not using my index? A cheklist

One of the most common question, when it comes to performance tuning is “Why oracle is not using my index?”. This question, together with her twin question, “Why Oracle is choosing full table scan even when the table is indexed?” cover large part of the performance tuning challenges.

Even well experienced developers and DBA’s can sometimes miss a very obvious reason causing the optimizer not to choose the expected plan. After finding myself time and time again looking for explanations for poor query performance i decided to create a small checklist that i can use to methodically check until I find the issue that is causing the optimizer not to use the index.

This is far from a complete list and the post is probably going to be constantly updated as I encounter ( ok , be reminded of) other performance killing reasons.

1) Are indexed column being changed ?
For example, the index will be used only if the value of the index is not changed in the where clause. Therefore, every manipulation on the “left side” of the where clause will prevent the index from being used. In order to check this you can add the /*+ INDEX(a, i_empno) */ hint if you can not see it being used in the explain plan then there is something in the query that prevent it from being used.

2) Are you using NOT?
The optimizer will not use the index if the query is based on the indexed columns and it contains NOT EQUAL and NOT IN.
3) Are you using LIKE (‘%% ‘) ?
if you use wildcard query with “%” in front of the string, the column index is not being used and a full table scan is required since the % can be replaces by any string. Therefore the optimizer needs to search the contents of every row of that field.
4) Are you using IS (NOT) NULL ?
- Null values are not included in the index. However, this could be worked around by using nvl when creating the index (function based index), adding the PK to the index or even adding a constant to the column: create index emp_dob_idx on emp (date_of_birth,1)
5) Are you using the leading columns in a concatenated index?
remember to put the column with the highest unique values first to make the result set smaller.
6) Are you selecting from a view?
Make sure that that the base tables have appropriate indexes
7) Are the statistics relevant and valid?
8) Does the index exists as all ?
you will be surprised how many times this is the problem
9) Is the query expected to return large portion of the table?
In this case the optimizer will prefer a full scan
10) If you are using subquery you must use only IN or = (you can workaround this by changing it to a join or a function )
11) Did you do a lot of changes on the table recently?
A large number of dml operations on the table might cause the statistics to become stale and stop using the index
12) It is possible that the data on the table is skewed?
The optimizer might expect normal distribution of values between the maximum and minimum values and might choose a wrong plan if this is not the case. For example, using 01/01/0001 or 31/12/4000 as a null values might confuse the optimizer
13) Does your query actually trying to use the index?
is the where clause contains the indexed column?
14) High degree of parallelism. High degree of parallelism skews the optimizer toward full table scans. select DEGREE from dba_tables where table_name='table-name' and owner='owner-name';
15) A full scan will be cheaper than using an index if the table is small.
16) Does it use Other indexes?
You may have other indexes that Oracle perceives as being “better” for the query.

17) Are you implicitly casting types?
Oracle sometimes cast implicitly. For example it might cast varchar2 to number when if the actual values allow (also for dates)
While it might work for a query, it will not use an index when you are joining a table on fields with different types.

18) Wrong Parameters:
optimizer_index_cost_adj – low value reduce the price of indexes use
all_rows access method – The first_rows optimizer mode is more likely to use an index than the all_rows mode.

Bonus advice:
Add the /*+ INDEX(a, col-name) */ hint. if you still can not see the index being used in the explain plan then there is something in the query that prevent it from being used.

Add the /*+ RULE */ hint. If the query uses the index with a RULE hint, you know that the problem is related to the cost-based optimizer (CBO)

Share on RedditEmail this to someoneShare on Google+Share on LinkedInShare on FacebookDigg thisTweet about this on Twitter