How can I restore the dmp file to the oralce database after restoring the oracle database ?, Oracleoralce

How can I restore the dmp file to the oralce database after restoring the oracle database ?, Oracleoralce

Here I am using the oracle10g version. My machine has 512 memory and it is difficult to run the oracle web management program. So I chose to use the command line method,

The report provided by oracle after my installation is completed below

Enterprise Manager Database Control URL-(orcl ):

Https: // localhost: 1158/em

The database configuration file has been installed

E:/oracle/product/10.2.0,

Other selected installation components have also been installed

E:/oracle/product/10.2.0/db_1.

The iSQL * Plus URL is:

Https: /localhost: 5560/isqlplus

The iSQL * Plus dba url is:

Https: // localhost: 5560/isqlplus/dba

Let's take a look at how to complete this task.

First, start the service. (If the database is in the startup status, skip this step)

Open the command line and execute the following statement

Net start OracleServiceORCL

Net start OracleOraDb10g_home2TNSListener

Net start OracleOraDb10g_home2iSQL * Plus

The above method is to start the service in the windows service. When the windows service cannot start the database instance, apply the following statement

Set oracle_sid = orcl

Oradim-startup-sid orcl

Sqlplus internal/oracle

Startup

Second, we will clean up the traces we have previously restored. If we have restored them in the database, we will first clean up the traces,

// Delete a user

Drop user xxxx cascade;

// Delete the tablespace

Drop tablespace xxxx;

// Delete database files

E:/xxxxxx. dbf

Third, after the preparation is complete, we can begin to restore it.

// Create a user

Create user Username identified by password DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP

// Grant User Permissions

Grant connect, resource, dba to xxxx

// Create a tablespace and specify the file name and size

Create smallfile tablespace "xxxx" DATAFILE 'e:/ORADATA/ORCL/xxxx. dbf'

SIZE 100 M

Autoextend on next 100 M

MAXSIZE UNLIMITED

LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO

// Execute grant.txt to grant the privilege to the created user

// Grant permissions

Grant create user, drop user, alter user, create any view,

Drop any view, EXP_FULL_DATABASE, IMP_FULL_DATABASE,

DBA, CONNECT, RESOURCE, create session to xxxx

// Start the import (full import). file: Location of the dmp file. ignore: because some tables already exist, this table will not be imported.

Add ignore = y. Specify the log file log = e:/log.txt

Imp user/pass @ orcl full = y file = e:/xxx. dmp ignore = y log = e:/log.txt

// When we do not need to completely restore the database, we can restore a specific table separately.

//---------------------------------------------------------------------------

Imp user/pass @ datbase file = e:/xxx. dmp ignore = y log = e:/log.txt tables = (xxxx)

Imp user/pass @ database file = e:/xxx. dmp ignore = y log = e:/log2.txt tables = (xxxx)

//---------------------------------------------------------------------------

// Here we have completed the restoration of the database. Now we can open isqlplus to view the data in the table.

Select * from ***

Fourth, let's take a look at common oracle operation commands.

1) view the properties of a tablespace

Select tablespace_name, extent_management, allocation_type from dba_tablespaces

2) query the columns in a table and the column names and Data Types of the column.

Select TABLE_NAME, COLUMN_NAME, DATA_TYPE from user_tab_columns where TABLE_NAME = 'xxxx'

3) Search for the user table in the tablespace

Select * from all_tables where owner = 'xxx' order by table_name desc

4) number of tables under a specified user

Select count (*) from user_tab_columns

5) view the table name, table column, and all columns in the database.

Select TABLE_NAME, COLUMN_NAME, DATA_TYPE from user_tab_columns order by table_name desc

6) view all the table names of the ZBFC user and the tablespaces in the table.

Select table_name, tablespace_name from all_tables where owner = 'xxxx' order by table_name desc

7) generate the text for table Deletion

Select 'drop table' | table_name | ';' from all_tables where owner = "ZBFC ";

8) Delete table cascading Deletion

Drop table table_name [cascade constraints];

9) Search for columns in a table

Select TABLE_NAME, COLUMN_NAME, DATA_TYPE from user_tab_columns where column_name like '%' | 'di' | '%' order by table_name

Desc

10) view the temporary space of the database

Select tablespace_name, EXTENT_SIZE, current_users, total_extents, used_extents, MAX_SIZE, free_extents from v $ sort_segment;

Https://download1.csdn.net/down3/20070615/15202338310.txt

During the import process, another error occurs: The data violates the uniqueness constraint and the database rejects the data.

IMP-00019: Row reject due to ORACLE 1 error

Someone said on the Internet that this is the reason for the character set, so I created a database and changed the character set to ZHS16GBK. After a new import, the result was successful.