1.DB 数目:

ps -ef|grep pmon

2.查看bankup: 及路径

crontab -l

3.DB 大小:

select sum(tsize)/1024/1024/1024 as total_size

from (select bytes as tsize from v$datafile

union all select bytes as tsize from v$tempfile

union all select bytes*members as tsize from v$log);

4.Tablespace :

set line 1000;

set pages 1000;

col HOST_NAME for a15;

col instance_name for a15;

col status for a15;

col VERSION for a15;

select host_name,instance_name,VERSION,status from v$instance;

col Status for a10;

col Name for a30;

col Type for a15;

col Total_Size(M) for a15;

col Ext_Management for a15;

col Used(M) for a15;

col Free(M) for a15;

col Used% for a10;

col Raise for a15;

compute SUM LABEL 'TOTAL' of SIZE USED on report

break on report

SELECT d.status "Status",

d.tablespace_name "Name",

d.contents "Type",

d.extent_management "Ext_Management",

to_char(nvl(a.bytes / 1024 / 1024, 0), '99999999.999') "Total_Size(M)",

to_char(nvl(a.bytes - nvl(f.bytes, 0), 0) / 1024 / 1024,

'99999999.999') "Used(M)",

to_char(nvl(nvl(f.bytes, 0), 0) / 1024 / 1024, '99999999.999') "Free(M)",

to_char(nvl((a.bytes - nvl(f.bytes, 0)) / a.bytes * 100, 0),

'990.00') "Used%",

(case

when to_number(nvl((a.bytes - nvl(f.bytes, 0)) / a.bytes * 100, 00)) >=83 then 'Warning'

end) as "Raise"

FROM sys.dba_tablespaces d,

(SELECT tablespace_name, SUM(bytes) bytes

FROM dba_data_files

GROUP BY tablespace_name) a,

(SELECT tablespace_name, SUM(bytes) bytes

FROM dba_free_space

GROUP BY tablespace_name) f

WHERE d.tablespace_name = a.tablespace_name(+)

AND d.tablespace_name = f.tablespace_name(+)

AND NOT

(d.extent_management LIKE 'LOCAL' AND d.contents LIKE 'TEMPORARY')

UNION ALL

SELECT d.status "Status",

d.tablespace_name "Name",

d.contents "Type",

d.extent_management "Ext_Management",

to_char(nvl(a.bytes / 1024 / 1024, 0), '99999990.900') "Size(M)",

to_char(nvl(t.bytes, 0) / 1024 / 1024, '99999999.999') "Used(M)",

to_char((nvl(a.bytes / 1024 / 1024, 0)) -

(nvl(t.bytes, 0) / 1024 / 1024),

'99999999.999') "Free(M)",

to_char(nvl(t.bytes / a.bytes * 100, 0), '990.00') "Used%",

(case

when to_number(nvl((t.bytes - nvl(a.bytes, 0)) / a.bytes * 100, 00)) >=83 then 'Warning'

end) as "Raise"

FROM sys.dba_tablespaces d,

(SELECT tablespace_name, SUM(bytes) bytes

FROM dba_temp_files

GROUP BY tablespace_name) a,

(SELECT tablespace_name, SUM(bytes_cached) bytes

FROM v$temp_extent_pool

GROUP BY tablespace_name) t

WHERE d.tablespace_name = a.tablespace_name(+)

AND d.tablespace_name = t.tablespace_name(+)

AND d.extent_management LIKE 'LOCAL'

AND d.contents LIKE 'TEMPORARY'

ORDER BY "Used%" DESC;

5.查询数据库所有的文件:

select FILE_NAME from dba_data_files

union all
select NAME from v$controlfile

union all
select MEMBER from v$logfile

union all
select NAME from v$tempfile;

6.查看Spfile:

show parameter spfile

7.查看是否归档:

archive log list

8.查看DB块大小:

show parameter db_block_size

9.查看sga/pga大小:

show parameter sga/pga

10.查看字符集

SQL> col parameter for a30

SQL> col value for a30

SQL> select * from v$nls_parameters;

11.Oracle 10g 升到Oracle11g的升级文档: