If you’re going to be working with Oracle, you need to be able to get a better handle on what’s going on with the Oracle database. Just like other database platforms, Oracle provides a data dictionary to help users interrogate the database system.
Looking at System Objects
Database administrators can view all of the objects in an Oracle system through the
DBA_% prefixed objects.
You can get a list of all available views through the
dba_objects system view:
/* There's a gotcha here:
if you installed Oracle as suggested, you'll be using a
case sensitive collation. That's not a big deal, just
don't forget that while you don't need to capitalize object
names in SQL*Plus, you do need to capitalize the names while
SELECT COUNT(DISTINCT object_name)
WHERE object_name LIKE 'DBA_%';
And the results:
Just over 1000 views, eh? That’s a lot of system views. If you just want to examine a list of tables stored in your Oracle database you can use the
dba_tables view to take a look. Here we’ll look at the
EXAMPLE database schema:
WHERE tablespace_name = 'EXAMPLE'
ORDER BY owner,
The curious can use the
desc command to get a list of all columns available, either in the
dba_tables view, or any of the tables returned by querying
A user shouldn’t have access to the
DBA_ views. Those are system level views and are best left to people with administrative access to a system. If a user shouldn’t have that level of access, what should they have? Certainly they should have access to their own objects.
Users can view their own data with the
USER_ views. There’s a
user_objects table that will show information about all objects visible to the current user. If you just want to see your own tables, you can use the
user_tables view instead:
FROM user_tables ;
Of course, users may have access to more than database objects that they own. In these cases, users can use the
ALL_ views to see everything that they have access to:
SELECT COUNT(DISTINCT object_name) FROM all_objects
SELECT COUNT(DISTINCT object_name) FROM dba_objects ;
Running this query nets 52,414 rows in
all_objects and 54,325 in
dba_objects. Clearly there are a few things that I don’t have direct access to, and that’s a good thing.
System Status with V$ Views
Oracle’s V$ views record current database activity. They provide insight into current activity and, in some cases, they also provide insight into historical activity. There are a number of dynamic performance views (Oracle’s term for the V$ views) covering everything from waits to sessions to data access patterns and beyond.
As an example, you can view all sessions on an Oracle database using the
SELECT sid, username, machine
WHERE username IS NOT NULL ;
Oracle has a wait interface, just like SQL Server. Waits are available at either the system or session level. The
v$system_event view shows wait information for the life of the Oracle process. The
v$session_event view shows total wait time at a session level (what has this process waited on since it started). You can look at currently running (or just finished sessions) using
Using this, we can look into my session on the system with:
WHERE wait_class <> 'Idle'
AND SID = 255 ;
Don’t be afraid to explore on your local installation. There’s no harm in playing around with different Oracle features to determine how they work and what kind of information you can glean from them.
You can also use the GV$ views, thanks to Jeff Smith for pointing out my omission. These are views that are designed for Oracle RAC so you can see the health of every node in the RAC cluster. The upside of this is that you can get a big picture of an entire cluster and then dive into individual nodes using the V$ views on each node. You can even execute queries that use the GV$ views, even if you don’t have RAC, and you’ll be just fine.
A Word of Warning
Be careful with the both the data dictionary and the V$ views – querying certain views may trigger license usage to show up in the
dba_feature_usage_statistics view. Before using features like Active Session History or the Automatic Workload Repository, make sure that you have the proper features licensed for your Oracle database. Using these optional features for your own education is fine.