Stumbling around on StackOverflow this morning, I came across this person’s HOMEWORK question, and the answer chided them on not using ANSI style JOINs. I’m not going to post the actual SQL here, but if you follow the previous link, it’s there for you to grab and have a go. Something I like to do is take REAL code off the innerwebs and use it to test our parser, formatter, and even the Query Builder.…
The question: We have big database with a lot of stuff and I want to use version control (Git) to manage changes. There are a lot of articles how to do it step by step but one piece is missing for me. Is there standard or recommended way for file structure of whole database (data excluded) and how it can be obtained from existing database? So I’m not sure about standard or recommended, but we…
Updated 18 July 2023 I was inspired by Joel’s post on this subject for APEX so much, that I thought I’d copy his idea. So you’re reading this post on my blog, so you found me – maybe probably via Google. Or you walked into my mom at the grocery store. Either way, so glad you got here. But did you know, there are LOTS of places you get help or learn about SQL Developer…
I was using the Instance Viewer today and ran into the drill down for Storage. Let’s take a closer look at that report. I like this report enough, that I want to be able to run it in SQLcl. SQLcl is a command-line interface, so I’ll have to make a few changes. The links won’t work, boo. Those are easy to fix up – just remove the SQLDev:Link code. The Used (proportion) column is a…
I’m always a bit surprised when I find a SQL Developer user who does NOT know about the Instance Viewer! Of course you loyal readers know all about it. But you’re busy – you don’t have time to tell your friends. So just send them this video, and I’ll tell them about it. In a nutshell: it’s a performance dashboard in SQL Developer. It will show you what’s happening in your database, all in one…
2021 Update: I originally wrote this post in 2018 – and since then I’ve taken on this type of question a few times. You might also enjoy these related posts, which are more technical/useful than a X in INT and Y out VARCHAR2 example like this: 4 ways to work with REF Cursors in your REST APIsORDS 101 for PL/SQL This from the YouTube:”Hi Jeff, Can you please share similar example where REST service is…
We recently (18.2) added a new way to generate execution plans in SQL Developer – we generate a call to DBMS_XPLAN for the SQL ID at your cursor position. DBMS_XPLAN is a package, and one of it’s procedures is ‘DISPLAY_CURSOR’: This table function displays the explain plan of any cursor loaded in the cursor cache. In addition to the explain plan, various plan statistics (such as. I/O, memory and timing) can be reported (based on…
15 minutes overview of how to use these features in Oracle SQL Developer: Get an Explain Plan Customize the display of the plan Get a cached plan Use DBMS_XPLAN Compare Plans Use Real Time SQL Monitoring Use AutoTrace. And if you want an awesome video, MUCH more in depth about how to read a plan, then I suggest this from @sqlmaria.
Ok that title has a TON of buzz and marketing words in it. But, we have the Oracle Cloud. And available there is a service where we take care of your database for you – Autonomous. We make sure it’s up, and that it’s fast. We have 1 autonomous cloud service today and will have and second one coming SOON. These services come with an S3 compatible Object Store OSS (Oracle Object Storage, complete with…
SQL tuning can be fun. The database gives us MANY things to help with this. There is even a nice set of views that contain everything we need to know about our SQL and their exectution plans. And, we have a really cool PL/SQL package, DBMS_XPLAN for generating reports on our troublesome queries. The not-fun part, is going from a SQL statement to the SQL_ID. Now, SQL Developer has for a few releases made it…
