常用SQL查询
查看表空间的名称及大小
select ttablespace_name round(sum(bytes/(*))) ts_size
from dba_tablespaces t dba_data_files d
where ttablespace_name = dtablespace_name
group by ttablespace_name;
查看表空间物理文件的名称及大小
select tablespace_name file_id file_name
round(bytes/(*)) total_space
from dba_data_files
order by tablespace_name;
查看回滚段名称及大小
select segment_name tablespace_name rstatus
(initial_extent/) InitialExtent(next_extent/) NextExtent
max_extents vcurext CurExtent
From dba_rollback_segs r v$rollstat v
Where rsegment_id = vusn(+)
order by segment_name;
查看控制文件
select name from v$controlfile;
查看日志文件
select member from v$logfile;
查看表空间的使用情况
select sum(bytes)/(*) as free_spacetablespace_name
from dba_free_space
group by tablespace_name;
SELECT ATABLESPACE_NAMEABYTES TOTALBBYTES USED CBYTES FREE
(BBYTES*)/ABYTES % USED(CBYTES*)/ABYTES % FREE
FROM SYSSM$TS_AVAIL ASYSSM$TS_USED BSYSSM$TS_FREE C
WHERE ATABLESPACE_NAME=BTABLESPACE_NAME AND ATABLESPACE_NAME=CTABLESPACE_NAME;
查看数据库库对象
select owner object_type status count(*) count# from all_objects group by owner object_type status;
查看数据库的版本
Select version FROM Product_component_version
Where SUBSTR(PRODUCT)=Oracle;
查看数据库的创建日期和归档方式
Select Created Log_Mode Log_Mode From V$Database;
捕捉运行很久的SQL
column username format a
column opname format a
column progress format a
select usernamesidopname
round(sofar* / totalwork) || % as progress
time_remainingsql_text
from v$session_longops v$sql
where time_remaining <>
and sql_address = address
and sql_hash_value = hash_value
/
查看数据表的参数信息
SELECT partition_name high_value high_value_length tablespace_name
pct_free pct_used ini_trans max_trans initial_extent
next_extent min_extent max_extent pct_increase FREELISTS
freelist_groups LOGGING BUFFER_POOL num_rows blocks
empty_blocks avg_space chain_cnt avg_row_len sample_size
last_analyzed
FROM dba_tab_partitions
WHERE table_name = :tname AND table_owner = :towner
ORDER BY partition_position
查看还没提交的事务
select * from v$locked_object;
select * from v$transaction;
查找object为哪些进程所用
select
pspid
ssid
sserial# serial_num
susername user_name
atype object_type
sosuser os_user_name
aowner
aobject object_name
decode(sign( command)
to_char(command) Action Code # || to_char(command) ) action
pprogram oracle_process
sterminal terminal
sprogram program
sstatus session_status
from v$session s v$access a v$process p
where spaddr = paddr and
stype = USER and
asid = ssid and
aobject=SUBSCRIBER_ATTR
order by susername sosuser
回滚段查看
select rownum sysdba_rollback_segssegment_name Name v$rollstatextents
Extents v$rollstatrssize Size_in_Bytes v$rollstatxacts XActs
v$rollstatgets Gets v$rollstatwaits Waits v$rollstatwrites Writes
sysdba_rollback_segsstatus status from v$rollstat sysdba_rollback_segs
v$rollname where v$rollnamename(+) = sysdba_rollback_segssegment_name and
v$rollstatusn (+) = v$rollnameusn order by rownum
耗资源的进程（top session）
select sschemaname schema_name decode(sign( command)
to_char(command) Action Code # || to_char(command) ) action status
session_status sosuser os_user_name ssid pspid sserial# serial_num
nvl(susername [Oracle process]) user_name sterminal terminal
sprogram program stvalue criteria_value from v$sesstat st v$session s v$process p
where stsid = ssid and ststatistic# = to_number() and (ALL = ALL
or sstatus = ALL) and paddr = spaddr order by stvalue desc pspid asc susername asc sosuser asc
查看锁（lock）情况
select /*+ RULE */ lsosuser os_user_name lsusername user_name
decode(lstype RW Row wait enqueue lock TM DML enqueue lock TX
Transaction enqueue lock UL User supplied lock) lock_type
oobject_name object decode(lslmode null Row Share
Row Exclusive Share Share Row Exclusive Exclusive null)
lock_mode oowner lssid lsserial# serial_num lsid lsid
from sysdba_objects o ( select sosuser susername ltype
llmode ssid sserial# lid lid from v$session s
v$lock l where ssid = lsid ) ls where oobject_id = lsid and oowner
<> SYS order by oowner oobject_name
查看等待（wait）情况
SELECT v$waitstatclass v$unt count SUM(v$sysstatvalue) sum_value
FROM v$waitstat v$sysstat WHERE v$sysstatname IN (db block gets
consistent gets) group by v$waitstatclass v$unt
查看sga情况
SELECT NAME BYTES FROM SYSV_$SGASTAT ORDER BY NAME ASC
查看catched object
SELECT owner name db_link namespace
type sharable_mem loads executions
locks pins kept FROM v$db_object_cache
查看V$SQLAREA
SELECT SQL_TEXT SHARABLE_MEM PERSISTENT_MEM RUNTIME_MEM SORTS
VERSION_COUNT LOADED_VERSIONS OPEN_VERSIONS USERS_OPENING EXECUTIONS
USERS_EXECUTING LOADS FIRST_LOAD_TIME INVALIDATIONS PARSE_CALLS DISK_READS
BUFFER_GETS ROWS_PROCESSED FROM V$SQLAREA
查看object分类数量
select decode (otype#INDEX TABLE CLUSTER VIEW
SYNONYM SEQUENCE OTHER ) object_type count(*) quantity from
sysobj$ o where otype# > group by decode (otype#INDEX TABLE
CLUSTER VIEW SYNONYM SEQUENCE OTHER ) union select
COLUMN count(*) from l$ union select DB LINK count(*) from
按用户查看object种类
select uname schema sum(decode(otype# NULL)) indexes
sum(decode(otype# NULL)) tables sum(decode(otype# NULL))
clusters sum(decode(otype# NULL)) views sum(decode(otype#
NULL)) synonyms sum(decode(otype# NULL)) sequences
sum(decode(otype# NULL NULL NULL NULL NULL NULL ))
others from sysobj$ o sysuser$ u where otype# >= and uuser# =
oowner# and uname <> PUBLIC group by uname order by
syslink$ union select CONSTRAINT count(*) from n$
有关connection的相关信息
）查看有哪些用户连接
select sosuser os_user_name decode(sign( command) to_char(command)
Action Code # || to_char(command) ) action pprogram oracle_process
status session_status sterminal terminal sprogram program
susername user_name sfixed_table_sequence activity_meter query
memory max_memory cpu_usage ssid sserial# serial_num
from v$session s v$process p where spaddr=paddr and stype = USER
order by susername sosuser
）根据vsid查看对应连接的资源占用等情况
select nname
vvalue
nclass
nstatistic#
from v$statname n
v$sesstat v
where vsid = and
vstatistic# = nstatistic#
order by nclass nstatistic#
）根据sid查看对应连接正在运行的sql
select /*+ PUSH_SUBQ */
command_type
sql_text
sharable_mem
persistent_mem
runtime_mem
sorts
version_count
loaded_versions
open_versions
users_opening
executions
users_executing
loads
first_load_time
invalidations
parse_calls
disk_reads
buffer_gets
rows_processed
sysdate start_time
sysdate finish_time
> || address sql_address
N status
from v$sqlarea
where address = (select sql_address from v$session where sid = )
．查询表空间使用情况
select atablespace_name 表空间名称
round((nvl(bbytes_free)/abytes_alloc)*) 占用率(%)
round(abytes_alloc//) 容量(M)
round(nvl(bbytes_free)//) 空闲(M)
round((abytes_allocnvl(bbytes_free))//) 使用(M)
Largest 最大扩展段(M)
to_char(sysdateyyyymmdd hh:mi:ss) 采样时间
from (select ftablespace_name
sum(fbytes) bytes_alloc
sum(decode(fautoextensibleYESfmaxbytesNOfbytes)) maxbytes
from dba_data_files f
group by tablespace_name) a
(select ftablespace_name
sum(fbytes) bytes_free
from dba_free_space f
group by tablespace_name) b
(select round(max(fflength)*/) Largest
tsname tablespace_name
from sysfet$ ff sysfile$ tfsysts$ ts
where tsts#=ffts# and fffile#=tfrelfile# and tsts#=tfts#
group by tsname tfblocks) c
where atablespace_name = btablespace_name and atablespace_name = ctablespace_name
查询表空间的碎片程度
select tablespace_namecount(tablespace_name) from dba_free_space group by tablespace_name
having count(tablespace_name)>;
alter tablespace name coalesce;
alter table name deallocate unused;
create or replace view ts_blocks_v as
select tablespace_nameblock_idbytesblocksfree space segment_name from dba_free_space
union all
select tablespace_nameblock_idbytesblockssegment_name from dba_extents;
select * from ts_blocks_v;
select tablespace_namesum(bytes)max(bytes)count(block_id) from dba_free_space
group by tablespace_name;
查询有哪些数据库实例在运行
select inst_name from v$active_instances;
===========================================================
######### 创建数据库look $ORACLE_HOME/rdbms/admin/buildallsql #############
create database db
maxlogfiles
maxdatafiles
maxinstances
logfile
GROUP (/u/oradata/db/log__dbrdo) SIZE M
GROUP (/u/oradata/db/log__dbrdo) SIZE M
GROUP (/u/oradata/db/log__dbrdo) SIZE M
datafile u/oradata/db/system__dbdbf) SIZE M
undo tablespace UNDO
datafile /u/oradata/db/undo__dbdbf SIZE M
