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_NAME
FROM 
(SELECT 
TABLESPACE_NAME  ,ROUND(SUM(BYTES) / (1024 * 1024)   , 2) TOTAL_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) TOT_GROOTTE_MB,MAX(DD.FILE_NAME) AS FILE_NAME
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER 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_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F  , 
(SELECT 
DD.TABLESPACE_NAME  ,ROUND(SUM(DD.BYTES) /(1024 * 1024)   , 2) TOT_GROOTTE_MB,MAX(DD.FILE_NAME) AS FILE_NAME
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER 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 DESC






4、某个表的列名，数据类型，和注释
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、批量删除一个用户下的表，以下有两种方式


BEGIN
FOR TABLENAME IN(SELECT TABLE_NAME FROM USER_TABLES) LOOP 
  EXECUTE IMMEDIATE 'DROP TABLE '||TABLENAME.TABLE_NAME ||' PURGE';
  END LOOP;
END;




DECLARE
CURSOR 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);
BEGIN
FOR 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);
BEGIN
FOR 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;
BEGIN
FOR 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 P
WHERE F.TABLESPACE_NAME(+) = D.TABLESPACE_NAME
AND F.FILE_ID(+) = D.FILE_ID
AND 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 50M






7、查询索引


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 F
WHERE 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