What to do if ora-12899 error occurs in Oracle database

In the process of using ORACLE, various problems and errors will occur. Among them, ORA-12899 is the problem that kept appearing when I imported data to my local machine some time ago. But fortunately, this problem has been solved. Now I will share it, hoping to help everyone.

ORA-12899 appears, which is caused by the character set. Chinese occupies 3 bytes in UTF-8 and 2 bytes in ZHS16GBK. The character set of the source dmp file is poured from the ZHS16GBK library. The data now needs to be imported into a library whose target character set is UTF-8, so ORA-12899 will appear

In fact, this problem can be solved well by modifying the character set of ORACLE;

But when modifying the character set, I found that I forgot the password of the sys account, which was tragic, so I had to change the password of the sys user first.

Change Password There are several situations, and I will list them one by one below:

1. Forgot the login password of users other than SYS and SYSTEM users.

Log in as SYS (or SYSTEM) user.

CONN SYS/PASS_WORD AS SYSDBA;

Use the following statement to modify the user's password.

ALTER USER user_name IDENTIFIED BY newpass;

Note: The password cannot be all numbers. And it cannot start with a number. Otherwise it will appear: ORA-00988: Password is missing or invalid

2. Forgot the password of the SYS user or the SYSTEM user.

If you forget the password of the SYSTEM user, you can log in as the SYS user. Then use ALTER USER password

to change the password.

CONN SYS//PASS_WORD AS SYSDBA; 
ALTER USER SYSTEM IDENTIFIED BY newpass;

If you forget the password of the SYS user, you can log in as the SYSTEM user. Then use ALTER USER password

to change the password.

CONN SYSTEM//PASS_WORD ; 
ALTER USER SYSTEM IDENTIFIED BY newpass;

3. If the passwords of the SYS and SYSTEM users are forgotten or lost.

This item is particularly important.

You can use the ORAPWD.EXE tool to change the password.

Start menu->Run->Enter 'CMD', open the command prompt window, enter the following command:

orapwd file=D:/oracle/product/10.2.0/db_1/database/pwdctcsys.ora 
password=newpass

This The command regenerates the database password file. The location of the password file is in the /database directory in the
directory of ORACLE_HOME.

This password is to modify the password of the sys user. The passwords of other users except sys and system will not be changed.

After changing the password, you can make changes as a dba. If you are not a dba, you will be prompted for insufficient permissions when executing the modification command.

Start-->Run -->cmd, then enter: "sqlplus sys/oracle@192.168.0.1/orcl as SYSDBA"

If you cannot enter normally, you need to manually enter the user name and password. After entering, the "SQL>" prompt will appear. Just execute the commands given below.

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 ;
ALTER DATABASE CHARACTER SET ZHS16GBK
*ERROR at line 1:
ORA-12712: new character set must be a superset of old character set

reports that the character set is incompatible. At this time, the next The INTERNAL_USE instruction does not check the character set superset:

SQL>ALTER DATABASE CHARACTER SET INTERNAL_USE ZHS16GBK;
SQL>SHUTDOWN IMMEDIATE
SQL>STARTUP

At this time, the ORA-12899 has been completely resolved, and the data can be imported. ..

PS: Let me add something to you: What is the *.ora file in oracle used for?

Why is there no *.ora file in 10g?

Is *.ora in 9i equal to *.dbf in 10g?

When creating the table7b3ca52bd248931a27c60ea469b9af68space5db79b134e9f6b82c0b36e0489ee08ed

DATAFILE is used to specify the specific location and size of the data file. But I saw some articles using

*.ora files, such as DATAFILE 'D:\\ORACLE\\ORADATA\\ORA92\\LUNTAN.ora' SIZE 5M , some

Use *.dbf file, such as DATAFILE 'D:\\ORACLE\\ORADATA\\ORA92\\LUNTAN.dbf' SIZE 5M .

The online saying is .dbf-data file, .tmp-temporary file, .log-redo log file (redo log file), .ctl-control file

. ora-parameter file, .dat-Oracle system file

just identifies the file type through the extension. For the data file, whether it is ora/dat/dbf, it is the same, there is no difference. . I personally think so too. I wonder what everyone’s opinions are?

Related recommendations:

How Oracle changes the data file location of the table space

Mysql and Oracle database stop and start Batch file

#Oracle Clob field is too long and save error. How to solve it?

The above is the detailed content of What to do if ora-12899 error occurs in Oracle database. For more information, please follow other related articles on the PHP Chinese website!