Tom Kyte

The following is a simplified version that exhibits the behavior in question. There are two tables T and F. 1% of the rows of table T are indexed by T_IX. Though F has the same number of rows as T_IX, F has 1% of rows "in common" with T_IX. The cardinality are as follows. <code> T ~ 1M T_IX ~ 10K F ~ 10K </code> The query is a semi-join that selects the rows from T that are both indexed in T_IX and "in common" with F. <code> select id from "T" where exists ( select null from "F" where T.num = F.num ) / </code> This query correctly returns 100 (0.01%) of the 1M rows in T. <code> The optimizer's plan is either i) Nested Loops join with F and T_IX, then perform the Table Access on T. PRO: Table Access on T is after the join. CON: Nested Loops starts. ii) Full Scanning T_IX then performing the Table Access on T. This is then hash joined with F. PRO: Full Scan of T_IX and the Table Access on T is batched. CON: Accessed 9,900 more rows from T then we needed (99% inefficient). </code> How can I encourage the optimizer to get the best of both worlds? <b>That is, how can I encourage the optimizer to perform a hash <i>index</i> join between F and the index T_IX <i>first</i>, and then, after many (99%) of T's rowids have been eliminated via that join, perform a Table Access (preferably batched) on T?</b> Does such an access path even exist? Note: The example below adds the hints in order to reliably generate the execution plans for the purpose of this example and is slightly modified to fit the format of this forum (mainly columns of the explain plans are removed). This example shows the two explain plans and the difference in Table Access on T. The Nested Loops join does perform the Table Access of T after the join and only has to get 100 rows, but the Hash join does not. The Hash join performs the Table Access on T before the join and has to get 10,000 rows. <code> SQL>create table T ( id number 2 , num number 3 , pad char(2e3) default 'x' 4 ) 5 / Table created. SQL>insert into T ( id, num ) 2 with "D" as 3 ( select level id 4 from dual connect by level <= 1e3 5 ) 6 select rownum 7 , decode( mod( rownum, 1e2 ), 0, rownum ) 8 from "D", "D" 9 / 1000000 rows created. SQL>create index T_IX on T ( num ) 2 / Index created. SQL>create table F ( num number 2 ) 3 / Table created. SQL>insert into F ( num ) 2 with "D" as 3 ( select level id 4 from dual connect by level <= 1e2 5 ) 6 select rownum 7 from "D", "D" 8 / 10000 rows created. SQL>...

I activated audit on some just for unsuccessful connection attempts ( my purpose was finding who/what locks some users ). This is something I had already done many times, always fine. However on a 19 version I noted something I could not find an explanation for: It appears that an additional audit popped out of nowhere, a select audit on sys owned tables: HIST_HEAD$ and HISTGRM$ I had to change audit_trail parameter, so no chance that they are some kind of inheritance from previous audit ( and I truncated aud$ in order to have a clean start ). However I would never ever dream of touching sys owned tables, my first commandment is "you shall not touch sys onwed tables ( unless under oracle support supervision, of course and with the only exception of aud$ ). I perused docs and Metalink but I was unable to find any relevant info on this. On old 10 and 11 version I never saw this. Is this a new kind of feature of 19 version? I even tried do disable this audit, no luck, from inside the pluggable db it complains because the operation is not allowed, from the root it gives me another error ... I hope that my poor English is clear enough Have a nice day Mauro Papandrea

Hello, As far as I understand it, Oracle processes regular expressions according to the POSIX standard, but also supports expressions that originate from Perl. Currently I had some missleading results when searching for a space. Theoretically, this should be found by the Perl-like expression \s. As I understand it, this is also noted in the Oracle documentation (https://docs.oracle.com/en/database/oracle/oracle-database/19/adfns/regexp.html Table 10-5). However, this does not seem to work in my example. Is this a bug - or is there a reason for this (for me unexpected) result? Should I forget about the Perl expressions and use only the POSIX expressions instead? Intention: Looking for ORA-01555, followed by a colon, space oder new line. <b>Unexpected result (expression wasn't found in string)</b> <code>SELECT 1 AS EXPECTED_RESULT, REGEXP_COUNT ('ORA-01555 caused by SQL statement below', 'ORA-01555[:|\s|\n]') AS REGEXPCNT FROM DUAL;</code> <b>Expected result if using :space: instead of \s</b> <code>SELECT 1 AS EXPECTED_RESULT, REGEXP_COUNT ('ORA-01555 caused by SQL statement below', 'ORA-01555[:|[:space:]|\n]') AS REGEXPCNT FROM DUAL;</code> Best regards, Marian

Hello, Tom We have been facing the slow rollback after killing job of huge transaction, then we discovered several ways to speed up the rollback process. So one possibility is altering hidden parameter "_cleanup_rollback_entries" from default value (100) to be 400. However, I still in doubt about the origin of 400 value. I saw some couple documents also said increase the value to be 400. But no explanation why it must be 400? Therefore, Please answer inline questions below 1) Why the recommended value is 400, how come of this value? 2) If there is larger transaction than mentioned is killed, how I ensure that value (400) is still effective for speedup rollback process? Thank in advance

