自己遇到的一些问题（随意杂乱）

create table suntan
(
id number(22) not null,
name varchar2(10) not null,
num number(6),
inseDate timestamp(6)
)

创建和删除索引的两种方法
alter table test add primary key (id);
alter table test drop primary key

alter table test add constraint s_id primary key (id);
alter table test drop constraint s_id


alter table suntan add constraint suntan_pk primary key (id);

alter table suntan add constraint suntan_uk unique (name);

create index suntan_index on suntan(id);

alter table suntan add constraint suntan_fk foreign key (num) references item(id);

alter table suntan add s_next varchar2(10);

alter table suntan modify s_next varchar2(20);

alter table suntan drop column s_next;

alter table suntan disable constraint suntan_fk;

alter table suntan enable constraint suntan_fk;

alter table suntan drop --foreign key(num)不行 --unique (name); 可以

alter table suntan drop constraint suntan_fk;--foreign key (num);--unique (name);

alter table suntan drop constraint suntan_uk cascade;

insert into suntan values(2, 'suntan', 2, to_date('2008-07-02', 'yyyy-MM-dd'), 'aa');

insert into suntan values(1, 'suntan1', 1, to_date('2008-07-02', 'yyyy-MM-dd'), 'aa');

----检验unique key 是否允许插入两个空值，当它没有规定NOT NULL 时

alter table suntan modify name varchar2(20) null;

insert into suntan values(3, NULL, 3, to_date('2008-07-02', 'yyyy-MM-dd'), 'aa3');

insert into suntan values(4, NULL, 4, to_date('2008-07-02', 'yyyy-MM-dd'), 'aa4');

经过证明可以，但是当为primary key 时，必须有值且都能为NULL，因为要根据这个primary key 能查到值
------------------

select * from suntan where num < (select max(num) from suntan)--ANY(select max(num) from suntan)

select * from suntan where num > (select MIN(num) from suntan) --(select MIN(num) from suntan)

select id,num, decode(id, 1, num*2,
2, num*2,
num) newDa from suntan;

select to_number('$2', '$99.99') from suntan;

select constraint_name, constraint_type, search_condition from user_constraints where table_name = 'SUNTAN'

select constraint_name, column_name from user_cons_columns where table_name = 'SUNTAN'

describe suntan; -- pl/sql 不支持， SQL/PLUS 支持

DESC suntan; -- pl/sql 不支持， SQL/PLUS 支持

select * from user_tables;

select * from user_objects; --查询当前用户不同的对象类型

select * from user_catalog; -- 查询当前用户的表、视图、同义词、序列


--创建视图和操作视图
create VIEW suntanView(id1, name1) as select s.id, s.name from suntan s where s.id=1; --PL/SQL不支持

select view_name, text from user_views;

select * from SUNTANVIEW;


--修改视图
create or replace View SUNTANVIEW as select id, name, num from suntan;
--在视图上可以执行DML操作，但必须遵循以下规则：
--1。 在简单视图上可以执行DML操作
insert into suntanView(5, 'suntan5', 5); 错误， 不能执行
update suntanView set name = 'suntan2' where id=2; 可以执行
delete from suntanView where id=2;
--2。 如下视图中包含以下内容不能删除行
-- a。 group 函数
-- b. group by 函数
-- c. distinct 函数
-- 删除视图
drop view suntanView


--序列
create sequence suntan_seq
increment by 1
start with 10
maxvalue 15
nocache
nocycle

select object_name, status from user_objects where object_type = 'SEQUENCE'--检索当前用户的序列名及其状态

select sequence_name, min_value, max_value, increment_by, last_number from user_sequences; --检索当前用户的序列名称及其设置

select suntan_seq.currval from dual; --第一次会提示尚未定义，执行完nextval后就可以了

select suntan_seq.nextval from dual;

select last_number from user_sequences where sequence_name='SUNTAN_SEQ'

alter sequence suntan_seq increment by 1 maxvalue 20 nocache nocycle

drop sequence suntan_seq;

--索引
create index num_index on suntan(num);

select ic.index_name, ic.column_name, ic.column_position col_pos, ix.uniqueness from
user_indexes ix, user_ind_columns ic where ic.index_name=ix.index_name
and ic.table_name='SUNTAN'

drop index num_index;


#2009.2.19 add-----------

select chr(54740) zhao,chr(65) chr65 from dual;

select ascii('A') A from dual

select user from dual

-----------------------

-- USEREVN 返回当前用户环境的信息,opt可以是: ENTRYID,SESSIONID,TERMINAL,ISDBA,LABLE,LANGUAGE,CLIENT_INFO,LANG,VSIZE


select userenv('isdba') from dual;

select userenv('sessionid') from dual;

--

select instr('oracle traning','ra',1,2) instring from dual;

