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: