majeed73 was kinda on the right track. When your login shell runs your shell script, it creates a child process. Inside that child process you are changing ORACLE_SID. Since it is exported, if that child process was to create any children of its own, those children would see the new ORACLE_SID. But a child cannot affect its parent. When the child process exits, the parent continues with the old value for ORACLE_SID. There is nothing that the child can do to change this. Ditto with your aliases by the way.

Since this stuff was in your .profile, your login shell must also be /bin/sh. The parent could invoke the script as a dotted script. Instead of:
"./script" or "script"
do this:
". ./script" or ". script"

That will cause the script to run in the current shell rather than a subshell. And you should be able to alias that:
alias script=". $HOME/realscript"
or whatever. So now the command "script" would do what you want.