ORA-12154 ORA-12162 calling SQL*Loader / SQL*Plus

Problem Description 

Running SQL*Loader as: 

sqlload userid=... control=... data=... log=... 

HOSTSTR logical has been set to same value as your connection string but without domain name. 

When you have specified connect string (ie. SCOTT/TIGER@DATABASE) but no domain you receive these errors: 

SQL*Loader-704: Internal error: ulconnect: OCIServerAttach [0] 
ORA-12154: TNS:could not resolve service name 

When you have not specified connect string (ie. SCOTT/TIGER) you receive these errors: 

SQL*Loader-704: Internal error: ulconnect: OCIServerAttach [0] 
ORA-12162: TNS:service name is incorrectly specified 

Your sqlnet.ora has: 

names.default_domain entry = world 

The syntax in your tnsnames.ora entry is correct. 

Your entry in tnsnames.ora does not include the .WORLD extension (default domain from sqlnet.ora). 

Solution Description 

Specify the .WORLD in your tnsnames.ora and also in your connect string. This will remove the error. 

Also, ensure you are not hitting [BUG:893290].   

Source : Oracle Metalink Note:116852.1

ORA-00439 When Trying To Create Materialized View With Enable Query Rewrite

ORA-00439 When Trying To Create Materialized View With Enable Query Rewrite

1. When trying to create a materialized view in SQL*Plus an error is returned:

  CREATE MATERIALIZED VIEW store_sales_mv

  PCTFREE 0 TABLESPACE mviews

  STORAGE (INITIAL 16k NEXT 16k PCTINCREASE 0)

  PARALLEL

  BUILD DEFERRED

  REFRESH COMPLETE

  ENABLE QUERY REWRITE

  AS

  SELECT

   s.store_name,

   SUM(dollar_sales) AS sum_dollar_sales

   FROM store s, fact

   WHERE f.store_key = s.store_key

   GROUP BY s.store_name;

  ORA-00439: feature not enabled: %s

     Cause: The specified feature is not enabled.

     Action: Do not attempt to use this feature.

3. The ENABLE QUERY REWRITE option is removed from the above statement and the error goes away and the statement processes successfully.

Solution Description:

The proper options must be installed to use some features of the materialized views. The query 'select * from V$option' will show what options are installed. The options needed to use enable query rewrite and refresh capabilities are:

Parameter                                              Value

Materialized view rewrite                        TRUE     -> For ENABLE QUERY REWRITE

Materialized view warehouse refresh     TRUE     -> For REFRESH

In addition, the following initialization parameters must also be set:

QUERY_REWRITE_ENABLED = default is set to FALSE, can change to TRUE

QUERY_REWRITE_INTEGRITY = ENFORCED, STALE_TOLERATED, TRUSTED

Source : Oracle Metalink 

Oracle Reports background engine stopping

In Forms 6i when a report is run, a background process called Reports Background Engine is created. Sometimes we want  this process to be terminated automatically when a report is completed and we exit from report.

Here is the PL/SQL code for this:

DECLARE
  pl_id ParamList;
BEGIN
  pl_id := Create_Parameter_List('dummy');
  Add_Parameter(pl_id,'ORACLE_SHUTDOWN',TEXT_PARAMETER,'Yes');
  Add_Parameter(pl_id,'PARAMFORM',TEXT_PARAMETER,'NO');
  Run_product(REPORTS,'<>',SYNCHRONOUS,     RUNTIME,FILESYSTEM,pl_id,NULL);
END;

How to Use ROLLUP and CUBE Operators in a GROUP BY Operation

The ROLLUP operator can be used in a the GROUP BY clause of a SELECT statement to group the selected rows based on the values of expressions, thus returning a single row summary for each group.  Thus, a SELECT statement using the ROLLUP operator returns both 'regular rows' and 'super-aggregate rows'.  Super-aggregate rows are rows that are derived by applying the same set of function(s) that were used to obtain the grouped rows.  In other words, super-aggregate rows contain sub-total values. 

The CUBE operator can be used to produce cross-tabulation values, thus produces totals in all possible dimensions. Thus CUBE produces both the line item totals (which can be produced by ROLLUP) in addition to column totals. 

A NULL value in a super-aggregate row represents 'all values'.  The GROUPING() function can be used to distinguish between a singular row with a null value in a column and a null value that represents the set of all values.  The expression in GROUPING() must match one of the expressions in the GROUP BY clause. GROUPING() returns 1 only if the value of expr in the row is a null representing the set of all values, else a 0 (zero) is returned.  

ROLLUP and CUBE operators can both be used in CREATE MATERIALIZED VIEW. 

The examples are based on the following table and data: 

SQL> desc test 
 Name                 Null?    Type 
 --------------------------------------- 
 YEAR                          NUMBER(4) 
 REGION                        CHAR(7) 
 DEPT                          CHAR(2) 
 PROFIT                        NUMBER 

SQL> select * from test; 

      YEAR REGION  DE     PROFIT 
---------- ------- -- ---------- 
      1995 West    A1        100 
      1995 West    A2        100 
      1996 West    A1        100 
      1996 West    A2        100 
      1995 Central A1        100 
      1995 East    A1        100 
      1995 East    A2        100 

7 rows selected. 

Example 1: ROLLUP 

SQL> select year, region, sum(profit), count(*) 
  2  from test 
  3  group by rollup(year, region); 

      YEAR REGION  SUM(PROFIT)   COUNT(*) 
---------- ------- ----------- ---------- 
      1995 Central         100          1 
      1995 East            200          2 
      1995 West            200          2 
      1995                 500          5 
      1996 West            200          2 
      1996                 200          2 
                           700          7 

