oracle 常用系统函数及操作

//---------------常用函数---------------
//TO_CHAR 把日期或数字转换为字符串
TO_CHAR(T1.UPDATE_TIME,'YYYY-MM-DD')=TO_CHAR(SYSDATE,'YYYY-MM-DD')

//------Round 函数 (四舍五入)
ROUND(ARERAGE_SPEED,1)

//------ROW_NUMBER() OVER  函数
SELECT T1.*, ROW_NUMBER() OVER (PARTITION BY T1.INFO_ID ORDER BY

T1.UPDATE_TIME DESC ) RN FROM RTT_TRAFFIC_INDEX_T T1

//------查看数据表结构
select * from user_col_comments t where t.table_name = 'RTT_TRAFFIC_INDEX_T'

//------查询oracle的连接数
select count(*) from v$session;

//------查询oracle的并发连接数
select count(*) from v$session where status='ACTIVE';

//------查看不同用户的连接数
select username,count(username) from v$session where username is not null group by username

//------查看所有用户：
select * from all_users;

//------查看用户或角色系统权限(直接赋值给用户或角色的系统权限)：
select * from dba_sys_privs;
select * from user_sys_privs;

//------查看角色(只能查看登陆用户拥有的角色)所包含的权限
select * from role_sys_privs;

//------查看用户对象权限：
select * from dba_tab_privs;
select * from all_tab_privs;
select * from user_tab_privs;

//------查看所有角色：
select * from dba_roles;

//------查看用户或角色所拥有的角色：
select * from dba_role_privs;
select * from user_role_privs;

//------查看哪些用户有sysdba或sysoper系统权限(查询时需要相应权限)
select * from V$PWFILE_USERS;

//------修改数据库允许的最大连接数：
alter system set processes = 300 scope = spfile;

//------查看游标数量
Select * from v$open_cursor Where user_name=''
 
//------查询数据库允许的最大连接数：
select value from v$parameter where name = 'processes';
 
//------查询数据库允许的最大游标数：
select value from v$parameter where name = 'open_cursors'

//------查看oracle版本
select banner from sys.v_$version;

//------按降序显示用户"SYSTEM"为每个会话打开的游标数

select o.sid, osuser, machine, count(*) num_curs  from v$open_cursor o,

v$session s  where user_name = 'SYSTEM' and o.sid=s.sid   group by o.sid,

osuser, machine  order by num_curs desc;

//------查看所有用户：
select * from all_users;
//------查看用户或角色系统权限(直接赋值给用户或角色的系统权限)：
 select * from dba_sys_privs;
 select * from user_sys_privs;
//------查看角色(只能查看登陆用户拥有的角色)所包含的权限
select * from role_sys_privs;
//------查看用户对象权限：
 select * from dba_tab_privs;
 select * from all_tab_privs;
 select * from user_tab_privs;
//------查看所有角色：
 select * from dba_roles;
//------查看用户或角色所拥有的角色：
 select * from dba_role_privs;
 select * from user_role_privs;
//------查看哪些用户有sysdba或sysoper系统权限(查询时需要相应权限)
 select * from V$PWFILE_USERS;
//-----当前的连接数
select count(*) from v$process
//------数据库允许的最大连接
select value from v$parameter where name = '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;
//------#连接数
select count(*) from v$session
//------
select count(*) from v$session where status='ACTIVE'　
//------#并发连接数
show parameter processes
//------重启数据库 #修改连接
alter system set processes = value scope = spfile;

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

//------#连接数
select count(*) from v$session
//------#并发连接数
Select count(*) from v$session where status='ACTIVE'　
//------重启数据库 #修改连接
alter system set processes = value scope = spfile

//------连接数量
SELECT username, machine, program, status, COUNT (machine) AS
FROM v$session
GROUP BY username, machine, program, status
ORDER BY machine;

//------按主机名查询
SELECT COUNT(*) FROM V$SESSION WHERE MACHINE = 'DXMH';      'DXMH'为主机名
 
//------数据恢复语句
create table informationlaw_bak
as
select * from informationlaw as of TIMESTAMP to_timestamp('20121126103435','yyyymmdd hh24miss');
 
//------按机器名分组查
select username,machine,count(username) from v$session where username is not
null group by username,machine;

//------杀死进程，创建索引。

select session_id from v$locked_object;
SELECT sid, serial#, username, osuser FROM v$session where sid = 138;
ALTER SYSTEM KILL SESSION '138,21332';
create index index_traffic_updateTime on RTT_TRAFFIC_INDEX_T(UPDATE_TIME)
 
create index index_name on table_name(column_name)

//------查看连接用户明细
SELECT /*+ rule */
s.username,
decode(l.type, 'TM', 'TABLE LOCK', 'TX', 'ROW LOCK', NULL) LOCK_LEVEL,
o.owner,
o.object_name,
o.object_type,
s.sid,
s.serial#
FROM gv$session s, gv$lock l, dba_objects o
WHERE l.sid = s.sid
AND l.id1 = o.object_id(+)
AND s.username is NOT NULL;