DBA Blogs

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.

 This video locally installs NVIDIA SANA which is a text-to-image framework that can efficiently generate images up to 4096 × 4096 resolution.

Code:

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?

Getting strange or unexpected results from SDO_GEOM.SDO_INTERSECTION?

Chances are that inverting the input geometries the result will be different.

This happens on Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.21.0.0.0.

I did find some cases where the intersection of A with B returns a simple polygon whereas B intersect A returns a geometry collection, that is a mix of point, lines and polygons.

Now, if I feed the result to function SDO_GEOM.SDO_BUFFER with a small negative radius, in one case I still get a valid polygon whilst in the other case I get a NULL.

As you can imagine this is somewhat disturbing as it may lead to a completely wrong result.

One workaround would be to always perform both operations and compare the results, albeit it's not clear whichever is the correct one in case they differ and it's not even clear if I should rely on the area of the resulting polygons for considering them "equal" or "equivalent".

In theory I'd say that the intersection of A with B should always match the intersection of B with A, however this is not what's happening so I am left wondering if this is a bug or an unpleasant side effect of the algorithm being used.

What I believe is really a bug instead is the behavior of SDO_GEOM.SDO_BUFFER when the input geometry is one of these geometry collections (SDO_GTYPE = 2004).

I'll submit these case to Oracle Support and see what they say, luckily I can supply a couple of test cases for them to look at.

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.

I am not a very good blogger. Only four posts in 2024. I will need to pick it up because I just increased my spend on AWS for this blog. This site kept going down and I finally spent a few minutes looking at it and found that it was running out of memory. I was running on a minimal t2.micro EC2 which has 1 virtual CPU and 1 gigabyte of memory with no swap. So, rather than just add swap I bumped it up to a t3.small and paid for a 3-year reserved instance. It was about $220, nothing outrageous. Worth it to me. I still have two years left on the t2.micro reserved instance that I was using for the blog so I need to find a use for it, but I don’t regret upgrading. I spent a little money to make the site more capable so now I must write more posts!

I have three Machine Learning books to work through. After finishing my edX ML class with Python I picked up a book that covered the same topics and used the same Python libraries and I have been steadily working through it: Machine Learning with PyTorch and Scikit-Learn. I am on chapter 9 and want to get up to chapter 16 which covers Transformers. My edX class got up to the material in chapter 15 so the book would add to what was taught in the class. Earlier chapters also expand on what was taught in the class. The cool thing about the book is the included code. Lots of nice code examples to refer to later. Even the Matplotlib code for the graphs could be very helpful to me. I’ve gotten away from using Matplotlib after using it in my PythonDBAGraphs scripts.

My birthday is the day after Christmas, so I get all my presents for the year at the end of December. I got two ML books for Christmas/Birthday. Probably the first I will dive into after I finish the PyTorch/Scikit-Learn book is Natural Language Processing with Python. This is available for free on the NLTK web site. I have the original printed book. I think once I get through the Transformers chapter of the PyTorch book it makes sense to look at natural language processing since that is what ChatGPT and such is all about. I have played with some of this already, but I like the idea of working through these books.

The second book that I got in December as a present seems more technical and math related, although the author claims to have kept the math to a minimum. It is Pattern Recognition and Machine-Learning. This might be a slower read. It seems to be available for free as a PDF. ChatGPT recommended this and the NLTK book when I was chatting with it about my desire to learn more about Artificial Intelligence and Machine Learning.

I have all these conversations with ChatGPT about whether it makes sense for me as an Oracle database specialist to learn about machine learning. It assures me that I should, but it might be biased in favor of ML. Oracle 23ai does have AI in its name and does have some machine learning features. But it could just be a fad that blows over after the AI bubble bursts as many have before it. I can’t predict that. My current job title is “Technical Architect”. I work on a DBA team and I’m in the on-call rotation like everyone else, but my role includes learning about new technology. Plus, I think that I personally add value because of some of the computer science background I had in school and that I have been refreshing in recent years. Plus, I need to get some level of understanding of machine learning for my own understanding regardless of how much we do or don’t use it for my job. Just being a citizen of the world with a computer science orientation I feel is enough motivation to get up to speed on recent AI advancements. Am I misguided to think I should study machine learning?

Despite all this talk about AI, I am still interested in databases. I have this folder on my laptop called “Limits of SQL Optimization”. I had these grandiose ideas about writing interesting blog posts about what SQL could or couldn’t do without human intervention. Maybe SQL is a little like AI because the optimizer does what a human programmer would have to do without it. I’m interested in it all really. I like learning about how computer things work. I’ve spent my career so far working with SQL statements and trying to understand how the database system processes them. I thought about playing with MySQL’s source code. I downloaded it and compiled it but that’s about it. Plus, some day we will have Oracle 23ai and I’ll have to figure out how to support it in our environment, even if we do not use its AI features. Anyway, I’m sure I will have some non-AI database things to post about here.

To wrap up I think I may have some things to post on this blog in 2025, so it’s worth the extra expense to keep it running. Could be some more machine learning/artificial intelligence coming as I work through my books. I still have databases on the brain. Wish you all a great new year.

Bobby

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 ...