Hi, <code>CREATE TYPE phone_typ AS OBJECT ( country_code VARCHAR2(2), area_code VARCHAR2(3), ph_number VARCHAR2(7)); / CREATE TYPE phone_varray_typ AS VARRAY(5) OF phone_typ; / CREATE TABLE dept_phone_list ( dept_no NUMBER(5), phone_list phone_varray_typ); INSERT INTO dept_phone_list VALUES ( 100, phone_varray_typ( phone_typ ('01', '650', '5550123'), phone_typ ('01', '650', '5550148'), phone_typ ('01', '650', '5550192'))); / INSERT INTO dept_phone_list VALUES ( 200, phone_varray_typ( phone_typ ('02', '750', '5550122'), phone_typ ('02', '750', '5550141'), phone_typ ('02', '750', '5550195'))); /</code> I can execute below query <code>select * from table(select phone_list from dept_phone_list where dept_no=100)</code> I can't execute as below Is there a way to do this ? <code>select * from table(select phone_list from dept_phone_list) ORA-01427: single-row subquery returns more than one row</code> Thanks, Girish

We would like to encrypt data at rest in an oracle database column (in one specific database table only) using an encryption key held externally in a vault. Does Oracle provide standard interfaces to make API calls ? The encryption key should not persist in the database in any form.

Hi, I am carrying out an update on dept table with update query as - "update dept set dname = initcap(dname) where deptno=20;" As, I have not committed, row lock will be placed on the row. I want to find out the rowid of dept table locked from V$LOCK. Currently, I am not able to figure out how to get it. Is there any other way?

Hello, Could you please clarify the meaning of the view V$RESERVED_WORDS and especially its contents? At first glance, the documentation has a clear explanation. The view contains reserved words, and columns RESERVED, RES_TYPE, RES_ATTR, and RES_SEMI describe, for which purposes these words can or cannot be used. But more than 90% of words are not actually reserved. In my case (Oracle Database 19c) the view contains 2491 words, and 2361 of them have value 'N' in all columns. Even if we exclude duplicates, it is still 2339 words: <code>select count(*) from v$reserved_words where reserved = 'N' and res_type = 'N' and res_attr = 'N' and res_semi = 'N' and duplicate = 'N' ; COUNT(*) ---------- 2339</code> What is the risk of using these words as object names or as variables in PL/SQL?

Hi Senior, My database is 12C PLSQL. Is it possible to alter table and add IDENTITY for primary key, if the data in the table is beyond <b>200Crore</b> or <b>2Billion</b> data. As I am concerned about database crashing or Cache overflow. Please guide me. In the code I am creating identity in table "fac_fix_original" <code> ALTER TABLE fac_fix_original ADD (SEQ_ID NUMBER GENERATED ALWAYS AS IDENTITY(START WITH 1 INCREMENT BY 1)); </code>

Good day, I want to backup my Snippets but cannot find the file to back it up as I cannot locate the XML file with my Snippets therein. I went to system23.1.1.345.2114 and in there I went to o.sqldeveloper.snippet and this folder is empty despite me setting "Sow hidden files"

We have a context index that is SYNCing every 6 seconds. Here is the DDL for the index: CREATE INDEX "SSS"."QUOTE_NUMBER_CONTEXT" ON "SSS"."QUOTE" ("NUMBER_") INDEXTYPE IS "CTXSYS"."CONTEXT" PARAMETERS ('SYNC (EVERY "SYSDATE+1/14400")'); This was working perfectly for many months. We then did patching on the database and server and there was a daylight savings change. Now, the scheduler is showing that the index is still SYNCing every 6 seconds, but any new data on the table is NOT being updated on the index until exactly 1 hour later! We have no idea why this is happening and we have checked all the parameters, with no luck. Thanks.

Hello, Mastering Oracle PL/SQL book has been released 21 years ago with Connor McDonald as one of the author: https://github.com/Apress/mastering-oracle-pl-sql I would like to know what you would modify if you would update the book for Oracle 19c: - any topic to be removed ? - any topic to be added ? - any important change to an existing topic ? Thanks.

Hi, Recently while preparing to run the Release Upgrade for 19.24 I was doing a cold back of my ORACLE_HOME and I noticed that I had a ton of old .log, .trc, and .trm files. I deleted old files in the trace directory, audit directory, and some of the log directories. I also leanred that OPatch now has a nice option to clean up .patch_storage which was really full of old files that I had always been afraid to touch. Is there an approved list of directories to periodically purge (maybe 30+ day old files) for ORACLE_HOME and the oraInventory? Thanks, Alan

