Changing a schema name

This is a really small thing, but I am so glad one of my co-workers has found it! So – did you know, that changing the schema name does not block anything?!

I did not know, and so I was very scared when we had a need to change the name of the schema “live” on production environment. Yes, we made sure the access path change was ready to go, and yes, fortunately there were no scripts, where the schema name would be explicitly set up. But still.

A couple of tables in this schema were frequently accessed by a number of applications, including more or less constant insert… so I was pretty much sure that we will introduce a chain of locks.

Imagine my surprise, when my co-worker proved me wrong! As an experiment, he started a pretty long-running insert into one of the tables of the schema X, and then while it was running, changes the schema name in another session to Y. And guess what? When this insert was finished (successfully!), the table was already in the schema Y! To be honest, I never tried to check this fact, I was assuming, it will never work this way, and was ready to all sorts of complications.

So nice to find out, that some things might be actually done way easier than you’ve planned!

Advertisements

Leave a comment

Filed under SQL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s