Oracle常用SQL命令

1. Oracle查看表空间使用率（包含临时表空间）  

set pagesize 500
set lin 120
col TABLESPACE_NAME for a30
col SUM_SPACE(M) for a15
col SUM_BLOCKS for a35
col USED_SPACE(M) for a15
col USED_RATE(%) for a15
col FREE_SPACE(M) for a15
SELECT D.TABLESPACE_NAME,  
       SPACE || 'M' "SUM_SPACE(M)",  
       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                                                          
SELECT D.TABLESPACE_NAME,  
       SPACE || 'M' "SUM_SPACE(M)",  
       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 1;  

　　

2. 查看表空间的名字和所属文件

 

set line 200;
col tablespace_name format a30;
col file_id format 999;
col file_name format a70;
col total_space format 999999;
 
select tablespace_name, file_id, file_name,
round(bytes/(1024*1024),0) total_space
from dba_data_files
order by tablespace_name;
 
##查看用户和默认的表空间
select username,default_tablespace from dba_users order by 1;

##查看物理文件使用率
select 　　b.file_name 物理文件名,
       　　b.tablespace_name 表空间,
       　　b.bytes / 1024 / 1024 大小M,
       　　(b.bytes - sum(nvl(a.bytes, 0))) / 1024 / 1024 已使用M,
       　　substr((b.bytes - sum(nvl(a.bytes, 0))) / (b.bytes) * 100, 1, 5) 利用率 　　from dba_free_space a,
       dba_data_files b 　　where a.file_id = b.file_id 　　group by b.tablespace_name,
       b.file_name,
       b.bytes 　　order by b.tablespace_name;

