Full backup and recovery of Oracle database user data

Backup and restore of Oracle database users and tables can be done quickly and easily using the Plsql-developer tool.

Full backup and recovery of Oracle database user data

1. Backup

1.1 pl/sql-> Tools, export user objects, options

Common user objects include the following:

Table,sequence,view,package,type,function,procedure,package Body,tregger

Select the user object that you want to import, export the. sql file, and this step exports the built-in statements, including stored procedures, triggers, views, and so on.

1.2 pl/sql-> tools, export table, options

Press the CTRL key to select all objects that you want to export

There is no data in the table exported in the previous step, so this step is to export all the data in the table (select the PL/SQL developer mode. The other two approaches are Oracle Export, SQL INSERT, and so on)

At this point, the backup is complete, usually the SQL file is only a few megabytes, PDE files have hundreds of megabytes.

2. Recovery

2.1 As the above we export is a user under the complete data, so if the database to be restored there is this user, we first to delete the user and the user all the data under the operation, and end the relevant process, the script is as follows:

Drop user username cascade;

Select sid,serial# from v$session where username= ' username ';

Alter system kill session SessionID;

Note: If you want to restore the backup to a different database, you do not need to do the above operation.

2.2 Create the user and give permission to the script as follows:

Description: To create a user's own data table space and temporary tablespace

CREATE USER username identified by 12345678 DEFAULT tablespace test_data temporary tablespace test_temp;

Grant create session to username;
Grant connect to username with admin option;
Grant resource to username with admin option;
--Grant/revoke System privileges
Grant alter any role to username;
Grant ALTER any sequence to username;
Grant alter any table to username;
Grant ALTER any trigger to username;
Grant alter tablespace to username;
Grant comment any table to username;
Grant create any sequence to username;
Grant create any table to username;
Grant create any trigger to username;
Grant create any view to username;
Grant create materialized view to username with admin option;
Grant create public synonym to username with admin option;
Grant create role to username;
Grant create session to username with admin option;
Grant create synonym to username with admin option;
Grant create tablespace to username;
Grant create user to username;
Grant CREATE view to username with admin option;
Grant drop any role to username;
Grant drop any sequence to username;
Grant drop any table to username;
Grant drop any trigger to username;
Grant drop tablespace to username;
Grant drop user to username;
Grant grant any role to username;
Grant insert any table to username;
Grant Select any table to username;
Grant unlimited tablespace to username with admin option;
Grant update any table to username;

2.3 Open a Command window, copy the backup good SQL text content to the command window to execute, this step is to create all the user objects, including tables, stored procedures, functions, sequences ... But there is no data in this table.

2.4 Importing Tables

Import tables in Pl/sql-> tools (press CTRL to select the tables you want to import)

All triggers are executed in turn, all foreign key constraints are suppressed, all table data is deleted, all table data is inserted, FOREIGN key constraints are enabled, and trigger actions are enabled. Importing user objects is usually resolved in minutes, and it typically takes several 10 minutes or even hours to import the data from the table.

=======================

Three ways to export a table when the Plsql tool is exported:

The PL/SQL tool exports the table with three ways to export the Oracle table structure and data, three ways are: Oracle Export (client Exp tool), SQL inserts, pl Developer, respectively, the following is a brief description of the following differences:

The first way is to export the. DMP formatted files,. DMP is a binary file that can be cross-platform and includes permissions, and is efficient and widely used. The import and export method is consistent with the Exp/imp tool. Http://www.cnblogs.com/rusking/p/4006166.html

The second way to export. sql format insert into script, can be viewed with a text editor, versatility is better, less efficient than the first, suitable for small data import and export. In particular, it is important to note that there are no large fields (Blob,clob,long) in the table, and if so, you will not be able to export (prompt for the following: table contains one or more long columns cannot export in SQL Format,user PL/SQL Developer format instead).

The third way to export. PDE-formatted files,. PDE is PL/SQL Developer's own file format, can only be imported with the PL/SQL Developer tool and cannot be viewed with a text editor.

When importing data, there are also three ways to export the import:

Original link: http://www.cnblogs.com/Acamy/p/5893724.html

Full backup and recovery of Oracle database user data