Oracle Database FAQ Summary

This article introduces a summary of common Oracle Database problems and summarizes some problems and solutions when using oracle databases. For more information, see.

1. When PLSQL connects to the database, it prompts that the listener cannot identify the Service requested in the current listener descriptor.

Modify listener. ora:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = F: oracleproduct10.2.0db _ 1)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(ORACLE_HOME = F: oracleproduct10.2.0db _ 1)
(SID_NAME = orcl)
) After the listener is configured here,
 
)
 
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = 127.0.0.1) (PORT = 1521 ))
)
)
Save the file and restart the listening service TNSListener!

Reason: Relationship between active and passive
1. Let listener actively load the service
The reason is to add
(SID_DESC =
(SID_NAME = orcl)
(ORACLE_HOME =/data/cache1/oracleDB/oracle/product/10.2.0/db_2)
)
When the lsnrctl start listener is used, the listener service is registered to the Process Monitor (pmon ).
2. listener passive loading Service
If this content does not exist, the pmon process of the Instance registers the service in the listener, Which is passive to the listener.
This is why the database can be connected normally after the listener is started, and vice versa.

2. PLSQL startup prompts inconsistent encoding and error messages

Database character set (AL32UTF8) and Client character set (ZHS16GBK) are different. Character set conversion may cause unexpected results.

Note: you can set the client character set through the NLS_LANG environment variable or the NLS_LANG registry key in HKEY_LOCAL_MACHINESOFTWAREORACLEHOME0.

Open the registry and run the regedit command. Find the path prompted in the text and find that the value of the NLS_LANG key is:

SIMPLIFIED CHINESE_CHINA.ZHS16GBK

Modify it to: SIMPLIFIED CHINESE_CHINA.AL32UTF8.

3. When you connect to sqlplus, the system prompts "the database is not opened"

If you execute alter database open, the system also prompts that the database is not opened or that dbf and pnc files are lost.

Error cause: Data File deleted

Solution:

Alter database datafile 'data file path' offline drop;

Alter database open;

If the current log file is deleted, it can only be restored through archiving logs. If the archive log is destroyed, it can only be reinstalled.

4. ORA-04091: table is mutating, trigger/function may not see it analysis and solutions

Error cause
This error is often encountered when writing a trigger. The root cause is the operation on this table. oracle db locks the table when writing a TRIGGER by default and does not allow operations on it. When updating a table T, when the trigger body or trigger calls a stored procedure to query the update table, this error is often encountered.

Solution
Use autonomous things to solve the problem.
The concept of autonomous things: Committing things in subprogram does not affect the transactions of the main program. Similarly, the commit or rollback of the main program does not affect the commit of the subprogram, that is, the sub-program and the main program are completely independent.
Example:
SQL> CREATE TABLE T (ID NUMBER (18), MC VARCHAR2 (20), DT DATE );
The table has been created.

SQL> CREATE OR REPLACE TRIGGER TR_T
2 AFTER DELETE ON T
-- Referencing old as old_value save old value
-- New as new_value: Save the new value.
3 FOR EACH ROW
4 DECLARE V_COUNT NUMBER;
5 -- PRAGMA AUTONOMOUS_TRANSACTION;
6 BEGIN
7 insert into t values (: OLD. ID,: OLD. MC, SYSDATE );
8 COMMIT;
9 END TR_DEL_CABLE;
10/

Trigger created

SQL> INSERT INTO T VALUES (1, '20170101', SYSDATE );
One row has been created.

SQL> INSERT INTO T VALUES (2, '20170101', SYSDATE );
One row has been created.

SQL> COMMIT;
Submitted.

SQL> SELECT ID, MC, TO_CHAR (DT, 'yyyymmdhh24: MI: ss') FROM T;
Id mc TO_CHAR (DT, 'yyyym
-----------------------------------------------
1 111111 20080802 11:07:36
2 222222 20080802 11:07:43

SQL> DELETE FROM T WHERE ID = 1;
Delete from t where id = 1
*
Row 3 has an error:
ORA-04091: The table TEST. T has changed and trigger/function cannot read it
ORA-06512: In "TEST. TR_T", line 4
ORA-04088: An error occurred while executing the trigger 'test. TR_T'

SQL> SELECT ID, MC, TO_CHAR (DT, 'yyyymmdhh24: MI: ss') FROM T;

Id mc TO_CHAR (DT, 'yyyym
-----------------------------------------------
1 111111 20080802 11:07:36
2 222222 20080802 11:07:43

SQL> CREATE OR REPLACE TRIGGER TR_T
2 AFTER DELETE ON T
3 FOR EACH ROW
4 DECLARE V_COUNT NUMBER;
5 PRAGMA AUTONOMOUS_TRANSACTION;
6 BEGIN
7 insert into t values (: OLD. ID,: OLD. MC, SYSDATE );
8 COMMIT;
9 END TR_DEL_CABLE;
10/

Trigger created
SQL> DELETE FROM T WHERE ID = 1;
One row has been deleted.
SQL> COMMIT;
Submitted.
SQL> SELECT ID, MC, TO_CHAR (DT, 'yyyymmdhh24: MI: ss') FROM T;

Id mc TO_CHAR (DT, 'yyyym
-----------------------------------------------
2 222222 20080802 11:07:43
1 111111 20080802 11:08:32
(Note: Some people said that this type of error can be avoided by placing the value of new old in the local variable or package variable, but I have tried a lot and still throw this error, therefore, it is not feasible to save the value to the method in the Variable .)

Root Solution
First, autonomous transactions must be used with caution, because a DML may produce many independent transactions, which can easily lead to deadlocks. ASKTOM's opinion on AUTONOMOUS_TRANSACTION is: the only purpose is to audit logs, none of them should be used.
The root solution is to completely abolish trigger and put the corresponding processing logic into the stored procedure.

5. sysdba users cannot access the system

If you are a system administrator and belong to ORA_DBA in the computer management user group, you can directly type sqlplus/as sysdba in the command line to enter the system

If there are multiple instances on the machine, you may not be able to enter them as an administrator. set ORACLE_SID = instance SID

If the sqlplus command is not recognized, you need to set the environment variable. Generally, this problem does not occur. You can view echo % PATH

On Windows 7, oracle Installation adds an SQL command line menu item at the beginning, without the command line editor or sqlplus service item. You can call up the command line editor by searching cmd, but sqlplus does not recognize