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: