oracle数据库管理优化必备语句

1、  SELECT T.START_TIME,T.USED_UBLK,S.USERNAME,R.SEGMENET_NAME FROM V$TRANSACTION T,V$SESSION S,V$DBA_ROLLBACK_SEGS R WHERE T.SES_ADDR=S.SADDR  AND T.XIDUSN=R.SEGMENT_ID运行的是无数，用户，保护的撤销段，启动时间以及生成的撤销块的数目。

2、  alter tablespace undo_garantee retention guarantee;alter system set undo_tablespace=undo_nogarantee;alter system set undo_tablespace=undo_garantee;撤销管理。

3、  select begin_time,end_time,undoblks,maxquerylen,ssolderrcnt,nospaceerrcnt from v$undostat;

4、  select (select max(undoblks)*max(maxquerylen) from v$undostat)* (select value from v$parameter where name=’db_block_size’) from dual;

5、  为了查询大量问题的事件类，可以查询v$system_wait_class

select wait_class,total_waits,time_waited from v$system_wait_class order by time_waited;

6、  SQL_ID是SQL在SGA库缓存中的唯一标志符：SELECT SQL_ID FROM V$SQL WHERE SQL_TEXT=’’;

7、  SELECT OWNER,SEGMENT_NAME,SEGMENT_TYPE FROM DBA_EXTENTS WHERE FILE_ID= AND 5 BETWEEN BLOCK_ID AND BLOCK_ID +BLOCKS

8、  1、磁盘数据文件物理写入和读取时间：select name,phyrds,phywrts,readtim,writetim from v$filestat a ,v$datafile b where a.file#=b.file# order by readtim desc

9、  物理移动数据文件：alter tablespace tbs_name offline;alter tablespace tbs_name rename datafile ‘ora_path’ to ‘new_path’;alter tablespace tbs_name online;

10、    查询重做日志文件的大小：select recid,a.start_time,b.start_tme ,b.start_tiem-a.start_time from v$log_history a,v$log_history  where a.recid +1=b.recid

11、    增加日志文件：alter database add logfile member ‘name ‘ to group num;

12、    alter database add logfile group num ‘’size 10m;

13、    alter database drop logfile group num;删除一个日志组

14、    alter database drop logfile member ‘’;删除单个日志文件

15、    重新组织数据文件大小：alter database datafile ‘name ’resize newSize

16、    数据库升级后的操作catupgrd.sql catalog.sql utlrp.sql

17、    SELECT TABLESPACE_NAME,SEGMENT_NAME,STATUS FROM DBA_ROLLBACK_SEGS;所有撤销段和回滚段的记录

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26276376/viewspace-767706/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26276376/viewspace-767706/