## 压缩空间
select 'alter database datafile ''' || file_name || ''' resize ' ||
       ceil((nvl(hwm, 1) * 8192) / 1024 / 1024) || 'm;' cmd
  from dba_data_files a,
       (select file_id, max(block_id + blocks - 1) hwm
          from dba_extents
         group by file_id) b
 where a.file_id = b.file_id(+)
   and ceil(blocks * 8192 / 1024 / 1024) -
       ceil((nvl(hwm, 1) * 8192) / 1024 / 1024) > 0;

 

 3. 将一个用户的所有对象授权给另一个用户：

 

-- 全局授权：
create user readonly identified by passwd_readonly;
grant select any dictionary to readonly;
grant select any table to readonly;
grant connect, resource to readonly;

-----
--授权USER1读取USER1所有表的权限
SELECT 'GRANT SELECT ON USER1.'||TABLE_NAME||' TO USER2;' FROM ALL_TABLES WHERE OWNER = 'USER1';

--首先是CPR账号
--授权表上的读写权限
select 'grant all on ' || owner || '.' || table_name || ' to hisuser;'
  from dba_tables
 where owner = 'CPR';

--授权视图上的读写权限
select 'grant all on ' || owner || '.' || view_name || ' to hisuser;'
  from dba_views
 where owner = 'CPR';

--授权函数和存储过程的读写权限
select 'grant execute on ' || owner || '.' || name || ' to hisuser;'
  from dba_source
 where owner = 'CPR'
   and type in ('PROCEDURE',
                'FUNCTION',
                'PACKAGE',
                'PACKAGE BODY',
                'TYPE BODY',
                'TRIGGER',
                'TYPE');

--授权序列的读写权限
select 'grant all on ' || sequence_owner || '.' || sequence_name ||
       ' to hisuser;'
  from dba_sequences
 where sequence_owner = 'CPR';

--创建同义词
select 'create or replace public synonym ' || synonym_name || ' for ' ||
       table_owner || '.' || table_name || ' ;'
  from dba_synonyms
 where table_owner = 'CPR';
select 'create or replace public synonym ' || view_name || ' for ' || owner || '.' ||
       view_name || ' ;'
  from dba_views
 where owner = 'CPR'
   and (owner NOT LIKE '%$%' OR view_name NOT LIKE '%$%');

--然后是system账号
--授权表上的读写权限
select 'grant all on ' || owner || '.' || table_name || ' to hisuser;'
  from dba_tables
 where owner = 'SYSTEM'
   and table_name NOT LIKE '%$%';

--授权视图上的读写权限
select 'grant all on ' || owner || '.' || view_name || ' to hisuser;'
  from dba_views
 where owner = 'SYS';

--授权函数和存储过程的读写权限
select DISTINCT 'grant execute on ' || owner || '.' || name ||
                ' to hisuser;'
  from dba_source
 where owner = 'SYS'
   and type in ('PROCEDURE',
                'FUNCTION',
                'PACKAGE',
                'PACKAGE BODY',
                'TYPE BODY',
                'TRIGGER',
                'TYPE')
   AND name NOT LIKE '%$%';

--授权序列的读写权限
select 'grant all on ' || sequence_owner || '.' || sequence_name ||
       ' to hisuser;'
  from dba_sequences
 where sequence_owner = 'SYSTEM'
   AND sequence_name NOT LIKE '%$%';

--创建同义词
select 'create or replace public synonym ' || synonym_name || ' for ' ||
       table_owner || '.' || table_name || ' ;'
  from dba_synonyms
 where table_owner = 'SYS'
   and synonym_name NOT LIKE '%$%';

select 'create or replace public synonym ' || view_name || ' for ' || owner || '.' ||
       view_name || ' ;'
  from dba_views
 where owner = 'SYS'
   and (owner NOT LIKE '%$%' OR view_name NOT LIKE '%$%');

 

 4.扩大表空间

 

--1.增加数据文件
ALTER TABLESPACE game ADD DATAFILE '/oracle/oradata/db/GAME02.dbf' SIZE 1000M;

--2.手动增加数据文件尺寸
ALTER DATABASE DATAFILE '/oracle/oradata/db/GAME.dbf' RESIZE 4000M;

--RAC自动管理的表空间增加数据文件的方法：
数据文件：
alter tablespace TEMP add datafile size 30g；
临时文件：
alter tablespace temp add tempfile size 30g;
--1.1 查询普通表空间剩余容量：
SELECT TABLESPACE_NAME,
SUM(BYTES) / 1024 / 1024 AS "FREE SPACE(M)"  
FROM DBA_FREE_SPACE  WHERE TABLESPACE_NAME = '&tablespace_name'  GROUP BY TABLESPACE_NAME;

--1.2 查询临时表空间剩余容量：
SELECT TABLESPACE_NAME,
FREE_SPACE / 1024 / 1024 AS "FREE SPACE(M)"  
FROM DBA_TEMP_FREE_SPACE  WHERE TABLESPACE_NAME = '&tablespace_name';

--2.1 查询普通表空间数据文件目录：
SELECT TABLESPACE_NAME,
FILE_ID,
FILE_NAME,
BYTES / 1024 / 1024 AS "BYTES(M)"  
FROM DBA_DATA_FILES  WHERE TABLESPACE_NAME = '&tablespace_name';

--2.2 查询临时表空间数据文件目录
SELECT TABLESPACE_NAME,
FILE_ID,
FILE_NAME,
BYTES / 1024 / 1024 AS "SPACE(M)"  
FROM DBA_TEMP_FILES  WHERE TABLESPACE_NAME = '&tablespace_name';

--3.1 为普通表空间增加数据文件扩容
ALTER TABLESPACE &tablespace_name ADD DATAFILE '&datafile_name' SIZE 4G;

--3.2 为临时表空间增加数据文件扩容
ALTER TABLESPACE &tablespace_name ADD TEMPFILE '&datafile_name' SIZE 4G;

 

5. 通过RMAN删除过期的归档日志

DELETE  force noprompt ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-5';

 6. 密码默认180天过期修改：

--1
SELECT  *  FROM  dba_profiles  WHERE  profile='DEFAULT'  AND  resource_name='PASSWORD_LIFE_TIME';
--180 天到期
 
更改密码为无期
--2
ALTER  PROFILE  DEFAULT  LIMIT  PASSWORD_LIFE_TIME  UNLIMITED;

7.ASM磁盘剩余空间查看：

select group_number,name,total_mb,free_mb from v$asm_diskgroup;

8.历史归档日志容量查看：

select trunc(COMPLETION_TIME) TIME, SUM(BLOCKS * BLOCK_SIZE)/1024/1024 SIZE_MB from V$ARCHIVED_LOG group by trunc (COMPLETION_TIME) order by 1;

09.验证RMAN备份信息：

SELECT START_TIME,
      END_TIME,
      OUTPUT_DEVICE_TYPE,
      STATUS,
      ELAPSED_SECONDS,
      COMPRESSION_RATIO,
      INPUT_BYTES_DISPLAY,
      OUTPUT_BYTES_DISPLAY
FROM V$RMAN_BACKUP_JOB_DETAILS
ORDER BY START_TIME DESC ;

10. Oracle 杀掉当前用户的进程

select ' alter system kill session '''|| sid ||','||serial# || ''';' from v$SESSION where username='UserName';

 

