Wednesday, December 06, 2006

Firefox 2.0 cool tip for recovering closed tabs

Firefox 2.0 has a nice command to recover a closed tab. In case you have accidentally closed a tab , just press ctrl+ shift+ t , and it will get back the tab. Very useful for people that have a lot of open tabs at the time (just like me).

Postgres 8.2 has been released

Postgres 8.2 is now on the streets. New features have been added:

-- Higher performance (+20% on OLTP tests)
-- Improved Warm standby databases
-- Online index builds
-- SQL2003 aggregates
-- Improved TSearch2 with Generalized Inverted Indexes
-- Support for DTrace probes
-- Advisory Locks
-- New ISN/ISBN and pgCrypto modules
-- Selective pg_dump options

Check this link for more info:

Monday, October 23, 2006

2 useful linux commands

I read or heard somewhere about this two linux commands: apropos and ctrl + r. And I found it very userful.
apropos : apropos searches for a keyword in some internal database(s) and displays a list of commands related to the searched keyword. This is helpful when you don't know the exact name of a command or have no clue of the command to perform certain task. You can also google for it, but you have this additional choice.

[root@devel2 ~]# apropos mail

aliases (5) - aliases file for sendmail
clamav-milter (8) - milter compatible mail scanner
fetchmail (1) - fetch mail from a POP, IMAP, ETRN, or ODMR-capable server
formail (1) - mail (re)formatter
hesiod_free_postoffice [hesiod_getmailhost] (3) - Hesiod functions for retrieving user to postoffice mappings
hesiod_getmailhost (3) - Hesiod functions for retrieving user to postoffice mappings
htnotify (1) - sends email notifications about out-dated web pages discovered by htmerge
logrotate (8) - rotates, compresses, and mails system logs
mail (1) - send and receive mail
mailaddr (7) - mail addressing description
mailcap (4) - metamail capabilities file
mailq (1) - print the mail queue
mailstats (8) - display mail statistics
makemap (8) - create database maps for sendmail
mutt (1) - The Mutt Mail User Agent
muttrc (5) - Configuration file for the Mutt Mail User Agent
newaliases (1) - rebuild the data base for the mail aliases

Ctrl +r : When pressing this in a shell you will filter you history of typed commands by the letters you type after pressing the keys.

