1.oracle数据库导入导出
(1)imp 用户名/密码@网络服务名 file=xxx.dmp full=y fromuser= touser= log=路径位置 ignore=y
exp username/password@localhost:1521/orcl file=D:\data.dmp LOG=D:\LOG.TXT
imp username/password@127.0.0.1:1521/orcl.27.239.4 file=E:\data.dmp fromuser=username1 touser=username2
(2)数据泵工具导入
drop user username cascade;
create user username identified by password;
grant connect,dba,resource to username;
grant unlimited tablespae to username;
注意:数据泵导入时,没有的用户会自动创建,但是用户密码需要设置
impdp username/password dumpfile=data.dmp directory=data_pump_dir logfile=log.log remap_schema=username1:username2;
(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 username/password directory=data_pump_dir dumpfile=data.dmp logfile=log.log version=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) 增加数据库的表空间
altertablespaceuser add datafile 'D:\ORACLE\ORADATA\ORCL\USERS04.DBF' size5G autoextend 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
注意:只有当包头编辑成功后才能编辑包体.其中的函数名与过程名须和包头中的函数过程一样.