Jonathan Lewis
LightNews LightNews
jloracle.bsky.social
Jonathan Lewis
@jloracle.bsky.social
240 followers 46 following 380 posts
Nearly-retired Oracle performance specialist. 50 years using computers, 40 years self-employed, 35 years as a freelance Oracle specialist.
Posts Replies Media Videos
Pinned
jloracle.bsky.social
Jonathan Lewis @jloracle.bsky.social · Nov 21
Copying from ex-Twitter, and pinning:

The demon subdomain, including my old website disappeared a while ago, but there is a copy from December 2018 on the Wayback machine starting at: t.co/D9qBXqwJTu
https://web.archive.org/web/20181213051606/http://www.jlcomp.demon.co.uk/
t.co
jloracle.bsky.social
Now reading: "Blue Machine - How the ocean shapes our world" by @helenczerski.bsky.social

A fascinating mix of scientific and historical detail, told in an enjoyable narrative style.

Bought it after attending the 2nd in her series of lectures at Gresham College: www.gresham.ac.uk/watch-now/se...
Notes from a Large Planet: a citizen’s guide to the Earth
A six-part
www.gresham.ac.uk
November 17, 2025 at 8:02 PM
jloracle.bsky.social
Reference point for @oraclebase.bsky.social and @martinwidlake.bsky.social : the 2017 Ig Nobel prize for Physics was awarded to the author of a paper discussing whether cats are solids or liquids or both:

