Oracle 一些查询语句备忘

最新推荐文章于 2024-11-15 22:11:31 发布

cuanchuan4660

最新推荐文章于 2024-11-15 22:11:31 发布

阅读量93

点赞数

文章标签：数据库

来自网络

[@more@]

-- 显示锁

SELECT s.USERNAME, s.MACHINE, s.LOGON_TIME, l.OBJECT_ID,l.SESSION_ID,s.SERIAL#,l.Oracle_USERNAME,l.OS_USER_NAME,l.PROCESS,
CASE l.LOCKED_MODE
WHEN 2 THEN '行共享'
WHEN 3 THEN '行独占'
WHEN 4 THEN '共享锁'
WHEN 5 THEN '共享行独占'
WHEN 6 THEN ' 独占'
ELSE '其他锁'
END LOCKED_MODE
FROM V$LOCKED_OBJECT l,V$SESSION S WHERE l.SESSION_ID=S.SID;

--显示并发连接

SELECT COUNT(*) AS concurrency_num
FROM V$session s WHERE s.status= 'ACTIVE';

-- 连接用户数

SELECT username, count(username) AS connection_num
FROM v$session s WHERE s.username is not NULL AND s.STATUS = 'ACTIVE' GROUP BY s.username;

--下面来自 http://space.itpub.net/12778571/viewspace-582695
-- 查看表空间

SELECT
a.tablespace_name,
a.bytes/1024/1024 "Total MB",
(a.bytes-b.bytes)/1024/1024 "Used MB",
b.bytes/1024/1024 "Free MB",
ROUND(((a.bytes-b.bytes)/a.bytes)*100,2) "Percent_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;

-- 查看数据文件
SELECT
file_name,tablespace_name,
bytes/1024/1024 "bytes MB",
maxbytes/1024/1024 "maxbytes MB"
FROM dba_data_files
WHERE tablespace_name='PSSHR';

-- 查看是否可自动扩展
SELECT
file_id,
file_name,
tablespace_name,
autoextensible,
increment_by
FROM dba_data_files
ORDER BY file_id DESC;

-- 获取创建表空间的语句
SELECT dbms_metadata.get_ddl('TABLESPACE','PSSHR') FROM dual;

--增加表空间数据文件
ALTER tablespace PSSHR
add datafile '/oracle/home/oradata/psshr/PSSHR2.dbf'
size 10M autoextend on maxsize 20G

-- 删除表空间数据文件
ALTER tablespace MLOG_NORM_SPACE
DROP datafile '/oracle/home/oradata/psshr/PSSHR2.dbf'

-- 删除用户对象
BEGIN
FOR cur_rec IN (SELECT object_name, object_type
FROM user_objects
WHERE object_type IN
('TABLE',
'VIEW',
'PACKAGE',
'PROCEDURE',
'FUNCTION',
'SEQUENCE',
'MATERIALIZED VIEW'
))
LOOP
BEGIN
IF cur_rec.object_type = 'TABLE'
THEN
--DBMS_OUTPUT.PUT_LINE
EXECUTE IMMEDIATE ( 'DROP '
|| cur_rec.object_type
|| ' "'
|| cur_rec.object_name
|| '" CASCADE CONSTRAINTS');
ELSE
EXECUTE IMMEDIATE ( 'DROP '
|| cur_rec.object_type
|| ' "'
|| cur_rec.object_name
|| '"');
END IF;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ( 'FAILED: DROP '
|| cur_rec.object_type
|| ' "'
|| cur_rec.object_name
|| '"'
);
END;
END LOOP;
END;