oracle9i或以下,单个数据文件最大32G(对于8K的数据块),整个数据库最多有64K个数据文件.单个表空间的数据文件数量没有具体的限制,也应该是在64K以下. oracle10g以上,引入了bigfile tablespace,bigfile tablespace只有一个数据文件,最大为4G*8k=32T database file size: Operating system dependent. Limited by maximum operating system file size; typically 222(2的22次方） or 4M blocks
11. Oracle利用现有的表创建一张新表，只要表结构相同
create table david as select * from all_users where 1<>1;
12. 循环插入数据
declare i integer;
begin
for i in 1..100000 loop
insert into test values(i);
end loop;
commit;
end;
13. 开发人员通常习惯赋予所有用户DBA权限，查看权限
Select * From User_Role_Privs
Select * From User_Sys_Privs
14. 看数据文件大小,单位是M
select round(bytes/(1024*1024),0) total_space from dba_data_files
select sum(bytes/(1024*1024)) total_space from dba_data_files
15 控制文件大小
select sum( block_size*file_size_blks )/1024/1024 from v$controlfile
16. 建立表空间
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
查看表空间：
select tablespace_name, file_name, sum(bytes)/1024/1024 table_size from dba_data_files group by tablespace_name,file_name;
可以把该 session杀掉。
select sid,serial# from v$session where username ='XXXX'
把得到的sid，serial#号替换到下面的语句中：
alter system kill session 'SID,SERIAL#'
18. 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;
19. 查看表上是否存在的索引
select * from user_indexes where table_name = 'yourtablename'
create index IX_Tablename_column on tablename(column)
20. select id, id2, round((id/id2)*100,2) || '%' percent from test;
21. 查询表的行数
select count(*) from table_name; 全表扫描 ，会自已找表有索引列并且该列为非空的(因为只有非空才能确保记录数是全的),走INDEX_FFS.
select count(1) from table_name; 不走索引，效率要高，但在表中有非空索引时也是走 INDEX_FFS 的
22. 用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()
-----------------------------------------------------------------------
26. 表中的数据如下图所示
a b c
1 PP41982 SO90029
2 PP41982 SO90029
只取出字段b，c不重复的字段，
select b,c from t group by b,c having(count(b) <2)
27. 查询锁的情况
1). insert into test values(1);
2). select userenv('sid') from dual;
3). select * from v$lock where sid=''
sid 在v$session 中有这个列，可以结合v$session 查询更多的信息
28. 删除重复行：
SQL> DELETE FROM a WHERE ROWID IN( SELECT MAX(ROWID) FROM a);
已删除 1 行。
SQL> select * from a;
NAME ID ORDER_TITLE
-------------------- ---------- --------------------
中国科学技术大学 1 科学
中国科学技术大学 1 科学
SQL>
29. 查看索引信息
--查看索引名称
SELECT * FROM USER_INDEXES;
-- 查看索引列名
SELECT * FROM DBA_IND_COLUMNS;
SELECT * FROM USER_IND_COLUMNS;
SELECT * FROM ALL_IND_COLUMNS;
select empno,ename from emp
union
select deptno,dname from dept
我们没有必要在每一个select结果集中使用order by子句来进行排序，我们可以在最后使用一条order by来对整个结果进行排序。例如：
select empno,ename from emp
union
select deptno,dname from dept
order by ename;
35. 查看看到A用户下的所有数据量>100万的表的信息
select * from user_all_tables a
where a.num_rows>1000000
前提是a用户下所有表的统计信息都是最新的。
保险的办法是所有表都count一遍:
select 'select '||''''||table_name ||''','||'count(*) from '||table_name from user_all_tables ;
把上面这段sql的执行结果拷贝出来执行即可
36. SQLPLUS 里执行 EXPLAIN PLAN
SQL>EXPLAIN PLAN FOR 你的sql语句;
如
SQL>EXPLAIN PLAN FOR SELECT * FROM EMP WHERE EMPNO=7369;
然后
SELECT plan_table_output
FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE'));
查看结果就是前面SQL语句的执行计划。
46. 这是看高速缓存命中率小于80%的SQL
SELECT EXECUTIONS , DISK_READS, BUFFER_GETS,
round((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2) Hit_radio,
ROUND(DISK_READS/EXECUTIONS,2) Reads_per_run,
SQL_TEXT
FROM V$SQLAREA
WHERE EXECUTIONS>0
AND BUFFER_GETS > 0
AND (BUFFER_GETS-DISK_READS)/BUFFER_GETS < 0.8
ORDER BY 2 desc,4 DESC;
47. 数据库在主备库切换之后要手动的用SQL来检查检查有没有死锁，如果有,kill 就可以了..
SQL> select 'alter system kill session '''||sid||','||serial#||''';' from v$session where sid in (select sid from v$lock where block = 1);
'ALTER SYSTEM KILL SESSION'''||SID||','||SERIAL#||''';'
48. EOF是标记控制字符开始，到结束，随便什么字符都可以用的
sqlplus '/ as sysdba' <<eof
{
shutdown immediate;
startup force dba pfile=$ORACLE_HOME/dbs/init.ora;
shutdown immediate;
}
exit;
eof
54. oracle表空间大小没有限制，根存储空间而定。
oracle9i或以下,单个数据文件最大32G(对于8K的数据块),整个数据库最多有64K个数据文件.单个表空间的数据文件数量没有具体的限制,也应该是在64K以下.
oracle10g以上,引入了bigfile tablespace,bigfile tablespace只有一个数据文件,最大为4G*8k=32T
database file size:
Operating system dependent. Limited by maximum operating system file size; typically 222(2的22次方） or 4M blocks
55. ROWNUM 是查询时的一个记录号，是一个伪列
rownum只和最终输出结果order by之前的顺序一致
select rownum,t.* from user_tables t;
select rownum,t.* from(select * from user_tables order by table_name)t;
select * from (select rownum,t.* from user_tables t order by table_name);
57. order by t.tm_error desc， 必须是 group by里的字符，或者是统计字段。
58. 大量更新表时：
1.关掉tableb 的所有触发器，这个一定要关掉，moving data的时候一定要全部关掉，不然批量操作的时候卡死你Y的。
alter system tableb disable all triggers;
执行完毕之后，启动触发器
alter system tableb enable all triggers;
2，除了主键索引之外，tableb表剩余的索引全部删除掉。等执行完毕之后，重建索引(索引重建很快，我的800万数据的表的6个索引重建才花了2分钟而已)
select count(*) from v$fixed_table where name like 'V%';
select view_name from V$fixed_view_definition;
select count(*) from v$fixed_view_definition;
select view_definition from V$FIXED_VIEW_DEFINITION WHERE view_name='V$PX_SESSION';
60. dba_views 是从Oracle底层数据库的表中得到的，不是从X$表或者v$视图。
SQL> SET LONG 2000000
SQL>select text from dba_views where viewname='DBA_IND_PARTITIONS';
62. Parse CPU to Parse Elapsd %: 127.27 % Non-Parse CPU: 97.12
parse cpu = amount of cpu time used to parse
elapsed time parsing = amount of time on the wall clock spent parsing.
100*（parse time cpu / parse time elapsed）= Parse CPU to Parse Elapsd %
in a perfect world, with no contention -- parse cpu = parse elapsed.
ratio = 100%
in a bad world, it takes longer to parse (elapsed) then cpu time used
(contention). ratio < 100%
in your case, what this is saying is the CPU exceeded the elapsed, which
technically is not possible -- but happens due to the way "small fast things"
are measured on computers. It is hard to measure things that happen very
rapidly accurately. So, this ratio, when > 100%, is the same as "100%" for all
intents and purposes
74. select name,value ,ISSYS_MODIFIABLE from v$parameter
如果ISSYS_MODIFIABLE 返回的是false，说明该参数无法用alter system语句动态修改,需要重启数据库
75. oracle子查询中能使用order by
from 子句后面的内联视图是可以使用order by子句进行排序的。
然而，其它视图或子查询是不能用order by进行排序的
如果你要用选择前几条的话，需要在套一层变成from后面的内联视图。
比如
select * from dept a
2 where a.deptno in
3 (
select depton from (
4 select b.deptno from dept b
5 order by b.dname
6 ) [where rownum < 5])
76. 修改temp表空间自动增长：
alter database tempfile 'D:/ORACLE/ORADATA/DBA/TEMP01.DBF' autoextend on next 20m;
修改表空间自动增长：
alter database datefile 'D:/ORACLE/ORADATA/DBA/user01.DBF' autoextend on next 20m;
77. alter index rebuild与alter index rebuild online的区别
online时可以在该索引的基表上执行DML，在在对基表操作的同时可以REBUILD INDEX，但是不能执行DDL语句，所以他们的锁机制是不样的。
创建索引时通常会对该表设置一个表级共享(DML)锁,如果设置ONLINE ,
如果是非ONLINE方式，通常会对该表设置一个表级共享(DML)锁，那么就对DML语句冲突，如果设置ONLINE ,(会使用临时日志IOT表来记录中间改变的数据)，但要使用两倍于传统方法的空间.表会变成行级共享锁,在创建索引或者ALTER完成后,对临时日志表与基表进行MERGE
81. 查询正在执行的sql
select OSUSER, PROGRAM, USERNAME, SCHEMANAME, B.Cpu_Time, STATUS, B.SQL_TEXT
from V$SESSION A
LEFT JOIN V$SQL B ON A.SQL_ADDRESS = B.ADDRESS
AND A.SQL_HASH_VALUE = B.HASH_VALUE
where b.SQL_TEXT is not null
order by b.cpu_time desc
82. Oracle在to_char()函数在计算一年中第几周是从该年的1月1日开始的。
83. 正在连接的用户不能删除，确实要删除的话，如下
1、select sid,serial#,username from v$session where user='USERNAME';
2、alter system kill session 'sid,serial#';
3、drop user username cascade;
85. 在Oracle中，要获得日期中的年份，例如把sysdate中的年份取出来，并不是一件难事。
常用的方法是：Select to_number(to_char(sysdate,''yyyy'')) from dual，
而实际上，oracle本身有更好的方法，那就是使用Extract函数，
使用方法是：Select Extract(year from sysdate) from dual，这种方法省掉了类型转换，看上去更加简洁。
相应的，要取得月份或日，可以用select extract (month from sysdate) from dual和select extract (day from sysdate) from dual。
此方法获得的结果，是数值型的，大家可以设置一个方法测试一下。
select EXTRACT(year FROM to_date('2009-11-10','yyyy-mm-dd')) year from dual;
86. 查询数据库默认的表空间类型：
SQL> select property_name,property_value from database_properties where property_name='DEFAULT_TBS_TYPE';
PROPERTY_NAME PROPERTY_VALUE
------------------ ------------------
DEFAULT_TBS_TYPE BIGFILE
93. Index ENABLE和DISABLE适用于FUNCTION-BASED INDEX
如果普通索引的话，你就用unusable 而不是disable
ENABLE和DISABLE只针对函数索引。
ENABLE applies only to a function-based index that has been disabled because a user-defined function used by the index was dropped or replaced. This clause enables such an index if these conditions are true:
* The function is currently valid
* The signature of the current function matches the signature of the function when the index was created
* The function is currently marked as DETERMINISTIC
Restriction on Enabling Function-based Indexes
You cannot specify any other clauses of ALTER INDEX in the same statement with ENABLE.
DISABLE Clause
DISABLE applies only to a function-based index. This clause lets you disable the use of a function-based index. You might want to do so, for example, while working on the body of the function. Afterward you can either rebuild the index or specify another ALTER INDEX statement with the ENABLE keyword.
楼主试试：
alter index xx unusable;
UNUSABLE Clause Specify UNUSABLE to mark the index or index partition(s) or index subpartition(s) UNUSABLE. An unusable index must be rebuilt, or dropped and re-created, before it can be used. While one partition is marked UNUSABLE, the other partitions of the index are still valid. You can execute statements that require the index if the statements do not access the unusable partition. You can also split or rename the unusable partition before rebuilding it.
94. 如何清除inactive的session
1.方法一
(1)UNIX的方法
A。sql>select usename,sid,paddr,status
from v$session
where usename='USERNAME'
AND STATUS='INACTIVE';
B。sql>SELECT SPID FROM V$PROCESS WHERE ADDR=上一步查出的PADDR
C。$KILL SPID
（2）WINDOWSnt/2000的方法
c:/>orakill SID SPID
2 ,方法二
select A.SID,B.SPID,A.SERIAL#,a.lockwait,A.USERNAME,A.OSUSER,a.logon_time,a.last_call_et/3600 LAST_HOUR,A.STATUS,
'orakill '||sid||' '||spid HOST_COMMAND,
'alter system kill session '''||A.sid||','||A.SERIAL#||'''' SQL_COMMAND
from v$session A,V$PROCESS B where A.PADDR=B.ADDR AND SID>6
97. 分页一般用到两种办法：
1，利用rownum
2，分析函数row_number()over()
1.
select from(
select t.*,rownum rn from(
select * from a
order by col1)t
where rn between 101 and 200)
2.
select *
from(select t.*,row_number()over(order by col1)rn
from t)
where rn between 101 and 200
99. listener 主要是侦听从客户端发来的对数据库的连接请求。
如果你在服务器端用sqlplus 进行连接，监听没有启动也是可以连上的，但是从远程来访问数据库，或者用PL/SQL dev 或者TOAD等进行连接，就必须启动监听。
100. 这个命令可以查看建表的SQL语句..
select dbms_metadata.get_ddl('TABLE','&tname') from dual;
101. 查询视图可以通过
select * from all_views
索引:all_indexes, 索引和列的关系 all_ind_columns。table_name即索引所在的表
如果只想查询当前用户下的，将上面数据字典的all改成user
102. char 最大长度是2000.
SQL> create table test (v2 char(2001));
create table test (c char(2001))
ERROR at line 1:
ORA-00910: specified length too long for its datatype
SQL> create table test1 (c char(2000));
Table created.
103. Oralce 快照是Oralce 7时候的叫法吧，8i之后改名物化视图
104, 一个小触发器
create table t_temp
(
id varchar2(10) primary key,
len1 number(6,0),
len2 number(6,0),
len number(7,0)
)
当update某一行的len1或len2值后，则修改该行len的值(len = len1 + len2)，
或者insert 一条新的记录后，修改len = len1 + len2
哪位帮我用触发器实现上面的功能
create trigger tri
befor insert or update on t_temp
for each row
begin
:NEW.len := :NEW.len1 + :NEW.len2;
end;
create or replace trigger tgtemp
before insert or update of len1,len2
on t_temp
for each row
begin
:new.len:=:new.len1+:new.len2;
end;
oracle 不允许触发器修改它正在触发的表，故用before 可以，after不行..
105. udump下的trc文件可以通过配置不让产生,利用命令
alter system set sql_trace=false;
其他的不能修改，只能手动的启动trace，手动的关闭trace.
比如：
alter session set events 'immediate trace name library_cache|controlf|systemstate|processstate|file_hdrs|REDOHDR level 10';
alter session set events 'immediate trace name off';
alter session set events '10046 trace name context forever,level 12';
alter session set events '10046 trace name context off';
alter system set events '10046 trace name context forever,level 12';
alter system set events '10046 trace name context off';
106. 重复数据只显示一条：
select min(id) id,b,c from tb group by b,c
107. 删除重复数据：
delete from tb where rowid not in (select min(rowid) from tb group by b,c);
108. oracle 批量重建索引
create or replace procedure p_rebuild_all_index
(tablespace_name in varchar2)
as
sqlt varchar(200);
begin
for idx in (select index_name, tablespace_name, status from user_indexes where tablespace_name=tablespace_name and status='VALID' and temporary = 'N') loop
begin
sqlt := 'alter index ' || idx.index_name || ' rebuild ';
dbms_output.put_line(idx.index_name);
dbms_output.put_line(sqlt);
EXECUTE IMMEDIATE sqlt;
--错误后循环继续执行。
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(SQLERRM);
end;
end loop;
end;
oracle 存储过程批量重建索引。
测试方法
declare
--表空间名称
tablespace_name varchar2(100);
begin
tablespace_name:='dddd';
p_rebuild_all_index(tablespace_name);
end;
109. oracle 会将SQL语句中 in 后面的东西生成一张内存中的临时表。然后进行查询。所以在相关字段上见索引比较重要。
110. 在Oracle中查看各个表、表空间占用空间的大小 查看当前用户每个表占用空间的大小：
Select Segment_Name,Sum(bytes)/1024/1024 From User_Extents Group By Segment_Name
查看每个表空间占用空间的大小：
Select Tablespace_Name,Sum(bytes)/1024/1024 From Dba_Segments Group By Tablespace_Name
111. 格式化2个时间相减
SELECT EXTRACT (DAY FROM interval)
|| '天'
|| EXTRACT (HOUR FROM interval)
|| '小时'
|| EXTRACT (MINUTE FROM interval)
|| '分钟'
|| EXTRACT (SECOND FROM interval)
|| '秒'
间隔
FROM (SELECT NUMTODSINTERVAL (callbegin - callend, 'DAY') interval
FROM tbilllog12 t
WHERE callbegin =
TO_DATE ('2009-12-1 0:00:58', 'YYYY-MM-DD HH24:MI:SS'))
115. recover database using backup controlfile和recover database using backup controlfile until cancel
前者是利用backup controlfile完全恢复
后者是利用backup controlfile不完全恢复。
using backup controlfile 告诉Oracle不要使用control file中的scn
using backup controlfile until cancel 用于redo log file丢失时使用，如redo log sequence#1,2,3,4,5,6，丢失4，5，会恢复到3。