(reverse-i-search)`apr': man apropos

I typed here "apr" after pressing ctrl+r and it gave me "man apropos".

If you type Ctrl+r again it will move to the next match for the typed letter(s).

Wednesday, August 23, 2006

Windows XP on CentOS with VMware

I installed Windows XP SP2 as a guest OS on a CentOS using VMware server the virtual machine software.

If you wonder why the windows have that OSX look , I'm using a gnome theme called "Glossy P".
The installation is pretty straightforward but it runs a little slow as expected, i was just trying to use the Net2phone dialer which runs only on windows (there are no plans to make a port for linux) and it worked fine, to get sound and microphone support with this software you need to add a sound adapter (the hardware must first work with CentOS) to the virtual machine in the settings at the VMware server console.

I used this How-to for ubuntu, most of the steps are the same .

Wednesday, July 26, 2006

Range partitioning in MySQL 5.1

There are 5 types of table partitions in mysql: range, list, hash, key and composite (or subpartitioning), i think the most commonly used type in the database world (at least in oracle) could be range partition, in this way a expression is evaluated and according to it different partitions are created with ranges of rows. The most common method to partition by range is using dates (by years,months,days) to group large amounts of rows from a table, mysql does not support partition by date type directly (oracle does), instead a function like year() or month() should be used to get an integer value.

Playing with this partition type, first i tried to create a table with a primary key on the id column and the partition column using insert_date, but MySQL generates an error if the column insert_date is not part of the primary key:

mysql> CREATE TABLE range_partition_tab (
-> id numeric primary key,
-> data varchar(20),
-> insert_date date
-> )
-> PARTITION BY RANGE ( year (insert_date) ) (
-> );
ERROR 1482 (HY000): A PRIMARY KEY need to include all fields in the partition function

But after i removed the primary key clause , it created the table.

mysql> CREATE TABLE range_partition_tab (
-> id numeric ,
-> data varchar(20),
-> insert_date date
-> )
-> PARTITION BY RANGE ( year (insert_date) ) (
-> );
Query OK, 0 rows affected (0.08 sec)

I tried to add a new partition:

mysql> alter table range_partition_tab add partition (partition p4 values less than (2007));
ERROR 1472 (HY000): VALUES LESS THAN value must be strictly increasing for each partition

But p3 holds all the rows that match range for p4, then since none value is greater than MAXVALUE a new partition can't be added over that value.

New partitions can be added for example by splitting the low end partition (p1) with the REORGANIZE PARTITION clause:

mysql> alter table range_partition_tab reorganize partition p1 into ( partition s0 values less than (2004), partition s1 values less than (2005) );
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0

The new partition must start from the value of p1 wich is 2005 to cover the same range, otherwise MySQL generates an error:

mysql> alter table range_partition_tab reorganize partition P3 into ( partition s0 values less than (2003), partition s1 values less than (2004) );
ERROR 1499 (HY000): Reorganize of range partitions cannot change total ranges except for last partition where it can extend the range

to drop a partition:

mysql> alter table range_partition_tab drop partition p3;
Query OK, 0 rows affected (0.15 sec)
Records: 0 Duplicates: 0 Warnings: 0

Tuesday, June 06, 2006

Table partitioning in MySQL 5.1

I'm very excited that MySQL implements this feature in version 5.1.x, all the new features are bridging the gap between open source and propietary RDMS like Oracle, this feature particularly makes MySQL suitable for Data warehouse systems, not just OLTP systems as it's well known.

There are a lot of benefits in the use of table partitioning like:

- Eases the administration of the tables
- Reduces time taken in large amount of DML
- Improves the response of some queries

Table partitioning allows the DBA to define how the table and the data cointained could be stored phisically, while remaining the logical structure the same. For example a table with data of 5 years (theorically a large table) could be splitted (depending on the needs) in 5 partitions, reducing the time taken by some queries that may fit into a particular year, easing the administration of the table when the data from a year needs to be dropped or moved, enhancing the response for bulk DMLs in different years, etc.

There are some features not available yet like partitioning by range on a date column ( and one that i reported, i couldn't find how to get data from a specific partition rather than knowing wich partition a query accesses (, but i think is just a matter of time.

Sunday, June 04, 2006

Memory requirement installing oracle xe on centos

I was trying to install oracle xe on a centos 4.2 box ( 256 RIMM memory, pentium 4 1.70 GHz), but i got stucked with a memory requirement error :

[root@localhost ~]# rpm -Uvh /root/oracle-xe-
Preparing… ########################################### [100%]
Oracle Database 10g Express Edition requires a minimum of 256 MB of physical
memory (RAM). This system has 249 MB of RAM and does not meet minimum
error: %pre(oracle-xe- scriptlet failed, exit status 1
error: install: %pre scriptlet failed (2), skipping oracle-xe-

I don't know why linux reports 249Mb and where are the other 7 Mb since there is a AGP video card of 64Mb and i didn't have that problem with the windows version, but after playing around with the rpm 'install-options' i found the ones that let me pass that requirement:

[root@localhost ~]# rpm -Uvh --force --nopre /root/oracle-xe-
Preparing... ########################################### [100%]
1:oracle-xe ########################################### [100%]
Executing Post-install steps..........
You must run '/etc/init.d/oracle-xe configure' as root user to
configure the database.

Saturday, May 13, 2006

Alternative to xmms for playing mp3 in linux

Xmms newer versions doesn't support mp3 files because of licensing issues, i read something about Zinf and give it a try, and it works great playing mp3, i'm using Centos 4 i just needed to install the libid3 package (id3lib-3.8.3-7.2.el4.rf.i386.rpm) and it was up and running:

[root@localhost ~]# rpm -Uvh zinf-2.2.5-1.i386.rpm
error: Failed dependencies: is needed by zinf-2.2.5-1.i386

[root@localhost ~]# rpm -Uvh zinf-2.2.5-1.i386.rpm
Preparing... ########################################### [100%]
1:zinf ########################################### [100%]

You can get zinf from :


After using zinf for a while a realized that it crash a lot, i'm back to xmms its a lot more stable, just downloading the xmms-mp3 package it can play mp3s.

Saturday, April 29, 2006

Tablespaces in Oracle part 2

This second part will be focused on basic commands to manage ( create, modify and drop) a tablespace .

How to create a tablespace

To create a tablespace we must use the CREATE TABLESPACE statement:

SQL> create tablespace tbs_data;

Tablespace created.

This creates a locally managed tablespace with system allocated extent sizes, the db_create_file_dest parameter must be set before we can use this command without any other clauses , and oracle will create a datafile in that destination with a name like this O1_MF_TBS_DATA_257C3400_.DBF and a size of 100MB , this files are called Oracle Managed Files (OMF).

Permanent tablespace

SQL> create tablespace tbs_data
2 datafile 'D:\oraclexe\oradata\XE\tbs_data.dbf' size 10m;

Tablespace created.

Temporary tablespace

SQL> create temporary tablespace tbs_temp
2 tempfile 'D:\oraclexe\oradata\XE\tbs_temp.dbf' size 10m;

Tablespace created.

Undo tablespace

SQL> create undo tablespace tbs_undo
2 datafile 'D:\oraclexe\oradata\XE\tbs_undo.dbf' size 10m;

Tablespace created.

Locally managed

SQL> create tablespace tbs_data_local
2 datafile 'D:\oraclexe\oradata\XE\tbs_data_local.dbf' size 10m
3 extent management local;

Tablespace created.

'extent management local' is the default if omitted.

Dictionary managed

SQL> create tablespace tbs_data_dictionary
2 datafile 'D:\oraclexe\oradata\XE\tbs_data_dictionary.dbf' size 10m
3 extent management dictionary;
create tablespace tbs_data_dictionary
ERROR at line 1:
ORA-12913: Cannot create dictionary managed tablespace

Oracle dont let you create dictionary managed tablespaces if your system tablespace is locally managed, dictionary managed tablespaces are more expensive than locally managed tablespaces and they may be totally deprecated in the future.

How to modify a tablespace

Take offline/online

SQL> alter tablespace tbs_data_l offline;

Tablespace altered.

Adding space

SQL> alter tablespace tbs_data
2 add datafile 'D:\oraclexe\oradata\XE\tbs_data02.dbf' size 10m ;

Tablespace altered.

Rename datafile

The tablespace must be taken offiline or the database should not be open before doing this.

SQL> alter database rename file 'D:\ORACLEXE\ORADATA\XE\TBS_DATA_LOCAL.DBF' to

Database altered.

Rename tablespace

SQL> alter tablespace tbs_data_local rename to tbs_data_l;

Tablespace altered.

How to delete a tablespace

If the tablespace is empty (has no objects)

SQL> drop tablespace tbs_data;

Tablespace dropped.

If is not empty

SQL> drop tablespace tbs_data including contents;

Tablespace dropped.

If we want to delete the datafile from the operating system

SQL> drop tablespace tbs_data including contents and datafiles;

Tablespace dropped.

If there are constraints

SQL> drop tablespace users including contents and datafiles;
drop tablespace users including contents and datafiles
ERROR at line 1:
ORA-02449: unique/primary keys in table referenced by foreign keys

SQL> drop tablespace users including contents and datafiles cascade constraints;

Tablespace dropped.

For more information, this is the best oracle architecture book:
Expert Oracle Database Architecture: 9i and 10g Programming Techniques and Solutions

Friday, March 10, 2006

Tablespaces in Oracle part 1

What is a tablespace

Tablespaces are the most bigger logical storage structure in oracle ( Tablespace -> segment -> extent -> block) . Tablespaces gather segments (you can think of it as objects like tables, indexes, etc), when we create an object like a table , if we dont specify a tablespace in the CREATE TABLE command it is assigned to our user default tablespace ( in most cases the SYSTEM tablespace , which is recommended by oracle just for dictionary objects). A tablespace is composed of datafiles wich are part of the physical structure of the oracle database (datafiles, control files, redo log files) they are stored in the OS containing the data of the database.

Relationship between Database - Tablespace - Datafile - Object:

The graph shows us the relationship between this structures, a database is composed logically of tablespaces , each tablespace is composed physically of datafiles and each datafile stores data from the objects . The size of the database is total size of the tablespaces and the total size of the tablespaces is the total size all of his datafiles. You can add or remove space to a tablespace in two ways , adding datafiles to the tablespace or resizing the actual datafiles.

Why we need tablespaces

Tablespaces ease the management of the database, they are useful to separate objects and apply different actions to each of them, we can take one of the tablespaces offline, make it read only or backup it affecting just part of the database

Types of tablespaces

Permanent tablespaces: where usual objects are stored ( data tablespaces)
Temporary tablespaces: used for temporary segments (in sort operations)
Undo tablespaces: for rollback segments.

Space management

There are basically two states when oracle needs to use some space (extents) in a tablespace: used (allocated) and available, and Oracle manages that space in two ways :

- Dictionary Managed Tablespaces
- Locally Managed Tablespaces

Dictionary Managed Tablespaces

The information about used and available extents are stored in the data dictionary. This type of management is not the most optimal because of the SQL overhead in the database when performing a lot of operations and also it can't be done simultaneously they must be serialized increasing wait times. Since 9i, Oracle is deprecating this method and it will not let you create dictionary managed tablespaces if you have your system tablespace locally managed.

Locally Managed Tablespaces

In this way Oracle uses bitmaps stored in the datafile itself to know when an extent is used ( with 1) and available or free ( with 0) , this approach eases the space management and remove the overhead in the database.

For more information, this is the best oracle architecture book:
Expert Oracle Database Architecture: 9i and 10g Programming Techniques and Solutions

Friday, February 10, 2006

Web 2.0

Web 2.0 is a buzzword (some people think is a silly term) that references to all the new rich user experience web based applications and the technologies around them , maybe you have seem some of them like,,,, this blog itself , and the list goes on and on. Some of this tools allows non tech users to interact, share and create content that otherwise couldn't be possible.

In a few years desktop applications like spredsheets and word processors will be moved totally to the web, there are initiatives like wikicalc, numsum and writely, maybe they will not have all the same functionality of desktop apps but most of the people just use about 10% (or less) of Excel.

I read a very interesting paper about web 2.0 / web office, here is the link:
Web Office , worth a read.

Friday, January 27, 2006

Creating an Oracle (XE) database manually on XP

There are two ways for creating an Oracle database, one is using Oracle Database Configuration Assistant and the other is manually, I'll provide a small step by step guide for the latter on windows XP.

This are the main steps:

1. Create the directory structure
2. Declare an oracle SID name.
3. Create a windows service and password file
4. Create the init.ora file
5. Start the instance in nomount mode
6. Use the Create database command
7. Create Data Dictionary

1. Create the directory structure

Under the admin
directory (C:\oraclexe\app\oracle\admin\) we have to create the structure for the new database (this is called OFA Oracle Flexible Arquitecture), the directory is called oraXE which will be the name of the database, and the directories adump, bdump, cdump, dpdump, pfile, udump are created in it:

2. Declare an oracle SID name.

Using the this command we declare the variable for oracle SID. Since could be more than one oracle instance running, oracle uses the SID to difference them.


3. Create a windows service and password file

Every instance on windows requires a windows service which can be created using oradim tool, the created service can be checked in the services list: type services.msc in the console or a link can be found in the control panel -> admin tools -> Services. Also a password file is created under database directory (ORACLE_HOME\database) with the SID embedded in the name (PWDoraXE.ora), this password file is used to authenticate the DBA before starting an instance.

oradim -new -sid %ORACLE_SID% -intpwd passwordhere -startmode M

Important : Remember to stop the default service for the "XE" database which is called "OracleServiceXE" and start the service just created "OracleServiceoraXE" for our new database in the services console.

4. Create the init.ora file
Open a text editor, add the lines below and save it as
initoraXE.ora in C:\oraclexe\app\oracle\admin\oraXE\pfile\. This are just some basic initial parameters, the ones that are not specified are taken by default.

control_files = (C:\oraclexe\oradata\oraXE\control01.ctl,
undo_management = auto
db_name = oraXE
db_block_size = 8192

control_files : Here we define where will be our control files, those are used for store management information of the database like location of the datafiles, timestamp of the creation of the database,etc. In this case there are three files copies, its recommended to store each copy on different physical disk which is called multiplexing.

undo_management: this parameter is used for choosing between automatic undo management (AUTO) or manually undo management (MANUAL, this is the default) in the latter is needed to create , size and monitor closely undo (rollback) segments, it's recommended to be set to AUTO.

db_name : The database name must be the same used in the create statement.

db_block_size : The default or standard database block size, this is the mininal logical unit of storage in oracle this should be a multiple of the operating system block size.

5. Start the instance in nomount mode

Before you try to connect to start the instance check that you have the right ORACLE_SID:


You can now authenticate through the operating system (no login or password) to start up the database, like this:

sqlplus /nolog

sql>connect / as sysdba

sql>startup nomount pfile=C:\oraclexe\app\oracle\admin\oraXE\pfile\initoraXE.ora

ORACLE instance started.

Total System Global Area 113246208 bytes
Fixed Size 1246556 bytes
Variable Size 58722980 bytes
Database Buffers 50331648 bytes
Redo Buffers 2945024 bytes

sqlplus /nolog : start the sqlplus client , the "/nolog" is to not be prompted for username/password.

connect / as sysdba : authenticate through the operating system with sysdba privileges. If you don't have the right ORACLE_SID ("oraXE") you may get an "ORA-12560: TNS:protocol adapter error".

startup nomount... : we must startup the db in nomount mode because at this point there a no database (physical files) to mount. The pfile that created in step 4 (C:\oraclexe\app\oracle\admin\oraXE\pfile\initoraXE.ora) must be specified otherwise it will look for the default location. If you get a ORA-01081 error is because probably the "XE" database is already started and you can't have two databases mounted at the same time, so first shutdown the running database using the shutdown command.

6. Use the Create database command

With this command we create the database:

sql> create database oraXE
logfile group 1 ('C:\oraclexe\oradata\oraXE\redo1.log') size 10M,
group 2 ('C:\oraclexe\oradata\oraXE\redo2.log') size 10M,
group 3 ('C:\oraclexe\oradata\oraXE\redo3.log') size 10M
character set WE8ISO8859P1
national character set utf8
datafile 'C:\oraclexe\oradata\oraXE\system.dbf'
size 50M
autoextend on
next 10M maxsize unlimited
extent management local
sysaux datafile 'C:\oraclexe\oradata\oraXE\sysaux.dbf'
size 10M
autoextend on
next 10M
maxsize unlimited
undo tablespace undo
datafile 'C:\oraclexe\oradata\oraXE\undo.dbf'
size 10M
autoextend on
default temporary tablespace temp
tempfile 'C:\oraclexe\oradata\oraXE\temp.dbf'
size 10M
autoextend on;

Database created.

7. Create Data Dictionary

Finally, the scripts for creating the data dictionary are under ORACLE_HOME\rdbms\admin.


sql> @?\rdbms\admin\catalog.sql
sql> @?\rdbms\admin\catproc.sql

? this will be replaced by the value of $ORACLE_HOME
catalog.sql creates the data dictionary views.
catproc.sql creates the dictionary items necessary for PL/SQL functionality.

Wednesday, January 18, 2006

Firefox extension: IE Tab

For those of you who wants to open sites using internet explorer embedded in a firefox tab, this is the rigth extension. This is a relative of the IE view extension but the difference is that IE view extension opens new internet explorer windows for each selected site.

You have to be on windows to use it :S ...

Here is the link

Monday, January 02, 2006

Download Oracle XE with wget

I needed to download Oracle XE using wget mainly because of the limit rate option, but also because i can run it in the background and files can be resumed at any time. The limit rate option allows me to not use almost all the available Internet bandwidth, so i tried to download it but i noticed that you can't get the file directly from (this link is for windows platforms) without beeing authenticated, you must have a cookie if you already signed in, otherwise you will be forwarded to, where there is a form asking for username and password that post to sending this and other additional variables for authentication and finally redirection to the url that we came from (as you can se below in the redirectUrl variable) that in this case was the file to download.

If we look at the talk between browser and server when sending the form we can see the POST variables :

POST /jsp/reg/loginHandler.jsp HTTP/1.1
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US;
rv:1.7.12) Gecko/20050915 Firefox/1.0.7
Accept: text/xml,application/xml,application/xhtml+xml,
Accept-Language: en-us,en;q=0.5
Accept-Encoding: gzip,deflate
Accept-Charset: ISO-8859-1,utf-8;q=0.7,*;q=0.7
Keep-Alive: 300
Connection: keep-alive
OracleXE.exeCookie: JSESSIONID=8d920d0fce90b20ff91eaec4cc2aa
OXgnkrDqRfMmlfDpQSxn6jAmljGr5XDqQLvpAe_;s_cc=true; s_sq=orac
Content-Type: application/x-www-form-urlencoded
Content-Length: 324


with that POST variables and URL i compose the following
wget command:

wget -v -c --limit-rate=3K --no-check-certificate --post-data="remoteIp=63.XXX.XX.XX&globalId=&redirectUrl=" -o oraxe.log &

-c , is used when you are resuming the download
--limit-rate=3K, limit to 3 Kbytes/sec
--no-check-certificate, it doesnt work verifying the server
-o oraxe.log, for output
& to run on background

At the end of the POST variables you can find "...username=camafla&password=xxxxx..." , you have to change
that with your personal information (also remoteIp=63.XXX.XX.XX).

The redirectUrl= variable may change but you just have to check with a browser whats the actual url.