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