Netopeak: August 2022

Tuesday, August 30, 2022

-- +----------------------------------------------------------------------------+ -- | Jeffrey M. Hunter | -- | jhunter@idevelopment.info | -- | www.idevelopment.info | -- |----------------------------------------------------------------------------| -- | Copyright (c) 1998-2015 Jeffrey M. Hunter. All rights reserved. | -- |----------------------------------------------------------------------------| -- | DATABASE : Oracle | -- | FILE : rac_instances.sql | -- | CLASS : Real Application Clusters | -- | PURPOSE : Provide a summary report of all configured instances for the | -- | current clustered database. | -- | NOTE : As with any code, ensure to test this script in a development | -- | environment before attempting to run it in production. | -- +----------------------------------------------------------------------------+ SET TERMOUT OFF; COLUMN current_instance NEW_VALUE current_instance NOPRINT; SELECT rpad(sys_context('USERENV', 'INSTANCE_NAME'), 17) current_instance FROM dual; SET TERMOUT ON; PROMPT PROMPT +------------------------------------------------------------------------+ PROMPT | Report : Oracle RAC Instances | PROMPT | Instance : ¤t_instance | PROMPT +------------------------------------------------------------------------+ SET ECHO OFF SET FEEDBACK 6 SET HEADING ON SET LINESIZE 180 SET PAGESIZE 50000 SET TERMOUT ON SET TIMING OFF SET TRIMOUT ON SET TRIMSPOOL ON SET VERIFY OFF CLEAR COLUMNS CLEAR BREAKS CLEAR COMPUTES COLUMN instance_name FORMAT a13 HEAD 'Instance|Name / Number' COLUMN thread# FORMAT 99999999 HEAD 'Thread #' COLUMN host_name FORMAT a28 HEAD 'Host|Name' COLUMN status FORMAT a6 HEAD 'Status' COLUMN startup_time FORMAT a20 HEAD 'Startup|Time' COLUMN database_status FORMAT a8 HEAD 'Database|Status' COLUMN archiver FORMAT a8 HEAD 'Archiver' COLUMN logins FORMAT a10 HEAD 'Logins?' COLUMN shutdown_pending FORMAT a8 HEAD 'Shutdown|Pending?' COLUMN active_state FORMAT a6 HEAD 'Active|State' COLUMN version HEAD 'Version' SELECT instance_name || ' (' || instance_number || ')' instance_name , thread# , host_name , status , TO_CHAR(startup_time, 'DD-MON-YYYY HH:MI:SS') startup_time , database_status , archiver , logins , shutdown_pending , active_state , version FROM gv$instance ORDER BY instance_number; ### SET TERMOUT OFF; COLUMN current_instance NEW_VALUE current_instance NOPRINT; SELECT rpad(instance_name, 17) current_instance FROM v$instance; SET TERMOUT ON; PROMPT PROMPT +------------------------------------------------------------------------+ PROMPT | Report : RMAN Registered Databases | PROMPT | Instance : ¤t_instance | PROMPT | Note : Listing of all databases in the RMAN recovery catalog. | PROMPT +------------------------------------------------------------------------+ SET ECHO OFF SET FEEDBACK 6 SET HEADING ON SET LINESIZE 180 SET PAGESIZE 50000 SET TERMOUT ON SET TIMING OFF SET TRIMOUT ON SET TRIMSPOOL ON SET VERIFY OFF CLEAR COLUMNS CLEAR BREAKS CLEAR COMPUTES COLUMN db_key FORMAT 999999 HEADING 'DB|Key' COLUMN dbinc_key FORMAT 999999 HEADING 'DB Inc|Key' COLUMN dbid HEADING 'DBID' COLUMN name FORMAT a12 HEADING 'Database|Name' COLUMN resetlogs_change_num HEADING 'Resetlogs|Change Num' COLUMN resetlogs FORMAT a21 HEADING 'Reset Logs|Date/Time' SELECT rd.db_key , rd.dbinc_key , rd.dbid , rd.name , rd.resetlogs_change# resetlogs_change_num , TO_CHAR(rd.resetlogs_time, 'DD-MON-YYYY HH24:MI:SS') resetlogs FROM rc_database rd ORDER BY rd.name /

