查询当前连接数:
select count(*) from v$session
SELECT username, machine, program, status, COUNT (machine) AS
连接数量
FROM v$session WHERE USERNAME = 'COTSDEV'
GROUP BY username, machine, program, status
ORDER BY machine;
查询表空间使用情况
select a.tablespace_name,a.bytes/1024/1024 "sum MB",
(a.bytes-b.bytes)/1024/1024 "used MB",b.bytes/1024/1024 "free MB",
round (((a.bytes-b.bytes)/a.bytes)*100,2) "used%" from
(select tablespace_name,sum(bytes) bytes from dba_data_files group by tablespace_name) a,
(select tablespace_name,sum(bytes) bytes,max (bytes) largest from dba_free_space group by tablespace_name)b
where a.tablespace_name=b.tablespace_name
order by ((a.bytes-b.bytes)/a.bytes) desc;
oracle用户资源配额
第一步: -- 在创建用户的时候,就指定用户在特定表空间上的配额
create user user_name_xxx identified by pwd_xxxxx default tablespace users(表空间) temporary tablespace temp(临时表空间) quota 200G on users;
第二步:/* 查看用户表空间的限额 max_bytes字段就是了 */
select * from user_ts_quotas;
select TABLESPACE_NAME,USERNAME,BYTES/1024,MAX_BYTES/1024/1024 from dba_ts_quotas;
SELECT * FROM dba_ts_quotas;
(其他辅助操作)
--不对用户做表空间限额控制(全局性的. 即修改用户多所有表空间的配额):
grant unlimited tablespace to XXXX;
--针对用户的某个特定的表空间
alter user XXX quota unlimited on XXX;
--回收用户对表空间的配额(全局):
revoke unlimited tablespace from xxx;
--回收特定表空间的配额
alter user xxxxx quota 0 on xxxxxx;
--oracle怎么查询表空间所在的路径
SELECT
mAX(B.file_id) id,
B.TABLESPACE_NAME 表空间,
B.FILE_NAME 物理文件名,
B.BYTES / 1024 / 1024 大小M,
(B.BYTES - SUM(NVL(A.BYTES, 0))) / 1024 / 1024 已使用M,
SUBSTR((B.BYTES - SUM(NVL(A.BYTES, 0))) / (B.BYTES) * 100, 1, 5) 利用率
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.BYTES
ORDER BY B.TABLESPACE_NAME;
--查询表空间
SELECT
mAX(B.file_id) id,
B.TABLESPACE_NAME 表空间,
B.FILE_NAME 物理文件名,
B.BYTES / 1024 / 1024 大小M,
(B.BYTES - SUM(NVL(A.BYTES, 0))) / 1024 / 1024 已使用M,
SUBSTR((B.BYTES - SUM(NVL(A.BYTES, 0))) / (B.BYTES) * 100, 1, 5) 利用率
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.BYTES
ORDER BY B.TABLESPACE_NAME;
锁表查询 kill
SELECT s.sid,
s.serial#,
s.username,
decode(l.type, 'TM', 'TABLE LOCK', 'TX', 'ROW LOCK', NULL) LOCK_LEVEL,
o.owner,
o.object_name,
o.object_type,
s.terminal,
s.machine,
s.logon_time,
s.program,
s.osuser
FROM v$session s, v$lock l, dba_objects o
WHERE l.sid = s.sid
AND l.id1 = o.object_id(+)
AND s.username is NOT Null;
alter system kill session '96,1903';