How to tune WHERE NAME LIKE ‘%ABC%’

More than once customers wanted me to tune queries where the LIKE clause made the query very slow… The easy answer is : you cannot. If you want to search for some characters in the middle of the string, Oracle will not use an index. Is it a correct answer? Maybe not. Let’s imagine my […]

on recycle bin

more than one user may wondered who created those BIN$ when they first connected to a 10g database. create table lsc_t(x number) partition by range(x) (partition LESS_THAN_ONE values less than (1)); drop table lsc_t; select object_name, subobject_name, created from user_objects where object_name like ‘BIN$%’; OBJECT_NAME SUBOBJECT_NAME CREATED —————————— ————— ——— BIN$bh2VJ6FqFJ3gRAAUT+rFpg==$0 LESS_THAN_ONE 07-JUL-09 Ok, it […]

return code before grep

In my previous post hide-password-from-ps-output-sql-loader I mentioned a way to pass the password to the loader thru a parameter file. As correctly suggested by Brian Tkatch, the password could be passed as standard input sqlldr control=x.ctl silent=header,feedback

Hide password from ps output : sql loader

By reporting the process status with ps, any Unix user will see the command line arguments ps -ef UID PID PPID C STIME TTY TIME CMD lsc 13837 13825 0 May 11 pts/17 0:01 -ksh oracle 4698 6294 0 12:00:40 ? 0:00 sqlplus -s system/manager appluser 4229 4062 0 12:00:03 ? 0:00 sqlldr scott/tiger applrun0 […]

ora-984 and sqlerrm

What’s wrong with this code ? Connected to: Oracle9i Enterprise Edition Release 9.2.0.8.0 – 64bit Production With the Partitioning option JServer Release 9.2.0.8.0 – Production SQL> drop table lsc_t ; Table dropped. SQL> create table lsc_t (x varchar2(255), y date); Table created. SQL> begin 2 null; 3 exception when others then 4 insert into lsc_t […]

grant access to trace files

As a developer, you sometimes need to contact your dba to get an user trace. As a dba, sending trace files to developer is not much fun. But how do you get access to your traces without any dba ? One way could be to set _trace_files_public=true, but this is bad. It is undocumented, it […]

package version control

