-
Oracle用imp和exp实现数据的导入和导出
2009-05-19 11:56:45
Oracle用imp和exp实现数据的导入和导出
使用方法如下:Imp username/password@connect_string param=value …exp username/password@connect_string param=value …一.常见问题问题1. SP2-0734:未知的命令开头 ”exp pafirc …”SP2-0734: unknown command beginning “exp pafirc...”解决办法:在windows 操作系统的SQLPLUS窗口中运行imp和exp命令,需要在命令前加$符号.在windows操作系统的cmd 命令行窗口中,直接使用imp和exp即可.在linux下, 以oracle用户登录,在终端中直接输入imp和exp就可以.以上运行的前提是,已经配置好了oracle的path环境变量($ORACLE_HOME/bin)如下图所示:问题2:如何把oracle表数据导出到excel中.解决办法:先创建ODBC到oracle的连接,然后,打开excel,数据-导入外部数据-新建数据查询就可以了.导出成功二.Imp和Exp命令详解:Exp导出模式:Full模式---导出整个数据库User模式---导出指定的用户Table模式—导出指定的表Tablespace模式—导出表空间Exp导出参数:OWNER指定要导出的用户列表FULL=y表示要导出整个数据库Tables指定要导出的表Tablepaces指定要导出的表空间ROWS=y表示导出表数据,否则只导出表的结构.HELP=Y表示要获取帮助Indexs指定是否导出索引Grants表示是否导出授权QUERY指定查询条件,允许用户导出表的一部分数据Triggers指定是否导出触发器1.Full模式:把数据库orcl完全导出到D:\pafirc.dmp中,exp system/pafirc@orcl file=e:\ pafirc.dmp full=y2.User模式:把system,pafirc用户的表导出到D:\pafirc.dmp中,exp pafirc/pafirc@orcl file=e:\ pafirc.dmp wner=(system, pafirc)3.Table模式:把表tab_proj导出到D:\pafirc.dmp中,exp pafirc/pafirc@orcl file=e:\ pafirc.dmp tables=(tab_proj)4. 备份表中的部分数据把表tab_proj中的字段budget小于500000数据导出到D:\pafirc.dmp中,exp pafirc/pafirc@orcl file=e:\ pafirc.dmp tables=(tab_proj) query=\" where budget<500000\"如下图:Imp命令数据的导入类似exp,只要把exp换成imp就可以,如果导入成功,会有下面的提示:三.创建用户及授权1.创建用户:CREATE USER PAFIRCIDENTIFIED BY PAFIRCDEFAULT TABLESPACE USERSTEMPORARY TABLESPACE TEMP;2.授予用户权限GRANT DBA TO PAFIRC WITH ADMIN OPTION;GRANT CONNECT TO PAFIRC WITH ADMIN OPTION;GRANT RESOURCE TO PAFIRC WITH ADMIN OPTION;GRANT DBA TO PAFIRC WITH ADMIN OPTION;ALTER USER PAFIRC DEFAULT ROLE DBA,CONNECT,RESOURCE; -
sqlserver identity
2009-05-15 19:28:49
in sqlserver,if you create a table with identity pk.
you can select by identity pkey as below:
select * from tableName where pkey=@@pkey
the identity pkey is use to the last record that you insert into db only.
-
Oracle exp/imp导出导入命令及数据库备份
2009-05-14 22:18:10
Oracle数据导入导出imp/exp就相当于oracle数据还原与备份。exp命令可以把数据从远程数据库服务器导出到本地的dmp文件,imp命令可以把dmp文件从本地导入到远处的数据库服务器中。执行环境:可以在SQLPLUS.EXE或者DOS(命令行)中执行, DOS中可以执行时由于 在oracle 8i 中 安装
目录 \ora81 \BIN被设置为全局路径, 该目录下有EXP.EXE与IMP.EXE文件被用来执行导入导出。 oracle用java编写,SQLPLUS.EXE、EXP.EXE、IMP.EXE这两个文件有可能是被包装后的类文件。 SQLPLUS.EXE调用EXP.EXE、IMP.EXE所包裹的类,完成导入导出功能 。导出工具 exp:
1. 它是操作系统下一个可执行的文件 存放目录/ORACLE_HOME/bin exp导出工具将数据库中数据备份压缩成一个二进制系统文件. 它有三种模式:
a. 用户模式:导出用户所有对象以及对象中的数据;
b. 表模式: 导出用户所有表或者指定的表;
c. 整个数据库: 导出数据库中所有对象。
2. 导出工具exp交互式命令行方式的使用的例子
$exp test/test123@appd
Enter array fetch buffer size: 4096 > 回车
Export file: expdat.dmp > m.dmp 生成导出的文件名
(1)E(ntire database), (2)U(sers), or (3)T(ables): (2)U > 3
Export table data (yes/no): yes > 回车wQeLin
Compress extents (yes/no): yes > 回车
Export done in ZHS16GBK character set and ZHS16GBK NCHAR character set
About to export specified tables via Conventional Path ...wQeLinux联盟
Table(T) or Partition(T:P) to be exported: (RETURN to quit) > cmamenu 要导出的表名
. . exporting table CMAMENU 4336 rows exported
Table(T) or Partition(T:P) to be exported: (RETURN to quit) >要导出的表名n
Table(T) or Partition(T:P) to be exported: (RETURN to quit) > 回车
Export terminated successfully without warnings.
3. 导出工具exp非交互式命令行方式的例子
1) 将数据库TEST完全导出,用户名system 密码manager 导出到D:\daochu.dmp中
exp system/manager@TEST file=d:\daochu.dmp full=y
2) 将数据库中system用户与sys用户的表导出
exp system/manager@TEST file=d:\daochu.dmp wner=(system,sys)
3) 将数据库中的表inner_notify、notify_staff_relat导出
exp aichannel/aichannel@TESTDB2 file= d:\data\newsmgnt.dmp tables=(inner_notify,notify_staff_relat)
4) 将数据库中的表table1中的字段filed1以"00"打头的数据导出
exp system/manager@TEST
上面是常用的导出,对于压缩,既用winzip把dmp文件可以很好的压缩。
也可以在上面命令后面 加上 compress=y 来实现。 file=d:\daochu.dmp tables=(table1) query=\" where filed1 like ''00%''\"导入工具 imp
1. 它是操作系统下一个可执行的文件 存放 目录 /ORACLE_HOME/bin
imp导入工具将EXP形成的二进制系统文件导入到数据库中.
它有三种模式:
a. 用户模式: 导出用户所有对象以及对象中的数据;
b. 表模式: 导出用户所有表或者指定的表;
c. 整个数据库: 导出数据库中所有对象。
2.导入工具imp交互式命令行方式的例子wQeLinux联盟
$ imp
Import: Release 8.1.6.0.0 - Production on 星期五 12月 7 17:01:08 2001
(c) Copyright 1999 Oracle Corporation. All rights reserved.
用户名: test
口令:****
连接到: Oracle8i Enterprise Edition Release 8.1.6.0.0 - 64bit Production
With the Partitioning option
JServer Release 8.1.6.0.0 - Production
导入文件: expdat.dmp> /tmp/m.dmp
输入插入缓冲区大小(最小为 8192 ) 30720>
经由常规路径导出由EXPORT:V08.01.06创建的文件
警告: 此对象由 TEST 导出, 而不是当前用户
已经完成ZHS16GBK字符集和ZHS16GBK NCHAR 字符集中的导入
只列出导入文件的内容(yes/no):no>
由于对象已存在, 忽略创建错误(yes/no):no> yes
导入权限(yes/no):yes>
导入表数据(yes/no):yes>
导入整个导出文件(yes/no):no> yes
. 正在将TEST的对象导入到 SCOTT
. . 正在导入表 "CMAMENU" 4336行被导入
成功终止导入,但出现警告。
3.导入工具imp非交互式命令行方式的例子1 )将D:\daochu.dmp 中的数据导入 TEST数据库中。
imp system/manager@TEST file=d:\daochu.dmp
imp aichannel/aichannel@HUST full=y file=file= d:\data\newsmgnt.dmp ignore=y
上面可能有点问题,因为有的表已经存在,然后它就报错,对该表就不进行导入。
在后面加上 ignore=y 就可以了。
2 )将d:\daochu.dmp中的表table1 导入
imp system/manager@TEST file=d:\daochu.dmp tables=(table1)基本上上面的导入导出够用了。不少情况要先是将表彻底删除,然后导入。
自动实现数据库的备份/恢复:
利用oracle提供的数据备份工具Exp进行数据的导出备份或者利用Oracle提供的RMAN进行数据库备份;利用Window22000 提供的定时任务,可以每天设定一个固定的时间,系统自动进行备份。具体实现过程:
1.创建批处理文件Ora_Backup.bat,在批处理文件中包含:exp username/password@oracleservice file=c:\backup.dmp wner=(lee)
附:(如何动态的产生备份文件的名字?如使用db20071128.dmp,如何在批处理命令中自动获得20071128时间?
解决方法:
1。在批处理中获得时间的方式为:%date%;
C:\Documents and Settings\Administrator>echo %date%
星期三 2007-11-282。时间可以获取其中的部分字符:%date:~4,10%
C:\Documents and Settings\Administrator>echo %date:~4,10%
2007-11-28
即:由第4位开始截取,长度为10。
C:\Documents and Settings\Administrator>echo %date:~0,-4%
星期三 2007-1
即:由第0开始截取,长度减掉后4位。3。如何得到字符串20071128,很简单:%date:~4,4%%date:~9,2%%date:~12,2%
C:\Documents and Settings\Administrator>echo %date:~4,4%%date:~9,2%%date:~12,2%
20071128)
使备份的文件名不重复的批处理:exp username/password@oracleservice file=c:\backup%date:~4,4%%date:~9,2%%date:~12,2%.dmp wner=(lee)
其中:username 指oracle数据库用户名;password指oracle数据库用户密码;oracleservice 指oracle 网络 服务名;lee为要导出的用户。2.在Windows2000下利用定时任务自动实现数据库的备份-创建任务
创建全备份任务:
创建任务计划,设定每周六早上8:00执行数据备份
打开控制面板->任务计划
点击 添加任务计划
点击 下一步
选择要执行的任务All_backup.bat,输入任务名称all_backup,选择每周,点击下一步。
选择起始时间8:00,在请选择下面日期中选择星期六,点击下一步。
数据用户名和密码,下一步。
点击完成,完成全备份任务的创建。数据恢复同理,创建批处理文件Orb_Backup.bat,在批处理文件中包含:
imp username/password@oracleservice file=backup.dmp 创建任务计划即可。但这样会出现很多的问题,一般不这样做。 -
SQL临时表
2009-04-10 17:45:43
1、MS SQLSERVERSQL Server 支持临时表。临时表就是那些名称以井号 (#) 开头的表。如果当用户断开连接时没有除去临时表,SQL Server 将自动除去临时表。临时表不存储在当前数据库内,而是存储在系统数据库 tempdb 内。
临时表有两种类型:
本地临时表:本地临时表的名称以单个数字符号 (#) 打头;它们仅对当前的用户连接是可见的;当用户从 Microsoft SQL Server 2000 实例断开连接时被删除。
全局临时表:全局临时表的名称以数学符号 (##) 打头,创建后对任何用户都是可见的。如果在创建全局临时表的连接断开前没有显式地除去这些表,那么只要所有其它任务停止引用它们,这些表即被除去。当创建全局临时表的连接断开后,新的任务不能再引用它们。当前的语句一执行完,任务与表之间的关联即被除去;因此通常情况下,只要创建全局临时表的连接断开,全局临时表即被除去。
例如,如果创建名为 employees 的表,则任何人只要在数据库中有使用该表的安全权限就可以使用该表,除非它已删除。如果创建名为 #employees 的本地临时表,只有您能对该表执行操作且在断开连接时该表删除。如果创建名为 ##employees 的全局临时表,数据表中的任何用户均可对该表执行操作。如果该表在您创建后没有其他用户使用,则当您断开连接时该表删除。如果该表在您创建后有其他用户使用,则 SQL Server在所有用户断开连接后删除该表。
现在,临时表的许多传统用途可由具有 table 数据类型的变量替换。2、ORACLE
Oracle支持临时表。临时表用来保存事务或会话期间的中间结果。在临时表中保存的数据只有对当前会话是可见的,任何会话都不能看到其他会话的数据,即使在当前会话COMMIT数据以后也是不可见的。多用户并行不是问题,一个会话从来不阻塞另一个会话使用临时表。即使锁定临时表,一个会话也不会阻塞其他会话使用临时表。临时表比正常表产生的REDO少得多,然而,由于临时表必须产生包含数据的UNDO信息,所以会产生一定数量的REDO日志。
临时表将从用户临时表空间的的目前日志中分配空间,或者如果从有定义权的程序中访问,将使用程序所有者的临时表空间。全局临时表实际上只是表本身的模板。创建临时表的行为不包括存储空间的分配,也不包括INITIAL的分配。因此,在运行时当一个会话首先将数据放到临时表中时,这时将创建这个会话的临时段。由于每个会话获取自己的临时段,每个用户可能在不同的表空间中为临时表分配空间。USER1的default临时表空间为TEMP1,他的临时表将从TEMP1中分配空间,USER2的default临时表空间为TEMP2,他的临时表将从TEMP2中分配空间。
临时表在每个数据库中只需创建一次,不必在每个存储过程中创建。临时表总是存在的,除非手动的删除他。临时表作为对象存在数据字典中,并且总是保持为空,直到有会话在其中放入数据。Oracle允许创建基于临时表的视图和存储过程。
临时表可以是以会话为基础的,也可以是以事务为基础的。ON COMMIT PRESERVE ROWS子句使临时表成为基于会话的模式。行将留在此表中,直到会话断开或通过DELETE或TRUNCATE从物理上删除这些行。ON COMMIT DELETE ROWS子句使临时表成为基于事务的模式。当会话提交后,行消失。这个临时表的自动清除过程不会有额外的开销。
在oracle中,应用程序需要的临时表应该在程序安装时创建,而不是在程序运行时创建。(这是与ms sqlserver或sybase的使用的不同)
在任何数据库中,临时表的一个缺点是:事实上优化器在临时表中没有真正的统计功能。然而,在oracle中,一系列较好的统计猜测可以通过DBMS_STATS包在临时表中设置。3、DB2
可使用 DECLARE GLOBAL TEMPORARY TABLE 语句来定义临时表。DB2的临时表是基于会话的,且在会话之间是隔离的。当会话结束时,临时表的数据被删除,临时表被隐式卸下。对临时表的定义不会在SYSCAT.TABLES中出现
下面是定义临时表的一个示例:
DECLARE GLOBAL TEMPORARY TABLE gbl_temp
LIKE empltabl
ON COMMIT DELETE ROWS
NOT LOGGED
IN usr_tbsp
此语句创建一个名为 gbl_temp 的用户临时表。定义此用户临时表 所使用的列的名称和说明与 empltabl 的列的名称和说明完全相同。隐式定义 只包括列名、数据类型、可为空特性和列缺省值属性。未定义所有其他列属性,包括唯一约束、外部关键字约束、触发器和索引。执行 COMMIT 操作时, 若未对该表打开 WITH HOLD 游标,则该表中的所有数据都被删除。不记录 对用户临时表所作的更改。用户临时表被放在指定的用户临时表空间中。此表空间必须存在,否则此表的声明将失败。
户定义临时表不支持:
• LOB 类型的列(或基于 LOB 的单值类型列)
• 用户定义类型列
• LONG VARCHAR 列
• DATALINK 列
其创建方法:
create table TempTableName,
或
select [字段1,字段2,...,] into TempTableName from table ,如上所说.而后便可像使用常规表一样使用它们.
临时表其实是放在数据库tempdb里的一个用户表
分两种:
一种是以#(局部)或##(全局)开头的表,这种表在会话期间存,会话结束则自动删除;
另一种,如果创建时不以#或##开头,而用tempdb.TempTable来命名它,则该表可在数据库重启前一直存在.
以上两种都可手动用
drop table TempTableName 来删除.
请参考---动态sql语句基本语法
1 :普通SQL语句可以用Exec执行
eg: Select * from tableName
Exec('select * from tableName')
Exec sp_executesql N'select * from tableName' -- 请注意字符串前一定要加N
2:字段名,表名,数据库名之类作为变量时,必须用动态SQL
eg:
declare @fname varchar(20)
set @fname = 'FiledName'
Select @fname from tableName -- 错误,不会提示错误,但结果为固定值FiledName,并非所要。
Exec('select ' + @fname + ' from tableName') -- 请注意 加号前后的 单引号的边上加空格
当然将字符串改成变量的形式也可
declare @fname varchar(20)
set @fname = 'FiledName' --设置字段名
declare @s varchar(1000)
set @s = 'select ' + @fname + ' from tableName'
Exec(@s) -- 成功
exec sp_executesql @s -- 此句会报错
declare @s Nvarchar(1000) -- 注意此处改为nvarchar(1000)
set @s = 'select ' + @fname + ' from tableName'
Exec(@s) -- 成功
exec sp_executesql @s -- 此句正确
3. 输出参数
declare @num int,
@sqls nvarchar(4000)
set @sqls='select count(*) from tableName'
exec(@sqls)
--如何将exec执行结果放入变量中?
declare @num int,
@sqls nvarchar(4000)
set @sqls='select @a=count(*) from tableName '
exec sp_executesql @sqls,N'@a int output',@num output
select @num
DECLARE @fname varchar(20),
@dyni_SQL varchar(8000),
@pcur_day varchar(20)
create table #tp_res(
f1 smalldatetime NULL,
f2 smallint NULL,
f3 float NULL
)
set @fname='f1'
set @dyni_SQL='insert into #tp_res' + ' ( ' + @fname + ') values (''' + @pcur_day + ''')'
exec (@dyni_SQL)
select * from #tp_res
drop table #tp_res
--需要注意,如果字段是数值型,不要用单引号,其他如字符型和日期型都要单引号即:
set @dyni_SQL='insert into #tp_res' + ' ( ' + @fname + ') values (' + cast(@pcur_day as varchar(50)) + ')'
示例:
select * into #tb_demo from employee
select * into #tb_demo1 from jobs
select * from #tb_demo left join #tb_demo1 on #tb_demo.job_id=#tb_demo1.job_id
drop table #tb_demo
drop table #tb_demo1
标题搜索
我的存档
数据统计
- 访问量: 26874
- 日志数: 47
- 建立时间: 2008-08-06
- 更新时间: 2012-05-09