select initcap('smith') upp from dual;

select name,length(name)

select lower('AaBbCcDd')AaBbCcDd from dual;

select upper('AaBbCcDd') upper from dual;

select ltrim(rtrim(' gao qian jing ',' '),' ') from dual; --LTRIM 删除左边出现的字符串. RTRIM 删除右边出现的字符串

select substr('13088888888',3,8) from dual; -- 取子字符串,从start开始,取count个

select replace('he love you','he','i') from dual;

select ceil(3.1415927) from dual; --返回大于或等于给出数字的最小整数

select floor(2345.67) from dual; --对给定的数字取整数

select round(55.5),round(-55.4),trunc(55.5),trunc(-55.5) from dual; --按照指定的精度进行舍入

select to_char(add_months(to_date('199912','yyyymm'),2),'yyyymm') from dual; --增加或减去月份

select to_char(sysdate,'yyyy.mm.dd'),to_char((sysdate)+1,'yyyy.mm.dd') from dual;

select last_day(sysdate) from dual; --返回日期的最后一天

select months_between('19-12月-1999','19-3月-1999') mon_between from dual; --给出date2-date1的月份

select next_day('18-5月-2001','星期五') next_day from dual; -- 给出日期date和星期x之后计算下一个星期的日期

select to_char(trunc(sysdate,'hh'),'yyyy.mm.dd hh24:mi:ss') hh,
to_char(trunc(sysdate,'mi'),'yyyy.mm.dd hh24:mi:ss') hhmm from dual; --trunc(date,fmt)按照给出的要求将日期截断,如果fmt='mi'表示保留分,截断秒

select to_number('1999') year from dual; --将给出的字符转换为数字

select least('啊','安','天') from dual; -- 返回一组表达式中的最小值

----------- #2009.2.19 add (end)


-------------2009.3.02 add
select *
from gate_charge_header t
where t.id not in (select t1.gate_charge_header_id
from gate_charge_details t1)

select *
from gate_charge_header
where id in (select id
from gate_charge_header
minus
select gate_charge_header_id from gate_charge_details
)
这两种方法实现同样的功能，但第二中更快


DELETE FROM suntan t
WHERE t.ROWID > (SELECT MIN(t1.ROWID)
FROM suntan t1
WHERE t1.num = t.num); ---删除重复记录
-------------#2009.3.02 add (end)


21. 如何判断数据库的时区？
解答：SELECT DBTIMEZONE FROM DUAL;

22. 解释GLOBAL_NAMES设为TRUE的用途
解答：GLOBAL_NAMES指明联接数据库的方式。如果这个参数设置为TRUE,
在建立数据库链接时就必须用相同的名字连结远程数据库

23。如何加密PL/SQL程序？
解答：WRAP

24. 解释FUNCTION,PROCEDURE和PACKAGE区别
解成
一个任务。procedure 不需要返回任何值而function将返回一个答：function 和procedure是PL/SQL代码的集合，通常为了完值
在另一方面，Package是为了完成一个商业功能的一组function和proceudre
的集合

25. 解释TABLE Function的用途
解答：TABLE Function是通过PL/SQL逻辑返回一组纪录，用于
普通的表/视图。他们也用于pipeline和ETL过程。

26. 举出3种可以收集three advisory statistics
解答：Buffer Cache Advice, Segment Level Statistics, Timed Statistics

27. Audit trace 存放在哪个oracle目录结构中?
解答：unix $ORACLE_HOME/rdbms/audit
Windows the event viewer

28. 解释materialized views的作用
解答：Materialized views 用于减少那些汇总，集合和分组的
信息的集合数量。它们通常适合于数据仓库和DSS系统。

29. 当用户进程出错，哪个后台进程负责清理它
解答： PMON

30. 哪个后台进程刷新materialized views?
解答：The Job Queue Processes.

31. 如何判断哪个session正在连结以及它们等待的资源？
解答：V$SESSION / V$SESSION_WAIT

32. 描述什么是 redo logs
解答：Redo Logs 是用于存放数据库数据改动状况的物理和逻辑结构。
可以用来修复数据库.

33. 如何进行强制LOG SWITCH?
解答：ALTER SYSTEM SWITCH LOGFILE;

34. 举出两个判断DDL改动的方法？
解答：你可以使用 Logminer 或 Streams

35. Coalescing做了什么？
解答：Coalescing针对于字典管理的tablespace进行碎片整理，将
临近的小extents合并成单个的大extent.


