Common error handling for Oracle Import and export data

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 for 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
Linux under:
Echo $NLS _lang
Under Windows:
Echo%nls_lang%
To set the client character set encoding:
Linux under:
# Common Unicode character sets
Export Nls_lang=american_america. Al32utf8
# Common Chinese Character set
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 Bash_profile settings take effect
source. bash_profile
Under Windows:
# Common Chinese Character set
Set Nls_lang=simplified Chinese_china. Zhs16gbk
# Common Unicode character sets
Set Nls_lang=american_america. Al32utf8
Can be permanently set by modifying registry key values
Hkey_local_machinesoftwareoraclehomexxnls_lang

To modify the server-side character set:
Sql>conn/as Sysdba;
If the database server is already started, execute the SHUTDOWN IMMEDIATE command to close the database server, and then execute 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 is a superset of old character set
Tip Our Character set: The new character set must be a superset of the old character set, at which point we can skip the check of the superset to make changes: use Internal_use to skip the check of the superset
Sql>alter DATABASE CHARACTER SET internal_use ZHS16GBK;

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

If you report the above error: select Sid,serial#,username,machine from V$session; see which sessions are available, and then
Alter system kill session ' SID ' serial# ' immediate;
Alter system kill session ' 158,7′immediate;
Sql>alter DATABASE CHARACTER SET internal_use ZHS16GBK;
Sql>shutdown IMMEDIATE;
sql>startup;

Error 2: Insert field contains & (security) symbol

Method One:
Sql> show define;
Define "&" (Hex 26)
Sql> set define off;
Sql> show define;
Define off
This is the setting that Oracle uses to identify custom variables, and to close them under Sql*plus.
Note: If you are executing in toad, it is recommended that you turn off define in the first line of each script you want to import, or else you will get an error when you import the second script that contains special characters.
If you are executing in sql*plus, you only need to set the define off once, and then you can import it continuously. Until you reset define on.
Method Two:
Replace ' & ' in SQL statement with CHR (38) because Chr (38) is an ASCII code for ' & '
sql> Select ' Tom ' | | Chr (38) | | ' Jerry ' from dual;
Method Three:
Split the original string
sql> Select ' Tom ' | | ' & ' | | ' Jerry ' from dual;

Error 3: Insert field contains ' (single quote) symbol

Method One: Use escape characters
SQL > Select ' Test ' | | "" from dual;
Note: What do you mean by "four single quotes" here? First and last are string connectors in Oracle, which is not disputed. So what does the second ' and third ' mean? The second ' is an escape character
The third ' is what we really are.
Method Two:
It's also using escape characters, except in different ways.
SQL > Select ' Test ' from dual;
Note: The second, third, here is the escape character and the real content that we mentioned in method one above
Method Three:
In SQL, ' Replace with Chr (39) because Chr (39) is ' the ASCII code
SQL > Select ' It ' | | Chr (39) | | ' Fine ' from dual;

Error 4:ora-00001:unique constraint violated

Primary keys have uniqueness constraints and cannot repeat the same values.

Import Export Exp/imp (including special fields IMP error handling)

Exp:

Get help EXP Help=y

Full export: full=y

Scheme Export owner= (System,sys,.....)

Table Export tables= (a,b,....)

Query Results Export

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

Table structure contains no data, index ...

Rows=n triggers=n index=n Statistics=none

.......

IMP

Full Export

Full=y Ignore=y

User-owned data import another user

FROMUSER=U1 TOUSER=U2

Table Import

FROMUSER=U1 tables= (a,b,....)

When importing data, an error occurs when special fields such as LOB are included in the table

"I can't find the xxx space ... ..."

Solution:

1. Use PL SQL to export user ' S OBJECT sql file modified tablespace xxx;

2. Execute the SQL file in the build table structure.

3. Import data through imp again.

IMPDP Import data Error ora-14102

Import statement: IMPDP username/******** @orcl remap_schema=hpchss:lwchss remap_tablespace=hpchss:lwchss remap_tablespace= Lwchss_indx:lwchss_idx directory=hyhp dumpfile=hp_2013-11-07.dmp logfile=phis.log
Error message:
Working with Object types SCHEMA_EXPORT /table/index/index
ORA-39083: Object type INDEX creation failed with error:
ORA-14102: Can only specify one LOGGING or nologging clause
failed sql:
C reate UNIQUE INDEX "Lwchss". Pk_physicalexamination_roster "on" Lwchss "." Phealth_physicalexam_roster "(" ID ") PCTFREE 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" P Arallel 1

ORA-39083: Object type CONSTRAINT creation failed, error occurred:
ORA-14102: Only one LOGGING or nologging clause can be specified
The failed SQL is:
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 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

Solutions

Take the two SQL, remove nologging or logging, and perform it again under Sqlplus.

In addition, your IMPDP, two remap_tablespace, can be written together, separated by a half-width comma, remap_tablespace=hpchss:lwchss,lwchss_indx:lwchss_idx