查询存档
清除优化缓存
alter system flush shared_pool ;
alter system flush BUFFER_CACHE ;
缓存
sys.v_$db_object_cache
查询最近执行的sql
select * from v$sqlarea t where t.PARSING_SCHEMA_NAME in ('CPFAIX') order by t.LAST_ACTIVE_TIME desc;
查询连接数据库的用户相关信息
SELECT osuser,
a.username,
cpu_time / 1000000 || 's',
sql_fulltext,
machine,b.*
from v$session a, v$sqlarea b
where a.sql_address = b.address
order by cpu_time desc;
select username,count(username) from v$session where username is not null group by username;
SELECT username, machine, program, status, COUNT(machine) AS 链接数
FROM v$session
GROUP BY username, machine, program, status
ORDER BY machine;
查询数据库最大几张表及表大小
select a.*
from(
select owner,segment_name,SEGMENT_TYPE, round(bytes/1024/1024/1024,2) all_size
from dba_segments
where SEGMENT_TYPE='TABLE' and owner='CPFAIX'
order by all_size desc
) a where rownum <15;
--表大小
SELECT bytes/1024/1024 ||'MB' TABLE_SIZE ,u.* FROM USER_SEGMENTS U WHERE U.SEGMENT_NAME='SYS_EXCHANGELOG';
--表空间大小
SELECT SUM(BYTES) / 1024 / 1024/1024 ||'GB' FROM USER_SEGMENTS U WHERE TABLESPACE_NAME = 'CPF';
--所有表空间大小
SELECT A.TABLESPACE_NAME,
FILENUM,
TOTAL "TOTAL (GB)",
F.FREE "FREE (GB)",
TO_CHAR(ROUND(FREE * 100 / TOTAL, 2), '990.00') "FREE%",
TO_CHAR(ROUND((TOTAL - FREE) * 100 / TOTAL, 2), '990.00') "USED%",
ROUND(MAXSIZES, 2) "MAX (GB)"
FROM (SELECT TABLESPACE_NAME,
COUNT(FILE_ID) FILENUM,
SUM(BYTES / (1024 * 1024 * 1024)) TOTAL,
SUM(MAXBYTES) / 1024 / 1024 / 1024 MAXSIZES
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) A,
(SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES / (1024 * 1024 * 1024))) FREE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F
WHERE A.TABLESPACE_NAME = F.TABLESPACE_NAME;