Oracle Queries

Version Information

You can use a couple of different queries, assuming your login has permission, to get information about the Oracle version and edition. The version numbering is explained at Getting Started with Database Administration which also introduces the following queries:

  • SELECT * FROM product_component_version
  • SELECT * FROM v$version
  • SELECT * FROM v$instance

Language Information

This is handy to look at when you are moving data between databases and having issues:

  • SELECT * FROM v$nls_parameters WHERE parameter IN ('NLS_LANGUAGE', 'NLS_CHARACTERSET')
Obviously dropping the where clause gives you the full set of language information.

Storage

Some handy storage related queries:

  • SELECT * FROM v$tablespace
  • SELECT * FROM v$datafile
Note the above two tables have a common table and can be joined thus:
SELECT * FROM v$tablespace ts, v$datafile df WHERE ts.ts# = df.ts#

Users and Access

If you need to check who you are logged in as, use this:
select user from dual;

Users can be associated with a profile, to get a list of profiles do this:
SELECT DISTINCT profile FROM dba_profiles
Then to see all the detail use this:
SELECT * FROM dba_profiles ORDER BY profile, resource_name

Limiting Rows

There are times when you want to limit the number of rows returned in a query. From Oracle 12 onwards this is quite simple and can be done as follows:
SELECT * FROM table_name ORDER BY column_name FETCH FIRST 5 ROWS ONLY
However before this it was a little trickier:
SELECT * FROM (SELECT * FROM table_name ORDER BY column_name) WHERE ROWNUM <= 5
The Oracle 12 syntax does have some other handy options about offsetting and percentages.