ORACLE常用的维护语句
select * from v$version;<br><br>查看表空间的使用情况<br><br>select sum(bytes)/(1024*1024) as free_space,tablespace_name<br>from dba_free_space<br>group by tablespace_name;<br><br><br>SELECT A.TABLESPACE_NAME,A.BYTES TOTAL,B.BYTES USED, C.BYTES FREE,<br>(B.BYTES*100)/A.BYTES "% USED",(C.BYTES*100)/A.BYTES "% FREE"<br>FROM SYS.SM$TS_AVAIL A,SYS.SM$TS_USED B,SYS.SM$TS_FREE C<br>WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME AND A.TABLESPACE_NAME=C.TABLESPACE_NAME order by TABLESPACE_NAME ;<br><br>1、查看表空间的名称及大小<br><br>select t.tablespace_name, round(sum(bytes/(1024*1024)),0) ts_size<br>from dba_tablespaces t, dba_data_files d<br>where t.tablespace_name = d.tablespace_name<br>group by t.tablespace_name order by tablespace_name ;<br><br><br>2、查看表空间物理文件的名称及大小，是否自扩展<br><br>select tablespace_name, file_id, file_name,<br>round(bytes/(1024*1024),0) total_space<br>from dba_data_files<br>order by file_name;<br><br>select FILE_NAME,STATUS,AUTOEXTENSIBLE,round(MAXBYTES/1024/1024) mb,<br>INCREMENT_BY "Incre"<br>from dba_data_files<br>order by 3;<br><br><br><br>3、 查看回滚段名称及大小<br><br>select segment_name, tablespace_name, r.status,<br>(initial_extent/1024) InitialExtent,(next_extent/1024) NextExtent,<br>max_extents, v.curext CurExtent<br>From dba_rollback_segs r, v$rollstat v<br>Where r.segment_id = v.usn(+)<br>order by segment_name ;<br><br><br>4、查看控制文件<br><br>select name from v$controlfile;<br><br><br>5、查看日志文件<br><br>select member from v$logfile;<br><br><br>6、查看表空间的使用情况<br><br>select sum(bytes)/(1024*1024) as free_space,tablespace_name<br>from dba_free_space<br>group by tablespace_name;<br><br><br>SELECT A.TABLESPACE_NAME,A.BYTES TOTAL,B.BYTES USED, C.BYTES FREE,<br>(B.BYTES*100)/A.BYTES "% USED",(C.BYTES*100)/A.BYTES "% FREE"<br>FROM SYS.SM$TS_AVAIL A,SYS.SM$TS_USED B,SYS.SM$TS_FREE C<br>WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME AND A.TABLESPACE_NAME=C.TABLESPACE_NAME;<br><br>set pagesize 1000 linesize 150<br>col file_name for a50<br>SELECT a.TABLESPACE_NAME,a.FILE_NAME,a.bytes/(1024*1024) "TOTAL Mb",b.bytes/(1024*1024) "USED Mb",to_char((b.bytes*100)/a.bytes, '99.9' )"%USED" FROM DBA_DATA_FILES a, SYS.SM$TS_USED b WHERE a.tablespace_name = b.tablespace_name order by tablespace_name;<br><br>7、查看数据库库对象<br><br>select owner, object_type, status, count(*) count# from all_objects group by owner, object_type, status;<br><br><br>8、查看数据库的版本 <br><br>Select version FROM Product_component_version<br>Where SUBSTR(PRODUCT,1,6)='Oracle';<br><br>9、 查看数据库的创建日期和归档方式<br><br>Select Created, Log_Mode, Log_Mode From V$Database;<br><br>10、如何远程判断Oracle数据库的安装平台<br><br>select * from v$version;<br><br>11、查看数据表的参数信息<br><br>
页:
[1]