Oracle SQL Fast Retrieval of CLOBS

If a table contains CLOBS, depending on the method you use to connect, usually the CLOB-columns just return a handle that will trigger another connection/query to the database in order to retrieve the value. This makes it really slow. An easy way to speed it up is to retrieve everything which is small enough as string value. A string value however is limited to 4’000 characters. Thus you need to issue two queries:

The dbms_lob.substr part is there to avoid problems with multibyte characters and encodings.

Some database providers like ODP.net provide properties like InitialLOBFetchSize that can be set to retrieve the data in-line as well. Devart on the other hand does not support any in-line loading of CLOB data.

Import local subversion repository to git

If you want to import a local subversion repository (maybe a dump) to git you can do this quite easily:

First you need to map the Subversion commit authors to Git commiters. For you just create a textfile author-mapping.txt:

And then you run the following commands:

Sometimes the protocol file:// cannot be handled. In that case you can run svnserve –daemon to serve your subversion repository locally. Then you can use svn://localhost/svnrepo instead.

Mangled Names on Synology Diskstation

From time to time I noticed strange filenames on the Synology Diskstation. Names like BGELMF~0. They always had a tilde. Those strange names are called mangled names. Samba has a feature to provide them to bad clients. Unfortunately this can also lead to “renaming” those files.

It is better to prevent this behavior altogether. To do this you need to connect over SSH to your diskstation and then you need to login as root.

Issue the following commands:

Now insert the following line in the section global:

Notice: this option could be reset after an update of the diskstation. If the bad filenames appear again, you should verify that the settings is still active.

Interesting Swiss Maps

They always hide interesting stuff on map.geo.admin.ch. You need to search “somewhere” for additional layers. One nice layer is the Zeitreihen layer because it allows anyone to see the maps from the 80s and 90s. Those maps (1:25’000) have a lot more detail than the ones used today. Today lots of small routes for example inside of forests are not visible anymore.

Check it here: Swiss Map from 1844 till 2012

Check it here: Swiss Map with Building Numbers

Backup all MySQL Databases of a User

The following script can be used to backup all MySQL-Databases of a user each in it’s own compressed. sql file inside a folder with todays date:

You just need to substitute username, password and backuplocation. If you use the root-MySQL user all databases of the whole MySQL-server are backed up.

Get a large corpus of text data

I needed to get a large corpus of text data in order to test some full-text-search functionality. Also the text data should not be in one big file but in many small files instead. This lead me to Project Gutenberg and to Wikipedia.

Here is how you can get lot’s of data (we exclude the zip from gutenberg because they contain just the other files that are downloaded anyway):

InnoDB Engine not available in MySQL

I had an empty standard installation of MySQL but somehow I could not create a database using the InnoDB engine. The engine was just not activated (you can check that with the

command). If it states support: no it is inactive or not available. As I was sure that InnoDB is supported using the default installation of the official Debian Package I did some research on the internet. I found out that InnoDB is automatically disabled if something is bad with it’s data files. You can just remove all files that start with "ib" from the directory /var/lib/mysql. After a restart of the service everything worked fine.

Revisioncontrol with Git

The most fundamental difference between Git and other revision control systems like SVN or CVS is the way how you work with it. With Git you will usually clone a repository. This creates a directory on your desktop where all the data about all revisions is stored. This means that you are not depending on a server somewhere. You can easily just use git locally.

Local install

Most current linux distributions include a package git-core and maybe also git-gui If you are using Windows you should have a look at msysgit

After the initial install you should configure the your user information:

Install on a server for example QNAP NAS TS-209

You can just install git the same way as with the local install. On a TS-209 this means:

You can create a repository on the server as follows:

with –bare your files will just be saved in the binary git-format.

To use that repository on the local machine you will need to create an empty repository and the you can specify the remote one as a remote destination. The command push allows you to writer local changes onto the server and fetch allows you to read updates from the server and apply them locally.

Full syntax for SSH-Url:

or

Caution: you cannot define the password for the user (not user:password@SERVER:PORT) if you define a port!

Using git for a SVN-Repository

Usually you just need to install the package git-svn. After that you can use a SVN repository just the same way as you would use a git repository. You just need to type git-svn instead of git for all commands.

Import an SVN repository:

Fetch updates

Ignore filemode changes

Just execute the following command:

Problems with git

  • Trailing Whitespace Error
    This pre-commit Error can be fixed easily. Just remove the following lines from the file .git/templates/hooks/pre-commit:

  • git-receive-pack: command not found

    The reason for that error is the PATH variable which will not be set if you are using a not-interactive SSH connection. For an easy fix you could just make links to the necessary binaries in you /bin folder:

Sources