process parentalcontrols taking up a lot of memory on Mac

Since a few days, I noticed my iMac was running slower. By checking the process manager, I noticed the parentalcontrol process was taking up half a GB of Memory.

A couple of forum posts mentioned the following procedure for fixing this issue:
- desactivate parental controls for all users.
- as root user : delete the directory /Library/Application/support/Apple/parentalcontrols
- reboot.

... and the problem was fixed.

truncate vs. delete

Everybody knows: TRUNCATE is fast, because it's a DDL, and it directly resets the highwater mark, and writes nothing in the rollback segments. 

So TRUNCATE is much faster than DELETE FROM.

I just tried to measure time difference on my laptop, using SET TIMING ON, manipulating data in a table containing 200'000 records.

13:39:48 SQL> create table my_table
 (id number primary key,
  text varchar2(20)
  ); 
   Table created. 
   Elapsed: 00:00:00.03 
13:40:48 SQL> begin 
13:41:01 2 for i in 1 .. 200000 loop 
13:41:01 3 insert into my_table (id, text) values(i, 'text' || i); 
13:41:01 4 end loop; 
13:41:01 5 end; 
13:41:01 6 / 
   PL/SQL procedure successfully completed. 
   Elapsed: 00:00:28.48 
13:41:29 SQL> commit; 
   Commit complete. 
   Elapsed: 00:00:00.03 
13:41:41 SQL> truncate table my_table; 
   Table truncated. 
   Elapsed: 00:00:00.48 
13:41:58 SQL> begin 
13:42:14 2 for i in 1 .. 200000 loop 
13:42:14 3 insert into my_table (id, text) values(i, 'text' || i); 
13:42:14 4 end loop; 
13:42:14 5 end; 
13:42:14 6 / 
   PL/SQL procedure successfully completed. 
   Elapsed: 00:00:21.41 
13:42:37 SQL> commit; 
   Commit complete. 
   Elapsed: 00:00:00.01 
13:42:40 SQL> delete from my_table; 
   200000 rows deleted. 
   Elapsed: 00:00:08.63 
13:42:55 SQL> commit; 

 Bottom line : TRUNCATE is immediate, DELETE runs during 8 seconds.

Google account on Mac

Recently, I was confronted by a little annoyance while using my google account. I just enabled 2-factors authentication, and a while after, I noticed that I couldn't sync ma iPad's and Mac's iCal app. I also couldn't create fresh accounts. I read this post ... and everything was solved. The point is that some app do not handle login to google correctly when 2-factors authentication is enabled. By configuring app-specific passwords in Google accounts, the problem is solved. I just hadn't realized this, because I use 2 google accounts (one is private, the other is for work), and right after enabling 2-factors authentication on the first account, I also configured app-specific paswords for Mac -iCal and iPhone just for that one account, leaving the other partially configured.

How to use SQL DECODE

This is a basic example on how to use the SQL function DECODE.
Let's consider a table where either one of the two fields FIELD1 and FIELD2 is not null. It never happens that both are null, or both not null.
The desired outcome is to display the value of FIELD1 when FIELD1 is not null, and the value of FIELD2 when it's not null. The DECODE function is the simplest way to achieve this. Let's see how to use it.

First, we create the table for the purpose of our demonstration, and populate it with a few records.

CREATE TABLE TWO_FIELDS (ID NUMBER, 
FIELD1 VARCHAR2(10), 
FIELD2 VARCHAR2(10));


INSERT INTO TWO_FIELDS VALUES (1, 'F1-1', NULL);
INSERT INTO TWO_FIELDS VALUES (2, NULL, 'F2-1');
COMMIT;

Let's now select some data according to our requirement, using the DECODE function:

SELECT ID
, FIELD1
, FIELD2
, DECODE(FIELD1, NULL, FIELD2, FIELD1) 
FROM TWO_FIELDS

...The DECODE function can be read like this: "if field1 is null, then display field2, otherwise display field1". Note that it is possible to enforce the rule with a constraint "either FIELD1 or FIELD2 is not null":

ALTER TABLE TWO_FIELDS 
ADD CONSTRAINT C1 
CHECK(FIELD1 IS NOT NULL OR FIELD2 IS NOT NULL);

... or (better) like this:

ALTER TABLE TWO_FIELDS 
ADD CONSTRAINT C2 
CHECK(
  (FIELD1 IS NOT NULL AND FIELD2 IS NULL)
  OR 
  (FIELD1 IS NULL AND FIELD2 IS NOT NULL)
)

