HIGH SCORE!! Problems with Oracle data import. 10

1. You have installed Oracle, of course, you have to build a database.

2. It is recommended that you create a user tablespace, of course, you can also put it in the system tablespace, but no one does this.

3. It is recommended to create a user to point to this tablespace, so that the data you import is directly placed under this user, of course, you have to assign permissions to this user, of course, you can also use system and the like, of course, don't do that.

After that you can import it.

imp username password @server file='Path + file name'touser=export username fromuser=import username.

As for exporting, it's up to you to do it, but anyway, the easiest way is to export it in its entirety, just add full=y.

First of all, you don't have to use full mode, you just need to export the data of the users on your server.

When it is imported to your computer, if you have already installed oralce, you can create a new database if you don't have a database, and if you have it, you don't need it.

Then create a tablespace, since there are more than 5G of data, just create a tablespace of about 10G, (if the data is a binary field table, the tablespace name should be the same as on the server.) )

Next, create a user, specify the default tablespace as the one you just created, and finally, import it.

User tablespaces and temporary tablespaces must be created.

DMP file import:

1.Log in to the database (Enterprise Manager Console, the Oracle client) as DBA with the SYS username

2.In Security --User, if the user object you want to import already exists, then remove it, and then create a new one (so that you can delete all the objects under the user), and select the original tablespace.

3.Add the DBA role to the user's role.

4.Run the imp aioss aioss@orcl10g file=d: full=y ignore=y commit=y command line

IMP doesn't have an operation interface.,If you want to use a graphical interface,,You can use third-party software.,Relatively,I think the object browser out of Japan is a little better.,Now it's out to version 10.,The above export and import function is also a reference to Oracle's IMP EXP module.,But it's the interface operation.,You can set parameters under the prompt.。

Password @sid number of the imp system user fromuser=the username of the exported data file touser=the user of the database to be imported file=the full path of the file (drag-and-drop is supported here) log=the absolute path of the imported log file.

This is the import command below the command prompt.

Or use the Import Table in the PL SQL Developer tool, select Oracle Import, and select the database file point at the bottom.

It is very convenient to use the PL SQL Developer tool. This is also a common tool used by oracles for maintenance.

imp ss/ss123@jhr full=y file=e:\dbku\ ignore=y log=e:\ljhrservice\dbku\201102202218\;

This is what I used when I used to import it, and I execute this under the cmd command!

c:\documents and settings\hflj>imp ss/ss123@jhr full=y file=e:\dbku\201102202218

dmp ignore=y log=e:\ljhrservice\dbku\201102202218\;

Importing is definitely much slower than exporting.

You can set the buffer parameter or increase the temp and undo tablespaces. Add a feedback parameter to display one for every 10,000 rows"."

imp user/pass@orcl full=y file=c:\ ignore=y buffer=8000000 commit=y feedback=10000

There is a problem with the tns configuration.

sqlplus yingxin 123456@ccpa try to log in.

tnsping ccpa see if it passes.

ORA-12154: tns: Unable to resolve the specified connection identifier Unable to connect to database SQLPuing Yingxin 123456@ccpa Try it.

Use sqlplus to see if the connection is successful.

Cause: The DB block size of the exported database is 4k, and the default DB block size of the imported database is 8k, causing the import failure.

Solution: alter system set db 4k cache size=60n

Try re-importing.

The block size of the tablespace of your exported library is 4k, and the default block size of your new library is not 4k (i.e., there is no buffer pool of 4k).

You can take a look at the parameters of the new library: sql>show parameter block size

The easiest way to do this is:

Manually create a tablespace with the same name in the new database, and then re-import the data.

V test might be a view. Use the statement select * from user views; Check to see if it exists.

See if the user you're logged in with is the same.

Look at the amount of data.

1.If the data exceeds 10w. Just add a marker. to determine whether it has been transmitted.

For example, if you add a trans flag column, 0 means no change, 1 means insert, and 2 means update

2.If it doesn't exceed 10W....Then use merge....into...Statement of it. For specific usage, please refer to it.

Make sure that King 123456@orcl zhenshi can log in successfully.

If it is not successful, modify it first and then import it.

If successful, check if there is an error in generating a log. imp king/123456@orcl_zhenshi file=f:\ log=f\

Post the error, and another: This has nothing to do with whether you have a library or not, an instance name, you can import a library of multiple different users, and you can also create multiple instances.

In this way, post the mistake first and let everyone see it.

Do not use the full=y argument in the imp command, create a new user newking in the local database, and name it as you like, as long as it is distinguished from king.

Try it like this.

imp userid=newking/123456@orcl_zhenshi file=f:\ grants=y rows=y ignore=y fromuser=king touser=newking statistics=none compile=y

A few people upstairs are right, post the error in the report, the more detailed the better, in order to locate the error.

Post the error message and take a look.

Your VM only has a C drive, but the data files in your tablespace are written to a D drive.

How the tablespace was successfully created.

The command used by the landlord is to import the whole database, and the error is full=y when you check the error exp, when you imp, you don't need the same tablespace, but the imported user must be the same, that is, what are the users of the original database (referring to the database user excluding the system user), the new library must have, and the above error can be ignored.

If the same user is created, IMP should be able to import data to the corresponding user.

In PL SQL, select * out the data of a table and press alt+end;

Then right-click on the table and it looks like mysql doesn't work. Copy to excel, then there is an excel, then open excel, delete the first column, and you're good to go.

How Do I Import MySQL Data to Oracle?

1. Import the data in the table into a text file for saving.

I did it in command line mode under win.

mysql>select * from my_mysql_table into outfile 'c: /my_';

2. Write control files.

Use Notepad to open a document and change the extension name after writing. CTL, here's what it looks like:

load data

infile 'c:/my_'

into table my_ora_table

replace

fields terminated by x'09'

Field 1, Field 2, Field 3 ,..field n).

Field 1 n is the table field data of MySQL that you want to import, and the fields that you don't need can be left unwritten.

3. Use sqlLDR to import text.

c:>sqlldr ora_usr

After entering, enter the path and password of the control file, and then load the control file and import it automatically. After the import is complete, a copy will be generated. log file, you can check whether your import is successful, and find the reason according to the error prompted.

Note: Before importing text, you should build a table in the oracle database to write data in advance, and it should be consistent with the data field to be imported, if you don't know the table structure of mysql, you can show create table my mysql table > mysql; View the SQL statement that creates the table and convert it to an executable SQL statement in Oracle

Our project team once poured oracle data into db2, no DBA, no tools, is to write a data migration program, the general steps are as follows:

1. According to the data dictionary of both databases, list the required fields 2, write the corresponding migration program, the main attention is that the oracle data type should be converted accordingly, transferred to db2, and some useless data when pouring out is deleted, so that the imported data is meaningful!

3. Extract data for verification!

For your reference, if the amount of data is not large, you can use the data of the table to pour the data into an sql file and then execute!