SQL statements used to view Oracle database information

1. view the table space name and size.

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_tablespacesorder by tablespace_name;

2. view the name and size of the tablespace physical file.

column db_block_size new_value blksz noprintselect 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. Check the rollback segment name and size.

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                                 optFROM    dba_rollback_segs a  , dba_segments b  , v$rollname c  , v$rollstat dWHERE       a.segment_name = b.segment_name  AND  a.segment_name = c.name (+)  AND  c.usn          = d.usn (+)ORDER BY a.segment_name;

4. View Control Files

Select name from V $ controlfile;

5. view log files

Select member from V $ logfile;

6. View table space usage

select * from( select sum(bytes)/(1024*1024)  as "free_space(M)",tablespace_name from dba_free_spacegroup by tablespace_name) order by "free_space(M)";

7. view database objects

Select owner, object_type, status, count (*) Count # From all_objects
Group by owner, object_type, status;

8. view the database version

Select * from V $ version;

9. view the database creation date and archiving method

Select created, log_mode, log_mode from V $ database;

10. view temporary database files

Select status, enabled, name from V $ tempfile;