Month: July 2009

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 with special permission like suid or guid bit (to run as the owner instead of to run as the run user), so it would be best to use relative (go+r) to make the file readable to group and others.

Therefore I prefer relative change. Still there is one thing I do not want, is making every file executable…

Ok, here it is
chmod -R a+rX .

note the big X 🙂 we change recursively all files and dirs to be readable, and we set the executable flag ONLY IF the file is executable for the owner !

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.

let’s describe the tables :SQL> desc lsc_t1
Name Null? Type
----------------------------- -------- --------------------
C0 NOT NULL NUMBER
C1 NUMBER
C2 NUMBER
C3 NUMBER
C4 NOT NULL NUMBER
C5 NOT NULL NUMBER

SQL> desc lsc_t2
Name Null? Type
----------------------------- -------- --------------------
C0 NUMBER
C1 NUMBER
C2 NUMBER
C3 NUMBER
C4 NOT NULL NUMBER
C5 NOT NULL NUMBER

The NOT NULL of c0 has been lost. C0 is the primary key, and the primary key is not transferred to the target table. We can see the non-deferrable validated not-null constraints c4 and c5.

Let’s compare all the constraints :select * from user_constraints where table_name in ('LSC_T1','LSC_T2');

LSC_T1

LSC_T2

C0

PRIMARY KEY

C1

DEFERRABLE

C2

ENABLED NOT VALIDATED

C3

DISABLED NOT VALIDATED

C4

DISABLED VALIDATED

ENABLED VALIDATED

C5

ENABLED VALIDATED

ENABLED VALIDATED

The deferrable and not-validated check constraints are lost. The regular ENABLE VALIDATE constraint is ok, but the DISABLE VALIDATE constraint is now enabled.

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 application allow you to search for a string in the middle of the name, but to avoid scanning too large amount of data, the application enforces the string to be at least 3 characters long. For example ABC.

In this case, instead of doing a full table scan of the table to retrieve only a few rows, we can use an hint to tell Oracle to use an index on the name:

Ok, it is quite easy to get rid of it. Either at DROP time with a DROP TABLE LSC_T PURGE or later with PURGE RECYCLEBIN. Most of the objects disappear from USER_OBJECTS when dropped actually. The recyclebin view is called : RECYCLEBIN.

purge recyclebin;

select object_name, subobject_name, created
from user_objects
where object_name like 'BIN$%';

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 in ftp. In sftp, it is always binary so there will be no conversion. Also no blocksize, recordlength or primary/secondary space for your OS/390 connections.

Interactive mode: similar. you enter your username and password, do cd, put and get. But to quit, by will not work in sftp 😉 Use quit or exit instead

Batch mode: different. Most probably you will end up setting a private/public key infrastructure for your ssh connection and use scp (secure copy). If you are using a ssh client like putty, it is possible to do something like pscp -l user -pw password server:file .

Security: sftp is secure, ftp is not.

Speed: ftp is fast, sftp is slow 🙁 !

Oh NOOOOOOO!!!!! What’s the point is bringing something new if it is slower !!!