36. TEMPORARY tablespace和PERMANENT tablespace 的区别是？
解答：A temporary tablespace 用于临时对象例如排序结构而 permanent tablespaces
用来存储那些'真实'的对象(例如表，回滚段等）


37. 创建数据库时自动建立的tablespace名称？
解答：SYSTEM tablespace.

38. 创建用户时，需要赋予新用户什么权限才能使它联上数据库。
解答：CONNECT

39. 如何在tablespace里增加数据文件？
解答：ALTER TABLESPACE <tablespace_name> ADD DATAFILE <datafile_name> SIZE <size>

40. 如何变动数据文件的大小？
解答：ALTER DATABASE DATAFILE <datafile_name> RESIZE <new_size>;

41. 哪个VIEW用来检查数据文件的大小？
解答： DBA_DATA_FILES

42. 哪个VIEW用来判断tablespace的剩余空间
解答：DBA_FREE_SPACE

43. 如何判断谁往表里增加了一条纪录？
解答：auditing

44. 如何重构索引？
解答： ALTER INDEX <index_name> REBUILD;

45. 解释什么是Partitioning（分区） 以及它的优点。
解答：Partition将大表和索引分割成更小，易于管理的分区。


46. 你刚刚编译了一个PL/SQL Package但是有错误报道，如何显示出错信息？
解答：SHOW ERRORS

47. 如何搜集表的各种状态数据？
解答： ANALYZE
The ANALYZE command.

48. 如何启动SESSION级别的TRACE
解答: DBMS_SESSION.SET_SQL_TRACE
ALTER SESSION SET SQL_TRACE = TRUE;

49. IMPORT和SQL*LOADER 这2个工具的不同点
解答：这两个ORACLE工具都是用来将数据导入数据库的。
区别是：IMPORT工具只能处理由另一个ORACLE工具EXPORT生成
的数据。而SQL*LOADER可以导入不同的ASCII格式的数据源


50。 用于网络连接的2个文件？
解答： TNSNAMES.ORA and SQLNET.ORA

//---------------------------------------

在这里会介绍UNDO，REDO是如何产生的，对TRANSACTIONS的影响，以及他们之间如何协同工作的。
什么是REDO
REDO记录transaction logs，分为online和archived。以恢复为目的。
比如，机器停电，那么在重起之后需要online redo logs去恢复系统到失败点。
比如，磁盘坏了，需要用archived redo logs和online redo logs区恢复数据。
比如，truncate一个表或其他的操作，想恢复到之前的状态，同样也需要。
什么是UNDO
REDO是为了重新实现你的操作，而UNDO相反，是为了撤销你做的操作，比如你得一个TRANSACTION执行失败了或你自己后悔了，则需要用ROLLBACK命令回退到操作之前。回滚是在逻辑层面实现而不是物理层面，因为在一个多用户系统中，数据结构，blocks等都在时时变化，比如我们INSERT一个数据，表的空间不够，扩展了一个新的EXTENT，我们的数据保存在这新的EXTENT里，其它用户随后也在这EXTENT里插入了数据，而此时我想ROLLBACK，那么显然物理上讲这EXTENT撤销是不可能的，因为这么做会影响其他用户的操作。所以，ROLLBACK是逻辑上回滚，比如对INSERT来说，那么ROLLBACK就是DELETE了。
COMMIT
以前，常想当然地认为，一个大的TRANSACTION（比如大批量地INSERT数据）的COMMIT会花费时间比短的TRANSACTION长。而事实上是没有什么区别的，因为ORACLE在COMMIT之前已经把该写的东西写到DISK中了，我们COMMIT只是
1，产生一个SCN给我们TRANSACTION，SCN简单理解就是给TRANSACTION排队，以便恢复和保持一致性。
2，REDO写REDO到DISK中（LGWR，这就是log file sync），记录SCN在ONLINE REDO LOG，当这一步发生时，我们可以说事实上已经提交了，这个TRANSACTION已经结束（在V$TRANSACTION里消失了）
3，SESSION所拥有的LOCK（V$LOCK）被释放。
4，Block Cleanout（这个问题是产生ORA-01555: snapshot too old的根本原因）
ROLLBACK
ROLLBACK和COMMIT正好相反，ROLLBACK的时间和TRANSACTION的大小有直接关系。因为ROLLBACK必须物理上恢复数据。COMMIT之所以快，是因为ORACLE在COMMIT之前已经作了很多工作（产生UNDO，修改BLOCK，REDO，LATCH分配），ROLLBACK慢也是基于相同的原因。
ROLLBACK会
1，恢复数据，DELETE的就重新INSERT，INSERT的就重新DELETE，UPDATE的就再UPDATE。
2，RELEASE LOCK

ROLLBACK要比COMMIT消耗更多资源，因为ORACLE认为你一旦做数据更新，那么就意味着你要COMMIT（其他数据库不全是这种设计理念，比如DB2），所以在你更新数据的时候就做了大量的工作，这也可以理解为什么不建议用TABLE来做TEMPORARY TABLE。（TEMP TABLE消耗的REDO比固定表在INSERT时要少很多 ，UPDATE时差不多是1/2，但是DELETE却相差无几）
REDO
产生REDO越多，你的系统越慢，不但影响你自己的SESSION，还影响其他SESSION，LGWR管理REDO，并且是TRANSACTION的结束标志。
首先要知道怎么监控REDO，当然，SET AUTOTRACE ON可以，不过只能监控DML语句，而像PROCEDURE则无法监视。那么我们就需要观察字典了，V$MYSTAT, V$STATNAME，前面有两个脚本，mystat，mystat2
SQL> @mystat "redo size"
NAME VALUE
---------------------------------------------------------------- ----------
redo size 1016784
SQL> insert into t select * from big_table;
已创建46990行。
SQL> @mystat2
NAME V
---------------------------------------------------------------- ----------
DIFF
----------------
redo size 6604308
5,587,524
看到产生了5,587,524的REDO，再对比下用NOLOG插入
SQL> @mystat "redo size"
NAME VALUE
---------------------------------------------------------------- ----------
redo size 6604308
SQL> insert /*+ APPEND */ into t select * from big_table;
已创建46990行。
SQL> @mystat2
NAME V
---------------------------------------------------------------- ----------
DIFF
----------------
redo size 6616220
11,912
看到APPEND插入用了11,912字节的REDO，比一般性插入要少很多。
或者用这个PROCEDURE也可以观察SQL消耗的REDO
create or replace procedure do_sql( p_sql in varchar2 )
2 as
3 l_start_redo number;
4 l_redo number;
5 begin
6 select v$mystat.value
7 into l_start_redo
8 from v$mystat, v$statname
9 where v$mystat.statistic# = v$statname.statistic#
10 and v$statname.name = 'redo size';
11
12 execute immediate p_sql;
13 commit;
14
15 select v$mystat.value-l_start_redo
16 into l_redo
17 from v$mystat, v$statname
18 where v$mystat.statistic# = v$statname.statistic#
19 and v$statname.name = 'redo size';
20
21 dbms_output.put_line
22 ( to_char(l_redo,'9,999,999') ||' bytes of redo generated for "' ||
23 substr( replace( p_sql, chr(10), ' '), 1, 25 ) || '"...' );
24 end;
25 /
用法就不多说了。
减少REDO
既然REDO这么消耗资源，那我们能屏蔽REDO吗？显然不能，那我们能减少REDO吗？这是可以的（注意，9.2以后，可以用FORCE LOGGING开关来控制是否强制REDO，如果YES，则不管NOLOGGING还是APPEND都是不起任何作用的，可以SELECT FORCE_LOGGING FROM V$DATABASE查看是否FORCE。另外需要明白，没有一个办法能彻底不记录REDO，只能是减少REDO。因为不管如何，数据字典总是要产生一些REDO的。
create table nologging as select xxx新建的表没有原来表的索引和默认值,只有非空(not null)的约束素条件可以继承过来.
INSERT /*+ APPEND */ INTO target_tablename SELECT如果运行此命令时还有对target_tablename的DML操作会排队在它后面,对OLTP系统在用的表操作是不合适的。快速插入数据可以指定append提示，但是需要注意noarchivelog模式下，默认用了append就是nologging模式的。 在archivelog下，需要把表设置程Nologging模式。可以通过如下语句设置为NO FORCE LOGGING。 Alter database no force logging;
这两种方法转移数据时没有用SGA里数据缓冲区和事物处理的回滚段,也不写联机事物日志，就象数据库装载工具SQLLOAD一样直接把数据写到物理文件。
REDO的问题
有时，会在ALERT中发现
Thread 1 cannot allocate new log, sequence 1466
Checkpoint not complete
Current log# 3 seq# 1465 mem# 0: /home/ora10g/oradata/ora10g/redo03.log
这问题出现在系统尝试reuse online redo log file但是却没有可用的。可能是由于DBWR没有完成(Checkpoint not complete)或ARCH没有完成。
1，DBWR，用多DBWR process，合理分布数据，
2，增加REDO LOG FILE
3，扩大REDO的大小
4，让CHECKPOINT发生更频繁，可以减少block buffer cache，FAST_START_MTTR_TARGET，LOG_CHECKPOINT_INTERVAL，LOG_CHECKPOINT_TIMEOUT。（要考虑全面哟）


select id,num, decode(id, 1, num*2,
2, num*2,
num) newDa from suntan


select t.CHARGE_TYPE_NAME ,
decode(t.CHARGE_TYPE_NAME, '闸口费', sum(charge_qty),
'报关费', sum(charge_qty),
'打单费', sum(charge_qty),
'做商检', sum(charge_qty),
'换商检', sum(charge_qty),
'退税', sum(charge_qty),
0) qty from GATE_CHARGE_DETAILS d, gcs_charge_type t
where t.id = d.charge_type and
(d.adjust_status <> 'Void' or d.adjust_status is null) and +
d.gate_charge_header_id = '404106d61e61bf8a011ea64ca8832238'
group by t.CHARGE_TYPE_NAME;

//下面的例子比如t1的数据比t2少，也就是t2表的suntanid字段的值在t1表中可能找不到对应的id,
下面这两个查询把t2表都查出来
select * from suntan t1, suntan2 t2 where t1.id(+) = t2.suntanid;
select * from suntan t1 right outer join suntan2 t2 on t1.id = t2.suntanid;
下面这两个查询把t1表都查出来,t2没有在t1表对应的值找不出来
select * from suntan t1, suntan2 t2 where t1.id = t2.suntanid(+);
select * from suntan t1 left outer join suntan2 t2 on t1.id = t2.suntanid;

//注意子查询（子查询可以嵌于 1.where子句, 2.from子句, 1.having子句）
select name, cnt from (
select
t1.name,
sum(case
when t1.num >3 then 1
else 0
end
) cnt
from suntan t1, suntan2 t2
where t1.id = t2.suntanid
group by t1.name
)


select t.name, max(t.num), avg(t.num) from suntan t where avg(t.num) > 3 group by t.name 错误，
错误的原因为试图用where子句来限制组的输出，where子句应该用于在分组前对纪录的限制上而
不是对组输出结果的限制上。使用having子句可以纠正语句的错误，下面这个SQL是正确的
select t.name, max(t.num), avg(t.num) from suntan t having avg(t.num) > 3 group by t.name

另外对组函数的用法特别需要注意的是：
如果在查询中使用了组函数，任何不在组函数中的列或表达式都必须包含在group by子句中，其中组函数有
（avg,sum,max,min, count等）
例如下面这个SQL就不对
select t.name, count(t.num) from suntan t
如果在后面加上group by t.name就可以正确执行


create table suntan
(
id number(22) not null,
name varchar2(10) not null,
num number(6),
inseDate timestamp(6)
)

alter table suntan add constraint suntan_pk primary key (id);

alter table suntan add constraint suntan_uk unique (name);

create index suntan_index on suntan(id);

alter table suntan add constraint suntan_fk foreign key (num) references item(id);

alter table suntan add s_next varchar2(10);

alter table suntan modify s_next varchar2(20);

alter table suntan drop column s_next;

alter table suntan disable constraint suntan_fk;

alter table suntan enable constraint suntan_fk;

alter table suntan drop --foreign key(num)不行 --unique (name); 可以

alter table suntan drop constraint suntan_fk;--foreign key (num);--unique (name);

alter table suntan drop constraint suntan_uk cascade;

insert into suntan values(2, 'suntan', 2, to_date('2008-07-02', 'yyyy-MM-dd'), 'aa');

insert into suntan values(1, 'suntan1', 1, to_date('2008-07-02', 'yyyy-MM-dd'), 'aa');

----检验unique key 是否允许插入两个空值，当它没有规定NOT NULL 时

alter table suntan modify name varchar2(20) null;

insert into suntan values(3, NULL, 3, to_date('2008-07-02', 'yyyy-MM-dd'), 'aa3');

insert into suntan values(4, NULL, 4, to_date('2008-07-02', 'yyyy-MM-dd'), 'aa4');

经过证明可以，但是当为primary key 时，必须有值且都能为NULL，因为要根据这个primary key 能查到值
------------------

select * from suntan where num < (select max(num) from suntan)--ANY(select max(num) from suntan)

select * from suntan where num > (select MIN(num) from suntan) --(select MIN(num) from suntan)

select id,num, decode(id, 1, num*2,
2, num*2,
num) newDa from suntan; -- pl/sql 不支持， SQL/PLUS 支持

select to_number('$2', '$99.99') from suntan;

select constraint_name, constraint_type, search_condition from user_constraints where table_name = 'SUNTAN'

select constraint_name, column_name from user_cons_columns where table_name = 'SUNTAN'

describe suntan; -- pl/sql 不支持， SQL/PLUS 支持

DESC suntan; -- pl/sql 不支持， SQL/PLUS 支持

select * from user_tables;

select * from user_objects; --查询当前用户不同的对象类型

select * from user_catalog; -- 查询当前用户的表、视图、同义词、序列


--创建视图和操作视图
create VIEW suntanView(id1, name1) as select s.id, s.name from suntan s where s.id=1; --PL/SQL不支持

select view_name, text from user_views;

select * from SUNTANVIEW;


--修改视图
create or replace View SUNTANVIEW as select id, name, num from suntan;
--在视图上可以执行DML操作，但必须遵循以下规则：
--1。 在简单视图上可以执行DML操作
insert into suntanView(5, 'suntan5', 5); 错误， 不能执行
update suntanView set name = 'suntan2' where id=2; 可以执行
delete from suntanView where id=2;
--2。 如下视图中包含以下内容不能删除行
-- a。 group 函数
-- b. group by 函数
-- c. distinct 函数
-- 删除视图
drop view suntanView


--序列
create sequence suntan_seq
increment by 1
start with 10
maxvalue 15
nocache
nocycle

select object_name, status from user_objects where object_type = 'SEQUENCE'--检索当前用户的序列名及其状态

select sequence_name, min_value, max_value, increment_by, last_number from user_sequences; --检索当前用户的序列名称及其设置

select suntan_seq.currval from dual; --第一次会提示尚未定义，执行完nextval后就可以了

select suntan_seq.nextval from dual;

select last_number from user_sequences where sequence_name='SUNTAN_SEQ'

alter sequence suntan_seq increment by 1 maxvalue 20 nocache nocycle

drop sequence suntan_seq;

--索引
create index num_index on suntan(num);

select ic.index_name, ic.column_name, ic.column_position col_pos, ix.uniqueness from
user_indexes ix, user_ind_columns ic where ic.index_name=ix.index_name
and ic.table_name='SUNTAN'

drop index num_index;


create tablespace suntan datafile 'D:\soft\oradata\database\suntan.dbf' size 50M;

--drop tablespace suntan including contents and datafile;
--ALTER DATABASE DATAFILE <datafile_name> RESIZE <new_size>;
--ALTER TABLESPACE <tablespace_name> ADD DATAFILE <datafile_name> SIZE <size>

create user test identified by test default tablespace suntan;

--drop user test cascade;

grant CONNECT,RESOURCE,DBA to test


//------------------------------------------------------------------------------------------------------------------------------------

上面说到了创建表分区的例子，所以创建表空间的时候创建了好几个表空间物理文件，之后感觉创建的有误，想删掉它们，可是删不掉(因为数据库进程启动着)，所以把数据库服务给关了，直接Shift+Del把文件给删掉了。

问题出来了：我再次启动oracle相关服务后，登录不了了。

出现了ORA-01033:ORACLE initialization or shutdown in progress



原因就是因为我把介质文件给删掉了

解决步骤：

以DBA(通常有好几个sys或system)用户sqlplus登录

请输入用户名: connect system/sys as sysdba

如果上面登陆不了可以换作（
sqlplus/nolog
connect sys/password as sysdba
）

然后(卸载数据)输入：

SQL> shutdown normal

会出现：
ORA-01109: 数据库未打开



已经卸载数据库。
ORACLE 例程已经关闭。



然后(装载数据)输入：

SQL> startup mount



ORACLE 例程已经启动。

数据库装载完毕。



之后：

SQL>alter database open;

第 1 行出现错误:
ORA-01157: 无法标识/锁定数据文件 6 - 请参阅 DBWR 跟踪文件
ORA-01110: 数据文件 6: 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\RAILWAY\2008_1.DAT'



SQL> alter database create datafile 6;

数据库已更改。

SQL>alter database open;

第 1 行出现错误:
ORA-01113: 文件 6 需要介质恢复
ORA-01110: 数据文件 6: 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\RAILWAY\2008_1.DAT'



SQL> alter database datafile 6 offline drop;

数据库已更改。



继续

SQL>alter database open;

如果出现以上错误，继续执行相关操作，上面的6要和create和drop对应。



如果

SQL> alter database open;

出现了：

数据库已更改。

则成功了。

这时可以再用PL/SQL等客户端工具，登录操作数据库了。


create table jbossSys_user(
name varchar2(40) not null,
password varchar2(40) not null,
primary key(name)
)

insert into jbossSys_user values('lihuoming','123456')
insert into jbossSys_user values('zhangfeng','111111')
insert into jbossSys_user values('wuxiao','123')


create table jbossSys_userrole(
username varchar2(40) not null,
rolename varchar2(40) not null,
primary key(username)
)
alter table jbossSys_userrole drop primary key cascade
alter table jbossSys_userrole add constraint jboss_user_role_pk primary key(username, rolename)

select constraint_name, constraint_type--, search_condition
from user_constraints where table_name='JBOSSSYS_USERROLE'
select constraint_name, column_name from user_cons_columns where table_name='JBOSSSYS_USERROLE'

//-------------------------------------------------------------------------------------
1 将数据库TEST完全导出,用户名system 密码manager 导出到D:\daochu.dmp中
exp system/manager@TEST file=d:\daochu.dmp full=y
2 将数据库中system用户与sys用户的表导出
exp system/manager@TEST file=d:\daochu.dmp owner=(system,sys)
3 将数据库中的表table1 、table2导出
exp system/manager@TEST file=d:\daochu.dmp tables=(table1,table2)
4 将数据库中的表table1中的字段filed1以"00"打头的数据导出
exp system/manager@TEST file=d:\daochu.dmp tables=(table1)query=\" where filed1 like '00%'\"
上面是常用的导出，对于压缩我不太在意，用winzip把dmp文件可以很好的压缩。
不过在上面命令后面 加上 compress=y 就可以了
数据的导入
1 将D:\daochu.dmp 中的数据导入 TEST数据库中。
imp system/manager@TEST file=d:\daochu.dmp
上面可能有点问题，因为有的表已经存在，然后它就报错，对该表就不进行导入。
在后面加上 ignore=y 就可以了。
2 将d:\daochu.dmp中的表table1 导入
imp system/manager@TEST file=d:\daochu.dmp tables=(table1)
基本上上面的导入导出够用了。不少情况我是将表彻底删除，然后导入。


ORA-01795的原因及解决办法
2008年10月18日 星期六 下午 10:20
系统报出一SQL异常，内容如下：

java.sql.SQLException: ORA-01795: maximum number of expressions in a list is 1000

找出抛异常时执行的SQL语句，貌不惊人啊，很平常一SQL语句，内容类似：SELECT * FROM DUAL T WHERE T.DUMMY IN ('1', '2', '3', ...)，只是IN后括号里的主键值多了些，其它没啥特别的。

看ORA-01795中给出的内容是SQL语句的 expressions 中list接受的最大值是1000，查了下ORA-01795的说明，确定问题出在IN后括号里的主键值超过1000上。

解决思路大至有两种，一种是换用JOIN或者EXIST，另一种是仍然用IN，但是把条件分成多个少于1000的IN即：SELECT * FROM DUAL T WHERE T.DUMMY IN ('1', '2', '3',...,'1000') OR IN ('1001', '1002', ..., '2000') OR ...

由于个人感觉JOIN不直观、EXIST含义不易理解，采用了方式二，附代码：

========================================================
StringBuffer sb = new StringBuffer();

for(int i=0; i<custNOs.length; i++) {

if(StringUtil.isEmpty(custNOs[i])) continue;

//这里不要犯低级错误而写成：if(i == custNOs.length)
if(i == (custNOs.length-1))
sb.append("'" + custNOs[i] + "'"); //SQL拼装，最后一条不加“,”。
else if((i%1000)==0 && i>0)
sb.append("'" + custNOs[i] + "' ) OR CUST_NO IN ( "); //解决ORA-01795问题
else
sb.append("'" + custNOs[i] + "', ");

}

String selectSQL = "SELECT * FROM CUSTOMER T WHERE T.CUST_NO IN ( " + sb.toString() + " )";


oracle中的 exists 和 in 简单用法介绍
本文来自: (www.91linux.com) 详细出处参考：http://www.91linux.com/html/article/database/oracle/20080509/10799.html

有两个简单例子，以说明 “exists”和“in”的效率问题

1) select * from T1 where exists(select 1 from T2 where T1.a=T2.a) ;

