21. Replace Distin with EXISTS
When submitting a query containing a pair of multi-table information (such as department tables and employee), avoid using DISTINCT in the Select clause.
Generally consider replacement with exist
E.g:
Inefficient:
Select Distinct DEPT_NO, DEPT_NAME
From DEPT D, EMP E
WHERE D.DEPT_NO = E.DEPT_NO
Efficient:
SELECT Dept_no, dept_name
From DEPT D
WHERE EXISTS (SELECT 'X'
From EMP E
WHERE E.DEPT_NO = D.DEPT_NO);
EXISTS makes the query more quickly, because the RDBMS core module will return the result immediately once it is satisfied.
22. Identify the 'inefficient execution' SQL statement
Use the following SQL tool to find inefficient SQL:
SELECT EXECUTIONS, DISK_READS, BUFFER_GETS,
Round ((buffer_gets-disk_reads) / buffer_gets, 2) Hit_Radio,
Round (Disk_reads / Executions, 2) Reads_Per_Run,
SQL_Text
From v $ sqlarea
Where executions> 0
And buffer_gets> 0
AND (Buffer_gets-disk_reads) / buffer_gets <0.8
ORDER BY 4 DESC;
(Translator Press: Although there are currently an endless graphics tools about SQL optimization, it is always the best way to solve the problem.
23. Use the TKPROF tool to query SQL performance status
The SQL TRACE tool collects the performance status data that is executing and records in a tracking file.
This tracking file provides a number of useful information, such as the number of parsed times. Make the number of execution, the CPU usage time. These materials will be available to optimize your system.
Set SQL TRACE in the session level: valid
Alter Session Set SQL_TRACE TRUE
Setting SQL TRACE to effectively imitate the entire database, you must set the SQL_TRACE parameter to True in Init.ra,
User_dump_dest parameter illustrates the directory of generating tracking files
(Translator Press: In this section, the author did not mention TKPROF usage, and the use of SQL TRACE is not accurate enough, set SQL
TRACE first wants to set TIMED_STATISTICs in Init.ora, so that it can get those important time status.
The generated trace file is unreadable, so use the TKPROF tool to convert it, TKPROF has many execution parameters.
You can refer to the Oracle manual to learn about the specific configuration.)
24. Analyze SQL statements with EXPLAIN PLAN
Explain Plan is a good tool for analyzing the SQL statement, which can even analyze statements without performing SQL.
Through analysis, we can know how Oracle is connected to the table, what way to scan the table (index scan or full table scan) and the used index name.
You need to interpret the results of the analysis from the order of the upward order. The result of the EXPLAIN PLAN analysis is used in the indent format.
The innermost operation will be interpreted first. If the two operations are in the same layer, the minimum operand will be executed first.
NESTED LOOP is a small number of operations that do not process the above rules, the correct execution path is check to NESTED
LOOP provides information on the operation, where the minimum operating number will be processed first.
Translator presses:
By practicing, it is more convenient to use the SET TRACE function in SQLPLUS.
Example:
SQL> List
1 SELECT *
2 from dept, emp
3 * where emp.deptno = dept.deptno
SQL> Set autotrace traceonly / * traceonly can not display the execution result * /
SQL> /
14 rows selected.
Execution Plan ------------------------------------------------ ------------
0 Select Statement Optimizer = Choose
1 0 NESTED LOOPS
2 1 Table Access (Full) of 'EMP'
3 1 Table Access (by Index Rowid) of 'DEPT'
4 3 Index (Unique Scan) of 'PK_DEPT' (UNIQUE)
Statistics
-------------------------------------------------- ------------
0 Recursive Calls
2 DB Block Gets
30 Consistent Gets
0 Physical READS
0 redo size
2598 BYTES SENT VIA SQL * NET to Client
503 Bytes Received Via SQL * Net from Client
2 SQL * NET ROUNDTRIPS TO / FROM Client
0 Sorts (Memory)
0 Sorts (Disk)
14 rows proped
Through the above analysis, the actual execution step is:
1. Table Access (Full) of 'EMP'
2. INDEX (Unique Scan) of 'PK_DEPT' (UNIQUE)
3. Table Access (by index rowid) of 'Dept'
4. Nested Loops (Joining 1 and 3)
Note: At present, many third-party tools such as Toad and Oracle itself provide EXPLAIN for Oms SQL Analyze.
PLAN tool. Maybe friends who like to be graphical interface can choose them.
25. Improve efficiency with index
Index is a concept part of the table, used to improve the efficiency of retrieval data. In fact, Oracle uses a complex self-balance B-Tree structure.
Typically, the Oracle Optimizer will use the index when scanning is scanned than full menu. When Oracle finds the best path to execute queries and Update statements.
Also use indexes when connecting multiple tables, can also improve efficiency. The other use index is the only authentication of primary key.
In addition to those LONG or Long Raw data types, you can index almost all columns. Typically, use indexes in large tables. Of course, you will find that
When scanning a small table, use indexes can also improve efficiency.
Although the use of indexes can be improved in query efficiency, we must also pay attention to it. Index requires space
Storage, you also need regular maintenance, and whenever a record is added or oriented in the table, the index itself will be modified. This means that every recorded INSERT,
DELETE, UPDATE will pay 4, 5 magnetic I / O.
Since the index requires additional storage space and processing, those unnecessary indexes will slow down the query reaction time.
Translator presses:
Regular reconstruction indexes are necessary.
Alter Index Rebuild
26. Operation of the index
Oracle has two access modes for the index.
Index unique Scan (Index Unique Scan)
In most cases, the optimizer accesses Index through the WHERE clause.
E.g:
Table lodging has two index:
Build uniqueness index on the lodging column Lodging_pk and the non-unique index of the establishment on the Manager column Lodging $ Manager.
SELECT *
From lodging
WHERE LODGING = 'Rose Hill';
Internally, the above SQL will be divided into two steps, first, the LODGING_PK index will be accessed by indexing unique scanning.
Get the corresponding ROWID, perform the next search by the RowID access table. If the column returned by the retrieve included in the index column, Oracle will not perform the second step (via the RowID access table). Because the retrieval data is saved In the index,
Single access index can fully meet the results of the query.
The following SQL only needs Index Unique Scan operations.
SELECT LODGING
From lodging
WHERE LODGING = 'Rose Hill';
Index range query (Index Range Scan)
Suitable for two cases:
1. Based on a range of retrieves
2. Retrieval based on unique index
example 1:
SELECT LODGING
From lodging
WHERE LODGING LIKE 'M%';
WHERE clause conditions include a series of values, Oracle will query lodging_pk by inquiry by index range. Since the index range query will return a set of values,
Its efficiency is the only scan of the band
Low.
Example 2:
SELECT LODGING
From lodging
WHERE manager = 'bill Gates';
This SQL execution is divided into two steps, and the index range of Lodging $ Manager is queried (get all the ruled RowID)
And the next step is to get the value of the Lodging column with the ROWID access table.
Since Lodging $ Manager is an unique index, the database cannot perform index unique scanning.
Because SQL returns the LodGing column, it does not exist in the Lodging $ Manager index,
So execute an operation through the RowID access table after the index range query.
In the WHERE clause, if the first character of the value corresponding to the column begins by wildcard, the index will not be adopted.
SELECT LODGING
From lodging
Where manager like '% hanman';
In this case, Oracle will use a full mete to scan.
27. Choice of the basic table
The basic table refers to the first-visited table (usually accessible in a full metric scan). According to the optimizer,
The choice of the base table in the SQL statement is different.
If you are using CBO (Cost Based
Optimizer, the optimizer checks the physical size, index status of each table in the SQL statement, and then select the lowest execution path.
If you use RBO (Rule Based Optimizer, and all connection conditions have indexes, in this case,
The basic table is the last table listed in the finals in the FROM clause.
Example:
SELECT A.NAME, B.Manager
From worker a,
Lodging B
WHERE a.lodging = b.loding;
Since there is an index on the Loding list of the Lodging table, the WORKER table will be used as the base table in the query in the Worker table.
28. Multiple equality indexes
When the execution path of the SQL statement can use multiple indexes distributed on multiple tables, Oracle will simultaneously use multiple indexes and merge their records at runtime.
Retrieves only records that are only valid for all indexes.
When Oracle selects the execution path, the level of unique index is higher than that of the unique index. However, this rule is only
When WHERE clauses are more valid. If the index class of the index column is compared to the index class of other tables. This clause is very low in the optimizer.
If two indexes in different tables think of the same level will be referenced, the order in the FROM clause will determine which will be used first.
The index of the last table in the FROM clause will have the highest priority.
If two indexes of the same level in the same table will be referenced, the most referenced in the WHERE clause will have the highest priority order.
Example:
There is a non-unique index on deptno, and EMP_CAT also has a non-unique index.
Select ename,
From EMP
WHERE DEPT_NO = 20
And Emp_cat = 'a';
Here, the DEPTNO index will be first retrieved, and then the records are merged with the EMP_CAT index. The execution path is as follows:
Table Access by Rowid ON Empand-Equal
Index Range Scan on DePT_IDX
Index Range Scan on Cat_IDX
29. Comparison and scope of equation
When there is an index column in the WHERE clause, Oracle cannot merge them, Oracle will use the range comparison.
Example:
There is a non-unique index on deptno, and EMP_CAT also has a non-unique index.
SELECT ENAME
From EMP
WHERE Deptno> 20
And Emp_cat = 'a';
Only the EMP_CAT index is used here, and then all records compare it to the DEPTNO condition. The execution path is as follows:
Table Access by RowID on EMP
Index Range Scan on Cat_IDX
30. Unclear index level
When Oracle is unable to determine the level of high and low differences, the optimizer will use only one index, which is listed in the WHERE clause.
Example:
There is a non-unique index on deptno, and EMP_CAT also has a non-unique index.
SELECT ENAME
From EMP
WHERE Deptno> 20
AND EMP_CAT> 'a';
Here, Oracle only uses a DEPT_NO index. The execution path is as follows:
Table Access by RowID on EMP
Index Range Scan on DePT_IDX
Translator presses:
Let's try the following situations:
SQL> SELECT INDEX_NAME, UNIQUENESS from User_indexes Where
Table_name = 'EMP';
INDEX_NAME UniQuenes
---------------------------------------
Empno unique
EMPTYPE NONUNIQUE
SQL> Select * from Emp where Empno> = 2 and EMP_TYPE = 'A';
No rows selected
Execution Plan
-------------------------------------------------- ------------
0 Select Statement Optimizer = Choose
1 0 Table Access (by index rowid) of 'EMP'
2 1 Index (Range Scan) of 'EMPTYPE' (Non-Unique)
Although EMPNO is unique index, because it is what it is, the level is relatively low than the unique index.
Configuration of Oracle9i's transparent gateway
Oracle Implementation of Heterogeneous Database Connection Services is called transparent gateway.
At present, Oracle uses transparent brakes to achieve interconnection of multiple mainstream databases such as SQL Server, Sybase, and DB2.
Since the work needs, I pass Oracle to access the Sybase database, configure Oracle9i Transparent Gateway for.
Sybase
Steps write as a document, for a needed user reference!
Configuring Transparent Gateway for Sybase Steps
1.
The Sybase Client is installed on the servers located in Oracle (or installing Oracle, Sybase Server) on the same server.
Ensure access to the Sybase database
2.
To install the Transparent Gateway for Sybase Option, use a custom installation.
Correctly select Sybase installation directory
3.
Select a SID string ready to assign it to the Sybase database. TG4SYBS
Set Sybase's DLL path to environment variable PATH (this is very important)
4.
Modify the initialization file, the default is:
Oracle_Home / Tg4sybs / Admin / INITTG4SYBS. ORA
Setting parameters
HS_FDS_CONNECT_INFO
Format: HS_FDS_CONNECT_INFO = Server_Name. Database_name [, Interface =
Interface_file]
Server_name. Database_name is sensitive.
Interface optional
Example: as follows
$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$ $$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
# This is a sample agent init file what contains the HS parameters
That Are
#needed for the transparent Gateway for Sybase
#
# HS init parameters
#
HS_FDS_CONNECT_INFO = MIGRATION_SERV.TAX
HS_FDS_TRACE_LEVEL = OFF
HS_FDS_RECOVERY_ACCOUNT = Recover
HS_FDS_RECOVERY_PWD = Recover
#
# Environment Variables Required for Sybase
#
SET SYBASE = D: / Sybase
$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$ $$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
In the upper example
Server_name is MIGRATION_SERV
Database_name is TAX
5.
Configuring the Listener of the Oracle Network Services, the configuration file is: Listener.ora
Default path: Oracle_home / network / admin
Join
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = GATEWAY_SID)
(Oracle_Home = Oracle_Home_Directory)
(Program = TG4SYBS)
)
)
Gateway_sid is 3 selected SID strings
Oracle_home_directory is Oracle_home
TG4SYBS is specific to Sybase is specific. If it is another database, it will be different.
The example is as follows:
$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$ $$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
(SID_DESC =
(SID_NAME = TG4SYBS)
(Oracle_Home = D: / Oracle / ORA92)
(Program = TG4SYBS)
)
$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$ $$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
6.
Stop listening
LSNRCTL Stop
Restart the listener
Lsnrctl Start
7.
Configuring Oracle Server TNSNames.ora enables it to access Sybase
Connect_Descriptor =
(Description =
(Address =
(Protocol = TCP)
(Host = host_name) (port = port_number)
)
(Connect_data =
(SID = GATEWAY_SID))))
(HS = OK))
Connect_Descriptor is a connection string, all of which is usually SYBS
Host_name: Name of Oracle Server
Port_number: Oracle Listening
Gateway_sid is 3 selected SID strings
The example is as follows:
$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$ $$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
Sybs =
(Description =
(Address_list =
(Address = (protocol = TCP) (Host = DW-Server1) (port = 1521)))
)
(Connect_data =
(SID = TG4SYBS)
)
(HS = OK)
)
$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$ $$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
8. Establish Database Link
Such as:
Create Database Link Sybs Connect To SA
Identified by Prient
Using 'sbys';
You can access the Sybase repository.
It should be noted that the name of the Sybase database, the column name, if it is lower-written, then add a double quotation number when accessing in Oracle. "
Such as:
SQL> SELECT "a" from "b" @sybs;
ORA-01034 error solution
- One of the common mistakes in Oracle
In advance, Oracle masters do not need to be seen.
This is a common error in the Oracle Database Server. Experienced users can solve this mistake almost immediately, and they can also go to Metalink (http://metalink.oracle.com) to search.
Unfortunately, most of them are the problems encountered by the primary users (what effects do not have to mention Metalink - generally do not have the top account :)). Therefore, this small post may have a certain role.
Problem Description
=======
When trying to start the database, Oracle reports the following errors:
Error:
ORA-27101 Shared Memory Realm Does Not Exist
ORA-01034 Oracle NOT AVAILABLE
Basic explanation
=======
Error: ORA-27101
TEXT: Shared Memory Realm Does Not Exist
-------------------------------------------
Cause: unable to locate shared memory realm
Action: Verify That The Realm IS Accessible
How to solve
=======
This problem can be clearly understood by this question:
Oracle_Home or Oracle_sid setting is incorrect.
In the previous version, if Oracle_sid is incorrect, only ORA-01034 is generally prompted. Oracle 8.1.7 gives an additional information: ORA-27101.
-> If you are UNIX, set the Oracle_SID to set the Oracle_SID in the shell (pay attention to sensitive problems).
In addition, check the oracle_home environment variable. How to check the following command:
% Echo $ Oracle_SID
% PS-EF | GREP SMON
-> If it is Windows, it is generally caused by multiple instances in the system.
Can be c: /> set oracle_sid = demo in the command line
Change the Demo here to your corresponding instance name.
If you still don't work, check the Oracle_home in the registry.
In addition, when there is a fixed-end database when there is a Windows environment, this error will be reported.
The solution is to put the SQLNET.ORA file
SQLNET.AUTHENTICATION_SERVICES = (NTS) NTS is changed to NONE.
ORA-03113 error analysis FENNG (original)
Keyword Oracle 03113
Fenng (Fenng@itpub.net)
Copyright statement: Please indicate the author and the source
Foreword
Each DBA is inevitable in the process of database management, it is necessary to encounter a variety of errors (ORA-XXXX). Some errors are called "classic mistakes" by DBA due to frequent occurrences. 3113
"End of Fileon Communication Channel" is one.
We can simply understand this error as an Oracle user process and database background process connection interruption. However, there are many reasons for this error, and there are many kinds of facts. If you set up the database, any behavior that can cause the database background process crash. It may produce this error. This error occurs often accompanied by other errors, such as: ORA-1034
Oracle Not Available.
In addition, the scene appears complicated, which may appear:
When the Oracle started;
Attempt to create a database;
Attempt to connect the database;
When the client is running SQL / PL / SQL;
When the backup / recovery database is;
In some cases. . . . . .
In the forum, you can often see the assistance of this problem. Simple in this question is sorted out. Improper, please advise!
Error reason
According to the situation that everyone reflects on the Internet, the error is about this:
Unix core parameter setting is not properly
Oracle execution file permission is incorrect / environment variable problem
User-end communication does not process correctly
Database Server Crash / Homework System Crash / Process is Kill
Oracle internal error
Error caused by specific SQL, PL / SQL
Space is not enough
Firewall problem
other reasons
Before starting to solve the problem, do the following things:
1. Remember what operations you have done before you have an error, the better, the better;
2, view the alertsid.log file in the background_dump_dest directory is also what you have to do;
3,
Google, there are many information on the Internet waiting for you to discover, don't ask others. Of course, if you find some things that are very helpful to you - this document doesn't have to look, don't delay your time ,Ha ha.
Unix core parameters set improper / init parameter setting
If the database does not set the correct job system core variable during the installation process, you may be in the installation database file
Nothing, when you create a database, you often appear 03113 errors. Another reason for this is init.ora
The processes parameter in the parameter file specifies the unreasonable value, the startup database causes the error to appear (of course this is
The bottom is also a problem with the core parameters).
This error message is usually as follows:
ORA-03113: End-of-file on communication channeloelora-01034: Oracle Not Available
ORA-27101: Shared Memory Realm Does Not Exist
There are two solutions:
1 Modify the core parameters to increase the value of the corresponding core parameters (recommended);
2 Reduce the value of the processes of the init.ora parameter.
have to be aware of is:
SEMMSL must be set to at least 10 process number maximum.
Semmns also depends on the process parameter values on each database.
-------------------------------------------------- -----------------------------
Note:
This error type only appears on the UNIX platform. If the value of Processes is too large on Windows, it will appear:
ORA-00068: Invalid Value 24200001 for Parameter
MAX_ROLLBACK_SEGMENTS, MUST BE
BetWeen 2 and 65535 / * The specified parameter value is over 65535 * /
or
ORA-27102: Out of memory / * less than 65535 a large parameter value * /
My soft environment:
Windows 2000 Version 5.0 Service Pack 3, CPU Type 586
Oracle Rdbms Version: 8.1.7.0.0.
-------------------------------------------------- -----------------------------
Change the core parameters on a specific platform may be different, please refer to Oracle
Install documentation on TechNet (http://otn.oracle.com). The installation document for a specific UNIX platform also has an explanation of the meaning of core parameters.
Parameters in init.ora If the setting is not set, it will generate this error. Experience shows that Shared_Pool_SIZE sets an error, and the setting of TIMED_STATISTISTICS = TRUE will also bring problems.
Oracle execution file permission is incorrect / environment variable problem
This problem only appears on the UNIX platform. Frequently, there are time to use UNIX for convenience
The TAR command has been installed by the compressed package, or the system administrator specifies that additional OS users can manage
According to the library, there is no specified correct environment variable.
Oracle execution file In the $ ORACLE_HOME / BIN directory, if there is a problem, you should use the following UNIX similar command to correct:
CHMOD 7755 $ Oracle_Home / Bin / Oracle
Have some time to do Relink to Oracle.
When installing on UNIX, the problem of environmental variables often occurs, and individual execution program connectivity. LD_
Library_path If the incorrect setting will cause problems, in this case, it is necessary to carry Relink for Oracle. If
The executable Oralcle is destroyed, and it is also necessary to Relink.
If the parallel server option is installed, the Distributed Lock Manager is not installed or properly running.
User-end communication does not process correctly
SQL * NET drive problem:
If the version is used, replace it to the new version of the driver. SQL * NET
The drive is not connected to the Oracle executable that will cause errors.
Check if the network is smooth
Frequently Asked Questions for the Windows platform:
When you create a database in the Windows platform, if this problem occurs, you can consider the following methods:
First check the local network settings. Check if there is a node of the same name on the Internet or conflict IP. If the problem is still, you can guarantee
Use the following method:
1. Disable network card: change the local connection status to disabled;
2. Open the SQLNET.ORA file (in a notebook form) to the NTS verification comment:
# SQLNET.AUTHENTICATION_SERVICES = (NTS) .3. Create a database;
4. After the creation is successful, restore local connections.
Database Server Crash / Homework System Crash / Process is Kill
During the connection, if the Oracle Database Server crashes or the job system where the database is located, this will appear
Error. Oracle
The cause of Server crash may be died because of the main background process. The KILL operation is performed by the error. If this is the reason is more easily solved. In addition, the application related to the OS has a memory leak (or viral) time It will also cause the Oracle's post-standing problem.
Recommended quotation method:
1. View if the application software related process is operating normally;
2, look at there is a memory leak;
3, kill viruses;
4. Determine that the system administrator does not operate;
5. Determine the hacker intrusion.
6. Other uncertain factors. . . . . .
Oracle internal error / bug
If you look at the Alert.log in the background_dump_dest directory, it is found that there is an ORA-600 or other error, you can go to Metalin
K Site View specific information and its solutions. Generally, you have to make a soft patch.
Error caused by specific SQL, PL / SQL
Try separate SQL separately, or use SQL_Trace to track, find SQL statements that cause problems:
Under SQLPlus:
Alter Session Set SQL_Trace True;
The illegal fonts and unreasonable processing results in the SQL statement will occasionally bring problems.
System space is not enough
Any time Hou must ensure that the database system has enough space. If User_Dump_Dest
If the background_dump_dest does not have the remaining space, this problem will result in this problem. In addition, if the audit is opened, the audit directory is sufficient, if you start TRACE, the Trace directory is sufficient.
Dave Wotton's documentation indicates that the problem will cause the file if the file is more than 2G (2G restrictions on the file system) will result in this issue.
Firewall problem
If the information is to be passed through a firewall, please contact the system administrator, ask if the database data is filtered or suddenly
Terfe. If you have a personal firewall, please check your local settings.
Other aspect description
There are many reasons why this error, just some typical situations listed above. Regularly go to some database technology forums
It can help. For example, ITPUB (http://www.itpub.net), cnoug (http://www.cnoug.org).
Reference information / more read
http://metalink.oracle.com
Oracle's technical support site, you have a CSI number to log in.
Reference Note Number:
Note: 17613.1
ORA-3113 on UNIX - What Information To Collect
Note: 131207.1
How to set unix environment variables
Note: 131321.1
How To Relink Oracle Database Software on Unix
NOTE: 22080.1
http://www.google.com/grphp hl = zh-cn
Google News Group
Http://www.jlcomp.demon.co.uk/faq/ora-3113.html
Technical expert Jonathan Lewis on a FAQ
Http://home.clara.net/dwotton/dba/ora3113.htm
Dave Wotton summarizes a very classic document.
In Oracle Management and Applications, there is inevitable problems. Typically, Oracle will display error labels and short descriptions, we can deal with issues based on the information displayed. But sometimes there is very little information, and it is troublesome. This article discusses such a few questions, and proposes a solution according to some information and experience.
I. ORA-00604 ERROR OCCURRED AT Recursive SQL Level This information indicates that an error occurs when the internal SQL statement is performed in the database. For example, insert a line of data in the table, but there is no scalable space. Oracle is going to find, where can be built next to extend space, how much it is, but there is no success. Generally, when an ORA-00604 error occurs, it is also accompanied by other errors, such as ORA-1547, and the like.
First, you should check the warning file alertsid.log to find information about ORA-600 class.
The most common reason for this error is that the parameter Open_Cursors value in the database file INITSID.ORA is too small. You can modify the INITSID.ORA file, the value of Open_Cursors is generally 255. After the modification, it is down on Oracle and then restart.
You can also set and start the event tracking function of the database. Plus a line in INITSID.ORA:
Event = "00604 TRACE Name Errorstack"
Down and restart Oracle so that this event tracks the parameters. In this way, when an ORA-604 error occurs, the information is saved in the trace file.
Other reasons for causing ORA-604 errors may be:
-
In INITSID.ORA, the parameter dc_free_extents or row_cache_enqueues is too low. The value of these two parameters can be appropriately added according to the operating system and the database, and the Oracle is restarted and restarted.
- Operation beyond space (with ORA-1547 errors). At this time, add a new file to the table space, that is, add the size of the table space.
-
Dacted Max_EXTENTS (accompanied by ORA-1556 errors). If this is to modify the table, allow more extensions. Please find the maximum maximum value of MAX_EXTENTS from the technical manual. If you have reached the maximum, you must use Compress
Extents option, unload the table (Export), and then import (import) the database.
Second, ORA-03106 Fatal Two-Task Communication Protocol Error
This information indicates that an error occurred while Oracle's network communication work. For example, when the client application uses the SQL * NET to access the server database, Oracle displays ORA-03106 errors.
First, the compatibility between the customer application and the database server should be checked, which is the most common reason for ORA-03106 errors. Now found, Developer / 2000
V1.3 Book with Oracle V8.0.5 for Digital Unix is incompatible; Oracle V7.0.1.6 for
Scounix and Oracle V8.0.5 for Digital
Unix is incompatible, and so on. Check the NLS (graphic set) compatibility between the customer application and the database server. The Chinese character set on the computer is generally set to zHS16CGB231280, which is generally set to zHS16GBK in recent years, and the settings under the English operation system are generally US7ASCII. It is best to set the character set to the same when the system is installed, which is also convenient for the discharge and import of the data between the database.
If the database link has not been unconnected, ORA-03106 error is displayed, then it is possible to set a problem with SQL * NET. To use the database link, the value of Global_Names in both the database file in INITSID.ORA should be false. The files on the server TNSNames.ora have the other party's database alias, which is used to build a database link. Alias. In the Cluster system, especially in the two-machine, people often write only the data library false name with the machine virtual displacement, and forget the real name of the database with the machine's true address. The database alias involved in the actual application should be written to TNSNames.ora. In addition, the value of Open_LINKS in INITSID.ORA is generally defaults to 4, and this value needs to be appropriately added when the application is used in the application.
You can also set and start the event tracking function of the SQL * NET to get the information generated when an ORA-03106 error is generated, and there is a targeted solving problem.
In the compare extreme case, the problem indicates that the shared memory used by Oracle has collapsed. It may be necessary to use the Abort option to dose a database and release all Semaphores (UNIX). Because Oracle uses Semaphores to control the synchronization of all background processes. Semaphores is also used to control dual tasks between user processes and shadow processes. Since the problem involved in this case is more complicated, the entire machine system can be removed and restarted.
Third, remove the information from Oracle8 and import Oracle7
DMP files from Oracle7 can be imported in Oracle8; but from Oracle8, the DMP file cannot be imported into Oracle7. If you use Oracle7's utility, you cannot uninharvize the Oracle8 information. This is very inconvenient for users who apply multiple versions of Oracle.
In fact, Oracle8 has considered this. In the server directory $ oracle_home / rdbms / admin
In the middle, there is a file catexp7.sql, which is used to solve this problem. First, in the Oracle8's server, log in Oracle with the SYS account, then run this catexp7.sql file. The Oracle system then establishes some unloading views, making the Oracle8 database seemed to be an Oracle7 database when discovered. At this time, you can use the Oracle7 utility directly to unload the Oracle8 data, and then it can be successfully imported into Oracle7.
When using Oracle7 utilities directly unloading Oracle8's information, some things that belong to Oracle8 feature are unloaded. Specifically, you can refer to the relevant technical manual, such as "Oracle8
Utilities.
Four, ORA-27101 Shared Memory Realm Does Not Exist
When the above error information appears, it is generally accompanied by error. ORA-01034: Oracle NOT
Available. The reason is that different Oracle_home is used on the same server. This problem is often in the Oracle8.1.7 servo version.
First check the file initsid.ora and listener.ora, see the correct or not, oracle_sid and oracle_home settings, Oracle8.1.7 is started and runs with this parameter value. In the UNIX environment, the meaning of letters is different, this should be noted. If Oracle_Home points to version 8.1.7, the database is built with version 8.1.6 or 8.1.5, or this error message may also occur.
In the Windows system, if the machine name or IP address is modified, the machine name or IP address used when boot is started, and this type of error will occur if it is not a real machine name or IP address. You can view file ORADIM.LOG under Directory Database, determine the reason according to the content. On the server involved in domain, including Windows and UNIX, depending on the system settings, you may need to add a function variable name when using the machine name.
