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的升级文档: