Search code examples
sqloracleoracle11g

"DROP SCHEMA" ORACLE SQL statement is not documented on docs.oracle.com


Currently trying to wrap my head around how the documentation of the Oracle's database including their implementation of SQL is structured.

There are pages documenting the CREATE SCHEMA and DROP statements for other type of objects. But extensive search of the documentation hasn't yielded any results concerning the "DROP SCHEMA" statement.

What principle one should abide to in their Oracle documentation search to find the subject of interest?


Solution

  • Although you can create schema, there's no drop schema. Schema represents Oracle user along with its objects (tables, views, procedures, ...). If you want to get rid of it, you'd just DROP USER.


    Note (in CREATE SCHEMA) documentation says:

    This statement does not actually create a schema. Oracle Database automatically creates a schema when you create a user (see CREATE USER). This statement lets you populate your schema with tables and views and grant privileges on those objects without having to issue multiple SQL statements in multiple transactions.

    Therefore, if you want to remove certain objects from your schema (i.e. not everything it contains, i.e. user itself), you'd do it one-by-one. Not necessarily in such a tedious way, though - you can create a PL/SQL procedure which drops e.g. views you created, or procedures, or whatever else. Tables are kind of tricky because of foreign key constraints, though.