oracle

There are several different types of Oracle Applications patches. E-Business Suite patching is a very broad topic. So we have decided to split it into two logical parts. Part 1 – Oracle E-Business Suite patch terminology. patch type hierarchy patch naming convention naming differences between 11i and R12 Part 2 – Querying patches, patchsets, family […]

To find the current SQL statement being executed by particular session in Oracle, Query V$SQLAREA and V$SESSION views. Here is the query to report the current SQL statement being executed by a session: select substr(v2.sid,1,5) “SID” ,substr(v2.username,1,8) “Username” ,substr(program,1,10) “Program” ,sql_text from sys.v_$sqlarea v1, sys.v_$session v2 where v1.address= v2.sql_address and v1.hash_value = v2.sql_hash_value; The above […]

A temporary tablespace can run out of space or become full and we may get error ORA-1652. ORA-1652 “unable to extend temp segment by %s in tablespace %s” This post provides the queries to find out the SQL which is using these temp segments. SELECT a.username, a.sid, a.serial#, a.osuser, b.tablespace, b.blocks, c.sql_text FROM v$session a, […]

A temporary tablespace does exist on a permanent basis as do other tablespaces, such as the System and Sysaux tablespaces. However, the data in a temporary tablespace is of a temporary nature, which persists only for the length of a user session. Oracle uses temporary tablespaces as work areas for tasks such as sort operations […]

Running DDL or DML statements. Below example shows a sample DDL run over SQL Developer Web and SQL Developer. CREATE TABLE sample_contract( Year DATE, Team VARCHAR2(12), Conference VARCHAR2(10), Division VARCHAR2(8), City VARCHAR2(13), Price NUMBER, Size NUMBER, Pct NUMBER) / Error report – ORA-00904: : invalid identifier 00904. 00000 – “%s: invalid identifier” *Cause: *Action: The […]

EZCONNECT is Oracle’s easy connect naming method. EZCONNECT eliminates the need for service name lookups in TNSNAMES.ORA files when connecting to an Oracle database across a TCP/IP network. Syntax of EZCONNECT: sqlplus username/password@[//]host[:port][/service_name] To enable EZCONNECT “sqlnet.ora” should have naming methods “ezconnect” specified in NAMES.DIRECTORY_PATH Parameter. “sqlnet.ora” is located in $ORACLE_HOME/network/admin Example: NAMES.DIRECTORY_PATH=(ezconnect, tnsnames) EZConnect […]

This is a step-by-step troubleshooting guide for error: ERROR: ORA-12514 / TNS-12514: TNS:listener could not resolve SERVICE_NAME given in connect descriptor This error can occur when connecting directly to the database or when selecting across a database link (dblink). The troubleshooting steps outlined below are applicable to either situation. As per Oracle documentation: ORA-12514: TNS:listener […]

I always prefer to use TNSNAMES.ORA to connect to my database server. It is one of the most convenient ways for database servers with a fixed hostname or IP address. For example, if TNSNAMES.ORA has the following entry: ORCL = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = geek.mydomain.co.in)(PORT = 1521)) ) (CONNECT_DATA […]

The ORA-6512 is a generic placeholder for the PLSQL call stack when an exception is unwound. Each line generated provides information on the PLSQL program unit and line number that results in the actual error. Cause: Backtrace message as the stack is unwound by unhandled exceptions. Action: Fix the problem causing exception or write an […]