Drop/create oracle database/schema by sql statement

Oracle is a little different with other RDBMS. It use "Schema" instead of "Database".

Schema is user based. If you want to delete a schema of a user, you can delete the user with the parameter "cascade".

drop user username cascade;

Create the user will also create the schema with the same name.

create user newusername identified by password;

Don't forget to grant the privilege to the user

grant all privilege to newusername;

Here's a useful script that might help everyone who tries to drop all tables created by a user in Oracle DB:

select 'drop '||object_type||' '|| object_name|| DECODE(OBJECT_TYPE,'TABLE',' CASCADE CONSTRAINTS;',';') from user_objects where object_name like 'T%';

This will print out all the drop statements.

The second step would be to copy and execute them inside your favourite Oracle DB client. The ones that have constraints to other tables will drop them by using the 'cascade constraint'

To change the current schema

alter session set current_schema=scott;