oracle日常使用小结

一、oracle表的导入导出

1、通过exp/imp
  将数据库TEST完全导出,用户名system 密码manager 的表table1,table2导出

    exp system/manager@TEST file=c:/test.dmp tables=(table1,table2)

  将c:/test.dmp中的表table1 导入

    imp system/manager@TEST  file=c:/test.dmp  tables=(table1)
 
  表的条件导出

  将数据库中的表table1中的字段filed1以"00"打头的数据导出

  exp system/manager@TEST file=c:/test.dmp tables=(table1) query=/" where filed1 like '00%'/"

cmd中执行成功的情况下会出现一下字段
“没有警告的情况下成功终止导出/导入” 这说明表已经成功导出/导入

2、通过SQL Plus生成txt导入导出

导出txt文件,如下输入 
 
  spool   c:/tbdata.txt  
  set   heading   off  
  set   feedback   off  
  select   *   from   tbname;  
  spool   off;

不过要注意所有的操作步骤都会出现在文本中

导入txt文件，注意上面导出的txt是不能够直接导入的，要先将多余的字段删除

第一步：把文本格式的数据文件放入C盘。如,test.txt
第二步：建立控制文件append.ctl。（名字可以随便取,放C盘下）

append.ctl的内容如下：
　　load data　　　　　　　　　　   --1、控制文件标识
  　infile   'test.txt'　　　　　　 --2、要输入的数据文件名为test.txt
  　append into table CTXSYS.test　　　--3、向CTXSYS表空间中的表test中追加记录
  　fields terminated by X'09'  TRAILING NULLCOLS--4、字段终止于X'09'，是一个制表符  TRAILING NULLCOLS将所有不在纪录                                                    中的指定位置的列当作空值
    (name,age,btime)　　   -----定义列对应顺序

   (name,age,btime)也可以替换为(name CHAR(500),age number,btime DATE "YYYY-MM-DD HH24:MI:SS")对数据的格式进行说明

  其中append为数据装载方式，还有其他选项：
   a、insert，为缺省方式，在数据装载开始时要求表为空
   b、append，在表中追加新记录
   c、replace，删除旧记录，替换成新装载的记录
   d、truncate，同上

第三步：在命令提示符下输入命令。
 C:/>sqlldr userid=username/password

 control=c:/append.ctl        数据库中用名的用户名和密码

二、oracle库的导入导出
 
   通过exp/imp

1、整个库的导入导出 

  将数据库TEST完全导出,用户名system 密码manager 导出到c:/test.dmp中

    exp system/manager@TEST file=c:/test.dmp full=y

  将c:/test.dmp 中的数据导入 TEST数据库中。

    imp system/manager@TEST  file=c:/test.dmp

  上面可能会报错，如果有些表已经存在，它就报错，对该表就不进行导入只要在后面加上 ignore=y 就可以了。

2、将用户scott的表导出

  将数据库中scott用户的表导出

   exp scott/tiger@TEST file=c:/test.dmp owner=(scott)

三、实际应用中的总结

1、删除重复记录
 
  表table1,列c1,c2,c3

  首先要找到的重复记录

  第一种情况 所有字段都重合的小量数据

 SQL> create table table_temp1 as select distinct * from table1;--找出所有唯一的列放入临时表，然后把原来的表清空 ，
                                                                  把临时表数据导入就可以了       
 
 
 
  第二种情况  列c1,c2,c3有重复大数据量的情况下执行
  
   SQL> delete student temp where (c1,c2,c3) in(select c1,c2,c3 from table1 group by c1,c2,c3 having
  2   count(*)>1) and rowid not in(select min(rowid) from table1  group by c1,c2,c3 having
  3  count(*)>1);

在使用的过程中还发现了两个有趣的现象

在“表编辑器”中group  BY语句会报错，在sql plus里面执行就没问题

还有in语句，在“表编辑器”中检索出的数据为空，同样在sql plus里面执行就没可以找到相应的记录。

2、全角半角转换
   To_single_byte( ) ---转成半角
   To_multi_byte( ) -----转成全角

   select To_single_byte(c1) from table1; --c1列的数据半角方式显示