Here's my situation. I'm a non-DBA user in an Oracle database. As such, I only have DDL and DML privileges for my own Schema, so I can only create, drop, update, etc for tables in my own Schema.
So let's say I write a script that creates and updates tables in my own Schema (let's call the Schema USER1). When the day comes that I leave the company and need to pass this script on to my replacement (let's say USER2), how do I do that if the script only works for USER1? Does USER2 really have to go through my script and replace all references of USER1 with USER2?
Point being that I'd really like my script to work regardless what user runs it without having to tinker with it.
First, if it's a production script that creates tables for an application and manipulates it's data, it probably shouldn't be run from your own user, but from some dedicated production user.
Regardless, if you're running a script in your own schema, it doesn't need to explicitly reference it. Just remove the references to the schema, and all the objects will be created under the user running it, meaning that any user can run it from his own account (assuming it has the basic DML/DDL permissions the script requires).