Просторы интернета

19 Февраль 2015
Earlier
Вс, Май 09, 2021

A recent question on the Oracle-L list server asked “Is there a way I can use an SQL_PATCH to add a driving_site() hint to a query?” to which the reply was: “Just do it, it should work.” Unfortunately this produced the response: “I'm on 11.2.0.4, I've tried it, it [...]

Source: jonathanlewis.wordpress.com
Вс, Апр 25, 2021
select title, short_url from v$blog where pubDate>=systimestamp - interval '5' month; with v$blog as ( select title ,to_timestamp_tz(pubDate,'DY, dd mon yyyy hh24:mi:ss TZR') pubDate ,short_url [...]
Source: orasql.org
Вт, Апр 13, 2021

An old question with a little extra twist came up on the Oracle Developer forum recently – someone wanted to export a table into a CSV file but wanted to do a character set conversion at the same time because various “non-printable” characters were getting corrupted.

Looking at the code [...]

Source: jonathanlewis.wordpress.com
Пн, Апр 05, 2021

A recent question on the Oracle Developer forum posed an interesting question on “finding the closest match” to a numeric value. The OP supplied SQL to create two tables, first a set of “valid” values each with an Id, then a set of measures. The requirement was to find, [...]

Source: jonathanlewis.wordpress.com
Пн, Мар 29, 2021
Продолжая про event, при разборе неприятной проблемы ORA-600 [532] leads to ORA-700 [ksepop:1 ksepop recursion ] аналитик поддержки с оговорками* рекомендовал в целях диагностики установить: , что (после применения параметра/рестарта) приводит и интересному эффекту в части использования PL/SQL, %rowtype и EXECUTE IMMEDIATE с IN OUT параметрами (**): При смене порядка [...]
Source: iusoltsev.wordpress.com
Пн, Мар 29, 2021
В начале месяца Алексей Фролов (Спортмастер) на семинаре RuOUG рассказал о непростом пути обновления на версии 19c В дополнение к теме имеется любопытный опыт работы с Bug 31888148 — ORA-1555 is not reported in alert log after RU 19.4.0.0.0 (Doc ID 31888148.8), к счастью, уже поправленном в современных версиях: В [...]
Source: iusoltsev.wordpress.com
Чт, Мар 25, 2021

From time to time I see people on the public Oracle forums asking whether they should adjust one or other of the resource-related parameter – and it's often the hidden or derived parameters that get targetted for this type of request. For example I came across a request fairly recently [...]

Source: jonathanlewis.wordpress.com
Пт, Мар 12, 2021

A request for help came up some time ago on ODC reporting a query that was hanging when it included a sequence.nextval. In fact the intial “query” was an “insert / select” with a select that was a join of two remote tables. Making the fairly automatic assumption that [...]

Source: jonathanlewis.wordpress.com
Пн, Мар 08, 2021

This note is a followup to a post from a few years back (originally dating further back to 2012) where I described an inconsistency that appeared when join elimination and deferrable constraints collided. The bug resurfacted recently in a new guise in a question on the Oracle Developer [...]

Source: jonathanlewis.wordpress.com
Чт, Мар 04, 2021

One of the less well-known hints is the hint /*+ use_nl_with_index() */ (link to 19c reference manual) which appeared in the 10g timeline, where the manuals supply the following description:

The USE_NL_WITH_INDEX hint will cause the optimizer to join the specified table to another row source with a nested [...]

Source: jonathanlewis.wordpress.com
Вт, Фев 16, 2021

There's a thread on the Oracle Database Forum at present where someone has supplied a script to create some data that's guaranteed to reproduce wrong results (provided your system stats and optimizer parameters are at their default values). They've even supplied a link to the [...]

Source: jonathanlewis.wordpress.com
Пн, Фев 01, 2021

Here's a quirky little detail – probably totally irrelevant to virtually everyone – that came up in a question on the Oracle Developer Forum a couple of days ago. It concerns the way Oracle stores and records default values for columns, and it also does a hat-tip to the [...]

Source: jonathanlewis.wordpress.com
Пн, Фев 01, 2021

I've hardly ever touched the pivot/unpivot feature in SQL, but a recent comment by Jason Bucata on a note I'd written about Java names and the effects of newer versions of Oracle allowing longer object and column names prompted me to look at a script I wrote several [...]

Source: jonathanlewis.wordpress.com
Вт, Янв 26, 2021

At the end of the previous post on index hints I mentioned that I had been prompted to complete a draft from a few years back because I'd been sent an email by Kaley Crum showing the optimizer ignoring an index_rs_asc() hint in a very simple query. Here, [...]

Source: jonathanlewis.wordpress.com
Пн, Янв 25, 2021

I've lost count of the number of times I've reminded people that hinting (correctly) is hard. Even the humble /*+ index() */ hint and its close relatives are open to misunderstanding and accidental misuse, leading to complaints that “Oracle is ignoring my hint”.

Strange though it may seem, I'm still [...]

Source: jonathanlewis.wordpress.com
Ср, Янв 20, 2021

This is a list of possible explanations of errors that you might see in the Hint Report section of an execution plan. It's just a list of the strings extracted from a chunk of the 19.3 executable around the area where I found something I knew could be reported, [...]

Source: jonathanlewis.wordpress.com
Ср, Янв 20, 2021

A little case study based on an example just in on the Oracle-L list server. This was supplied with a complete, working, test case that was small enough to understand and explain very quickly.

The user created a table, and used calls to dbms_stats to fake some statistics into place. [...]

Source: jonathanlewis.wordpress.com
Пн, Янв 18, 2021

Here's an anomaly that appeared in a question on the ODC recently about tweaking the output of dbms_metadata.get_ddl(), As the title suggests, though, this posting isn't about the dbms_metadata package it's about supplemental logging and something that should not to be possible and may have unfortunate side effects.

We start with [...]

Source: jonathanlewis.wordpress.com
Чт, Янв 14, 2021

Reading Richard Foote's latest blog note about automatic indexing and “non-equality” predicates I was struck by a whimsical thought about how the optimizer handles “between” predicates. (And at the same time I had to worry about the whimsical way that WordPress treats “greater than” and “less than” symbols.)

It's probably [...]

Source: jonathanlewis.wordpress.com
Ср, Янв 13, 2021

This is a note I drafted in 2018 but never got around to publishing. It's an odd quirk of behaviour that I discovered in 12.2.0.1 but I've just checked and it's still present in 19.3.0.0.

Here's a funny little thing that I found while checking some notes I had on adding [...]

Source: jonathanlewis.wordpress.com
Чт, Янв 07, 2021

I created this simple service a couple of years ago. It's pretty simple, small and intuitive Python app, so you can easily modify it to suit your own needs and run on any platform: https://github.com/xtender/pySync

[...]
Source: orasql.org
Ср, Дек 23, 2020

There are times when an upgrade makes a big difference to performance because an enhancement to the optimizer code path changes the costing of a plan that was always possible, but badly costed. I noticed an example of this while testing the code in the email I mentioned in last [...]

Source: jonathanlewis.wordpress.com
Ср, Дек 23, 2020

Obviously we can format/beautify SQL or PL/SQL code using external tools, but sometimes it would be more convenient to format it directly in database, for example if we want to see few different sql_text's from v$sqlarea. And thanks to Oracle SqlCL and [...]

Source: orasql.org
Пн, Дек 21, 2020

Here's a little puzzle – and if you don't see the answer almost immediately you did need to read this note. The question comes from a thread on the Oracle Groundbreakers' Forum –

“Why I am getting 0020 instead of 2020 for below query?”

select to_char(to_date(sysdate,'dd-mm-yyyy'),'iyyy') from dual.

Instead of sysdate [...]

Source: jonathanlewis.wordpress.com
Пн, Дек 21, 2020

Sometimes it's the little things that catch you out (perhaps only briefly) on an upgrade. Here's one that came up on Oracle's Groundbreakers Developers Community [sic] (who knows what it will be called this time next year and, while we're at it, who can tell where the apostrophe(s) [...]

Source: jonathanlewis.wordpress.com
Пт, Дек 11, 2020

Write a pure SQL query with PL/SQL that stop after :N seconds, where :N is a bind variable.

My solution with v(start_hsecs, delta, flag) as ( select hsecs as start_hsecs, 0 as delta, 1 as flag from v$timer union [...]
Source: orasql.org
Ср, Дек 09, 2020

This note is a list of links to the presentations that Bryn Llewellyn gave to the Swedish Oracle Users Group in a full-day seminar on EBR in 2017. I've had it as a draft note on my blog for some time, but have just revisited it and thought that others [...]

Source: jonathanlewis.wordpress.com
Вт, Дек 08, 2020

This is a follow-up to a note that's 10 years old [opens in new tab], prompted by a thread on the Oracle Developer Community forum asking about the order of operation in an execution plan, and following up with a question about PGA memory [...]

Source: jonathanlewis.wordpress.com
Пн, Ноя 30, 2020

If you're like me there are probably lots of little things that you know but find terribly easy to forget. Here's one of my bêtes noires – starting with a little quiz:

rem rem Script: lob_in_row.sql rem [...]
Source: jonathanlewis.wordpress.com
Чт, Ноя 26, 2020

Several years ago I published a couple of examples of scripts that I'd been using since Oracle 6 (with minor changes over time) to analyse the content of tables in some detail. The first was a breakdown of the lengths of the rows in the table, the second [...]

Source: jonathanlewis.wordpress.com
Комментирование отключено.