oracle10g 心得

一 本地登录： 
     系统管理员：以sys用户登录 oracle SQL*Plus时,主机字符串(H):填写什么? 答案是：全局数据库+ as sysdba（我的为例：orcl as sysdba）
      普通用户登录：只在UserName中输入： 用户名/密码@服务器名 将连接到指定的数据库。例子：Hr/hjm880514@orcl
     切换用户： SQL> connect 用户名/密码@服务器名  例子：connect hjm/hjm880514@myoracle  切换到 connect HR/hjm880514@orcl
二、远程登陆：
登陆方式和本地登陆大致一样：（假设本地远程DB的SID为orcl_192.168.2.254）
（1）、普通帐户：
      sqlplus scott/tiger@orcl_192.168.2.254
（2）、以DBA登陆：
      
      或者先登陆：sqlplus /nolog
      然后切换到管理员：
      conn  sys/manage@192.168.2.254 as sysdba
1 终止Sql语句：ctrl+c 
2  登录进去解锁用户：SQL> alter user hr account unlock;（解锁HR用户）
3 锁定用户：SQL> alter user hr account lock;（加锁 HR用户）
4 修改用户密码：SQL> alter user hr identified by 123;（更改HR用的密码为123）
5 查询用户及状态：SQL> select username,account_status from dba_users;（用户名，和锁定状态）
6 查看当前用户：SQL> show user
7链接用户：SQL> conn hr/123（链接HR用户 用户名/密码）
8 链接sys用户：conn /as sysdba   
9 设置用户密码失效：alter user hr password expire;（设置密码过期失效）
10 创建表：　create table tableName (a number,b varchar2(10)); 
11 查询用户权限：select * from user_sys_privs;（查询的是当前用链接户的权限）
12 给用户赋权：
（参考HR权限：grant create view，unlimited tablespace， create database link,create sequence,create session,alter session,create synonym to hjm; ）
　grant create session to zhangsan;//授予zhangsan用户创建session的权限，即登陆权限
　grant unlimited tablespace to zhangsan;//授予zhangsan用户使用表空间的权限
　grant create table to zhangsan;//授予创建表的权限
　grante drop table to zhangsan;//授予删除表的权限
　grant insert table to zhangsan;//插入表的权限
　grant update table to zhangsan;//修改表的权限 
13 取消用户权限： revoke crate view from hjm;收回创建视图的权限

14 修改表：
（1）增加新字段（默认值为nukown）：alter table table_name  add (name varchar(20) default 'unkown');
（2）修改表字段：alter table table_name  modify (name varchar2(30) default 'hebei'); 
（3）删除表字段：alter table table_name  drop column column_name;
（4）修改表名：rename TableOldName to TableNewName; 
（5）清空表名：truncate table tableName;
 (6) 给表加注释：comment on table tableName is '注释内容';
（7) 查看表结构：desc tableName 
15 写完sql命令后“/”执行命令，ctrl+c退出命令行！
16 查询当前用户创建的表：select table_name from user_tables;
查询用户创建的触发器：select trigger_name from user_triggers;
17 oracle的数据类型：一 char(n) n=1 to 2000字节、二 varchar2(n) n=1 to 4000字节三、number(m,n) 
m=1 to 38 n=-84 to 127四date 日期类型 五 long、 boolean 
5
、
LOB
数据类型
 
百度文库数据类型：http://wenku.baidu.com/view/c41654d084254b35eefd341c.html
18 oracle 字段组合：select id,name||'   '||age xs from mytable;
触发器：   两个重要的内存表（其中old相当于sql server触发器中的deleted，new相当于 sqlserver中的 inserted表）
             OLD                  NEW 
INSERT     -                   +
DELETE   +                   -
UPDATE   +                   +
+:代表使用到该表
-：代表不适用到该表
 1 删除触发器：
create trigger 触发器名
after delete on 表名
for each row
begin
update mytable set name='删除后更新' where id=:old.id; （old表是删除和更新表，取其中字段为：<：old.字段名>)
end 触发器名; （删除触发器必须带触发器名）
2 增加触发器：
 create trigger 触发器名
  after insert on 表名
  for each row
  begin
  insert into mytable values(:new.id,:new.name,3);  （new表是增加和更新表，取其中字段为：<：new.字段名>)
 end;(没有触发器名)
3 更新触发器：同上更新表可以从old和new表中取 注意 after 后面改为update；
19  oracle主键自增 ：create sequence  userID_seq  increment by 1 start with 1 //创建序列 Users表中id从序列中取
例：insert into  users values(userID_seq.Nextval,'hjm','hjm','hjm',1)  

20 存储过程创建和执行：
（1） create or replace procedure pro_test
  as
  begin
       for i in (
           select * from test
          ) loop
           dbms_output.put_line('tabels in scott schema：'||i.uname);
       end loop;
  end pro_test;--注意end
  
call pro_test();--执行存储过程
（2）
 create or replace procedure pro_test1
  as
  V_TOTAL NUMBER(10);
  begin
      SELECT count(*) into v_total from test;
           dbms_output.put_line('tabels in scott schema：'||v_total);
       
  end pro_test1;--注意end
  
call pro_test1();
执行带参数存储过程：1> call pro_test2(3);
2> declare n number;
begin
n:=6;
pro_test2(n);
end;

21 oracle给database link 表 创建同义词： create synonym 名称 for 表名@数据库Link名称  例：create synonym hhh for a001@d_orcl;  // select * from hhh　
22 31 查询表的字段：select * from user_col_comments t where t.table_name='USERS' //表名必须为大写
23 小计功能实现：select nvl(wtime,'合计') ,uname b,sum(id) c from test group by rollup(wtime,uname)
24 导入.DMP文件（不同用户）：imp system/hjm880514@orcl fromuser=gadata0024 touser=gadatata0011 file=I:\1.dmp
25 插入系统时间：insert into test1 values(to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),1,1 )
26 取字段信息及备注：SELECT table_name,table_name||'.'||column_name,comments FROM DBA_COL_COMMENTS where table_name='ITEM' and owner='HYMS0001'<pre code_snippet_id="195842" snippet_file_name="blog_20140220_1_7047994" name="code" class="csharp">27 select *　from item where   instr(stritemname, '手') > 0
28, orale 2个日期差多少小时：select (Sysdate-to_date( '2014-05-04 20:20:20','yyyy-MM-dd hh24:mi:ss'))*24 hour  from dual                                  <span style="font-family: Arial, Helvetica, sans-serif;">28 导入数据： imp userid=用户名/密码@orcl file=d:\nc60.dmp full=y                                                                                                                                                                                          29                   select object_name,machine,s.sid,s.serial# 
from v$locked_object l,dba_objects o ,v$session s
where l.object_id　=　o.object_id and l.session_id=s.sid; //查询被锁项目
alter system kill session '1119,7900';//1119 对应sid 7900 对应serial# 

D:\>imp study/study@orcl file=zrp.dmp tables=toms ignore=y  //导入某张表