sqlplus / as sysdba <<EOF
whenever sqlerror exit sql.sqlcode
alter tablespace foo
rename to foo_old;
create tablespace foo
datafile size 100m;
alter table foo move tablespace $TABLESPACE_NAME nocompress;
if [ $RETURN_CODE -ne 0 ]; then
echo "*** Tablespace renaming error code $RETURN_CODE. ***"
In this example we rename a tablespace and then create a new tablespace in its place and move a table there. It seems rather contrived but this is actually what I'm doing to move tables to an uncompressed and unencrypted tablespace to test storage vendor deduplication claims. But I digress ...
To test this, I used a tablespace that doesn't exist. This results in an ORA-00959 error when the tablespace does not exist. However, the return code I get was 191. I spent a good portion of the day testing and retesting with all sorts of debug output until I stumbled across this comment. Since Linux (and I'm told Unix) return codes only go up to 255, the ORA sqlcode value of 959 is wrapped until there is a remainder. The value of 191 is the difference, achieved simply by modulo operation:
959 % 256 = 191
And suddenly the skies were cleared.
As always, hope this helps!