Useful Oracle SQLPLUS command

Running sql script in the SQL*Plus

Running sql script in the SQL*Plus is very simple. Without extension and with '@' as prefix, you can easily run a sql script. The below is the example of running "sample.sql" script

@sample


Deleting all tables in current database

Using SQL*Plus create the below script generating sql.

Spool drop_tables.sql
SELECT 'DROP TABLE ', table_name,' CASCADE CONSTRAINTS;'
FROM user_tables;
Spool off

You can also use all_tables instead of user_tables. In this case, all tables which is not used by the login account also can be deleted.

Then, run the script with @ directive as the below:

@drop_tables;

Then all tables will be deleted from your database.