http://blog.csdn.net/xjzdr
查看Oracle的所有连接:
select * from v$session where username is not null
查看不同用户的连接数量:
select username,count(username) from v$session where username is not null group by username
查看哪个过程被锁:
SELECT * FROM V$DB_OBJECT_CACHE WHERE OWNER='过程的所属用户' AND LOCKS!='0';
查看锁定数据库的用户:
select * from v$locked_object;
查看没有提交的事务:
select * from v$transaction;--从这句也可以查看锁定数据库的用户
获得当前数据库的session数及数据库启动以来最大的session数
select sum(sessions_current) cur_sessions,sum(sessions_highwater) high_sessions from V$LICENSE;
查看某个表所在的表空间名:
select tablespace_name from user_tables where table_name='BILL_ORG'--说明:表名必须大写
查看数据文件存放的路径:
select tablespace_name,file_id,bytes/1024/1024,file_name
from dba_data_files order by file_id
查看当前用户下的所有表:
select * from all_tables where owner='YSPJ'
查看运行过的SQL语句:
SELECT SQL_TEXT FROM V$SQL
查看客户端机器名、IP地址、登录客户端的用户:
SELECT
SYS_CONTEXT('USERENV','TERMINAL') TERMINAL,
SYS_CONTEXT('USERENV','HOST') HOST,
SYS_CONTEXT('USERENV','OS_USER') OS_USER,
SYS_CONTEXT('USERENV','IP_ADDRESS') IP_ADDRESS
FROM DUAL
查看某个SID执行的SQL语句的运行情况:
select command_type,sql_text,sharable_mem,persistent_mem,runtime_mem,
sorts,version_count,loaded_versions,open_versions,users_opening,executions,users_executing,loads,
first_load_time,invalidations,parse_calls,disk_reads,buffer_gets,rows_processed,sysdate start_time,
sysdate finish_time,'>' || address sql_address,'N' status
from v$sqlarea
where address = (select sql_address from v$session where sid =11)
查看某个表所占用的空间的大小:
select sum(bytes)/(1024*1024) as "size(M)" from user_segments where segment_name=upper('BILL_MAIN');
查看某个存储过程的源代码:
select text from all_source where owner=user and name=upper('month_BenYueJieYu_2');
查看当前数据徊的用户的SID:
select username,sid,serial# from v$session;
将当前某个用户的连接杀死:比如杀死YSPJ用户的连接:
先执行alter system kill session 'sid,serial#';
根据返回的SID和serial,执行下面的语句:
alter system kill session 'sid,serial#';--其中SID和serial为上面的SQL语句返回的值。
授予权限:
GRANT CONNECT, RESOURCE TO 用户名;
GRANT SELECT ON 表名 TO 用户名;
GRANT SELECT, INSERT, DELETE ON表名 TO 用户名1, 用户名2;
回收权限
REVOKE CONNECT, RESOURCE FROM 用户名;
REVOKE SELECT ON 表名 FROM 用户名;
REVOKE SELECT, INSERT, DELETE ON表名 FROM 用户名1, 用户名2;
修改数据库的系统日期:
alter session set nls_date_format='yyyymmddhh24miss';
查看日志文件的存放路径:
select member from v$logfile
查看表空间的使用情况:
select sum(bytes)/(1024*1024) as free_space,tablespace_name
from dba_free_space
group by tablespace_name;
查看数据库的大小和表空间的使用情况:
select b.file_id 文件ID,
b.tablespace_name 表空间,
b.file_name 物理文件名,
b.bytes 总字节数,
(b.bytes-sum(nvl(a.bytes,0))) 已使用,
sum(nvl(a.bytes,0)) 剩余,
sum(nvl(a.bytes,0))/(b.bytes)*100 剩余百分比
from dba_free_space a,dba_data_files b
where a.file_id=b.file_id
group by b.tablespace_name,b.file_name,b.file_id,b.bytes
order by b.tablespace_name
查看连接池中的SQL语句:
select sql_text from v$sqlarea
查看Oracle的版本:
select * from v$version
监控是哪个用户在运行SQL语句:
SELECT osuser, username, sql_text from v$session a, v$sqltext b
where a.sql_address =b.address order by address, piece;
查看ORACLE所使用的字符集:
select * from sys.props$ where name='NLS_CHARACTERSET';
改数据库全局名称,建公共的数据库链接。
①、用system身份登录ying数据库
alter database rename global_name to ying.test.com.cn
用system身份登录orcl数据库:
alter database rename global_name to orcl.test.com.cn