分类： Oracle
2010-09-09 12:23:38
1. Oracle利用现有的表创建一张新表，只要表结构相同
create table david as select * from all_users where 1<>1;
2. 循环插入数据
declare i integer;
begin
for i in 1..100000 loop
insert into test values(i);
end loop;
commit;
end;
3.开发人员通常习惯赋予所有用户DBA权限， 查看权限。。
Select * From User_Role_Privs
Select * From User_Sys_Privs
4. 看数据文件大小,单位是M
select round(bytes/(1024*1024),0) total_space
from dba_data_files
select sum(bytes/(1024*1024)) total_space
from dba_data_files
5 控制文件大小
select sum( block_size*file_size_blks )/1024/1024
from v$controlfile
6.建立表空间
CREATE TABLESPACE data01
DATAFILE '/oracle/oradata/db/DATA01.dbf' SIZE 500M
UNIFORM SIZE 128k; #指定区尺寸为128k,如不指定，区尺寸默认为64k
删除表空间
DROP TABLESPACE data01 INCLUDING CONTENTS AND DATAFILES;
修改表空间大小
alter database datafile '/path/NADDate05.dbf' resize 100M
7. select tablespace_name, file_name, sum(bytes)/1024/1024 table_size from dba_data_files group by tablespace_name,file_name;
8. 有没有被lock，可以通过这2张dynamic view来确定：
v$locked_object,V$session
可以把该 session杀掉。
select sid,serial# from v$session where username ='XXXX'
把得到的sid，serial#号替换到下面的语句中：
alter system kill session 'SID,SERIAL#'
9. PL/SQL oracle 查询前10条信息
SELECT * FROM table WHERE ROWNUM < 11
select * from ( select * from table order by desc) where rownum <=5
== select top 5 * from table;
10. 查看表上是否存在的索引
select * from user_indexes where table_name = 'yourtablename'
create index IX_Tablename_column on tablename(column)
11. select id, id2, round((id/id2)*100,2) || '%' percent from test;
12. 查询表的行数
select count(*) from table_name; 全表扫描 ，会自已找表有索引列并且该列为非空的(因为只有非空才能确保记录数是全的),走INDEX_FFS.
select count(1) from table_name; 不走索引，效率要高，但在表中有非空索引时也是走 INDEX_FFS 的
13. 用function来查看当前session的trace文件的文件名
如下
create or replace function gettracename return varchar2 is
v_result varchar2(200);
begin
SELECT d.VALUE
|| '/'
|| LOWER (RTRIM (i.INSTANCE, CHR (0)))
|| '_ora_'
|| p.spid
|| '.trc' into v_result
FROM (SELECT p.spid
FROM v$mystat m, v$session s, v$process p
WHERE m.statistic# = 1 AND s.SID = m.SID AND p.addr = s.paddr) p,
(SELECT t.INSTANCE
FROM v$thread t, v$parameter v
WHERE v.NAME = 'thread'
AND (v.VALUE = 0 OR t.thread# = TO_NUMBER (v.VALUE))) i,
(SELECT VALUE
FROM v$parameter
WHERE NAME = 'user_dump_dest') d;
return v_result;
end gettracename;
运行SQL> select gettracename() from dual;即可
GETTRACENAME()
-----------------------------------------------------------------------
F:\DEVELOPER\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\UDUMP/orcl_ora_3800.trc
14. select case when(a>b) then a else b end from TableA;
select greatest(a,b) from tb
15. TRUNCATE TABLE Table_name
使用truncate时，速度比delete，但是系统不做Log。清空表的数据，仅保留类别结构，被删除的数据不能ROLLBACK，这点是与DELETE的主要差别.不能Rollback也就是会删除log文件.在SQL Server里面如果你用Truncate，自动增长的行又会从1开始
16. select substrb('大小abc',1,6) from dual;
17. 表中的数据如下图所示
a b c
1 PP41982 SO90029
2 PP41982 SO90029
只取出字段b，c不重复的字段，
select b,c from t group by b,c having(count(b) <2)
17. 查询锁的情况
1). insert into test values(1);
2). select userenv('sid') from dual;
3). select * from v$lock where sid=''
sid 在v$session 中有这个列，可以结合v$session 查询更多的信息
18. 删除重复行：
SQL> DELETE FROM a WHERE ROWID IN( SELECT MAX(ROWID) FROM a);
已删除 1 行。
SQL> select * from a;
NAME ID ORDER_TITLE
-------------------- ---------- --------------------
中国科学技术大学 1 科学
中国科学技术大学 1 科学
SQL>
19. 查看索引信息
--查看索引名称
SELECT * FROM USER_INDEXES;
-- 查看索引列名
SELECT * FROM DBA_IND_COLUMNS;
SELECT * FROM USER_IND_COLUMNS;
SELECT * FROM ALL_IND_COLUMNS;
20. oracle trunc()函数用法
1.TRUNC(for dates)
TRUNC函数为指定元素而截去的日期值。
其具体的语法格式如下：
TRUNC（date[,fmt]）
其中：
date 一个日期值
fmt 日期格式，该日期将由指定的元素格式所截去。忽略它则由最近的日期截去
下面是该函数的使用情况：
TRUNC（TO_DATE(’24-Nov-1999 08:00 pm’,’dd-mon-yyyy hh:mi am’)）
=’24-Nov-1999 12:00:00 am’
TRUNC（TO_DATE(’24-Nov-1999 08:37 pm’,’dd-mon-yyyy hh:mi am’,’hh’)） =’24-Nov-1999 08:00:00 am’
2.TRUNC(for number)
TRUNC函数返回处理后的数值，其工作机制与ROUND函数极为类似，只是该函数不对指定小数前或后的部分做相应舍入选择处理，而统统截去。
其具体的语法格式如下
TRUNC（number[,decimals]）
其中：
number 待做截取处理的数值
decimals 指明需保留小数点后面的位数。可选项，忽略它则截去所有的小数部分
下面是该函数的使用情况：
TRUNC（89.985，2）=89.98
TRUNC（89.985）=89
TRUNC（89.985，-1）=80
注意：第二个参数可以为负数，表示为小数点左边指定位数后面的部分截去，即均以0记。
21. local是局部有序，整体无序，global是有序的，所以local可能会比global慢，得看你的sql语句怎么写的，需求是什么样的
global索引->自己想怎么玩就怎么玩
local索引->表怎么玩它就怎么玩
22， 查看磁盘物理读写情况：
SELECT NAME,phyrds, phywrts,readtim,writetim FROM v$filestat a, v$datafile b WHERE a.FILE#=b.FILE#
ORDER BY readtim DESC;
|
23. 从表中筛选出所有能被5整除的value值数据 24.
29. PGA中sort_area_size大小不够时，用到临时表空间。 oracle没有标识列，自动增长的这个概念 如果null参与聚集运算，则除count(*)之外其它聚集函数都忽略null. ~count(1)和count(主键) 这两个只扫描主键Index就可以得到数据，
~truncate 只是删除了表中的记录，并不会改变表的结构及依赖约束
|
'ALTER SYSTEM KILL SESSION'''||SID||','||SERIAL#||''';'
数据库在主备库切换之后要手动的用SQL来检查检查有没有死锁，如果有,kill 就可以了..