查看表空间的名称及大小
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