Bonne pratiques: utiliser les types de données appropriés

Je vais essayer de résumer les principes et bonne techniques à mettre en oeuvre lors du développement d'applications utilisant une base de données Oracle.

En cherchant des informations en profondeur sur les fondements d'une bonne performance, je suis tombé sur un principe en apparence évident, mais qu'il est bon de rappeler: utiliser les types de données appropriées.

C'est en navigant dans les pages de Tom Kyte que je suis tombé sur cette entrée:

"Using the correct datatype seems like common sense, but virtually every system I look at does one of the following:
• Uses a string to store dates or times
• Uses a string to store numbers
• Uses VARCHAR2(4000) to store all strings.
• Uses CHAR(2000) to store all strings, wasting tons of space and forcing"
...
"When dates and numbers are stored in strings, or stored using inappropriate lengths, your system suffers:
• You lose the edit upon insertion to the database, verifying that your dates are actual dates and numbers are valid numbers.
• You lose performance.
• You potentially increase storage needs.
• You definitely decrease data integrity."
...

Que se passe-t-il dans la trace lorsqu'on ne respecte pas ce principe.

1. Conversion implicite:

Oracle Network Configuration

Recently, I had to help a developer, whou could not connect to his local Oracle instance using SQLDeveloper, but had no problem connecting using SQL*Plus.

I found nothing suspicious in the network configuration files  (listener.ora, tnsnames.ora, sqlnet.cfg).

After issuing netstat from a command line prompt (DOS), we noticed that the server was running on port 1522, instead of 1521 (as mentionned in the network configuration).

This did not appear at first: the XE - Instance had been installed on the machine in its own home (under C:\oraclexe) Oracle Client was also installed in a separated home (under C:\Oracle), and ORACLE_HOME and TNS_ADMIN were pointing to the home where the client had been installed.

U:\>set ora
ORACLE_HOME=C:\oracle\product\1120\client

U:\>set tns
TNS_ADMIN=C:\oracle\tns_admin

The tnslsnr was started from the bin directory where the XE instances had been installed : ( C:\oraclexe\app\oracle\product\11.2.0\server\BIN\).

One would expect the network configuration in use would be the one in the home from which tnslsrn was started, but the settings of the tns_admin takes precedence. This explains why the instances in the network were accessible when running tnsping from either home, but not the local instance: TNS_ADMIN points to the network config in the home under C:\Oracle, which does not know of the local XE - Instance.

When setting TNS_ADMIN to C:\oraclexe\app\oracle\product\11.2.0\server\network\admin, it is possible to access the local instance (but not the instances in the network):

$set tns_admin=C:\oraclexe\app\oracle\product\11.2.0\server\network\ADMIN
$tnsping yopdn1
...
Used parameter files:
C:\oraclexe\app\oracle\product\11.2.0\server\network\ADMIN\sqlnet.ora

TNS-03505: Failed to resolve name

$tnsping xe
...
Used parameter files:
C:\oraclexe\app\oracle\product\11.2.0\server\network\ADMIN\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = S72D19.mobi.mobicorp.ch)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XE)))

OK (70 msec)

Points to remember:
- TNS_ADMIN determines which network configuration will be used.
- listener.ora only matters for the server.
- tnsnames.ora only matters for the client software.
- In SQLNet.ora, the parameter NAMES.DEFAULT_DOMAIN detemines the access to the local instance.

When using multiple homes, the listener process should be started from the home where the instance is installed.

In order to access instances in the network, TNS_ADMIN should point to a home that "knows" about these instances.

How to find out which column are used in a referential integrity constraint

I had to generate a script for generating index on columns that used as foreign key.

Lets remind us that indexing foreing key columns is a good practice - see Tom Kyte's blog entry on this topic.

I knew that you can query the data dictionary to find out all the data in order to generate a script -  in particular the views USER_CONSTRAINTS and USER_CONS_COLUMNS.

SELECT UCC1.TABLE_NAME
, UCC1.COLUMN_NAME
, UCC1.CONSTRAINT_NAME
, UC.R_CONSTRAINT_NAME, UCC2.TABLE_NAME
FROM USER_CONSTRAINTS UC 
, USER_CONS_COLUMNS UCC1
, USER_CONS_COLUMNS UCC2
WHERE UC.CONSTRAINT_TYPE = 'R'
AND UCC1.CONSTRAINT_NAME = UC.CONSTRAINT_NAME
AND UCC2.CONSTRAINT_NAME = UC.R_CONSTRAINT_NAME