oracle学习日记

1. 查询当前有多少连接数
select count(*) from v$process
2. 数据库允许的最大连接数
select value from v$parameter where name = 'processes'
3. 查看当前有哪些用户正在使用数据
SELECT osuser, a.username,cpu_time/executions/1000000||'s', sql_fulltext,machine
from v$session a, v$sqlarea b
where a.sql_address=b.address
4. 修改最大连接数
alter system set processes=300 scope=spfile;
5. 重启数据库
shut down immediate;
startup;
5. 设置分页
select * from ( select row_.*, rownum rownum_ from ( select dism.* from DISM_MEAS_TYPE dism ) row_ where rownum <= ?) where rownum_ > ?
6. 拷贝表结构或数据(where 后面条件是否成立)
create table dis_common.CIM_SUBCONTROLAREA as select * from  SJJC_OMS_KDNRCIM.CIM_SUBCONTROLAREA where 1=2
insert into DIS_COMMON.CIM_SUBCONTROLAREA select * from SJJC_OMS_KDNRCIM.CIM_SUBCONTROLAREA
7. 修改密码
sqlplus /nolog 这句话是说不登陆数据库，只启动sqlplus

SQL> conn /as sysdba
已连接。
SQL> alter user system identified by password;
SQL> alter user sys identified by password;

8  exp 导出数据
C:\Users\mxs>exp sjjc_szd/sjjc@orcl0.155 file=d:\temp\sjjc_szd.dmp owner=sjjc_szd

导入数据：

C:\Users\mxs>imp sjjc_szd/sjjc@orcl0.155 fromuser=sjjc_szd touser=sjjc_szd file=d:\temp\sjjc_szd.dmp

9.  查询解析次数
select *
  from (SELECT hash_value,
               sql_text,
               executions,
               buffer_gets,
               disk_reads,
               parse_calls
          FROM V$SQLAREA
         ORDER BY buffer_gets + 100 * disk_reads DESC)
where sql_text like
       '%DISS_PSR_ATTR%';
      
       SELECT substr(sql_text, 1, 40) "SQL",
       count(*),
       sum(executions) "TotExecs"
  FROM v$sqlarea
WHERE executions < 5 --语句执行次数
GROUP BY substr(sql_text, 1, 40)
HAVING count(*) > 30 --所有未共享的语句的总的执行次数
ORDER BY 2;

10
查看共享SQL区的使用率：
select(sum(pins-reloads))/sum(pins) "Library cache" from v$librarycache;--动态性能表
查看数据字典缓冲区的使用率：
select (sum(gets-getmisses-usage-fixed))/sum(gets) "Data dictionary cache" from v$rowcache;
这个使用率也应该在90％以上，否则需要增加共享池的大小。

修改共享池的大小:
ALTER SYSTEM SET SHARED_POOL_SIZE = 64M  scope=spfile; 

缓冲区高速缓存：

SELECT name,value FROM v$sysstat order by name WHERE name IN(''DB BLOCK GETS'',''CONSISTENT GETS'',''PHYSICAL READS'');

计算出来数据缓冲区的使用命中率＝1-(physical reads/(db block gets+consistent gets)),这个命中率应该在90％以上，否则需要增加数据缓冲区的大小。


11  修改缓冲区大小

alter system set shared_pool_size=100m scope=spfile;
alter system set db_cache_size=100m scope=spfile;

12.查询是否存在死锁
select sess.sid,
   sess.serial#,
   lo.oracle_username,
   lo.os_user_name,
   ao.object_name,
   lo.locked_mode
   from v$locked_object lo,
   dba_objects ao,
   v$session sess
where ao.object_id = lo.object_id and lo.session_id = sess.sid;

select * from v$session t1, v$locked_object t2 where t1.sid = t2.SESSION_ID;
--杀死
alter system kill session '452';