FROM dba_segments
GROUP BY segment_name);
5.5 查看回滚段名称及大小
select segment_name, tablespace_name, r.status,
(initial_extent / 1024) InitialExtent, (next_extent / 1024) NextExtent, max_extents,
v.curext CurExtent
From dba_rollback_segs r, v$rollstat v Where r.segment_id = v.usn(+) order by segment_name;
5.6 查看控制文件
select name from v$controlfile;
5.7 查看日志文件
select member from v$logfile;
5.8 查看表空间的使用情况
select sum(bytes) / (1024 * 1024) as free_space, tablespace_name from dba_free_space
group by tablespace_name;
SELECT A.TABLESPACE_NAME, A.BYTES TOTAL, B.BYTES USED, C.BYTES FREE,
(B.BYTES * 100) / A.BYTES \ (C.BYTES * 100) / A.BYTES \
FROM SYS.SM$TS_AVAIL A, SYS.SM$TS_USED B, SYS.SM$TS_FREE C WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME AND A.TABLESPACE_NAME = C.TABLESPACE_NAME;
5.9 查看数据库对象
select owner, object_type, status, count(*) count# from all_objects
group by owner, object_type, status;
5.10 查看数据库的版本
Select version
FROM Product_component_version
Where SUBSTR(PRODUCT, 1, 6) = 'Oracle';
5.11 查看Oracle字符集
select * from sys.props$ where name = 'NLS_CHARACTERSET';
5.12 在某个用户下找所有的索引
select user_indexes.table_name, user_indexes.index_name, uniqueness, column_name
from user_ind_columns, user_indexes
where user_ind_columns.index_name = user_indexes.index_name and user_ind_columns.table_name = user_indexes.table_name order by user_indexes.table_type, user_indexes.table_name, user_indexes.index_name, column_position;
5.13 表、索引的存储情况检查
select segment_name, sum(bytes), count(*) ext_quan from dba_extents
where tablespace_name = '&tablespace_name' and segment_type = 'TABLE'
group by tablespace_name, segment_name; select segment_name, count(*) from dba_extents
where segment_type = 'INDEX'
and owner = '&owner' group by segment_name;
5.14 查看数据库的创建日期和归档方式
Select Created, Log_Mode, Log_Mode From V$Database;
5.15 显示所有数据库对象的类别和大小
select type,
count(name) num_instances, sum(source_size) source_size, sum(parsed_size) parsed_size, sum(code_size) code_size, sum(error_size) error_size,
sum(source_size) + sum(parsed_size) + sum(code_size) + sum(error_size) size_required from dba_object_size group by type order by 1;
5.16 设置RAC为归档模式？
步骤：
1. 以SYSDBA身份登陆2个节点，执行
alter system set cluster_database=false scope =spfile sid=’*’; 设置归档路径
alter system set log_archive_start=true scope=spfile;
2. 2个节点
shutdown immediate
3. 在一个节点上执行
startup mount
alter database archivelog; shutdown immediate; alter database open;
alter system set cluster_database=true scope =spfile sid=’*’; shutdown immediate
4、分别启动2个节点，修改完毕
6. AWR报告
与9i 中的statspack相似，awr报告也需要两个快照，才能生成这两个时间点之间的性能报告。
$sqlplus / as sysdba
? 生成快照一（10g中自动会每个整点都会生成一个快照）
SQL> exec dbms_workload_repository.create_snapshot(); ? (间隔一段时间)生成快照二
SQL> exec dbms_workload_repository.create_snapshot(); ? 生成报告
SQL> @?/rdbms/admin/awrrpt.sql
7. Troubleshooting
常用性能相关SQL，监控数据库性能的SQL语句。
7.1 监控事务的等待
select event,
sum(decode(wait_Time, 0, 0, 1)) \ sum(decode(wait_Time, 0, 1, 0)) \ count(*) \ from v$session_Wait group by event order by 4;
7.2 查看一些等待信息：
select sid, event
from v$session_wait
where event not like 'SQL%' and event not like '%ipc%';
查看是否存在下面等常见的等待事件：
? buffer busy waits， ? free buffer waits，
? db file sequential read, ? db file scattered read, ? enqueue,latch free, ? log file parallel write, ? log file sync
7.3 查看等待(wait)情况
SELECT v$waitstat.class,
v$waitstat.count count,
SUM(v$sysstat.value) sum_value FROM v$waitstat, v$sysstat
WHERE v$sysstat.name IN ('db block gets', 'consistent gets') group by v$waitstat.class, v$waitstat.count;
7.4 回滚段查看
select rownum,
sys.dba_rollback_segs.segment_name Name, v$rollstat.extents Extents,
v$rollstat.rssize Size_in_Bytes, v$rollstat.xacts XActs, v$rollstat.gets Gets, v$rollstat.waits Waits, v$rollstat.writes Writes,
sys.dba_rollback_segs.status status
from v$rollstat, sys.dba_rollback_segs, v$rollname
where v$rollname.name(+) = sys.dba_rollback_segs.segment_name and v$rollstat.usn(+) = v$rollname.usn order by rownum;
7.5 回滚段的争用情况
select name, waits, gets, waits / gets \ from v$rollstat a, v$rollname b where a.usn = b.usn;