SQL statement to list all of the current database properties

Question

I have tried a few different ways to list the db properties and have come up short.

SQL> SHOW DATABASE VERBOSE emp;
SP2-0158: unknown SHOW option "DATABASE"
SP2-0158: unknown SHOW option "VERBOSE"
SP2-0158: unknown SHOW option "emp"

Heres another that I dont understand why its not working

 SQL> show database;
 SP2-0158: unknown SHOW option "database"


SQL> DGMGRL
SP2-0042: unknown command "DGMGRL" - rest of line ignored.

Does anyone have ideas as to what I am missing.

Answer 1

There's a table called database_properties - you should query that

select property_name, property_value, description from database_properties

If this isn't what you're looking for, you should be more specific

Answer 2

If you wan the full version information for your DB then:

SELECT *
  FROM v$version;

If you want your DB parameters then:

SELECT *
  FROM v$parameter;

If you want more information about your DB instance then:

SELECT *
  FROM v$database;

If you want the database properties then:

SELECT *
  FROM database_properties;

If you want the "size" of your database then this will give you a close enough calculation:

SELECT SUM(bytes / (1024*1024)) "DB Size in MB" 
  FROM dba_data_files;

You will need DBA level permissions to see these views or you could request the data from your DBA and he will (probably) oblige.

Hope it helps...