This is security through obscurity. While it might not hurt you in all cases, on it's own it does not provide viable security. Do not rely on this as your protection.
A short parable
Let's say you keep your money in a jar labeled
MONEY in your house. Since you know you sometimes forget to lock the door when you leave the house, you relabel the jar
COOKIESto prevent a thief from finding it. Would you feel more secure now? Sure, a very lazy, dumb thief might miss it, but you would not have to be a master thief to steal your money. Wouldn't it be better to just remember to lock the door instead?
Back to the computer world
Let's say you have an old phpBB installation with an SQL injection vulnerability. By default the tables are prefixed by
phpbb_. You change this to
obscure_. Will this help you?
A naive scan might fail to find hard coded table names (like
phpbb_users with all the passwords), and therefore fail. But even a script kiddie could run a script that runs a
SHOW TABLES and finds your
obscure_users. In fact, there are tools that will automatically dump the contents of all your tables through a SQL injection vulnerability.
So what is the lesson here? While changing table prefixes (relabeling the jar) might perhaps protect you from the most basic dumb automated attack (the stupid, lazy thief), you would still be vulnerable to simple attacks performed by script kiddies (a thief searching through your jars). When you have a real vulnerability like SQL injection, the solution is to fix that vulnerability (lock the door), and not to add a thin veil of obscurity.
That said, a simple precaution that might slow down some attacks might still be worthwhile as "defence in depth" if it has no harmful side effects. Just don't feel safe just because you implement it.
(As an addendum, I should say that running multiple installations in the same database might come with security implications on it's own, depending on the situation.)