7 rows selected. 


A summary of the above information in tabular form: 

Year Central(A1+A2)  East(A1+A2)  West(A1+A2)      
1995 (100+NULL)      (100+100)    (100+100)    500 
1996 (NULL+NULL)     (NULL+NULL)  (100+100)    200 
                                               700 


Example 2: ROLLUP and GROUPING() 

SQL> select year, region, sum(profit), 
  2         grouping(year) "Y", grouping(region) "R" 
  3  from test 
  4  group by rollup (year, region); 

      YEAR REGION  SUM(PROFIT)          Y          R 
---------- ------- ----------- ---------- ---------- 
      1995 Central         100          0          0 
      1995 East            200          0          0 
      1995 West            200          0          0 
      1995                 500          0          1 
      1996 West            200          0          0 
      1996                 200          0          1 
                           700          1          1 

7 rows selected. 

Note, GROUPING(expr) function returns 1 for super-aggregate rows if the null value for expr represents the set of all values.  


Example 3: CUBE 

SQL> select year, region, sum(profit), count(*) 
  2  from test 
  3  group by cube(year, region); 

      YEAR REGION  SUM(PROFIT)   COUNT(*) 
---------- ------- ----------- ---------- 
      1995 Central         100          1 
      1995 East            200          2 
      1995 West            200          2 
      1995                 500          5 
      1996 West            200          2 
      1996                 200          2 
           Central         100          1 
           East            200          2 
           West            400          4 
                           700          7 

Thus, in addition to the subtotals produced by ROLLUP, the CUBE operator  
completes all possible entries in a crosstab report. 

A summary of the above information in tabular form: 

Year Central(A1+A2)  East(A1+A2)  West(A1+A2)      
1995 (100+NULL)      (100+100)    (100+100)    500 
1996 (NULL+NULL)     (NULL+NULL)  (100+100)    200 
      100             200          400         700 


Example 4: CUBE and GROUPING() 

SQL> select year, region, sum(profit), 
  2         grouping(year) "Y", grouping(region) "R" 
  3  from test 
  4  group by cube (year, region); 

      YEAR REGION  SUM(PROFIT)          Y          R 
---------- ------- ----------- ---------- ---------- 
      1995 Central         100          0          0 
      1995 East            200          0          0 
      1995 West            200          0          0 
      1995                 500          0          1 
      1996 West            200          0          0 
      1996                 200          0          1 
           Central         100          1          0 
           East            200          1          0 
           West            400          1          0 
                           700          1          1 

10 rows selected. 

Note, GROUPING(expr) function returns 1 for super-aggregate rows if the null  
value for expr represents the set of all values.  

Important: 

The GROUP BY clause must contain all SELECT expressions that are not group function arguments or an ORA-979 (not a GROUP BY expression) will results.  

GROUPING(expr) will result in an ORA-909 (invalid number of arguments) if greater than one expression specified. 

Source : Oracle Metalink

Index skip scan hint explained

Index skip scan Hint Usage

Index skip scans improve index scans by nonprefix columns since it is often faster to scan index blocks than scanning table data blocks.

In this case a composite index is split logically into smaller subindexes. The number of logical subindexes depends on the cardinality of the initial column. Hence it is now possible to use the index even if the leading column is not used in a where clause.

Example:

SQL> create table t1(a number,b number);

Table created.

SQL> begin 

  2  for i in 1..1000

  3  loop

  4  insert into t1 values(i, 56);

  5  end loop;

  6  end;

  7  /

PL/SQL procedure successfully completed.

SQL> create index t1_i on t1(a,b);

Index created.

SQL> analyze table t1 compute statistics;

Table analyzed.

SQL> explain plan for select /*+ index_ss(t1 t1_i) */ * from t1 where b=56;

Explained.

SQL> start ?\rdbms\admin\utlxpls.sql

-----------------------------------------------------------------

| Operation  |  Name    |  Rows | Bytes|  Cost  | Pstart| Pstop |

-----------------------------------------------------------------

| SELECT STATEMENT      |     1K|    4K|   1001 |       |       |

|   INDEX SKIP SCAN T1_I|     1K|    4K|   1001 |       |       |-----------------------------------------------------------------

At which situation IN is better than EXIST ?

Well, the two are processed very differently

Select * from T1 where x in ( select y from T2 )

is typically processed as:

select * 

  from t1, ( select distinct y from t2 ) t2

 where t1.x = t2.y;

Sub query is evaluated, distinct'ed, indexed (or hashed or sorted) and then joined to the original table - typically.

As opposed to:

select * from t1 where exists (select null from t2 where y = x)

That is processed more like:

   for x in (select * from t1)

   loop

      if (exists (select null from t2 where y = x.x)

      then 

         OUTPUT THE RECORD

      end if

   end loop

It always results in a full scan of T1 whereas the first query can make use of an index on T1(x).

So, when is where exists appropriate and in appropriate?  Lets say the result of the sub query:

    (select y from T2)

is "huge" and takes a long time.  But the table T1 is relatively small and executing ( select null from t2 where y = x.x ) is very very fast (nice index on t2(y)).  Then the exists will be faster as the time to full scan T1 and do the index probe into T2 could be less then the time to simply full scan T2 to build the subquery we need to distinct on.

Let’s say the result of the sub query is small -- then IN is typically more appropriate.

If both the sub query and the outer table are huge -- either might work as well as the other -- depends on the indexes and other factors.

The ROT (rule of thumb) here is :

BIG outer query and SMALL inner query = IN. SMALL outer query and BIG inner query = WHERE EXISTS.