Oracle Database FAQ Summary

1. Plsql Connection database prompt listener does not recognize the service currently listening to the descriptor request

Modify Listener.ora to:
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 you configure this to listen,

)

LISTENER =
(Description_list =
(DESCRIPTION =
(address = (PROTOCOL = TCP) (HOST = 127.0.0.1) (PORT = 1521))
)
)
Save the file, and then restart the listening service Tnslistener on it!

Reason: active and passive relationship
1. Is let listener active load service
Reason is to add
(Sid_desc =
(Sid_name = ORCL)
(Oracle_home =/data/cache1/oracledb/oracle/product/10.2.0/db_2)
)
, the Listener service is registered to the Process Monitor (Pmon) when the Lsnrctl start listener is used
2.listener Passive Load Service
If there is no such content, then by the instance of the Pmon process in the Listener Registration Service, for listener speaking, is passive.
This is why the start of the monitoring after the start of the database can be normal connection, the other is not the reason.

2, Plsql start prompt coding inconsistency problem, error hint

The 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 key in registry LOCAL_MACHINESOFTWAREORACLEHOME0.

Open the registry and regedit by command. Found the path to the hint in the text, found that the value of the Nls_lang key is:

Simplified Chinese_china. Zhs16gbk

Modified to: Simplified Chinese_china. Al32utf8 can

3, connection Sqlplus Prompt "Database not open"

Execute ALTER DATABASE Open also prompts the database not to open, or prompts DBF,PNC file to be lost

Error Reason: Data file was deleted

Solution:

ALTER DATABASE datafile ' data file path ' offline drop;

ALTER DATABASE open;

If the current log file is deleted, it can only be recovered through the archive log, and if the archive log is destroyed, it can only be mounted.

4, Ora-04091:table is mutating, trigger/function can not, it analysis and solutions

The cause of the mistake
This error is frequently encountered in writing trigger, and the root cause is due to the operation of this table. In ORACLE DB, the default is to lock this table when writing trigger, not allow to operate it, when the update of a table T, in trigger body or trigger called stored procedures and the Update table query, which often encounter this error.

Solutions to the Symptoms
The use of autonomous things to solve.
The concept of autonomous things: that is, in the subprogram of the delivery of things do not affect the main program's transactions, the same main program of the submission or rollback does not affect the subroutine commit, that is, the subroutine of things and the main program of things completely independent.
Example:
Sql> CREATE TABLE T (ID number (), MC VARCHAR2 (), DT DATE);
Table has been created.

sql> CREATE OR REPLACE TRIGGER tr_t
2 after DELETE on T
--referencing old as Old_value saving legacy values
--new as New_value save 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 has been created

Sql> INSERT into T VALUES (1, ' 111111 ', sysdate);
1 lines have been created.

Sql> INSERT into T VALUES (2, ' 222222 ', sysdate);
1 lines have been created.

Sql> COMMIT;
Submit completed.

Sql> SELECT Id,mc,to_char (DT, ' YYYYMMDD HH24: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
*
Line 1th Error:
ORA-04091: Table TEST. T has changed, the trigger/function cannot read it
ORA-06512: In "test.tr_t", line 4
ORA-04088: Error during trigger ' test.tr_t ' execution

Sql> SELECT Id,mc,to_char (DT, ' YYYYMMDD HH24: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 has been created
Sql> DELETE from T WHERE id=1;
1 rows have been deleted.
Sql> COMMIT;
Submit completed.
Sql> SELECT Id,mc,to_char (DT, ' YYYYMMDD HH24:MI:SS ') from T;

ID MC to_char (DT, ' Yyyym
---------- -------------------- -----------------
2 222222 20080802 11:07:43
1 111111 20080802 11:08:32
(Note: It was previously said that you can avoid this type of error by putting the value in the new old into a local variable or a package variable, but I've tried a lot, and still throw it, so it's not feasible to save the value in a variable.) ）

A radical solution
First, autonomous transactions must be used with caution, because a DML may produce a number of independent things, which can easily trigger deadlocks, Asktom on Autonomous_transaction's view: The only use is to audit the log, other should not use.
The radical solution is to abolish trigger completely and put the corresponding processing logic into the stored procedure.

5, SYSDBA users can not access the system

If you are a system administrator and belong to ORA_DBA in the Computer Management user group, you can type Sqlplus/as SYSDBA into the system directly at the command line

If you have more than one instance on your machine, you may not get in as an administrator, you need to set the set oracle_sid= instance SID

If the Sqlplus command is not recognized, you need to set the environment variable, which is generally not a problem and you can view the Echo%path

WINDOW7 Oracle installation will be in the beginning of a number of SQL command-line menu items, no command-line editor, no Sqlplus service items, you can find cmd to pull up the command line editor, but Sqlplus will not recognize