chmod -R 777 .

This is one of the thing I hate to see, recursively changing everything to 777 👿 If you want to give read access to all, then 644 is enough for files and 755 for directories. If you want to give execution permission too, you could give 755 to executable files. Also sometimes you have files […]

CTAS and NOT NULL

When you create a table as select (CTAS), you lose a lot of information like grants, partitioning, organization, referential integrity, check constraints. But the NOT NULL constraints remain. Sometimes … Let’s see when the not null constraints are not copied to the new table. Here is the test case : create table lsc_t1( c0 number […]

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

select from comma-separated list

This is asked over and over in the forums, but why not proposing an 11g solution here 😉 create table t(description varchar2(12) primary key, numbers varchar2(4000)); insert into t(description, numbers) values (‘PRIME’,’2,3,5,7′); insert into t(description, numbers) values (‘ODD’,’1,3,5,7,9′); commit; DESCRIPTION NUMBERS PRIME 2,3,5,7 ODD 1,3,5,7,9 Now I want to unpivot numbers in rows select description,(column_value).getnumberval() […]

to ftp or to sftp

Ftp is seen as an old-time unsecure protocol. Many shops nowadays have switched or are switching to sftp. I will try to point out some differences : Compatibility: none. the protocol is completly different. Multiple graphical clients however do support both mode. But the basic “ftp” client will not work with sftp. Ascii mode: only […]