1、查看表空间的名称及大小
set linesize 140;
set pages 200;
column tablespace_name format a30;
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;
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;
3、查看回滚段名称及大小
COLUMN roll_name FORMAT a13 HEADING `Rollback Name`
COLUMN tablespace FORMAT a11 HEADING `Tablspace`
COLUMN in_extents FORMAT a20 HEADING `Init/Next Extents`
COLUMN m_extents FORMAT a10 HEADING `Min/Max Extents`
COLUMN status FORMAT a8 HEADING `Status`
COLUMN wraps FORMAT 999 HEADING `Wraps`
COLUMN shrinks FORMAT 999 HEADING `Shrinks`
COLUMN opt FORMAT 999,999,999 HEADING `Opt. Size`
COLUMN bytes FORMAT 999,999,999 HEADING `Bytes`
COLUMN extents FORMAT 999 HEADING `Extents`
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;
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;
9、查看数据库的创建日期和归档方式
select created,log_mode,log_mode from v$database;
10、查看临时数据库文件
select STATUS, ENABLED, NAME from v$tempfile;
1、查看表空间的名称及大小
set linesize 140;
set pages 200;
column tablespace_name format a30;
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;
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;
3、查看回滚段名称及大小
COLUMN roll_name FORMAT a13 HEADING `Rollback Name`
COLUMN tablespace FORMAT a11 HEADING `Tablspace`
COLUMN in_extents FORMAT a20 HEADING `Init/Next Extents`
COLUMN m_extents FORMAT a10 HEADING `Min/Max Extents`
COLUMN status FORMAT a8 HEADING `Status`
COLUMN wraps FORMAT 999 HEADING `Wraps`
COLUMN shrinks FORMAT 999 HEADING `Shrinks`
COLUMN opt FORMAT 999,999,999 HEADING `Opt. Size`
COLUMN bytes FORMAT 999,999,999 HEADING `Bytes`
COLUMN extents FORMAT 999 HEADING `Extents`
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;
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;
9、查看数据库的创建日期和归档方式
select created,log_mode,log_mode from v$database;
10、查看临时数据库文件
select STATUS, ENABLED, NAME from v$tempfile;