Restoration of Oracle databases in a hybrid framework, framework oracle

Restoration of Oracle databases in a hybrid framework, framework oracle

In the earlier articles, "How to Create a database in Oracle, back up a database, and export and import data" roughly introduced the backup and restoration operations of the Oracle database, this article describes the script or restoration operations of Oracle databases based on the development framework.

We have various development frameworks in Winform, hybrid development, and Web development frameworks. The underlying layers support a variety of databases, such as MS SQLServer, Oracle, MySQL, SQLite, and PostgreSQL, as shown in.

SQLServer supports the most common backup and recovery operations, while Oracle databases are the second and more common databases. However, compared with SQLServer, oracle databases are not so convenient to process. Therefore, this article focuses on how to process database scripts or restore databases based on backup files.

1. Database Script Processing in the framework

If a database script is used, Oracle can create a tablespace according to the previous "How to Create a database, back up a database, and export and import data" method, then create the corresponding database information, as shown below.

create tablespace win_tbs datafile 'C:\app\Administrator\oradata\whcdb\win.dbf' size 100M;create user win identified by win default tablespace win_tbs;grant connect,resource to win; grant dba to win;

The above Code creates a tablespace file, creates a win user, and attaches it to the tablespace.

With this information, we can log on to the Oracle database by using win and password.

Then we can use this user to create tables and process data on PLSQL.

The table creation script is as follows.

/* ===================================================== =======================================* // * Table: TB_CITY * // * ======================================== ======================= */create table TB_CITY (ID number not null, CITYNAME NVARCHAR2 (50), ZIPCODE NVARCHAR2 (50), PROVINCEID number, constraint PK_TB_CITY primary key (ID); comment on table TB_CITY is 'national city table '; comment on column TB_CITY.CITYNAME is 'city name'; comment on column TB_CITY.ZIPCODE is 'postal Code'; comment on column TB_CITY.PROVINCEID is 'province id ';

For example, if we create different types of database scripts, we only need to add them in sequence or choose to add them to execute the database scripts.

So execute these SQL, how to operate it, is not directly dragged to the PL-SQL can be?

Of course not. Otherwise, a longer database script may lead to very slow execution efficiency.

You can use either of the following methods.

This method is very fast to execute and is more efficient than simply executing on the SQL window of the PL-SQL.

Another way is to execute the database script in another place in the PL-SQL, as shown below.

In the "Tools"> "Import table" operation, a dialog box is displayed, which is also one of the efficient operations for executing scripts.

The methods described above are used to build the corresponding data objects and data based on the database script when the database does not exist.

 

2. Use imp and exp to import and export data

Another operation to restore an existing backup file or back up an existing database is to use the Oracle commands imp and exp.

The basic format used by imp: imp [username [/password [@ service]

You can also use imp to perform step-by-step operations.

Let's take a look at the format of the two commands:

Basic syntax and example:
1. EXP:
There are three main methods (complete, user, table)
1. Complete:
Exp system/manager buffer = 64000 FILE = C: \ FULL. dmp full = Y
To perform full export, you must have special permissions.
2. User Mode:
Exp sonic/sonic buffer = 64000 FILE = C: \ SONIC. dmp owner = SONIC
In this way, all the SONIC objects are output to the file.
3. Table mode:
Exp sonic/sonic buffer = 64000 FILE = C: \ SONIC. dmp owner = sonic tables = (SONIC)
In this way, the SONIC table is exported.
2. IMP:
Three modes are available (full, user, and table)
1. Complete:
Imp system/manager buffer = 64000 FILE = C: \ FULL. dmp full = Y
2. User Mode:
Imp sonic/sonic buffer = 64000 FILE = C: \ SONIC. dmp fromuser = sonic touser = SONIC
In this way, all the SONIC objects are imported into the file. You must specify the FROMUSER and TOUSER parameters to import data.
3. Table mode:
Exp sonic/sonic buffer = 64000 FILE = C: \ SONIC. dmp owner = sonic tables = (SONIC)
In this way, the SONIC table is imported.

 

Import the data of a user into another user format:

Imp win/win file = c: \ win. dmp fromuser = win touser = win

If a table already exists, an error is returned when the table is executed, so that the table is not imported. Then add ignore = y to the end.

In the database directory of the framework, we usually include the corresponding Oracle backup file (. in this way, you can directly restore the database when developing and using Oracle. However, if you want to refine the database scripts, oracle script processing is also a good choice.