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;
