How to force a trace file to be created when an ORA error occurs?

There was an issue we ran into where one of the users ran out temporary tablespace (TEMP) we had a message in the database alert log that there oracle couldn’t extend temporary tablespace and we couldn’t find the cause and user who ran into the issue. So turning on an event for the error oracle will create a trace file which will contain user, machine, os, SQL which will allow DBA to find additional information.

Example:

Oracle docs note this about ORA-01652:
Error: RA-01652: unable to extend temp segment by string in tablespace string
Cause: Failed to allocate an extent of the required number of blocks for a temporary segment in the tablespace indicated.
Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated.


To turn trace on you would run the following SQL:
ALTER system SET EVENTS ‘1652 TRACE NAME ERRORSTACK LEVEL 3’;

It will write to the alert.log

Tue Jul 21 11:04:45 2009
Errors in file /u01/admin/TESTDB/udump/testdb_ora_17682588.trc:
ORA-1652: unable to extend temp segment by 128 in tablespace TEMP

The trace file would contain the following information including the SQL statement:
….
oct: 2, prv: 0, sql: 7000000593f3dc8, psql: 700000057c3ec30, user: 420/TEST
O/S info: user: ajaffer, term: MYCOMPUTER, ospid: 3684:2444, machine: MYCOMPUTERNAME

To turn trace off you would run the following SQL:
ALTER system SET EVENTS ‘1652 TRACE NAME ERRORSTACK OFF’;

If one would like to set an event in the spfile, you can set by executing the DDL below. If there are multiple events then it can be set by they have to be “:” separated. In the example below event is set for ORA-01653 and ORA-01652
ALTER system SET event=’1653 TRACE NAME ERRORSTACK LEVEL 3:1652 TRACE NAME ERRORSTACK LEVEL 3′ scope=SPFILE;

To clear the event in the spfile, you can execute the following SQL
ALTER SYSTEM SET EVENT=” SCOPE=spfile;

One can similar turn trace on for various ora errors but be aware that some of them by caused internally from within Oracle example ORA-604 which you may not want to turn on an event for.

Thank you for reading and support.

Recompile the whole DB using Oracle's scripts utlrp.sql and utlprp.sql

The utlrp.sql and utlprp.sql scripts are provided by Oracle to recompile all invalid objects in the database. 

They are typically run after major database changes such as upgrade or downgrade or patches installation. 

They are located in the $ORACLE_HOME/rdbms/admin directory and provide a wrapper for the UTL_RECOMP package. UTL_RECOMP provides a more general recompilation interface, including options to recompile objects in a single schema. Please see the documentation for package UTL_RECOMP for more details. 

The utlrp.sql script simply calls the utlprp.sql script with a command line parameter of "0". 

The utlprp.sql accepts a single integer parameter that indicates the level of parallelism as follows.

     0 - The level of parallelism is derived based on the CPU_COUNT parameter.

     1 - The recompilation is run serially, one object at a time.

     N - The recompilation is run in parallel with "N" number of threads.

Both scripts must be run as the SYS user, or another user with SYSDBA, to work correctly.

Thank you for the reading and support.

ORA-22950 cannot ORDER objects without MAP or ORDER method

The following is the cause of this error:

The values of a scalar datatype such as CHAR or REAL have a predefined order, which allows them to be compared.
But an object type, such as a some_type, which can have multiple attributes of various data types, has no predefined axis of comparison.
To be able to compare and order variables of an object type, you must specify a basis for comparing them.
Two special kinds of member methods can be defined for doing this: map methods and order methods.
The action you can take to resolve this issue: Define a MAP or ORDER method for the object type.

For more information, you can see https://docs.oracle.com/cd/B19306_01/appdev.102/b14260/adobjbas.htm#sthref211

ORA-02030 error when try to grant permissions to a v$ tables

Face with an error ORA-02030 when you try to give permission on a v$ view

SQL> grant select on v$session to hr;
grant select on v$session to hr
*
ERROR at line 1:
ORA-02030: can only select from fixed tables/views

Why it couldn’t give select privileges to a v$ view. If you tried to give permission to other v$views you will face same the error.