Hi Tom & Team, we have ORACLE Database 19C and it is editionable. ALPHA/BETA/ORA$BASE we have created our tables with XXX_E (non editionable) and created views(editionable) on top of it. Active edition is ALPHA. in BETA(Inactive edition), when we increase column length for one of table, creating invalids in ALPHA edition. Can you advise how to find why are those invalid objects are getting created? no errors are shown in dba_errors. when we recompile, all invalids are getting cleared.

If I only wanted to do multi-master replication of just a few tables (<5) between two Oracle databases in Oracle 19c here in 2023, without having to pay for the Golden Gate license, what are my options? I want something like the "Advanced Replication" feature from the older versions of Oracle (I remember using it back in Oracle v7), but I understand this feature was desupported long ago. Then there was Oracle Streams, CDC, and updatable Materialize Views... all of which seemed to be desupported now as of Oracle 19c in favor of doing all replication activities with Golden Gate. I just discovered "Golden Gate Free", but this is limited to databases less than 20G in size. My database is bigger. I only need to do replication between two databases for about 3 tables for about a year while we align other activities to migrate fully off of one of those databases. But it must support OLTP between the databases and keep the 3 tables in sync in "near real time". In other words, both databases need to allow concurrent read/write of these tables. And ideally, conflict resolution should be done via a "latest time stamp wins" approach. What would you do if you were me? Thanks, Jack

Hi Tom, I have sql statement with pdf book url attribute. I need virtual column till me the url is valid or not found. So I need page http status code either java function in oracle apex, or sql query. example: select title_no, title_name, pub_date, 'https://library.iugaza.edu.ps/thesis/'||a.TITLE_NO||'.pdf' as pdf_url from lib_master. The result as follow: TITLE_NO TITLE_NAME PUB_DATE PDF_URL -------- ------------------------------------------------------------------------------------------------- ------- ---------------------------------------------- 55171 measurement of radon and ils daughter's concentrations in indoor and outdoor throughout gaza strip 2003 https://library.iugaza.edu.ps/thesis/55171.pdf 55172 ance system" in the gaza strip using geographical information system and decision support system 2003 https://library.iugaza.edu.ps/thesis/55172.pdf 55234 study of the onset of the earth magnetosphere under the influence of the solar wind 2003 https://library.iugaza.edu.ps/thesis/55234.pdf 55335 study of the measurement of labor productivity in the palestinian construcrtion industry 2003 https://library.iugaza.edu.ps/thesis/55335.pdf I need display attribute return url status code, i.e. 200, 404, etc. Thank you.

Hi Gurus, I have a schema as described in the link https://livesql.oracle.com/ords/livesql/s/ct0zdah8slxq66h8fnpfhwdby. <code>drop table t; create table t ( instance number , id_sup number , id number , srt number , v01 number , v02 number , v03 number , value_01 number , value_02 number ) ; insert into t values (200,1,2,2,26,20,13,20,13); insert into t values (200,1,3,3,30,23,15,23,23); insert into t values (200,1,4,4,18,13,9,13,13); insert into t values (200,1,5,5,22,16,11,16,16); insert into t values (200,1,75,6,24,18,12,24,12); insert into t values (200,74,76,8,26,20,13,20,26); insert into t values (200,74,77,9,28,21,14,28,21); insert into t values (200,74,10,10,28,21,14,14,21); insert into t values (200,74,79,11,34,26,17,26,17); insert into t values (200,74,80,12,22,16,11,16,16); insert into t values (200,74,81,13,17,13,8,13,17); insert into t values (200,74,82,14,27,20,14,20,27); insert into t values (200,74,83,15,22,16,11,11,11); insert into t values (200,74,84,16,24,18,12,18,24); insert into t values (200,85,20,18,18,13,9,9,13); insert into t values (200,85,21,19,34,26,17,17,34); insert into t values (200,85,22,20,21,16,11,11,11); insert into t values (200,85,86,21,19,14,9,9,9); insert into t values (200,23,30,23,29,21,15,15,21); insert into t values (200,23,24,24,30,23,15,15,30); insert into t values (200,23,90,25,30,23,15,23,23); insert into t values (200,23,26,26,30,23,15,30,30); insert into t values (200,23,87,27,30,23,15,23,23); insert into t values (200,23,88,28,24,17,12,17,24); insert into t values (200,29,89,30,16,12,8,8,8); insert into t values (200,29,94,31,40,30,20,30,40); insert into t values (200,29,91,32,21,16,11,21,16); insert into t values (200,29,92,33,36,27,18,27,18); insert into t values (200,29,95,34,38,28,18,28,38); insert into t values (200,29,93,35,36,27,18,18,18); select * from t order by srt ; </code> I want to have sub-totals like this way : <code> INSTANCE ID_SUP ID SRT V01 V02 V03 VALUE_01 VALUE_02 ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- 200 1 2 2 26 20 13 20 13 200 1 3 3 30 23 15 23 23 200 1 4 4 18 13 9 13 13 200 1 5 5 22 16 11 16 16 200 1 75 6 24 18 12 24 12 200 TOTAL 120 96 77 200 74 76 8 26 20 13 20 26 200 74 77 9 28 21 14 28 21 ...

