Oracle小知识_1

引用自：http://blog.sina.com.cn/s/blog_4758a28b01000abq.html

---select into 使用

select * from scott.T_EMP_INFO;

insert into scott.t_emp_info(i_id,s_username,s_password,s_age,s_place)
values (2,'nickshang','zhenlain','22','programe');

select * from scott.t_emp_info;
 
 --创建备份表  相同结构
 create table scott.t_emp_info_backup as select *from scott.t_emp_info where 1=0;
 
 --复制数据
insert into scott.t_emp_info  (selecti_id+1,s_username,s_password,s_age,s_place fromscott.t_emp_info);

 

---关闭数据库
SHUTDOWN immediate

//用户加锁，解锁
以sys/change_on_install登录  
  加锁：  alter  user  用户名  account  lock  
  解锁:  alter  user  用户名  account   unlock

//oracle创建用户

CREATE USER "STUDNET_TEST" PROFILE "DEFAULT"IDENTIFIED BY "*******" DEFAULT TABLESPACE "SYSAUX" TEMPORARYTABLESPACE "TEMP" ACCOUNT UNLOCK
GRANT "CONNECT" TO "STUDNET_TEST" WITH ADMIN OPTION
GRANT "DBA" TO "STUDNET_TEST" WITH ADMIN OPTION

//更改用户密码 --注意用户名要大写
ALTER USER "SYSTEM" IDENTIFIED BY "zhenlian";

//查看数据库中所有表
select * from user_all_tables

//查看数据库中所以非系统用户
select * from dba_users where default_tablespace <> 'system';

//查看所有用户
select  *  from  all_users;  

//查看DBA用户
select  *  from  dba_users; 

//oracle 连接描述
(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=zhenlian)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=orcl)))

oracle中查看用户权限    
1.查看所有用户：
  select * from dba_user;
  select * from all_users;
  select * from user_users;
2.查看用户系统权限：
  select * from dba_sys_privs;
  select * from all_sys_privs;
  select * from user_sys_privs;
3.查看用户对象权限：
  select * from dba_tab_privs;
  select * from all_tab_privs;
  select * from user_tab_privs;
4.查看所有角色：
  select * from dba_roles;
5.查看用户所拥有的角色：
  select * from dba_role_privs;
  select * from user_role_privs;
以上是在Oracle中查看用户权限 ，
在DB2中为：
  select * from syscat.dbauth
  或者
  get authorizations

优化SQl语句的核心步骤    
1）确保TIMED_STATISTICS在实例级设置为TRUE。
2）确保MAX_DUMP_FILE_SIZE设置为足够大的值。
3）确定指向USER_DUMP_DEST的位置，并确保有足够大的值。
4）为正在被讨论的会话开启SQL_TRACE。
5）运行应用程序。
6）确定跟踪文件的位置。
7）在步骤6所确定的跟踪文件上运行tkprof以产生跟踪输出文件。
8）研究跟踪输出文件。
9）优化最昂贵的SQL语句。
10）反复运行步骤4到步骤9，直到所需的性能目标达到为止

ORACLE的基本语法集锦    
ORACLE的基本语法集锦

 

-- 表
create table test (names varchar2(12),
                  dates date,
                  num   int,
                  dou   double);
-- 视图
create or replace view vi_test as
select * from test;

-- 同义词
create or replace synonym aa
for dbusrcard001.aa;

-- 存储过程
create or replace produce dd(v_id in employee.empoy_id%type)
as
begin
  
end
dd;

-- 函数
create or replace function ee(v_id in employee%rowtype) returnvarchar(15)
is
var_test varchar2(15);
begin
  return var_test;
exception when others then
  
end

-- 三种触发器的定义
create or replace trigger ff
alter delete
on test
for each row
declare
begin
   delete from test;
   if sql%rowcount < 0 orsql%rowcount is null then
     rais_replaction_err(-20004,"错误")
   end if
end

create or replace trigger gg
alter insert
on test
for each row
declare
begin
   if :old.names = :new.namesthen
     raise_replaction_err(-2003,"编码重复");
   end if
end

create or replace trigger hh
for update
on test
for each row
declare
begin
  if updating then
    if :old.names <> :new.names then
 reaise_replaction_err(-2002,"关键字不能修改")
    end if
  end if
end

-- 定义游标
declare
   cursor aa is
     select names,num from test;
begin
   for bb in aa
   loop
       if bb.names = "ORACLE" then
       
       end if
   end loop;
  
end

-- 速度优化，前一语句不后一语句的速度快几十倍
select names,dates
from test,b
where test.names = b.names(+) and
     b.names is null and
     b.dates > date('2003-01-01','yyyy-mm-dd')

 
select names,dates
from test
where names not in ( select names
                      from b
                     where dates > to_date('2003-01-01','yyyy-mm-dd'))
                      

-- 查找重复记录
select names,num
from test
where rowid != (select max(rowid)
                from test b
               where b.names = test.names and
                     b.num = test.num)

-- 查找表TEST中时间最新的前10条记录
select * from (select * from test order by dates desc) where rownum< 11

-- 序列号的产生
create sequence row_id
minvalue 1
maxvalue 9999999999999999999999
start with 1
increment by 1

insert into testvalues(row_id.nextval,....) 

 

Trackback:http://tb.blog.csdn.net/TrackBack.aspx?PostId=1790780

