查看数据库——Oracle数据库技术与实验指导

1.查看表空间的名称及大小

select tablespace_name,min_extents,max_extents,pct_increase,status from dba_tablespaces;
select tablespace_name,initial_extent,next_extent,contents,logging,extent_management,allocation_type from dba_tablespaces order by tablespace_name;
select t.tablespace_name, round(sum(bytes / (1024 * 1024)), 0) ts_size from dba_tablespaces t, dba_data_files d where t.tablespace_name = d.tablespace_name group by t.tablespace_name;

2.查看表空间物理文件的名称及大小

column db_block_size new_value blksz noprint;
select value db_block_size from v$parameter where name='db_block_size';
column tablespace_name format a16;
column file_name format a60;
set linesize 160;
select file_name,round(bytes/(1024*1024),0) total_space,autoextensible,increment_by*&blksz/(1024*1024) as incement,maxbytes/(1024*1024) as maxsize from dba_data_files order by tablespace_name;
select tablespace_name, file_id,file_name, round(bytes/(1024*1024),0) total_space from dba_data_files order by tablespace_name;

3.查看回滚段名称及大小

select a.owner||' . '||a.segment_name roll_name , a.tablespace_name tablespace , to_char(a.initial_extent)||'/'||to_char(a.next_extent) in_extents , to_char(a.min_extents)||'/'||to_char(a.max_extents)m_extents,a.status status,b.bytes bytes , b.extents extents , d.shrinks shrinks , d.wraps wraps , d.optsize opt from dba_rollback_segs a , dba_segments b , v$rollname c , v$rollstat d where a.segment_name = b.segment_name and a.segment_name = c.name (+) and c.usn = d.usn (+) order by a.segment_name;
select segment_name, tablespace_name, r.status,(initial_extent/1024) InitialExtent,(next_extent/1024) NextExtent,max_extents, v.curext CurExtent From dba_rollback_segs r, v$rollstat v Where r.segment_id = v.usn(+) order by segment_name ;  

4.查看控制文件

select name from v$controlfile;

5.查看日志文件

select member from v$logfile;

6.查看表空间的使用情况

select * from( select sum(bytes)/(1024*1024)  as "free_space(m)",tablespace_name from dba_free_space group by tablespace_name) order by "free_space(m)";

7.查看数据库库对象

select owner, object_type, status, count(*) count# from all_objects group by owner,object_type,status;

8.查看数据库的版本

select * from v$version;
Select version FROM Product_component_version Where SUBSTR(PRODUCT,1,6)='Oracle';

9.查看数据库的创建日期和归档方式

select created,log_mode,log_mode from v$database;

10.查看临时数据库文件

select STATUS, ENABLED, NAME from v$tempfile;