Applies to: Oracle Server – Enterprise Edition – Version: 9.2.0.6.0
This problem can occur on any platform.
Symptoms
When NLS_LANGUAGE is set to ENGLISH and NLS_TERRITORY is set to AUSTRALIA, the text related to Archival messages are not displayed. The following contents are shown in the Alert log :
Oracle Flashback Query allows users to see a consistent view of the database as it was at a point in the past. This functionality allows comparative reporting over time and recovery from logical corruptions.
Traditionally, performing a function on an indexed column in the where clause of a query guaranteed an index would not be used. Oracle 8i introduced Function Based Indexes to counter this problem. Rather than indexing a column, you index the function on that column, storing the product of the function, not the original column data. When a query is passed to the server that could benefit from that index, the query is rewritten to allow the index to be used. The following code samples give an example of the use of Function Based Indexes:
You have to periodically check your indexes to see if they become skewed and, therefore, good candidates for rebuild.
Index monitoring could be initiated and stopped using ALTER INDEX syntax shown below.
Let’s create a virtual index
07:59:12 orcl> create index hr.emp2_emp_id_virtual on hr.employees2(employee_id) nosegment;
Index created.
Choose the join order that gives you least number of rows to be joined to the other tables. That is, if you’re joining three tables, the one with the more restrictive filter should be joined first to one of the other two tables.
Here is a situation, This afternoon I have seen my CPU hitting through the roof on a Live system. There are about 6 Oracle instances and one is chewing more than 90% of CPU time.
SET LINESIZE 145
SET PAGESIZE 9999COLUMN sid FORMAT 999 HEADING ‘SID’
COLUMN oracle_username FORMAT a12 HEADING ‘Oracle User’ JUSTIFY right
COLUMN os_username FORMAT a9 HEADING ‘O/S User’ JUSTIFY right
COLUMN session_program FORMAT a18 HEADING ‘Session Program’ TRUNC
COLUMN session_machine FORMAT a8 HEADING ‘Machine’ JUSTIFY right TRUNC
COLUMN session_pga_memory FORMAT 9,999,999,999 HEADING ‘PGA Memory’
COLUMN session_pga_memory_max FORMAT 9,999,999,999 HEADING ‘PGA Memory Max’
COLUMN session_uga_memory FORMAT 9,999,999,999 HEADING ‘UGA Memory’
COLUMN session_uga_memory_max FORMAT 9,999,999,999 HEADING ‘UGA Memory MAX’
Installation
The following scripts must be run as a user with SYSDBA privilege:
* SPCREATE.SQL: Creates entire Statspack environment (calls SPCUSR.SQL, SPCTAB.SQL, SPCPKG.SQL) * SPDROP.SQL: Drops entire Statspack environment (calls SPDTAB.SQL, SPDUSR.SQL)
We often get thousands of replication conflits/errors at current system. A resolution is generally looking at EM console and resolving each error manually. Please see examples below.
1. Enable trace at instance level
Put the following line in init.ora. It will enable trace for all sessions and the background
processes
Reason : Active x’actions running on the table space
Locate SQL_TEXT and Session Address running alter Tablspace Command Read more…
Using the DIFFERENCES Procedure
The DIFFERENCES procedure compares two replicas of a table, and determines all rows in the first replica that are not in the second and all rows in the second that are not in the first. The output of this procedure is stored in two user-created tables. The first table stores the values of the missing rows, and the second table is used to indicate which site contains each row.
Tweet