Sunday, August 28, 2022

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(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_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 sys.props$ 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(a.program,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 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. 为这个表空间增加一个数据文件 alter tablespace 表空间名 add datafile '/u1/oradata/userdata_002.ora' size 50m; 　　 --Unix alter tablespace 表空间名 add datafile 'c:\oradata\userdata_002.ora' size 50m; 　　 --Windows NT 8. 重新调整数据文件的大小 alter database datafile '/u1/oradata/userdata_001.ora' resize 50M; 　 --Unix alter database datafile 'c:\oradata\userdata_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 extents>4 -- 空间碎片严重程度 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/dss@oralce query=\"where day_id > 12\" tables=(customer) file= d:\sample.dmp log= rows=n full=y tables=() owner= -- 三种导出方式 imp userid/pwd@oracle_sid fromuser =( dss,dwh) touser =(dss,dwh) dwh file=sample.dmp -- 分区表的到导出 user1.table_name:px 分区 px exp user_id/pwd@oracle_sid tables=(user1.table_name:px) file=sample.dmp 4. 如何使数据库运行于归档模式 1. 打开 ini.ora 文件 2. 修改文件内容 log_archive_start = true log_archive_dest_1 = "location=D:\Oracle\oradata\ORACLE\archive" 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_home\usr.ora d:\backup\user.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; SQL>alter 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 STARTUP[FORCE][RESTRICT][PFILE=filename][OPEN 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 s.prod_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 s.prod_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 PROFILE 名称删除一个新的尚未分配给用户的 PROFILE ， 2 、 DROP PROFILE 名称 CASCADE 3 、注意事项 A 、一旦 PROFILE 被删除，用户被自动加载 DEFAULT PROFILE B 、对于当前连接无影响 C 、 DEFAULT PROFILE 不可以被删除十一、信息获取： 1 、 DBA_USERS: 用户名， PROFILE 2 、 DBA_PROFILES: PROFILE 及各种限制参数的值每个用户的限制： PROFILE （关键字段）十二、 PROFILE 的口令机制限制 1 、限制内容 A 、限制连续多少次登录失败，用户被加锁 B 、限制口令的生命周期 C 、限制口令的使用间隔 2 、限制生效的前提： A RESOURCE_LIMIT:=TRUE B ORACLE\RDBMS\ADMIN\UTLPWDMG.SQL 3 、如何创建口令机制： CREATE PROFILE 名称 SESSIONS_PER_USER ..... password_life_time 30 failed_log_attempts 3 password_reuse_time 3 4 、参数的含义： A FAILED_LOGIN_ATTEMPTS: 当连续登陆失败次数达到该参数指定值时，用户加锁 B PASSWORD_LOCK_TIME: 加锁天数 C PASSWORD_LIFE_TIME: 口令的有效期（天） D PASSWORD_GRACE_TIME: 口令修改的间隔期（天） E PASSWORD_REUSE_TIME: 口令被修改后原有口令隔多少天被重新使用。 F PASSWORD_REUSE_MAX: 口令被修改后原有口令被修改多少次被重新使用。 22.ORACLE 的权限管理一授权的两种分类 : 1 SYSTEM 授权 : 允许特定的用户对特定的一类对象做特定的操作 . 可操作的对象 :TABLES,INDEXES,PROCEDURES,SEGMENTS; 系统授权是对某一类对象 . GRANT SELECT ANY TABLE TO ZT 2 OBJECT( 对象授权 ): A 允许一个特定的用户对一个特定的对象做特定的操作 . 对象 :TABLE,INDEX,SEGMENT,.. GRANT UPDATE ON EMP TO ZT 二系统授权的特点 1 ORACLE 中有超过 80 种以上的 SYSTEM 授权 2 几乎所有的 SYSTEM 授权包含 ANY 关键字 3 SYSTEM 授权 :GRANT 授权 4 SYSTEM 授权 :REVOKE 回收 5 允许用户做系统一级的操作 , 建表空间 , 建 SESSION. 6 最常用的系统授权 : INDEX:CREATE ANY INDEX ALTER ANY INDEX DROP ANY INDEX TABLE:CREATE ANY TABLE ALTER ......... DROP ......... SELECT ....... UPDATE......... SESSION:CREATE SESSION ALTER SESSION RESTRICTED SESSION TABLESPACE: CREATE TABLESPACE ALTER TABLESPACE DROP TABLESPACE 三如何授权 : 1 命令格式 GRANT CREATE SESSION,SELECT ANY TABLE TO [USERNAME|PUBLIC|ROLE] 2 带有转授权限 : A 转授 : 当 A 用户得到系统授权后 , 如果可以将这个权限授给别人 , 称转授 . GRANT CREATE SESSION,SELECT ANY TABLE TO [USERNAME|PUBLIC|ROLE] WITH ADMIN OPTION 举例 : 1 SYSTEM->ZT GRANT CREATE SESSION TO ZT WITH ADMIN OPTION ZT 用户可以拥有 CREATE SESSION 权限 2 ZT->OLM GRANT CREATE SESSION TO OLM, ZT,OLM 也有了 . 四 SYSDBA 和 SYSOPER 系统权限 1 SYSOPER:SYSTEM OPERATOR: 系统操作员 STARTUP,SHUTDOWN; ALTER DATABASE[MOUNT|OPEN]; RECOVER TABLESPACE BACKUP DATABASE ARCHIVELOG OR NOARCHIVELOG; 2 SYSDBA SYSTEM DABASE ADMIN: 数据库管理员 , SYSOPER WITH ADMIN OPTION RECOVER DATABASE CREATE DATABASE 五信息获取 DBA_SYS_PRIVS GRANTEE: 得到权限的人 PRIVILEGE: 得到何种权限 ADMIN OPTION: 是否可以转授想知道 SCOTT 有多少权限 SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE='SCOTT' 六回收权限 : REVOKE 权限 FROM [USERNAME|PUBLIC|ROLE] 举例 : SYSTEM->ZT(CREATE SESSION WITH ADMIN OPTION) ZT->OLM(CREATE SESSION) 七对象授权 TABLE: ALTER DELETE SELECT UPDATE INSERT INDEX:DELETE INSERT SELECT UPDATE CREATE, 对象是已经存在的事物 , 授权者是所有者 . 一 , 授权 : GRANT UPDATE(ENAME) ON EMP TO ZT WITH GRANT OPTION 二信息获取 1 DBA_TAB_PRIVS: GRANTEE: 得到者 GRANTOR: 授权者 PRIVILEGE: 权限 GRANTABLE: 是否可以转授 OWNER: 所有者三回收对象授权 1 REVOKE 权限 FROM 用户第十五章 : 角色管理一 , 角色及其特点 : 1, 角色实际上是若干权限的集合体 2,DBA 通过为应用中的不同用户 , 不同职责 , 定义不同的角色 , 可以达到减少工作量的目的 . 3, 角色的使用同授权一样 , 可以用 GRANT 授权 ,REVOKE 回收 . 4, 角色可以象开关一样打开关闭 5, 角色的使用可以提高性能 . 6, 角色的使用可以大大减少工作量二建立角色 1 CREATE ROLE 名 2 CREATE ROLE 名 IDENTIFIED BY 口令三修改角色 1 ALTER ROLE 名称 IDENTIFIED BY 口令将没有口令的加一个口令 2ALTER ROLE 名称 NOT IDENTIFIED 将有口令的变为没有口令三分配一个角色 GRANT ROLE 名 TO 用户名四回收一个角色 REVOKE ROLE 名 FROM 用户名五如何创建一个默认角色只有设为默认角色 , 才可以在用户登录时 , 使角色所含有的权限生效 1 ALTER USER SCOTT DEFAULT ROLE 角色 1, 角色 2 2 ATLER USR SCOTT DEFAULT ROLE ALL 3 ALTER USER SCOTT DEFAULT ROLE ALL EXCEPT 角色名 4 ALTER USER SCOTT DEFAULT ROLE NONE 六打开和关闭角色 : 1 SET ROLE 名称 2 SET ROLE IDENTIFIED BY 口令 ,( 建立角色时 , 带口令 ) 3 SET ROLE NONE 七删除角色 DROP ROLE 名称八信息获取 DBA_ROLE: DBA_ROLE_PRIVS: 九如何向角色里加权限 : GRANT 权限 TO 角色名 REVOKE 权限 FROM 角色名角色 :1 不能跟用户名重复 2 ROLE 的 ALTER 和其他对象的 ALTER 不一样 ,ROLE 的 ALTER 不能修改 ROLE 的内容 , 只可以修改角色的认证方式 23. 如何创建数据库连接 database link 如果服务器的初始化参数中 global_names 的值为 TRUE 那么就要求你创建的连接名称必须与 SID 相同，如果改成 FALSE 就可以按如下的方式创建了 create public database link link_name connect to user_name identified by passwd using ‘connecting_string’ 然后可以创建一个同义词为使用方便起见。 create synonyms synonyms_name for @ link_name 24. 回滚段的管理 1. 回滚段概述回滚段用于存放数据修改之前的值（包括数据修改之前的位置和值）。回滚段的头部包含正在使用的该回滚段事务的信息。一个事务只能使用一个回滚段来存放它的回滚信息，而一个回滚段可以存放多个事务的回滚信息。 2. 回滚段的作用事务回滚：当事务修改表中数据的时候，该数据修改前的值（即前影像）会存放在回滚段中，当用户回滚事务（ ROLLBACK ）时， ORACLE 将会利用回滚段中的数据前影像来将修改的数据恢复到原来的值。事务恢复：当事务正在处理的时候，例程失败，回滚段的信息保存在重做日志文件中， ORACLE 将在下次打开数据库时利用回滚来恢复未提交的数据。读一致性：当一个会话正在修改数据时，其他的会话将看不到该会话未提交的修改。而且，当一个语句正在执行时，该语句将看不到从该语句开始执行后的未提交的修改（语句级读一致性）。当 ORACLE 执行 SELECT 语句时， ORACLE 依照当前的系统改变号（ SYSTEMCHANGENUMBER-SCN ）来保证任何前于当前 SCN 的未提交的改变不被该语句处理。可以想象：当一个长时间的查询正在执行时，若其他会话改变了该查询要查询的某个数据块， ORACLE 将利用回滚段的数据前影像来构造一个读一致性视图。事务级的读一致性 ORACLE 一般提供 SQL 语句级（ SQLSTATEMENTLEVEL ）的读一致性，可以用以下语句来实现事务级的读一致性。 SET TRANSACTION READONLY ；或： SET TANNSACTION SERIALIZABLE ；以上两个语句都将在事务开始后提供读一致性。需要注意的是，使用第二个语句对数据库的并发性和性能将带来影响。 3. 回滚段的种类系统回滚段：当数据库创建后，将自动创建一个系统回滚段，该回滚段只用于存放系统表空间中对象的前影像。非系统回滚段：拥有多个表空间的数据库至少应该有一个非系统回滚段，用于存放非系统表空间中对象的数据前影像。非系统回滚段又分为私有回滚段和公有回滚段，私有回滚段应在参数文件的 ROLLBACKSEGMENTS 参数中列出，以便例程启动时自动使其在线（ ONLINE ）。公有回滚段一般在 OPS （ ORACLE 并行服务器）中出现，将在例程启动时自动在线。 DEFERED 回滚段：该回滚段在表空间离线（ OFFLINE ）时由系统自动创建，当表空间再次在线（ ONLINE ）时由系统自动删除，用于存放表空间离线时产生的回滚信息。 4. 回滚段的使用分配回滚段：当事务开始时， ORACLE 将为该事务分配回滚段，并将拥有最少事务的回滚段分配给该事务。事务可以用以下语句申请指定的回滚段： SET TRANSTRACTION USE ROLLBACK SEGMENT rollback_segment 事务将以顺序，循环的方式使用回滚段的区（ EXTENTS ），当当前区用满后移到下一个区。几个事务可以写在回滚段的同一个区，但每个回滚段的块只能包含一个事务的信息。例如（两个事务使用同一个回滚段，该回滚段有四个区）： 1 、事务在进行中，它们正在使用回滚段的第三个区； 2 、当两个事务产生更多的回滚信息，它们将继续使用第三个区； 3 、当第三个区满后，事务将写到第四个区，当事务开始写到一个新的区时，称为翻转（ WRAP ）； 4 、当第四个区用满时，如果第一个区是空闲或非活动（使用该区的所有事务完成而没有活动的事务）的，事务将接着使用第一个区。回滚段的扩张（ EXTEND ）当当前回滚段区的所有块用完而事务还需要更多的回滚空间时，回滚段的指针将移到下一个区。当最后一个区用完，指针将移到第一个区的前面。回滚段指针移到下一个区的前提是下一个区没有活动的事务，同时指针不能跨区。当下一个区正在使用时，事务将为回滚段分配一个新的区，这种分配称为回滚段的扩展。回滚段将一直扩展到该回滚段区的个数到达回滚段的参数 MAXEXTENTS 的值时为止。回滚段的回收和 OPTIMAL 参数 OPTIMAL 参数指明回滚段空闲时收缩到的位置，指明回滚段的 OPTIMAL 参数可以减少回滚段空间的浪费。 5. 创建回滚段语法： CREATE [PUBLIC] ROLLBACK SEGMENT rollback_segment [TABLESPACE tablespace] [STORAGE ([INITIAL integer[K|M]] [NEXT integer[K|M]] [MINEXTENTS integer] [MAXTENTS {integer|UNLIMITED}] [OPTIMAL {integer[K|M]|NULL}]) ] 注：回滚段可以在创建时指明 PRIVATE 或 PUBLIC ，一旦创建将不能修改。 MINEXTENTS 必须大于等于 2 PCTINCREASE 必须是 0 OPTIMAL 如果要指定，必须大于等于回滚段的初始大小（由 MINEXTENTS 指定）建议：一般情况下， INITIAL=NEXT 设置 OPTIMAL 参数来节约空间的使用不要设置 MAXEXTENTS 为 UNLIMITED 回滚段应创建在一个特定的回滚段表空间内 CREATE ROLLBACK SEGMENT rbs01 TABLESPACE rbs STORAGE ( INITIAL 100K NEXT 100K MINEXTENTS 10 MAXEXTENTS 500 OPTIMAL 1000K); 6. 回滚段的信息 DROP ROLLBACK SEGMENT rbs01; 查询回滚段的信息所用数据字典： DBA_ROLLBACK_SEGS 可以查询的信息：回滚段的标识 (SEGMENT_ID) 、名称 (SEGMENT_NAME) 、所在表空间 (TABLESPACE_NAME) 、类型 (OWNER) 、状态 (STATUS) 。例： SQL>SELECT segment_name,tablespace_name,owner,status FROM dba_rollback_segs; 回滚段的统计信息数据字典： V$ROLLNAME,V$ROLLSTAT SELECT n.name,s.extents,s.rssize,s.optsize,s.hwmsize,s.xacts,s.status FROM v$rollname n,v$rollstat s WHERE n.usn=s.usn; 回滚段的当前活动事务数据字典： V$SESSION,V$TRANSACTION SQL>SELECT s.username,t.xidusn,t.ubafil,t.ubablk,t.used_ublk FROM v$session s,v$transaction t WHERE s.saddr=t.ses_addr; 4 函数用法 1. 时间函数 select add_months(sysdate,2) from dual select last_day(sysdate) from dual select next_day(sysdate,'FRIDAY') from dual select months_between('19 MAY 95',add_months(sysdate,2)) duration from dual 2. 字符函数 select chr(67) from dual select concat('asf','af') / 列 1|| 列 2 from dual select initcap('sample') from dual select lower('SSD')/upper('ssd') from dual select lpad('myname',20,'*') from dual -- 左边填充指定长度的字符 select rpad('myname',20,'*') from dual select ltrim('myname','m') /rtrim('myname','m') from dual select replace('myname','yn','*') from dual select substr('myname',2,6) from dual select instr('myname','n',1,1) from dual select length('myname') from dual 3. 转换函数 select to_char(sysdate)||'Is good day!' from dual select to_number('23') from dual select least(34,567,3,45,1090) / greatest(34,567,3,45,1090) from dual select user from dual -- 查询当前用户 4.decode 函数 select decode(columnname,'1','first','2','second','other' ) from table_name -- 如果 columnname 等于 1 那么取 first 值，如果等于 2 取 second 否则等于 other 。实现逻辑控制 5. 连接的用法 select last_name, d.dept_id from employees e RIGHT OUTER JOIN departments d ON (e.department_id = d.department_id); 5 常见问题 1 ．字符集问题错误现象： 1 、有的时候，服务器数据先导出，重装服务器，再导入数据，结果，发生数据查询成 ??? 。 2 、有时，服务器设置就有问题，字符集设成单字节了。错误原因：一般，是因为字符集设置不对照成的。如果两台服务器上的字符设置不同就造成数据不能通讯，那么最简单的方法就是将字符集设置成一样的格式。解决方法： 1 、检查服务器上 Oracle 数据库的字符集 SQL> conn sys/change_on_install 连接成功 . SQL> desc props$ 列名可空值否类型 ------------------------------- -------- ---- NAME NOT NULL VARCHAR2(30) VALUE$ VARCHAR2(2000) COMMENT$ VARCHAR2(2000) SQL> col value$ format a40 SQL> select name,value$ from props$; NAME VALUE$ ------------------------------ ------------------------- DICT.BASE 2 NLS_LANGUAGE AMERICAN NLS_TERRITORY AMERICA NLS_CURRENCY $ NLS_ISO_CURRENCY AMERICA NLS_NUMERIC_CHARACTERS ., NLS_DATE_FORMAT DD-MON-YY NLS_DATE_LANGUAGE AMERICAN NLS_CHARACTERSET ZHS16GBK NLS_SORT BINARY NLS_CALENDAR GREGORIAN NLS_RDBMS_VERSION 7.3.4.0.0 GLOBAL_DB_NAME ORACLE.WORLD EXPORT_VIEWS_VERSION 3 查询到 14 记录 . NLS_CHARACTERSET 这个参数应该是 ZHS16GBK ，如不是，改为它。 SQL*Plus 中修改方法： SQL> update props$ set value$=' 新字符集 ' where name='NLS_CHARACTERSET'; 操作系统中修改方法： connect internal alter database ORCL character set ZHS16GBK; alter database ORCL national character set ZHS16GBK; 2 、检查操作系统级 Oracle 汉字显示的字符集运行 regedit ，定位到： HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE 找到以下字符串 NLS_LANG 检查是否以下内容，如不是，改之 SIMPLIFIED CHINESE_CHINA.ZHS16GBK 2．回滚段太旧问题 3 ．备份与恢复一：非归档备份执行 testsql1.txt 关闭系统，利用 oracle backup manager 备份数据打开数据库，执行 testsql2.txt 关闭数据库系统。删除 ts_test2 文件重新启动数据库时出现 1157,1110 错误，处理方法： 1 ：利用 oracle recovery manager 恢复数据。 2:alter database datafile 'd:\orant\database\ts_test2' offline drop; 然后打开数据库即可（丢失 ts_test2 文件内容）。注意： 1:alter database datafile 'd:\orant\database\ts_test2' offline 只能在 archivelog 模式下时才可使用。删除文件后，建议把表空间删除后重建。 2: 删除表空间 drop tablespace ts_test including contents; 3: 重建表空间二：丢失没有回滚段的非 SYSTEM 数据文件（在归档模式下运行） A ：使用 recover database 恢复，要求数据库安装但没有打开，即脱机备份 B ：使用 recover datafile 恢复，要求数据文件要脱机但数据库是打开的 C ：使用 recover tablespace 恢复，要求表空间脱机且数据库是打开的问题产生过程： svrmgrl connect internal insert into myinfo values(34,'myname34','no'); ....... commit; alter system switchlogfile; shutdown abort ( 删除 ts_test1 文件，拷贝旧的 ts_test1 文件 ) startup 启动时出现如下错误： ora-01113:file 2 needs media recovery ora-01110:data file2:d:\orant\database\ts_test1 恢复方法 A ： 1: startup mount 2: recover database; ( 提示恢复方式，选择 auto 即可 ) 3: alter database open 恢复方法 B ： 1:startup mount; 2: alter database archivelog; 3: alter database datafile 'd:\orant\database\ts_test1' offline 4: alter database open; 5: recover datafile 'd:\orant\database\ts_test1' ( 提示恢复方式，选择 auto 即可 ) 6: alter database datafile 'd:\orant\database\ts_test1' online 恢复方法 C ： 1: startup mount; 2: alter database archivelog; 3: alter database open; ( 提示出现 ora-01113,ora-1110 错误 ) 4: alter database datafile 'd:\orant\database\ts_tes1' offline; 5: alter database open; 6: alter tablespace ts_test offline; 7: recover tablespace ts_test; ( 提示恢复方式，选择 auto 即可 ) 8: alter tablespace ts_test online; 三：丢失有回滚段的非 SYSTEM 数据文件如果系统含有其他的回滚段，处理方法等同于非回滚段的处理方法，如果没有其他回滚段则需要先建立回滚段信息。产生过程： 1: svrmgr30 2: connect internal 3: create table test(s1 number) tablespace ts_test 4: select * from test; ( 结果为空 ) 5: set transaction use rollback segment tr_rbs; (tr_rbs 必须为 online) 6 ： shutdown 7: 删除 tr_rbs 对应的数据文件，然后拷贝旧的文件到当前目录下。 8 ：重新启动数据库时出现错误 ora-01113:file 2 needs media recovery ora-01110:data file2:d:\orant\database\ts_rbs 恢复方法： 1 ： svrmgr30 2: connect internal 3: startup mount 4: alter database datafile 'd:\orant\database\ts_rbs' offline; 5: alter database open; 6: create rollback segment temp1 tablespace system; 7: create rollback segment temp2 tablespace system; 8: alter rollback segment temp1 online; 9: alter rollback segment temp2 online; 10: select * from test; 错误信息 :ora-00376:file 3 cannot be read at this time ora_01110:data file d:\orant\database\ts_rbs 11: recover tablespace tr_rbs 12: select * from test; 错误信息：等同 10 13 ： alter tablespace ts_rbs online; 14: select * from test 提示信息：没有数据 15: alter rollabck segment tr_rbs online; 16: alter rollback segment temp1 offline; 17: alter rollback segment temp2 offline; 18: drop rollback segment temp1; 19: drop rollback segment temp2; 恢复方法也可采用文件恢复的方式进行，处理方法与情况 2 处理方法等同。四：丢失未归档的联机日志文件产生过程： 1 ： shutdown abort 2: 删除日志文件 ( 可从 v$logfile 得到日志文件信息 ) 3: 拷贝原日志文件信息恢复过程 : 1 ： startup mount 2: recover database until cancel; 3: alter database open resetlogs; 4: shutdown 说明： recover database until change 或 recover database until time 等方法恢复五：热备份期间的数据库崩溃产生过程： 1: connect internal 2: startup 3: archive log list( 处于归档状态并且自动归档为 enabled) 4: alter tablespace ts_test begin backup; 5: host ( 备份数据文件，如果有多个数据文件，所有数据文件都做备份 ) 6: create table test(c1 number) tablespace ts_test; 7: insert into test values(1); 8: commit; 9: alter system switch logfile; 10: shutdown abort( 用 shutdown 提示有备份文件，不许 shutdown) 完成以上工作后，用 startup 命令启动数据时出现 ora-01113 和 ora-01110 错误恢复方法 A: 1: startup mount 2: alter database datafile 'd:\orant\database\ts_test1' end backup; 3: alter database datafile 'd:\orant\database\ts_test2' end backup; 4: alter database open; 5: select * from test;( 可看到原来已经插入的数据，恢复完成 ) 恢复方法 B: 1: 删除数据文件 del d:\orant\database\ts_test1 与 ts_test2 2: 拷贝原来的数据文件 ( 即第 5 步拷贝的数据 ) 3: alter database datafile 'd:\orant\database\ts_test1' end backup; alter database datafile 'd:\orant\database\ts_test2' end backup; 4: recover database 5: alter database open; 6: select * from test;( 可看到原来已经插入的数据，恢复完成 ) 说明：当数据库打开时，出现 ora-01113 与 ora-01110 错误，无法判断是由于数据文件丢失或备份引起的错误，如果想查询是否处于热备份，可查看数据字典 select file#,status from v$backup 六：数据文件丢失产生过程： 1: connect internal 2: startup 3: archive log list( 处于归档状态并且自动归档为 enabled) 4: 删除一个数据文件 ( 表空间的一个文件 ), 假设为： d:\orant\database\ts_test3 恢复方法： 1: startup mount 2: alter database create datafile 'd:\orant\database\ts_test3' 3: recover datafile 'd:\orant\database\ts_test3' 4: alter database open; 5: select * from test;( 测试数据，恢复已完成 ) 4. 不能登陆 OEM （ Oracle 的企业管理器）这是需要首先创建一个资料库，你提供数据库的帐号，然后选择一个表空间。资料库创建完毕用工具登陆 OEM ，初始密码 sysman/oem_temp 提示你修改然后就 OK 了。 5. 提示凭证索引集失败将 Sqlnet.ora 文件中的验证方式屏蔽掉 6 参数配置 1. 查看基本信息显示数据库的有关信息 SELECT * from v$database 显示实例的信息 select * from v$instance 显示所有的参数设置 select * from v$parameter 显示版本信息 select * from v$version 在 SQLPLUS 下查看一些基本的参数 show parameter 参数名称 SELECT name FROM v$parameter WHERE name LIKE ‘%control%’; 2. 基本全局参数及含义 sga 系统全局区（ SGA ）是一个分配给 Oracle 的包含一个 Oracle 实例的数据库的控制信息内存段。　主要包括数据库高速缓存 (the database buffer cache) ，　重演日志缓存（ the redo log buffer ），　共享池（ the shared pool ），　数据字典缓存（ the data dictionary cache ）以及其它各方面的信息。 db_block_buffers 1 、数据高速缓冲区 2 、访问过的数据都放在这一片内存区域，该参数越大， Oracle 在内存中找到相同数据的可能性就越大，也即加快了查询速度。 3 、 db_block_buffers 以块为单位，假如 DB_BLOCK_SIZE=2K ， db_block_buffers=3200 ，则占用内存 =3200*2K=6400K 。 share_pool_size 1 、 SQL 共享缓冲池 2 、该参数是库高速缓存和数据字典的高速缓存。 log_buffer 1 、重演日志缓冲区 sort_area_size 1 、排序区 processes 1 、同时连接的进程数 global_names 1 、如果“数据库链路名”和它所要连接的“数据库”拥有相同的名字，则设置 global_names = TRUE ，否则，设置 global_names = FALSE db_block_size 1 、数据库块大小 2 、 Oracle 默认块为 2KB ，太小了，因为如果我们有一个 8KB 的数据，则 2KB 块的数据库要读 4 次盘，才能读完，而 8KB 块的数据库只要 1 次就读完了，大大减少了 I/O 操作。 3 、数据库安装完成后，就不能再改变 db_block_size 的值了，只能重新建立数据库并且建库时，要选择手工安装数据库。 open_links 1 、同时打开的链接数 dml_locks 1 、用户一次可对表设定锁的最大数目 2 、如果有三个用户修改６个表，则需１８个 DML 锁来实现并行操作，如果设定 DML_LOCKS 不够大，操作时执行将中断，你可以通过你的应用程序的操作规模和最大的并行使用的用户数来估算系统所需要的 DML_LOCKS 的值，但该值的大小对数据库的其他性能没有影响，所以一般都把它设得很大，远超过实际中可能达到的值，如果在系统运行中经常发生表锁死的现象，就应该考虑加大该值。 open_cursors 1 、打开光标数 2 、这个参数可以同时被每个用户进程拥有，这个参数的最大值依操作系统不同而不同。 3 、在创建一个包含很多表的合成视图时，如果此参数很小，则视图创建会不成功。 dbwr_io_slaves 1 、后台写进程数 7.启动或者停止Oracle 1 、 startup nomount 非安装启动，这种方式启动下可执行：重建控制文件、重建数据库读取 init.ora 文件，启动 instance ，即启动 SGA 和后台进程，这种启动只需要 init.ora 文件。 2 、 startup mount dbname 安装启动，这种方式启动下可执行：数据库日志归档、数据库介质恢复、使数据文件联机或脱机，重新定位数据文件、重做日志文件。执行“ nomount ”，然后打开控制文件，确认数据文件和联机日志文件的位置，但此时不对数据文件和日志文件进行校验检查。 3 、 startup open dbname 先执行“ nomount ”，然后执行“ mount ”，再打开包括 Redo log 文件在内的所有数据库文件，这种方式下可访问数据库中的数据。 4 、 startup ，等于以下三个命令 startup nomount alter database mount alter database open 5 、 startup restrict 约束方式启动这种方式能够启动数据库，但只允许具有一定特权的用户访问非特权用户访问时，会出现以下提示： ERROR ： ORA-01035: ORACLE 只允许具有 RESTRICTED SESSION 权限的用户使用 6 、 startup force 强制启动方式当不能关闭数据库时，可以用 startup force 来完成数据库的关闭先关闭数据库，再执行正常启动数据库命令 7 、 startup pfile= 参数文件名带初始化参数文件的启动方式先读取参数文件，再按参数文件中的设置启动数据库例： startup pfile=E:\Oracle\admin\oradb\pfile\init.ora 8 、 startup EXCLUSIVE 有三种关闭方式： 1 、 shutdown normal 正常方式关闭数据库。 2 、 shutdown immediate 立即方式关闭数据库。在 SVRMGRL 中执行 shutdown immediate ，数据库并不立即关闭，而是在 Oracle 执行某些清除工作后才关闭（终止会话、释放会话资源），当使用 shutdown 不能关闭数据库时， shutdown immediate 可以完成数据库关闭的操作。 3 、 shutdown abort 直接关闭数据库，正在访问数据库的会话会被突然终止，如果数据库中有大量操作正在执行，这时执行 shutdown abort 后，重新启动数据库需要很长时间。在 8i 中在命令行输入 svrmgrl 而在 9i 中是 dgmgrl ，然后 connect username/password 现在已经废除了 internal 的内部连接 8.DBA在UNIX下常用命令命令用法具体含义解释 man man command Manual Pages - Help with any UNIX command man ps Help on the UNIX ps command clear clear To clear the screen pwd pwd Present / Current Working Directory cd cd [directoryname] Change directory, without argument will change your working directory to your home directory. cd work Change working directory to "work" cd .. Change working directory to parent directory (.. is parent and . is current directory) ls ls [-options] [names] List files. [names] if omitted, will list all files and subdirectories in the directory. Wild cards can be specified. ls -l ls -al List files with date and permissions including hidden files ls -lt List files with date, sorted in the date modified ls -ltr bt* List files with date, sorted in the date modified, oldest first, with filenames starting with bt Wildcards * Any character, any number of positions ? Any character, one position [] A set of characters which match a single character position. - To specify a range within [] ls *x* List all files which contains an x in any position of the name. ls x* List all files which start with x ls *T0[1-3]ZZ List all files which contain T0 followed by 1,2 or 3 followed by ZZ. The following files match this condition: analyzeall.AAAT01ZZ dbaoc_err.AAAT03ZZ dbstart_log.AAAT03ZZ calerterr.AAAT01ZZ dbaoc_log.AAAT01ZZ ls job?.sql List files which start with job followed by any single character followed by .sql Example: jobd.sql jobr.sql ls alert*.???[0-1,9] alert_AAAT01ZZ.1019 alert_AAAD00ZZ.1020 alert_AAAI09ZZ.1021 touch - touch filename Create a 0 byte file or to change the timestamp of file to current time (wild cards as above can be used with the file names) mkdir mkdir directoryname Create Directory mkdir -p directorypath Create directory down many levels in single pass mkdir -p /home/biju/work/yday/tday rmdir rmdir directoryname Remove directory rm rm filename Remove file rm -rf directoryname Remove directory with files. Important - There is no way to undelete a file or directory in UNIX. So be careful in deleting files and directories. It is always good to have rm -i filename for deletes cp cp filename newfilename Copy a file cp -r * newloc To copy all files and subdirectories to a new location, use -r, the recursive flag. mv mv filename newfilename Rename (Move) a file. Rename filename to newfilename. mv filename directoryname Move filename under directoryname with the same file name. mv filename directoryname/newfilename Move filename to directoryname as newfilename. mv * destination If you use a wildcard in the filename, mv catenates all files to one sigle file, unless the destination is a directory. cp -i file1 file2 mv -i file1 file2 rm -i file* Use the -i flag with rm, mv and cp to confirm before destroying a file. file file filename To see what kind of file, whether editable. Executable files are binary and you should not open them. file d* dbshut: ascii textdbsnmp: PA-RISC1.1 shared executable dynamically linked -not strippeddbstart: ascii textdbv: PA-RISC1.1 shared executable dynamically linked -not strippeddemobld: commands textdemodrop: commands text vi vi filename Edit a text file. Vi is a very powerful and "difficult to understand" editor. But once you start using, you'll love it! All you want to know about vi are here. More vi tricks later!! cat cat filename See contents of a text file. cat (catenate) will list the whole file contents. Cat is mostly used to catenate two or more files to one file using the redirection operator. cat file1 file2 file3 > files Catenate the contents of file1, file2 and file3 to a single file called files. If you do not use the redirection, the result will be shown on the standard output, i.e., screen. more page more filename page filename Show the contents of the file, one page at a time. In more/page, use space to see next page and ENTER to see next line. If you wish to edit the file (using vi), press v; to quit press q. tail tail -n filename To see the specified number of lines from the end of the file. head head -n filename To see the specified number of lines from the top of the file. pg pg filename To show the contents of the file, page by page. In pg, you go up and down the pages with + and - and numbers. 1 First Page of the file $ Last Page of the file+5 Skip 5 pages-6 Go back 6 pagesENTER Next page- Previous Pageq Quit/string Search for string env env To see value of all environment variables. To set an environment variable: In ksh or sh "export VARIABLENAME=value", Note there is no space between =.In csh "setenv VARIABLENAME value" echo $VARIABLENAME See value of an environment variable echo echo string To print the string to standard output echo "Oracle SID is $ORACLE_SID" Will display "Oracle SID is ORCL" if the value of ORACLE_SID is ORCL. lp lp filename To print a file to system default printer. chmod chmod permission filename Change the permissions on a file - As explained under ls -l, the permissions are read, write, execute for owner, group and others. You can change permissions by using numbers or the characters r,w,x. Basically, you arrive at numbers using the binary format. Examples:rwx = 111 = 7rw_ = 110 = 6r__ = 100 = 4r_x = 101 = 5 chmod +rwx filename chmod 777 filename Give all permissions to everyone on filename chmod u+rwx,g+rx,o-rwx filename chmod 750 filename Read, write, execute for owner, read and execute for group and no permission for others chown chown newuser filename Change owner of a file chgrp chgrp newgroup filename Change group of a file chown newuser:newgroup filename Change owner and group of file compress compress filename Compress a file - compressed files have extention .Z. To compress file you need to have enough space to hold the temporary file. uncompress uncompress filename Uncompress a file df df [options] [moutpoint] Freespace available on the system (Disk Free); without arguments will list all the mount points. df -k /ora0 Freespace available on /ora0 in Kilobytes. On HP-UX, you can use "bdf /ora0". df -k . If you're not sure of the mount point name, go to the directory where you want to see the freespace and issue this command, where "." indicates current directory. du du [-s] [directoryname] Disk used; gives operating system blocks used by each subdirectory. To convert to KB, for 512K OS blocks, divide the number by 2. du -s gives the summary, no listing for subdirectories find Find files. find is a very useful command, searches recursively through the directory tree looking for files that match a logical expression. It has may options and is very powerful. find /ora0/admin -name "*log" -print Simple use of find - to list all files whose name end in log under /ora0/admin and its subdirectories find . -name "*log" -print -exec rm {} \; to delete files whose name end in log. If you do not use the "-print" flag, the file names will not be listed on the screen. grep Global regular expression print to search for an expression in a file or group of files. grep has two flavours egrep (extented - expands wild card characters in the expression) and frep (fixed-string - does not expand wild card characters). This is a very useful command, especially to use in scripts. grep oracle /etc/passwd to display the lines containing "oracle" from /etc/passwd file. grep -i -l EMP_TAB *.sql to display only the file names (-l option) which contains the string EMP_TAB, ignore case for the string (-i option), in all files with sql extention. grep -v '^#' /etc/oratab display only the lines in /etc/oratab where the lines do not (-v option; negation) start with # character (^ is a special character indicating beginning of line, similarly $ is end of line). ftp ftp [hostname] File Transfer Protocol - to copy file from one computer to another ftp AAAd01hp invoke ftp, connect to server AAAd01hp. 331 Password required for BIJU.Password: enter password - will not be echoed. 230 User BIJU logged in.Remote system type is UNIX.Using binary mode to transfer files.ftp> ascii Specifying to use ASCII mode to transfer files. This is used to transfer text files. 200 Type set to A.ftp> binary Specifying to use binary mode to transfer files. This is used for program and your export dump files. 200 Type set to I.ftp> ls To see the files in the remote computer. 200 PORT command successful.150 Opening ASCII mode data connection for /usr/bin/ls.total 8-rw-rw-rw- 1 b2t dba 43 Sep 22 16:01 afiedt.bufdrwxrwxrwx 2 b2t dba 96 Jul 9 08:47 appdrwxrwxrwx 2 b2t dba 96 Jul 9 08:49 bin-rw-rw-rw- 1 b2t dba 187 Jul 30 14:44 check.sql226 Transfer complete.ftp> get check.sql transfer the file check.sql from the remote computer to the local computer. The file will be copied to the present directory with the same name. You can optionally specify a new name and directory location. 200 PORT command successful.150 Opening BINARY mode data connection for check.sql (187 bytes).226 Transfer complete.187 bytes received in 0.02 seconds (7.79 Kbytes/s)ftp> !ls ! runs commands on the local machine. AAAP02SN a4m08.txt tom3.txta4m01.txtftp> put a4m01.txt /tmp/test.txt transfer file from local machine to remote machine, under /tmp directory with name test.txt. mail mail "xyz@abc.com" < message.log Mail a file to internet/intranet address. mail the contents of message.log file to xyz@abc.com mail -s "Messages from Me" "xyz@abc.com" "abc@xyz.com" < message.log mail the contents of message.log to xyz and abc with a subject. who who [options] to see who is logged in to the computer. who -T Shows the IP address of each connection who -r Shows when the computer was last rebooted, run-level. ps ps process status - to list the process id, parent process, status etc. ps without any arguments will list current sessions processes. ps -f ull listing of my processes, with time, terminal id, parent id, etc. ps -ef as above for all the processes on the server. kill kill [-flag] processid to kill a process - process id is obtained from the ps command or using the v$process table in oracle. kill 12345 Kill the process with id 12345 kill -9 12345 To force termination of process id 12345 script script logfilename to record all your commands and output to a file. Mostly useful if you want to log what you did, and sent to customer support for them to debug. start logging to the logfilename. The logging is stopped when you do "exit". hostname hostname displays the name of the computer. uname uname -a to see the name of the computer along with Operating system version and license info. date date displays the current date and time. date "+%m%d%Y" displays date in MM/DD/YYYY format cal cal displays calender of current month cal 01 1991 Displays January 1991 Calender telnet telnet [hostname] to open a connection to another computer in the network. Provide the alias name or IP address of the computer. & command & add & to the end of the command to run in background nohup command & no hangup - do not terminate the background job even if the shell terminates. fg fg to bring a background job to foreground bg bg to take a job to the background. Before issuing this command, press ^Z, to suspend the process and then use bg, to put it in the background. jobs jobs to list the current jobs in the shell. rcp rcp [-r] sourcehost:filename destinationhost:filename Remote copy. Copy files from one coputer to another. To set up the computer for remote copy and remote login (rlogin) will be discussed later. rcp host1:/ora0/file1.txt host2:/ora0/temp/file1.txt Copy file from host1 to host2. If the computer name is omitted, the hostname is assumed. 分类: Database Oracle

vi /scripts/check/xunjian.sql set heading off select '一、数据库的基本情况' from dual; set heading off select '1、数据库版本' from dual; set heading on select * from v$version; set heading off select '2、查看数据库基本信息' from dual; set heading on set linesize 500 col host_name for a20 select dbid,name,instance_name,instance_name,version,parallel rac,host_name from v$database,v$instance; set heading off select '3、实例状态' from dual; set heading on select instance_number,instance_name ,status from gv$instance; set heading off select '4、数据库运行时间' from dual; set heading on select to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') 启动时间, TRUNC(sysdate - (startup_time))||'天 '||TRUNC(24*((sysdate-startup_time) -TRUNC(sysdate-startup_time))) ||'小时 '||MOD(TRUNC(1440*((SYSDATE-startup_time)- TRUNC(sysdate-startup_time))),60) ||'分 '||MOD(TRUNC(86400*((SYSDATE-STARTUP_TIME)- TRUNC(SYSDATE-startup_time))),60) ||'秒' 运行时间 from v$instance; set heading off select '5、内存情况' from dual; set heading on select * from v$sgainfo; set heading off select '6、cpu情况' from dual; set heading on col STAT_NAME for a20 col COMMENTS for a50 Select stat_name,value,comments from v$osstat where stat_name in ('NUM_CPUS','IDLE_TIME','BUSY_TIME','USER_TIME','SYS_TIME','IOWAIT_TIME'); set heading off select '二、检查Oracle对象状态' from dual; set heading off select '1、查看参数文件位置' from dual; show parameter spfile set heading off col NAME for a50 select '2、查看控制文件' from dual; set heading on select status,name from v$controlfile; set heading off select '3、查看在线日志' from dual; set heading on col MEMBER for a50 select group#,status,type,member from v$logfile; set heading off select '4、检查日志切换频率' from dual; set heading on select sequence#,to_char(first_time,'yyyymmdd_hh24:mi:ss') firsttime,round((first_time-lag(first_time) over(order by first_time))*24*60,2) minutes from v$log_history where first_time > sysdate - 1 order by first_time ,minutes; set heading off select '5、查看数据文件' from dual; set heading on col NAME for a50 select name,status from v$datafile; set heading off select '6、查看无效的对象' from dual; set heading on set linesize 500 select owner,object_name,object_type from dba_objects where status!='VALID' and owner!='SYS' and owner!='SYSTEM'; set heading off select '7、查看回滚段状态' from dual; set heading on select segment_name,status from dba_rollback_segs; set heading off select '8、检查是否有禁用约束' from dual; set heading on set linesize 1000 SELECT owner, constraint_name, table_name, constraint_type, status FROM dba_constraints WHERE status ='DISABLE' and constraint_type='P'; set heading off select '9、检查是否有禁用触发器' from dual; set heading on col owner for a10 col taigger_name for a10 col table_name for a30 col table_name for a30 SELECT owner, trigger_name, table_name, status FROM dba_triggers WHERE status = 'DISABLED'; set heading off select '10、Oracle Job是否有失败' from dual; set heading on select job,what,last_date,next_date,failures,broken from dba_jobs Where schema_user='CAIKE'; set heading off select '11、检查失效的索引' from dual; set heading on select index_name,table_name,tablespace_name,status From dba_indexes Where owner='CTAIS2' And status<>'VALID'; set heading off select '三、检查Oracle相关资源的使用情况' from dual; set heading off select '1、查看表空间的使用情况' from dual; set heading on set linesize 1000 SELECT UPPER(F.TABLESPACE_NAME) "tablespace_name", D.TOT_GROOTTE_MB "tablesapce_size(M)", D.TOT_GROOTTE_MB - F.TOTAL_BYTES "used_tablespace_size(M)", TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990.99') "used%", F.TOTAL_BYTES "free_size(M)", F.MAX_BYTES "max_byte(M)" FROM (SELECT TABLESPACE_NAME, ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES, ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES FROM SYS.DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F, (SELECT DD.TABLESPACE_NAME, ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB FROM SYS.DBA_DATA_FILES DD GROUP BY DD.TABLESPACE_NAME) D WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME ORDER BY 4 DESC; set heading off select '2、查看临时表空间使用情况' from dual; set heading on select tablespace_name , sum(bytes)/1024/1024 from dba_temp_files group by tablespace_name; set heading off select '3、查看临时段使用的情况' from dual; set heading on COL username FORMAT a10; COL segtype FORMAT a10; SELECT username, segtype, extents "Extents Allocated" ,blocks "Blocks Allocated" FROM v$tempseg_usage; set heading off select '4、查看所有数据文件i/o情况' from dual; set heading on SELECT ts.name AS ts, fs.phyrds "Reads", fs.phywrts "Writes" ,fs.phyblkrd AS br, fs.phyblkwrt AS bw ,fs.readtim "RTime", fs.writetim "WTime" FROM v$tablespace ts, v$datafile df, v$filestat fs WHERE ts.ts# = df.ts# AND df.file# = fs.file# UNION SELECT ts.name AS ts, ts.phyrds "Reads", ts.phywrts "Writes" ,ts.phyblkrd AS br, ts.phyblkwrt AS bw ,ts.readtim "RTime", ts.writetim "WTime" FROM v$tablespace ts, v$tempfile tf, v$tempstat ts WHERE ts.ts# = tf.ts# AND tf.file# = ts.file# ORDER BY 1; set heading off select '5、查看top 10 热segment' from dual; set heading on col objct_name for a30 col OWNER for a20 select * from (select ob.owner, ob.object_name, sum(b.tch) Touchs from x$bh b , dba_objects ob where b.obj = ob.data_object_id and b.ts# > 0 group by ob.owner, ob.object_name order by sum(tch) desc) where rownum <=10; set heading off select '6、查看物理读最多的object' from dual; set heading on select * from (select owner,object_name,value from v$segment_statistics where statistic_name='physical reads' order by value desc) where rownum<=10; set heading off select '7、查看热点数据文件(从单块读取时间判断)' from dual; set heading on SELECT t.file_name, t.tablespace_name, round(s.singleblkrdtim / s.singleblkrds, 2) AS CS, s.READTIM, s.WRITETIM FROM v$filestat s, dba_data_files t WHERE s.file# = t.file_id and rownum<=10 order by cs desc; set heading off select '8、检查Oracle初始化文件中相关参数值' from dual; set heading on select resource_name,max_utilization,initial_allocation, limit_value from v$resource_limit; set heading off select '注：若LIMIT_VALU - MAX_UTILIZATION<=5，则表明与RESOURCE_NAME相关的Oracle初始化参数需要调整。可以通过参数文件调整。' from dual; set heading off select '9、检查数据库连接情况' from dual; set heading on select sid,serial#,username,program,machine,status from v$session; set heading off select "(注：杀掉会话的语句alter system kill session 'SID,SERIAL#')" from dual; set heading off select '10、查看热点数据文件' from dual; set heading on SELECT t.file_name, t.tablespace_name, round(s.singleblkrdtim / s.singleblkrds, 2) AS CS, s.READTIM, s.WRITETIM FROM v$filestat s, dba_data_files t WHERE s.file# = t.file_id and rownum<=10 order by cs desc; set heading off select ' 11、检查一些扩展异常的对象 ' from dual; set heading on select Segment_Name, Segment_Type, TableSpace_Name, (Extents/Max_extents)*100 Percent From sys.DBA_Segments Where Max_Extents != 0 and (Extents/Max_extents)*100>=95 order By Percent; set heading off select ' 12、检查system表空间内的内容 ' from dual; set heading on select distinct(owner) from dba_tables where tablespace_name='SYSTEM' and owner!='SYS' and owner!='SYSTEM' union select distinct(owner) from dba_indexes where tablespace_name='SYSTEM' and owner!='SYS' and owner!='SYSTEM'; set heading off select ' 13、检查对象的下一扩展与表空间的最大扩展值 ' from dual; set heading on select a.table_name, a.next_extent, a.tablespace_name from all_tables a, (select tablespace_name, max(bytes) as big_chunk from dba_free_space group by tablespace_name ) f where f.tablespace_name = a.tablespace_name and a.next_extent > f.big_chunk union select a.index_name, a.next_extent, a.tablespace_name from all_indexes a, (select tablespace_name, max(bytes) as big_chunk from dba_free_space group by tablespace_name ) f where f.tablespace_name = a.tablespace_name and a.next_extent > f.big_chunk; set heading off select '四、内存的具体查看' from dual; set heading off select ' 1、查看内存占用各个池子大小' from dual; set heading on COL name FORMAT a32; SELECT pool, name, bytes FROM v$sgastat WHERE pool IS NULL OR pool != 'shared pool' OR (pool = 'shared pool' AND (name IN('dictionary cache','enqueue','library cache','parameters', 'processes','sessions','free memory'))) ORDER BY pool DESC NULLS FIRST, name; set heading off select ' 2、检查shered pool free space ' from dual; set heading on SELECT * FROM V$SGASTAT WHERE NAME = 'free memory' AND POOL = 'shared pool'; set heading off select ' 3、检查shared pool中library cach ' from dual; set heading on select namespace,pinhitratio from v$librarycache; set heading off select ' 4、检查整体命中率(library cache)' from dual; set heading on select sum(pins) "hits", sum(reloads) "misses", sum(pins)/(sum(pins)+sum(reloads)) "Hits Ratio" from v$librarycache; set heading off select ' 5、library cache中详细比率信息' from dual; set heading on SELECT 'Library Lock Requests' "Ratio" , ROUND(AVG(gethitratio) * 100, 2) ||'%' "Percentage" FROM V$LIBRARYCACHE UNION SELECT 'Library Pin Requests' "Ratio", ROUND(AVG(pinhitratio) * 100, 2) ||'%' "Percentage" FROM V$LIBRARYCACHE UNION SELECT 'Library I/O Reloads' "Ratio" , ROUND((SUM(reloads) / SUM(pins)) * 100, 2) ||'%' "Percentage" FROM V$LIBRARYCACHE UNION SELECT 'Library Reparses' "Ratio" , ROUND((SUM(reloads) / SUM(pins)) * 100, 2) ||'%' "Percentage" FROM V$LIBRARYCACHE; set heading off select ' 6、检查数据字典的命中率' from dual; set heading on SELECT (SUM(GETS - GETMISSES - FIXED)) / SUM(GETS) "ROW CACHE" FROM V$ROWCACHE; set heading off select '注：row cache的命中率至少小于90%' from dual; set heading off select ' 7、每个子shared pool由单独的shared pool latch保护查看他们的命中率 ' from dual; set heading on col name format a15 select addr,name,gets,misses,1-misses/gets from v$latch_children where name='shared pool'; set heading off select ' 8、查看shared pool建议' from dual; set heading on column c1 heading 'Pool |Size(M)' column c2 heading 'Size|Factor' column c3 heading 'Est|LC(M) ' column c4 heading 'Est LC|Mem. Obj.' column c5 heading 'Est|Time|Saved|(sec)' column c6 heading 'Est|Parse|Saved|Factor' column c7 heading 'Est|Object Hits' format 999,999,999 SELECT shared_pool_size_for_estimate c1,shared_pool_size_factor c2, estd_lc_size c3,estd_lc_memory_objects c4,estd_lc_time_saved c5, estd_lc_time_saved_factor c6,to_char(estd_lc_memory_object_hits,99999999999) c7 FROM V$SHARED_POOL_ADVICE; set heading off select ' 9、查看shared pool中各种类型的chunk的大小数量' from dual; set heading on SELECT KSMCHCLS CLASS, COUNT(KSMCHCLS) NUM, SUM(KSMCHSIZ) SIZ, To_char( ((SUM(KSMCHSIZ)/COUNT(KSMCHCLS)/1024)),'999,999.00')||'k' "AVG SIzE" FROM X$KSMSP GROUP BY KSMCHCLS; set heading off select ' 10、查看使用shard_pool保留池情况' from dual; set heading on SELECT request_misses, request_failures, free_space FROM v$shared_pool_reserved; set heading off select '11、 pga 建议' from dual; set heading on SELECT (SELECT ROUND(value/1024/1024,0) FROM v$parameter WHERE name = 'pga_aggregate_target') "Current Mb" , ROUND(pga_target_for_estimate/1024/1024,0) "Projected Mb" , ROUND(estd_pga_cache_hit_percentage) "%" FROM v$pga_target_advice ORDER BY 2; set heading off select ' 12、查看buffer cache 命中率' from dual; set heading on select 1-(sum(decode(name, 'physical reads', value, 0))/ (sum(decode(name, 'db block gets', value, 0))+ (sum(decode(name, 'consistent gets', value, 0))))) "Buffer Hit Ratio" from v$sysstat; set heading off select ' 13、查看buffer cache设置大小建议' from dual; set heading on select size_for_estimate, estd_physical_read_factor, to_char(estd_physical_reads,99999999999999999999999) as"estd_physical_reads" from v$db_cache_advice where name = 'DEFAULT'; set heading off select '14、查看buffer cache中defalut pool 命中率' from dual; set heading on select name,1-(physical_reads)/(consistent_gets+db_block_gets) from v$buffer_pool_statistics; set heading off select '注：default池命中率至少要大于90%' from dual; set heading off select '15、检查lgwr i/o性能' from dual; set heading on select total_waits,time_waited,average_wait,time_waited/total_waits as avg from v$system_event where event = 'log file parallel write'; set heading off select '16、检查与redo相关性能指标' from dual; set heading on set linesize 500 select name,value from v$sysstat where name like '%redo%'; set heading off select ' 17、查询redo block size' from dual; set heading on select max(lebsz) from x$kccle; set heading off select '18、计算出每个事务平均处理多少个redo block' from dual; set heading on select a.redoblocks/b.trancount from (select value redoblocks from v$sysstat where name='redo blocks written') a ,(select value trancount from v$sysstat where name='user commits') b; set heading off select ' 19、检查undo rollback segment 使用情况' from dual; set heading on col name for a60 select name ,rssize,extents,latch,xacts,writes,gets,waits from v$rollstat a,v$rollname b where a.usn=b.usn order by waits desc; set heading off select ' 20、计算每秒钟产生的undoblk数量' from dual; set heading on select sum(undoblks)/sum((end_time-begin_time)*24*60*60) from v$undostat; set heading off select ' 21、查询undo具体信息' from dual; set heading on COL undob FORMAT 99990; COL trans FORMAT 99990; COL snapshot2old FORMAT 9999999990; SELECT undoblks "UndoB", txncount "Trans" ,maxquerylen "LongestQuery", maxconcurrency "MaxConcurrency" ,ssolderrcnt "Snapshot2Old", nospaceerrcnt "FreeSpaceWait" FROM v$undostat; set heading off select ' 22、查询rollback 段详细信息(收缩次数,扩展次数,平均活动事务等)' from dual; set heading on COL RBS FORMAT a50; SELECT n.name "RBS", s.extends "Extends", s.shrinks "Shrinks" ,s.wraps "Wraps", s.aveshrink "AveShrink" ,s.aveactive "AveActive" FROM v$rollname n JOIN v$rollstat s USING(usn) WHERE n.name != 'SYSTEM'; set heading off select ' 23、查询当前rollback segment使用情况' from dual; set heading on COL RBS FORMAT a50; SELECT n.name "RBS", s.status, s.waits, s.gets, to_char(s.writes,'9999999999999') ,s.xacts "Active Trans" FROM v$rollname n JOIN v$rollstat s USING(usn) WHERE n.name != 'SYSTEM'; set heading off select '24、查询使用rollback segment时等待比率' from dual; set heading on SELECT ROUND(SUM(waits/gets)*100,2)||'%' "Contention" FROM v$rollstat; set heading off select '25、查询使用rollback segment时等待比率及其平局活动事务数' from dual; set heading on COL contention FORMAT 9999999990; SELECT AVG(xacts) "Trans per RBS" ,ROUND(SUM(waits/gets)*100,2)||'%' "Contention" FROM v$rollstat; set heading off select '五、检查Oracle数据库性能' from dual; set heading off select '1、检查数据库的等待事件' from dual; set heading on set pages 80 set lines 120 col event for a40 select sid,event,p1,p2,p3,WAIT_TIME,SECONDS_IN_WAIT from v$session_wait where event not like 'SQL%' and event not like 'rdbms%'; set heading off select '2、查看与redo相关等待事件' from dual; set heading on col event format a40 select event,total_waits,total_timeouts,average_wait from v$system_event where upper(event) like'%REDO%'; set heading off select '3、查看session redo event' from dual; set heading on select event,total_waits,total_timeouts,average_wait from v$session_event where upper(event) like'%REDO%'; set heading off select '4、Disk Read最高的SQL语句的获取' from dual; set heading on SELECT SQL_TEXT FROM (SELECT * FROM V$SQLAREA ORDER BY DISK_READS) WHERE ROWNUM<=5 order by SQL_TEXT desc set heading off select '5、查找前十条性能差的sql' from dual; set heading on SELECT * FROM (SELECT PARSING_USER_ID EXECUTIONS,SORTS,COMMAND_TYPE,DISK_READS, SQL_TEXT FROM V$SQLAREA ORDER BY DISK_READS DESC) WHERE ROWNUM<10 ; set heading off select '6、等待时间最多的5个系统等待事件的获取' from dual; set heading on SELECT * FROM (SELECT * FROM V$SYSTEM_EVENT WHERE EVENT NOT LIKE 'SQL%' ORDER BY TOTAL_WAITS DESC) WHERE ROWNUM<=5; set heading off select '7、检查运行很久的SQL' from dual; set heading on COLUMN USERNAME FORMAT A12 COLUMN OPNAME FORMAT A16 COLUMN PROGRESS FORMAT A8 SELECT USERNAME,SID,OPNAME,ROUND(SOFAR*100 / TOTALWORK,0) || '%' AS PROGRESS,TIME_REMAINING,SQL_TEXT FROM V$SESSION_LONGOPS , V$SQL WHERE TIME_REMAINING <> 0 AND SQL_ADDRESS=ADDRESS AND SQL_HASH_VALUE = HASH_VALUE; set heading off select '9、检查碎片程度高的表' from dual; set heading on 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); set heading off select '10、检查死锁及处理' from dual; set heading on col sid for 999999 col username for a10 col schemaname for a10 col osuser for a16 col machine for a16 col terminal for a20 col owner for a10 col object_name for a30 col object_type for a10 select sid,serial#,username,SCHEMANAME,osuser,MACHINE, terminal,PROGRAM,owner,object_name,object_type,o.object_id from dba_objects o,v$locked_object l,v$session s where o.object_id=l.object_id and s.sid=l.session_id; set heading off select '11、查看数据库中行chain' from dual; set heading on SELECT 'Chained Rows ' "Ratio" , ROUND( (SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'table fetch continued row') / (SELECT SUM(value) FROM V$SYSSTAT WHERE name IN ('table scan rows gotten', 'table fetch by rowid')) * 100, 3)||'%' "Percentage" FROM DUAL; set heading off select '12、查询解析比率' from dual; set heading on SELECT 'Soft Parses ' "Ratio" , ROUND( ((SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'parse count (total)') - (SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'parse count (hard)')) / (SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'execute count') * 100, 2)||'%' "Percentage" FROM DUAL UNION SELECT 'Hard Parses ' "Ratio" , ROUND((SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'parse count (hard)') / (SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'execute count') * 100, 2)||'%' "Percentage" FROM DUAL UNION SELECT 'Parse Failures ' "Ratio" , ROUND((SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'parse count (failures)') / (SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'parse count (total)')* 100, 5)||'%' "Percentage" FROM DUAL; set heading off select '13、查看与latch有关的event信息' from dual; set heading on COL event FORMAT a20; COL waits FORMAT 9999990; COL timeouts FORMAT 99999990; COL average FORMAT 99999990; SELECT event "Event", time_waited "Total Time", total_waits "Waits" ,average_wait "Average", total_timeouts "Timeouts" FROM V$SYSTEM_EVENT WHERE event = 'latch free' ORDER BY EVENT; set heading off select '14、查看大表小表扫描对应的值' from dual; set heading on SELECT value, name FROM V$SYSSTAT WHERE name IN ('table fetch by rowid', 'table scans (short tables)' , 'table scans (long tables)'); SELECT 'Short to Long Full Table Scans' "Ratio" , ROUND( (SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'table scans (short tables)') / (SELECT SUM(value) FROM V$SYSSTAT WHERE name IN ('table scans (short tables)', 'table scans (long tables)')) * 100, 2)||'%' "Percentage" FROM DUAL UNION SELECT 'Short Table Scans ' "Ratio" , ROUND( (SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'table scans (short tables)') / (SELECT SUM(value) FROM V$SYSSTAT WHERE name IN ('table scans (short tables)', 'table scans (long tables)', 'table fetch by rowid')) * 100, 2)||'%' "Percentage" FROM DUAL UNION SELECT 'Long Table Scans ' "Ratio" , ROUND( (SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'table scans (long tables)') / (SELECT SUM(value) FROM V$SYSSTAT WHERE name IN ('table scans (short tables)', 'table scans (long tables)', 'table fetch by rowid')) * 100, 2)||'%' "Percentage" FROM DUAL UNION SELECT 'Table by Index ' "Ratio" , ROUND( (SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'table fetch by rowid') / (SELECT SUM(value) FROM V$SYSSTAT WHERE name IN ('table scans (short tables)', 'table scans (long tables)' , 'table fetch by rowid')) * 100, 2)||'%' "Percentage" FROM DUAL UNION SELECT 'Efficient Table Access ' "Ratio" , ROUND( (SELECT SUM(value) FROM V$SYSSTAT WHERE name IN ('table scans (short tables)','table fetch by rowid')) / (SELECT SUM(value) FROM V$SYSSTAT WHERE name IN ('table scans (short tables)', 'table scans (long tables)' , 'table fetch by rowid')) * 100, 2)||'%' "Percentage" FROM DUAL; set heading off select '15、index使用比率' from dual; set heading on col name for a30 SELECT to_char(value,'999999999999999999999'), name FROM V$SYSSTAT WHERE name IN ('table fetch by rowid', 'table scans (short tables)' , 'table scans (long tables)') OR name LIKE 'index fast full%' OR name = 'index fetch by key'; SELECT 'Index to Table Ratio ' "Ratio" , ROUND( (SELECT SUM(value) FROM V$SYSSTAT WHERE name LIKE 'index fast full%' OR name = 'index fetch by key' OR name = 'table fetch by rowid') / (SELECT SUM(value) FROM V$SYSSTAT WHERE name IN ('table scans (short tables)', 'table scans (long tables)') ),0)||':1' "Result" FROM DUAL; set heading off select '16、等待class' from dual; set heading on col wait_class for a30 SELECT wait_class, COUNT(wait_class) FROM v$system_event GROUP BY wait_class ORDER BY 1; vi xunjian.sh sqlplus / as sysdba <

free -m Oracle Recommends to have 8 GB Physical Memory for each node It is necessary to have at least 3GB for SGA to correctly upgrade (and runs) the GI 19c version grid@exadb01]$ sqlplus / as sysasm SQL> alter system set sga_max_size = 3G scope=spfile sid='*'; SQL> alter system set sga_target = 3G scope=spfile sid='*'; SQL> alter system set memory_target=0 sid='*' scope=spfile; SQL> alter system set memory_max_target=0 sid='*' scope=spfile /* required workaround */; SQL> alter system reset memory_max_target sid='*' scope=spfile; SQL> alter system set use_large_pages=true sid='*' scope=spfile /* 11.2.0.2 and later(Linux only) */; crsctl query crs activeversion [oracle@ol7-121-rac1 ~]$ crsctl query crs softwareversion Oracle Clusterware version on node [ol7-121-rac1] is [12.1.0.2.0] 19.3.0/ /u02/app/19.3.0/gri mkdir -p /u01/app/19.3.0/grid chown -R grid:oinstall /u01/app/19.3.0/grid unzip -q /media/sf_software/LINUX.X64_193000_grid_home.zip unzip -q /tmp/LINUX.X64_193000_grid_home.zip -d /u01/app/19.3.0/grid [oracle@ol7-121-rac1 ./runcluvfy.sh stage -pre crsinst -upgrade -rolling \ -src_crshome /drcrs/app/oracle/product/grid12c \ -dest_crshome dumparea/oracle/app/grid19c \ -dest_version 19.0.0.0.0 -fixup -verbose cd /u01/app/19.3.0/grid ./runcluvfy.sh stage -pre crsinst -upgrade -rolling -src_crshome /u01/app/12.1.0.2/grid -dest_crshome /u01/app/19.3.0/grid -dest_version 19.0.0.0.0 -fixup -verbose -method sudo -user oracle Update opatch version and apply patches 28553832 and 27006180 Verifying Oracle patch:28553832 ...FAILED Verifying Oracle patch:21255373 ...FAILED cd /u01/grid/cv/rpm rpm -ivh cvuqdisk-1.0.10-1.rpm [grid@exacl04n1 +ASM1]$ sqlplus / as sysdba SQL*Plus: Release 18.0.0.0.0 - Production on Fri Jan 24 14:57:24 2020 Version 18.6.0.0.0 Copyright (c) 1982, 2018, Oracle. All rights reserved. Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production Version 18.6.0.0.0 SQL> show parameter memory_target NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ memory_target big integer 0 SQL> show parameter memory_max_target NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ memory_max_target big integer 0 SQL> show parameter use_large_pages NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ use_large_pages string TRUE SQL> show parameter sga_max_size NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ sga_max_size big integer 3G SQL> show parameter sga_target NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ sga_target big integer 3G Another point is stop all Oracle Databases running in the node before executing the script. So, in the first node was called the rootupgrade.sh: [root@exacl04n1 ~]# /u01/app/19.0.0.0/grid/rootupgrade.sh echo "ALTER DISKGROUP DATAC4 SET ATTRIBUTE 'compatible.asm' = '19.3.0.0.0';" | sqlplus -s / as sysasm Diskgroup altered. [grid@exacl04n1 +ASM1]$ echo "ALTER DISKGROUP RECOC4 SET ATTRIBUTE 'compatible.asm' = '19.3.0.0.0';" | sqlplus -s / as sysasm Diskgroup altered. [grid@exacl04n1 +ASM1]$ /u01/app/19.0.0.0/grid/oui/bin/runInstaller -nowait -waitforcompletion -ignoreSysPrereqs -updateNodeList ORACLE_HOME=/u01/app/19.0.0.0/grid "CLUSTER_NODES={exacl04n1,exacl04n2}" CRS=true LOCAL_NODE=exacl04n1 Starting Oracle Universal Installer... Checking swap space: must be greater than 500 MB. Actual 16174 MB Passed The inventory pointer is located at /etc/oraInst.loc [grid@exacl04n1 +ASM1]$ cat /u01/app/oraInventory/ContentsXML/inventory.xml |grep grid # 安装并应用DB OJVM patch 30484981. su - oracle -- Shutdown DB and Listener sqlplus / as sysdba shutdown immediate exit lsnrctl stop -- Run: opatch apply cd $ORACLE_HOME/OPatch ./opatch apply /u01/orasw/patches/30463595/30484981 -oh $ORACLE_HOME -- Startup DB and Listener sqlplus / as sysdba startup exit lsnrctl start -- Run: datapatch cd $ORACLE_HOME/OPatch ./datapatch -verbose Check dba_registry COL version FORMAT a10 COL action FORMAT a10 COL status FORMAT a10 COL action_time FORMAT a30 COL description FORMAT a65 SELECT patch_id,patch_type,action,status,action_time,description FROM dba_registry_sqlpatch; 重新编译 $ cd $ORACLE_HOME/rdbms/admin oracle@anbob:/u01/app/oracle/product/19JVM/rdbms/admin $ $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl --n 1 --e --b utlrp --d '''.''' utlrp.sql SQL> select count(*) from cdb_objects where status='INVALID'; COUNT(*) ---------- 0

当你初为DBA角色，以下脚本能助力一臂之力，已经进化D请飘过. 调整 db_files 数量默认为200，调整更大以支持更多表空间建立，修改步骤： SQL> conn sys/123456 as sysdba SQL> show parameter db_files; --显示环境最大的dbfile数量 SQL> shutdown immediate --关闭不了，使用shutdown abort; SQL> startup nomount SQL> alter system set db_files=10000 scope=spfile; --修改spfile 文件数 SQL> shutdown SQL> startup SQL> show parameter db_files; 备份spfile 文件，防止数据库配置后无法启动 SQL> create pfile='/home/oracle/spfile' from spfile; 创建spfile，数据库无法启动时使用 # cp /opt/oracle/admin/lnt/pfile/init.ora.63020161389 $ORACLE_HOME/dbs/initlnt.ora --init+实例名+'.ora' # create spfile from pfile # startup 关闭归档 SQL> conn sys/123456 as sysdba SQL> shutdown immediate; SQL> startup mount; SQL> select flashback_on from v$database; --查询归档状态 SQL> archive log list; --查询归档状态 SQL> alter database flashback off; --关闭 SQL> alter database noarchivelog; --关闭 SQL> archive log list; SQL> alter database open; 定时任务注意以命令模式提交定时任务，表示30分钟执行 var job_num number; begin dbms_job.submit(:job_num,'PRC_TBS_MONITOR();',SYSDATE,'SYSDATE + 30/(24*60)'); end; / 附加其它命令： a) 查看定时任务 select * from DBA_JOBS; b) 杀掉任务 exec dbms_job.remove(26); c) 如果定时任务不执行，检查当库允许的job个数： show parameter job_queue_processes; alter system set job_queue_processes=10 scope=both; RAC环境修改连接数 # sqlplus sys/123456 as sysdba SQL>create pfile='/home/oracle/spfile' from spfile; SQL>show parameter instance_name; --显示节点集群sid SQL>show parameter processes; --当前进程数 SQL>show parameter sessions; --当前连接数 SQL>alter system set processes=1024 scope=spfile; SQL>alter system set sessions=1558 scope=spfile;-- #(1.5 * PROCESSES) + 22 = sessions SQL>exit; # srvctl stop database -d devdb --关闭集群 # srvctl start database -d devdb --启动集群 SAG大小调整 a) 查看系统允许共享的最大虚拟内存 # getconf PAGE_SIZE --分页大小 4096 # cat /proc/sys/kernel/shmall --允许共享的最大分页数 2097152 系统允许的最大共享内存为： 4096 * 2097152 /1024/1024/1024 = 8G oracle中sga + pga(大约0.8G) <= 8G b) 调整sga为8G 如果sga目标调成8G，系统总允许共享内存至少为9G，那么系统允许共享的最大分页数shmall 至少为： 9*1024*1024*1024/4096 = 2359296 如果本机内存为16G，设置成跟物理内存一样大，那么： 16*1024*1024*1024/4096 = 4194304 c) 修改shmall # echo 4194304 > /proc/sys/kernel/shmall --当前环境有效 # vi /etc/sysctl.conf -- kernel.shmall 参数重启有效 d) 调整数据库sga大小 # sqlplus /nolog SQL> conn sys/123456 as sysdba SQL> show parameter sga --显示当前sga大小 SQL> shutdown immediate --关闭不了，使用shutdown abort; SQL> startup nomount SQL> show parameter sga SQL> show parameter memory_target --查看当前oracle能用的最大内存 SQL> alter system set sga_max_size=8G scope=spfile; SQL> alter system set sga_target=8G scope=spfile; SQL> alter system set memory_max_target=9G scope=spfile; --大于sga_max_size SQL> alter system set memory_target=9G scope=spfile; SQL> shutdown SQL> startup SQL> show parameter sga

/* 运行方法 sqlplus "/ as sysdba" @/home/oracle/sql/spooldb.sql */ SET markup html ON spool ON pre off entmap off set term off set heading on set verify off set feedback off set linesize 2000 set pagesize 30000 set long 999999999 set longchunksize 999999 column index_name format a30 column table_name format a30 column num_rows format 999999999 column index_type format a24 column num_rows format 999999999 column status format a8 column clustering_factor format 999999999 column degree format a10 column blevel format 9 column distinct_keys format 9999999999 column leaf_blocks format 9999999 column last_analyzed format a10 column column_name format a25 column column_position format 9 column temporary format a2 column partitioned format a5 column partitioning_type format a7 column partition_count format 999 column program format a30 column spid format a6 column pid format 99999 column sid format 99999 column serial# format 99999 column username format a12 column osuser format a12 column logon_time format date column event format a32 column JOB_NAME format a30 column PROGRAM_NAME format a32 column STATE format a10 column window_name format a30 column repeat_interval format a60 column machine format a30 column program format a30 column osuser format a15 column username format a15 column event format a50 column seconds format a10 column sqltext format a100 SET markup html off column dbid new_value spool_dbid column inst_num new_value spool_inst_num select dbid from v$database where rownum = 1; select instance_number as inst_num from v$instance where rownum = 1; column spoolfile_name new_value spoolfile select 'spool_'||(select name from v$database where rownum=1) ||'_'|| (select instance_number from v$instance where rownum=1)||'_'||to_char(sysdate,'yy-mm-dd_hh24.mi') as spoolfile_name from dual; spool &&spoolfile..html SET markup html off set serveroutput on; exec dbms_output.enable(9999999999); exec dbms_output.put_line(''); exec dbms_output.put_line(''); exec dbms_output.put_line(''); exec dbms_output.put_line(''); exec dbms_output.put_line(''); SET markup html on prompt

版本 select * from v$version; select * from dba_registry_history; prompt

最近一次启动时间，版本，以及是否RAC select * from (select name db_name from v$database), (select instance_name from v$instance), (select archiver from v$instance), (select snap_interval awr_interval, retention awr_retention FROM DBA_HIST_WR_CONTROL), (select flashback_on from v$database), (select parallel from v$instance), (select startup_time from v$instance), (select decode(name,null,'NOT ASM','ASM') IS_ASM from (select null name from dual union all (select name IS_ASM from v$datafile where name like '+%' and rownum = 1))), (select max(end_time) rman_lastcompleted from v$rman_status where status = 'COMPLETED' and object_type like 'DB FULL') ; prompt

30分钟内CPU或等待最长的 select t.*, s.sid, s.serial#, s.machine, s.program, s.osuser from (select c.USERNAME, a.event, to_char(a.cnt) as seconds, a.sql_id --,dbms_lob.substr(b.sql_fulltext,50,1) sqltext from (select rownum rn, t.* from (select decode(s.session_state, 'WAITING', s.event, 'Cpu + Wait For Cpu') Event, s.sql_id, s.user_id, count(*) CNT from v$active_session_history s where sample_time > sysdate - 30 / 1440 group by s.user_id, decode(s.session_state, 'WAITING', s.event, 'Cpu + Wait For Cpu'), s.sql_id order by CNT desc) t where rownum < 20) a, v$sqlarea b, dba_users c where a.sql_id = b.sql_id and a.user_id = c.user_id order by CNT desc) t, v$session s where t.sql_id = s.sql_id(+) ; prompt

近期负载情况(根据AWR快照) select s.snap_date, decode(s.redosize, null, '--shutdown or end--', s.currtime) "TIME", to_char(round(s.seconds/60,2)) "elapse(min)", round(t.db_time / 1000000 / 60, 2) "DB time(min)", s.redosize redo, round(s.redosize / s.seconds, 2) "redo/s", s.logicalreads logical, round(s.logicalreads / s.seconds, 2) "logical/s", physicalreads physical, round(s.physicalreads / s.seconds, 2) "phy/s", s.executes execs, round(s.executes / s.seconds, 2) "execs/s", s.parse, round(s.parse / s.seconds, 2) "parse/s", s.hardparse, round(s.hardparse / s.seconds, 2) "hardparse/s", s.transactions trans, round(s.transactions / s.seconds, 2) "trans/s" from (select curr_redo - last_redo redosize, curr_logicalreads - last_logicalreads logicalreads, curr_physicalreads - last_physicalreads physicalreads, curr_executes - last_executes executes, curr_parse - last_parse parse, curr_hardparse - last_hardparse hardparse, curr_transactions - last_transactions transactions, round(((currtime + 0) - (lasttime + 0)) * 3600 * 24, 0) seconds, to_char(currtime, 'yy/mm/dd') snap_date, to_char(currtime, 'hh24:mi') currtime, currsnap_id endsnap_id, to_char(startup_time, 'yyyy-mm-dd hh24:mi:ss') startup_time from (select a.redo last_redo, a.logicalreads last_logicalreads, a.physicalreads last_physicalreads, a.executes last_executes, a.parse last_parse, a.hardparse last_hardparse, a.transactions last_transactions, lead(a.redo, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_redo, lead(a.logicalreads, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_logicalreads, lead(a.physicalreads, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_physicalreads, lead(a.executes, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_executes, lead(a.parse, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_parse, lead(a.hardparse, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_hardparse, lead(a.transactions, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_transactions, b.end_interval_time lasttime, lead(b.end_interval_time, 1, null) over(partition by b.startup_time order by b.end_interval_time) currtime, lead(b.snap_id, 1, null) over(partition by b.startup_time order by b.end_interval_time) currsnap_id, b.startup_time from (select snap_id, dbid, instance_number, sum(decode(stat_name, 'redo size', value, 0)) redo, sum(decode(stat_name, 'session logical reads', value, 0)) logicalreads, sum(decode(stat_name, 'physical reads', value, 0)) physicalreads, sum(decode(stat_name, 'execute count', value, 0)) executes, sum(decode(stat_name, 'parse count (total)', value, 0)) parse, sum(decode(stat_name, 'parse count (hard)', value, 0)) hardparse, sum(decode(stat_name, 'user rollbacks', value, 'user commits', value, 0)) transactions from dba_hist_sysstat where stat_name in ('redo size', 'session logical reads', 'physical reads', 'execute count', 'user rollbacks', 'user commits', 'parse count (hard)', 'parse count (total)') group by snap_id, dbid, instance_number) a, dba_hist_snapshot b where a.snap_id = b.snap_id and a.dbid = b.dbid and a.instance_number = b.instance_number and a.dbid = &&spool_dbid and a.instance_number = &&spool_inst_num order by end_interval_time)) s, (select lead(a.value, 1, null) over(partition by b.startup_time order by b.end_interval_time) - a.value db_time, lead(b.snap_id, 1, null) over(partition by b.startup_time order by b.end_interval_time) endsnap_id from dba_hist_sys_time_model a, dba_hist_snapshot b where a.snap_id = b.snap_id and a.dbid = b.dbid and a.instance_number = b.instance_number and a.stat_name = 'DB time' and a.dbid = &&spool_dbid and a.instance_number = &&spool_inst_num) t where s.endsnap_id = t.endsnap_id order by s.snap_date desc ,time asc ; prompt

逻辑读最多 select * from (select sql_id, s.EXECUTIONS, s.LAST_LOAD_TIME, s.FIRST_LOAD_TIME, s.DISK_READS, s.BUFFER_GETS from v$sql s where s.buffer_gets > 300 order by buffer_gets desc) where rownum <= 10 ; prompt

物理读最多 select * from (select sql_id, s.EXECUTIONS, s.LAST_LOAD_TIME, s.FIRST_LOAD_TIME, s.DISK_READS, s.BUFFER_GETS, s.PARSE_CALLS from v$sql s where s.disk_reads > 300 order by disk_reads desc) where rownum<=10 ; prompt

执行次数最多 select * from (select sql_id, s.EXECUTIONS, s.LAST_LOAD_TIME, s.FIRST_LOAD_TIME, s.DISK_READS, s.BUFFER_GETS, s.PARSE_CALLS from v$sql s order by s.EXECUTIONS desc) where rownum <= 10 ; prompt

解析次数最多 select * from (select sql_id, s.EXECUTIONS, s.LAST_LOAD_TIME, s.FIRST_LOAD_TIME, s.DISK_READS, s.BUFFER_GETS, s.PARSE_CALLS from v$sql s order by s.PARSE_CALLS desc) where rownum <= 10 ; prompt

磁盘排序最多 select sess.username, sql.address, sort1.blocks from v$session sess, v$sqlarea sql, v$sort_usage sort1 where sess.serial# = sort1.session_num and sort1.sqladdr = sql.address and sort1.sqlhash = sql.hash_value and sort1.blocks > 200 order by sort1.blocks desc ; prompt

提交次数超过10000的session select t1.sid, t1.value, t2.name from v$sesstat t1, v$statname t2 where t2.name like '%user commits%' and t1.STATISTIC# = t2.STATISTIC# and value >= 10000 order by value desc ; prompt

长度超过100的SQL SELECT SQL_ID, COUNT(*) line_count FROM V$SQLTEXT GROUP BY SQL_ID HAVING COUNT(*) >= 100 ORDER BY COUNT(*) DESC ; prompt

查询共享内存占有率 select count(*),round(sum(sharable_mem)/1024/1024,2) from v$db_object_cache a ; prompt

表有带并行度 select t.owner, t.table_name, degree from dba_tables t where trim(t.degree) <>'1' and trim(t.degree)<>'0' and owner not in ('SYSTEM','SYSMAN','SYS','CTXSYS','MDSYS','OLAPSYS','WMSYS','EXFSYS','LBACSYS','WKSYS','XDB','ORDSYS','DBSNMP','OUTLN','TSMSYS') and owner not like 'FLOWS%' and owner not like 'WK%' ; prompt

索引有带并行度 select t.owner, t.table_name, index_name, degree, status from dba_indexes t where trim(t.degree) <>'1' and trim(t.degree)<>'0' and owner not in ('SYSTEM','SYSMAN','SYS','CTXSYS','MDSYS','OLAPSYS','WMSYS','EXFSYS','LBACSYS','WKSYS','XDB','ORDSYS','DBSNMP','OUTLN','TSMSYS') and owner not like 'FLOWS%' and owner not like 'WK%' ; prompt

失效索引 select t.index_name, t.table_name, blevel, t.num_rows, t.leaf_blocks, t.distinct_keys from dba_indexes t where status = 'UNUSABLE' and table_owner not in ('SYSTEM','SYSMAN','SYS','CTXSYS','MDSYS','OLAPSYS','WMSYS','EXFSYS','LBACSYS','WKSYS','XDB') and table_owner not like 'FLOWS%' ; select t2.owner, t1.blevel, t1.leaf_blocks, t1.INDEX_NAME, t2.table_name, t1.PARTITION_NAME, t1.STATUS from dba_ind_partitions t1, dba_indexes t2 where t1.index_name = t2.index_name and t1.STATUS = 'UNUSABLE' ; prompt

失效对象 select t.owner, t.object_type, t.object_name from dba_objects t where STATUS='INVALID' order by 1, 2 ; prompt

位图索引和函数索引、反向键索引 select t.owner, t.table_name, t.index_name, t.index_type, t.status, t.blevel, t.leaf_blocks from dba_indexes t where index_type in ('BITMAP', 'FUNCTION-BASED NORMAL', 'NORMAL/REV') and owner not in ('SYSTEM','SYSMAN','SYS','CTXSYS','MDSYS','OLAPSYS','WMSYS','EXFSYS','LBACSYS','WKSYS','XDB','ORDSYS','DBSNMP','OUTLN','TSMSYS') and owner not like 'FLOWS%' and owner not like 'WK%' ; prompt

组合索引组合列超过4个的 select table_owner,table_name, index_name, count(*) from dba_ind_columns where table_owner not in ('SYSTEM','SYSMAN','SYS','CTXSYS','MDSYS','OLAPSYS','WMSYS','EXFSYS','LBACSYS','WKSYS','XDB','ORDSYS','DBSNMP','OUTLN','TSMSYS') and table_owner not like 'FLOWS%' and table_owner not like 'WK%' group by table_owner,table_name, index_name having count(*) >= 4 order by count(*) desc ; prompt

索引个数字超过5个的 select owner,table_name, count(*) cnt from dba_indexes where owner not in ('SYSTEM','SYSMAN','SYS','CTXSYS','MDSYS','OLAPSYS','WMSYS','EXFSYS','LBACSYS','WKSYS','XDB','ORDSYS','DBSNMP','OUTLN','TSMSYS') and owner not like 'FLOWS%' and owner not like 'WK%' group by owner,table_name having count(*) >= 5 order by cnt desc ; prompt

哪些大表从未建过索引。 select segment_name, bytes / 1024 / 1024 / 1024 "GB", blocks, tablespace_name from dba_segments where segment_type = 'TABLE' and segment_name not in (select table_name from dba_indexes) and bytes / 1024 / 1024 / 1024 >= 0.5 order by GB desc ; select segment_name, sum(bytes) / 1024 / 1024 / 1024 "GB", sum(blocks) from dba_segments where segment_type = 'TABLE PARTITION' and segment_name not in (select table_name from dba_indexes) group by segment_name having sum(bytes) / 1024 / 1024 / 1024 >= 0.5 order by GB desc ; prompt

哪些表的组合索引与单列索引存在交叉的情况。 select table_name, trunc(count(distinct(column_name)) / count(*),2) cross_idx_rate from dba_ind_columns where table_owner not in ('SYSTEM','SYSMAN','SYS','CTXSYS','MDSYS','OLAPSYS','WMSYS','EXFSYS','LBACSYS','WKSYS','XDB','ORDSYS','DBSNMP','OUTLN','TSMSYS') and table_owner not like 'FLOWS%' and table_owner not like 'WK%' group by table_name having count(distinct(column_name)) / count(*) < 1 order by cross_idx_rate desc ; prompt

哪些对象建在系统表空间上。 select * from ( select owner, segment_name, tablespace_name, count(*) num from dba_segments where tablespace_name in('SYSTEM','SYSAUX') group by owner, segment_name, tablespace_name) where owner not in ('SYSTEM','SYSMAN','SYS','CTXSYS','MDSYS','OLAPSYS','WMSYS','EXFSYS','LBACSYS','WKSYS','XDB','ORDSYS','DBSNMP','OUTLN','TSMSYS') and owner not like 'FLOWS%' and owner not like 'WK%' ; prompt

检查统计信息是否被收集 select t.job_name,t.program_name,t.state,t.enabled from dba_scheduler_jobs t where job_name = 'GATHER_STATS_JOB' ; select client_name,status from dba_autotask_client ; select window_next_time,autotask_status from DBA_AUTOTASK_WINDOW_CLIENTS ; prompt

检查哪些未被收集或者很久没收集 select owner, count(*) from dba_tab_statistics t where (t.last_analyzed is null or t.last_analyzed < sysdate - 100) and table_name not like 'BIN$%' group by owner order by owner ; prompt

被收集统计信息的临时表 select owner, table_name, t.last_analyzed, t.num_rows, t.blocks from dba_tables t where t.temporary = 'Y' and last_analyzed is not null ; prompt

日志切换频率分析 select * from (select thread#, sequence#, to_char(first_time, 'MM/DD/RR HH24:MI:SS') from v$log_history order by first_time desc) where rownum <= 50 ; prompt

最近10天中每天日志切换的量 SELECT SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5) Day, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'00',1,0)) H00, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'01',1,0)) H01, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'02',1,0)) H02, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'03',1,0)) H03, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'04',1,0)) H04, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'05',1,0)) H05, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'06',1,0)) H06, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'07',1,0)) H07, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'08',1,0)) H08, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'09',1,0)) H09, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'10',1,0)) H10, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'11',1,0)) H11, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'12',1,0)) H12, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'13',1,0)) H13, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'14',1,0)) H14, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'15',1,0)) H15, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'16',1,0)) H16, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'17',1,0)) H17, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'18',1,0)) H18, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'19',1,0)) H19, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'20',1,0)) H20, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'21',1,0)) H21, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'22',1,0)) H22 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'23',1,0)) H23, COUNT(*) TOTAL FROM v$log_history a where first_time>=to_char(sysdate-11) GROUP BY SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5) ORDER BY SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5) DESC ; prompt

