Blog

MySQL partial import

#mysql #import

Here’s the situation. You have a dumpfile called mysql.dmp which contains a whole MySQL database dump. However you only need to import one table (or anything, but only a portion of that dumpfile). Because MySQL dumps are simply SQL scripts, we can use sed in order to extract only the portion of the SQL instructions we want. Let say the name of ... Read more

20 Sep 2015 - less than 1 minute read

SQL*Net more data from client

#oracle #network

The error SQLNet more data from client* usually happens when there’s a large amount of data sent from client (or other database in case of dblinks), which doesn’t fit into single SDU size Oracle packet. The server process knows that the call hasn’t ended and there is more data/packets to come before the call ends. Also, the large amount of data... Read more

20 Aug 2015 - 1 minute read

Archive log files based on date

#linux

Here’s some piece of code to use on a cron job when you have a software component (database or application) that spits out logs and you want to archive or delete those files based on creation date. #Archive find log/ -type f -mtime +681 -ls -exec mv {} log/2011/. \; #Delete find . -type f -mtime +30 -ls -exec rm {} \; Read more

20 Jul 2015 - less than 1 minute read

Registering with the listener

#oracle #network

When restarting a database, if you can’t acess it from outside, you may have to associate the newly restarted database with the listener. The Pmon process that is started with the instance is responsible for registration of oracle server with listener. Pmon process wakes up at every 60 seconds and provide information to the listener. If any pro... Read more

20 Jun 2015 - less than 1 minute read

Moving MySQL datafiles

#mysql #linux

In this article, I’ll demonstrate multiple solutions to move datafiles Method 1: using symbolic links Stop the Mysql instance. /etc/init.d/mysql stop Then move the files and put symbolic links to lure Mysql into thinking the files are still in place. mkdir /new_dir/datafiles/my_db cd /old_dir/datafiles/ cp -Rvp my_db/* /new_dir/datafiles/my_... Read more

20 May 2015 - less than 1 minute read

Resolve PLAN_TABLE is old version error

#oracle

When displaying execution plans on SQL*plus, you may notice an information message like so: ------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| ----------------------------------------------------------... Read more

20 Apr 2015 - 1 minute read

Check database uptime

#database #oracle #mysql

Sometimes you have to check when a database was started for the last time. To get the information, just log on to the database and use the following query to get the last startup time: On Oracle database SELECT to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') AS "DB Startup Time" FROM sys.v_$instance; On Mysql database SHOW GLOBAL STATUS LI... Read more

20 Mar 2015 - less than 1 minute read

Change the default temporary tablespace

#database #oracle #sql

When your temporary tablespace gets full and you can’t resize it, you may consider switching ti a new ont to reclaim disk space. Changing the default temporary tablespace will allows you to discard the old one entirely, and reduce the total database size. In the following example, Temp1 is the current temporary tablespace, that we want to switch... Read more

20 Feb 2015 - 1 minute read

Enable parallel DML

#oracle #sql

Effectively using parallel DML can speed up data manipulation. By the way, DML stands for (Data Manipulation Language), and refers to Insert, Update and Delete which plays at the row level. DDL (Data Definition Language) refers to Create, Alter or Drop statements and is used to change the structure of database objects. Using parallel DML is us... Read more

20 Jan 2015 - less than 1 minute read

Getting the server IP Address from SQL*plus

#system #network

Here’s a small script to get the IP address of the host server directly from the SQL*plus command line. DECLARE v_host_name v$instance.host_name%type; v_ip_address varchar2(50); BEGIN SELECT host_name INTO v_host_name FROM v$instance; dbms_output.put_line('the database server name is ' || v_host_name); SELECT UTL_INADDR.GET_HOST_ADDRESS(v_host... Read more

20 Nov 2014 - less than 1 minute read

ORA-01666 When Activating a Physical Standby Database

#oracle #standby

You’re waking up a Physical standby database, and are welcomed by the following ORA-Code: ORA-01666: controlfile is for a standby database Don’t panick, that’s just because the database has been configured as a Standby one. Therefore, you can only open it as a Standby database. Simply start if with the following commands : sqlplus / as sysdb... Read more

20 Oct 2014 - less than 1 minute read

Running Mysql commands from Bash script

#mysql #linux

Eventually, you’ll need to automate queries on your Mysql database, let’s say, for reporting purposes. Hee’s how to put SQL queries into a shell script, and possibly including variables based on the date or whatever fits your needs: $ cat execmysql.sh #!/bin/sh ids="3,4" table="NMS.main" qry="select id,data from $table where id in ($ids)" echo ... Read more

20 Sep 2014 - less than 1 minute read