Which SQL statements are commonly used for Oracle maintenance?

The following articles mainly introduce SQL statements commonly used for Oracle maintenance. If you are a computer, I believe that after reading this article, you will have a better understanding of the relevant SQL statements. The following describes the specific content of this article.
 

1. view the table space name and size.

 
 

  
  
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. view the name and size of the tablespace physical file.

 
 

  
  
select tablespace_name, file_id, file_name,   
  
  
round(bytes/(1024*1024),0) total_space   
  
  
from dba_data_files   
  
  
order by tablespace_name;   
 
 

3. Check the rollback segment name and size.

 
 

  
  
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. View Control Files

 
 

  
  
select name from v$controlfile;  
 
 

5. You need to view the log files in common Oracle maintenance SQL statements.

 
 

  
  
select member from v$logfile;  
 
 

6. View table space usage

 
 

  
  
select sum(bytes)/(1024*1024) as free_space,tablespace_name   
  
  
from dba_free_space   
  
  
group by tablespace_name;   
  
  
SELECT A.TABLESPACE_NAME,A.BYTES TOTAL,B.BYTES USED, C.BYTES FREE,   
  
  
(B.BYTES*100)/A.BYTES "% USED",(C.BYTES*100)/A.BYTES "% FREE"   
  
  
FROM SYS.SM$TS_AVAIL A,SYS.SM$TS_USED B,SYS.SM$TS_FREE C   
  
  
WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME AND A.TABLESPACE_NAME=C.TABLESPACE_NAME;   
 
 

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 version FROM Product_component_version   
  
  
Where SUBSTR(PRODUCT,1,6)=''Oracle'';   
 
 

9. view the database creation date and archiving method

 
 

  
  
Select Created, Log_Mode, Log_Mode From V$Database;  
 
 

10. Capture SQL statements that have been running for a long time

 
 

  
  
column username format a12   
  
  
column opname format a16   
  
  
column progress format a8   
  
  
select username,sid,opname,   
  
  
round(sofar*100 / totalwork,0) ''%'' as progress,   
  
  
time_remaining,sql_text   
  
  
from v$session_longops , v$sql   
  
  
where time_remaining <> 0   
  
  
and sql_address = address   
  
  
and sql_hash_value = hash_value   
  
  
/   
  
  
 
 
 

The above content is an introduction to common SQL statements for Oracle maintenance. I hope you will find some gains.

Article by: http://www.programbbs.com/doc/class10-3.htm