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

19 Февраль 2015
Пт, Ноя 10, 2017

Oracle 12.2 has introduced a number of new features that ease partitioning, the most-awaited perhaps is the ALTER TABLE MODIFY syntax to convert a non-partitioned table to partitioned.

What do you do if your database version is pre-12.2? Partitioning a non-partitioned table in pre-12.2 databases is trickier, not [...]

Source: www.oratable.com
Вс, Окт 22, 2017
При установленном параметре: параллельное построение индекса легко распеределяется между нодами RAC: , несмотря на исправленный Bug 16458105 — PARALLEL_FORCE_LOCAL=true not honoured for ALTER INDEX — superseded (Doc ID 16458105.8): — что отражено/соответствует документу Patch Set — List of Bug Fixes by Problem Type (Doc ID 1683802.1) Реклама Filed under: [...]
Source: iusoltsev.wordpress.com
Чт, Окт 19, 2017

It's that time of year again – the UKOUG Tech conference is approaching and I've organised a panel session on the Cost Based Optimizer.

This year I've got Christian Antognini, Nigel Bayliss, Maria Colgan and special guest star, all the way from Australia, Richard Foote on the panel, with Neil [...]

Source: jonathanlewis.wordpress.com
Ср, Окт 11, 2017

My father-in-law died a few weeks ago, aged 95. This is the story that he wrote for his children and grandchildren a few years ago describing his experiences as a Naval engineer on the aircraft carrier HMS Indefatigable during the second world war.


When war broke out on [...]

Source: jonathanlewis.wordpress.com
Пт, Окт 06, 2017

Following on from a comment to a recent posting of mine about “bad” SQL ending up in the shared pool and the specific detail that too much bad SQL could cause contention problems while staying virtually invisible, there's a related note today on the ODC (formerly OTN) [...]

Source: jonathanlewis.wordpress.com
Вт, Окт 03, 2017

Here's a quick quiz.

According to the Oracle 12.1 Database SQL Tuning Guide the first stage of parsing a statement is the Syntax Check, which is followed by the Semantic Check, followed by the Shared Pool Check. So where you do think the statement text will be while the Syntax [...]

Source: jonathanlewis.wordpress.com
Пн, Окт 02, 2017

How can a single piece of SQL text – checked very carefully – end up with multiple SQL_IDs ? There are probably quite a lot of people who know the answer to this question but won't think of it until they're reminded and, thanks to a question that came up [...]

Source: jonathanlewis.wordpress.com
Пн, Окт 02, 2017

Unfortunately associative arrays still require more “coding”:
we still can't use “indices of” or “values of” in simple FOR(though they are available for FORALL for a long time), don't have convinient iterators and even function to get all keys…
That's why I want to show my templates for such things like iterator [...]

Source: orasql.org
Пт, Сен 29, 2017

I see quite often when developers ask questions about connected components:

