Hiding Indexes

Posted by admin on Nov 25, 2013 in Database, Oracle

I just found out that its possible to hide indexes rather than making them unusable. alter session set ddl_lock_timeout = 10; alter index INDEX_START_DATE_TIME invisible; This is beneficial because the index does not have to be rebuilt it it is made visible again.


MySQL variables for 5.6

Posted by admin on Jul 23, 2013 in Database

After upgrading MySQL Cluster from 7.2 to 7.3 (5.6.11) performance was poor. My workload is not typical OLTP or OLAP but a mixture of both and some real-time updating. Also I have 2 MySQL instances running on different ports and the disk subsystem is quiet slow. The following parameters got the databases back to normal […]


replace into

Posted by admin on Apr 10, 2012 in Database

I had been using mysql’s replace into syntax until I read this article http://code.openark.org/blog/mysql/replace-into-think-twice which makes a lot of sense. I have mostly changed statements to use insert .. on duplicate key instead. For example insert into currentusage ( userid, anytimebytesuploaded, anytimebytesdownloaded, peakbytesuploaded, peakbytesdownloaded, offpeakbytesuploaded, offpeakbytesdownloaded, bytesexcluded) select userid, sum(case when timing = ‘ANYTIME’ then […]


Reset MySQL root password

Posted by admin on Jul 7, 2011 in Database, Unix

First things first. Log in as root and stop the mysql daemon. Now lets start up the mysql daemon and skip the grant tables which store the passwords. mysqld_safe –skip-grant-tables You should see mysqld start up successfully. If not, well you have bigger issues. Now you should be able to connect to mysql without a […]


Oracle group_concat

Posted by admin on Feb 17, 2011 in Business Intelligence, Database

Oracle has the MySQL equivalent of group_concat using the following procedure SELECT USER_ID, wmsys.wm_concat(SERVICE_PACK_NAME), FROM USERS, SERVICE_PACKS, group by USER_ID; returns user1 servicepack1,servicepack2,servicepack3 user2 servicepack3 etc



Posted by admin on May 12, 2010 in Database, Unix

Balance (http://www.inlab.de/balance.html) is a useful tool to route TCP to another server. Consider a server A that can not get to server C directly due to firewall rules but can get to Server B. Server B will act as a proxy. If I am routing oracle commands on port 1521, I would start it up […]


SOAP Service and Client Example

Posted by admin on May 6, 2010 in Database

Server: This test server will query a MySQL database and return tables with names matching the selection criteria. #!/usr/bin/perl #use SOAP::Lite +trace=>”all”; use SOAP::Transport::HTTP; my $daemon = SOAP::Transport::HTTP::CGI ->new (LocalPort => 8080) ->dispatch_to(‘Services’) ->handle; BEGIN { package Services; use vars qw(@ISA); @ISA = qw(Exporter SOAP::Server::Parameters); use SOAP::Lite; use DBI; use Data::Dumper; $SOAP::Constants::DO_NOT_USE_CHARSET = 1; # […]


mod_plsql XML example

Posted by admin on Feb 3, 2010 in Database

To add a mod_plsql procedure to an Oracle application server is pretty straightforward. The first step is to add the DAD to the server using the console. After logging into the application server console (http://app:1156) select HTTP_server > Administration > PLSQL > Create DAD. Add the database connectivity information and restart the server. Then create […]


Compiling PHP on Centos

Posted by admin on Nov 30, 2009 in Database, Unix

The precompiled PHP binary does not include MySQL support so it must be recompiled from source. Download the PHP package and untar it Download the httpd headers yum install httpd-devel Configure ./configure –with-mysql=/usr/bin/ –with-libdir=lib64 –with-apxs2 –with-pic –without-aolserver make make test make install edit http.conf LoadModule php5_module /usr/lib64/httpd/modules/libphp5.so AddType application/x-httpd-php .php Alias /php/ “/var/www/html/php/htdocs/” <Directory “/var/www/html/php/htdocs/”> […]


Database Stats

Posted by admin on Sep 9, 2009 in Database

The Oracle automated process for gathering statistics is hidden away under schedules. To find if it is active, SELECT * FROM DBA_SCHEDULER_JOBS; The stored procedure can be run at another time by SYS using EXEC DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC;

Copyright © 2008-2018 Brinsmead Data Services All rights reserved.