1、数据库启动
A、startup nomount
非安装启动，这种办法下启动可实行：重修掌握文件、重修数据库，读取init.ora文件，启动instance，即启动SGA和后台进程，这种启动只须要init.ora文件
B、startup mount (dbname)
安装启动，这种办法启动下可实行：数据库日志归档、数据库介质规复、使数据文件联机或脱机、重新定位数据文件、重做日志文件。
C、startup open (dbname)
先实行“nomount”，然后实行“mount”，再打开包括Redo log文件在内的所有数据库文件，这种办法下可访问数据库中的数据。
D、startup 即是按顺序实行以下三个命令
startup nomount
alter database mount
alter database open
E、startup restrict
约束办法启动，这种办法能够启动数据库，但只许可具有一定特权的用户访问
F、startup force
逼迫启动办法，当不能关闭数据库时，可以用startup force来完成数据库的关闭，先关闭数据库，再实行正常启动数据库命令
G、startup pfile=参数文件名
带初始化参数文件的启动办法，先读取参数文件，再按参数文件中的设置启动数据库
H、startup EXCLUSIVE
独占和共享启动
2、关闭数据的办法
A、shutdown normal 正常办法关闭数据库
B、shutdown immediate
立即办法关闭数据库，在SVRMGRL中实行shutdown immediate，数据库并不立即关闭，而是在Oracle实行某些打消事情后才关闭（终止会话、开释会话资源），当利用shutdown不能关闭数据库时，shutdown immediate可以完成数据库关闭的操作。
C、shutdown abort
直接关闭数据库，正在访问数据库的会话会被溘然终止，如果数据库中有大量操作正在实行，这时实行shutdown abort后，重新启动数据库须要很永劫光。
3、查询每个表空间大小
Select Tablespace_Name,Sum(bytes)/1024/1024 From Dba_Segments Group By Tablespace_Name
4、查询剩余表空间大小
SELECT tablespace_name 表空间,sum(blocks8192/1000000) 剩余空间M FROM dba_free_space GROUP BY tablespace_name
5、查询表分配空间大小
Select Segment_Name,Sum(bytes)/1024/1024 From User_Extents Group By Segment_Name
6、查询表示实占用空间大小
select num_rows avg_row_len from user_tables
where table_name in (select table_name from user_tables)
7、检讨系统中所有表空间总大小
select b.name,sum(a.bytes/1000000)总空间 from v$datafile a,v$tablespace b where a.ts#=b.ts# group by b.name
8、查询数据库中文件信息
select
b.file_name 物理文件名,
b.tablespace_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
9、查询所有用户表利用大小的前5
select from (select segment_name,bytes from dba_segments where owner = USER order by bytes desc ) where rownum <= 5
10、查询碎片高的表
SELECT TABLE_NAME,(BLOCKS8192/1024/1024)"理论大小 M",
(NUM_ROWSAVG_ROW_LEN/1024/1024/0.9)"实际大小 M",
round((NUM_ROWSAVG_ROW_LEN/1024/1024/0.9)/(BLOCKS8192/1024/1024),3)100||'%' "实际利用率%"
FROM USER_TABLES
where blocks>100
and (NUM_ROWSAVG_ROW_LEN/1024/1024/0.9)/(BLOCKS8192/1024/1024)<0.3
order by (NUM_ROWSAVG_ROW_LEN/1024/1024/0.9)/(BLOCKS8192/1024/1024) desc
11、查询索引占碎片比例
select name,del_lf_rows,lf_rows,
round(del_lf_rows/decode(lf_rows,0,1,lf_rows)100,0)||'%' frag_pct
from index_stats
where round(del_lf_rows/decode(lf_rows,0,1,lf_rows)100,0)>30
12、集群因子高的表
集群因子越靠近块数越好，靠近行数则解释索引列的列值相等的行分布极度散列，可能不走索引扫描而走全表扫描
select tab.table_name,tab.blocks,tab.num_rows,ind.index_name,ind.clustering_factor,
round(nvl(ind.clustering_factor,1)/decode(tab.num_rows,0,1,tab.num_rows),3)100||'%' "集群因子靠近行数"
from user_tables tab, user_indexes ind where tab.table_name=ind.table_name and tab.blocks>100
and nvl(ind.clustering_factor,1)/decode(tab.num_rows,0,1,tab.num_rows) between 0.35 and 3
13、根据 sid 查 spid 或根据 spid 查 sid
select s.sid,s.serial#,s.LOGON_TIME,s.machine,p.spid,p.terminal
from v$session s,v$process p
where s.paddr=p.addr and s.sid=SS or p.spid=BB
14、根据 sid 查sql 语句
select username,sql_text,machine,osuser from v$session a,v$sqltext_with_newlinesb
where DECODE(a.sql_hash_value, 0, prev_hash_value, sql_hash_value)=b.hash_value
and a.sid=&sid order by piece
15、查询sid
select instance_name from V$instance
16、查询ORACLE 的session的id
select sid from V$mystat where rownum=1
17、查询ORACLE 的SESSION_SERIAL
select from v$session where username is not null
18、通过SID查询SPID在进一步查询到IP
select s.sid,s.serial#,s.LOGON_TIME,s.machine,p.spid,p.terminal
from v$session s,v$process p
where s.paddr=p.addr and SID=7
netstat -anp |grep 2716
19、查询四锁SID
select sid, blocking_session, LOGON_TIME,sql_id,status,
event,seconds_in_wait,state, BLOCKING_SESSION_STATUS
from v$session where event like 'enq%'
and state='WAITING'
and BLOCKING_SESSION_STATUS='VALID'
20、查询DDL锁住的SQL
SELECT s.sid, kglpnmod "Mode", kglpnreq "Req",
s.LOGON_TIME FROM x$kglpn p, v$session s
WHERE p.kglpnuse=s.saddr AND kglpnhdl
IN(SELECT p1raw FROM sys.v_$session_wait WHERE event like 'library cache %')
21、查询锁住的DDL工具
select d.session_id,s.SERIAL#,d.name
from dba_ddl_locks d,v$session s
where d.owner='system' and d.SESSION_ID=s.sid
22、查询正在实行的SQL
select s.sid,s.serial#,s.username,spid,v$sql.sql_id,
machine,s.terminal,s.program,sql_text
FROM v$process,v$session s,v$sql
WHERE addr=paddr and s.sql_id=v$sql.sql_id
AND sql_hash_value=hash_value
23、查询正在实行的SCHEDULER_JOB
select owner,job_name,sid,b.SERIAL#,b.username,spid
from ALL_SCHEDULER_RUNNING_JOBS,v$session b,v$process
where session_id=sid and paddr=addr
24、查询正在实行的 dbms_job
select job,b.sid,b.SERIAL#,b.username,spid
from DBA_JOBS_RUNNING a ,v$session b,v$process
where a.sid=b.sid and paddr=addr
25、查询会话和进程花费的均匀内存大小
select round(sum(pga_used_mem)/1024/1024,0) total_used_M,
round(sum(pga_used_mem)/count(1)/1024/1024,0) avg_used_M,
round(sum(pga_alloc_mem)/1024/1024,0) total_alloc_M,
round(sum(pga_alloc_mem)/count(1)/1024/1024,0) avg_alloc_M
from v$process
26、实行次数排序
select from (select executions,username,PARSING_USER_ID,sql_id,sql_text
from v$sql,dba_users where user_id=PARSING_USER_ID
order by executions desc)where rownum <=5
27、物理读查询
select from (select DISK_READS,username,PARSING_USER_ID,sql_id,ELAPSED_TIME/1000000,sql_text
from v$sql,dba_users
where user_id=PARSING_USER_ID
order by DISK_READS desc)where rownum <=3
28、逻辑读查询
select from (select BUFFER_GETS,username,PARSING_USER_ID,sql_id,ELAPSED_TIME/1000000,sql_text
from v$sql,dba_users
where user_id=PARSING_USER_ID
order by BUFFER_GETS desc)where rownum <=3
29、高CPU占用查询
select from (select CPU_TIME/1000000,username,PARSING_USER_ID,sql_id,ELAPSED_TIME/1000000,sql_text
from v$sql,dba_users
where user_id=PARSING_USER_ID
order by CPU_TIME/1000000 desc)where rownum <=3
30、等待查询
select event,sum(decode(wait_time,0,0,1)) "之前等待次数",
sum(decode(wait_time,0,1,0)) "正在等待次数",
count() from v$session_wait
group by event order by 4 desc
31、查询须要利用绑定变量的SQL
select sql_id, FORCE_MATCHING_SIGNATURE, sql_text
from v$SQL
where FORCE_MATCHING_SIGNATURE
in(select /+ unnest /FORCE_MATCHING_SIGNATURE from v$sql
where FORCE_MATCHING_SIGNATURE > 0
and FORCE_MATCHING_SIGNATURE != EXACT_MATCHING_SIGNATURE
group by FORCE_MATCHING_SIGNATURE having count(1) > 10)
32、查询数据文件可用占比
select b.file_id,b.tablespace_name,b.file_name,b.AUTOEXTENSIBLE,
ROUND(b.MAXBYTES/1024/1024/1024,2) ||'G' "文件最大可用总容量",
ROUND((b.bytes-sum(nvl(a.bytes,0)))/1024/1024/1024,2)||'G' "文件已用容量",
ROUND(((b.MAXBYTES/1024/1024/1024)-((b.bytes-sum(nvl(a.bytes,0)))/1024/1024/1024))/(b.MAXBYTES/1024/1024/1024),2)100||'%' "文件可用百分比"
from dba_free_space a,dba_data_files b where a.file_id=b.file_id
and b.file_id>4 group by b.tablespace_name,b.file_name,b.file_id,b.bytes,b.AUTOEXTENSIBLE,b.MAXBYTES
order by b.tablespace_name;
33、查看表空间可用占比
select b.tablespace_name,a.total,b.free,
round((b.free/a.total)100) "% Free"
from(select tablespace_name, sum(bytes/(10241024)) total
from dba_data_files group by tablespace_name) a,(select tablespace_name, round(sum(bytes/(10241024))) free
from dba_free_space group by tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name
order by "% Free"
34、查询undo表空间利用情形
select tablespace_name,status,sum(bytes)/1024/1024 M
from dba_undo_extents
group by tablespace_name,status
35、查看ASM磁盘组利用率
select name,round(total_mb/1024) "总容量",
round(free_mb/1024) "空闲空间",
round((free_mb/total_mb)100) "可用空间比例"
from gv$asm_diskgroup
36、查询每个利用表空间情形
SELECT c.owner "用户",
a.tablespace_name "表空间名",
total/1024/1024 "表空间大小 M",
free/1024/1024 "表空间剩余大小 M",
( total - free )/1024/1024 "表空间利用大小 M",
Round(( total - free ) / total, 4) 100 "表空间总计利用率%",
c.schemas_use/1024/1024 "用户利用表空间大小 M",
round((schemas_use)/total,4)100 "用户利用表空间率%"
FROM (SELECT tablespace_name, Sum(bytes) free FROM DBA_FREE_SPACE GROUP BY tablespace_name) a,
(SELECT tablespace_name,Sum(bytes) total FROM DBA_DATA_FILES GROUP BY tablespace_name) b,
(Select owner ,Tablespace_Name,Sum(bytes) schemas_use From Dba_Segments Group By owner,Tablespace_Name) c
WHERE a.tablespace_name = b.tablespace_name
and a.tablespace_name =c.Tablespace_Name
order by c.owner,a.tablespace_name;
37、查看闪回区\快速规复区空间利用率
select sum(percent_space_used)||'%' "已利用空间比例"
from V$RECOVERY_AREA_USAGE
38、查看行迁移或者行连接的表
select From dba_tables where nvl(chain_cnt,0)<>0
39、查询缓冲区命中率
SELECT a.VALUE+b.VALUE logical_reads, c.VALUE phys_reads,
round(100(1-c.value/(a.value+b.value)),2)||'%' 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'
40、查询共享池命中率
select sum(pinhits-reloads)/sum(pins)100 from v$librarycache
41、查询归档日志切换频率
select sequence#,to_char(first_time,'yyyy-mm-dd hh24:mi:ss') First_time,
First_change#,switch_change#
from v$loghist
where first_time>sysdate-3 order by 1
42、查询没有建立索引的表
Select table_name
from user_tables
where table_name not in (select table_name from user_indexes)
43、查询LGWR一次须要的时长
select event,state,seq#,seconds_in_wait,program
from v$session
where program like '%LGWR%'
and state='WAITING'
