Fahad Pulikkal

1. Startup Time

Startup information is stored in v$instance.startup_time, and uptime can be easily computed as sysdate – startup_time.

select to_char(startup_time, 'HH24:MI DD-MON-YY') "Startup time"
from v$instance
/

Sample Output:
Startup time
---------------
03:05 09-FEB-06

Another query which outputs the time period since the database was started:
select
 'Hostname : ' || host_name
 ,'Instance Name : ' || instance_name
 ,'Started At : ' || to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') stime
 ,'Uptime : ' || floor(sysdate - startup_time) || ' days(s) ' ||
 trunc( 24*((sysdate-startup_time) -
 trunc(sysdate-startup_time))) || ' hour(s) ' ||
 mod(trunc(1440*((sysdate-startup_time) -
 trunc(sysdate-startup_time))), 60) ||' minute(s) ' ||
 mod(trunc(86400*((sysdate-startup_time) -
 trunc(sysdate-startup_time))), 60) ||' seconds' uptime
from
 sys.v_$instance;

2. Database Products and version Notes

select BANNER product_versions
from v$version;

Sample Output:

PRODUCT_VERSIONS
-----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

3. Database Information

• Database Name – Name of the database
• Created – Date/time the database was created
• Log Mode – Archive log mode: NOARCHIVELOG or ARCHIVELOG

select 	NAME,
	CREATED,
	LOG_MODE,
	CHECKPOINT_CHANGE#,
	ARCHIVE_CHANGE#
from 	v$database;

4. Database Size

SQL query to check how large your database is.

col "Database Size" format a20
col "Free space" format a20
col "Used space" format a20
select	round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size"
,	round(sum(used.bytes) / 1024 / 1024 / 1024 ) -
	round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space"
,	round(free.p / 1024 / 1024 / 1024) || ' GB' "Free space"
from    (select	bytes
	from	v$datafile
	union	all
	select	bytes
	from 	v$tempfile
	union 	all
	select 	bytes
	from 	v$log) used
,	(select sum(bytes) as p
	from dba_free_space) free
group by free.p
/

Retrieve size information in terms of file name and tablespaces:

select 	FILE_NAME,
	d.TABLESPACE_NAME,
	d.BYTES datafile_size,
	nvl(sum(e.BYTES),0) bytes_used,
	round(nvl(sum(e.BYTES),0) / (d.BYTES), 4) * 100 percent_used,
	d.BYTES - nvl(sum(e.BYTES),0) bytes_free
from 	DBA_EXTENTS e,
	DBA_DATA_FILES d
where  	d.FILE_ID = e.FILE_ID (+)
group  	by FILE_NAME,d.TABLESPACE_NAME, d.FILE_ID, d.BYTES, STATUS
order  	by d.TABLESPACE_NAME,d.FILE_ID;

5.  Files and Locations

• Filename – Name of the file(s)
• Location – Location of the file(s)

select 	'Archived Log Directory' "Filename",
	value "Location"
from 	v$parameter
where 	name = 'log_archive_dest'
UNION
select 	'Control Files' "Filename",
	value "Location"
from 	v$parameter
where  	name = 'control_files'
UNION
select 	'Datafile' "Filename",
	name "Location"
from   	v$datafile
UNION
select 	'LogFile Member' "Filename",
	member "Location"
from  	v$logfile;

6.  Licence Information

select   SESSIONS_MAX,
         SESSIONS_WARNING,
         SESSIONS_CURRENT,
         SESSIONS_HIGHWATER,
         USERS_MAX
from     sys.v_$license;

7.  Database Options

select   PARAMETER,
         VALUE
from     sys.v_$option;

• Option – Name of the option
• Value – Value of the option

8.  INIT.ORA

a,

select	NAME,
	VALUE,
	ISDEFAULT,
	ISSES_MODIFIABLE,
	ISMODIFIED
from 	v$parameter
order  	by NAME;

b,  Show latent parameter changes

select name
,      value
from   v$parameter
where  ismodified != 'FALSE'
/

c,  Show non-default parameters

set pages 999 lines 100
col name format a30
col value format a50
select 	name
,	value
from	v$parameter
where	isdefault = 'FALSE'
and	value is not null
order	by name
/

9.  Distribution of Objects and Data

a, Which schemas are taking up all of the space.

set pages 999
col "size MB" format 999,999,999
col "Objects" format 999,999,999
select	obj.owner "Owner"
,	obj_cnt "Objects"
,	decode(seg_size, NULL, 0, seg_size) "size MB"
from 	(select owner, count(*) obj_cnt from dba_objects group by owner) obj
,	(select owner, ceil(sum(bytes)/1024/1024) seg_size
	from dba_segments group by owner) seg
where 	obj.owner  = seg.owner(+)
order	by 3 desc ,2 desc, 1
/

b, Show the ten largest objects in the database.

col	owner format a15
col	segment_name format a30
col	segment_type format a15
col	mb format 999,999,999
select  owner
,	segment_name
,	segment_type
,	mb
from	(
	select	owner
	,	segment_name
	,	segment_type
	,	bytes / 1024 / 1024 "MB"
	from	dba_segments
	order	by bytes desc
	)
where	rownum < 11
/

10.  Java Installed or Not

select 	count(*)
from 	all_objects
where 	object_type like '%JAVA%'
and 	owner = 'SYS'
/

If result is 9000, Java is installed.

11.  Character Set information

select * from nls_database_parameters
/

Links: