oracle连接数过多清理机制
Oracle临时表空间清理
Oracle 临时表空间清理回收
Oracle临时表空间使用时，一旦空间自动扩展，已扩展的大小是不能收缩的，除非重建临时表空间。
正常来说临时表空间中非活动段是由后台smon进程自动释放的，但smon进程每两个小时五分钟才进行一次临时段的回收清理，且每次清理只会回收五个临时段，因此如果连接排序等操作过于频繁时，会导致临时表空间扩展，当然这跟配置的Oracle物理内存也有关系，主要是PGA区，如果配置的内存过少，大量的排序在内存中无法完成，将会额外使用临时表空间来完成，因此临时表空间消耗与配置了大PGA内存相比必然多出。
频繁复杂的语句连接排序完全可能导致临时表空间不停扩展，临时段来不及回收，出现无临时段可用的情况。
如果临时段不能及时释放，通常可以通过重启数据库来清理非活动的临时段，实践证明没有问题。
但如果生产环境不适宜重启数据库，也可以使用手工方式进行非活动临时段的清理。
Oracle 9i OEM中经常看到临时表空间满，其实那是假象，因为OEM是从v$temp_extent_pool统计的。
正常来说要看v$sort_segment中sum(free_blocks)是不是近于0，以确认临时表空间是不是真的满了，如果为0,则表明临时表空间已被当前激活临时段用完。
手工清理非活动临时段的方法如下：
1、正常退出、终止所有连接数据库的应用
5、执行临时表空间非激活临时段清理
该方法适用于Oracle9i以上版本。
oracle连接数过多导致系统非常慢分析总结
Tue Jan 19 10:52:49 2010
skgpspawn failed:category = 27142, depinfo = 11, p = fork, loc = skgpspawn5
skgpspawn failed:category = 27142, depinfo = 11, p = fork, loc = skgpspawn3
skgpspawn failed:category = 27142, depinfo = 11, p = fork, loc = skgpspawn3
skgpspawn failed:category = 27142, depinfo = 11, p = fork, loc = skgpspawn3
19-JAN-2010 10:32:38 * (CONNECT_DATA=(SID=ora9jsy)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.2.1)(PORT=1076)) * establish * ora9jsy * 0
19-JAN-2010 10:32:39 * (CONNECT_DATA=(SID=ora9jsy)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.2.1)(PORT=1077)) * establish * ora9jsy * 0
TNS-12500: TNS:listener failed to start a dedicated server process
TNS-12540: TNS:internal limit restriction exceeded
删除大量Oracle数据方法总结
删除大量Oracle数据方法总结Oracle中删除超过50w条记录的数据，如果直接使用delete，效率就严重受到了影响。
那么首先我们需要了解对于这个表的数据，我们到底是全部删除，还是部分删除。
这里有三个关键字我们需要注意：truncate,delete,drop。
全部删除，不保留数据结构就直接drop最好。
如果是部分删除，一般就这样一些办法：1. 如果删除的数据是大部分，分段提交删除的数据。
create or replace proceduredelete_table is i number(10);beginfor x in (select * from emp where DEPTNO like‘a%’)loopdelete emp where emp.id = x.id i:=i+1;if i>1000thencommit;i:=0;end if;end loop;exception when others then dbms_out.put_line(sqlcode);rollback;end delete_table;或者–每500条数据提交一次DECLARE CNT NUMBER(10):=0;I NUMBER(10);BEGINSELECT COUNT(*) INTO CNT FROM ep_arrearage_bakWHERE TO_CHAR(DF_DATE,‘MM’)=’01′;FOR I IN1..TRUNC(CNT/500)+1LOOPDELETE FROM ep_arrearage_bak WHERE TO_CHAR(DF_DATE,‘MM’)=’01′AND ROWNUM<=500;COMMIT;END LOOP;END;2、把要保留的数据放在一个临时表里,truncate table原表后再放回来；create table t_back as select * from t where….drop table t;rename t_back to t;3. 专门使用一个大回滚段，比如定义：undo tablespace 2G4、如果将方法1做一点修改，可以这么做：有条件的分步删除数据表中的记录–创建测试表create table test as select * from dba_objects;–创建删除表的存储过程create or replace procedure deleteTab–插入语句SQL> insert into test select * from dba_objects;6374rows created.SQL> / 6374rows created.SQL> / 6374rows created.SQL> commit;–创建删除的存储过程create or replace procedure deleteTab/**** Usage: run the script to create the proc deleteTab** in SQL*PLUS, type "execdeleteTab(‘Foo’,'ID>=1000000′,’3000′);"** to delete the records in the table "Foo", commit per 3000 records.** Condition with default value ’1=1′ and default Commit batch is 10000.**/(p_TableName in varchar2, –The TableName which you want to delete fromp_Condition in varchar2 default’1=1′, –Delete condition, such as "id>=100000"p_Count in varchar2 default’10000′– Commit after delete How many records)as pragma autonomous_transaction;n_delete number:=0;beginwhile 1=1 loopEXECUTE IMMEDIATE‘delete from ‘||p_TableName||‘ where ‘||p_Condition||‘ and rownum <= :rn’USING p_Count;if SQL%NOTFOUND thenexit;elsen_delete:=n_delete + SQL%ROWCOUNT;end if;commit;end loop;commit;DBMS_OUTPUT.PUT_LINE(‘Finished!’);DBMS_OUTPUT.PUT_LINE(‘Totally ‘||to_char(n_delete)||‘ records deleted!’);end; /–执行语句SQL> exec deleteTab(‘TEST’,‘object_id >0′,’10000′)5、如果确认将来不需要做恢复,改为非归档模式,删除完改回来再做个备份。
Oracle占用cpu过高的处理办法
Oracle占⽤cpu过⾼的处理办法问题描述:今天上午10点多，公司⽹络断了⼀会，过了⼤约⼗来分钟，⽹⼯处理好了，可数据库这下⼦可撑不住了，打开linux top查看了⼀下CPU百分百了，这可能是因为缓冲在客户端的数据⼀下⼦全传上来了导致数据库压⼒过⼤，可以前没有出现过这种问题，于是进⾏了分析和处理，以下为处理过程：问题分析：⼀般cpu占⽤效⾼都是排序、sql解析和全表扫描，这⾥⾸先需要找出占⽤cpu最⾼的sql，然后查看他的执⾏计划，⽐如：看执⾏计划是⾛索引还是全表扫描（刚开始查看top发现占⽤同样多的CPU的进程很多，还以为是oracle 的bug, 后来发现不是）。
处理过程：1, 根据操作系统进程查找Oracle数据库中占⽤最多CPU的SQL使⽤Linux系统 "top命令->P "查出占⽤cpu最⾼的进程PID操作如下：在sqlplus中执⾏如下sql:SQL>SELECTsql_textFROM v$sqltext aWHERE (a.hash_value, a.address) IN(SELECT DECODE(sql_hash_value, 0, prev_hash_value, sql_hash_value),DECODE(sql_hash_value, 0, prev_sql_addr, sql_address)FROM v$session bWHERE b.paddr =(SELECT addr FROM v$process c WHERE c.spid = '&pid'))ORDER BY piece ASC其中&pid 是使⽤top 查看系统中进程占⽤CPU极⾼的PID找到SQL语句进⾏相应的调整优化2，分析找到的sql语句，如查看sql执⾏计划。
总结：这⾥的问题是查询的where 条件字段没有在索引⾥⾯，导致查询慢。
oracle 数据清理和归档方案
Oracle 数据清理和归档方案概述在日常的数据管理中，数据清理和归档是非常重要的环节。
在Oracle数据库中，合理的数据清理和归档方案可以提高数据库的性能和可靠性，同时也能减少数据库的存储空间和备份/恢复的时间和成本。
本文将就Oracle数据库中的数据清理和归档方案进行详细的探讨。
数据清理方案数据清理是指清理不再需要的数据，以释放数据库的存储空间和提高查询性能。
下面是一些常用的数据清理方案：1. 删除过期数据在数据库中，某些数据可能有一定的保存期限，超过期限后就可以被删除。
可以通过编写定时任务或者使用Oracle的调度程序来定期删除过期数据。
例如，可以根据时间戳或者某个字段的值来判断数据是否过期，然后使用DELETE语句将其删除。
2. 聚合数据对于一些历史数据，可以根据需要将其进行聚合，以减少数据库的存储空间。
例如，可以将按天存储的数据聚合到按周、按月或者按年存储的数据中。
在聚合数据之后，可以删除原始的细粒度数据，以释放存储空间。
3. 数据归档对于一些历史数据，虽然可能不再被频繁访问，但是仍然需要保留。
这时可以考虑将这些数据归档到其他存储介质中，例如磁带库或者归档文件系统。
通过归档可以释放数据库的存储空间，同时仍然可以满足数据保留的需求。
数据归档方案数据归档是指将历史数据从Oracle数据库中移动到其他存储介质中，以解决存储空间不足的问题。
下面是一些常用的数据归档方案：1. 使用Oracle归档功能Oracle数据库提供了强大的归档功能，可以自动将历史数据归档到归档日志文件中。
通过配置适当的参数，可以调整归档的频率和方式。
归档日志文件可以保存在磁盘、磁带库或者归档文件系统中。
2. 使用第三方软件除了Oracle自带的归档功能，还可以使用一些第三方软件来进行数据归档。
这些软件通常具有更多的定制化和可扩展性，可以根据需要进行灵活的配置和管理。
例如，可以使用EMC Data Domain、IBM Tivoli Storage Manager等软件来进行数据归档。
Oracle 大数据量操作优化
如何加速Oracle大批量数据处理一、提高DML操作的办法：简单说来：１、暂停索引，更新后恢复.避免在更新的过程中涉及到索引的重建.２、批量更新，每更新一些记录后及时进行提交动作.避免大量占用回滚段和或临时表空间.３、创建一临时的大的表空间用来应对这些更新动作．4、批量更新，每更新一些记录后及时进行提交动作.避免大量占用回滚段和或临时表空间.5、创建一临时的大的表空间用来应对这些更新动作．6、加大排序缓冲区alter session set sort_area_size=100000000;insert into tableb select*from tablea;commit;如果UPDATE的是索引字段，就会涉及到索引的重建，暂停索引不会提高多少的速度，反而有可能降低UPDATE 速度，因为在更新是索引可以提高数据的查询速度，重建索引引起的速度降低影响不大。
ORACLE优化修改参数最多也只能把性能提高15%，大部分都是SQL语句的优化！update总体来说比insert要慢：几点建议：1、如果更新的数据量接近整个表，就不应该使用index而应该采用全表扫描2、减少不必要的index，因为update表通常需要update index3、如果你的服务器有多个cpu，采用parellel hint，可以大幅度的提高效率另外，建表的参数非常重要，对于更新非常频繁的表，建议加大PCTFREE的值，以保证数据块中有足够的空间用于UPDATE，从而降低CHAINED_ROWS。
二、各种批量DML操作：（1）、oracle批量拷贝：set arraysize20set copycommit5000copy from username/password@oraclenameappend table_name1using select*from table_name2;（2）、常规插入方式：insert into t1select*from t;为了提高速度可以使用下面方法，来减少插入过程中产生的日志:alter table t1nologging;insert into t1select*from t;commit;(3)、CTAS方式：create table t1asselect*from t;为了提高速度可以使用下面方法，来减少插入过程中产生的日志，并且可以制定并行度:create table t1nologging parallel(degree2)as select*from t;（4）、Direct-Path插入：insert/*+append*/into t1select*from t;commit;为了提高速度可以使用下面方法，来减少插入过程中产生的日志：alter table t1nologging;insert/*+append*/into t1select*from t;Direct-Path插入特点：1、append只在insert…select…中起作用，像insert/*+append*/into tvalues(…)这类的语句是不起作用的。
Oracle大批量删除数据方法
批量删除海量数据通常都是很复杂及缓慢的，方法也很多，但是通常的概念是:分批删除，逐次提交。
下面是删除过程，数据表可以通过主键删除，测试过Delete和For all两种方法，for all在这里并没有带来性能提高，所以仍然选择了批量直接删除。
首先创建一下过程，使用自制事务进行处理：create or replace procedure delBigTab(p_TableName in varchar2,p_Condition in varchar2,p_Count in varchar2)aspragma autonomous_transaction;n_delete number:=0;beginwhile 1=1 loopEXECUTE IMMEDIATE'delete from '||p_TableName||' where '||p_Condition||' and rownum <= :rn'USING p_Count;if SQL%NOTFOUND thenexit;elsen_delete:=n_delete + SQL%ROWCOUNT;end if;commit;end loop;commit;DBMS_OUTPUT.PUT_LINE('Finished!');DBMS_OUTPUT.PUT_LINE('Totally '||to_char(n_delete)||' records deleted!');end;以下是删除过程及时间:SQL> create or replace procedure delBigTab2 (3 p_TableName in varchar2,4 p_Condition in varchar2,5 p_Count in varchar26 )7 as8 pragma autonomous_transaction;9 n_delete number:=0;10 begin11 while 1=1 loop12 EXECUTE IMMEDIATE13 'delete from '||p_TableName||' where '||p_Condition||' and rownum <= :rn'14 USING p_Count;15 if SQL%NOTFOUND then16 exit;17 else18 n_delete:=n_delete + SQL%ROWCOUNT;19 end if;20 commit;21 end loop;22 commit;23 DBMS_OUTPUT.PUT_LINE('Finished!');24 DBMS_OUTPUT.PUT_LINE('Totally '||to_char(n_delete)||' records deleted!');25 end;26 /Procedure created.SQL> set timing onSQL> select min(NUMDLFLOGGUID) from HS_DLF_DOWNLOG_HISTORY;MIN(NUMDLFLOGGUID)------------------11000000Elapsed: 00:00:00.23SQL> exec delBigTab('HS_DLF_DOWNLOG_HISTORY','NUMDLFLOGGUID < 11100000','10000');PL/SQL procedure successfully completed.Elapsed: 00:00:18.54SQL> select min(NUMDLFLOGGUID) from HS_DLF_DOWNLOG_HISTORY;MIN(NUMDLFLOGGUID)------------------11100000Elapsed: 00:00:00.18SQL> set serveroutput onSQL> exec delBigTab('HS_DLF_DOWNLOG_HISTORY','NUMDLFLOGGUID < 11200000','10000'); Finished!Totally 96936 records deleted!PL/SQL procedure successfully completed.Elapsed: 00:00:18.6110万记录大约19sSQL> exec delBigTab('HS_DLF_DOWNLOG_HISTORY','NUMDLFLOGGUID < 11300000','10000'); Finished!Totally 100000 records deleted!PL/SQL procedure successfully completed.Elapsed: 00:00:18.62SQL> exec delBigTab('HS_DLF_DOWNLOG_HISTORY','NUMDLFLOGGUID < 11400000','10000'); Finished!Totally 100000 records deleted!PL/SQL procedure successfully completed.Elapsed: 00:00:18.85SQL>SQL> exec delBigTab('HS_DLF_DOWNLOG_HISTORY','NUMDLFLOGGUID < 13000000','10000');Finished!Totally 1000000 records deleted!PL/SQL procedure successfully completed.Elapsed: 00:03:13.87100万记录大约3分钟SQL> exec delBigTab('HS_DLF_DOWNLOG_HISTORY','NUMDLFLOGGUID < 20000000','10000');Finished!Totally 6999977 records deleted!PL/SQL procedure successfully completed.Elapsed: 00:27:24.69700万大约27分钟以上过程仅供参考.来源：网络编辑：联动北方技术论坛。
shared pool 清理机制
shared pool 清理机制共享池（Shared Pool）是Oracle数据库中的重要组件，用于缓冲共享的SQL和PL/SQL代码。
由于共享池是被所有用户共享的，所以在多用户环境中，共享池的使用可能会导致内存资源的争用，进而影响整个系统的性能。
为了解决这个问题，Oracle数据库引入了一种被称为共享池清理机制（Shared Pool Cleanup）的功能。
共享池清理机制的作用是在共享池内存资源紧张的情况下，清理掉一些不再使用的对象，从而释放内存空间给其他正在使用的对象。
共享池清理机制的实现主要包括以下几个步骤：1. 标记（Marking）：在共享池空间不足时，Oracle会遍历共享池中的所有内存块，标记那些被使用的内存块和那些未被使用的内存块。
这一步骤主要是为了确定哪些对象是可以被清理的。
2. 移动（Moving）：在标记完成后，Oracle会将已经清理标记的未使用内存块从共享池中移除，并将内存空间释放给操作系统。
移动操作一般采用“逐个或批量”方式进行，根据需求释放不同大小的内存块。
3. 刷新（Refreshing）：在移动操作完成后，Oracle会刷新共享池中剩余的内存块，使其重新合理地分配给需要使用的对象。
这一步骤可以提高共享池的利用率和性能。
需要注意的是，共享池清理机制是自动执行的，Oracle会根据预设的内存阈值和LRU（Least Recently Used）算法自动触发并执行清理操作。
同时，也可以通过调整初始化参数来调整清理机制的行为，如设置共享池的大小、调整内存阈值等。
总而言之，共享池清理机制是Oracle数据库中的一项重要功能，可以有效地管理共享池的内存资源，提高系统的性能和效率。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
Oracle连接数过多释放机制年07月05日18:07:21∙14532Oracle服务器连接数过多会当掉，把连接数过多的客户机网线拔出后，在远程Oracle上依然还会保留此用户的连接数，久久不能释放，上网查了下可以以下面方法解决。
通过profile可以对用户会话进行一定的限制，比如IDLE时间。
将IDLE超过一定时间的会话断开，可以减少数据库端的会话数量，减少资源耗用。
使用这些资源限制特性，需要设置resource_limit为TRUE：[oracle@test126 udump]$ sqlplus "/ as sysdba"SQL*Plus: Release 10.2.0.1.0 - Production on Fri Oct 13 07:58:21 2006Copyright (c) 1982, 2005, Oracle. All rights reserved.Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProductionWith the Partitioning and Data Mining optionsSQL> show parameter resourceNAME TYPE VALUE------------------------------------ -----------------------------------------resource_limit boolean TRUEresource_manager_plan string该参数可以动态修改：SQL> alter system set resource_limit=true;System altered.数据库缺省的PROFILE设置为：SQL> SELECT * FROM DBA_PROFILES;PROFILE RESOURCE_NAME RESOURCE LIMIT-------------------- -------------------------------- -----------------------DEFAULT COMPOSITE_LIMIT KERNEL UNLIMITED DEFAULT SESSIONS_PER_USER KERNEL UNLIMITEDDEFAULT CPU_PER_SESSION KERNEL UNLIMITED DEFAULT CPU_PER_CALL KERNEL UNLIMITEDDEFAULT LOGICAL_READS_PER_SESSION KERNEL UNLIMI TEDDEFAULT LOGICAL_READS_PER_CALL KERNEL UNLIMITE DDEFAULT IDLE_TIME KERNEL UNLIMITED DEFAULT CONNECT_TIME KERNEL UNLIMITED DEFAULT PRIVATE_SGA KERNEL UNLIMITED DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD 10 DEFAULT PASSWORD_LIFE_TIME PASSWORDUNLIMITEDPROFILE RESOURCE_NAME RESOURCE LIMIT-------------------- -------------------------------- -----------------------DEFAULT PASSWORD_REUSE_TIME PASSWORD UNLIMITEDDEFAULT PASSWORD_REUSE_MAX PASSWORD UNLIMITEDDEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD NULLDEFAULT PASSWORD_LOCK_TIME PASSWORD UNLIMITEDDEFAULT PASSWORD_GRACE_TIME PASSWORD UNLIMITED16 rows selected.创建一个允许3分钟IDLE时间的PROFILE：SQL> CREATE PROFILE KILLIDLE LIMIT IDLE_TIME 3;Profile created.新创建PROFILE的内容：SQL> col limit for a10SQL> select * from dba_profiles where profile='KILLIDLE';PROFILE RESOURCE_NAME RESOURCE LIMIT------------------------------ -------------------------------- -------- ----------KILLIDLE COMPOSITE_LIMIT KERNEL DEFAULTKILLIDLE SESSIONS_PER_USER KERNEL DEFAUL TKILLIDLE CPU_PER_SESSION KERNEL DEFAULTKILLIDLE CPU_PER_CALL KERNEL DEFAULTKILLIDLE LOGICAL_READS_PER_SESSION KERNEL DEF AULTKILLIDLE LOGICAL_READS_PER_CALL KERNEL DEFA ULTKILLIDLE IDLE_TIME KERNEL 3 KILLIDLE CONNECT_TIME KERNEL DEFAULTKILLIDLE PRIVATE_SGA KERNEL DEFAULTKILLIDLE FAILED_LOGIN_ATTEMPTS PASSWORD DEFAULTKILLIDLE PASSWORD_LIFE_TIME PASSWORD DEFAULTPROFILE RESOURCE_NAME RESOURCE LIMIT------------------------------ -------------------------------- -------- ----------KILLIDLE PASSWORD_REUSE_TIME PASSWORD DEFAULTKILLIDLE PASSWORD_REUSE_MAX PASSWORD DEFAULTKILLIDLE PASSWORD_VERIFY_FUNCTION PASSWORD DEFAULTKILLIDLE PASSWORD_LOCK_TIME PASSWORD DEFAULTKILLIDLE PASSWORD_GRACE_TIME PASSWORD DEFAULT16 rows selected.测试用户：SQL> select username,profile from dba_users whereusername='EYGLE';USERNAME PROFILE------------------------------ --------------------EYGLE DEFAULT修改eygle用户的PROFILE使用新建的PROFILE：SQL> alter user eygle profile killidle;User altered.SQL> select username,profile from dba_users where username='EYGLE';USERNAME PROFILE------------------------------ --------------------EYGLE KILLIDLE进行连接测试：[oracle@test126 admin]$ sqlplus eygle/eygle@eygleSQL*Plus: Release 10.2.0.1.0 - Production on Fri Oct 13 08:07:13 2006Copyright (c) 1982, 2005, Oracle. All rights reserved.Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProductionWith the Partitioning and Data Mining optionsSQL> select username,profile from dba_users whereusername='EYGLE';USERNAME PROFILE------------------------------ ------------------------------EYGLE KILLIDLE当IDLE超过限制时间时，连接会被断开：SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual; TO_CHAR(SYSDATE,'YY-------------------2006-10-13 08:08:41SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual; select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual*ERROR at line 1:ORA-02396: exceeded maximum idle time, please connect again1.sqlplus /nolog2.打开sqlplus3.4.5.connect system/bianqiwei@orcltns as sysdba6.使用具有dba权限得用户登陆oracle7.8.9.show parameter resource_limit10.显示资源限定是否开启,value为true是开启,为false是关闭11.12.13.alter system set resource_limit=true14.如果未开启,则使用此命令开启资源限定功能15.16.17.create profile profileName limit connect_time 60 idle_time 3018.创建profile文件,profileName任意起,connect_time设置连接超过多少分钟后强制释放,idle_time设置连续不活动的会话超过多少分钟后强制释放19.20.alter user oracleUser profile profileName21.将profile文件作用于指定用户Oracle session连接数和inactive的问题记录oracle学习2009-03-1015:42:37 阅读317 评论0 字号：大中小订阅 .从上周起，服务器Oracle数据库出现问题，用不到半天，就会报maxsession （150）的问题，肯定是数据库的会话超过最大数了。