T1数据量小而T2数据量非常大时，T1<<T2 时，1) 的查询效率高。

2) select * from T1 where T1.a in (select T2.a from T2) ;

T1数据量非常大而T2数据量小时，T1>>T2 时，2) 的查询效率高。



exists 用法：

请注意 1）句中的有颜色字体的部分 ，理解其含义；

其中 “select 1 from T2 where T1.a=T2.a” 相当于一个关联表查询，相当于

“select 1 from T1,T2 where T1.a=T2.a”

但是，如果你当当执行 1） 句括号里的语句，是会报语法错误的，这也是使用exists需要注意的地方。

“exists（xxx）”就表示括号里的语句能不能查出记录，它要查的记录是否存在。

因此“select 1”这里的 “1”其实是无关紧要的，换成“*”也没问题，它只在乎括号里的数据能不能查找出来，是否存在这样的记录，如果存在，这 1） 句的where 条件成立。



in 的用法：

继续引用上面的例子

“2) select * from T1 where T1.a in (select T2.a from T2) ”

这里的“in”后面括号里的语句搜索出来的字段的内容一定要相对应，一般来说，T1和T2这两个表的a字段表达的意义应该是一样的，否则这样查没什么意义。

打个比方：T1，T2表都有一个字段，表示工单号，但是T1表示工单号的字段名叫“ticketid”，T2则为“id”，但是其表达的意义是一样的，而且数据格式也是一样的。这时，用 2）的写法就可以这样：

