Oracle的常见操作

1. 创建用户 Create user 用户名 identified by “密码”; 例如:create user ghc_ez identified by “ghc_211”; 授权: grant connect,resource,dba to用户名; 例如：grant connect,resource,dba to ghc_ez; 2.创建所需表空间--注意创建表空间的位置(datafile) -----查询当前用户所有的表的表空间------------- Select table_name 表名 ,tablespace_name 所使用表空间 from user_tables order by table_name; -------------------------------------------------- ------------------修改表空间----------------- 修改用户表table的表空间： alter table 表名 move tablespace 新表空间名; ------------------------------------------------------------------ ------------------创建表空间------------------ create tablespace fc_data logging datafile 'D:\app\Administrator\oradata\bi_data.dbf' size 50m autoextend on next 50m maxsize 20480m extent management local; ------------------------------------------------- create tablespace fwk_data logging datafile 'D:\app\Administrator\oradata\bi_data.dbf' size 50m autoextend on next 50m maxsize 20480m extent management local; ------------------------------------------------- create tablespace hie_data logging datafile 'D:\app\Administrator\oradata\bi_data.dbf' size 50m autoextend on next 50m maxsize 20480m extent management local; ------------------------------------------------- create tablespace fwk_data logging datafile 'D:\app\Administrator\oradata\bi_data.dbf' size 50m autoextend on next 50m maxsize 20480m extent management local; 3.导入 Imp 用户名/密码@服务名 full=y file=dmp文件所在的路径 ignore=y 例如:imp ghc_ez/ghc_211@orcl full=y file=d:ezhou.dmp ignore=y --导出数据-- exp hz_hie_201604/ghc_211@orcl FILE=d:\hz_hie_201604_data.dmp tables=(agd_doctor,analysis_patientsettlement） --导出当前用户的所有表和数据 exp hz_hie_201604/ghc_211@orcl owner=(hz_hie_201604) FILE=d:\hz_hie_201604_data.dmp --导入数据-- imp hz_hie_201604/ghc_211@orcl file=d:\hz_hie_201604_data.dmp full=y ignore=y --修改用户表table的表空间：alter table 表名 move tablespace 新表空间名; alter table view_usra10 move tablespace bi_data; --查询当前用户所有表的表空间名称 Select table_name 表名 ,tablespace_name 所使用表空间 from user_tables order by table_name; 3.impdp system/123456@WZOJK_250 directory=TY_DUMP_DIR dumpfile=fsdb_all_bak_20161114.DMP LOGFILE=fsdb_all_bak_20161114.LOG full=y impdp system/wanzhou123456@WZDB directory=TY_DUMP_DIR dumpfile=WZDB_ALL_BAK_20161130.DMP LOGFILE=wzdb_all_bak_20161201.LOG full=y 导出语句 expdp system/123456@WZOJK_250 directory=TY_DUMP_DIR1 dumpfile=wzdb_all_bak_20161206.DMP LOGFILE=wzdb_all_bak_20161206.LOG full=y expdp system/123456@ggdb_250 directory=TY_DUMP_DIR dumpfile=ggdb_all_bak_20170103.DMP LOGFILE=ggdb_all_bak_20170103.LOG full=y --游标的使用-- --for循环游标不需要开启和关闭游标-- declare --声明一个游标 cursor cur_emp is select empno,ename,job,sal from emp where job='IT'; --定义一个游标变量 cur_rec cur_emp%rowtype; begin for cur_rec in cur_emp loop dbms_output.put_line(cur_rec.empno||'-'||cur_rec.ename||'-'cur_rec.job||'-'cur_rec.sal); end loop; end / --fetch游标使用的时候必须明确的打开和关闭-- declare cursor cur_emp is select empno,ename,job,sal from emp where job='it'; cur_rec cur_emp%rowtype; begin open cur_emp; --loop循环 loop --提取一行数据到cur_rec中 fetch cur_emp into cur_rec; --判断是否取到值，没取到值就退出 --取到值cur_emp%notfound是false --取不到cur_emp%notfound是true exit when cur_emp%notfound; dbms_output.put_line(cur_rec.empno||'-'||cur_rec.name); end loop; close cur_emp; end / --游标的4种属性 %notfound fetch是否提取到数据 没有是true %found fetch是否提取到数据 有是true %rowfound 已经取出来的记录的条数 %isopen 游标是否打开 --存储过程 create or replace procedure Up_msg_get_messages( v_user_id varchar2,--用户id v_login_org varchar2, v_result out sys_refcursor) ) as begin open v_result for select a.*,b.* from fwk_msg a left join fwk_msg_type b on a.msg_type=b.msg_type_code where a.due_data>=sysdate end up_msg_get_messages; --函数 create or replace function fun_name(eno number) return number as v_sal emp.sal%type; begin select sal into v_sal from emp empno=eno; return v_sal; end fun_name; --权限 CONNECT角色，主要应用在临时用户，特别是那些不需要建表的用户， 通常只赋予他们CONNECT role。CONNECT是使用Oracle的简单权限， 拥有CONNECT角色的用户，可以与服务器建立连接会话(session，客户端对服务器连接，称为会话) RESOURCE角色，更可靠和正式的数据库用户可以授予RESOURCE role。RESOURCE提供给用户另外的权限 以创建他们自己的表、序列、过程(procedure)、触发器(trigger)、索引(index)等。 DBA角色，DBA role拥有所有的系统权限----包括无限制的空间限额和给其他用户授予各种权限的能力。用户SYSTEM拥有DBA角色 一般情况下，一个普通的用户(如SCOTT)，拥有CONNECT和RESOURCE两个角色即可进行常规的数据库开发工作。 --给某个用户或者角色权限 grant connect to wj; grant resource to wj; grant connect,resource,dba to wj; grant create session to zhangsan;//授予zhangsan用户创建session的权限，即登陆权限 grant unlimited tablespace to zhangsan;//授予zhangsan用户使用表空间的权限 grant create table to zhangsan;//授予创建表的权限 grant drop table to zhangsan;//授予删除表的权限 grant insert table to zhangsan;//插入表的权限 grant update table to zhangsan;//修改表的权限 grant all to public;//这条比较重要，授予所有权限(all)给所有用户(public) grant select on tablename to zhangsan;//授予zhangsan用户查看指定表的权限 grant drop on tablename to zhangsan;//授予删除表的权限 grant insert on tablename to zhangsan;//授予插入的权限 grant update on tablename to zhangsan;//授予修改表的权限 grant insert(id) on tablename to zhangsan; grant update(id) on tablename to zhangsan;//授予对指定表特定字段的插入和修改权限，注意，只能是insert和update grant alert all table to zhangsan;//授予zhangsan用户alert任意表的权限 --回收权限 revoke dba from wj; --创建自动增长列-- create sequence employ_autoId minvalue 1 maxvalue 999999999999999 start with 1 increment by 1 nocache; --创建触发器将序列中的赋值给插入到emoloee表中 create or replace trigger insert_employee_autoId before insert on employee(表名) for each row begin select employ_autoId(sequence名称).nextval into :new.id(需要自动增长的名称) from dual; end insert_employee_autoId;