oracle问题 希望大家帮忙啊 是这样 我链接的是一个局域网内的数据库服务器A 我想把A的数据弄到我的B电脑上 都装的是oracle 本人小白 希望大哥大姐们帮帮我 步骤 过程 最好详细一点 在下感激不尽 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 我刚在网上查了用 exp/imp 能不能有详细点的语句啊 -- 方法很多:-- 方法一:imp、exp导出、导入的方法!-- 方法二:rman备份、恢复的方法!-- 方法二:直接cop相关数据文件、控制文件、联机日志文件、初始参参数文件等! -- exp、imp实践代码(比较乱,你自己先参考、参考:exp system/manager@TEST file=d:\daochu.dmp owner=(system,sys)exp system/bee56915 file=/ora/u02/exp/hll011.dmp owner=hlldrop tablespace hll including contents and datafiles;CREATE TABLESPACE hllNOLOGGINGDATAFILE 'E:\oracle\product\10.2.0\oradata\sztyora\hll01.dbf'SIZE 1024MAUTOEXTEND ON NEXT 1024M; CREATE TEMPORARY TABLESPACE TEMPTBTEMPFILE 'E:\oracle\product\10.2.0\oradata\sztyora\temptb01.dbf'SIZE 1024M REUSE;ALTER TABLESPACE TEMPTBADD TEMPFILE'E:\oracle\product\10.2.0\oradata\sztyora\temptb02.dbf'SIZE 1024M REUSE;drop user hll cascade;CREATE USER hllIDENTIFIED BY szty2009hllDEFAULT TABLESPACE hllTEMPORARY TABLESPACE temptb;PROFILE low_limits;GRANT RESOURCE, CREATE SESSION TO HLL;GRANT CREATE USER,DROP USER,ALTER USER ,CREATE ANY VIEW,DROP ANY VIEW,EXP_FULL_DATABASE,IMP_FULL_DATABASE,CONNECT,RESOURCE,DBA,CREATE SESSION TO hll;imp hll/szty2009hll file=E:\exp\hll0419.dmp log=E:\exp\hll0419.log full=y;imp system/bee56915 file=D:\exp\ora\hll_exp_20100225.dmp log=D:\exp\ora\hll0226_implog.dmp full=y;imp system/wzs138322 file=/ora/u02/exp/hll_tb100525.dmp log=/ora/u02/exp/hll_tb100525.dmp.log fromuser=hll touser=hll grants=yimp system/wzs138322 file=/home/oracle/lftest_0702.dmp log=/home/oracle/lftest_0702.dmp.log fromuser=lftest touser=lftest2 grants=yimp system/wzs138322 file=/home/oracle/lftest_0706.dmp log=/home/oracle/lftest_0706.dmp.log fromuser=lftest touser=lftest3 grants=y----------------------------------------------------------------------------------------------------sys@SZTYORA> select file_name fn from sys.dba_data_files 2 ;FN----------------------------------------------------------------------------------------------------D:\ORACLE\PRODUCT\10.2.0\ORADATA\SZTYORA\USERS01.DBFD:\ORACLE\PRODUCT\10.2.0\ORADATA\SZTYORA\SYSAUX01.DBFD:\ORACLE\PRODUCT\10.2.0\ORADATA\SZTYORA\UNDOTBS01.DBFD:\ORACLE\PRODUCT\10.2.0\ORADATA\SZTYORA\SYSTEM01.DBFD:\ORACLE_DBFILE\MVNFORUM.DBFD:\ORACLE_DBFILE\HLL01.DBFsimp hll/szty2009hll file=D:\exp\hll0415.dmp log=D:\exp\hll0415.log full=y;导入服务器使用 AL32UTF8 字符集 (可能的字符集转换). 正在将 HLL 的对象导入到 SYSTEMdrop table "ABROADSMSMODATA";drop table "ABROADSMSMTDATA";drop table "ABROADSMSSYNDATA";drop table "CITY";drop table "CLIENTUPDATELOG";drop table "CQHKHLLONLINE";drop table "CQHKPAYDES";drop table "CQHKPAYDES2";drop table "CQHKPAYDOWN1";drop table "CQHKPAYMSG";drop table "CQHKPAYPID";drop table "CQHKSPREAD";drop table "DATA_LOGIN_LOG";drop table "FREEORDERUSER";drop table "GOODSDETAIL";drop table "HLLCLIENTLOGON";drop table "HLLCLIENTLOGON_FIRSTIN";drop table "HLLSPLOG";drop table "IMSI2MOBILE";drop table "IMSI2MOBILEPOP";drop table "INTERNALSMSMODATA";drop table "INTERNALSMSMTDATA";drop table "INTERNALSMSSYNDATA";drop table "INTERNALZHSMSMODATA";drop table "INTERNALZHSMSSYNDATA";drop table "MBLOGREVIEW_XXX";drop table "MOBILEAREAMAP";drop table "MOBILEFRENDS";shutdown immediate;已选择6行。imp system/manager file=tank log=tank fromuser=seapark TABLES=(a,b)exp hll/szty2009hll file=D:\exp\xyg\black_mobile.dmp tables=(black_mobile);imp system/wzs138322@sztyoralf7 file=D:\exp\xyg\black_mobile.dmp log=D:\exp\xyg\black_mobile_imp.log touser=hll TABLES=(black_mobile)imp system/wzs138322 file=/ora/u02/exp/black_mobile.dmp log=/ora/u02/exp/black_mobile_imp.log touser=hll fromuser=hll TABLES=BLACK_MOBILEexp userid=oss_ddms_hb/ossddms123hb@qh_yz file=ts_a_subbill_70.dmp indexes=n grants=n constraints=n direct=n tables=oss_sa_hb.ts_a_subbill QUERY=\"WHERE acycid =\'300\'\"exp hll/szty2009hll file=D:\exp\xyg\black_mobile.dmp tables=(emp,dept) QUERY=\"WHERE rownum <=100\"exp scott/bee56915 file=/home/oracle/scott.dmp tables=\(emp,dept\) QUERY=\"WHERE rownum <= 100 \"exp scott/bee56915 file=exp system/bee56915 file=/ora/u02/exp/hll011.dmp owner=hllexp system/bee56915 file=/u02/exp/scott.dmp TABLES=(emp,dept) QUERY="WHERE rownum <= 100"exp userid=oss_ddms_hb/ossddms123hb@qh_yz file=ts_a_subbill_70.dmp indexes=n grants=n constraints=n direct=n tables=oss_sa_hb.ts_a_subbill QUERY=\"WHERE acycid =\'300\'\"exp "scott/bee56915 file=/u02/exp/scott.dmp indexes=n grants=n constraints=n direct=n tables=(DEPT,EMP,BONUS,SALGRADE,T1,T2,T3,T4,T) query='WHERE deptno=30'"DEPT,EMP,BONUS,SALGRADE,T1,T2,T3,T4,T,---------------------------------------------------------------------------------exp导出时,加where条件:exp "scott/bee56915 file=/u02/exp/scott.dmp indexes=n grants=n constraints=n direct=n TABLES=(emp,bonus) QUERY='WHERE rownum<=100'"限制:1.The parameter QUERY cannot be specified for full, user, or tablespace mode exports. 参数query不能使用在完全导出,用户导出和表空间导出方式2.The parameter QUERY must be applicable to all specified tables. 参数query指定的内容必须对所有指定的表都有效3.The parameter QUERY cannot be specified in a direct path export (DIRECT=y) 参数query不能与direct路径导出同时使用4.The parameter QUERY cannot be specified for tables with inner nested tables. 参数query不能导出内嵌表5.You cannot determine from the contents of the export file whether the data is the result of a QUERY export. 用query参数导出的数据与同其他方式导出的数据是一样的,无法根据导出文件进行分辨-----------------------------------------------------------------------------------exp "scott/bee56915 file=/u02/exp/scott.dmp indexes=n grants=n constraints=n direct=n TABLES=(emp,bonus) QUERY='WHERE rownum<=100'"[oracle@sztyora exp]$ exp "scott/bee56915 file=/u02/exp/scott.dmp indexes=n grants=n constraints=n direct=n tables=(DEPT,EMP,BONUS,SALGRADE,T1,T2,T3,T4,T) query='WHERE rownum<=100'"Export: Release 10.2.0.4.0 - Production on Fri Jun 11 20:56:32 2010Copyright (c) 1982, 2007, Oracle. All rights reserved.Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsExport done in US7ASCII character set and AL16UTF16 NCHAR character setserver uses AL32UTF8 character set (possible charset conversion)Note: grants on tables/views/sequences/roles will not be exportedNote: indexes on tables will not be exportedNote: constraints on tables will not be exportedAbout to export specified tables via Conventional Path .... . exporting table DEPT 4 rows exportedEXP-00091: Exporting questionable statistics.. . exporting table EMP 14 rows exportedEXP-00091: Exporting questionable statistics.. . exporting table BONUS 0 rows exportedEXP-00091: Exporting questionable statistics.. . exporting table SALGRADE 5 rows exportedEXP-00091: Exporting questionable statistics.. . exporting table T1 1 rows exportedEXP-00091: Exporting questionable statistics.. . exporting table T2 1 rows exportedEXP-00091: Exporting questionable statistics.. . exporting table T3 1 rows exportedEXP-00091: Exporting questionable statistics.. . exporting table T4 1 rows exportedEXP-00091: Exporting questionable statistics.. . exporting table T 3 rows exportedEXP-00091: Exporting questionable statistics.Export terminated successfully with warnings.[oracle@sztyora exp]$ 谢谢大家 我已经基本会用 exp了 还有个问题 我想链接的数据库IP是 192.168.200.66 用户明KYY 我该怎么连接到他 然后导出呢??? 配置oracle\product\10.2.0\db_1\NETWORK\ADMIN\tnsnames.ora 文件你重名的SID = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = ip地址)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = 你要连接数据库的SID) ) )exp的时候@后面加上你重名的SID即可 HOME_ning(玩物丧志) 你还不如直接建立一个db_link,重新在本地建表更方便 如何在.NET内操作oracle, 一次性执行多句sql 求一条SQL语句??? 估计是ado 问题 请高人 指点 不胜感激!!! 急!存储过程中如何写更新其他用户表中的数据 .没有人回,闷!闷!闷!闷!闷!...... 表关联获取数据的问题! 数据迁移,出现数据库连接数量问题 怎样将一个小数用SQL格式化为百分数形式显示呢? 怎样用游标返回临时表 关于数据库备份的问题 帮我看看我的 SQL语句有什么问题 oracle10g存储过程的问题 oracle数据库中listener.ora sqlnet.ora tnsnames.ora的区别
imp、exp导出、导入的方法!-- 方法二:
rman备份、恢复的方法!-- 方法二:
直接cop相关数据文件、控制文件、联机日志文件、初始参参数文件等!
exp system/manager@TEST file=d:\daochu.dmp owner=(system,sys)exp system/bee56915 file=/ora/u02/exp/hll011.dmp owner=hll
drop tablespace hll including contents and datafiles;CREATE TABLESPACE hll
NOLOGGING
DATAFILE 'E:\oracle\product\10.2.0\oradata\sztyora\hll01.dbf'
SIZE 1024M
AUTOEXTEND ON NEXT 1024M; CREATE TEMPORARY TABLESPACE TEMPTB
TEMPFILE 'E:\oracle\product\10.2.0\oradata\sztyora\temptb01.dbf'
SIZE 1024M REUSE;ALTER TABLESPACE TEMPTB
ADD TEMPFILE
'E:\oracle\product\10.2.0\oradata\sztyora\temptb02.dbf'
SIZE 1024M REUSE;drop user hll cascade;CREATE USER hll
IDENTIFIED BY szty2009hll
DEFAULT TABLESPACE hll
TEMPORARY TABLESPACE temptb;
PROFILE low_limits;GRANT RESOURCE, CREATE SESSION TO HLL;GRANT CREATE USER,DROP USER,ALTER USER ,CREATE ANY VIEW,DROP ANY VIEW,EXP_FULL_DATABASE,IMP_FULL_DATABASE,CONNECT,RESOURCE,DBA,CREATE SESSION TO hll;imp hll/szty2009hll file=E:\exp\hll0419.dmp log=E:\exp\hll0419.log full=y;
imp system/bee56915 file=D:\exp\ora\hll_exp_20100225.dmp log=D:\exp\ora\hll0226_implog.dmp full=y;
imp system/wzs138322 file=/ora/u02/exp/hll_tb100525.dmp log=/ora/u02/exp/hll_tb100525.dmp.log fromuser=hll touser=hll grants=yimp system/wzs138322 file=/home/oracle/lftest_0702.dmp log=/home/oracle/lftest_0702.dmp.log fromuser=lftest touser=lftest2 grants=yimp system/wzs138322 file=/home/oracle/lftest_0706.dmp log=/home/oracle/lftest_0706.dmp.log fromuser=lftest touser=lftest3 grants=y
----------------------------------------------------------------------------------------------------
sys@SZTYORA> select file_name fn from sys.dba_data_files
2 ;FN
----------------------------------------------------------------------------------------------------
D:\ORACLE\PRODUCT\10.2.0\ORADATA\SZTYORA\USERS01.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\SZTYORA\SYSAUX01.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\SZTYORA\UNDOTBS01.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\SZTYORA\SYSTEM01.DBF
D:\ORACLE_DBFILE\MVNFORUM.DBF
D:\ORACLE_DBFILE\HLL01.DBFs
imp hll/szty2009hll file=D:\exp\hll0415.dmp log=D:\exp\hll0415.log full=y;导入服务器使用 AL32UTF8 字符集 (可能的字符集转换)
. 正在将 HLL 的对象导入到 SYSTEM
drop table "ABROADSMSMODATA";
drop table "ABROADSMSMTDATA";
drop table "ABROADSMSSYNDATA";
drop table "CITY";
drop table "CLIENTUPDATELOG";
drop table "CQHKHLLONLINE";
drop table "CQHKPAYDES";
drop table "CQHKPAYDES2";
drop table "CQHKPAYDOWN1";
drop table "CQHKPAYMSG";
drop table "CQHKPAYPID";
drop table "CQHKSPREAD";
drop table "DATA_LOGIN_LOG";
drop table "FREEORDERUSER";
drop table "GOODSDETAIL";
drop table "HLLCLIENTLOGON";
drop table "HLLCLIENTLOGON_FIRSTIN";
drop table "HLLSPLOG";
drop table "IMSI2MOBILE";
drop table "IMSI2MOBILEPOP";
drop table "INTERNALSMSMODATA";
drop table "INTERNALSMSMTDATA";
drop table "INTERNALSMSSYNDATA";
drop table "INTERNALZHSMSMODATA";
drop table "INTERNALZHSMSSYNDATA";
drop table "MBLOGREVIEW_XXX";
drop table "MOBILEAREAMAP";
drop table "MOBILEFRENDS";shutdown immediate;已选择6行。imp system/manager file=tank log=tank fromuser=seapark TABLES=(a,b)
exp hll/szty2009hll file=D:\exp\xyg\black_mobile.dmp tables=(black_mobile);
imp system/wzs138322@sztyoralf7 file=D:\exp\xyg\black_mobile.dmp log=D:\exp\xyg\black_mobile_imp.log touser=hll TABLES=(black_mobile)
imp system/wzs138322 file=/ora/u02/exp/black_mobile.dmp log=/ora/u02/exp/black_mobile_imp.log touser=hll fromuser=hll TABLES=BLACK_MOBILE
exp userid=oss_ddms_hb/ossddms123hb@qh_yz file=ts_a_subbill_70.dmp indexes=n grants=n constraints=n direct=n tables=oss_sa_hb.ts_a_subbill QUERY=\"WHERE acycid =\'300\'\"
exp hll/szty2009hll file=D:\exp\xyg\black_mobile.dmp tables=(emp,dept) QUERY=\"WHERE rownum <=100\"
exp scott/bee56915 file=/home/oracle/scott.dmp tables=\(emp,dept\) QUERY=\"WHERE rownum <= 100 \"
exp scott/bee56915 file=exp system/bee56915 file=/ora/u02/exp/hll011.dmp owner=hllexp system/bee56915 file=/u02/exp/scott.dmp TABLES=(emp,dept) QUERY="WHERE rownum <= 100"
exp userid=oss_ddms_hb/ossddms123hb@qh_yz file=ts_a_subbill_70.dmp indexes=n grants=n constraints=n direct=n
tables=oss_sa_hb.ts_a_subbill QUERY=\"WHERE acycid =\'300\'\"exp "scott/bee56915 file=/u02/exp/scott.dmp indexes=n grants=n constraints=n direct=n tables=(DEPT,EMP,BONUS,SALGRADE,T1,T2,T3,T4,T) query='WHERE deptno=30'"DEPT,
EMP,
BONUS,
SALGRADE,
T1,
T2,
T3,
T4,
T,
---------------------------------------------------------------------------------
exp导出时,加where条件:
exp "scott/bee56915 file=/u02/exp/scott.dmp indexes=n grants=n constraints=n direct=n TABLES=(emp,bonus) QUERY='WHERE rownum<=100'"限制:
1.The parameter QUERY cannot be specified for full, user, or tablespace mode exports.
参数query不能使用在完全导出,用户导出和表空间导出方式
2.The parameter QUERY must be applicable to all specified tables.
参数query指定的内容必须对所有指定的表都有效
3.The parameter QUERY cannot be specified in a direct path export (DIRECT=y)
参数query不能与direct路径导出同时使用
4.The parameter QUERY cannot be specified for tables with inner nested tables.
参数query不能导出内嵌表
5.You cannot determine from the contents of the export file whether the data is the result of a QUERY export.
用query参数导出的数据与同其他方式导出的数据是一样的,无法根据导出文件进行分辨-----------------------------------------------------------------------------------
exp "scott/bee56915 file=/u02/exp/scott.dmp indexes=n grants=n constraints=n direct=n TABLES=(emp,bonus) QUERY='WHERE rownum<=100'"
[oracle@sztyora exp]$ exp "scott/bee56915 file=/u02/exp/scott.dmp indexes=n grants=n constraints=n direct=n tables=(DEPT,EMP,BONUS,SALGRADE,T1,T2,T3,T4,T) query='WHERE rownum<=100'"Export: Release 10.2.0.4.0 - Production on Fri Jun 11 20:56:32 2010Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)
Note: grants on tables/views/sequences/roles will not be exported
Note: indexes on tables will not be exported
Note: constraints on tables will not be exportedAbout to export specified tables via Conventional Path ...
. . exporting table DEPT 4 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table EMP 14 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table BONUS 0 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table SALGRADE 5 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table T1 1 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table T2 1 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table T3 1 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table T4 1 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table T 3 rows exported
EXP-00091: Exporting questionable statistics.
Export terminated successfully with warnings.
[oracle@sztyora exp]$
我该怎么连接到他 然后导出呢???
你重名的SID =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ip地址)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = 你要连接数据库的SID)
)
)exp的时候@后面加上你重名的SID即可
HOME_ning(玩物丧志)