Oracle管理及常用基础脚本Word格式.docx

1、 select namespace,pins,reloads,invalidations 9. 回滚段的争用情况 select name, waits, gets, waits/gets Ratio from v$rollstat a, v$rollname b where a.usn = b.usn;10. 监控表空间的 I/O 比例 select df.tablespace_name name,df.file_name file,f.phyrds pyr, f.phyblkrd pbr,f.phywrts pyw, f.phyblkwrt pbw from v$filestat f, db

2、a_data_files df where f.file# = df.file_id order by df.tablespace_name;11. 监控文件系统的 I/O 比例 select substr（a.file#,1,2） #, substr（a.name,1,30） Name a.status, a.bytes, b.phyrds, b.phywrts from v$datafile a, v$filestat b where a.file# = b.file#;12. 在某个用户下找所有的索引 select user_indexes.table_name, user_indexe

3、s.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;13. 监控 SG

4、A 中字典缓冲区的命中率 select parameter, gets,Getmisses , getmisses/（gets+getmisses）*100 miss ratio （1-（sum（getmisses）/ （sum（gets）+sum（getmisses）*100 Hit ratio from v$rowcache where gets+getmisses 0 group by parameter, gets, getmisses;14. 监控 SGA 中共享缓存区的命中率，应该小于 1% Total PinsTotal Reloads sum（reloads）/sum（pins

5、） *100 libcache select sum（pinhits-reloads）/sum（pins） hit radio,sum（reloads）/sum（pins） reload percent15. 显示所有数据库对象的类别和大小 select count（name） num_instances ,type ,sum（source_size） source_size , sum（parsed_size） parsed_size ,sum（code_size） code_size ,sum（error_size） error_size, sum（source_size） +sum（pa

6、rsed_size） +sum（code_size） +sum（error_size） size_required from dba_object_size group by type order by 2;16. 监控 SGA 中重做日志缓存区的命中率，应该小于 1% select name, gets, misses, immediate_gets, immediate_misses, Decode（gets,0,0,misses/gets*100） ratio1, Decode（immediate_gets+immediate_misses,0,0, immediate_misses/（

7、immediate_gets+immediate_misses）*100） ratio2 FROM v$latch WHERE name IN （redo allocation, redo copy）;17. 监控内存和硬盘的排序比率，最好使它小于 .10 ，增加 sort_area_size SELECT name, value FROM v$sysstat WHERE name IN （sorts （memory）sorts （disk）18. 监控字典缓冲区 select （sum（pins - reloads） / sum（pins） lib cache select （sum（get

8、s - getmisses - usage - fixed） / sum（gets） row cache from v$rowcache;select sum（pins） executionscache misses while executing后者除以前者 , 此比率小于 1%, 接近 0% 为好 select sum（gets） dictionary gets,sum（getmisses） dictionary cache get missesfrom v$rowcache 19. 找 ORACLE 字符集 select * from sys.props$ where name=NLS_

9、CHARACTERSET20. 监控 MTS select busy/（busy+idle） shared servers busy from v$dispatcher; 此值大于 0.5 时，参数需加大 select sum（wait）/sum（totalq） dispatcher waits from v$queue where type=dispatcher select count（*） from v$dispatcher; select servers_highwater from v$mts; servers_highwater 接近 mts_max_servers 时，参数需加大

10、 21. 碎片程度 select tablespace_name,count（tablespace_name） from dba_free_space group by tablespace_name having count（tablespace_name）10; alter tablespace name coalesce; alter table name deallocate unused; create or replace view ts_blocks_v as select tablespace_name,block_id,bytes,blocks,free space segm

11、ent_name from dba_free_space union all select tablespace_name,block_id,bytes,blocks,segment_name from dba_extents; select * from ts_blocks_v; select tablespace_name,sum（bytes）,max（bytes）,count（block_id） from dba_free_space group by tablespace_name; 查看碎片程度高的表 SELECT segment_name table_name , COUNT（*）

12、 extents FROM dba_segments WHERE owner NOT IN （SYSSYSTEM） GROUP BY segment_name HAVING COUNT（*） = （SELECT MAX（ COUNT（*） ） FROM dba_segments GROUP BY segment_name）;22. 表、索引的存储情况检查 select segment_name,sum（bytes）,count（*） ext_quan from dba_extents where tablespace_name=&tablespace_name and segment_type

13、=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;23 、找使用 CPU 多的用户 session 12 是 cpu used by this session select a.sid,spid,status,substr（a.program,1,40） prog,a.terminal,osuser,value/60/100 value

14、from v$session a,v$process b,v$sesstat c where c.statistic#=12 and c.sid=a.sid and a.paddr=b.addr order by value desc;2 空间管理 1. 察看数据库的大小，和空间使用情况 SQL col tablespace format a20 - 在显示中指明列的输出格式 select b.file_id - 文件 ID, b.tablespace_name - 表空间 , b.file_name - 物理文件名 , b.bytes - 总字节数 , （b.bytes-sum（nvl（a.

