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 is quite […]

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 <<EOF scott/tiger EOF Username: The Username: prompt is displayed 🙁   🙁 How do we get rid of […]

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 28445 28281  0 11:54:03 ?        0:00 imp king/gold full=y … What you see here above […]

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 (x,y)   5          values (sqlerrm, […]

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) […]

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 | |   1 |  NESTED LOOPS      |    18E|    15E|    18E  (0)|999:59:59 | |   2 |   […]

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, […]

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 […]

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 […]

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 user […]

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 (1-31),      […]

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 - Production

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. […]