oracle相关操作

查询锁表
SELECT /*+ rule */ lpad(' ',decode(l.xidusn ,0,3,0))||l.oracle_username User_name,
o.owner,o.object_name,o.object_type,s.sid,s.serial#
FROM v$locked_object l,dba_objects o,v$session s
WHERE l.object_id=o.object_id
AND l.session_id=s.sid
ORDER BY o.object_id,xidusn DESC;
解锁 'sid,serial'
alter system kill session '553,6';

查询正在执行的存储过程：
select name from v$db_object_cache where locks > 0 and pins > 0 and type='PROCEDURE'

查询某个表空间里所有表的大小：
select sum(d.bytes) / 1024 / 1024 / 1024 || 'G' as total_bytes,
d.tablespace_name
from dba_data_files d
where d.tablespace_name = 'BASE01'
group by d.tablespace_name

查询表空间数据文件名及其大小：
select t.file_name,t.bytes from dba_data_files t where t.tablespace_name='UNDOTBS1';
查询某个表空间里的表：
select table_name,tablespace_name from user_tables where tablespace_name='UNDOTBS1';

查询表空间使用情况
select df.tablespace_name
"Tablespace",df.bytes/(1024*1024)
"Total Size(MB)",
sum(fs.bytes)/(1024*1024) "Free
Size(MB)",
round(sum(fs.bytes)*100/df.bytes)
"% Free",
round((df.bytes-sum(fs.bytes))*100/
df.bytes) "% Used"
from dba_free_space fs,
(select tablespace_name,
sum(bytes) bytes from
dba_data_files group by
tablespace_name ) df
where fs.tablespace_name(+) =
df.tablespace_name
group by df.tablespace_name,
df.bytes;

查询数据库连接数
select count(*) from v$process -- 数据库当前连接数
select value from v$parameter where name = 'processes' – 数据库所允许的最大连接数

查看服务器上的数据库：oracle用户登陆服务器，sqlplus '/as sysdba'，select username from dba_users;