几个常用的数据库操作以及视图

一.查看表空间剩余百分比

SELECT tablespace_name, TO_CHAR

(100*sum_free_blocks/sum_alloc_blocks, '999.99')

AS percent_free

FROM

(SELECT tablespace_name, SUM(blocks)

AS sum_alloc_blocks

FROM dba_data_files

GROUP BY tablespace_name),

(SELECT tablespace_name

AS fs_ts_name,MAX(blocks)

AS max_blocks,

COUNT(blocks)

AS count_blocks, SUM(blocks) AS sum_free_blocks

FROM dba_free_space

GROUP BY tablespace_name)

WHERE tablespace_name = fs_ts_name

ORDER BY percent_free DESC;

 

涉及的视图有：dba_free_space  dba_data_files

 

二.查看当前sessions的sid,pid,以及serial#等

select p.pid, p.spid, s.sid, s.serial# from v$session s,v$process p

where s.sid = (select sid from v$mystat where rownum = 1) and p.addr = s.paddr

 

涉及的视图有：v$session v$process v$mystat

 

三.查看回滚段与数据段信息的一些SQL

1.系统实际UNDO表空间占用率可以使用如下语句来计算。

select  ((select (nvl(sum(bytes),0)) from dba_undo_extents where tablespace_name ='UNDOTBS1'  and status in ('ACTIVE','UNEXPIRED')) *100) / (select sum(bytes)  from dba_data_files  where tablespace_name='UNDOTBS1')  "PCT_INUSE"  from dual;

PCT_INUSE

----------

    27.575

将UNEXPIRED，ACTIVE两种状态的回滚段一起算上，实际现场的UNDO占用率为27.575%。

 

2.查看表空间的使用情况

SELECT A.TABLESPACE_NAME,A.BYTES/1024/1024 TOTAL,B.BYTES/1024/1024 USED, C.BYTES/1024/1024 FREE,

 (B.BYTES*100)/A.BYTES "% USED",(C.BYTES*100)/A.BYTES "% FREE"

 FROM SYS.SM$TS_AVAIL A,SYS.SM$TS_USED B,SYS.SM$TS_FREE C

 WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME AND A.TABLESPACE_NAME=C.TABLESPACE_NAME;

 

3.查看回滚段的状况

select rownum, sys.dba_rollback_segs.segment_name Name, v$rollstat.extents

Extents, v$rollstat.rssize Size_in_Bytes, v$rollstat.xacts XActs,

v$rollstat.gets Gets, v$rollstat.waits Waits, v$rollstat.writes Writes,

sys.dba_rollback_segs.status status

  from v$rollstat, sys.dba_rollback_segs, v$rollname

where v$rollname.name(+) = sys.dba_rollback_segs.segment_name and

v$rollstat.usn (+) = v$rollname.usn order by rownum;

 

4.查看数据段的信息

select * from dba_segments where tablespace_name ='UNDOTBS1'

 

相关视图：

select * from dba_rollback_segs

select * from v$rollstat

select * from v$rollname

select * from dba_undo_extents

select * from dba_segments where tablespace_name ='UNDOTBS1'

 

涉及的视图有：dba_rollback_segs v$rollstat v$rollname dba_undo_extents  dba_segments

 

4.定位高耗资源的SQL

select sql_text,

       sharable_mem,persistent_mem,runtime_mem,

       sorts,

       users_opening,

       executions,

       loads,

       parse_calls,

       disk_reads,buffer_gets,rows_processed,

       optimizer_mode,address,hash_value

from v$sql

order by disk_reads desc;

 

 

根据上述语句的输出的sql_text定位执行对应sql的Oracle进程ID：

 

select a.sid,a.osuser "程序用户",a.process "程序ID",

       a.username "ORA用户" ,a.terminal "P终端",

       b.spid "后台ID",b.username "后台用户",

       b.terminal "B终端"

  from v$session a,v$process b

 where a.paddr = b.addr and (a.sql_address,a.sql_hash_value) in (

 select address,hash_value from v$sqltext where

 upper(sql_text) like '%ZXDBM_ISMP.SYNC_TASK%'

 )

 

'%ZXDBM_ISMP.SYNC_TASK%' 部分使用第一个sql查询出来的sql_text代替；

查询结果中的spid "后台ID" 就是执行该sql对应的oracle进程的pid(oracle服务器上的oracle进程)。如果确认高耗资源的sql可以中止执行以释放系统资源，需要在数据库服务器上使用oracle用户将查询出来的spid 杀掉（kill -9 spid）。如果高耗资源的sql执行了表的增、删、改操作，也需要kill掉对应的oracle后台进程以释放这些表的锁资源；

 

涉及的视图有：v$sql  v$session  v$process  v$sqltext

 

五.修改数据库的连接数

修改Oracle数据库的连接数

 

1. 查看processes和sessions参数

SQL> show parameter processes

　　NAME                                  TYPE         VALUE

　　db_writer_processes                  integer     1

　　gcs_server_processes                 integer     0

　　job_queue_processes                  integer     10

　　log_archive_max_processes            integer     2

　　processes                            integer     50

SQL> show parameter sessions

　　NAME                                  TYPE         VALUE

　　license_max_sessions                 integer     0

　　license_sessions_warning             integer     0

　　logmnr_max_persistent_sessions       integer     1

　　sessions                             integer     60

　　shared_server_sessions               integer

2. 修改processes和sessions值

　　SQL> alter system set processes=300 scope=spfile;

　　系统已更改。

　　SQL> alter system set sessions=335 scope=spfile;

　　系统已更改。

3. 修改processes和sessions值必须重启oracle服务器才能生效

　　ORACLE的连接数(sessions)与其参数文件中的进程数(process)有关，它们的关系如下：

　　sessions=(1.1*process+5)

 

查询数据库当前进程的连接数：

　　select count(*) from v$process;

查看数据库当前会话的连接数：

　　select count(*) from v$session;

查看数据库的并发连接数：

　　select count(*) from v$session where status='ACTIVE';

查看当前数据库建立的会话情况：

　　select sid,serial#,username,program,machine,status from v$session;

查询数据库允许的最大连接数：

　　select value from v$parameter where name = 'processes';

　　或者：show parameter processes;

修改数据库允许的最大连接数：

　　alter system set processes = 300 scope = spfile;

　　(需要重启数据库才能实现连接数的修改)

重启数据库：

　　shutdown immediate;

　　startup;

查看当前有哪些用户正在使用数据：

　　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

　　order by cpu_time/executions desc;

　　备注：UNIX 1个用户session对应一个操作系统process，而Windows体现在线程。

　　启动oracle

　　su - oracle

　　sqlplus system/pwd as sysdba   //进入sql

　　startup                                      //启动数据库

　　lsnrctl start                               //启动监听

　　sqlplus "/as sysdba"

　　shutdown immediate;

　　startup mount;

　　alter database open;

 

6.查询数据库对象锁定的情况

--查看某个表被谁锁定

select t2.username, t2.sid, t2.serial#, t2.logon_time, t3.object_name

  from v$locked_object t1, v$session t2, dba_objects t3

 where t1.session_id = t2.sid

   and t1.object_id = t3.object_id

 order by t2.logon_time;

 

--杀掉进程

alter system kill session 'sid,serial#';  ---sid,serial#对应上面语句查出的value

 

涉及的视图有：v$locked_object  v$session  dba_objects

锁的情况在测试环境中出现了很多次了，建议每次业务由通变成不通时，用这个SQL查看下是不是因为某个表被锁而导致的。