Table “MESSAGES” contains fields “SENDER” and “RECIPIENT”, which store clients id.
How to quickly get all groups of clients who are connected even through other clients if the table has X million rows?
So for this table, there should be 4 groups: (1, [...]
Source: orasql.org
Ср, Сен 27, 2017

I've troubleshooted one query today and I was very surprised that bind variables in this query were specified with &ersand instead of :colon! I have never seen this before and I couldn't find anything about this in documentation…
Unfortunately SQL*Plus doesn't support ampersand yet, even if you disable define (“set define [...]

Source: orasql.org
Пн, Сен 25, 2017
Stored Procedures in Packages

Typical scenario: in a project's design phase, procedureA is meant to be placed in packageX. During implementation, packages are refactored: packageX gets split into packageY and packageZ. All goes well – the application gets deployed and is running merrily – till a change request comes in.

A new developer refers [...]

Source: www.oratable.com
Вт, Сен 19, 2017

Here's a little oddity that came up recently on the OTN database forum – an example where a “with” subquery (common table expression / factored subquery) produced a different execution plan from the equivalent statement with the subquery moved to an inline view; tested in and Here are [...]

Source: jonathanlewis.wordpress.com
Чт, Сен 14, 2017
with tst as ( select 'qwe word1 asd ...............' s from dual union all select 'qwe word1 asd word2 .........' s from dual union all select 'qwe word1 asd word2 zxc word3' s from dual union all select 'qwe word2 asd word1 zxc [...]
Source: orasql.org
Пт, Сен 01, 2017
Proxy User Authentication in Oracle

Proxy user authentication is a powerful feature in Oracle that lets one database user (proxy user) connect to the database "on behalf of" another user (client user).

With proxy login, the proxy user can act as if it is the client user with all of the client user's access rights, without [...]

Source: www.oratable.com
Ср, Авг 16, 2017
XMLTABLE to Convert XML To Relational Data

Here's a prototype for using the SQL/XML function XMLTABLE to map XML data into relational rows and columns.

This solution uses the standard EMP table – the same can be extended to work with any XMLTYPE-relational mapping.

Problem Statement: Receive XML Payload, Parse and Store in Relational Table

An application receives employee data [...]

Source: www.oratable.com
Пн, Авг 14, 2017

A few years ago a bug relating to join elimination showed up in a comment to a post I'd done about the need to keep on testing and learining. The bug was visible in version and, with a script to replay it, I'd found that it had disappeared [...]

Source: jonathanlewis.wordpress.com
Пт, Авг 11, 2017
Dynamic SQL

A comparison between static SQL vs dynamic SQL shows us pretty clearly that, if there exists a choice, we are better off choosing static SQL.

So, when should we use dynamic SQL?

Oracle documentation tells us that we need dynamic SQL to run:

SQL whose text is unknown at compile [...]
Source: www.oratable.com
Чт, Авг 10, 2017

I've reviewed this book recently, and I highly recommend it as it has almost all that needed to become strong Oracle developer. You can check at least the table of contents:

ORACLE SQL & PL/SQL Golden Diary: by Asim Chowdhury
New Book Demystifies Complex Cross-Version Oracle Problem Solving

Compiled [...]

Source: orasql.org
Чт, Авг 03, 2017

One of the special events that can make it necessary to rebuild an index is the case of the “massive DML”, typically a bulk delete that purges old data from a table. You may even find cases where it's a good idea to mark a couple of your indexes as [...]

Source: jonathanlewis.wordpress.com
Ср, Июл 26, 2017

Some questions do not have definitive answers. "Is a full table scan bad? Should this design be denormalized? Will partitioning this table help?" The answers vary widely depending on the specifics on the problem.

Fortunately, "Should I use static SQL or dynamic SQL?" is not one of those questions.

You can come [...]

Source: www.oratable.com
Вт, Июл 25, 2017

This posting was prompted by a tweet from Kamil Stawiarski in response to a question about how he'd discovered the meaning of Redo Op Codes 5.1 and 11.6 – and credited me and Julian Dyke with “the hardest part”.

Over the years I've accumulated (from Julian Dyke, or odd MoS [...]

Source: jonathanlewis.wordpress.com
Пн, Июл 24, 2017

The following is the text of an article I published in the UKOUG magazine several years ago (2010), but I came across it recently while writing up some notes for a presentation and thought it would be worth repeating here.

Fast Now, Fast Later

The title of this piece came from a [...]

Source: jonathanlewis.wordpress.com
Сб, Июл 22, 2017

This is the second of two items that my mother typed out more than 25 years ago. I had very mixed emotions when reading it but ultimately I felt that it was a reminder that, despite all the nasty incidents and stupid behaviour hyped up by the press and news [...]

Source: jonathanlewis.wordpress.com
Пт, Июл 14, 2017
В PDB snapshot copy окружении версии Артём Горбик показал странно отображаемую сессию-блокер: — формально работающую в CDB$ROOT: , и в то же время выполняющую Scheduler Job в контейнере-клоне: Модифицировал под это дело скрипт (с суффиксом _CDB), добавив к инстансу/сессии (INST#/SID#4) номер контейнера — CON#: — и в выводе скрипта [...]
Source: iusoltsev.wordpress.com
Чт, Июл 13, 2017
на physical standby / ADG instance в виде: , как нам с Максимом Филатовым удалось выяснить, вызывается установкой параметра: и, соответственно, лечится (по рекомендации техподдержки): В общедоступном описании бага: о параметре не упомянуто, в то время как в документах типа ORA-1555 Error With Query Duration as 0 Seconds (Doc ID [...]
Source: iusoltsev.wordpress.com
Пт, Июл 07, 2017

The title is a well-known shorthand for parameter optimizer_features_enable and it has been the topic of a recent blog post by Mike Dietrich in which he decries the practice of switching the parameter back to an older version on an upgrade (even though, as he points out, Oracle support [...]

Source: jonathanlewis.wordpress.com
Ср, Июл 05, 2017

My mother died a few weeks ago after a couple of months in terminal care. One of my tasks while she was in care was to go through all her paperwork and while doing so I discovered a couple of stories from her past that she had typed out (remember [...]

Source: jonathanlewis.wordpress.com
Ср, Июн 28, 2017

SQLCODE and SQLERRM are Oracle's built-in error reporting functions in PL/SQL.

When an error occurs in PL/SQL at runtime:

SQLCODE returns the number of the last encountered error.

SQLERRM returns the message associated with its error-number argument. The error-number argument is optional: if omitted, SQLERRM returns the message associated with the current [...]

Source: www.oratable.com
Пт, Июн 16, 2017
Check if JVM is Installed in Oracle

I recently thought of using Java's built-in BigInteger.gcd() method inside a PL/SQL wrapper to calculate GCD in SQLs.

And so I wrote the Java code and ran it on Oracle XE.

Things didn't go as planned.

SQL> -- Create java source for GCD calculation SQL> create or replace and resolve java [...]
Source: www.oratable.com
Ср, Июн 14, 2017

An interesting observation appeared recently as a side-channel on a question on the OTN database forum – how does Oracle execute an unpivot() operation. Here's an example of such a query:

rem rem Script: unpivot_2.sql rem [...]
Source: jonathanlewis.wordpress.com
Комментирование отключено.