日志组大小 select group#,bytes,status from v$log ; prompt

查看recovery_file_dest使用率 select substr(name, 1, 30) name, space_limit as quota, space_used as used, space_reclaimable as reclaimable, number_of_files as files from v$recovery_file_dest ; select * from V$FLASH_RECOVERY_AREA_USAGE ; prompt

检查序列小于20的情况 select sequence_owner, count(*) CNT, sum(case when t.cache_size <= 20 then 1 else 0 end ) CNT_LESS_20, sum(case when t.cache_size > 20 then 1 else 0 end ) CNT_MORE_20 from dba_sequences t group by sequence_owner ; prompt

表空间使用情况 set markup html off prompt

declare type NUMBER_ARRAY is table of number(15) index by varchar2(30); ts_free_mb NUMBER_ARRAY; cursor c1 is select tablespace_name, sum(free_mb) + sum(expired_mb) free_mb from (SELECT tablespace_name, round(sum(nvl(bytes, 0)) / 1024 / 1024, 2) free_mb, 0 expired_mb FROM dba_free_space GROUP BY tablespace_name union all select tablespace_name, 0 free_mb, round(sum(nvl(bytes, 0)) / 1024 / 1024, 2) expired_mb from dba_undo_extents d where tablespace_name = (select value from v$parameter where name = 'undo_tablespace') and status = 'EXPIRED' group by tablespace_name) group by tablespace_name; cursor c2 is SELECT /*+ rule */ tablespace_name, round(sum(bytes) / 1024 / 1024, 2) total_mb FROM dba_data_files where tablespace_name not in (select tablespace_name from dba_data_files where upper(AUTOEXTENSIBLE) = 'YES') GROUP BY tablespace_name; ts_name varchar2(30); ts_total number(15); ts_used number(15); ts_free number(15); ts_rate varchar2(5); begin for rec1 in c1 loop ts_free_mb(rec1.tablespace_name) := rec1.free_mb; end loop; dbms_output.put_line('

'); dbms_output.put_line(''); for rec2 in c2 loop ts_name := null; ts_total := null; ts_used := null; ts_free := null; ts_rate := null; ts_name := rec2.tablespace_name; ts_total := rec2.total_mb; ts_free := nvl(ts_free_mb(ts_name), 0); ts_used := nvl(ts_total - ts_free, 0); ts_rate := to_char(round((ts_total - ts_free) / ts_total * 100, 2), 'fm990.99'); dbms_output.put_line(''); end loop; dbms_output.put_line('

ts_name	ts_total	ts_used	ts_free	ts_rate
' \|\| ts_name \|\| '	' \|\| ts_total \|\| '	' \|\| ts_used \|\| '	' \|\| ts_free \|\| '	' \|\| ts_rate \|\| '

'); end; / prompt

set markup html on prompt

整个数据库有多大 select owner, round(sum(bytes) / 1024 / 1024 / 1024, 2) "GB" from dba_segments group by owner order by 2 desc ; prompt

对象大小TOP10 select * from (select owner, segment_name, segment_type, round(sum(bytes) / 1024 / 1024) object_size from DBA_segments group by owner, segment_name, segment_type order by object_size desc) where rownum <= 10 ; prompt

回收站情况(大小及数量） select * from (select SUM(BYTES) / 1024 / 1024 / 1024 as recyb_size from DBA_SEGMENTS WHERE SEGMENT_NAME LIKE 'BIN$%') a, (select count(*) as recyb_cnt from dba_recyclebin) ; prompt

查谁占用了undo表空间 SELECT r.name "roll_segment_name", rssize/1024/1024/1024 "RSSize(G)", s.sid, s.serial#, s.username, s.status, s.sql_hash_value, s.SQL_ADDRESS, s.MACHINE, s.MODULE, substr(s.program, 1, 78) program, r.usn, hwmsize/1024/1024/1024, shrinks ,xacts FROM sys.v_$session s,sys.v_$transaction t,sys.v_$rollname r, v$rollstat rs WHERE t.addr = s.taddr and t.xidusn = r.usn and r.usn=rs.USN Order by rssize desc ; prompt

查谁占用了temp表空间 select sql.sql_id, t.Blocks * 16 / 1024 / 1024, s.USERNAME, s.SCHEMANAME, t.tablespace, t.segtype, t.extents, s.PROGRAM, s.OSUSER, s.TERMINAL, s.sid, s.SERIAL# from v$sort_usage t, v$session s , v$sql sql where t.SESSION_ADDR = s.SADDR and t.SQLADDR=sql.ADDRESS and t.SQLHASH=sql.HASH_VALUE ; prompt

观察回滚段，临时段及普通段否是自动扩展 select t2.contents, t1.* from (select file_name, tablespace_name, bytes, maxbytes, autoextensible from dba_temp_files union all select file_name, tablespace_name, bytes, maxbytes, autoextensible from dba_data_files) t1, dba_tablespaces t2 where t1.tablespace_name = t2.tablespace_name ; prompt

表大小超过10GB未建分区的 select owner, segment_name, segment_type, round(sum(bytes) / 1024 / 1024 / 1024,2) object_size from dba_segments where segment_type = 'TABLE' and bytes > 10*1024*1024*1024 group by owner, segment_name, segment_type order by object_size desc ; prompt

分区最多的前10个对象 select * from (select table_owner, table_name, count(*) cnt from dba_tab_partitions group by table_owner, table_name order by cnt desc) where rownum <= 10 ; prompt

分区不均匀的表 select * from (select table_owner, table_name, max(num_rows) max_num_rows, trunc(avg(num_rows), 0) avg_num_rows, sum(num_rows) sum_num_rows, case when sum(num_rows) = 0 then 0 else trunc(max(num_rows) / trunc(avg(num_rows), 0), 2) end rate, count(*) part_count from dba_tab_partitions group by table_owner, table_name) where rate > 5; prompt

列数量超过100个或小于2的表 select * from (select owner, table_name, count(*) col_count from dba_tab_cols group by owner, table_name) where col_count > 100 or col_count <= 2 and owner not in ('SYSTEM','SYSMAN','SYS','CTXSYS','MDSYS','OLAPSYS','WMSYS','EXFSYS','LBACSYS','WKSYS','XDB') and owner not like 'FLOWS%' ; prompt

表属性是nologging的 select owner, table_name, tablespace_name, logging from dba_tables where logging = 'NO' and owner not in ('SYSTEM','SYSMAN','SYS','CTXSYS','MDSYS','OLAPSYS','WMSYS','EXFSYS','LBACSYS','WKSYS','XDB') and owner not like 'FLOWS%' ; prompt

表属性含COMPRESSION的 select owner, table_name, tablespace_name, COMPRESSION from dba_tables where COMPRESSION = 'ENABLED' and owner not in ('SYSTEM','SYSMAN','SYS','CTXSYS','MDSYS','OLAPSYS','WMSYS','EXFSYS','LBACSYS','WKSYS','XDB') and owner not like 'FLOWS%' ; prompt

索引属性含COMPRESSION的 select owner, index_name, table_name, COMPRESSION from dba_indexes where COMPRESSION = 'ENABLED' and owner not in ('SYSTEM','SYSMAN','SYS','CTXSYS','MDSYS','OLAPSYS','WMSYS','EXFSYS','LBACSYS','WKSYS','XDB') and owner not like 'FLOWS%' ; prompt

触发器 select OWNER, TRIGGER_NAME, TABLE_NAME, STATUS from dba_triggers where owner not in ('SYSTEM','SYSMAN','SYS','CTXSYS','MDSYS','OLAPSYS','WMSYS','EXFSYS','LBACSYS','WKSYS','XDB') and owner not like 'FLOWS%'; prompt

将外键未建索引的情况列出 select * from (select pk.owner PK_OWNER, pk.constraint_name PK_NAME, pk.table_name PK_TABLE_NAME, fk.owner FK_OWNER, fk.constraint_name FK_NAME, fk.table_name FK_TABLE_NAME, fk.delete_rule FK_DELETE_RULE, ind_col.INDEX_NAME FK_INDEX_NAME, ind.index_type FK_INDEX_TYPE, con_col.COLUMN_NAME FK_INDEX_COLUMN_NAME, con_col.POSITION FK_INDEX_COLUMN_POSITION, decode(ind_col.INDEX_NAME, NULL, NULL, NVL(x_ind.status, 'VALID')) IS_IND_VALID, decode(ind_col.INDEX_NAME, NULL, NULL, NVL(x_ind_part.status, 'VALID')) IS_IND_PART_VALID from (select * from dba_constraints where constraint_type = 'R') fk, (select * from dba_constraints where constraint_type = 'P') pk, dba_cons_columns con_col, dba_ind_columns ind_col, dba_indexes ind, (select index_owner, index_name, status from dba_ind_partitions where status <> 'VALID') x_ind_part, (select owner as index_owner, index_name, status from dba_indexes where status <> 'VALID') x_ind where fk.r_constraint_name = pk.constraint_name and pk.owner = fk.owner and fk.owner = con_col.owner and fk.table_name = con_col.table_name and fk.constraint_name = con_col.CONSTRAINT_NAME and con_col.owner = ind_col.TABLE_OWNER(+) and con_col.TABLE_NAME = ind_col.TABLE_NAME(+) and con_col.COLUMN_NAME = ind_col.COLUMN_NAME(+) and ind_col.INDEX_OWNER = ind.owner(+) and ind_col.INDEX_NAME = ind.index_name(+) and ind_col.INDEX_OWNER = x_ind.index_owner(+) and ind_col.INDEX_NAME = x_ind.index_name(+) and ind_col.INDEX_OWNER = x_ind_part.index_owner(+) and ind_col.INDEX_NAME = x_ind_part.index_name(+)) where FK_INDEX_NAME is null order by FK_OWNER ASC ; /* 性能不好 prompt

热点块(汇总） SELECT *+ rule * e.owner, e.segment_name, e.segment_type, sum(b.tch) tch FROM dba_extents e, (SELECT * FROM (SELECT addr, ts#, file#, dbarfil, dbablk, tch FROM x$bh ORDER BY tch DESC) WHERE ROWNUM <= 10) b WHERE e.relative_fno = b.dbarfil AND e.block_id <= b.dbablk AND e.block_id + e.blocks > b.dbablk group by e.owner, e.segment_name, e.segment_type order by tch desc ; prompt

热点块(展开，未汇总） SELECT *+ rule * distinct e.owner, e.segment_name, e.segment_type, dbablk,b.tch FROM dba_extents e, (SELECT * FROM (SELECT addr, ts#, file#, dbarfil, dbablk, tch FROM x$bh ORDER BY tch DESC) WHERE ROWNUM <= 10) b WHERE e.relative_fno = b.dbarfil AND e.block_id <= b.dbablk AND e.block_id + e.blocks > b.dbablk order by tch desc ; */ prompt

附录：查看session_cached_cursors的参数设置情况，如果使用率为100%则增大这个参数值 SELECT 'session_cached_cursors' PARAMETER, LPAD(VALUE, 5) VALUE, DECODE(VALUE, 0, ' n/a', TO_CHAR(100 * USED / VALUE, '990') || '%') USAGE FROM (SELECT MAX(S.VALUE) USED FROM V$STATNAME N, V$SESSTAT S WHERE N.NAME = 'session cursor cache count' AND S.STATISTIC# = N.STATISTIC#), (SELECT VALUE FROM V$PARAMETER WHERE NAME = 'session_cached_cursors') UNION ALL SELECT 'open_cursors', LPAD(VALUE, 5), TO_CHAR(100 * USED / VALUE, '990') || '%' FROM (SELECT MAX(SUM(S.VALUE)) USED FROM V$STATNAME N, V$SESSTAT S WHERE N.NAME IN ('opened cursors current', 'session cursor cache count') AND S.STATISTIC# = N.STATISTIC# GROUP BY S.SID), (SELECT VALUE FROM V$PARAMETER WHERE NAME = 'open_cursors'); prompt

附录：供参考的Oracle所有参数 show parameter set markup html off exec dbms_output.put_line(''); exec dbms_output.put_line(''); spool off /* 获取awr、addm、ash */ --以下不使用html标签 SET markup html off spool ON pre off entmap off set trim on set trimspool on set heading off --查询dbid、instance_number column dbid new_value awr_dbid column instance_number new_value awr_inst_num select dbid from v$database; select instance_number from v$instance; --半小时内的ash报告 column ashbegintime new_value ashbegin_str column ashendtime new_value ashend_str select to_char(sysdate-3/144,'yyyymmddhh24miss') as ashbegintime, to_char(sysdate,'yyyymmddhh24miss') as ashendtime from dual; column ashfile_name new_value ashfile select 'ashrpt_' || to_char(&&awr_inst_num) || '_' || to_char(&&ashbegin_str) || '_' || to_char(&&ashend_str) ashfile_name from dual; spool &&ashfile..html select * from table(dbms_workload_repository.ash_report_html(to_char(&&awr_dbid),to_char(&&awr_inst_num),to_date(to_char(&&ashbegin_str),'yyyymmddhh24miss'),to_date(to_char(&&ashend_str),'yyyymmddhh24miss'))); spool off; --按需创建awr断点 column begin_snap new_value awr_begin_snap column end_snap new_value awr_end_snap select max(snap_id) begin_snap from dba_hist_snapshot where snap_id < (select max(snap_id) from dba_hist_snapshot); select max(snap_id) end_snap from dba_hist_snapshot; declare snap_maxtime date; snap_mintime date; begin select max(end_interval_time) + 0 into snap_maxtime from dba_hist_snapshot where snap_id = to_number(&&awr_end_snap); select max(end_interval_time) + 0 into snap_mintime from dba_hist_snapshot where snap_id = to_number(&&awr_begin_snap); if sysdate - snap_maxtime > 10/1445 then dbms_workload_repository.create_snapshot(); end if; end; / --最新两次snap_id间的awr报告 column begin_snap new_value awr_begin_snap column end_snap new_value awr_end_snap select max(snap_id) begin_snap from dba_hist_snapshot where snap_id < (select max(snap_id) from dba_hist_snapshot); select max(snap_id) end_snap from dba_hist_snapshot; column awrfile_name new_value awrfile select 'awrrpt_' || to_char(&&awr_inst_num) || '_' || to_char(&&awr_begin_snap) || '_' || to_char(&&awr_end_snap) awrfile_name from dual; spool &&awrfile..html select output from table(dbms_workload_repository.awr_report_html(&&awr_dbid,&&awr_inst_num,&&awr_begin_snap,&&awr_end_snap)); spool off; --可获取的最长awr报告(一周以来的所有分析) column begin_snap new_value awr_begin_snap column end_snap new_value awr_end_snap select a.begin_snap, a.end_snap from (select startup_time, min(snap_id) begin_snap, max(snap_id) end_snap from dba_hist_snapshot group by startup_time) a, (select max(startup_time) startup_time from dba_hist_snapshot) b where a.startup_time = b.startup_time and rownum = 1; column awrfile_name new_value awrfile select 'awrrpt_' || to_char(&&awr_inst_num) || '_' || to_char(&&awr_begin_snap) || '_' || to_char(&&awr_end_snap) ||'_all' awrfile_name from dual; spool &&awrfile..html select output from table(dbms_workload_repository.awr_report_html(&&awr_dbid,&&awr_inst_num,&&awr_begin_snap,&&awr_end_snap)); spool off; --最新addm报告 column addmfile_name new_value addmfile select 'addmrpt_' || to_char(&&awr_inst_num) || '_' || to_char(&&awr_begin_snap) || '_' || to_char(&&awr_end_snap) addmfile_name from dual; set serveroutput on spool &&addmfile..txt declare id number; name varchar2(200) := ''; descr varchar2(500) := ''; addmrpt clob; v_ErrorCode number; BEGIN name := '&&addmfile'; begin dbms_advisor.create_task('ADDM', id, name, descr, null); dbms_advisor.set_task_parameter(name, 'START_SNAPSHOT', &&awr_begin_snap); dbms_advisor.set_task_parameter(name, 'END_SNAPSHOT', &&awr_end_snap); dbms_advisor.set_task_parameter(name, 'INSTANCE', &&awr_inst_num); dbms_advisor.set_task_parameter(name, 'DB_ID', &&awr_dbid); dbms_advisor.execute_task(name); exception when others then null; end; select dbms_advisor.get_task_report(name, 'TEXT', 'TYPICAL') into addmrpt from sys.dual; dbms_output.enable(20000000000); for i in 1 .. (DBMS_LOB.GETLENGTH(addmrpt) / 2000 + 1) loop dbms_output.put_line(substr(addmrpt, 1900 * (i - 1) + 1, 1900)); end loop; dbms_output.put_line(''); begin dbms_advisor.delete_task(name); exception when others then null; end; end; / spool off; exit;

Netopeak

Tuesday, August 30, 2022

Sunday, August 28, 2022

Labels

weithenn

Blog Archive

About Me