Oracle SQL Developer Tips and Tricks: Data Edition

welcome everyone my name is Jeff Smith I'm a product manager here at Oracle and I'm responsible for tools such as Oracle sequel developer today I want to do a shortened quicker version of a talk I did at code one this fall in San Francisco it's a talk I've been doing for a very long time sequel developer tips and tricks but I gave it a data specific twist so all of these tips and tricks are about making it easier to work with and understand the data that you are interacting with in your Oracle database this is a kind of new mantra we have going on at Oracle and I think it's pretty self-explanatory obviously if you're using a tool like sequel developer and using the sequel language you're working data each and every day and you know hopefully tools can help you do these things you know see the data in new ways and discover business insights and help you be more successful in your job or the task at hand and that's really what's behind every tip and trick that I show our end users so if you enjoy this sit back and I'm going to try to do this in 20 minutes let's see how close I can get to that all right let's get into the tips and tricks I'm going to do these rapid-fire I won't live demo everything but for the more interesting ones or the ones that are very specific or quirky in terms of like exactly where you right-click I'll try to step you through that so the thing that I see people do the most that might drive me the most crazy is this actually a very common thing you're writing a query or report you've got data in a grid and you want to get that data in that grid out into someplace else and perhaps a different format either to the clipboard or to a file or even just back to the screen and what you'll see people do and this isn't bad it's just I know there's faster ways to get you where you want to go though right-click in that grid good dialogue and seven clicks later they'll end up with what they want in this case they've got a comma delimited format of the query results in the worksheet so they've either said send that to the worksheet or they've said send it to the clipboard and they've pasted it in now there's a faster way for you to get what you want here and so pay attention to this trick I think you're gonna like it a lot so instead you can build the format of the output that you want directly in to your query and the trick is if you add a comment and that comment is only the format and it matches exactly how it's listed in the grid when you do the export and you run this via f5 or as executes as a script the results come back exactly in that format so I will love demo this so let's say I want some data back in the form of XML now if I run this with control-enter traffic light it comes back in this grid and it's not doing anything but if I run this with this button here we automatically put this through our XML for matter so the things that you can put inside this hint are determined by what you see in this dialog so anything that's not a binary file format here you can use so loader which would be sequel loader that works but PDF which is you know like an Adobe PDF that's not valid here's a new one JSON formatted let's see what that does so we get a JSON formatted file so excellent sorry XML versus JSON have at it use the one that you want to do now if you don't want to use these comments and you would rather just set this specifically for your session you can also say set sequel format and I can say something like um an C console so this is a special one that says format the output specifically to fit to screen so we have the columns sized properly so we don't have that nasty um scrolling off the screen okay so I think you'll like that trick a lot series and I show that one first if nothing else you get from this video I hope you find that useful right so I didn't show this but if you want to use the sequel plus commands like spool you could quickly write these query results out to files onto your operating system there's a new command that we've added called DDL that allows you to generate the DDL for example for a table so if you said something like spool locations dot sequel and then you said DDL locations and then select star from locations you could have in one file everything you need to create this table you know structure and data someplace else it doesn't get much easier than that here's an example of what this looks like when you're using it inside of sequel CL which is the command line version of sequel developer and when we're using the sequel swarm the sequel format of HTML so you get just a very basic HTML cell grid with a little bit of JavaScript in here that allows for filtering of the data if you are going to use the mouse to do the right-click to do the exports I do recommend that you go into preferences first and set up all of the defaults so if you're always doing an export to the format of insert statements and you always want it to go to the clipboard you only have to set that once and then every time you go in to do the export it's going to remember that and you can actually set defaults for each of the different file formats so if you want JSON formatted to always go to a file but you always want an XML to go to clipboard you can set the defaults for each of those serve time you go to right-click you're not always changing things okay so um speaking of data and maybe you don't need to write a query you can go directly into a table or view and you want to browse it one of the features we have is a space where you can put in predicates so instead of saying you know select star from all objects I might want to say or object type equals something now these are pretty easy to type column names but not all this are blessed with such easy to understand and type data models but if you want to while you're in this drop down if you hit control spacebar which is the same keyboard shortcut you see in a sequel worksheet for help with your query we'll bring you up a list of columns and you can keyboard through this and quickly you know set up your filters here's a more interesting trick so here's an example of using that query predicate filter I'm gonna do a split grid so I want to see two different ranges of data in the same screen so at the top bottom sorry at the top right and the bottom right of these pages you see this little widget that if I pull and drag on it splits the screen say I'm drag that pull it down now I've got two grids and I can do anything I want let me just do this very quickly for you in super slo-mo so you can see what I mean so I can grab this pull it down I've got a grid up here and a grid up here I can also grab this pull that over I've got a grid grid grid so it's the same data but I can have different sorts different column orders whatever I want down here and I don't even have to have a grid grid grid I could have grid up here here I could have the column definition and here I could have the statistics and anytime I want I can come up here and say unsplit and bring me back to that single page so I use that trick all day every day it's very nice now instead of splitting a few err on a table you might want to have different tables open at the same time so I'll see people opening up a second third even fourth copy of sequel developer just so they can have on one screen more than one object open at once and that's totally not necessary it's very easy to do here I'll live demo very quickly but the main thing to remember is that you need to pin a table first before you try to open the second table by default the tool doesn't want to have more than one table open at a time because of resource requirements that might come into play but if you're okay to pay that tax here's this pin button I'm gonna click on it and I'm going to open a different table so now I've got this table and then this table and then you've got two different things you can do I can grab this with my mouse and find a place to dock it in the UI so now I've got customers and I've got employees let's put it back up here the other thing I can do is right-click and say new document tab group so I've got employees over here customers here and here in this screenshot up this basically done that four different times or dragged it to three different spaces you could go as far here as you have room on your monitor my monitors not quite that big but four is pretty reasonable and you can do this with more than just tables you could do with any database object that has a viewer for it in sequel developer so restful services view synonyms table spaces users packages but you know tables and browsing data is probably the most common I call this Tips & Tricks let's show a couple tricks and again I will have links to these code samples in the description of the video I'm not gonna live demo this but I'll just give you a quick feel for how this works you know you might want to have a report that includes links to objects in the database so here's for an example a report that shows me the size of objects in a schema in terms of how much space they're consuming and then when I want to go look at one of those things I can click into them so these look like blue hyperlinks and they actually are but instead of opening up something in my internet browser it's going to open up in the object Explorer so using this special code here where I'm supplying to sequel developer the schema owner the object type and the object name and here's the the real magic code that activates the hyperlink as I click on one of these things it'll go open these objects because we know everything we need to know we know who owns it what kind of object it is and what its name is and so when you're building your own reports that's a really nice trick to have it just saves you from having to click around the the database connection tree similar thing here the syntax you can guess what this does so I'm saying hey I've got a gauge and I'm providing different delineation points to show where these callers are going to change but I basically have created a gauge here that just you know makes it easier for me to see visually what I'm looking at so these numbers kind of all run together in my head but based on these thresholds that I've set I could know immediately if this person's making enough for too much money this one's pretty intuitive but if you didn't know you could do it probably wouldn't have figured it out so I'm copying rose here in a grid that could have easily copied this out of a grid and Excel as long as the data is delimited I can select an equal range of values and paste in what's from the clipboard so it's a really easy way to insert a bunch of rows into a table all at once so I'm in a table viewer here I'm clicking the plus button selecting that area and I'm pasting that data in and I'll have to do is hit the commit button and I've quickly inserted what seven or eight nine new rows pay attention here cuz I get this question a lot people would like to be able to copy and paste data out of grids and they've figured out how to get the data but they haven't been able to figure out how to get the column headers and the trick here is simply instead of control C or command C it's control or command shift C and what that does is it grabs the data and the column headers so just so you don't think I'm messing with you troll shift C I've got the column headers and the data from that grid right there here's a nice trick um you're working on a select statement you're gonna have a we're in list and you can't easily use a sequel statement to provide those values so you can have up to like what a thousand specific inputs here for this in list so maybe the easiest way to get those is by browsing a table I can simply select this list of values here and drag and drop it on to this editor and will automatically put in a common delimited list of values and if these are strings will automatically quote those for you so that can save you quite a bit of typing let's talk a little bit about getting help with writing your queries and seeing just exactly what your queries are doing so this is called the insight feature this is what you'll see generally as you're typing if you stop for a second or two sometimes you'll get these little pop-ups so here's an example of where we're pulling example queries out of the online Oracle Docs for example if you need help with a case statement if I click on that case when e salary greater than 200 text that's actually going to pull the example query out of the Oracle Doc's that's being used to demonstrate the case statement and that's what goes into the worksheet now if I click on the actual picture of the book next to that text it'll open up the online Oracle Doc's reference page for the case statement itself so I can learn more about the case statement so a lot of people know about the help getting a column list or a list of tables I think this is a little bit less well understood you know the the ability for us to help you with the syntax of the code you're working on and you activate this manually using ctrl spacebar or you can wait for the automatic helper to pop up so the preferences will say we won't automatically do these pop-ups if there's more than say 10 or so suggestions to make we don't find them super useful if there's like 10 million objects for you to choose from if you still want the help you can hit ctrl spacebar and that'll force up the list so obviously have more than 10 things from here to choose from select start from E is showing me the things in my schema that start with the letter E and then it's going across other schemas that I have access to this start with the letter e so like I said this list isn't super helpful to me the more I type here the shorter that list is going to be and the more useful it will be and also when the list is 10 items are fewer than if I have the automatic insight on I don't have to hit ctrl spacebar they'll just show up this is the preference where I'm talking about with the automatic feature on these checked on after our weight point 4 seconds if I have 10 or fewer suggestions I'll see this pop up automatically otherwise I can hit ctrl spacebar to force this up select star from we all love writing those queries even though we probably shouldn't we know better if you want you can have sequel to develop or help you fix this so if I mouse over the star will show you the list of columns that resolves to and if I actually click on this text will replace the star with these items you can undo that if you want to go back to the star so this is also just useful for like say you've got a cursor and some Peele sequel and some ones are in a select star from and you don't know what the columns are gonna be you can just use this little peek over the asterisk figure to see oh okay - runs these are these are the columns that we used to I don't know for example populate my ref cursor I don't want to get into any philosophical debates or discussions here but some people absolutely hate the style of join syntax when you're working with your tables this is the way we've generally written it in Oracle where we have a where table column equals table comm and it's defined in the where clause and the insight feature will help you write these when you have existing foreign key constraints so here I've got three different key constraints that can key and on if you hate this style you can select the text right click go to refactoring and say toggle ANSI Oracle joints so we'll rewrite the statement for you to be ANSI style join and if you are an old-school user and you really don't like this format you can do the same thing so it'll go in both directions so let me just show you an example of this so here I've got the ANSI style joint syntax and if I don't like that I can right click and say refactoring toggle Oracle antsy joints and you can see that's been moved down to the where clause you can always undo to undo that so I'm not gonna pick a winner but just say you know the tool can help you either which way you like I'm I type fast although not I will always type accurately and before I go to run something or if you're bringing in some code and you're running it as a user that might not have the privileges necessary we'll try to show that to you so the parser will go through there and say hey I don't actually see something called employees in your schema so if you see these little squiggle marks if you mouse over that that's when we'll try to explain to you the advice that we're providing so backs we're talking about the ancien sequel style oracle cell joins and you go click on an object such as a table or a view there's a model page and this will show you the referential integrity defined in the database via foreign key constraints now unfortunately a lot of you are used to seeing your schemas built this way where there is no referential integrity built into the database itself our modeling tools can't help you with this if you need help understanding your data if you have a driving primary key value in other tables and that column name is propagated across like this in our model or you can say discover foreign keys and using that logic we'll say ok this is probably a relationship for you and show that to you let's end on a high or I guess maybe low note working with Excel we're all doing that simply show you a couple tricks I get this question fairly frequently I don't know why I don't know why you'd want to have such large Excel files all the time when that date is better off in your table but whatever when you need to get a bunch of data out of your database and into Excel if you use the database export feature so if I say tools database export and I click Excel here let's not get the DDL let's just get the data and let's just pick a couple of specific tables let's do regions let's do locations and let's do employees and if we come back here and I say save has it annex single excel file and finish you can see that it's created this file for me how many rows its grabbed if I look at my excel file I've got one workbook per table so whether or not I get one file per table or one workbook per table it's all based on whether I have single file here or separate files checked if I have separate files checked I'll point to a directory instead of a file and we'll give you one file per object and in this case it's time to be Excel and once you get multiple files it's whatever format you want if you want to instead copy data to another database instead of out to a flat file then it's really nice you can just drag and drop tables from one connection into a different connection so I've got multiple connections here drag these or select these Jack them up to this cloud connection and will automatically move the DDL and the data of those objects up and will honor referential integrity constraints and indexes and all that good stuff for you as well so it's a nice thing to do when you've got tests or development instances with you know a reasonable amount of data if you're going to be talking about terabytes worth of data then that's probably when you go back to offloading stuff to CSV and using things like external tables or maybe even using data pump to move stuff around if you are going to be moving large amounts of data use that export wizard again use the loader format which is code for a sequel loader and then when you're ready to move data around seriously so this is fast for my laptop it might not be fast for a serious machine in this case I was able to load a lot of data and in a short amount of time external tables are even faster and co-developer on the export wizards and on the import wizards will help you set up both sequel loader and external table staging scenarios when you're trying to get data out in CSV and trying to get it back into a database from CSV once you've got data in your database and you want to start getting it back out using queries you might run into the scenario here and there where your queries aren't as fast as they could be how can we help you with that about when you're looking at your query and you want to get to the plan behind that if you use the execution planner the explain plan button instead of using the the button part if you click on this drop down we can generate for you the DBMS X plan reports so we reverse engineer the sequel ID for the query text and make it easy for you to get out your execution plan reports the way you like once you're looking at plans if you want to compare those side-by-side we make that pretty easy you just pin a plan and then right click and say compare and we can do this side-by-side and if you've got trace files down on your machine we have a trace file viewer so file open points of the trace file make it really easy for you to see what queries are ran during your tray session and the plans and wait events for each of those all right I know that was a lot of things thrown at you in a short amount of time and I think I went over the 20 minutes just a little bit I apologize if you want deep dive overviews of all the things that I've went over today then just meander down below the video here in the YouTube page and you'll see in the description links to all of the different tricks in long form thanks everyone for your time I will admit this was more fun doing this at Oracle code 1 with a full room and some people laughing him jokes but I hope this found value with you if you have any questions feel free to leave me a comment and don't forget to subscribe if you want updates as I post new videos thanks everyone and happy sequel diving out there

Channel: Jeff Smith

Views: 10,951

Keywords: oracle, database, sql developer, data, tips, tricks, codeone

Id: 8-aUXY2nSVs

Length: 29min 8sec (1748 seconds)

Published: Wed Nov 13 2019