How Oracle implements a one-stop operation for creating databases, backing up databases, and importing data exports

How Oracle implements a one-stop operation for creating databases, backing up databases, and importing data exports

The management of data objects and data in Oracle is undoubtedly managed using PL/SQL developer, which also provides us with a lot of convenient and fast operations, so that we no longer complain about Oracle's own ugly, hard-to-use UI. Since we are generally building tables, checking data for the majority of operations, less will consider the entire system of Oracle full backup operations. But in some of our release operations, we have to consider how to export Oracle's objects, table data into SQL scripts, and put the creation of Oracle Tablespace, the creation of Oracle database, into the script, This allows us to quickly restore or deploy an Oracle database to a new machine.

This article mainly describes how to combine SQL script and PL/Developer tool to create table space, create database, backup database, data export and so on, then implement Oracle object creation, import data, etc., so that we can quickly understand, Create the required deployment SQL scripts and database operations.

1. Prepare the database creation script

createtablespace whc_tbs datafile ‘E:\oracle\oradata\whcdb\whc.dbf‘size 100M;--DROP TABLESPACE whc_tbs INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;createuserwhc identified bywhc defaulttablespace whc_tbs;grantconnect,resource towhc; grantdba towhc;--Revoke dba from whc;

Where--The comment statement is to delete the namespace and remove the DBA user's permissions.

These scripts are used for the purpose of creating a new table space and Oracle database.

Where the E:\oracle\oradata\whcdb\whc.dbf path is where we want to store the database data, so ensure that the path has sufficient space and sufficient access rights, otherwise it will fail.

2. Exporting database objects

In the PL/SQL Developer menu Tools=>export User Objects A dialog interface, and then box select the Oracle database objects to be everywhere, including tables, sequences, stored procedures, views, functions and so on, and specify the exported file names, Do the following.

3) Export Table data

After exporting the table structure and other objects, our next step is to export the table data from the database, and the PL/SQL Developer tool supports exporting the data to the PL/SQL database script as shown below. Such exported scripts can be imported in the PL/SQL Developer tool or by ourselves through the use of the "plus" tool.

So far, we've completed three scripts, including creating database space and database scripts, creating Oracle database objects (tables, stored procedures, views, sequences, etc.), creating scripts for importing data, so that three of them are a complete database. The final step is the question of how we import database objects and data.

4) Import database objects and data

Import database objects and data operations, both can be implemented through the import tables operation, we specify the database script created in the previous two steps, the execution of the new environment in the creation of database objects and database data. as shown below.

The data interface that executes is shown below.

Complete the above steps, we have all the things in the new database environment, successfully complete the entire Oracle database object and data migration work.

It is worth noting that when we export Oracle objects and data, the default is the original Oracle database table space and database user name, if we want to specify a different table space and database user objects in the new database server, then 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 under the Linux platform, the first step is similar, that is, the path name of the specified tablespace is slightly different, and the other operations are no different.

Original link: http://www.cnblogs.com/qfb620/p/4604757.html

How Oracle implements a one-stop operation for creating databases, backing up databases, and importing data exports