1. Check data character set:
Generally, when Oracle is installed, ZHS16GBK is selected by default. If there is any change, select userenv (‘language ‘) from dual; statement is used to check the character set used to ensure that the character sets on both sides are consistent.
In the case of inconsistency, when importing data, it may return to the situation that the length of the field becomes longer, leading to data writing failure, or even garbled.
2. Create a table space:
When creating a table space, you can create it according to the size of the file you need to import
Method 1: directly give the table space with the size of imported data (my nearly 60 g)
Syntax: create tablespace name datafile ‘data file name’ size tablespace size
Example: create tablespace atmcs_ 01 datafile ‘E:\APP\ADMINISTRATOR\ORADATA\ORCL\ATMCS_ 01.bdf’ size 30000m;
Supplementary note: there is an upper limit for the size of the table space, and each file is no more than 32GB (the exact value is 32768m). Therefore, when the data to be imported is greater than the maximum table space value, we can use the following statement:
alter tablespace ATMCS_ 01 add datafile ‘E:\APP\ADMINISTRATOR\ORADATA\ORCL\ATMCS_ 02. BDF ‘size 30000m; add the table space size
From the above statement, it is not difficult to find that the table space requirements are consistent, the file name is not the same name, and the additional size is determined according to their own needs. If it’s not enough, add more.
Method 2: self growing table space
Syntax: create tablespace name datafile ‘data file name’ size initial size AUTOEXTEND on next size maxsize unlimited
Example: create tablespace atmcs_ 01 datafile ‘E:\APP\ADMINISTRATOR\ORADATA\ORCL\ATMCS_ 02.bdf’ size 200m autoextend on next 10m maxsize unlimited
Supplementary note: from the statement point of view, it is the maximum unlimited, in fact, it is not more than 32GB. I used to use self growth table space to have insufficient table space before, so I simply opened it to the maximum.
3. To create a user specified table space:
Syntax: create user user name “identified by password” default tablespace name;
Example: create user test identified by admin123456 default tablespace atmcs_ 01;
4: User authorization:
Grammar: Grantconnect,resource,dba To user name;
Example: Grantconnect,resource,dba to TEST ；
Import method 1:
Win + R wake up CMD directly
Syntax: imp user name / password @ service name file =’dmp file address’log =’import log storage address’full = y ignore = y
Example: imp test/ [email protected] file=”D:\data\oracle\2020-11-27.dmp” log=”D:\data\oracle\ test.log ” full =y ignore=y;
Import method 2:
Using PLSQL, Tools > Import tables > select executable file > select import file in the lower right corner > click Import
When you select an executable file, the executable file is on the installation disk: APP / administrator / product / 11.2.0 / dbhome_ 1\bin\ imp.exe In the middle, my
It’s in D: app, administrator, product, 11.2.0, dbhome_ 1\bin\ imp.exe
When entering the import table, you can select by default, unless you have your own special requirements, such as no longer need the constraint relationship, you can cancel the check
Supplementary note: the two methods are consistent in principle. PLSQL is relatively stable, and some permission problems will be removed. In more cases, you need to try it yourself. No matter whether all of the above is true or not, remember to come back to comment and share with yourself
Oracle data when the experience or problems.
End of import:
During the import process, we will encounter various problems, such as imp-00017: due to Oracle error 2153, imp-00017: due to Oracle error 959… And so on. In a word, we can solve whatever problems we encounter, if we don’t ask
Record and analyze some strange knowledge.