updatedb does not work with samba drives and cygwin

updatedb and locate are parts of the findutils package, which exists since a long time on most unix / linux flavors.

Something I never get worked is to search for files on network drives with Cygwin, I always got :

find: /cygdrive/y/Favorites/Links/del changed during execution of find (old inode number -474324984, new inode number -44545478
4, filesystem type is system) [ref 1114]
find: /cygdrive/y/Favorites/Links/del changed during execution of find (old inode number -513303800, new inode number -47432498
4, filesystem type is system) [ref 1114]

which is because the file system is mounted and unmounted on demand and get new inodes.

So I wrote a hack in /usr/bin/updatedb


# $find $NETPATHS $FINDOPTIONS …
# lschnei2
(
for d in c: y:
do
cd $d
/cygdrive/c/OS/system32/attrib /s
done
) |
sed ‘s,………..\(.\):,/cygdrive/\1,;y,\\,/,’ |
tr ‘\r’ ‘\0′ | tr -d ‘\n’

Then I can search efficiently


$ time updatedb

real 0m18.273s
user 0m3.806s
sys 0m7.332s

$ time locate hosts
/cygdrive/C/B28359_01/rac.111/b28252/img/cluster_hosts_table.gif
/cygdrive/C/B28359_01/rac.111/b28252/img_text/cluster_hosts_table.htm
/cygdrive/C/OS/system32/drivers/etc/hosts
/cygdrive/C/OS/system32/drivers/etc/lmhosts.sam
/cygdrive/Y/.ssh/known_hosts

real 0m0.261s
user 0m0.265s
sys 0m0.015s

Cycling

How to detect cycling records in 9i, remember CONNECT BY NOCYCLE does not exist in 9i


SQL> create table lsc_t as
  2  select 1 parent, 2 child from dual
  3  union all select 2,3 from dual
  4  union all select 4,5 from dual
  5  union all select 5,6 from dual
  6  union all select 6,4 from dual;

Table created.

SQL> select parent,child
  2  from lsc_t
  3  where level=50
  4  connect by parent=prior child
  5  and level<=50
  6  and prior dbms_random.value != dbms_random.value;

    PARENT      CHILD
---------- ----------
         5          6
         6          4
         4          5

Ok, it is a bit abusing the connect by operator, but it is for hierarchic purpose :mrgreen:

Oracle Streams

If you have a datawarehouse and the data are getting to big for a full duplicate or tablespace transport, if you want to experience more about Streams or simply if you are in San Francisco and wants some distraction on Thursday after at 1pm, do not miss Chen session Oracle Streams – Live Demo

Oracle OpenWorld Unconference

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
Length: 1,613,366,248 (1.5G) [application/zip]

100%[==================================>] 1,613,366,248 891.73K/s

