Oracle 问题记录

1、如何将表移动表空间?
ALTER TABLE SCOTT.EMP MOVE TABLESPACE USERS2;
2、如何将索引移动表空间?
ALTER INDEX SCOTT.PK_EMP REBUILD TABLESPACE USERS2;
3、如何解锁?
ALTER SYSTEM KILL SESSION 'SID,SERIR#';
4、如何修改表的缓冲池?
ALTER TABLE scott.emp STORAGE (BUFFER_POOL KEEP);
5、怎样查看哪些用户拥有SYSDBA、SYSOPER 权限？
select * from V_$PWFILE_USERS;
6、CHR()的反函数是?
ASCII()
SELECT CHAR(65) FROM DUAL;
SELECT ASCII('A') FROM DUAL;
7、字符串的连接
SELECT CONCAT(COL1,COL2) FROM TABLE ;
SELECT COL1||COL2 FROM TABLE ;
8、怎么把select出来的结果导到一个文本文件中？
SQL>SPOOL C:\ABCD.TXT;
SQL>select * from table;
SQL >spool off;
9、如何修改表名?
alter table old_table_name rename to new_table_name;
10. 返回大于等于N的最小整数值?
SELECT CEIL(N) FROM DUAL;
11. 返回小于等于N的最小整数值?
SELECT FLOOR(N) FROM DUAL;
12、如何删除重复记录？
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);
13、如何知道使用CPU多的用户session?
11是cpu used by this session
select a.sid,spid,status,substr(a.program,1,40) prog,
a.terminal,osuser,value/60/100 value
from v$session a,v$process b,v$sesstat c
where c.statistic#=11 and c.sid=a.sid and a.paddr=b.addr
order by value desc;
14、如何监控 SGA 的命中率？
select a.value + b.value "logical_reads", c.value "phys_reads",
round(100 * ((a.value+b.value)-c.value) / (a.value+b.value)) "BUFFER HIT
RATIO"
from v$sysstat a, v$sysstat b, v$sysstat c
where a.statistic# = 38 and b.statistic# = 39
and c.statistic# = 40;
gets+getmisses <>0
group by parameter, gets, getmisses;
15、 如何监控 SGA 中共享缓存区的命中率，应该小于1% ？
select sum(pins) "Total Pins", sum(reloads) "Total Reloads",
sum(reloads)/sum(pins) *100 libcache
from v$librarycache;
select sum(pinhits-reloads)/sum(pins) "hit radio",
sum(reloads)/sum(pins) "reload percent"
from v$librarycache;
16、 如何显示所有数据库对象的类别和大小？
select count(name) num_instances ,type ,sum(source_size) source_size ,
sum(parsed_size) parsed_size ,sum(code_size) code_size ,
sum(error_size) error_size,
sum(source_size) +sum(parsed_size) +sum(code_size) +sum(error_size)
size_required
from dba_object_size
group by type order by 2;
17、 监控 SGA 中重做日志缓存区的命中率，应该小于1%
SELECT name, gets, misses, immediate_gets, immediate_misses,
Decode(gets,0,0,misses/gets*100) ratio1,
Decode(immediate_gets+immediate_misses,0,0,
immediate_misses/(immediate_gets+immediate_misses)*100) ratio2
FROM v$latch WHERE name IN ('redo allocation', 'redo copy');
18、 监控内存和硬盘的排序比率，最好使它小于 .10，增加 sort_area_size
SELECT name, value
FROM v$sysstat WHERE name IN ('sorts (memory)', 'sorts (disk)');