工作中oracle数据库笔记

1.oracle数据库导入导出

（1）imp 用户名/密码@网络服务名   file=xxx.dmp  full=y  fromuser=    touser=   log=路径位置    ignore=y

exp WMABS/WMABS@192.168.0.253:1521/orcl file=D:\WMABS2.dmp  LOG=D:\LOG.TXT

imp ZR_ABS_DEV/ZR_ABS_DEV@127.0.0.1:1521/orcl.27.239.4 file=E:\zr_abs.dmp  fromuser=ZR_ABS  touser=ZR_ABS

（2）数据泵工具导入

drop user SPDABS cascade;

create user SPDABS identified by SPDABS;

grant connect,dba,resource to SPDABS;

grant unlimited tablespae to SPDABS;

注意：数据泵导入时，没有的用户会自动创建，但是用户密码需要设置

impdp ABS/ABS dumpfile=ABS.dmp directory=data_pump_dir logfile=ABS.logremap_schema=ABSOLD:ABSNEW;

（3）exp 用户名/密码@远程的IP:端口/实例file=存放的位置:\文件名称.dmpfull=y

将数据库中的表table1、table2导出：exp system/manager@TEST file=d:\daochu.dmp tables=(table1,table2) 

  将数据库中的表table1中的字段filed1以"00"打头的数据导出：exp system/manager@TEST file=d:\daochu.dmp tables=(table1) query=\" where filed1 like '00%'\"

（4）数据泵工具导出

SELECT * FROM DBA_DIRECTORIES （查询data_pump_dir路径）

expdp ABS/ABS directory=data_pump_dir dumpfile=ABS.dmplogfile=ABS.logversion=11.1.0.6.0

2. oracle数据库添加新用户

oracle 创建用户的语法：

create user username 【identified by password】【identified exeternally】【identified globally as 'CN=user'】

[DEFAULTTABLESPACE tablespace]

[TEMPORARY TABLESPACE temptablespace]

[QUOTA [integer K[M] ] [UNLIMITED] ] ON tablespace

[PROFILES profile_name]

[PASSWORD EXPIRE]

[ACCOUNT LOCK / ACCOUNT UNLOCK] 

注释如下
CREATE USER username：用户名，一般为字母数字型和“#”及“_”符号。

IDENTIFIED BY password：用户口令，一般为字母数字型和“#”及“_”符号。

IDENTIFIED EXETERNALLY：表示用户名在操作系统下验证，该用户名必须与操作系统中所定义的用户名相同。

IDENTIFIED GLOBALLY AS ‘CN=user'：用户名由Oracle安全域中心服务器验证，CN名字表示用户的外部名。

[DEFAULT TABLESPACE tablespace]：默认的表空间。

[TEMPORARY TABLESPACE tablespace]：默认的临时表空间。

[QUOTA [integer K[M] ] [UNLIMITED] ] ON tablespace：用户可以使用的表空间的字节数。

[PROFILES profile_name]：资源文件的名称。

[PASSWORD EXPIRE]：立即将口令设成过期状态，用户再登录前必须修改口令。

[ACCOUNT LOCK or ACCOUNT UNLOCK]：用户是否被加锁，默认情况下是不加锁的。

3.oracle删除用户

drop user username;

drop user username cascade;

cascade：用户拥有对象，则不能直接删除

4.oracle数据库的三种标准角色

(1)connect role(连接角色)

　　 临时用户，特别是那些不需要建表的用户，通常只赋予他们connectrole。connect是使用oracle的简单权限，这种权限只有在对其他用户的表有访问权时，包括select、insert、update和delete等，才会变得有意义。拥有connect role的用户还能够创建表、视图、序列(sequence)、簇(cluster)、同义词(synonym )、会话(session)和与其他数据库的链(link)（不同版本的oracle有不同）。

(2)resource role(资源角色)

　 　更可靠和正式的数据库用户可以授予resource role。resource提供给用户另外的权限以创建他们自己的表、序列、过程(procedure)、触发器(trigger)、索引(index)和簇(cluster)。

(3)dba role(数据库管理员角色)

　 　dba role拥有所有的系统权限----包括无限制的空间限额和给其他用户授予各种权限的能力。system由dba用户拥有。下面介绍一些dba经常使用的典型权限。

　　 ①grant(授权)命令

　　 下面对刚才创建的用户username授权，命令如下：

　 　grant connect, resource to username;

　 　②revoke(撤消)权限

5.oracle数据库删除表的注意事项

在删除一个表中的全部数据时，须使用: truncate table 表名

6.oracle数据库的常用函数与sql

