Skip to content
  • Oracle
  • About
  • Oracle
Search
Close

– nice theory…

– Good and not so good ideas on software development and Oracle databases in particular

Scripts from my PL/Scope presentation at Code One

October 22, 2018 NiceTheoryVidarLeave a comment

Thanks to all of you that attended my PL/Scope session at Code One in San Fransisco!

You can find the scripts from my presentation about using PL/Scope to find your way through a PL/SQL code base in my github repository here: https://github.com/nicetheory/PL-Scope

Read More »

Where the partitions have no name

September 20, 2018September 20, 2018 NiceTheoryVidarLeave a comment

Working on a routine for dropping old partitions from a log table, I realized that I don’t have to know the name of the partition I want to drop. I can just specify a value for the partition key and Oracle resolves the partition for me. Here’s how.

Read More »

Finding bad queries in a package in a flash

September 12, 2018September 12, 2018 NiceTheoryVidar1 Comment

I was going through some of the Top SQL-reports in SQL Developer, running them against our production system.

One of the “culprits” that showed up was a procedure call, not a query. A quick investigation showed that this procedure was fairly large and consisted of quite a few queries.

Read More »

Matching strings with a combination of tools

February 28, 2018February 28, 2018 NiceTheoryVidarLeave a comment

I had a case where I had to match some names and needed to find a set of ways to clean and match strings. The solution I ended up with was a combination of regular expressions, the NLSSORT-function and the UTL_MATCH-package with the Jaro-Winkler algorithm.

Read More »

Function result cache and application context – don’t get burned

February 1, 2018February 1, 2018 NiceTheoryVidarLeave a comment

Oracles function result cache (FRC) can in certain cases give a considerable performance boost. Application context is another useful feature. How does FRC work when the function result relies on context-settings? And how can we make them play well together?

Read More »

Finding dead code using PL/Scope

January 22, 2018January 21, 2018 NiceTheoryVidar1 Comment

Can we use PL/Scope to find dead and potentially dead PL/SQL code? Yes! And it takes less than a second!

PL/Scope records declarations and usages of procedures and functions, so it should be easy to do a quick comparison of what’s declared and what’s actually used.

Read More »

TRIM: More than meets the eye

January 18, 2018January 19, 2018 NiceTheoryVidarLeave a comment

I’ve always just used the TRIM-function to remove leading and trailing spaces. While going through Markus Winands presentation “Four* Major Database Release of 2017 in Review” on SlideShare.net, I realized that the TRIM function can do more than just remove spaces.

Read More »

Compression of Oracle database network traffic

January 11, 2018January 11, 2018 NiceTheoryVidarLeave a comment

I read this blog-post by Connor McDonald the other day about the Advanced Network Compression and network transfer savings. It reminded me of a feature not many know of and comes without an extra cost option. In fact, it’s on by default.

Read More »

In-database archiving and foreign keys

January 9, 2018January 9, 2018 NiceTheoryVidarLeave a comment

In-database archiving is a neat, new 12c-functionality that lets you logically “delete” rows by changing a flag in a column: A “soft-delete”. How does this functionality work in relation to foreign keys?

Read More »

In-database archiving + ANSI re-architecture = bug

January 8, 2018January 11, 2018 NiceTheoryVidar1 Comment

I had a task to clean up some data and I decided to use in-database archiving, a new feature in 12c. As I was working I noticed some unexpected resultsets along the way. I was quite puzzled for a little time, so I set up a test-case and realized that I’ve struck on a bug. The bug seems related to the ANSI re-architecture-features of the optimizer.

Read More »

Posts navigation

Older Posts
Newer Posts

Categories

  • Oracle (29)
  • PL/SQL (11)
  • Optimization (9)
  • Coding practice (7)
  • PL/Scope (7)

Tags

  • Oracle (16)
  • PL/SQL (11)
  • SmartDB (9)
  • 12c (6)
  • PL/Scope (6)

Year

  • 2022 (2)
  • 2021 (1)
  • 2020 (1)
  • 2019 (3)
  • 2018 (13)
Blog at WordPress.com.
Back to top
– nice theory…
Blog at WordPress.com.
  • Follow Following
    • - nice theory...
    • Already have a WordPress.com account? Log in now.
    • - nice theory...
    • Customize
    • Follow Following
    • Sign up
    • Log in
    • Report this content
    • View site in Reader
    • Manage subscriptions
    • Collapse this bar
 

Loading Comments...