表分析：
BEGIN
DBMS_STATS.gather_table_stats (ownname => 'ORPHEN',
tabname => 'DEPT',
partname => NULL
);
END;
-----------------------------------------------------------------------------------------
BEGIN
DBMS_STATS.gather_table_stats (ownname => 'ORPHEN',
tabname => 'DEPT',
partname => NULL,
estimate_percent => 33
);
END;
-----------------------------------------------------------------------------------------
BEGIN
DBMS_STATS.delete_table_stats (ownname => 'ORPHEN', tabname => 'DEPT');
END;
-----------------------------------------------------------------------------------------
Analyze Table ORPHEN.BIG_TABLE --TOAD 分析表的方法
Estimate Statistics
Sample 33 Percent;
2007-9-22
刷新shared_pool,buffer_cache
一、oracle9i
1、刷新shared_pool
alter system flush shared_pool;
2、刷新buffer_cache
会话级：
alter session set events 'immediate trace name flush_cache level 1';
alter session set events = 'immediate trace name flush_cache';
系统级：
alter system set events = 'immediate trace name flush_cache';
二、oracle10g
1、刷新shared_pool
alter system flush shared_pool;
2、刷新buffer_cache
alter system flush buffer_cache;
Lock Table
SQL> LOCK TABLE hr.employees IN EXCLUSIVE MODE;
Table(s) Locked.
2007-9-23
Flashback queries
SQL> SELECT salary FROM employees WHERE employee_id=100;
SALARY
------
25
SQL> SELECT salary FROM employees
2 AS OF TIMESTAMP(SYSTIMESTAMP-INTERVAL°10° minute)
3 WHERE employee_id=100;
SALARY
------
24000
--Flashbback droped table
SQL> FLASHBACK TABLE hr.job_history TO BEFORE DROP;
Flashback complete.
select x from table for update nowait -- 直接判断,不等待进程结束
1. 查找全表扫描的SQL
SELECT sql_text
FROM v$sqltext t, v$sql_plan p
WHERE t.hash_value = p.hash_value
AND p.operation = 'TABLE ACCESS'
AND p.options = 'FULL'
ORDER BY p.hash_value, t.piece;
2. 查找Fast Full Index 扫描 的SQL
SELECT sql_text
FROM v$sqltext t, v$sql_plan p
WHERE t.hash_value = p.hash_value
AND p.operation = 'INDEX'
AND p.options = 'FULL SCAN'
ORDER BY p.hash_value, t.piece;
当前用户的session
select sid,serial# from v$session where sid =(select sid from v$mystat where rownum =1)
循环更新
declare
row_num number := 0;
begin
for c_usr in (select login_id from im_user t where id is null) loop
update im_user i set i.id =
(select id from bmw_users u where i.login_id = u.nick)
where login_id = c_usr.login_id;
row_num := row_num + 1;
if mod(row_num,1000) =0 then
commit;
end if;
end loop;
commit;
end;
/
showlock.sql
第一个脚本showlock.sql，该脚本通过连接v$locked_object与all_objects两视图，显示哪些对象被哪些会话锁住：
/* showlock.sql */
column o_name format a10
column lock_type format a20
column object_name format a15
select rpad(oracle_username,10) o_name,session_id sid,
decode(locked_mode,0,'None',1,'Null',2,'Row share',
3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive') lock_type,
object_name ,xidusn,xidslot,xidsqn
from v$locked_object,all_objects
where v$locked_object.object_id=all_objects.object_id;
showalllock.sql
第二个脚本showalllock.sql，该脚本主要显示当前所有TM、TX锁的信息；
/* showalllock.sql */
select sid,type,id1,id2,
decode(lmode,0,'None',1,'Null',2,'Row share',
3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive')
lock_type,request,ctime,block
from v$lock
where TYPE IN('TX','TM');
----------查询锁住-------------
column sid format 999;
column b format 9;
column object_name format a30;
select v$lock.sid,
decode(v$lock.type,
'MR', 'Media Recovery',
'RT','Redo Thread',
'UN','User Name',
'TX', 'Transaction',
'TM', 'DML',
'UL', 'PL/SQL User Lock',
'DX', 'Distributed Xaction',
'CF', 'Control File',
'IS', 'Instance State',
'FS', 'File Set',
'IR', 'Instance Recovery',
'ST', 'Disk Space Transaction',
'TS', 'Temp Segment',
'IV', 'Library Cache Invalida-tion',
'LS', 'Log Start or Switch',
'RW', 'Row Wait',
'SQ', 'Sequence Number',
'TE', 'Extend Table',
'TT', 'Temp Table',
'Unknown') LockType,
rtrim(object_type) || ' ' || rtrim(owner) || '.' || object_name object_name,
decode(lmode, 0, 'None',
1, 'Null',
2, 'Row-S',
3, 'Row-X',
4, 'Share',
5, 'S/Row-X',
6, 'Exclusive', 'Unknown') LockMode,
decode(request, 0, 'None',
1, 'Null',
2, 'Row-S',
3, 'Row-X',
4, 'Share',
5, 'S/Row-X',
6, 'Exclusive', 'Unknown') RequestMode,
ctime, block b
from v$lock, all_objects
where sid > 6
and v$lock.id1 = all_objects.object_id;
------------
------------查询表空间使用情况-------------------------
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 4 DESC
--------------------------------------
如何查询做比较大的排序的进程？
SELECT b.tablespace, b.segfile#, b.segblk#, b.blocks, a.sid, a.serial#,
a.username, a.osuser, a.status
FROM v$session a,v$sort_usage b
WHERE a.saddr = b.session_addr
ORDER BY b.tablespace, b.segfile#, b.segblk#, b.blocks ;
如何查询做比较大的排序的进程的SQL语句？
select /*+ ORDERED */ sql_text from v$sqltext a
where a.hash_value = (
select sql_hash_value from v$session b
where b.sid = &sid and b.serial# = &serial)
order by piece asc ;
如何查找重复记录？
SELECT * FROM TABLE_NAME
WHERE ROWID!=(SELECT MAX(ROWID) FROM TABLE_NAME D
WHERE TABLE_NAME.COL1=D.COL1 AND TABLE_NAME.COL2=D.COL2);
如何删除重复记录？
DELETE FROM TABLE_NAME
WHERE ROWID!=(SELECT MAX(ROWID) FROM TABLE_NAME D
WHERE TABLE_NAME.COL1=D.COL1 AND TABLE_NAME.COL2=D.COL2);
分析表：
analyze table TABLE_NAME compute statistics for table for all indexes for all indexed columns;
sqlplus查看存储过程脚本：
select text from all_source where owner='ARK' AND type = 'PROCEDURE' and name ='CHECK_INDEXES';
重建索引
alter index IDX_NAME rebuild tablespace TBS_NAME parallel 5 online compute statistics;
alter index IDX_NAME noparallel;
每天 8/9/10/15/16/17/23 7个整点执行的报告
job interval
trunc(sysdate) + decode(to_char(sysdate,'hh24'),8,9,9,10,10,15,15,16,16,17,17,23,32)/24
很狠的杀进程招数：
select 'alter system kill session '||''''||se.sid||','||se.SERIAL#||''''||' ;'
from v$session se
where se.SID in (
select sid
from v$lock t
where block >0 );
---------------------------
当我们使用sql_trace/10046等事件进行进程跟踪时，会生成跟踪文件.跟踪文件名称由以下几部分组成:
<sid>_ora_<pid>.trc
以下脚本用户获得跟踪文件名称:
SELECT a.VALUE
|| b.symbol
|| c.instance_name
|| '_ora_'
|| d.spid
|| '.trc' trace_file
FROM (SELECT VALUE
FROM v$parameter
WHERE NAME = 'user_dump_dest') a,
(SELECT SUBSTR (VALUE, -6, 1) symbol
FROM v$parameter
WHERE NAME = 'user_dump_dest') b,
(SELECT instance_name
FROM v$instance) c,
(SELECT spid
FROM v$session s, v$process p, v$mystat m
WHERE s.paddr = p.addr AND s.SID = m.SID AND m.statistic# = 0) d
/
---------------
查看统计undo大小的脚本:
select t.*
from (SELECT D.TABLESPACE_NAME,
SPACE “SUM_SPACE(M)”,
BLOCKS SUM_BLOCKS,
SPACE - NVL(FREE_SPACE, 0) “USED_SPACE(M)”,
ROUND((1 - NVL(FREE_SPACE, 0) / SPACE) * 100, 2) “USED_RATE(%)”,
FREE_SPACE “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(+)) t
order by “USED_RATE(%)” desc;
----
select name, used_rate || ‘%’ as used_rate, ‘Free:’ || free
from (select ts.name,
round((t.space - (us.free + f.free)) / t.space * 100, 2) as used_rate,
to_char(us.free + f.free) as free
from (select undotsn, expiredblks * 8 / 1024 as free
from v$undostat
where rownum = 1) us,
v$tablespace ts,
(select tablespace_name,
round(sum(bytes) / (1024 * 1024), 2) free
from dba_free_space
group by tablespace_name) f,
(select tablespace_name,
sum(blocks) blocks,
round(sum(bytes) / (1024 * 1024), 2) space
from dba_data_files
group by tablespace_name) t
where ts.ts# = us.undotsn
and ts.name = f.tablespace_name
and ts.name = t.tablespace_name)
--------------------------
--胜通--
如何让sqlplus在shterm中不显示输出内容
想提取大量数据，并且不想让其输出到shterm的屏幕中，可将其spool到指定文件。但是，同时输出结果的文件，要用到其他地方导入，即出来查询结果，其他什么都不spool出来，可如下处理：
set linesize 2000
set head off
set feedback off
set termout off
set timing off
spool item
select title || ',' || tags || ',' || systag || ',' || item_type || ',' ||
item_id || ',' || COLLECTOR_COUNT || ',' || category
from collect_item;
spool off
将以上内容保存到一个sh文件，放在crontab中执行，就会得到只有查询结果的item文件。
如果你要在sqlplus执行如上操作，那么item文件中肯定包含一些没必要的“杂物
-------------
--查看应用连接过来的连接数情况
SELECT B.MACHINE, B.CNT, MAX(A.CNT) MAX, MIN(A.CNT) MIN
FROM (SELECT MACHINE, COUNT(*) CNT FROM V$SESSION GROUP BY MACHINE) A,
(SELECT REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(MACHINE,
'[0-9]',
'#'),
'#+',
'#'),
'#[a-z]*/.',
'#.') MACHINE,
COUNT(*) CNT
FROM V$SESSION
GROUP BY REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(MACHINE,
'[0-9]',
'#'),
'#+',
'#'),
'#[a-z]*/.',
'#.')) B
WHERE REGEXP_LIKE(A.MACHINE, REPLACE(B.MACHINE, '#', '[0-9]*'))
GROUP BY B.MACHINE, B.CNT
----------------------
调整sysaux表空间
select distinct snap_id from dba_hist_snapshot order by 1;
select min(snap_id),max(snap_id) from wrh$_active_session_history;
select dbid from wrh$_active_session_history where rownum <5;
exec DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(low_snap_id => 3615, high_snap_id => 3800);
alter database datafile '/u01/oracle/oradata/ark/system.dbf' resize 1024m;
修改保留时间为三天
exec dbms_workload_repository.modify_snapshot_settings(interval=>30, retention=>3*24*60);
SELECT *from dba_hist_wr_control;
--
SELECT LAST_DAY(ADD_MONTHS(TO_DATE(TO_CHAR(SYSDATE+365, 'yyyy'), 'yyyy'),
1 * 11)) + 1 - 1 / 24 / 60 / 60
FROM DUAL;
二进制转十进制
select sum(data1)
from (select substr('1101', rownum, 1) * power(2, length('1101') - rownum) data1
from dual
十进制转二进制
select replace(sys_connect_by_path(decode(bitand(10, power(2, ceil(log(2, 10)) - level)),
0,
'0',
'1'),
'.'),'.',null)
from dual
where level = ceil(log(2, 10))
connect by level <= ceil(log(2, 10));
connect by rownum <= length('1101'))
出处：http://www.itpub.net/717913.html
This work is licensed under a CC A-S 4.0 International License.