Drop Old Schemas before Replacing with a New Export

This optional section describes how to delete a schema from an Oracle database. If it is ever required to import an exported schema into the same database from which it was exported earlier, the old HPD schema must be dropped, that is, deleted, first. The database administrator might need to do this on a test database, before replacing it with a newer copy from production.

Note

Before starting, it is necessary to find out:
- The HPD schema owner name of the HPD schema to be dropped.
- All HPD user accounts associated with this schema.
- The tablespace name of this schema.
- The folder and name of the associated datafile(s) and their size in MB (or GB).

This information can be found by using Oracle’s Enterprise Manager program, for example, or from records kept when the new HPD schema was first created. This information must be known if this schema is to be restored again later.

> sqlplus system/password@database

SQL> DROP USER hpdowner CASCADE;

User dropped.

-- Use DROP ROLE to drop the HPD Admin (hpdownerADM_R) role used by this schema
SQL> DROP role HPDOWNERADM_R;

-- Use DROP USER to drop all other HPD users of this schema as necessary
SQL> DROP USER hpd_user1 CASCADE;
SQL> DROP USER hpd_user2 CASCADE;

...

-- Optionally: If the tablespace is no longer needed, it can be deleted too:
SQL> ALTER TABLESPACE tablespace_name OFFLINE;

Tablespace altered.

SQL> DROP TABLESPACE tablespace_name INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;

Tablespace dropped.

SQL> exit

Connect to the Oracle database as the Oracle System database administrator user (or another DBA) account. Make sure that a good backup exists, and that no-one else is storing data in the tablespace besides the HPD database schema owner. If other HPD user accounts were created to connect to the schema to be dropped, these will need to be processed, that is, dropped, as well.