oracle常用SQL语句

常用数据字典查询语句：

 

查看表空间使用情况：

语句一：

/* Formatted on 2012/5/31 14:51:13 (QP5 v5.185.11230.41888) */ 
SELECT D.TABLESPACE_NAME, 
       SPACE || 'M' "SUM_SPACE(M)", 
       BLOCKS "SUM_BLOCKS", 
       SPACE - NVL (FREE_SPACE, 0) || 'M' "USED_SPACE(M)", 
       ROUND ( (1 - NVL (FREE_SPACE, 0) / SPACE) * 100, 2) || '%' 
          "USED_RATE(%)", 
       FREE_SPACE || 'M' "FREE_SPACE(M)" 
  FROM (  SELECT TABLESPACE_NAME, 
                 ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE, 
                 SUM (BLOCKS) BLOCKS 
            FROM DBA_DATA_FILES 
        GROUP BY TABLESPACE_NAME) D, 
       (  SELECT TABLESPACE_NAME, 
                 ROUND (SUM (BYTES) / (1024 * 1024), 2) FREE_SPACE 
            FROM DBA_FREE_SPACE 
        GROUP BY TABLESPACE_NAME) F 
 WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+) 
UNION ALL                                                           --if have temp_tablespace 
SELECT D.TABLESPACE_NAME, 
       SPACE || 'M' "SUM_SPACE(M)", 
       BLOCKS SUM_BLOCKS, 
       USED_SPACE || 'M' "USED_SPACE(M)", 
       ROUND (NVL (USED_SPACE, 0) / SPACE * 100, 2) || '%' "USED_RATE(%)", 
       NVL (FREE_SPACE, 0) || 'M' "FREE_SPACE(M)" 
  FROM (  SELECT TABLESPACE_NAME, 
                 ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE, 
                 SUM (BLOCKS) BLOCKS 
            FROM DBA_TEMP_FILES 
        GROUP BY TABLESPACE_NAME) D, 
       (  SELECT TABLESPACE_NAME, 
                 ROUND (SUM (BYTES_USED) / (1024 * 1024), 2) USED_SPACE, 
                 ROUND (SUM (BYTES_FREE) / (1024 * 1024), 2) FREE_SPACE 
            FROM V$TEMP_SPACE_HEADER 
        GROUP BY TABLESPACE_NAME) F 
 WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+) 
ORDER BY "USED_RATE(%)";

语句二：

--查询表空间使用情况
　　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)"
　　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
　　FROM SYS.DBA_DATA_FILES DD
　　GROUP BY DD.TABLESPACE_NAME) D
　　WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
　　ORDER BY 1
　

语句三：

select f.tablespace_name as tablespace_name,
       a.total as "total(MB)",
       u.used as "used(MB)",
       f.free as "free(MB)",
       round((u.used / a.total) * 100) "% used",
       round((f.free / a.total) * 100) "% Free"
  from (select tablespace_name, sum(bytes / (1024 * 1024)) total
          from dba_data_files
         group by tablespace_name) a,
       (select tablespace_name, round(sum(bytes / (1024 * 1024))) used
          from dba_extents
         group by tablespace_name) u,
       (select tablespace_name, round(sum(bytes / (1024 * 1024))) free
          from dba_free_space
         group by tablespace_name) f
 WHERE a.tablespace_name = f.tablespace_name
   and a.tablespace_name = u.tablespace_name;

 

————————————————————————————————————————————————————————————

 

 

##查看表空间及对应数据文件：
select file_name,tablespace_name,status from dba_data_files;

##查看实例状态
select status from v$instance;

##查看数据库状态：
select open_mode from v$database;

 

##通过PID查看对应的正在运行的sql语句：

SELECT   /*+ ORDERED */
         sql_text
    FROM v$sqltext a
   WHERE (a.hash_value, a.address) IN (
            SELECT DECODE (sql_hash_value,
                           0, prev_hash_value,
                           sql_hash_value
                          ),
                   DECODE (sql_hash_value, 0, prev_sql_addr, sql_address)
              FROM v$session b
             WHERE b.paddr = (SELECT addr
                                FROM v$process c
                               WHERE c.spid ='9786'))
ORDER BY piece ASC;

 

##查询数据库内无效索引：

select * from dba_objects where status!='VALID' and object_type='INDEX';  

 

##查看当前数据库使用的参数文件是spfile还是pfile？

show parameter spfile;  查看values值，如果有值则为spfile，且说明路径；若value为空则使用pfile，默认位置为 $ORACLE_HOME/dbs,如果非默认位置就不好找了。

 

##创建业务表空间

CREATE TABLESPACE apple

NOLOGGING

DATAFILE

 '/oracle/oradata/apple01.dbf'  SIZE  2000M,

 '/oracle/oradata/apple02.dbf'  SIZE  2000M

AutoExtend On Next 100M Maxsize 20000M

EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K

SEGMENT SPACE MANAGEMENT AUTO;

 

ALTER TABLESPACE apple LOGGING;

 

 

##/*创建用户并授权*/

CREATE USER apple  IDENTIFIED BY apple

DEFAULT TABLESPACE apple

TEMPORARY TABLESPACE  temp;

 

GRANT CONNECT,RESOURCE TO apple;

REVOKE UNLIMITED TABLESPACE FROM apple;

ALTER USER apple QUOTA UNLIMITED ON apple;

 

##修改数据库默认表空间

SQL> alter database default tablespace test;

Database altered.

SQL> select a.property_name, a.property_value from database_properties a where a.property_name like '%DEFAULT%';

PROPERTY_NAME                  PROPERTY_VALUE

------------------------------ --------------------

DEFAULT_TEMP_TABLESPACE        TEMP

DEFAULT_PERMANENT_TABLESPACE   TEST

DEFAULT_TBS_TYPE               SMALLFILE