oracle问题希望大家帮忙啊

我刚在网上查了用 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=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]$

谢谢大家我已经基本会用 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，重新在本地建表更方便

调试易

oracle问题希望大家帮忙啊

解决方案 »

oracle问题 希望大家帮忙啊

解决方案 »

oracle问题希望大家帮忙啊