Export schema
Logical backups
The data in an Oracle database is structured such that it facilitates the storage, retrieval, updating and deletion for records. Because of this, it is very different from a simple list of table contents. For example, it contains additional structures called indexes which speed up query times, and empty space to allow for table growth. It includes administrative information about users and their access privileges, and temporary information which is only refreshed when required.
Because of these additional structural features, the contents of a table in the database are not necessarily held in a single place, and their arrangement may not be particularly efficient when considered purely in terms of storage space.
A logical backup uses the standard database engine to extract logical objects such as tables from the database in a consistent way so they can be stored in an external file. The main Oracle tool for doing this is called Data Pump. One of the advantages of a logical backup is that it can be used to manipulate individual schemas. This might be to export a schema for backup purposes, or to import a schema from a template during the installation of a database application.
In this exercise you will be exporting the HR schema, removing it from the database and recreating it using Data Pump import.
Exporting a schema
In the DBA explorer, expand the SYSTEM connection to reveal the Data Pump option which allows you to export or import data. Right click the Export option as shown below and choose Data Pump Export Wizard... from the pop-up menu.
Data Pump offers many options for selecting which data to include in the backup. Because we are interested in exporting a complete schema, choose the Schemas radio button as shown below, and click the Next button.
The second step of the wizard lets you choose the schema(s) you want export - notice that you can process several at once. Click on HR as shown below (1), click the button (2) to move HR into the selected list (3), and click Next.
Accept the default options for the next three steps by clicking Next, but change the name of the output file in step 5 to HR.DMP as shown below (1) and append a timestamp to the filename (2). Both of these steps would be help to identify the correct file to use if you needed to recover the exported schema.
Accept the remaining default values to complete the wizard. A progress dialog will be displayed for a short time while the export is prepared. After that, you can expand the Export Jobs branch in the DBA explorer to see the status of the job as shown below (1). While the export is running, the state will appear as EXECUTING (2). The operation may take a minute or two to complete, and clicking the refresh icon (3) will update the display. When the export job has finished, the entry in the explorer will disappear.
Oracle puts the export file into a specific folder on the C: drive. You can identify your file either by the name (if you noted it down) or by the timestamp:
