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!