Database General Stats

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:

DB_BLOCK_SIZE

This parameter in the init.ora is the most important. This can be done only during creation time. If you have already created the Database you cannot change this value. You will have to re-create the Database with a different size.

show parameter DB_BLOCK_SIZE;
SELECT
 name, value, display_value, description
 FROM
 v$parameter
 WHERE
 name = lower('DB_BLOCK_SIZE');

 

Database Size

dba_data_files –  means total size of the data file.

The data file size is total of dba_free_space and dba_segments:
dba_data_files = dba_free_space + dba_segments

dba_segments – means used size of the data file

dba_free_space – means free size of the data file

Size Check Queries:

To get total datafiles size, free and used space in percentage:

select t2.total "TOTAL SIZE",t1.free "FREE SPACE",
 (t1.free/t2.total)*100 "FREE (%)",
 (1-t1.free/t2.total)*100 "USED (%)"
from (select sum(bytes)/1024/1024 free from dba_free_space) t1 ,
 (select sum(bytes)/1024/1024 total from dba_Data_files) t2;

To get the size of all Data files:

select sum(bytes)/1024/1024 "Data files size in MB" from dba_data_files;

To get the size of all TEMP files:

select nvl(sum(bytes),0)/1024/1024 "Temp files size in MB" from dba_temp_files;

To get the size of the on-line redo-logs:

SELECT SUM(BYTES)/1024/1024 "Redo files size in MB" FROM SYS.V_$LOG;

Total Size and free size:

select round(sum(used.bytes) / 1024 / 1024 ) || ' MB' "Database Size"
, round(free.p / 1024 / 1024) || ' MB' "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;

Individual tablespace size:

select tablespace_name as "Tablespace Name",sum(bytes)/(1024*1024) as "Size"
from dba_data_files
 group by tablespace_name
 ORDER BY 1;

Individual tablespace used size:

select tablespace_name as "Tablespace Name",sum(bytes)/(1024*1024) as "Used Size"
from dba_segments
 GROUP BY TABLESPACE_NAME
 order by 1;

Individual tablespace free space:

select tablespace_name as "Tablespace Name", sum(bytes)/(1024*1024) as "Free Space"
 from dba_free_space
 group by tablespace_name
 order by 1;

Reference Links: