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:
|
1 2 3 4 5 6 7 8 9 10 |
/* 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 you're searching. */ SELECT COUNT(DISTINCT object_name) FROM dba_objects WHERE object_name LIKE 'DBA_%'; |
And the results:
|
1 2 3 |
COUNT(*) ---------- 1025 |
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:
|
1 2 3 4 5 6 7 8 |
SELECT owner, tablespace_name, table_name FROM dba_tables WHERE tablespace_name = 'EXAMPLE' ORDER BY owner, tablespace_name, table_name ; |
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 dba_tables.
User Objects
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:
|
1 2 3 |
SELECT table_name, tablespace_name 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:
|
1 2 3 |
SELECT COUNT(DISTINCT object_name) FROM all_objects UNION ALL 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 v$session view:
|
1 2 3 |
SELECT sid, username, machine FROM v$session 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 v$session_wait.
Using this, we can look into my session on the system with:
|
1 2 3 4 5 6 7 8 9 10 |
SELECT wait_class, event, total_waits, time_waited, average_wait, max_wait, time_waited_micro FROM v$session_event 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.
5 Comments. Leave new
In newer versions of Oracle, you also have GV$ views. On a RAC instance, V$ will show what’s happening on that particular node instance, whereas GV$ will show information across node instances.
On a non-RAC instance, GV$ is the same as V$.
I don’t have any idea how I missed that. I’ll add your comment in up above… I’ll even give you credit!
A small quibble. Your query to see system views includes other objects that start with DBA, like synonyms. The distinct cleans much of this up as synonym names duplicate view names. However, if the intent is to find only DBA views a better query would be:
SELECT COUNT(*)
FROM dba_views
WHERE view_name LIKE ‘DBA%’
Thanks for your contribution!
Has anyone shopped at CC’s Vapor Paradise Vapor Store located in 4610 Evergreen Way #6?
https://www.menshealth.com/fitness/a19522639/a-request-and-a-funny-dog-story/