Basic queries for retrieving database information

posted Sep 13, 2010, 5:33 AM by Sachchida Ojha   [ updated Sep 13, 2010, 5:38 AM ]
-- Get the name of the DB
select name from v$database;
select * from global_name;

-- Check the patch level
select * from registry$history;

-- Determine Version and options of database examined
select comp_name, version, status from dba_registry;

-- Determine name of database examined
select name, created, log_mode, protection_mode, dataguard_broker, guard_status,
force_logging from v$database;

-- Determine properties of database
select property_name, substr(property_value,1,20) PropertyValue
from database_properties;

-- Check which options are installed
select substr(parameter,1,45) "Option Name", substr(value,1,10) "Enabled"
from v$option;

-- Check accounts. Check if a user is external
select substr(username,1,10) username, substr(account_status,1,15) accountStatus,
substr(default_tablespace,1,15) defaultTable, profile, substr(password,1,5)
from dba_users
order by username;

-- Queries the the profiles
select substr(profile,1,35), substr(resource_name,1,25), substr(limit,1,10)
from dba_profiles
order by resource_name;

-- Queries to check banner

select banner from v$version where banner like 'Oracle%';