oracle – Why doesn’t /*+ NO_INDEX*/ hint , affect the “execution plan”?

I have a sample table with below structure :

create table mi_dimcustomer
(customer_num    number(10),
 <other columns> <data types>)

And there is a unique index on column customer_num. I’m trying to hint the optimizer not to use this index (just for the sake of practice) like this :

select /*+gather_plan_statistics*/ /*+ no_index(t idx1_dimcustomer) */
 *
  from mi_dimcustomer t
 where t.customer_num = 12;

But in the execution plan , I can see that the optimizer is still using the index! This is the way I’m capturing the execution plan :

Step-1)

    select sql_id, child_number, sql_text
      from v$sql
     where sql_text like '%where t.CUSTOMER_NUM = 12%';

Step-2)    

select *
  from table(dbms_xplan.display_cursor('2qataxp9mahpj',
                                       '0',
                                       'ALLSTATS LAST +COST +OUTLINE'))

And you can see the execution plan below:

SQL_ID  2qataxp9mahpj, child number 0
-------------------------------------
select /*+gather_plan_statistics*//*+ NO_INDEX(t idx1_dimcustomer) */ * 
from mi_dimcustomer t where t.CUSTOMER_NUM = 12
 
Plan hash value: 3784660444
 
-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name             | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                  |      1 |        |     2 (100)|      1 |00:00:00.01 |       3 |
|   1 |  TABLE ACCESS BY INDEX ROWID| MI_DIMCUSTOMER   |      1 |      1 |     2   (0)|      1 |00:00:00.01 |       3 |
|*  2 |   INDEX UNIQUE SCAN         | IDX1_DIMCUSTOMER |      1 |      1 |     1   (0)|      1 |00:00:00.01 |       2 |
-----------------------------------------------------------------------------------------------------------------------

Why is this happening?

Thanks in advance

select – To trim particular value in a column in Oracle sql

As you ask specifically for a RegEx solution, let’s start with

SELECT
  text_1,
  text,
  REGEXP_REPLACE(nvl(l.text_1, l.text),
                 '^'||21810||'=||'||21810||'=',
                 '|'||21810||'='||'B1'
                ) as checked,
  REGEXP_REPLACE(nvl(l.text_1, l.text),
                 '^(d+=)C1(||21810=)C2',
                 '12B1'
                ) as suggested,
  REGEXP_REPLACE(nvl(l.text_1, l.text),
                 '^(d+=)C1(||21810=)C2',
                 '1|||||2B1'
                ) as suggested2
FROM table_1 l
;

Not quite sure, how you ended up with the RegEx in your question. However, you are searching for ^21810=||21810= (21810= at the start of the string, or |21810= anywhere in the string) and replacing the match with |21810=B1.

My attempt checks the whole string, and requires

  • one or more numbers followed by an equal sign at the beginning of the string (preserved for later reference),
  • followed by C1 (which is not preserved for later),
  • followed by ||21810= (preserved), and
  • C2 ending the string (not preserved).

The preserved parts plus B1 are returned.

However, starting from your requirements as stated and your sample data, you could work as well with, e.g.:

SELECT
  text_1,
  text,
  REPLACE(REPLACE(nvl(text_1, text),'C1',''), 'C2', '') || 'B1' as suggested,
  REPLACE(REPLACE(nvl(text_1, text),'C1','|||||'), 'C2', '') || 'B1' as suggested2
FROM table_1
WHERE instr(nvl(text_1, text), '21810') > 0
;

It filters the strings to change via the WHERE clause, drops the unwanted parts with empty replacements, and adds B1 at the end.

NB: Your sample output includes five additional pipe characters. These are missing from your stated requirements. I provided both options for your reference.

See it in action: SQL Fiddle

Please comment, if and as this requires adjustment / further detail.

sql – I am using Oracle EBS R2 Is there any solution to get the IP address of Client machine

I have tried the below but it is based on user. I am trying to get the client machine IP address no matter which user is connected to that.

