李
- <>
查看表空间相关信息
李>SELECT TABLESPACE_NAME, ,,,,,,,BLOCK_SIZE——db_block_size大小 ,,,,,INITIAL_EXTENT 之前得到才能DBA_TABLESPACES >2。表空间对应文件名称及大小
SELECT TABLESPACE_NAME, ,,,,,,FILE_ID, ,,,,,,FILE_NAME, ,,,,,,圆(时间/BYTES (1024, *, 1024), 0), TOTAL_SPACE ,得到DBA_DATA_FILES ,ORDER BY  TABLESPACE_NAME;3。查看表空间大小,使用情况
SELECT 上部(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(圆((D.TOTAL_BYTES 作用;F.FREE_BYTES),/, D.TOTAL_BYTES *, 100, 2),,“990.99”), ,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,AS USEAGE_PERCENT,——使用占比 ,,,F.MAX_BYTES AS BLOCK_MAX_BYTES ,,,,,,,,,,,,,,,最块大块 得到(SELECT TABLESPACE_NAME,, ,,,,,,,,,,圆(总和(字节),/,(1024,*,1024),2),FREE_BYTES,, ,,,,,,,,,,圆(MAX(字节),/,(1024,*,1024),2),MAX_BYTES ,,,,,得到SYS.DBA_FREE_SPACE ,,,,GROUP BY TABLESPACE_NAME), F,, ,,(SELECT DD.TABLESPACE_NAME,, ,,,,,,,,,,圆(总和(DD.BYTES),/, (1024, *, 1024), 2), TOTAL_BYTES ,,,,,得到SYS.DBA_DATA_FILES DD ,,,,GROUP BY DD.TABLESPACE_NAME), D ,WHERE D.TABLESPACE_NAME =, F.TABLESPACE_NAME ,ORDER BY 4, DESC;4。查看数据库版本相关信息
SELECT 产品版本,STATUS 得到PRODUCT_COMPONENT_VERSION;5。查看表大小
analyze table tablename compute 统计;, SELECT NUM_ROWS *, AVG_ROW_LEN 得到USER_TABLES WHERE TABLE_NAME =,“表”,6。查看正在执行的SQL
SELECT b.sid oracleID, ,,,,,,b.username, ,,,,,,b.machine, ,,,,,,b.PROGRAM, ,,,,,,b.serial #, ,,,,,,spid , ,,,,,,, ,,,,,,paddr, ,,,,,,sql_text, ,,,,,c.CPU_TIME 得到v $ process ,, v $ session b、v $ sqlarea c WHERE a.addr =b.paddr ,,以及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 得到才能V $ LOCKED_OBJECT 看哪,,DBA_OBJECTS AO, V $ SESSION 税 ,WHERE AO.OBJECT_ID =LO.OBJECT_ID ,,以及LO.SESSION_ID =, SESS.SID; , ALTER 才能SYSTEM KILL  SESSION “SID,串行#”,,,,,杀掉锁表进程
甲骨文常用SQL汇总(持续更新)