How Does Oracle implement a one-stop operation for creating databases, backing up databases, and exporting and importing data?

Oracle uses PL/SQL Developer to manage data objects and data. This tool also provides many convenient and quick operations, so that we no longer complain about the ugly and difficult-to-use UI of Oracle itself. Most of the operations for table creation and Data check are performed, but the complete backup operations of the entire Oracle System are rarely considered. However, in some of our release operations, we must consider how to export Oracle Objects and table data to SQL scripts, in addition, the Oracle tablespace and Oracle database creation operations are also put in the script, so that we can quickly restore or deploy the Oracle database to the new machine.
This document describes how to use SQL scripts and PL/SQL Developer tools to create tablespaces, create databases, back up databases, export data, and perform Oracle object creation, data import, and other operations, this helps us quickly understand and create the required deployment SQL scripts and database operations.
1. Prepare the database creation script

Create tablespace whc_tbs datafile 'e: \ oracle \ oradata \ whcdb \ whc. dbf' size 100 M;
-- Drop tablespace whc_tbs including contents and datafiles cascade constraints;
Create user whc identified by whc default tablespace whc_tbs;
 
Grant connect, resource to whc;
Grant dba to whc;
-- Revoke dba from whc;
The -- Comment statement deletes the namespace and removes the permissions of DBA users.
These scripts are used to create a tablespace and an Oracle database.
E: \ oracle \ oradata \ whcdb \ whc. the dbf path is the location where we want to store database data. Therefore, make sure that the path has sufficient space and access permissions. Otherwise, the path will fail.
 
2. Export database objects

In the menu Tools => Export User Objects of PL/SQL Developer, a dialog box is displayed, and the Oracle database Objects, including tables, sequences, stored procedures, and views, to be displayed, and specify the name of the exported file.

 

3) export table data

After exporting the table structure and other objects, the next step is to export the table data of the database. The PL/SQL Developer tool supports exporting data to the PL/SQL database script, as shown below. The exported script can be imported in PL/SQL Developer or by using SQL plus.

 

So far, we have completed three scripts, including creating database space and database scripts, creating Oracle database objects (tables, stored procedures, views, sequences, etc) create a script to import data. The last step is how to import database objects and data.
 
4) import database objects and Data

You can use the Import Tables operation to Import database objects and data. We specify the database script created in the preceding two steps, then you can create database objects and database data in the new environment. As shown below.

The executed data interface is as follows.

 

After completing the above steps, we have everything in the new database environment, and successfully completed the migration of the entire Oracle database object and data.
Note that when we export Oracle Objects and data, the tablespace and User Name of the original Oracle database are used by default, if we want to specify different tablespace and database user objects in the new database server, we need to replace the generated SQL script and specify a reasonable tablespace and database user in the first step.
If it is a database server on the Linux platform, the first step is similar, that is, the path name of the specified tablespace is slightly different, and other operations are not different.


From wuw.cong (Wu huacong)'s column