I was remarking to someone this morning how easy I thought it was to setup a job, say on the first and third Wednesday of the month. The flexibility of DBMS_SCHEDULER, even without taking into account the power of schedules, is really powerful. We have a scheduler wizard that puts a graphical user interface onto the DBMS_SCHEDULER package. Here’s how it works, in a decent sized nutshell. Which will launch… Clicking the edit button opens…
Columbus didn’t exactly discover America. And we’re not really going to discover your long, lost foreign keys. But, we’ll do our best to guess where they might be. Our data modeling tool is often the bearer of bad news – your database doesn’t actually have any referential integrity defined. Oh, it’s defined somewhere, in the application. Probably. Just not in the database. (I realize these aren’t complete sentences, but this is a blog post, not…
SQL can be hard to read. Execution plans can be EVEN HARDER to read. It doesn’t get any easier when you are joining a table to itself. Or maybe you are having a hard time finding the portion of a plan to correlates to a subquery. We do have a solution! QB_NAME Hint (Docs) /*+ QB_NAME ( qb_name ) */ So why am I talking about this today? Well one of our SQL Advocates, Connor…
Today’s question: can I design a table that takes advantage of the new Temporal Validity feature in Oracle Database 12c? Today’s answer: ‘Yes.’ In a nutshell: Temporal Validity Support lets you associate one or more valid time dimensions with a table and have data be visible depending on its time-based validity, as determined by the start and end dates or time stamps of the period for which a given record is considered valid. Examples of…
Do as I say, not as I do. Because I am like most of you, I am very lazy. Case in point: loading some data from a CSV into an Oracle table. I can use a wizard in SQL Developer and in a few clicks, have it loaded. Usually I’m playing with a hundred rows. Or maybe a few thousand. But this time I needed to load up about 150MB of CSV, which isn’t very…
Someone asked in our forums how to format their code the way they want it. Specifically, they have code like so: 158075071469479715cd08c_000007 They don’t want this: The user doesn’t want the first predicate on the same line as the ‘WHERE’ keyword, and doesn’t want the additional predicate clauses to be on the same vertical position as the WHERE keyword as well. Thankfully there are 2 Formatter preferences that make this easy to fix. So the…
How often do you need to build a new table to test something out? How often have you typed CREATE TABLE AS SELECT… If the answer to both of those question is ‘quite a bit Jeff!,’ then continue reading. In SQLcl, we have built a new command simply called ‘CTAS.’ So let’s try it out. What tables do I have to work with? I’m lazy, so instead of typing ‘select table_name from user_tables’ I just…
I love performing my tips & tricks session. No two talks are alike. I frequently update the slides and content to account for new things I’ve learned and new features we’ve added. But. I’ve been delivering this talk going on 4 years now. Should I let my talk at Open World 2015 be my last hurrah of this topic for awhile? Maybe do more talks on Oracle Cloud, REST, data modeling, reporting, tuning, database administration,…
This post will show how to connect to your Database as a Service environment running in our Oracle Cloud using Oracle SQL Developer. The screenshots below and demonstration steps are written with version 4.1.1 of SQL Developer. We (Oracle) basically have 2 types of Database services you can run in our cloud: Schema – known as Database Schema Service Database – known as Database as a Service (DBaaS) I’ll be talking about the DBaaS offering…
You do your job. You take the requests from the customers, and give them to the engineers. Wait, that’s me. Your job is probably something more like, you write SQL and PL/SQL, you build databases, you create reports, forms, and applications, you keep all of this running, you make it work, and you make it ‘fast enough.’ For much of your work, you live in SQL Developer. You are probably doing these types of things:…
