Tweet
Oracle Undocumented Parameters
select ksppinm
from x$ksppi
where substr(ksppinm,1,1) = ‘_’;
ORA-00312: Message 312 not found; Solution
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 :
Flashback Query
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.
Function Based Indexes
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:
When to Rebuild a B-Tree Index
You have to periodically check your indexes to see if they become skewed and, therefore, good candidates for rebuild.
Index Monitoring
Index monitoring could be initiated and stopped using ALTER INDEX syntax shown below.
Building virtual index using the NOSEGMENT clause.
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.
Selecting the Best Join Order : Methods Oracle Uses to Join Tables
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.
Sid from Unix Process ID
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.
Find out SGA PGA Size
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’
Statspack Scripts
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)
API to Retrieve runtime Replication Parameters
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.
How to enable trace in Oracle
1. Enable trace at instance level
Put the following line in init.ora. It will enable trace for all sessions and the background
processes
Can't convert a Table Space into Read Only Mode
Reason : Active x’actions running on the table space
Locate SQL_TEXT and Session Address running alter Tablspace Command Read more…
Rectify Differences Between Replicated Tables
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.
Get the current Session ID
SQL> desc v$mystat
Name Null? Type
—————————————– ——– —————————-
SID NUMBER
STATISTIC# NUMBER
VALUE NUMBER
Loading or Re-Synchronising Data in a Replication Cluster
To strop replication and not to place a system into read only status please execute following steps.
Execute DBMS_REPUTIL.replication_OFF;
Replication Packages/Triggers Missing at new site : ORA-04067: stored procedure OBJECT_NAME$RP does not exist
I built a 6 node cluster using notes at http://www.oracledbasupport.co.uk/adding-a-new-master-node-in-an-existing-multi-master-cluster/
Average Amount of Time to Apply Transactions at Remote Sites
SELECT substr(DBLINK,1,25), AVG_LATENCY “Average Latency”
FROM DEFSCHEDULE
WHERE upper(DBLINK) like ‘&1′;
Tracking the Rate of Transactions Entering the Deferred Transactions Queue
SQL will dispaly rate of x’actions entering at DEFTRAN Queue for last Hour
SQL> select to_char(trunc(start_time,’MI’),’DD-MON-YY HH24:MI:SS’),count(*) from deftran where start_time > sysdate -(1/24)
group by to_char(trunc(start_time,’MI’),’DD-MON-YY HH24:MI:SS’)
order by 1 desc
Tracking the Average Number of Row Changes in a Replication Transaction
SELECT DECODE(TXNS_ENQUEUED, 0, ‘No Transactions Enqueued’,
(CALLS_ENQUEUED / TXNS_ENQUEUED)) “Average Number of Row Changes”
FROM V$REPLQUEUE; Read more…