“select * from T1 where T1.ticketid in (select T2.id from T2) ”



当然，exists和in还有其他用法，这里就不做介绍了

本文来自: (www.91linux.com) 详细出处参考：http://www.91linux.com/html/article/database/oracle/20080509/10799.html


最高效的删除重复记录方法 ( 因为使用了ROWID)例子：
DELETE FROM EMP E WHERE E.ROWID > (SELECT MIN(X.ROWID) FROM EMP X WHERE X.EMP_NO = E.EMP_NO);


select sum(tb.truck_size_3t),
sum(tb.truck_Size_5T),
sum(tb.truck_Size_8T),
sum(tb.truck_Size_10T),
sum(tb.truck_Size_12T),
sum(tb.truck_Size_20F),
sum(tb.truck_Size_40F),
sum( tb.truck_size_40q),
sum(tb.truck_Size_45F),
sum( tb.truck_Size_48F),
sum(tb.truck_Size_RF)
from truck_booking tb , Company cus, Company client
where tb.truck_booking_id in
(select distinct truck_booking_id from truck_booking_item tbi , company c ,Truck_Booking_Location loc,
Truck_Booking_Destination tbd , company ven
where tbi.vendor_id = c.company_id
and c.company_code ='APEX'
and loc.company_code ='DONGGUAN' and loc.company_name='東莞德源'
and tbd.truck_booking_item_id =tbi.truck_booking_item_id
--and tbi.truck_booking_item_id=tbd.truck_booking_item_id
and tbi.vendor_id = ven.company_id
and loc.truck_booking_item_id=tbi.truck_booking_item_id
and tbi.confirm_date >= to_date('2008-11-10','yyyy-MM-dd')
--and tbi.confirm_date <= to_date('2008-11-13','yyyy-MM-dd')
and cus.company_id=tb.customer_id and client.company_id=tb.client_id )
--group by tb.truck_booking_id


