SQL专栏
前言
sqlplus / nolog
conn /as sysdba(或者conn 账号/密码)
set linesize 500;
select instance_name,host_name,startup_time,
status,database_status
from v$instance;
select
group#,status,type,member
from v$logfile;
select
tablespace_name,status
from dba_tablespaces;
select name,status from v$datafile;
select
owner,object_name,object_type
from dba_objects
where status!='VALID'
and owner!='SYS'
and owner!='SYSTEM';
SELECT
owner, object_name, object_type
FROM dba_objects
WHERE status= 'INVALID';
select
segment_name,status
from dba_rollback_segs;
a.检查Oracle初始化文件中相关的参数值
b.检查数据库连接情况,检查系统磁盘空间
c.检查Oracle各个表空间使用情况,检查一些扩展异常的对象,
d.检查system表空间内的内容,检查对象的下一扩展与表空间的最大扩展值,总共七个部分。
select resource_name,
max_utilization,
initial_allocation,
limit_value
from v$resource_limit;
select count(*) from v$session;
select
sid,serial#,username,program,machine,status
from v$session;
alter system kill session 'SID,SERIAL#';
[oracle@local ~]$ df -h
select f.tablespace_name,
a.total,
f.free,
round((f.free / a.total) * 100) '% Free'
from (select tablespace_name, sum(bytes / (1024 * 1024)) total
from dba_data_files
group by tablespace_name) a,
(select tablespace_name, round(sum(bytes / (1024 * 1024))) free
from dba_free_space
group by tablespace_name) f
WHERE a.tablespace_name = f.tablespace_name(+)
order by '% Free';
select Segment_Name,
Segment_Type,
TableSpace_Name,
(Extents / Max_extents) * 100 Percent
From sys.DBA_Segments
Where Max_Extents != 0
and (Extents / Max_extents) * 100 >= 95
order By Percent;
select distinct (owner)
from dba_tables
where tablespace_name = 'SYSTEM'
and owner != 'SYS'
and owner != 'SYSTEM'
union
select distinct (owner)
from dba_indexes
where tablespace_name = 'SYSTEM'
and owner != 'SYS'
and owner != 'SYSTEM';
select a.table_name, a.next_extent, a.tablespace_name
from all_tables a,
(select tablespace_name, max(bytes) as big_chunk
from dba_free_space
group by tablespace_name) f
where f.tablespace_name = a.tablespace_name
and a.next_extent > f.big_chunk
union
select a.index_name, a.next_extent, a.tablespace_name
from all_indexes a,
(select tablespace_name, max(bytes) as big_chunk
from dba_free_space
group by tablespace_name) f
where f.tablespace_name = a.tablespace_name
and a.next_extent > f.big_chunk;
a.检查数据库备份日志信息;
b.检查backup卷中文件产生的时间;
c.检查oracle用户的email
#cat /backup/hotbackup/hotbackup-09-7-22.log|grep –i error
#ls –lt /backup/hotbackup
#tail –n 300 /var/mail/oracle
set pages 80
set lines 120
col event for a40
select sid, event, p1, p2, p3, WAIT_TIME, SECONDS_IN_WAIT
from v$session_wait
where event not like 'SQL%'
and event not like 'rdbms%';
SELECT SQL_TEXT
FROM (SELECT * FROM V$SQLAREA ORDER BY DISK_READS)
WHERE ROWNUM <= 5;
SELECT *
FROM (SELECT PARSING_USER_ID
EXECUTIONS,
SORTS,
COMMAND_TYPE,
DISK_READS,
SQL_TEXT
FROM V$SQLAREA
ORDER BY DISK_READS DESC)
WHERE ROWNUM < 10;
SELECT *
FROM (SELECT *
FROM V$SYSTEM_EVENT
WHERE EVENT NOT LIKE 'SQL%'
ORDER BY TOTAL_WAITS DESC)
WHERE ROWNUM <= 5;
COLUMN USERNAME FORMAT A12
COLUMN OPNAME FORMAT A16
COLUMN PROGRESS FORMAT A8
SELECT USERNAME,
SID,
OPNAME,
ROUND(SOFAR * 100 / TOTALWORK, 0) || '%' AS PROGRESS,
TIME_REMAINING,
SQL_TEXT
FROM V$SESSION_LONGOPS, V$SQL
WHERE TIME_REMAINING <> 0
AND SQL_ADDRESS = ADDRESS
AND SQL_HASH_VALUE = HASH_VALUE;
SET LINE 240
SET VERIFY OFF
COLUMN SID FORMAT 999
COLUMN PID FORMAT 999
COLUMN S_# FORMAT 999
COLUMN USERNAME FORMAT A9 HEADING 'ORA USER'
COLUMN PROGRAM FORMAT A29
COLUMN SQL FORMAT A60
COLUMN OSNAME FORMAT A9 HEADING 'OS USER'
SELECT P.PID PID,
S.SID SID,
P.SPID SPID,
S.USERNAME USERNAME,
S.OSUSER OSNAME,
P.SERIAL# S_#,
P.TERMINAL,
P.PROGRAM PROGRAM,
P.BACKGROUND,
S.STATUS,
RTRIM(SUBSTR(A.SQL_TEXT, 1, 80)) SQLFROM V$PROCESS P,
V$SESSION S,
V$SQLAREA A
WHERE P.ADDR = S.PADDR
AND S.SQL_ADDRESS = A.ADDRESS(+)
AND P.SPID LIKE '%&1%';
SELECT segment_name table_name, COUNT(*) extents
FROM dba_segments
WHERE owner NOT IN ('SYS', 'SYSTEM')
GROUP BY segment_name
HAVING COUNT(*) = (SELECT MAX(COUNT(*))
FROM dba_segments
GROUP BY segment_name);
SELECT DF.TABLESPACE_NAME NAME,
DF.FILE_NAME 'FILE',
F.PHYRDS PYR,
F.PHYBLKRD PBR,
F.PHYWRTS PYW,
F.PHYBLKWRT PBW
FROM V$FILESTAT F, DBA_DATA_FILES DF
WHERE F.FILE# = DF.FILE_ID
ORDER BY DF.TABLESPACE_NAME;
SELECT SUBSTR(A.FILE#, 1, 2) '#',
SUBSTR(A.NAME, 1, 30) 'NAME',
A.STATUS,
A.BYTES,
B.PHYRDS,
B.PHYWRTS
FROM V$DATAFILE A, V$FILESTAT B
WHERE A.FILE# = B.FILE#;
select sid,
serial#,
username,
SCHEMANAME,
osuser,
MACHINE,
terminal,
PROGRAM,
owner,
object_name,
object_type,
o.object_id
from dba_objects o, v$locked_object l, v$session s
where o.object_id = l.object_id
and s.sid = l.session_id;
alter system kill session '&sid,&serial#';
#kill -9 pid
#top
# free -m
结果中的蓝色部分表示系统总内存,红色部分表示系统使用的内存,黄色部分表示系统剩余内存,当剩余内存低于总内存的10%时视为异常。
# iostat -k 1 3
#uptime
select spid
from v$process
where addr not in (
select paddr from v$session
);
select table_name, num_rows, chain_cnt
From dba_tables
Where owner = 'CTAIS2'
And chain_cnt <> 0;
analyze table tablename list chained rows;
create table aa as
select a.*
from sb_zsxx a,chained_rows b
where a.rowid=b.head_rowid
and b.table_name ='SB_ZSXX';
delete from sb_zsxx
where rowid in (
select head_rowid
from chained_rows
where table_name = 'SB_ZSXX'
);
insert into sb_zsxx
select * from chained_row
where table_name = 'SB_ZSXX';
Select
table_name,num_rows,last_analyzed
From user_tables
where table_name ='DJ_NSRXX'
exec sys.dbms_stats.gather_schema_stats(ownname=>'CTAIS2',cascade => TRUE,degree => 4);
SELECT a.VALUE + b.VALUE logical_reads,
c.VALUE phys_reads,
round(100 * (1 - c.value / (a.value + b.value)), 4) hit_ratio
FROM v$sysstat a, v$sysstat b, v$sysstat c
WHERE a.NAME = 'db block gets'
AND b.NAME = 'consistent gets'
AND c.NAME = 'physical reads';
select sum(pinhits) / sum(pins) * 100 from v$librarycache;
select name,value from v$sysstat where name like '%sort%';
select name,value from v$sysstat
where name in (
'redo entries','
redo buffer allocation retries'
);
# grep -i accepted /var/log/secure
# grep -i inval /var/log/secure &&grep -i failed /var/log/secure
alter user USER_NAME identified by PASSWORD;
exec dbms_workload_repository.create_snapshot();
@?/rdbms/admin/awrrpt.sql
——End——
联系客服