11. Oracle归档日志清理脚本 

##脚本方法1：
#!/bin/bash
if [ -f ~/.bash_profile ]; then
    . ~/.bash_profile
fi

#set env
echo "Oracle home:"$ORACLE_HOME
echo "Oracle SID:"$ORACLE_SID

$ORACLE_HOME/bin/rman target sys/SysPassword@Oracle_SID log=/tmp/rman.log <<EOF
crosscheck archivelog all;
delete noprompt expired archivelog all;
delete noprompt archivelog all completed before 'sysdate - 5';
exit;
EOF


####脚本方法2：
#!/bin/bash
if [ -f ~/.bash_profile ]; then
    . ~/.bash_profile
fi

#set env
echo "Oracle home:"$ORACLE_HOME
echo "Oracle SID:"$ORACLE_SID

$ORACLE_HOME/bin/rman log=/tmp/rman.log <<EOF       
connect target sys/SysPassword@Oracle_SID
run{
crosscheck archivelog all;    
delete noprompt expired archivelog all;    
delete noprompt archivelog all completed before 'sysdate - 5';    
}  


##设置定时任务：
crontab -l
0 1 * * * /home/oracle/scripts/clear_archivelog.sh > /tmp/clear_archivelog.log

##备注：
1. 必须在Oracle用户下编辑执行脚本
2. 必须给/home/oracle/scripts/clear_archivelog.sh执行权限
3. 必须验证

 

 12. Oracle锁管理

 

##1. 确定锁进程的sid
SELECT
    t2.username,
    t2.sid,
    t2.serial#,
    t2.logon_time
FROM
    v$locked_object t1,
    v$session t2
WHERE
    t1.session_id = t2.sid
ORDER BY
    t2.logon_time;
    
##2. 通过sid获得它的sql，看是哪一条sql导致锁的占用    
SELECT
    sql_text
FROM
    v$sql t1,
    v$session t2
WHERE
    t1.address = t2.sql_address
    AND t2.sid = & sid; --&sid 就是上一条sql中查到的sid

##3.1 如有记录则表示有lock，记录下SID和serial# ，执行下面的sql，即可解除锁    
ALTER system kill SESSION 'SID,serial#';


##3.2 批量删除脚本：
SELECT
    'alter system kill session ''' || sid || ',' || serial# || ''';' "deadlock"
FROM
    v$session
WHERE
    sid IN (
    SELECT
        sid
    FROM
        v$lock
    WHERE
        block = 1);
##注意：应当注意对于 sid 在 100 以下的应当谨慎，可能该进程对应某个application，如对应某个事务,可以 kill.

 

13. 长时间运行SQL语句处理：

##Oracle中查询某个sql是哪个用户发出可用如下语句：
SELECT
    a.sql_text,
    b.username
FROM
    v$sql a,
    v$session b
WHERE
    a.hash_value = b.sql_hash_value;
    
    

##查询执行时间长的SQL语句
SELECT
    sid,
    serial#,
    sql_text,
    executions
FROM
    v$sql
JOIN v$session ON
    v$sql.sql_id = v$session.sql_id
WHERE
    cpu_time > 20000;
    
##杀掉SQL语句：
alter system kill session 'sid,serial#';


##自己编辑的SQL语句，待完善
SELECT
    b.username ,
    b.sid,
    b.serial#,
    a.sql_text
FROM
    v$sql a,
    v$session b
WHERE
    (a.sql_id = b.sql_id)
    AND ( a.hash_value = b.sql_hash_value)
    AND cpu_time > 20000
ORDER BY
    1;

--通过sql_id查找sid,serial#

select sid,serial#,username ,osuser from v$session where (sql_hash_value,sql_address)=(select hash_value,address from v$sqlarea where sql_id='f7285r39svxr6' );

 14.  使用触发器实现记录oracle用户登录失败信息到alert.log日志文件

转载自：https://www.cnblogs.com/xinxin1994/p/6078107.html


