Troubleshooting of common oracle data import and export errors

Today, the ORA-01756: quoted string not properly terminated error occurred while importing data using volume El. Now I 've sorted out some solutions that are commonly used in oracle.

Error 1: ORA-01756: quoted string not properly terminated

Chinese content cannot be inserted due to inconsistent character set encoding
Query the character encoding settings of the current database:
SQL> select * from v $ nls_parameters T WHERE t. PARAMETER = 'nls _ language' or t. PARAMETER = 'nls _ CHARACTERSET ';
Or
SQL> select userenv ('language') from dual;
Query client character set encoding
In linux:
Echo $ NLS_LANG
Windows:
Echo % NLS_LANG %
Set the client character set encoding:
In LINUX:
# Common unicode character sets
Export NLS_LANG = american_america.AL32UTF8
# Common Chinese character sets
You can edit the bash_profile file for permanent settings.
Vi. bash_profile
Export NLS_LANG = "SIMPLIFIED CHINESE_CHINA.ZHS16GBK"
Or export NLS_LANG = "Simplified Chinese_china". ZHS16GBK
# Make the bash_profile settings take effect
Source. bash_profile
Windows:
# Common Chinese character sets
Set NLS_LANG = SIMPLIFIED CHINESE_CHINA.ZHS16GBK
# Common unicode character sets
Set NLS_LANG = american_america.AL32UTF8
You can change the registry key value to set permanently.
HKEY_LOCAL_MACHINESOFTWAREORACLEHOMExxNLS_LANG

Modify the server character set:
SQL> conn/as sysdba;
If the database server has been started, run the shutdown immediate command to shut down the database server, and then run the following command:

SQL> shutdown immediate;
SQL> STARTUP MOUNT;
SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;
SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES = 0;
SQL> ALTER SYSTEM SET AQ_TM_PROCESSES = 0;
SQL> ALTER DATABASE OPEN;
SQL> ALTER DATABASE CHARACTER SET ZHS16GBK;
ORA-12712: new character set must be a superset of old character set
Prompt our character set: the new character set must be the superset of the old character set. In this case, we can skip the superset check and make changes: INTERNAL_USE is required to skip the superset check.
SQL> ALTER DATABASE CHARACTER SET INTERNAL_USE ZHS16GBK;

ERROR at line 1:
ORA-12721: operation cannot execute when other sessions are active

If the preceding error is reported, select sid, serial #, username, machine from v $ session;
Alter system kill session 'sid' serial # 'immediate; then
Alter system kill session '192, 7' immediate; as shown in the following figure:
SQL> ALTER DATABASE CHARACTER SET INTERNAL_USE ZHS16GBK;
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP;

Error 2: The inserted field contains the amute symbol.

Method 1:
SQL> show define;
Define "&" (hex 26)
SQL> set define off;
SQL> show define;
Define OFF
This setting is used in Oracle to identify User-Defined variables and should be disabled under SQL * PLUS.
NOTE: If it is executed in TOAD, we recommend that you disable define in the first line of each script to be imported, otherwise, an error occurs when you import the second script containing special characters.
If it is executed in SQL * PLUS, you only need to set define OFF once, and then you can import it continuously. Until you reset define ON.
Method 2:
Replace '&' with chr (38) in an SQL statement because chr (38) is the ASCII code '&'.
SQL> Select 'Tom '| chr (38) | 'Jerry' from dual;
Method 3:
Split the original string
SQL> Select 'Tom '|' & '| 'Jerry' from dual;

Error 3: The inserted field contains the '(single quotation mark) symbol.

Method 1: Use escape characters
SQL> Select 'test' | "from dual;
Note: What are the meanings of the "" four single quotes? First, the first and last are character strings in Oracle, and there is no objection. So what does the second and third sign mean? The second 'is an escape character.
The third is our real content.
Method 2:
Escape characters are also used, but in different ways.
SQL> Select 'test "'from dual;
Note: The second and third are the escape characters and real content mentioned in method 1 above.
Method 3:
Replace 'with chr (39) in SQL, because chr (39) is 'ascii code
SQL> Select 'it' | chr (39) | 'fine 'from dual;

Error 4: ORA-00001: unique constraint violated

The primary key has a unique constraint and cannot insert the same value repeatedly.

Import and Export EXP/IMP (containing special field imp error handling)

EXP:

Get help EXP help = y

Full export: full = y

Solution export owner = (system, sys ,.....)

Table export tables = (a, B ,....)

Export query results

Tables = (a) query = "where filed = 'xxxx '"

Table structure does not contain data, index ......

Rows = n triggers = n index = n statistics = none

.......

IMP

Complete Export

Full = y ignore = y

Import user data to another user

Fromuser = u1 touser = u2

Table Import

Fromuser = u1 tables = (a, B ,....)

When importing data, an error is reported if the table contains special fields such as LOB.

"XXX space not found ..........."

Solution:

1. Use pl SQL to export the SQL file of USER's OBJECT and modify tablespace xxx;

2. Execute the SQL file to generate the table structure.

3. import data through imp.

Impdp import data error ora-14102

Import Statement: impdp username/******** @ orcl remap_schema = hpchss: lwchss remap_tablespace = lwchss_indx: lwchss_idx directory = hyhp dumpfile = HP_2013-11-07.DMP logfile = phis. log
Error message:
Processing object type SCHEMA_EXPORT/TABLE/INDEX
ORA-39083: object type INDEX creation failed with error:
ORA-14102: Only one LOGGING or NOLOGGING clause can be specified
Failed SQL:
Create unique index "LWCHSS ". "PK_PHYSICALEXAMINATION_ROSTER" ON "LWCHSS ". "partition" ("ID") PCTFREE 10 INITRANS 2 MAXTRANS 255 nologging nocompress logging storage (INITIAL 65536 FREELISTS 1 freelist groups 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT defaults) TABLESPACE "partition" PARALLEL 1

ORA-39083: Object Type CONSTRAINT creation failed with error:
ORA-14102: Only one LOGGING or NOLOGGING clause can be specified
Failed SQL:
Alter table "LWCHSS ". "REPORT_ORG_WORKER_EDU" add constraint "PK_FACT_ORG_WORKER_EDU" primary key ("REPORT_DATE", "ORG_ID", "EDU_ID ") using index pctfree 10 INITRANS 2 MAXTRANS 255 nologging nocompress logging storage (INITIAL 65536 FREELISTS 1 freelist groups 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "LWCHSS_IDX" ENABLE

Solution

Remove the two sqls from nologging or logging and run them again under sqlplus.

In addition, your impdp and two remap_tablespace can be written together and separated by commas. remap_tablespace = hpchss: lwchss, lwchss_indx: lwchss_idx