--统计会话情况：
SELECT 'TOTAL SESSIONS NUM IS: '||COUNT(*) FROM v$session
UNION ALL
SELECT 'ACTIVE SESSIONS NUM IS: '||COUNT(*) FROM v$session WHERE status ='ACTIVE'
UNION ALL
SELECT 'USER SESSIONS NUM IS : '||COUNT(*) FROM V$SESSION WHERE TYPE='USER'
UNION ALL
SELECT 'BACKGROUND SESSIONS NUM IS: '||COUNT(*) FROM V$SESSION WHERE TYPE='BACKGROUND';


--当前连接数，各个服务器分布
select machine,
       count(*),
       (select count(*) from v$session where username IS NOT NULL) TOTAL_SESSION
  from v$session
 where USERNAME IS NOT NULL
 group by machine
 ORDER BY MACHINE;



-查看某个时间DML情况

select to_char(b.begin_interval_time, 'yyyy-mm-dd hh24:mi:ss') update_time,
       c.sql_text sql_text
  from dba_hist_active_sess_history a, DBA_HIST_SNAPSHOT b, v$sqlarea c
 where a.snap_id = b.snap_id
   and a.sql_id = c.sql_id
   and b.begin_interval_time >= to_date('2015-07-08 11:00:00', 'yyyy-mm-dd hh24:mi:ss')
   and b.begin_interval_time < to_date('2015-07-08 11:20:00', 'yyyy-mm-dd hh24:mi:ss')
   and lower(c.sql_text) like '%insert%'
 order by update_time;


查看一个用户所有的权限及角色
select privilege from dba_sys_privs where grantee='ZABORA' 
union
select privilege from dba_sys_privs where grantee in 
(select granted_role from dba_role_privs where grantee='WEBDATAXXX' ); 


查看等待事件：
 SELECT EVENT, COUNT(*) FROM GV$SESSION GROUP BY EVENT HAVING COUNT(*) > 5 ORDER BY 2 DESC;
 SELECT 'kill -9 ' || spid from v$session s, v$process p where s.username = 'USER2' and s.paddr = p.addr and event = 'latch: cache buffers chains';

等待事件分析
 SELECT event, COUNT (*)
FROM DBA_HIST_ACTIVE_SESS_HISTORY
WHERE
sample_time >=
TO_DATE ('2015-07-28 11:20:00','yyyy-mm-dd hh24:mi:ss')
AND sample_time <
TO_DATE ('2015-07-28 11:30:00','yyyy-mm-dd hh24:mi:ss')
-- AND EVENT='row cache lock'
GROUP BY event
ORDER BY 2 DESC;

看等待的内容
col event for a30
col p1text for a10
SELECT  event,p1text,p1, B.PARAMETER , count(*) 
FROM DBA_HIST_ACTIVE_SESS_HISTORY a,V$ROWCACHE b 
WHERE  sample_time >=TO_DATE ('2015-07-28 11:00:00','yyyy-mm-dd hh24:mi:ss') 
AND sample_time <TO_DATE ('2015-07-28 11:20:00','yyyy-mm-dd hh24:mi:ss')
AND EVENT='row cache lock' 
and a.p1=B.CACHE# 
group by event,p1text,p1, B.PARAMETER ;

回滚段的等待
SELECT  sql_id,count(*)
FROM DBA_HIST_ACTIVE_SESS_HISTORY
WHERE  sample_time >=
TO_DATE ('2015-07-28 11:00:00','yyyy-mm-dd hh24:mi:ss')
AND sample_time <
TO_DATE ('2015-07-28 11:00:00','yyyy-mm-dd hh24:mi:ss')
AND EVENT='row cache lock'
and p1=3
group by sql_id
order  by 2 desc ;


---表空间使用率：

select a.tablespace_name,total,free,total-free used from 
( select tablespace_name,sum(bytes)/1024/1024 total from dba_data_files
   group by tablespace_name) a, 
( select tablespace_name,sum(bytes)/1024/1024 free from dba_free_space
   group by tablespace_name) b  
where a.tablespace_name=b.tablespace_name;

set line 300
select total.tablespace_name,
round(total.MB, 2) as Total_MB,
round(total.MB - free.MB, 2) as Used_MB,
round((1 - free.MB / total.MB) * 100, 2) || '%' as Used_Pct 
from (select tablespace_name, sum(bytes) / 1024 / 1024 as MB 
from dba_free_space 
group by tablespace_name) free,
(select tablespace_name, sum(bytes) / 1024 / 1024 as MB 
from dba_data_files 
group by tablespace_name) total 
where free.tablespace_name = total.tablespace_name;


--扩表空间：
alter database datafile '+DATA/qijia/datafile/dpm001.dbf' resize 10g;



主键冲突检查：
select owner,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,R_CONSTRAINT_NAME 
from dba_CONSTRAINTS where CONSTRAINT_NAME like 'PK_TM_MEMBER_IDENTITY_PID';

select column_name,position from dba_cons_columns where constraint_name='PK_TM_MEMBER_IDENTITY_PID';
select constraint_name,constraint_type,status,validated from dba_constraints where table_name='TM_MEMBER_IDENTITY' and owner='MEMBER';


查看表、分区
select  TABLE_OWNER,TABLE_NAME,PARTITION_NAME from  DBA_TAB_PARTITIONS where TABLE_OWNER='PAYMENT' and TABLE_NAME='TB_SETTLEMENT_ORDER';

查看分区表某个分区的索引状态
  select index_name,partition_name,status from dba_ind_partitions 
  where index_owner='PAYMENT' and PARTITION_NAME='PA_201412';


  select index_name,partition_name,status from dba_ind_partitions 
  where index_owner='PAYMENT';


--查表的索引
select index_name,table_name,column_name,column_position from dba_ind_columns where table_name='CUR_OPEN_PLAYITEM' and TABLE_OWNER='WEBDATA';

--表的数据信息：
select table_name, num_rows from dba_tables  where owner = 'WEBDATA' ORDER BY NUM_ROWS DESC; 



添加表注释：
COMMENT ON table t1 IS '个人信息';


添加字段注释：
comment on column t1.id  is 'id';
comment on column t1.nameis '姓名';
comment on column t1.age is '年龄';

--修改用户的密码
select * from dba_users where username='TAOKAUSER';
alter user TAOKAUSER identified by sasa;


--授权
grant all on dcs.dcs_category to bafapps;--  dcs.dcs_category（用户名.表名）  bafapps接收权限的用户名
grant all on dcs.dcs_category_tl to bafapps;
grant alert table on tablename to zhangsan with admin option;//关键字 with admin option

--创建同义词
create synonym dcs_category for dcs.dcs_category;
create synonym dcs_category_tl for dcs.dcs_category_tl;


--创建表空间


CREATE TABLESPACE TaoKa DATAFILE 
  'D:\oracle\product\10.2.0\oradata\orcl\TaoKa' SIZE 1M AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT MANUAL;


// 创建一个序列
CREATE SEQUENCE Car_GUID increment by 1;


// 创建一个表
CREATE TABLE Car
(
 GUID NUMBER NOT NULL PRIMARY KEY,
 PhoneId Char(11) NOT NULL,
 UserName VarChar(20) NOT NULL
);


// 添加注释
comment on table car is '定位信息用户表';
Comment on column car.guid is '自增字段';
Comment on column Car.PhoneId is '电话唯一标识';
Comment on column Car.UserName is '用户名称';