CREATE OR REPLACE TRIGGER logon_denied_to_alert
  AFTER servererror ON DATABASE
DECLARE
  message   VARCHAR2(168);
  ip        VARCHAR2(15);
  v_os_user VARCHAR2(80);
  v_module  VARCHAR2(50);
  v_action  VARCHAR2(50);
  v_pid     VARCHAR2(10);
  v_sid     NUMBER;
  v_program VARCHAR2(48);
BEGIN
  IF (ora_is_servererror(1017)) THEN
  
    -- get ip FOR remote connections :
    IF upper(sys_context('userenv', 'network_protocol')) = 'TCP' THEN
      ip := sys_context('userenv', 'ip_address');
    END IF;
  
    SELECT sid INTO v_sid FROM sys.v_$mystat WHERE rownum < 2;
    SELECT p.spid, v.program
      INTO v_pid, v_program
      FROM v$process p, v$session v
     WHERE p.addr = v.paddr
       AND v.sid = v_sid;
  
    v_os_user := sys_context('userenv', 'os_user');
    dbms_application_info.read_module(v_module, v_action);
  
    message := to_char(SYSDATE, 'YYYYMMDD HH24MISS') ||
               ' logon denied from ' || nvl(ip, 'localhost') || ' ' ||
               v_pid || ' ' || v_os_user || ' with ' || v_program || ' – ' ||
               v_module || ' ' || v_action;
  
    sys.dbms_system.ksdwrt(2, message);
  
  END IF;
END;

 

15.  解决Oracle 11gR2 空闲连接过多，导致连接数满的问题

 

转载自：http://blog.itpub.net/28998293/viewspace-1153969/

CREATE PROFILE KILLIDLE LIMIT IDLE_TIME 30;
SELECT * FROM dba_profiles WHERE PROFILE='KILLIDLE';
ALTER USER TEST_USER PROFILE KILLIDLE;
SELECT username,PROFILE FROM dba_users WHERE username='TEST_USER';
ALTER SYSTEM SET resource_limit=TRUE;

CREATE OR REPLACE PROCEDURE sp_kill_idlesession
/**********************************
    清除idle超时的会话进程
    **********************************/
 AS
    CURSOR c_kill_sqls IS
        SELECT 'alter system kill session ''' || s.sid || ',' || s.SERIAL# ||
               ''' immediate' sqlstr
        FROM   v$session s
        WHERE  s.STATUS = 'SNIPED';
BEGIN
    FOR v_sql IN c_kill_sqls LOOP
        EXECUTE IMMEDIATE v_sql.sqlstr;
    END LOOP;
END;

--添加JOB，定时清理过期会话
DECLARE
    jobnum NUMBER := 661;
BEGIN
    dbms_job.submit(job       => jobnum,
                    what      => 'sp_kill_idlesession;',
                    next_date => to_date('30-04-2014 18:00:00',
                                         'dd-mm-yyyy hh24:mi:ss'),
                    INTERVAL  => 'SYSDATE + 1/144');
    COMMIT;
END;

--如果30分钟过期时间太短，对数据库访问性能产生了影响，可以调整
ALTER PROFILE KILLIDLE LIMIT IDLE_TIME 30; 

 

16. 查询Oracle中当前登录用户的IP等信息

 

--01) 利用 DBMS_SESSION 过程包，先执行
BEGIN 
DBMS_SESSION.set_identifier(SYS_CONTEXT('USERENV', 'IP_ADDRESS'));
END;
/

--02) 再执行触发器trigger
create or replace trigger on_logon_trigger
after logon on database
begin
dbms_application_info.set_client_info(sys_context( 'userenv', 'ip_address' ) );
end;
/

--03) 查看正在登录的用户名、IP、机器和客户端工具等信息
select username,program,machine,client_info,sys_context('userenv','ip_address') as ipadd from v$session s  where username is not null  order by username,program,machine;

17.  列出Oracle所有业务用户的所有表的数据类型是varchar且长度小于64位

select a.owner, a.table_name ,b.column_name,b.data_type,b.data_length ,c.comments
from all_tables a , all_tab_cols b , all_col_comments c 
where a.table_name = b.table_name
       and b.table_name = c.table_name
       and b.column_name = c.column_name
       and a.owner in ('AA', 'BB', 'CC', 'DD', 'STD')
       and b.data_type like 'VARCHAR%' 
       and b.data_length < 64
order by owner;