Category Archives: database administration

Home »  database administration

Installing PostgreSQL on Lion

On January 30, 2012, Posted by , In database administration,systems administration, By , With No Comments

Lion comes with a _postgres system account and PostgreSQL 9.0.4 client tools already installed. Lion Server includes PostgreSQL server, which replaces the MySQL server distributed with earlier versions of OS X. If you want to add PostgreSQL server to a regular Lion installation the easiest way to do this is…

Converting from MyISAM to InnoDB

On November 6, 2011, Posted by , In database administration, With No Comments

To change the default table engine add the following to my.cnf: default-storage-engine=InnoDB To check the engines being used: SHOW TABLES STATUS Each existing table has to be converted explicitly: user=’mydba’ db=’mydb’ mysql -u ${user} -p -e "SHOW TABLES IN ${db};" \ | tail -n +2 | xargs -I ‘{}’ echo…

Using Ruby with Postgres on Ubuntu 10.04 LTS

Installation of PostgreSQL on Ubuntu is straightforward, see http://library.linode.com/databases/postgresql/ubuntu-10.04-lucid To create a simple database with its own user use the command line: appenv=myapptst psql <<EOF create user ${appenv} createdb password ‘*****’; EOF createdb –username=postgres –owner ${appenv} ${appenv} To access the database from Rails use pg gem install pg Entries in…

pgLoader

On August 28, 2010, Posted by , In database administration, By , , With No Comments

One of the weaknesses of PostgreSQL compared to Oracle is the lack of a strong bulk loader utility like sqlldr. Natively Postgres offers only the COPY command, which does not include suppport for a bad file and reject log. EnterpriseDB have acknowledged the need for a better tool but it…

[42000] [Microsoft][ODBC Microsoft Access Driver] Record(s) cannot be read; no read permission on … (-1907)

On August 7, 2010, Posted by , In database administration, With No Comments

I ran into this error extracting data using ODBC from an Access database. All access control within Access had been turned off or removed, but still the problem persisted. I tried using ‘repair’ utilities but to no avail. Eventually I split the database, copying all tables and indexes into a…

Installing Oracle XE on Ubuntu

On January 16, 2010, Posted by , In database administration,systems administration, By ,, , With No Comments

Oracle XE is available for 32-bit architecture (i386) Linux only, although it may be possible to force installation on a 64-bit host. It will run in 512KB RAM but requires a 1GB swap space. Add: deb http://oss.oracle.com/debian unstable main non-free to /etc/apt/sources.list and then as root: wget http://oss.oracle.com/el4/RPM-GPG-KEY-oracle -O- |…

Oracle tablespace utilization

On October 10, 2009, Posted by , In database administration, By , With No Comments

I stole this from VS Babu SELECT a.TABLESPACE_NAME, a.BYTES bytes_used, b.BYTES bytes_free, b.largest, round( ( ( a.BYTES – b.BYTES ) / a.BYTES ) * 100, 2 ) percent_used FROM ( SELECT TABLESPACE_NAME, sum( BYTES ) BYTES FROM dba_data_files GROUP BY TABLESPACE_NAME ) a, ( SELECT TABLESPACE_NAME, sum( BYTES ) BYTES,…

Adding a surrogate key to an Oracle table

On July 4, 2009, Posted by , In database administration,software development, By , With No Comments

This works for me for a mid-sized table. ALTER TABLE foo ADD ( foo_id integer ) / CREATE SEQUENCE foo_id_seq / CREATE OR REPLACE TRIGGER before_update_foo BEFORE INSERT OR UPDATE ON FOO REFERENCING OLD AS old NEW AS new FOR EACH ROW DECLARE l_foo_id foo.foo_id%TYPE; BEGIN IF :new.foo_id IS NULL…

Changing Oracle’s SGA size

On April 11, 2009, Posted by , In database administration, By , With No Comments

To see the current values use ‘show parameter sga_target’. To alter: alter system set sga_max_size = 300M scope=spfile; alter system set sga_target = 300M scope=spfile; Restart database to effectuate change.

Installing JBoss with Oracle XE on Windows

On November 3, 2008, Posted by , In database administration,middleware,software development, By , With No Comments

Both Tomcat and Oracle XML DB use port 8080 by default. Before installing JBoss I first changed the default port for XML DB as suggested here. call dbms_xdb.cfg_update(updateXML( dbms_xdb.cfg_get() , ‘/xdbconfig/sysconfig/protocolconfig/httpconfig/http-port/text()’ , 8081)) / I restarted the Oracle database and verified port 8081 was in use and not 8080.