(1)  空值处理

函数 NVL(expr1,expr2) 如果expr1为NULL，则函数返回expr2，否则返回expr1本身

函数NVL2(expr1,expr2,expr3)如果参数表达式expr1值为NULL，则NVL2()函数返回参数表达式expr3的值；如果参数表达式expr1值不为NULL，则NVL2()函数返回参数表达式expr2的值。

函数coalesce(expr1,expr2,expr3)返回第一个非空值

(2)  常用函数

Count(*) 表中行的总数

Count(column) 列不为空的行数

Stddev(column) 列的标准偏差

Variance(column) 列的方差

(3)  常用子句

Group by column having

(4)  集合操作

Union 并（去掉重复行）

Union all 并（保留重复行）

Intersect 交

Minus 差  

7.Oracle数据库操作中，我们有时会用到锁表查询以及解锁和kill进程等操作，那么这些操作是怎么实现的呢？本文我们主要就介绍一下这部分内容。

(1)锁表查询的代码有以下的形式：

select count(*) from v$locked_object;

select * from v$locked_object;

(2)查看哪个表被锁

select b.owner,b.object_name,a.session_id,a.locked_mode fromv$locked_object a,dba_objects b where b.object_id = a.object_id;

(3)查看是哪个session引起的

select b.username,b.sid,b.serial#,logon_time from v$locked_objecta,v$session b where a.session_id = b.sid order by b.logon_time;

(4)杀掉对应进程

执行命令：alter system killsession'1025,41';其中1025为sid,41为serial#.

8.oracle数据库常见错误

   （1）查询数据库表时，千万不要带update命令，否则表会被锁。

（2）当系统运行被卡住了时，首先要排除是不是数据表被锁了。

（3）查询oracle数据库的路径：select * from dba_directories

9.oracle数据库表空间相关操作

（1） 查看数据库表空间

select * from dba_tablespace_usage_metrics

（2）  增加数据库的表空间

altertablespaceuseradd datafile 'D:\ORACLE\ORADATA\ORCL\USERS04.DBF'size5Gautoextend on next 1G maxsize 10G;

select * from dba_data_files

（3）  查看数据库表空间名

select tablespace_namefrom dba_tablespaces;

10.Oracle数据库增加表分析

进入SQL命令行窗口，运行命令：

  exec dbms_stats.gather_table_stats('ABSPROD','ACCT_PAYMENT_SCHEDULE');

11.oracle数据库本地安装后，自己可以连，其他人连接时提示无监听的错误解决方法：

12.oracle的merge into 应用:

MERGE INTO ACCT_PAYMENT_SCHEDULE T USING (select r.*,q.loanserialno from acct_payment_datar inner join acct_putout q on r.assetno = q.serialno )  T1

ON ( T.PERIODNO=T1.SEQID ANDT.OBJECTNO=T1.LOANSERIALNO )  WHENMATCHED THEN UPDATE SET

T.ACTUALPAYPRIAMT=T1.ACTUALPAYPRIAMT,T.ACTUALPAYINTAMT=T1.ACTUALPAYINTEAMT,T.ACTUALFINEAMT=T1.ACTUALFINEAMT,T.ACTUALPAYCOMPAMT=T1.ACTUALPAYCOMPAMT,

T.PREFINEAMT=T1.PREFINEAMT,T.DUEFINEAMT=T1.DUEFINEAMT,T.ACTUALPAYSUM=T1.ACTUALPAYSUM,T.DUEDAYS=T1.DUEDAYS,T.ACTUALPAYDATE=T1.PAYDATE,T.ACTSERVICECHARGE=

T1.ACTSERVICECHARGE,T.ACTPREMIUMCHARGE=T1.ACTPREMIUMCHARGE,T.REMARK=T1.REMARK

13. 清除oracle数据库缓存

         ALTER SYSTEM FLUSH SHARED_POOL;  

ALTER SYSTEM FLUSH BUFFER_CACHE ; 

ALTER SYSTEM FLUSH GLOBAL CONTEXT;

14.在oracle视图中传入参数

create or replace package O is

       function setPara(num varchar) return varchar;

       function get_param return varchar;

end O;

create or replace package body O is

  param_value varchar(100);

 function setPara(num varchar) return varchar is

  begin

    param_value := num;

    return num;

 end;

 function get_param return varchar is

    begin

      return param_value;

   end;

end O;

select * from P_VIEW_USER t where O.set_param(2) = 2

注意：只有当包头编辑成功后才能编辑包体.其中的函数名与过程名须和包头中的函数过程一样.