Is there any software tool available which allows us to visually mark/tag a window? I'm not restricting this question to any OS.
/* Aside: I once replaced a live production database containing thousands of records with an empty development version simply because the two instances of Enterprise Manager looked exactly the same. */
None that I'm aware of, but perhaps a virtual desktop system for your OS of choice would help keep the separation a little better for you.
Since you didn't restrict your question, I'll answer with my solution to this type of problem. I often am logged into multiple different machines with PuTTY as different users (including as root). If I have a window that I want to distinguish from the others, for example when I need to make sure I know I am typing commands as root, I change the background of the window to a different colour. A window that is mostly red really stands out as being something extraordinary.
If you are using TOAD for your db access you can set a custom colour for each of your connections. (List of Quest products here scroll down page to the TOAD links) The colour appears as a border around each TOAD window (at least it did on the Windows version I used in my last job) I set production dbs to RED, pre-production to orange, and dev to green.