live.stemfellowship.org/the-ig-nobel...
The Ig Nobel Prize: Why are Cats Liquid? - Stem Fellowship
By Angela Zhu Did you know that there exists an amusing prize for the funniest scientists and their comical findings? In this series, we will explore the famous Ignoble Nobel Prize (abbreviated as “Ig...
live.stemfellowship.org
November 17, 2025 at 9:26 AM
jloracle.bsky.social
Thinking small but useful RESETTABLE, a recent (23.26, maybe older) v$ view seems to list resettable parameters:
GV$SYSTEM_RESET_PARAMETER

Name
------
INST_ID
NUM
NAME
TYPE
RESET_VALUE
DISPLAY_RESET_VALUE
ISSYS_RESETTABLE
ISPDB_RESETTABLE
ISINSTANCE_RESETTABLE
UPDATE_COMMENT
CON_ID
November 10, 2025 at 9:41 AM
Reposted by Jonathan Lewis
oraclebase.bsky.social
Small change, but so useful.

RESETTABLE Clause to in Oracle AI Database 26ai

oracle-base.com/articles/26/...

@OracleDatabase #database #26ai
November 10, 2025 at 8:21 AM
jloracle.bsky.social
Special day on the allotment tomorrow. My order of raspberry canes has arrived from James McIntyre james-mcintyre.co.uk/product-cate... so I'll be hard at work in the fruit cage

5 x Glen Doll (floricane)
5 x All Gold (primocane)
8 x Joan J (primocane)

with 2 "spare" Joan J for my old raspberry bed
November 8, 2025 at 6:34 PM
jloracle.bsky.social
Golden oldie time.

From 2007 - and nearly answering a recent question about index sizing: thoughts about space and I/O requests when you are heading for billions of rows in the database.

jonathanlewis.wordpress.com/2007/03/18/t...
November 4, 2025 at 8:24 PM
jloracle.bsky.social
How do you truncate a partition at the parent end of a referential integrity constraint?

You can't (error ORA-02266) but someone asked how today so I pointed them to a workaround I described in 2012 (after testing to see if it still worked on 19.11)

jonathanlewis.wordpress.com/2012/06/05/t...
Truncate Partition
A recent posting on the OTN database forum (which I didn’t answer correctly on my first attempt) raised the problem of truncating partitions when there are referential integrity constraints i…
jonathanlewis.wordpress.com
November 3, 2025 at 7:12 PM
jloracle.bsky.social
Someone has just sent me a communication through chat. It must be someone I know because I've set chat to respond only to people I follow, and I don't follow many.

But I can't use chat unless I supply my credit card number or a view of my face to an organisation I've never heard of - so no thanks.
November 2, 2025 at 6:24 PM
jloracle.bsky.social
Here's an article I published 12 years ago about rowids and sorting - if you store and report rowids the you will find that a columnn of type rowid sorts differently from a column where the rowid is stored in its character format.
See: jonathanlewis.wordpress.com/2013/12/01/r...
(Last tested 23.6)
Rowids
I have, in the past, used the dbms_rowid package to create rowids from block addresses (typically faking the first and last rowids that could appear in an extent) but I’ve just been sent a pi…
jonathanlewis.wordpress.com
November 2, 2025 at 6:21 PM
Reposted by Jonathan Lewis
connormcd.bsky.social
Heads up VirtualBox. If you've updated to 7.2.4 and found that your VMs will no longer start (Error: Failed to load R0 module), then uninstall your existing installation first (or uninstall the 7.2.4 you just installed) and then install 7.2.4 from scratch
November 1, 2025 at 5:50 AM
Reposted by Jonathan Lewis
dohdatabase.com
In the multitenant architecture in Oracle AI Database, the container database is essential. If you create it the right way, you can avoid some difficulties later on.

Here's my recommendation for creating container databases.
How I Think You Should Create Your Container Databases
In the multitenant architecture in Oracle AI Database, the container database is essential. If you create it the right way, you can avoid some difficulties later on. Here's my recommendation for creating container databases.
dohdatabase.com
October 28, 2025 at 6:31 AM
Reposted by Jonathan Lewis
danischnider.bsky.social
Since last week, I can finally use the QUALIFY clause in Oracle. If you never heard of this SQL extension, read my newest blog post:

danischnider.wordpress.com/2025/10/24/q...
QUALIFY: The WHERE Condition for Analytic Functions
Filtering analytic functions was not straight-forward in Oracle and most other databases. Oracle 26ai now supports the QUALIFY clause in SQL. It works like a WHERE condition, but can be used for an…
danischnider.wordpress.com
October 24, 2025 at 2:35 PM
Reposted by Jonathan Lewis
martindba.bsky.social
Today I revisited a 4yo post on one of SQLcl’s underrated features: autotrace — shows full session stats for a SQL statement (way beyond SQL*Plus). Still works in latest release. Updated prereqs too. More details here
🔗 martincarstenbach.com/2021/06/10/s...
#Oracle #SQLcl #performance #IKYK
SQLcl autotrace is way more than “just” (SQL*Plus) autotrace
As part of the research I did for the 2nd edition of our Exadata book I used session statistics quite heavily. Session statistics can provide additional insights in situations where the wait interf…
martincarstenbach.com
October 21, 2025 at 9:41 AM
jloracle.bsky.social
A new post about parallel DML and how to waste huge amounts of space in your database in no time at all.

jonathanlewis.wordpress.com/2025/10/10/m...
October 10, 2025 at 12:26 PM
jloracle.bsky.social
If you're in the mood to read a long and detailed article about shrinking indexes, here's one from 2022 that should meet your requirements:

jonathanlewis.wordpress.com/2022/09/02/s...
October 10, 2025 at 9:38 AM
jloracle.bsky.social
Here's a note I wrote for redgate / simpletalk a few years ago about basic index compression - costs and benefits: www.red-gate.com/simple-talk/...

It's part 5 of a series; part 4 was the technical bit about the mechanics of (basic) index compression. There's a link to it at the start of part 5.
October 9, 2025 at 8:34 AM
Reposted by Jonathan Lewis
sqldaily.bsky.social
Are you making any of these common #SQL mistakes?

Forgetting about NULL
Processing data in Java memory
Using JDBC Pagination to paginate large results
Using aggregate instead of window functions

@lukaseder.bsky.social explains what to do instead & lists 7 more
10 Common Mistakes Java Developers Make when Writing SQL
This article is part of a series. You might also like: 10 More Common Mistakes Java Developers Make when Writing SQLYet Another 10 Common Mistakes Java Developers Make When Writing SQL Java develop…
buff.ly
October 8, 2025 at 11:02 AM
Reposted by Jonathan Lewis
pewu78.bsky.social
Looks like it's not available anymore, my SR regarding this was closed with something like "Internal bug was submitted for investigation, but hey, we got that alternate features "Look-up By Error or Error Code's First Argument" or "Search by Stack Trace" added which replace it.
For real?🤦‍♂️
pewu78.bsky.social Piotr Wrzosek @pewu78.bsky.social · Jul 1
ORA-600/ORA-7445 Troubleshooting tools on MOS are not working for me - at least since yesterday.
October 8, 2025 at 11:32 AM
jloracle.bsky.social
This forum thread might be useful if you're playing around with 23ai and ONNX models (whatever they are) that don't come from Oracle Corp.
forums.oracle.com/ords/apexds/... - particularly the comment from "Peter"
October 7, 2025 at 1:07 PM
jloracle.bsky.social
I'm on the agenda for the #ukoug conference 2025.
9:00 a.m. on Monday morning (I tell myself that that's to encourage people to be on-site early enough to attend the opening keynote afterwards)

Abstract at:
jonathanlewis.wordpress.com/public-appea...
October 7, 2025 at 8:23 AM
jloracle.bsky.social
A variant of this topic just appeared on one of the Oracle forums - what do you get for the rowid in a join view where you haven't explicitly selected any rowids? (In my case - which table gets the delete when you delete from a join view)

jonathanlewis.wordpress.com/2022/05/31/j...
September 29, 2025 at 2:32 PM
jloracle.bsky.social
There are two ways I suprise myself when thinking about Oracle puzzles:

1) Struggling to unravel some internal detail then finding that I wrote about it 20 years ago

2) Discovering a "new" detail I should have noticed 20 years

Here's an example of (2):
jonathanlewis.wordpress.com/2025/09/29/r...
rowlen surprise
Here’s a little detail about Oracle’s calculation of user_tables.avg_row_len that I hadn’t noticed before – and I really should have noticed it years ago, so maybe I’m…
jonathanlewis.wordpress.com
September 29, 2025 at 1:46 PM
jloracle.bsky.social
There's a problem on the oracle-l listserver at present about an insert taking far too much time (and CPU). It's a known issue and there are 47 statistics in v$sysstat (19.11) with names like 'ASSM%' to help diagnose it.

How many do you think are described in the database reference manual?

None.
September 28, 2025 at 9:31 PM
jloracle.bsky.social
A question on the Oracle db discusion forum asked for advice on using "alter index .... monitoring usage" in 19c. The correct advice is "don't use it, Oracle replaced it with 'index usage tracking' in 12.2

Pt 1 of 3: jonathanlewis.wordpress.com/2024/03/15/i...

(links to pts 2 & 3 in the pingbacks)
September 26, 2025 at 9:49 AM
Reposted by Jonathan Lewis
tmmdv.bsky.social
Normally yes. If it asks for a bind value when running this then it uses varchar2 and causes the issue. It looks as if this happens only if you try to Run Script with a single SQL. Once there is more than 1 statement, it doesn't ask for bind and all is good.
September 25, 2025 at 11:09 AM