oracle 常用查询总结

<pre name="code" class="html"><pre name="code" class="sql">1、查询表空间使用量，需要有DBA权限 SELECT UPPER(F.TABLESPACE_NAME) "表空间名"  ,D.TOT_GROOTTE_MB "表空间大小(M)"  , D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)"  , TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100  ,2)   , '990.99') "使用比"  , F.TOTAL_BYTES "空闲空间(M)"  , F.MAX_BYTES "最大块(M)",D.FILE_NAMEFROM (SELECT TABLESPACE_NAME  ,ROUND(SUM(BYTES) / (1024 * 1024)   , 2) TOTAL_BYTES  ,ROUND(MAX(BYTES) / (1024 * 1024)   , 2) MAX_BYTESFROM SYS.DBA_FREE_SPACEGROUP BY TABLESPACE_NAME) F  , (SELECT DD.TABLESPACE_NAME  ,ROUND(SUM(DD.BYTES) /(1024 * 1024)   , 2) TOT_GROOTTE_MB,MAX(DD.FILE_NAME) AS FILE_NAMEFROM SYS.DBA_DATA_FILES DDGROUP BY DD.TABLESPACE_NAME) DWHERE D.TABLESPACE_NAME = F.TABLESPACE_NAMEORDER BY 4 DESC;2 、查表空间创建时间SELECT A.FILE_NAME,       A.TABLESPACE_NAME,       TO_CHAR(B.CREATION_TIME, 'YYYY - MM - DD') CREATION_TIME  FROM DBA_DATA_FILES A, V$DATAFILE B WHERE A.FILE_ID = B.FILE# ORDER BY TABLESPACE_NAME; --下面是将上面两种全写在一起的WITH TAB1 AS (SELECT A.FILE_NAME,       A.TABLESPACE_NAME,       TO_CHAR(B.CREATION_TIME, 'YYYY - MM - DD') 创建时间  FROM DBA_DATA_FILES A, V$DATAFILE B WHERE A.FILE_ID = B.FILE# ORDER BY TABLESPACE_NAME) ,TAB2 AS ( SELECT UPPER(F.TABLESPACE_NAME) "表空间名"  ,D.TOT_GROOTTE_MB "表空间大小(M)"  , D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)"  , TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100  ,2)   , '990.99') "使用比"  , F.TOTAL_BYTES "空闲空间(M)"  , F.MAX_BYTES "最大块(M)",D.FILE_NAME "路径"FROM (SELECT TABLESPACE_NAME  ,ROUND(SUM(BYTES) / (1024 * 1024)   , 2) TOTAL_BYTES  ,ROUND(MAX(BYTES) / (1024 * 1024)   , 2) MAX_BYTESFROM SYS.DBA_FREE_SPACEGROUP BY TABLESPACE_NAME) F  , (SELECT DD.TABLESPACE_NAME  ,ROUND(SUM(DD.BYTES) /(1024 * 1024)   , 2) TOT_GROOTTE_MB,MAX(DD.FILE_NAME) AS FILE_NAMEFROM SYS.DBA_DATA_FILES DDGROUP BY DD.TABLESPACE_NAME) DWHERE D.TABLESPACE_NAME = F.TABLESPACE_NAMEORDER BY 4 DESC) SELECT A.*,B.创建时间 FROM TAB2 A INNER JOIN TAB1 B ON A.表空间名=B.TABLESPACE_NAME ORDER BY 2 DESC ----------------------------------------------------------------------------------------------------------------3、查表大小及创建、分析时间、行数字段 NUM_ROWS 要经过表分析后才准确   分析表语句为：ANALYZE TABLE HA_SOAR.SP_ORG_USER COMPUTE STATISTICS;SELECT 'ANALYZE TABLE '||OWNER||'.'|| TABLE_NAME ||' COMPUTE STATISTICS;' FROM DBA_TABLES WHERE OWNER='HA_SOAR';SELECT A.TABLE_NAME 表名,       B.CREATED 创建时间,       B.LAST_DDL_TIME 最后修改时间,       C.LAST_ANALYZED 最后分析时间,       C.TABLESPACE_NAME 表空间,       C.NUM_ROWS 行数,       D.BYTES / 1024 / 1024||'M' 大小,       C.BLOCKS 数据块数,       C.EMPTY_BLOCKS 空块数,       A.COMMENTS 表注释  FROM USER_TAB_COMMENTS A INNER JOIN USER_OBJECTS B  ON A.TABLE_NAME = B.OBJECT_NAME INNER JOIN USER_TABLES C   ON B.OBJECT_NAME = C.TABLE_NAME INNER JOIN USER_SEGMENTS D ON C.TABLE_NAME = D.SEGMENT_NAME   AND D.SEGMENT_TYPE = 'TABLE' ORDER BY B.CREATED DESC4、某个表的列名，数据类型，和注释SELECT A.COLUMN_NAME AS 列名,       DATA_TYPE || '(' || DATA_LENGTH || ')' AS 数据类型,       B.COMMENTS AS 注释说明  FROM USER_TAB_COLUMNS A INNER JOIN USER_COL_COMMENTS B    ON A.COLUMN_NAME = B.COLUMN_NAME WHERE A.TABLE_NAME = UPPER('&TABLE')   AND B.TABLE_NAME = UPPER('&TABLE') ORDER BY A.COLUMN_ID-----------------------------------------------------------------------------------------------------------------------5、批量删除一个用户下的表，以下有两种方式BEGINFOR TABLENAME IN(SELECT TABLE_NAME FROM USER_TABLES) LOOP   EXECUTE IMMEDIATE 'DROP TABLE '||TABLENAME.TABLE_NAME ||' PURGE';  END LOOP;END;DECLARECURSOR TIANMING_DROP IS SELECT TABLE_NAME FROM USER_TABLES;HANG VARCHAR2(50);BEGIN  OPEN TIANMING_DROP;  LOOP   FETCH TIANMING_DROP INTO HANG;  EXIT WHEN TIANMING_DROP%NOTFOUND;  EXECUTE IMMEDIATE  'DROP  TABLE '|| HANG ;  END LOOP;  CLOSE TIANMING_DROP;END;类似有批量授权DECLARE CURSOR CUR_A IS SELECT TABLE_NAME FROM USER_TABLES;V_SQL VARCHAR2(2000);BEGINFOR TABLENAME IN CUR_A LOOP      V_SQL:='GRANT SELECT,INSERT ON ' ||TABLENAME.TABLE_NAME|| ' TO DBO_PROPERTYMANAGEMENTCENTERIM';      EXECUTE IMMEDIATE V_SQL;     END LOOP;END;DECLARE CURSOR CUR_A IS SELECT TABLE_NAME FROM USER_TABLES;V_SQL VARCHAR2(2000);BEGINFOR TABLENAME IN CUR_A LOOP      V_SQL:='REVOKE SELECT,INSERT ON ' ||TABLENAME.TABLE_NAME|| ' FROM DBO_PROPERTYMANAGEMENTCENTERIM';      EXECUTE IMMEDIATE V_SQL;     END LOOP;END;--批量 TRUNCATE或DROP DECLARE CURSOR CUR_TRUNC IS SELECT  TABLE_NAME FROM USER_TABLES;BEGINFOR CUR_DEL IN CUR_TRUNC LOOP  EXECUTE IMMEDIATE 'TRUNCATE TABLE '||CUR_DEL.TABLE_NAME;  END LOOP;END;  --------------------------------------------------------------------------------------------------------------6、查询临时表空间及收缩临时表空间SELECT F.TABLESPACE_NAME,D.FILE_NAME "TEMPFILE NAME",ROUND((F.BYTES_FREE + F.BYTES_USED) / 1024 /1024, 2) "TOTAL MB",ROUND(((F.BYTES_FREE + F.BYTES_USED) -NVL(P.BYTES_USED, 0)) / 1024 / 1024, 2) "FREE MB" ,ROUND(NVL(P.BYTES_USED, 0)/ 1024 / 1024, 2)"USED MB",ROUND((ROUND(NVL(P.BYTES_USED, 0)/ 1024 /1024, 2)/ROUND((F.BYTES_FREE + F.BYTES_USED) / 1024 / 1024, 2))*100,2) AS"USED_RATE(%)"FROM SYS.V_$TEMP_SPACE_HEADER F,DBA_TEMP_FILES D, SYS.V_$TEMP_EXTENT_POOL PWHERE F.TABLESPACE_NAME(+) = D.TABLESPACE_NAMEAND F.FILE_ID(+) = D.FILE_IDAND P.FILE_ID(+) =D.FILE_ID; 执行TEMP 表空间的ONLINE SHRINK 操作：--将临时表空间 TEMP_DATA  压缩到 100M  如果不指定 KEEP 会压缩到最小2M 建议加上 KEEP ALTER TABLESPACE TEMP_DATA SHRINK SPACE KEEP 100M;  也可以对某个 表空间中的数据文件进行压缩SELECT * FROM DBA_TEMP_FILES;  --查数据文件ALTER TABLESPACE TEMP_HA_WXZJ_DATA SHRINK TEMPFILE 'C:\APP\ADMINISTRATOR\ORADATA\ORCL\TEMP_HA_WXZJ_DATA.DBF'   KEEP 50M7、查询索引SELECT B.TABLE_NAME,       B.COLUMN_NAME,       A.INDEX_NAME,       A.INDEX_TYPE,       A.UNIQUENESS,       A.TABLESPACE_NAME,       A.LOGGING,       A.BLEVEL,       A.LEAF_BLOCKS,       A.DISTINCT_KEYS  FROM USER_INDEXES A INNER JOIN USER_IND_COLUMNS B ON A.INDEX_NAME=B.INDEX_NAME ORDER BY B.TABLE_NAME;8、查询锁表信息SELECT 'LOCK' "STATUS",  A.USERNAME "用户名", A.SID "SID", A.SERIAL# "SERIAL#",  B.TYPE "锁类型",  DECODE(B.LMODE, 1, 'NO LOCK',                   2, 'ROW SHARE',                   3, 'ROW EXCLUSIVE', 4, 'SHARE',                   5, 'SHARE ROW EXCLUSIVE',                   6, 'EXCLUSIVE', 'NONE') "占用的模式",  DECODE(B.REQUEST, 1, 'NO LOCK',                    2, 'ROW SHARE',                     3, 'ROW EXCLUSIVE',                     4, 'SHARE',                     5, 'SHARE ROW EXCLUSIVE',                     6, 'EXCLUSIVE', 'NONE') "请求的模式",  C.OBJECT_NAME "对象名",  C.OWNER "对象所有者", C.OBJECT_TYPE "对象类型",  B.ID1 "资源ID1", B.ID2 "资源ID2",B.CTIME "CTIME(秒) ",  'ALTER SYSTEM KILL SESSION '''||A.SID||','||A.SERIAL#||''';' "KILL SESSION ",  'KILL -9 '||D.SPID "KILL PROCESS (UNIX LINUX)",   'ORAKILL '||F.INSTANCE_NAME||' '||D.SPID "KILL PROCESS (WINDOWS)" FROM V$SESSION A, V$LOCK B, V$LOCKED_OBJECT B1, DBA_OBJECTS C, V$PROCESS D, V$INSTANCE FWHERE A.TYPE <> 'BACKGROUND'  AND A.SID = B.SID  AND B.REQUEST = 0  AND D.ADDR = A.PADDR  AND B1.SESSION_ID = A.SID  AND B1.OBJECT_ID = C.OBJECT_ID  AND F.STATUS = 'OPEN'  AND F.DATABASE_STATUS = 'ACTIVE'ORDER BY B.CTIME