Oracle DBA Tools

This video is a brief overview of Oracle DBA administration features using Aqua Data Studio. Here’s a list of areas that we’ll introduce in this video. Some of these are core and native to Aqua Data Studio. Some of these are specific to the DBA Tools Drill Down, and some of these manager utilities you see on the list. We’ll start by introducing the navigation tree and you’re encouraged to right click the Explorer tree where you have Create, Alter, Drop Object editors. The very popular Tools menu has many scripting features for Schema, Server and Compare scripts. Let’s take a closer look in the query analyzer at some of the Explain plan diagramming and whiteboarding features, even the client statistics on the execution of SQL statements. Drill into these different DBA utilities for Oracle. All of the features we show here are fully available in any version of Aqua Data Studio.

Aqua Data Studio

There’s more information on the product page, www.aquafold.com. Just to introduce those areas under help about, you can see the link to the product page. There have been a number of enhancements to the product homepage recently. Here are just a few tabs to introduce. Here’s the product homepage, Aqua Data Studio, a database IDE that supports many different database platforms and has very advanced functionality for the Oracle database. Here’s a specific list of those areas that we’ll drill into. Okay, so just to show there’s a reference point here. Even the online wiki has documentation on all these Oracle DBA drill downs. If you’re new Aqua Data Studio, there’s other utilities that would introduce how to connect and dive in. If we dive into the Oracle Explorer tree, you can see the Schema, Drill down and Storage Management and Security. If I drill into these areas here, drill into a schema, I can see all my object types.

It starts with tables and views to make that helpful. And then other object types. If I drill into my objects and right click here, you can see the Create, Alter and Drop object editors. This would be for tables, views, or if I drill down here into my storage Management Security. Here under security, you see profiles, roles, users. As you drill into those areas, again, you can right click and you see your Create, Alter, Drop user. You’re encouraged to spend time in the Explorer tree.

Query Analyzer

Here’s a Query Analyzer window I’ve opened, which is the SQL editor aqua Data Studio. We can dive into some of these areas here in the Execution plan. You’re also encouraged to explore the Tools menu as other core areas in Aqua Data Studio. You can see the tools import, export data schema and server script and some of the compare tools. Those are Core native features aqua Data Studio for many years, what we’d like to focus on today is these advanced drill down areas that you can see here in the DBA Tools Oracle’s Drill Down.

Oracle DBA Tools

Or if you right click on your instance, you can see a shortcut to those DBA drill down areas in Oracle as well. You save a click or two by right clicking. Okay, so you’re encouraged to look at the right click menu and the Tools menu. Now let’s dive deeper into these different areas. Here I have a SQL statement, a complex SQL statement in Oracle. If I execute that, you see on the lower portion where I have my result sets, different tabs that would be helpful for an Oracle DBA drilling into performance issues within individual SQL statements. Here’s the Execution Plan tab on the lower portion of the screen. If you right click here, you have different visual notifications you can add for color coding critical nodes, color coding critical operations. Here’s a diagram feature called the Explain Diagram. This would give you a visual representation of the access path for this SQL statement execution.

Compare SQL Statements

Another utility is the explain plan. Whiteboard and so this would allow you to do comparisons between this SQL statement and other SQL statements. That can be a very powerful utility for seeing the difference between performance from a statement in the past to any current blocking activity. Okay, next to that tab is the Client Statistics tab, which also might be of interest in this type of format, where you can see these type of values and averages and statistics that some of us are familiar with. These are some core features that are native Aqua Data Studio in general. 

Oracle Database Administration Tool

Now here, let’s take a closer look at some of these management utilities that are listed here under DBA Tools or the right click menu. I have these opened up already and we can scroll through these and introduce some of these areas, but you’re encouraged to explore these on your own and then even mouse over and explore these different tabs.

There’s many different tabs, and this has been driven by end users for many years. If we start with the instance manager here, you can see summary information on the General tab and then these different Drill down tabs for control file, your Oracle parameters, NLS parameters, and DBMS Jobs. As you might expect, you can click on these different areas. Right click from here. Here’s. Create alter. Drop job, for example. Or if I drilled in the management area, right click there, you see the Create Alter Drop. You have that flexibility of driving from the navigation tree or within these individual DBA drill downs in the DBA Tools. Here’s the Storage Manager, where you see the Tree tab and a visual representation of storage. You have table spaces, data files, objects, fragmentation. Again, I can right click here and there’s my Create Alter drop table space there’s, coalesce table space and table space properties.

Data Storage

You’d have similar capabilities on these other tabs as well. Moving along. We have the rollback manager. If I just expand these groupings here, you can see some of the Oracle file management are grouped together. Storage Manager, rollback Segment Manager, log Manager. Some of the file management capabilities here. You can right click and create an Alter and Drop rollback segments. You can explore your logs and archive logs and then create Alter drop capabilities there as well. Perhaps the most popular drill down in the DBA Tools drill down in Oracle is the Session Manager. Here, if I right click on a connection and open up the Session Manager, you can see all my active sessions within Oracle. These individual Drill down tabs, session Stats, locks, long operations, I can right click and explore that connection. I could terminate a session or start an Oracle’s trace, stop a trace file.

User Permissions

There’s mining utilities to dig into your trace files as well. Right click DBA Tools Session Browser or from the DBA Tools menu, session Browser security Manager another very important area where I can drill into all my users and roles and privileges. There you can see the tree view here, you can see users roles profiles here, and then your Craze Alter Drop as well. And then some of the other areas. SGA Manager Service Statistics these have some helpful drill down capabilities. There’s some different pie graph drill downs in the SGA Drill down. Here if I explore or right click on an Oracle instance here, I can see the SGA Manager and some summary information here, highlight an active SQL statement. You also have the execution plan here, and then some of the whiteboard and critical path diagramming of your execution plans here’s, your library cache statistics, SGA Stats and some bar graph and mouse over capabilities there.

Aqua Data Studio for Oracle

Finally you have the Server statistics, which is almost informational only or read only. If I right click here, server Statistics you can see instant stats, weights and latches. So, this is a brief overview of the DBA Tools drill down for Oracle. As you see, there’s a list of other databases with advanced DBA administration features. You can see a similar look and feel here for some of these other platforms. For Oracle, we actually have the more advanced functionality with the list here of DBA Drill Downs. You have the DBA Tools Drill down, the Tools main menu with DBA features for moving data, schema Scripting Compares, Schema Sync File Compare, Object Compare and then other scripting options would be in the Er modelr right, where you can script or reverse engineer databases and script objects that way. So, thank you for joining this overview video of DBA features for Oracle in Aqua Data Studio.