ORACLE入门之查看数据库的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;