Drop and create oracle user/schema

drop-create-schema.sql

--connection as dba
connect <system>/<password> as sysdba;

--drop oracle user/schema
drop user <user_name> cascade;

--create a new user and schema
create user <user_name> identified by <password>;

--grant dba, other roles and privileges
grant dba to <user_name>;
grant connect to <user_name>;
grant resource to <user_name>;
grant all privileges to <user_name>;