select distinct sum(tbi.cbm) jj ,sum(tbi.weight) kk ,c.company_code ven ,loc.company_code,loc.company_name,to_char(tbi.confirm_date, 'yyyy-MM-dd')
from truck_booking_item tbi , company c ,Truck_Booking_Location loc,
Truck_Booking_Destination tbd , company ven
where tbi.vendor_id = c.company_id
and tbd.truck_booking_item_id =tbi.truck_booking_item_id
and tbi.truck_booking_item_id=tbd.truck_booking_item_id and tbi.vendor_id = ven.company_id
and loc.truck_booking_item_id=tbi.truck_booking_item_id and tbi.confirm_date >= to_date('2008-11-10','yyyy-MM-dd')

group by c.company_code,loc.company_code,loc.company_name,to_char(tbi.confirm_date, 'yyyy-MM-dd')
order by to_char(tbi.confirm_date, 'yyyy-MM-dd')


select distinct h.id, h.ccs_rl_no, h.charge_datetime,h.gate_charge_no,
h.client,h.vendor, h.client_group_id,h.clt_ref_booking_no,
h.declaration_type,h.declaration_form_total,h.remarks, h.charge_total,
h.cash_total,h.pos_total,h.payment_cash_total,h.payment_monthly_total,
gcg.client_group_name,
d.gate_charge_header_id,
sum(decode(ct.charge_type_name, '闸口费', d.charge_qty, '0')) 闸口费,
sum(decode(ct.charge_type_name, '报关费', d.charge_qty, '0')) 报关费,
sum(decode(ct.charge_type_name, '打单费', d.charge_qty, '0')) 打单费,
sum(decode(ct.charge_type_name, '做商检', d.charge_qty, '0')) 做商检,
sum(decode(ct.charge_type_name, '换商检', d.charge_qty, '0')) 换商检,
sum(decode(ct.charge_type_name, '退税', d.charge_qty, '0')) 退税,
h.charge_total -
sum(decode(ct.charge_type_name, '闸口费', d.charge_qty, '0')) -
sum(decode(ct.charge_type_name, '报关费', d.charge_qty, '0')) -
sum(decode(ct.charge_type_name, '打单费', d.charge_qty, '0')) -
sum(decode(ct.charge_type_name, '做商检', d.charge_qty, '0')) -
sum(decode(ct.charge_type_name, '换商检', d.charge_qty, '0')) -
sum(decode(ct.charge_type_name, '退税', d.charge_qty, '0')) 其他
from gate_charge_header h,
gate_charge_details d,
gcs_charge_type ct,
gcs_client_group gcg

where h.id = d.gate_charge_header_id and ct.id = d.charge_type and h.client_group_id = gcg.id
--and h.gate_charge_no in ('2009031200001', '2008122600070', '2008122600076', '2008122700032', '2009031200003')
group by h.id, h.ccs_rl_no, h.charge_datetime,h.gate_charge_no,
h.client,h.vendor, h.client_group_id,h.clt_ref_booking_no,
h.declaration_type,h.declaration_form_total,h.remarks,
h.charge_total,h.cash_total,h.pos_total,h.payment_cash_total,h.payment_monthly_total,
gcg.client_group_name,
d.gate_charge_header_id
order by gate_charge_no