Oracle does not offer any kind of version control like CVS or subversion in the database. CVS and subversion could be used in the filesystem, then the code could be deployed with sqlplus. To quickly compare packages in Test and Production I used : select env,name,type,line,text from ( select env,name,type,line,text, count(distinct text) over(partition by name,type,line) […]

commit suicide;

Tanel Poder directed me to Miladin Modrakovic blog, I will definitely add this to my prefered feed. You have to try this : SQL> alter session set events ‘immediate crash’; alter session set events ‘immediate crash’ * ERROR at line 1: ORA-03113: end-of-file on communication channel A very decent method for killing yourself 😈

track DDL changes

Why is my package being invalidated? This is the question I asked myself a few times those days. In order to find out what is happening in my schema, I created this simple DDL trigger which tracks all DDL changes in my schema. CREATE TABLE AUDIT_DDL ( d date, OSUSER varchar2(255), CURRENT_USER varchar2(255), HOST varchar2(255), […]

high cost

What’s wrong with this query ? select (t6.column_value).getstringval() t6 from table(xmlsequence(extract(xmltype(‘<x/>’),’/x’))) t1, table(xmlsequence(t1.column_value))t2, table(xmlsequence(t2.column_value))t3, table(xmlsequence(t3.column_value))t4, table(xmlsequence(t4.column_value))t5, table(xmlsequence(t5.column_value))t6; T6 ———————— <x/> Elapsed: 00:00:00.01 Well, let’s check the plan : ——————————————————————– | Id | Operation | Rows | Bytes | Cost (%CPU)| Time | ——————————————————————– | 0 | SELECT STATEMENT | 18E| 15E| 18E (0)|999:59:59 | | […]

Stored outlines

Note: Performance Tuning Guide Stored outlines will be desupported in a future release in favor of SQL plan management. In Oracle Database 11g Release 1 (11.1), stored outlines continue to function as in past releases. However, Oracle strongly recommends that you use SQL plan management for new applications. SQL plan management creates SQL plan baselines, […]

where is my database link listed?

$ sqlplus scott/tiger@DEVL SQL> select * from all_db_links; no rows selected SQL> select * from dual@PROD; D – X Hey, why does this work??? Ok, after some research I found out that this seems to be an implicit loopback database link. The fact that the DEVL database has the global name set to PROD is […]

11g release 1 patchset 1

I just notice on Sven Blog that 11.1.0.7 is available. I have recently upgraded my connection at home so it took a bit less than half an hour to download this 1.5G patchset $ wget -O p6890831_111070_Linux-x86-64.zip http://oracle-updates.oracle… –10:17:40– http://oracle-updates.oracle.com/ARUConnect/p6890831_111070_Linux-x86-64.. Resolving oracle-updates.oracle.com… 87.248.199.23, 87.248.199.24 Connecting to oracle-updates.oracle.com|87.248.199.23|:80… connected. HTTP request sent, awaiting response… 200 OK […]

About case sensitivity

SQL is a case insensitive language. That means, you can write any SQL statement in uppercase or lowercase. SQL> SELECT DUMMY FROM DUAL; D – X SQL> select dummy from dual; D – X Some formatters like Toad and some courseware like Skillsoft E-Learning advise to use UPPERCASE for commands and lowercase for tables and […]

Difference between rollbac and rollback

What is the difference between rollbac and rollback? SQL> create table t as select 1 x from dual; Table created. SQL> update t set x=2; 1 row updated. SQL> savepoint a; Savepoint created. SQL> update t set x=3; 1 row updated. SQL> rollbac to savepoint a; Rollback complete. SQL> select * from t; X ———- […]

return size of to_char

The width of a columns is known before execution. Well, in most cases… SELECT TO_CHAR(SYSDATE,’DAY’,’NLS_DATE_LANGUAGE=FRENCH’) FROM DUAL; TO_CHAR( ——– SAMEDI The length is as most 8 characters (VENDREDI). Therefore the width of the column is 8. SELECT TO_CHAR(SYSDATE,’YEAR’) FROM DUAL; TO_CHAR(SYSDATE,’YEAR’) —————————————— TWO THOUSAND EIGHT Oracle thinks the length is at most 42 characters. This […]

SET LONGCHUNKSIZE

I noticed a side effect of SET LONGC today… The default setting for LONG and LONGC is 80. This is quite annoying when you SELECT TEXT FROM ALL_VIEWS as it truncates the text to 80. So why not setting it to the maximum? Let’s first demo the usage of LINESIZE, LONG and LONGCHUNKSIZE SQL> create […]

Start Oracle in Vista with one click

I have been using Vista for about 3 months and finally found a way to start my databases with a single click 🙂 The method I used until yesterday was a .BAT file that starts the services, I had then to right click on the shortcut, run as administrator, confirm the UAC warning. 3 Clicks. […]

Hey Scott, where have you been ?

Today I missed Scott in my emp table. When selecting from EMP, Scott is not there. Gone… Ok, let’s recreate the scott schema. C:> sqlplus / as sysdba SQL*Plus: Release 10.2.0.3.0 – Production Copyright (c) 1982, 2006, Oracle. All Rights Reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 SYS@lsc02> spool scott.txt SYS@lsc02> drop […]

How to cron?

RTFM is not the best answer … man crontab SunOS 5.10 Last change: 10 Nov 2005 User Commands crontab(1) A crontab file consists of lines of six fields each. The fields are separated by spaces or tabs. The first five are integer patterns that specify the following: minute (0-59), hour (0-23), day of the month […]

drop all objects

warning: the script below is destructive and not 100% safe update: it is unsafe to drop SYS_ objects, check for instance note 579399.1 A question was posted on the french forums of developez.net about how to drop all objects of an user. The drop user toto cascade; followed by create user toto identified by tott; […]

alert log in xml format

The alert log is in xml format in Oracle 11g. If you want to parse the content of the file and use the XML functions to retrieve the data, you can use this way : SQL> create or replace directory alert as 2 ‘/app/oracle/diag/rdbms/lsc01/lsc01/alert’; Directory created. SQL> var c clob SQL> declare 2 b bfile […]

oradebug tracefile_name

I have enabled tracing in a session and now I want to retrieve the name of the tracefile. Ex: my session has sid 335. How do I retrieve trace file name from sqlplus ? select pid from v$process where addr in (select paddr from v$session where sid=335); PID ———- 47 Now I can use oradebug […]

Side effect of cursor sharing

Cursor sharing transform strings in bind variable. So if you do SELECT * FROM EMP WHERE ENAME=’SCOTT’; it will be transformed in SELECT * FROM EMP WHERE ENAME=:sys_b0; This sometimes improved performance of application that do not use binds, for instance. Now let’s imagine your application generates trace files. The application generates a SELECT ‘LSC-11.1011.000.1110.1.0100.000.110’ […]

alter user identified by values in 11g

I wrote about dba_users changes in 11g . When spooling alter user commands in 11g, it is important to understand the mechanism. Oracle 11g supports both sensitive and insensitive passwords. When issuing an CREATE/ALTER USER IDENTIFIED BY PASSWORD, both the insensitive and the sensitive hashes are saved. SQL> create user u identified by u; User […]

Oracle 10.2.0.4

Sven’s Technik-Blog » Blog Archive » Oracle 10.2.0.4 für Linux x86 ist verfügbar… Patchset 6810189 is now available for Download on Metalink. select * from v$version; BANNER —————————————————————- Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – Prod PL/SQL Release 10.2.0.4.0 – Production CORE 10.2.0.4.0 Production TNS for Linux: Version 10.2.0.4.0 – Production NLSRTL Version 10.2.0.4.0 […]

add a new language to Oracle

I first thought of adding Klingon. Well, finally I added Romansh, which is the fourth official language in my country. Ok, here we go : $ $ORACLE_HOME/nls/lbuilder/lbuilder & The Oracle Locale Builder tool is started. File –> New… –> Language You specify the language, the spelling for January, for Monday, etc… Most fields are mandatory. […]