Some SQL to view Oracle database information

oracle| Data | database
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 table space physical file name and size

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. View rollback segment name and size

COLUMN roll_name FORMAT A13 HEADING ' Rollback name ' column tablespace FORMAT a11 HEADING ' tablspace ' column in_extents forma T A20 HEADING ' init/next extents ' column m_extents FORMAT A10 HEADING ' min/max ' column status FORMAT extents A8 ' Sta Tus ' column wraps format 999 HEADING ' wraps ' column shrinks format 999 HEADING ' shrinks ' column opt format 999,999,999 headi NG ' 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_na Me and A.segment_name = C.name (+) and C.usn = D.USN (+) Order by A.segment_name;

4, view the control file

Select name from V$controlfile;

5. View Log file

Select member from V$logfile;

6, view the use of table space

SELECT * FROM (select sum (bytes)/(1024*1024) as "Free_space (M)" and Tablespace_name from Dba_free_spacegroup by Tablespace_ Name) Order BY "Free_space (M)";

7. View Database Library objects

Select owner, object_type, Status, COUNT (*) count# from all_objects Group by Owner,object_type,status;

8, view the version of the database

SELECT * from V$version;

9, view the database creation date and the way of filing

Select Created,log_mode,log_mode from V$database;

10. View Temporary Database files

Select STATUS, ENABLED, NAME from V$tempfile;