About ur DB

select banner "Version of Your Data Base" from v$version;
set pause on
select name "Data Base Name" from v$database;

set pages 80
select log_mode "Your DB in " from v$database;

col "Parameters Mention in SP File" for a35
col value for a25
select name "Parameters Mention in SP File",value from v$spparameter
where ordinal=1 order by name;

col "Parameters of All Dumps" for a25
col value for a30
select upper(NAME) "Parameters of All Dumps",value from v$spparameter
where name in ('user_dump_dest','background_dump_dest','core_dump_dest');

select round(sum(bytes)/1024/1024,2) as "Size of DB in MB" from
(select sum(bytes) bytes
from dba_data_files
union all
select sum(bytes) bytes from dba_temp_files
union all
select sum(l.bytes) bytes from v$log l,v$logfile f where l.group#=f.group#);

select round(sum(bytes)/1024/1024,2) "Used Space of DB in MB's" from sys.sm$ts_used;

select round(sum(bytes)/1024/1024,2) "Free Space of DB in MB's" from sys.sm$ts_free;

select tablespace_name,bytes/1024/1024 "SIZE IN MB" from sm$ts_avail;

col "Default Temporary Tablespace" for a35
select property_value "Default Temporary Tablespace" from database_properties
where property_name='DEFAULT_TEMP_TABLESPACE';

select name "Control files Location in DB" from v$controlfile;

select count(*) as "Number of Redo Groups" from v$log;
col "Redo Members Location in DB" for a50

select group# "Group No.",member "Redo Members Location in DB" from v$logfile;

select username "Data Base Default Users" from dba_users where rownum<5;

select username "Users Created by SYS" from dba_users
where username not in ('SYS','SYSTEM','DBSNMP','OUTLN');

select sum(decode(object_type,'TABLE',1,0)) "SYS :- TABLES",
sum(decode(object_type,'VIEW',1,0)) "VIEWS",
sum(decode(object_type,'SEQUENCES',1,0)) "SEQUENCES",
sum(decode(object_type,'SYNONYMS',1,0)) "SYNONYMS"
from dba_objects where owner='SYS';

select sum(decode(object_type,'TABLE',1,0)) "SYSTEM :- TABLES",
sum(decode(object_type,'VIEW',1,0)) "VIEWS",
sum(decode(object_type,'SEQUENCES',1,0)) "SEQUENCES",
sum(decode(object_type,'SYNONYMS',1,0)) "SYNONYMS"
from dba_objects where owner='SYSTEM';

SELECT le.leseq "Current log sequence No",
100*cp.cpodr_bno/le.lesiz "Percent Full",
cp.cpodr_bno "Current Block No",
le.lesiz "Size of Log in Blocks"
FROM x$kcccp cp, x$kccle le
WHERE le.leseq =CP.cpodr_seq
AND bitand(le.leflg,24) = 8;

col b.tablespace for a15
col SIZE for a5
col SID_SERIAL for a10
col a.username for a15
col a.program for a15
SELECT b.tablespace,
ROUND(((b.blocks*p.value)/1024/1024),2)||'M' "SIZE",
a.sid||','||a.serial# SID_SERIAL,
a.username,a.program FROM sys.v_$session a,
sys.v_$sort_usage b,sys.v_$parameter p
WHERE p.name = 'db_block_size' AND a.saddr = b.session_addr
ORDER BY b.tablespace, b.blocks;

col sid_serial for a10
col orauser for a10
col program for a25
col undoseg for a10
col undo for a10
SELECT TO_CHAR(s.sid)||'-'||TO_CHAR(s.serial#) sid_serial,
NVL(s.username, 'None') orauser,
s.program,r.name undoseg,
t.used_ublk * TO_NUMBER(x.value)/1024||'K' "Undo"
FROM sys.v_$rollname r,
sys.v_$session s, sys.v_$transaction t, sys.v_$parameter x
WHERE s.taddr = t.addr AND r.usn = t.xidusn(+)
AND x.name = 'db_block_size';

set verify off
select sum(decode(object_type,'TABLE',1,0)) "USERS :- TABLES",
sum(decode(object_type,'VIEW',1,0)) "VIEWS",
sum(decode(object_type,'SEQUENCE',1,0)) "SEQUENCES",
sum(decode(object_type,'SYNONYM',1,0)) "SYNONYMS"
from dba_objects where owner='&USER';