The problem is caused because of trying to give select privileges on a synonym. Oracle v$ views are named V_$VIEWNAME and they have synonyms in format V$VIEWNAME and you can’t give privilage on a synonym.
If you want to give permission to a V$ view you must grant permissions like below:

SQL> grant select on v_$session to hr;
Grant succeeded.

RAISE_APPLICATION_ERROR

The RAISE_APPLICATION_ERROR is actually a procedure defined by Oracle that allows the developer to raise an exception and associate an error number and message with the procedure. This allows the application to raise application errors rather than just Oracle errors. Error numbers are defined between -20,000 and -20,999. All other numbers belong to Oracle for its own errors. The message can be anything that will fit in a varchar2(2000).

RAISE_APPLICATION_ERROR can be used in either (or both) the executable section and the exception section of a PL/SQL program. The error number and message is displayed to the user.

Syntax:
RAISE_APPLICATION_ERROR ({error_number}, {error_message});

{error_number} - The Error number must be between -20000 and -20999

{error_message} - The Error message is the message you want to display when the error occurs


Using in Execution section

BEGIN
...
RAISE_APPLICATION_ERROR (-20001,'This is a user error message');
...
END;


Using in  Exception section:

BEGIN
...
EXCEPTION
    WHEN NO_DATA_FOUND THEN
    RAISE_APPLICATION_ERROR (-20003, 'This is another user error message');
END;


Examples:

Input:
DECLARE 
    l_number    NUMBER;
BEGIN
    SELECT 2 INTO l_number FROM DUAL;
    dbms_output.put_line('l_number = '||l_number);
END;

Output: 
l_number = 2

Input:
DECLARE 
    l_number    NUMBER;
BEGIN
    SELECT 2 INTO l_number FROM DUAL;
    RAISE_APPLICATION_ERROR (-20001,'This is a user error message');
    dbms_output.put_line('l_number = '||l_number);
END;

Output: 
There is no output due to our ERROR
line 19: ORA-20001: This is a user error message

ORA-06512: at line 5

Oracle File extensions

SQL*Plus files

.sql - SQL script

.lst - spool file

PL/SQL files

.pls - PL/SQL source

.plb - PL/SQL binary

.pks - Package source or package specification

.pkb - Package binary or package body

.pck - Combined package specification plus body

Oracle database files

.dbf - database file

.log - Online Redo Log

.rdo - Online Redo Log

.arc - Archive log

SQL*Loader files

.ctl - Control file

.dat - Data file

.bad - Bad file

.dsc - Discard file

SQL*Net files

.ora - tnsnames.ora, sqlnet.ora, etc.

Forms files

.fmb - Forms binary

.fmt - Forms text

.fmx - Forms executable

Reports files

.rdf - contains a single report definition in binary format. 

        .rdf files are used to both run and edit reports.

.rep - contains a single report definition in binary format. 

          .rep files are used solely to run reports; you cannot edit a .rep file

.rex - contains a single report definition in text format. .REX files are portable.

Switching to a Different Schema

The following statement sets the schema of the current session to the schema name specified in the statement.

ALTER SESSION SET CURRENT_SCHEMA = <schema name>;

In subsequent SQL statements, Oracle Database uses this schema name as the schema qualifier when the qualifier is omitted. In addition, the database uses the temporary tablespace of the specified schema for sorts, joins, and storage of temporary database objects. The session retains its original privileges and does not acquire any extra privileges by the preceding ALTER SESSION statement.

You can use this command above to avoid the use of public synonyms.  By setting the CURRENT_SCHEMA attribute to the schema owner name it is not necessary to create public synonyms for production table names. Without the current_schema syntax, you would have to assign a public synonym for every table in your production database.

If you want to check the current schema of the session you can use the following statement:

SELECT sys_context( 'userenv', 'current_schema' ) from dual; 

Let's look in the following example:

CONNECT user1

ALTER SESSION SET CURRENT_SCHEMA = schema_2;

SELECT * FROM empoyers;

Because user1 is not schema-qualified, the table name is resolved under schema schema_2. But if user1 does not have select privilege on table schema_2.empoyers, then user1 cannot execute the SELECT statement even switching the current session schema.