Oracle常用维护SQL汇总

2016-12-30 19:50:06来源:CSDN作者:u011098327人点击

第七城市

1. 查看表空间相关信息

SELECT TABLESPACE_NAME,       BLOCK_SIZE, --db_block_size大小       INITIAL_EXTENT  FROM DBA_TABLESPACES

2.表空间对应文件名称及大小

SELECT TABLESPACE_NAME,       FILE_ID,       FILE_NAME,       ROUND(BYTES / (1024 * 1024), 0) TOTAL_SPACE  FROM DBA_DATA_FILES ORDER BY TABLESPACE_NAME

3.查看表空间大小,使用情况

SELECT UPPER(F.TABLESPACE_NAME) AS TABLESPACE_NAME, --表空间名称      D.TOTAL_BYTES  AS TOTAL_BYTES,               --表空间大小M       F.FREE_BYTES   AS FREE_BYTES,                --空闲大小M     D.TOTAL_BYTES - F.FREE_BYTES AS USEAGE_BYTES, --使用大小M     TO_CHAR(ROUND((D.TOTAL_BYTES - F.FREE_BYTES) / D.TOTAL_BYTES * 100, 2), '990.99')                                   AS USEAGE_PERCENT, --使用占比     F.MAX_BYTES  AS BLOCK_MAX_BYTES               --最大BLOCK块 FROM (SELECT TABLESPACE_NAME,              ROUND(SUM(BYTES) / (1024 * 1024), 2) FREE_BYTES,              ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES         FROM SYS.DBA_FREE_SPACE        GROUP BY TABLESPACE_NAME) F,      (SELECT DD.TABLESPACE_NAME,              ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOTAL_BYTES         FROM SYS.DBA_DATA_FILES DD        GROUP BY DD.TABLESPACE_NAME) D  WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME  ORDER BY 4 DESC


4.查看数据库版本相关信息

SELECT PRODUCT,VERSION,STATUS FROM PRODUCT_COMPONENT_VERSION

5.查看表大小

analyze table tablename compute statistics; SELECT NUM_ROWS * AVG_ROW_LEN FROM USER_TABLES WHERE TABLE_NAME = 'tablename';

6.查看正在执行的SQL
SELECT b.sid oracleID,       b.username,       b.machine,       b.PROGRAM,       b.serial#,       spid ,       STATUS,       paddr,       sql_text,       c.CPU_TIMEFROM v$process a, v$session b, v$sqlarea cWHERE a.addr = b.paddr   AND b.sql_hash_value = c.hash_value

7.查看当前表被锁对象

SELECT SESS.SID,       SESS.SERIAL#,       LO.ORACLE_USERNAME,       LO.OS_USER_NAME,       AO.OBJECT_NAME,       LO.LOCKED_MODE,       SESS.MACHINE,       SESS.TERMINAL,       SESS.LOGON_TIME  FROM V$LOCKED_OBJECT LO, DBA_OBJECTS AO, V$SESSION SESS WHERE AO.OBJECT_ID = LO.OBJECT_ID   AND LO.SESSION_ID = SESS.SID;    ALTER SYSTEM KILL SESSION 'SID,SERIAL#';   --杀掉锁表进程


第七城市

最新文章

123

最新摄影

微信扫一扫

第七城市微信公众平台