It is a question of good design. For many columns (usernames, zip codes, phone numbers etc) you know the length. With VARCHAR2(10) you know the maximum length for the column is 10, with VARCHAR2(1000) there is a very good chance you get values with length more than 10.