促进最佳SQL的一些提示    
1．在从I/O的观点来看使用索引没有意义时鼓励进行全表扫描。
2．如果SQL包含了子查询，则优化它们。
3．在SQL 语句的where子句的条件中使用not exists来代替not in。
4．使用有前导字段的like运算来代替substr函数。
5．可在适当的地方使用nvl函数。
6．对于带有许多OR条件的非常复杂的查询来说，可以考虑用unionall重写。
7．使用合适的索引。
8．如果查询总是去检索基于主从表关系的行，则应建立外键列的索引。
9．使用组合索引（有两个或多个列的索引）。
10．考虑使用非唯一索引来支持唯一性约束。
11．当允许使用约束时，考虑使用enable novalidate子句。
12．当where子句的条件中包含低数据基数的列，包含关于这些列的像or、and或not这样的逻辑运算，或者从有大量行的表中返回大量的行时，应考虑位图索引。
13．考虑单表散列或索引簇（有赖于应用程序）。
14．要了解带有视图的SQL语句。
15．尽可能避免远程访问。
16．主动地确定嵌套循环、合并连接或散列连接。
17．在可能的情况下确定并使用数组处理和批处理。
18．如果应用程序包含了许多动态SQL的发生，则考虑使用executeimmediate，它的运行比DBMS_SQL更好。
19．对于非常大的表，考虑利用表和索引的分区。
20．如果仍然使用基于规则的优化程序，则构造from子句使得最小的表成为在表的列表中定义的最后一个表。
21．如果需要减少索引建立所花费的时间，可以在会话级将sort_area_size参数更改为一个大值，使得该索引建立的大多数排序在内存中发生。
22．需要不断的测试所有的查询。

 关于oracle存储过程的若干问题备忘    
 

 

1.在oracle中，数据表别名不能加as，如：

select a.appname from appinfo a;-- 正确
select a.appname from appinfo as a;-- 错误
 也许，是怕和oracle中的存储过程中的关键字as冲突的问题吧

2.在存储过程中，select某一字段时，后面必须紧跟into，如果select整个记录，利用游标的话就另当别论了。

  select af.keynode into kn from APPFOUNDATIONaf where af.appid=aid and af.foundationid=fid;--有into，正确编译
  select af.keynode from APPFOUNDATION af whereaf.appid=aid and af.foundationid=fid;--没有into，编译报错，提示：Compilation
  Error: PLS-00428: an INTO clause is expected inthis SELECT statement

3.在利用select...into...语法时，必须先确保数据库中有该条记录，否则会报出"nodata found"异常。

  可以在该语法之前，先利用select count(*) from查看数据库中是否存在该记录，如果存在，再利用select...into...

4.在存储过程中，别名不能和字段名称相同，否则虽然编译可以通过，但在运行阶段会报错

 select keynode into kn from APPFOUNDATIONwhere appid=aid and foundationid=fid;-- 正确运行
select af.keynode into kn from APPFOUNDATION af whereaf.appid=appid and af.foundationid=foundationid;--运行阶段报错，提示
ORA-01422:exact fetch returns more than requested number ofrows
5.在存储过程中，关于出现null的问题

假设有一个表A，定义如下：
create table A(
id varchar2(50) primary key not null,
vcount number(8) not null,
bid varchar2(50) not null -- 外键
);如果在存储过程中，使用如下语句：
select sum(vcount) into fcount from A wherebid='xxxxxx';如果A表中不存在bid="xxxxxx"的记录，则fcount=null(即使fcount定义时设置了默认值，如：fcountnumber(8):=0依然无效，fcount还是会变成null)，这样以后使用fcount时就可能有问题，所以在这里最好先判断一下：
if fcount is null then
   fcount:=0;
end if;这样就一切ok了。

6.Hibernate调用oracle存储过程

       this.pnumberManager.getHibernateTemplate().execute(
               new HibernateCallback() ...{
                   public Object doInHibernate(Session session)
                           throws HibernateException, SQLException ...{
                       CallableStatement cs = session
                               .connection()
                               .prepareCall("{call modifyapppnumber_remain(?)}");
                       cs.setString(1, foundationid);
                       cs.execute();
                       return null;
                   }
               });
 

Trackback:http://tb.blog.csdn.net/TrackBack.aspx?PostId=1790959

昨天刚装好了oracle10g却总是碰到RemoteOperationException: ERROR:Wrong password for user；
查了半天也没搞懂为什么，明明用户和密码是对的。后来看一位网友发的文章才明白是本地策略的问题。

现抄录下来供大家参考：

在OEM中有些操作需要输入操作系统的用户名密码才能继续下去，但是以前无论怎样输入，总是会返回RemoteOperationException:ERROR: Wrong password for user.（错误的用户名密码）的错误。
今天好好的查了一下，终于明白了问题的所在。

解决的方法就是在用户策略里面将你需要使用的用户授予Logon as aBatch Job的权限。

1. Click on START => Programs => Administrative Tools=> Local Security Policy (or also via START => Settings =>Control Panel => Administrative Tools)打开控制面板中的管理工具中的本地安全策略
2. Under Local Policies on the left, select User Rights Assignmentfolder
然后是本地策略－用户权限分配
3. Look at the right side of the window and scroll down to the"Logon as Batch Job" privilege and select it. 然后找到Logon as aBatch Job（作为批处理作业登陆）这一项
4. Either select Action from the menu bar then Security orright-click on the "Logon as a Batch Job" privilege thenSecurity.
5. Select the Add button which evokes a Select Users/Groupsbox.
6. From the "Look In" field, select the localworkstation/server.
7. Select the User administrator.
8. Select Add then OK.
然后打开将administrator这个用户添加一下就ok了！