Define is a SQL*Plus client variable. It is NOT a database level setting.
Setting this to ON instructs SQL*Plus to look for substitution variables in your code. These are variables prefixed by an ampersand (&).
If there are any, the client will stop and ask you to supply a value for the variable. At which point it'll change your code to include this text.
The problem comes if you have & buried in some text somewhere. e.g. say you're inserting:
Tom & Jerry
When SQL*Plus comes to process this, it'll ask you what to replace the variable Jerry with. And insert that instead:
SQL> create table t (
2 c1 varchar2(20)
3 );
Table created.
SQL>
SQL> set define off
SQL> insert into t values ( 'Tom & Jerry' );
1 row created.
SQL>
SQL> set define on
SQL> insert into t values ( 'Tom & Jerry' );
Enter value for jerry: mouse
old 1: insert into t values ( 'Tom & Jerry' )
new 1: insert into t values ( 'Tom mouse' )
1 row created.
SQL> select * from t;
C1
--------------------
Tom & Jerry
Tom mouse
So unless you know your script includes these variables, it's best to set define off. This avoids unexpected changes to your data & code!