15、bytes,0） - 已使用 , sum（nvl（a.bytes,0） - 剩余 , sum（nvl（a.bytes,0）/（b.bytes）*100 - 剩余百分比 from dba_free_space a,dba_data_files b where a.file_id=b.file_id group by b.tablespace_name,b.file_name,b.file_id,b.bytes order by b.tablespace_name / dba_free_space - 表空间剩余空间状况 dba_data_files - 数据文件空间占用情况 2. 查看现有回滚段

16、及其状态 select segment_name,owner,tablespace_name,segment_id,file_id,status from dba_rollback_segs;3. 表空间大小 select tablespace_name,count（*）,sum（blocks）,sum（bytes）/1024/1024 from dba_data_files group by tablespace_name;4. 表空间使用情况 select df.tablespace_name 表空间名 ,totalspace 总空间 M,freespace 剩余空间 M,round（1-

17、freespace/totalspace）*100,2） 使用率 %from （select tablespace_name,round（sum（bytes）/1024/1024） totalspace group by tablespace_name） df, （select tablespace_name,round（sum（bytes）/1024/1024） freespace from dba_free_space group by tablespace_name） fs where df.tablespace_name=fs.tablespace_name;5. 删除表空间 sele

18、ct t.name,d.name from v$tablespace t,v$datafile d where t.name=DATA_HOST_A and t.ts#=d.ts#;alter tablespace DATA_HOST_A offline;drop tablespace DATA_HOST_A including contents;6. 查看数据文件的位置 select tablespace_name,file_id,bytes/1024/1024,file_name from dba_data_files order by file_id;7. 为这个表空间增加一个数据文件

19、alter tablespace 表空间名 add datafile /u1/oradata/userdata_002.ora size 50m; -Unix c:oradatauserdata_002.ora -Windows NT 8. 重新调整数据文件的大小 alter database datafile /u1/oradata/userdata_001.ora resize 50M; -Unix 9. 临时表空间和排序表空间的使用状态 select substr（vses.username,1,12） ORA_USER, substr（osuser,1,12） OS_USERsubst

20、R（sql_text,1,50） SQL_STMTfrom V$sqlarea vsql, V$session vses, V$sort_usage vsort where vsort.tablespace = TEMP and vsort.sqladdr=vses.sql_address and vsql.address=vsort.sqladdr select tablespace_name, extent_size, total_extents, used_extents, free_extents, max_used_size from v$sort_segment;10. 确定盘区和

21、盘区内容及表或者空间的碎片程度 select segment_name,exten from dba_extents - 确定盘区的内容 desc dba_segments - 确定段的内容 - 返回了结果表明碎片的严重 select segment_name ,tablespace_name,extents,segment_type from dba_segments where extents4 - 空间碎片严重程度 select tablespace_name ,count（tablespace_name） from dba_free_space group by tablespace_

22、name having count（tablespace_name） 10 3 基本知识 1. 为一个事务指定一个回滚段 set transaction use rollback segment rollback_segment_name - 指定回滚段 2. 如何创建和使用光标 - 光标的使用 declare name char（30） declare homebase char（40） declare style char（20） declare arttist_id int create artist_cursor cursor for select * from dim_age ope

23、n artist_cursor fetch artist_cursor into arttist_id,homebase,style while （sqlstatus=0） begin print homebase print style print arttist_id end close artist_cursor deallocate cursor artist_cursor go 3. 如何导出和导入数据 - 数据导出导入 exp dss/dssoralce query=where day_id 12 tables=（customer） file= d:sample.dmp log=

24、rows=n full=y tables=（） owner= - 三种导出方式 imp userid/pwdoracle_sid fromuser =（ dss,dwh） touser =（dss,dwh） dwh file=sample.dmp - 分区表的到导出 user1.table_name:px 分区 px exp user_id/pwdoracle_sid tables=（user1.table_name:px） file=sample.dmp 4. 如何使数据库运行于归档模式 1. 打开 ini.ora 文件 2. 修改文件内容 log_archive_start = true

25、log_archive_dest_1 = location=D:OracleoradataORACLEarchive log_archive_format = %ORACLE_SID%T%TS%S.ARC 3. 进入服务管理器输入 shutdown startup mount alter database archivelog alter database open 此时数据库运行与归档模式下可以查询输入 archive log list 5. 如何做数据库的热备份和恢复 - 要对数据库做热备份需要数据库运行在归档模式下 :- 首先使表空间处于备份状态 - 拷贝数据文件 - 使表空间回复到正常

26、状态 - 进入 sqlplus 输入： alter tablespace users begin backup ; $ copy d:oracle_homeusr.ora d:backupuser.dbf; alter tablespace users end backup ; alter system checkpoint ;- 恢复数据库需要做如下的步骤：- 将映像备份文件拷贝到各个表空间对应的正确的位置 svrmgrl connect internal startup mount alter database open recover database 6. 如何查看所有的表和创建结构相同的表 1 、查看当前所有对象 select * from tab;2 、建一个和 a 表结构一样的空表 create table b as select * from a where 1=2; create table b（b1,b2,b3） as select a1,a2,a3 from a where 1=2;7. 如何查看当前日期 select to_char（sysdate,yyyy-mm-dd,hh24:mi:ss） from dual;8. 如何在用户间复制数据 copy fr