10:47:31 (879.89 KB/s) – `p6890831_111070_Linux-x86-64.zip’ saved [1613366248/1613366248]

$ unzip p6890831_111070_Linux-x86-64.zip
$ cd Disk1
$ ./runInstaller
Starting Oracle Universal Installer…
Preparing to launch Oracle Universal Installer from
Oracle Universal Installer, Version 11.1.0.7.0 Production
Copyright (C) 1999, 2008, Oracle. All rights reserved.

$ su -
root’s password:
# /u00/app/oracle/product/11.1/db_1/root.sh
Running Oracle 11g root.sh script…
# exit
$ sqlplus / as sysdba
SQL*Plus: Release 11.1.0.7.0 – Production on Sat Sep 20 11:10:35 2008

Copyright (c) 1982, 2008, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup quiet migrate
ORACLE instance started.
Database mounted.
Database opened.
SQL> set time on
11:42:21 SQL> sho parameter sga_target

NAME TYPE VALUE
———————————— ———– ——————————
sga_target big integer 200M
11:42:25 SQL> alter system set sga_target=300m scope=spfile;

System altered.

11:42:28 SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
11:42:43 SQL> startup quiet migrate
ORACLE instance started.
Database mounted.
Database opened.
11:43:01 SQL> @?/rdbms/admin/catupgrd

11:54:03 SQL> Rem END catupgrd.sql
11:54:03 SQL> startup
ORACLE instance started.

Total System Global Area 313159680 bytes
Fixed Size 2159272 bytes
Variable Size 226495832 bytes
Database Buffers 79691776 bytes
Redo Buffers 4812800 bytes
Database mounted.
Database opened.
11:56:28 SQL> select comp_name,status,version from dba_registry;

COMP_NAME STATUS VERSION
———————————– ——– ———-
Oracle Ultra Search VALID 11.1.0.7.0
Oracle XML Database VALID 11.1.0.7.0
Oracle Text VALID 11.1.0.7.0
Oracle Expression Filter VALID 11.1.0.7.0
Oracle Rules Manager VALID 11.1.0.7.0
Oracle Workspace Manager VALID 11.1.0.7.0
Oracle Database Catalog Views VALID 11.1.0.7.0
Oracle Database Packages and Types VALID 11.1.0.7.0
JServer JAVA Virtual Machine VALID 11.1.0.7.0
Oracle XDK VALID 11.1.0.7.0
Oracle Database Java Packages VALID 11.1.0.7.0

My first try with 200Mb sga target did not succeeded. But with 300Mb sga target it worked fine.

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 columns.


/* Formatted on 2008/09/19 10:00 
(Formatter Plus v4.8.8) */
SELECT dummy
  FROM DUAL;

Well, Toad decided to put DUAL in uppercase. Anyway, this is no authority, define your own guideline and stick to it. If you use TOAD a lot, it is maybe OK to use lowercase for non-keywords.

The column dummy is actually the uppercase “DUMMY” column of “DUAL”. Putting double quotes around a column of table makes it case sensitive, so “DUMMY” is not “dummy”.

SQL> select "dummy" from "dual";
select "dummy" from "dual"
                    *
Error at line 1
ORA-00942: table or view does not exist

SQL> SELECT "DUMMY" FROM "DUAL";
D
-
X

OK, something very bad about Toad formatter is (at least the version I am using) that it considers unquoted password to be case insensitive. Which is wrong. The password, in 11g, is case sensitive, even when not in double quotes. Therefore, be sure to put the right case when creating scripts and do not format your CREATE USER statements with Toad or use double quotes for disambiguation!


SQL> ALTER USER scott IDENTIFIED BY BIG;
User altered.
SQL> connect scott/BIG
Connected.
SQL> /* Formatted on 2008/09/19 10:17 (Formatter Plus v4.8.8) */
SQL> ALTER USER scott IDENTIFIED BY big;
SQL> connect scott/BIG
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.

Hey, we have a journeyman !

Congrats to Denes Kubicek for being the first journeyman on OTN.

OTN stars Justin and Nicolas (18954 and 15321 posts) do not get this title, as we Oracle Ace are sticked to our Ace logos. OTN upgraded the forums recently, many posts about this :

  • OTN Forums – Change the look and feel yourself
  • OTN Forums Update – Take Two
  • New OTN Forums – not great yet
  • OTN forums get a new look
  • OTN Forums Upgrade
  • Forums.oracle.comUpgrade: New Rewards System/Known Issues
  • In the last link, Justin explains the new ranking system. Aces are aces, and everyone else but Denes are newbies ;)

    Ow, I forget, they got smileys too

    Happy :) :)
    Sad :( :(
    Grin :D :D
    Love :x :x
    Mischief ;\ ;\
    Cool B-) B-)
    Devil ]:) ]:)
    Silly :p :p
    Angry X-( X-(
    Laugh :^0 :^0
    Wink ;) ;)
    Blush :8} :8}
    Cry :_| :_|
    Confused ?:| ?:|
    Shocked :0 :0
    Plain :| :|

    puzzled by Toad

    one colleague just showed me how to insert duplicate rows in a table that has a primary key.

    
    create table t(x number primary key);[F5]
    Table created.
    insert into t values (1);[F5]
    1 row created.
    insert into t values (1);[F5]
    1 row created.
    

    Hey, what happened? It took me quite a while to figure out which feature of Toad was “allowing” this.

    Let’s look at Toad.ini (or View – ToadOptions – Transaction)

    
    [SETTINGS]
    ALWAYSROLLBACK=1
    THREADQUERIES=1
    

    Which means, Toad is starting a separate session each time a query is run (by F5 for instance) and at the end of this child session, it rollbacks. This is not the default, but this is pure magic!

    Let’s prove it :

    
    select count(*) from t where x=1;[F5]
    
      COUNT(*)
    ----------
             0
    
    1 row selected.
    

    :mrgreen: