`
suntanguo
  • 浏览: 6256 次
  • 性别: Icon_minigender_1
  • 来自: 深圳
社区版块
存档分类
最新评论

自己遇到的一些问题(随意杂乱)

阅读更多
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
分享到:
评论

相关推荐

Global site tag (gtag.js) - Google Analytics