SELECT distinct 
       c.CLIENTIP
  FROM v$process a, 
       v$session b, 
       fnd_logins c, 
       fnd_user d
 WHERE a.pid = c.pid
   AND d.user_id = c.user_id
   and d.user_name = '&User_name'
   AND a.addr = b.paddr
   AND c.end_time IS NULL;

oracle – Dúvida para filtrar informações em um determinado período, no PL/SQL

Como faço para retornar todas informações do primeiro dia do ano ate o ultimo dia do mês anterior? Obs.: Minha Query só apresenta ate o dia 1 do mês anterior

where trunc(a.dt_entrada) BETWEEN TRUNC(sysdate, ‘year’) and trunc (add_months(sysdate, 1), ‘month’)

How to get Full Plan in Oracle?

I’m still a novice in SQL, and I would really appreciate it if you could help me.
How do I get SQL Plan for my query? I have used this for getting a short plan:

EXPLAIN PLAN FOR
SELECT *
  FROM custinfo@smartdev2 s_cs
 WHERE s_cs.idcust = (SELECT b_cust_id
                        FROM mlb_test.mt_operation_out
                       WHERE id = 1230);

SELECT plan_table_output
  FROM TABLE (DBMS_XPLAN.display ('plan_table', NULL, 'basic'));

But what do i need to use for me to get entire execution plan?

Does Oracle Database support nested transactions?

Does current version of Oracle Database (21c) support nested transactions?

I’m aware of Oracle DB supporting savepoints. However I’m looking for a way to make COMMINT not to write to disk for purpose of running tests.

Oracle Bugs Founders – Credentials List

I heard there is exists list of credentials founders of Oracle Bugs. Errors in code which consider as bug by Oracle during P1 SR for example.

Our team have found official bug and we are waiting for one-off patch.

Meantime is it possible to stay in history by being in this list? If it exists.

For some of us it will be good point in CV.

Here is my topic about it – Oracle Enterprise Manager 13c – query used in metric

restore – Oracle 11g XE – import very old .dump without log file nor schema name

Is there a way to restore very old oracle backup .dump without log file nor schema name?
I have only old .dump file.

I’ve tried:

imp username/password@dbname file=filename.dmp full=y

with this result:

Import: Release 11.2.0.2.0 - Production on Fri Jul 16 12:05:23 2021

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.


IMP-00058: ORACLE error 12154 encountered
ORA-12154: TNS:could not resolve the connect identifier specified
IMP-00000: Import terminated unsuccessfully

oracle – Data Pump Job error caused by time zone

I’m running a impdp command for a dmp file, but when i read the log file, it show a error like this:

ORA-39097: Data Pump job encountered unexpected error -30094
ORA-39065: unexpected master process exception in DISPATCH
ORA-30094: failed to find the time zone data file for version 4 in $ORACLE_HOME/oracore/zoneinfo

I’ve checked version using SELECT * from v$timezone_file;, and the result displayed is 14.

And i’m running the impdp on server with the following properties:

Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for 64-bit Windows: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

I’ve read about upgrading versions, but I don’t quite understand the concept of it, if someone can help me.

Syntax for openquery for oracle linked server with an merge into using update statement

I have an ORACLE Update Statement which I want to run on sql server as a step in a scheduled job. The ORACLE Update Statement, as it can be executed on sqldeveloper e.g., reads as follows:

merge into TABLEA A
using
(
select

from TABLEB B
..)
when matched then
update set A.X = B.Y

As you can see this is a ORACLE merge into statement.
I have searched this forum and other sites, but couldn’t find what the syntax is
when using SQL server openquery. When I write the statement like:

update
openquery
(linkedserver,
‘oracle sql statement as above’)
I get an error saying Incorrect syntax near ‘)’.
The SQL Server Version is 15.0 on Windows Server 2019 and the ORACLE DB is Version 19c.
Thanks for any hint on this issue.