DBA常用脚本.doc

Oracle管理及常用基础脚本 1 性能监控 1. 检测数据库中的事件和等待 SELECT event, total_waits, total_timeouts,time_waited, average_wait FROM v$system_event 2. 查询会话中的事件和等待时间 select sid, event, total_waits,average_wait from v$session_event where sid=10; 3. 查询等待进程 SELECT sid, seq#, event, wait_time, state FROM v$session_wait; 4. 监控全局区的性能 select * from v$sgastat; 5. 查询命中率 select gethitratio from v$librarycache where namespace = SQL AREA; 6. 当前 sql 语句 select sql_text, users_executing, executions, loads from v$sqlarea; 7. 查询高速缓存中的命中率 select sum(pins) Executions, sum(reloads) Cache Misses, sum(reloads)/sum(pins) from v$librarycache; 8. 查询全局字典中的有效装载次数 select namespace,pins,reloads,invalidations from v$librarycache; 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, dba_data_files df where f.file# = df.file_id order by df.tablespace_name; 11. 监控文件系统的 I/O 比例 select substr(a.file#,1,2) #, substr(,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_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; 13. 监控 SGA 中字典缓冲区的命中率 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% select sum(pins) Total Pins, sum(reloads) Total Reloads, sum(reloads)/sum(pins) *100 libcache from v$librarycache; select sum(pinhits-reloads)/sum(pins) hit radio,sum(reloads)/sum(pins) reload percent from v$librarycache; 15. 显示所有数据库对象的类别和大小 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(parsed_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/(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 from v$librarycache; select (sum(gets - getmisses - usage - fixed) / sum(gets) row cache from v$rowcache; select sum(pins) executions, sum(reloads) cache misses while executing from v$librarycache; 后者除以前者 , 此比率小于 1%, 接近 0% 为好 select sum(gets) dictionary gets,sum(getmisses) dictionary cache get misses from v$rowcache 19. 找 ORACLE 字符集 select * from ps$ where name=NLS_CHARACTERSET; 20. 监控 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 时，参数需加大 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 segment_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(*) extents FROM dba_segments WHERE owner NOT IN (SYS, SYSTEM) GROUP BY segment_name HAVING COUNT(*) = (SELECT MAX( COUNT(*) ) FROM dba_segments GROUP BY segment_name); 17. 表、索引的存储情况检查 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; 18 、找使用 CPU 多的用户 session 12 是 cpu used by this session select a.sid,spid,status,substr(gram,1,40) prog,a.terminal,osuser,value/60/100 value 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 - 在显示中指明列的输出格式 SQL select b.file_id - 文件 ID, b.tablespace_name - 表空间 , b.file_name - 物理文件名 , b.bytes - 总字节数 , (b.bytes-sum(nvl(a.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. 查看现有回滚段及其状态 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-freespace/totalspace)*100,2) 使用率 % from (select tablespace_name,round(sum(bytes)/1024/1024) totalspace from dba_data_files 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. 删除表空间 select , from v$tablespace t,v$datafile d where =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. 为这个表空间增加一个数据文件 alter tablespace 表空间名 add datafile /u1/oradata/userdata_002.ora size 50m; -Unix alter tablespace 表空间名 add datafile c:oradatauserdata_002.ora size 50m; -Windows NT 8. 重新调整数据文件的大小 alter database datafile /u1/oradata/userdata_001.ora resize 50M; -Unix alter database datafile c:oradatauserdata_002.ora resize 50M; -Windows NT 9. 临时表空间和排序表空间的使用状态 select substr(vses.username,1,12) ORA_USER, substr(osuser,1,12) OS_USER, substR(sql_text,1,50) SQL_STMT from 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. 确定盘区和盘区内容及表或者空间的碎片程度 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_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 open 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= 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 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. 如何做数据库的热备份和恢复 - 要对数据库做热备份需要数据库运行在归档模式下 : - 首先使表空间处于备份状态 - 拷贝数据文件 - 使表空间回复到正常状态 - 进入 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 、查看当前所有对象 SQL select * from tab; 2 、建一个和 a 表结构一样的空表 SQL create table b as select * from a where 1=2; SQL create table b(b1,b2,b3) as select a1,a2,a3 from a where 1=2; 7. 如何查看当前日期 SQL select to_char(sysdate,yyyy-mm-dd,hh24:mi:ss) from dual; 8. 如何在用户间复制数据 SQL copy from user1 to user2 create table2 using select * from table1; 9. 视图中不能使用 order by ，但可用 group by 代替来达到排序目的 SQL create view a as select b1,b2 from b group by b1,b2; 10. 通过授权的方式来创建用户 SQL grant connect,resource to test identified by test default tablespace users; SQLalter user test quota 100M on users- 在指定的表空间中为某个用户分配资源 SQL conn test/test 如果不给用户 connect 和 resource 角色的话那么他们在指定的表空间中将 无法创建对象，比如 create table 11. 如何锁定用户的账号和密码 alter user test account unlock/lock; 12. 如何在命令行安装 runInstaller -responsefile filename -silent -nowelcome 13. 如何使用操作系统认证 Set REMOTE_LOGIN_PASSWORDFILE to NONE CONNECT / AS SYSDBA CONNECT / AS SYSOPER 14. 如何通过密码文件认证 $orapwd file=$ORACLE_HOME/dbs/orapw SID password=admin entries=10 Set REMOTE_LOGIN_PASSWORDFILE to EXCLUSIVE or SHARED CONNECT INTERNAL/CONNECT 15. 如何进行管理例程 STARTUP PFILE=/DISK1/initU15.ora STARTUPFORCERESTRICTPFILE=filenameOPEN RECOVER database|MOUNT|NOMOUNT ALTER DATABASE database MOUNT; ALTER DATABASE database OPEN READ ALTER DATABASE database OPEN READ ONLY; ALTER DATABASE MOUNT | OPEN ALTER DATABASE OPEN READ WRITE| READ ONLY 16. 如何创建物化视图 CREATE MATERIALIZED VIEW LOG ON sales WITH SEQUENCE, ROWID (prod_id, cust_id, time_id, channel_id, promo_id, quantity_sold, amount_sold) INCLUDING NEW VALUES; 首先创建视图日志 CREATE MATERIALIZED VIEW sum_sales PARALLEL BUILD IMMEDIATE REFRESH FAST ON COMMIT AS SELECT d_id, s.time_id,COUNT(*) AS count_grp,SUM(s.amount_sold) AS sum_dollar_sales, COUNT(s.amount_sold) AS count_dollar_sales,SUM(s.quantity_sold) AS sum_quantity_sales, COUNT(s.quantity_sold) AS count_quantity_sales FROM sales s GROUP BY d_id, s.time_id; 然后创建视图 17. 分区表的创建、更改和删除等管理 - 创建表空间 create tablespace space_name1 datafile /oradata/space_name1.dbf size 100m - 创建分区表 create table mx_xsmx (cat char(1) , product varchar(20) partition by range(cat) 有 range hash list 三种分区方法 (partition xsmx_01 values less than (B) tablespace space_name1, partition xsmx_02 values less than (maxvalue) tablespace space_name1) - 添加分区 alter table mx_xsmx add partition xsmx_03 values less than (Z) - 将某一个分区割裂 alter table mx_xsmx split partition xsmx_03 at (G) into (partition xsmx_04,partition xsmx_05) - 删除分区 alter table mx_xsmx drop partition xsmx_04 alter table mx_xsmx truncate partition xsmx_04 alter table mx_xsmx rename partition xsmx_04 to xsmx_06 alter table mx_xsmx modify partition xsmx_01 storage (next 1m pctincrease 0) - 分区表的导出 某个分区 exp sales/sales_password tables=mx_xsmx:xsmx_01 rows=Y file=sales1999_q1.dmp - 6 、 IMPORT 分区： - ORACLE8 的 IMPORT 工具可在表的分区以及导入数据，例如在 2001 年，用户要查看 1999 年的数据， DBA 必须导入 1999 年的数据，使之在线，操作如下： STEP1 、建立表的 1999 年的四个表空间和相应的分区，参照（ 2 ）； STEP2 、导入数据： oracle$ imp sales/sales_password FILE =sales1999_q1.dmp TABLES = (sales:sales1999_q1) IGNORE=y - 选择指定的分区来查询结果 select * from mx_xsmx partition (xsmx_04) where DeptNo between 11 and 20 - 对分区表来作索引 create index index_name on table_name(DeptNO 列名 ) local (partition part1 tablespace space_idx1, partition part2 tablespace space_idx2) 18. 创建表的语句中参数的含义 创建表及其参数含义 CREATE TABLE MB_CPZ ( YWFWDM VARCHAR2 (4), CPZDM VARCHAR2 (2) TABLESPACE DWSDATA PCTFREE 10 - 本参数设置为非零的时候后台 SMON 进程会自动合并相邻的自由范围 . PCTUSED 40 - 为恢复 INSERT 操作而设定的。 INITRANS 1 MAXTRANS 255 STORAGE ( INITIAL 65536 - 初始范围为 65536 个字节就是 64K ， oracle 的块大小我们设定为 8K 。这也是 Oracle 给出的默认值 NEXT 0 - Oracle 将不会考虑这个设置的值 MINEXTENTS 1 - 最少 1 个范围 MAXEXTENTS 2147483645 - 最大范围 Oracle 将不会考虑这个设置的值 FREELISTS 1 FREELIST GROUPS 1 ) NOCACHE; 不放入高速缓存 LOGGING|NOLOGGING 所有的对表的操作都要记入 REDOLOG ， ORACLE 建议使用 NOLOGGING ； 手动修改表的参数 ALTER TABLE 名称 PCTFREE 20 PCTUSED 50 STOAGE （ MAXEXTENTS 1000 ）； 手工分配空间 ALTER TABLE 名称 ALLOCATE EXTENT （ SIZE 500K DATAFILE ）； 19. 索引的基本知识 一、索引的分类： 1 、逻辑上： 单列索引 复合索引 唯一索引 非唯一索引 2 、物理上： B-TREE OR BITMAP B-TREE （ NORMAL 和反向索引） 二、 CREATE INDEX CREATE INDEX 名称 ON 表名（列名） PCTFREE 30 STORAGE （。） TABLESPACE 名称没有定义 PCTUSED ：索引是按照一定存储的，如果通过 PCTUSED 允许恢复对 BLOCK 的 INSERT 操作，可能影响 INDEX 的效率。 三、建立索引的注意事项 1 、索引对查询性能有提高，但对 DML 语句有影响。 2 、索引也应该放在一个专用的表空间 3 、定义索引的 EXTENT 的大小时， =5*DB BLOCK 4 、建立索引时，应采用 NOLOGGING 方式。 四、修改索引 ALTER INDEX 名称 STORAGE （新值） 五、分配空间给索引 1 、 ALTER INDEX 名称 ALLOCATE EXTENT （ SIZE 200K DATAFILE 。 ） 六、重建索引 1 、提高查询性能 2 、当一个索引重建时，老的索引会在新索引建立完成后，被删除。 3 、新索引建立过程中，老的索引仍可用于查询。 4 、硬盘的开销大， 七、 DROP 一个索引 DROP INDEX 名称 八、信息获取 1 、 DBA_INDEXES: 建索引的参数 2 、 DBA_IND_COLUMNS: 20.ORACLE 中用户管理 一、 ORACLE 的安全域 1 、 TABLESPACE QUOTAS ：表空间的使用定额 2 、 DEFAULT TABLESPACE ：默认表空间 3 、 TEMPORARY TABLESPACE ：指定临时表空间。 4 、 ACCOUNT LOCKING ：用户锁 5 、 RESOURCE LIMITE ：资源限制 6 、 DIRECT PRIVILEGES ：直接授权 7 、 ROLE PRIVILEGES ：角色授权先将应用中的用户划为不同的角色， 二、创建用户时的清单： 1 、选择一个用户名称和检验机制： A ，看到用户名，实际操作者是谁，业务中角色。 2 、选择合适的表空间： 3 、决定定额： 4 、口令的选择： 5 、临时表空间的选择：先建立一个临时表空间，然后在分配。不分配，使用 SYSTEM 表空间 6 、 CREATE USER 7 、授权： A ，用户的工作职能 B ，用户的级别 三、用户的创建： 1 、命令： CREATE USER 名称 IDENTIFIED BY 口令 DEFAULT TABLESPACE 默认表空间名 TEMPOARAY TABLESPACE 临时表空间名 QUOTA 15M ON 表空间名 PASSWORD EXPIRE ：当用户第一次登陆到 ORACLE ，创建时所指定的口令过期失效，强迫用户自己定义一个新口令。 ACCOUNT LOCK ：加用户锁 QUOTA UNLIMITED ON TABLESPACE ：不限制，有多少有多少。 PROFILE 名称 ：受 PROFILE 文件的限制。 四、如何控制用户口令和用户锁 1 、强迫用户修改口令： ALTER USER 名称 IDENTIFIED BY 新口令 PASSWORD EXPIRE ； 2 、给用户加锁： ALTER USER 名称 ACCOUNT LOCK|UNLOCK 3 、注意事项： A 、所有操作对当前连接无效 B 、 1 的操作适用于当用户忘记口令时。 五、更改定额 1 、命令： ALTER USER 名称 QUOTA 0 ON 表空间名 ALTER USER 名字 QUOTA （数值） K|M|UNLIMITED ON 表空间名； 2 、使用方法： A 、控制用户数据增长 B 、当用户拥有一定的数据，而管理员不想让他在增加新的数据的时候。 C 、当将用户定额设为零的时候，用户不能创建新的数据，但原有数据仍可访问。 六、 DROP 一个 USER 1 、 DROP USER 名称 适合于删除一个新的用户 2 、 DROP USER 名称 CASCADE ： 删除一个用户，将用户的表，索引等都删除。 3 、对连接中的用户不好用。 七、信息获取： 1 、 DBA_USERS: 用户名，状态，加锁日期，默认表空间，临时表空间 2 、 DBA_TS_QUOTAS: 用户名，表空间名，定额。 两个表的连接字段： USERNAME GRANT CREATE SESSION TO 用户名 21.PROFILE 的管理（资源文件的管理） 一、 PROFILE 的管理内容： 1 、 CPU 的时间 2 、 I/O 的使用 3 、 IDLE TIME （空闲时间） 4 、 CONNECT TIME （连接时间） 5 、并发会话数量 6 、口令机制： 二、 DEFAULT PROFILE ： 1 、所有的用户创建时都会被指定这个 PROFILE 2 、 DEFAULT PROFILE 的内容为空，无限制 三、 PROFILE 的划分： 1 、 CALL 级 LIMITE ： 对象是语句： 当该语句资源使用溢出时： A 、该语句终止 B 、事物回退 C 、 SESSION 连接保持 2 、 SESSION 级 LIMITE ： 对象是：整个会话过程 溢出时：连接终止 四、如何管理一个 PROFILE 1 、 CREATE PROFILE 2 、分配给一个用户 3 、象开关一样打开限制。 五、如何创建一个 PROFILE ： 1 、命令： CREATE PROFILE 名称 LIMIT SESSION_PER_USER 2 CPU_PER_SESSION 1000 IDLE_TIME 60 CONNECT_TIME 480 六、限制参数： 1 、 SESSION 级 LIMITE ： CPU_PER_SESSION: 定义了每个 SESSION 占用的 CPU 的时间：（ 1/100 秒） 2 、 SESSION_PER_USER: 每个用户的并发连接数 3 、 CONNECT_TIME: 一个连接的最长连接时间（分钟） 4 、 LOGICAL_READS_PER_SESSION: 一次读写的逻辑块的数量 5 、 CALL 级 LIMITE CPU_PER_CALL: 每个语句占用的 CPU 时间 LOGICAL_READS_PER_CALL: 七、分配给一个用户： CREATE USER 名称。 PROFILE 名称 ALTER USER 名称 PROFILE 名称 八、打开资源限制： 1 、 RESOURCE_LIMT: 资源文件中含有 2 、 ALTER SYSTEM SET RESOURCE_LIMIT=TRUE ； 3 、默认不打开 九、修改 PROFIE 的内容： 1 、 ALTER PROFILE 名称参数 新值 2 、对于当前连接修改不生效。 十、 DROP 一个 PROFILE 1 、 DROP