I'm using a UDT with dataset inside there as a table and i want to use that UDT on Merge statement. When using that UDT, i put that UDT inside "ON (" as following: "... ON (SELECT * FROM TABLE (UDT)) ..." then, how can i to use that UDT inside INSERT statment putting a only variable in the "VALUES" statment? For Example: MERGE INTO TABLE1 T1 USING (SELECT * FROM TABLE(UDT)) T2 ON (T1.X = T2.Y) WHEN NOT MATCHED THEN INSERT VALUES UDT; //<-------- here, i want only use UDT and that's all, how can i create something like this? The Table1 (T1) and UDT (T2) has the same structure

Hi Tom, first at all, thanks for all your support. I wish a blessed christmas time for you, your team and families. I'm looking forward for the best practise of a cursor to fetch just one (or a few) row(s) on my own sorting and lock them. Without locking the whole resultset or include all rows with the condition. <code> select * from MyTable where < MyCondition > order By < MyOrderby > fetch first 1 row only for update skip locked; </code> Unfortunately the combination of fetch-first-only and for update causes a ORA-02014 :( - without fetch-first-only it will lock the whole resultset - a rownum condition instead of fetch-first-only will not consider my orderby - an inner subselect with fetch-first-only will not skip the locked rows and finally fetch zero rows if the inner sql only fetch already locked rows <code> select * from MyTable where Rowid in (select Rowid from MyTable where < MyCondition > order By < MyOrderby > fetch first 1 row only) for update skip locked; </code> Whats your best practice or advice? Thanks, Norman

We have a production DB that is used for reporting is been attempted to clone with NetApp Snapmirror technology. The current clone is done with old method placing the tablespaces of PROD DB in backup mode and then copying the files using OS ?cp? tool to the designated clone DB directories and run the necessary DB layer clone steps. This works fine but now taking extra long time to complete and impacting business functions. With NetApp SNapmirror technology all steps are working up to the DB layer clone steps. There is an issue with very last steps of recovering the DB after SnapMirror has performed. The step used is ?alter database recover automatic using backup controlfile until cancel? as that seems to be the only option available according Oracle Doc ID: DATAFILE NEEDS MORE RECOVERY TO BE CONSISTENT ORA-1194 ORA-1547 ORA-1110 (Doc ID 1528788.1) - Scenario 2: Backup Controlfile is Used for recovery. Doing this recovery interactively via SQL*PLUS needs placing the redo log file path when prompted. Then DB recover successfully. See below for output. SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL ; ORA-00279: change 75786588505 generated at 12/06/2024 13:41:35 needed for thread 1 ORA-00289: suggestion : /arch_poct/archive/OFPOC/arch_1186748276_1_18.arc ORA-00280: change 75786588505 for thread 1 is in sequence #18 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} '/redo_poct/oradata/OFPOC/redo06.log' Log applied. Media recovery complete. SQL> alter database open resetlogs ; Database altered. But this clone process has to be done on daily basis and must be automated. When scripted there is no way with SQL*PLUS to be read from a file containing `/redo_poct/oradata/OFPOC/redo06.log' for it?s standard input. Below is the script #! /usr/bin/ksh sqlplus "/ as sysdba" < /projects/of/poc/spt/log/setup_sql_redo_20241217.log <<EOF set echo on alter database recover automatic using backup controlfile until cancel; EOF Where cat /projects/of/poc/spt/log/setup_sql_redo_20241217.log '/redo_poct/oradata/OFPOC/redo06.log' Output ? /projects/gen/ksh/working> ./TestRecovery.sh SQL*Plus: Release 11.2.0.2.0 Production on Tue Dec 17 17:29:00 2024 Copyright (c) 1982, 2010, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> SQL> alter database recover automatic using backup controlfile until cancel * ERROR at line 1: ORA-00279: change 75787505640 generated at 12/16/2024 11:21:36 needed for thread 1 ORA-00289: suggestion : /arch_poct/archive/OFPOC/arch_1186748276_1_157.arc ORA-00280: change 75787505640 for thread 1 is in sequence #157 ORA-00278: log file '/arch_poct/archive/OFPOC/arch_1186748276_1_157.arc' no longer needed for this recovery ORA-00308: cannot open archived log '/arch_poct/archive/OFPOC/arch_1186748276_1_15...