Near the end of a product demonstration with a customer yesterday, someone asked how they could build physical models for each of their different environments using Oracle SQL Developer Data Modeler (SQLDev.) Imagine having a application and database setup ‘out of the box’ that adequately described your meta data. But, you might have one implementation of that system for a very very large customer that required database partitioning. The physical aspects of that environment could…
I received a very interesting question from a reader the other day, which is awesome for two reasons. One, it gives me something to think about and play with. And ‘B’, it provides me with material for today’s blog post. So here’s the original question – We would like to capture inofmation like who, when and why a user connected to a database in production using SQL developer, so i was wondering if is it…
Just because you can do something, doesn’t necessarily mean that you should. Case in point, database object names. Yes, you can call a table in Oracle pretty much anything you want, including ‘TABLE.’ Using quotes allows you to do some pretty silly things like use reserved words, mixed case names, and worse. On a call today someone mentioned that they found it odd that you could have a table and an index share a name.…
Debugging PLSQL is one of the primary features of Oracle SQL Developer. Yes, its the IDE for the database, but PLSQL is the database’s programming language. It really, really needs to support all things PLSQL. Programming languages need modern day amenities if they are to be adopted by mainstream developers. One of those amenities is a debugger. A primary challenge I face as an advocate for Oracle, PLSQL, and our developer tools is that database…
One of the coolest parts of my job is that I actually get to use the software that I help manage. The end-user perspective is hard to imagine sometimes, but when you ARE the end-user things become clear as day. This morning I spent quite a bit of time in DataPump and the SQL Developer Cart. I needed to copy a schema from databaseA to databaseB. Everything’s relative, and this goes double when it comes…
So while hanging out with a bunch of co-workers last week, someone mentioned they had this trick. The challenge is knowing how many rows you have in your result set before you actually read them all. You see, Oracle has no way of telling us how many records are in our data set before we actually fetch the entire data set. Yes, Oracle gives us %ROWCOUNT, but that only tells us ‘…how many rows affected…
Updated: July 16, 2020 There are many data types to choose from when defining your table columns. The eighty percent rule tells us we can probably get away with a smaller subset of data types, 8 times out of 10. Here is what the ‘TYPE’ control looks like in the modeler when defining a column: I was curious to see what data types were used most in my 11.2.0.3 database, so I fired off a…
The Late Show with David Letterman used to have a bit called, ‘Stupid Pet Tricks.’ I quit watching because I prefer his Scottish and funnier future-replacement, Craig Ferguson. But I will take advantage of some Google-Fu to drive some traffic to ye olde blog (can you tell I’m in the UK this week?) So most folks GET copy and paste in Windows and Mac. Ctrl or Apple + C for copy, and + V for…
SQL optimization and tuning is fun for a lot of folks. For others it’s a affirmation that the database is ‘magic.’ I fall somewhere in the middle. No matter where you find yourself on that spectrum however, it’s pretty safe to assume that at some point you will look at an execution plan for insight to your poorly performing SQL statement. Viewing a plan in SQL Developer is pretty easy, just hit the ‘Explain Plan’…
Want to confuse an Oracle database n00b? Ask them if their query takes into account the time component of a date when they code their WHERE blahDate between … AND … logic. But sir, my dates do not have a time associated with it. You must be confusing this with TimeStamp. See, that has the word ‘time’ in it, so that is where Oracle keeps time stuff. It’s at this point that I seriously start…