ORACLE数据库逻辑备份、表空间创建及用户授权-mysql教程

IGNORE、GRANTS和INDEXES，其中IGNORE参数将忽略表的存在，继续导入，这个对于需要调整表的存储参数时很有用，我们可以先根据实际

1、Oracle数据库逻辑备份导入及导出：

2012年2月7日导出正式库数据
$exp userid=leopardsnow/leopardsnow@ywdb file=./leopardsnow20120207.dmp log=./leopardsnow20120207.log owner=leopardsnow grants=y

2012年1月13日导出正式库数据
$exp userid=leopardsnow/leopardsnow@ywdb file=./leopardsnow20120113.dmp log=./leopardsnow20120113.log owner=leopardsnow grants=y

2011年6月27日导出正式库数据
$exp userid=leopardsnow/leopardsnow@ywdb file=./leopardsnow20121215.dmp log=./leopardsnow20111215.log owner=leopardsnow grants=y

exp system/system88@ywdb file=./leopardsnow0627.dmp log=./leopardsnow0627.log owner=leopardsnow grants=y

2011年6月19日导出正式库数据
一种方式：
exp system/system88@ywdb file=./ywdb20110619.dmp log=./ywdb20110619.og full=y

二种方式：
exp system/system88@ywdb file=./leopardsnow.dmp log=./leopardsnow.log owner=leopardsnow grants=y
exp system/system88@ywdb file=./shark.dmp log=./shark.log owner=shark grants=y

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
2、创建表空间及用户授权

SQL> create tablespace shark datafile '/opt/oracle/oradata/YWDB/datafile/shark.dbf'
size 500M AutoExtend On Next 10M maxsize unlimited extent management local uniform size 128k
segment space management auto

SQL> create user shark identified by shark
default tablespace shark
temporary tablespace temp;

SQL> grant connect,resource to shark
---------------------------------------------------------------------------------------
SQL> create tablespace dttmp datafile '/opt/oracle/oradata/YWDB/datafile/dttmp.dbf'
size 6000M AutoExtend On Next 100M maxsize unlimited extent management local uniform size 128k
segment space management auto

SQL> create user leopardsnow identified by leopardsnow
default tablespace dttmp
temporary tablespace temp;

SQL> grant connect,resource to leopardsnow
-------------------------------------------------------------------------------

imp system/system88 file=./expfull20110619.dmp log=expfull20110620.log fromuser=(shark,leopardsnow) touser=(shark,leopardsnow)

3、更改用户密码：

SQL>alter user system identified by 要改的密码;

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－

imp aichannel/aichannel@hust full=y file= d:\data\newsmgnt.dmp ignore=y
1. 导入一个完整数据库
imp system/manager file=bible_db log=dible_db full=y ignore=y

$exp scott/tiger tables=(emp,dept) file=/directory/scott.dmp grants=y

2 将数据库中的表inner_notify、notify_staff_relat导出
exp aichannel/aichannel@TESTDB2 file= d:\data\newsmgnt.dmp tables=(inner_notify,notify_staff_relat)

2011年3月27日操作记录：
$exp userid=szfescotest5/szfescotest5@test file=./20110328baseadjust_log.dmp tables=baseadjust_log
$exp userid=szfescotest5/szfescotest5@test file=./20110328base_adjust_new.dmp tables=base_adjust_new
$exp userid=szfescotest5/szfescotest5@test file=./20110328employee_fee_period.dmp tables=employee_fee_period
$exp userid=szfescotest5/szfescotest5@test file=./20110328employee_fee_period_item.dmp tables=employee_fee_period_item

3 将数据库中system用户与sys用户的表导出
$exp system/manager@TEST file=d:\daochu.dmp owner=system grants=y

$exp userid=leopardsnow/leopardsnow@ywdb file=./20110329full.dmp log=./20110329full.log owner=leopardsnow grants=y

测试库用户szfescotest5备份：
$exp szfescotest5/szfescotest5@test file=./20110329full.dmp grants=y

Export: Release 10.2.0.1.0 - Production on Tue Mar 29 13:14:56 2011Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses ZHS16GBK character set (possible charset conversion)About to export specified users ...
. exporting pre-schema procedural objects and actions
EXP-00008: ORACLE error 4063 encountered
ORA-04063: package body "DMSYS.DBMS_DM_IMP_INTERNAL" has errors
ORA-06508: PL/SQL: could not find program unit being called: "DMSYS.DBMS_DM_IMP_INTERNAL"
ORA-06512: at "DMSYS.DBMS_DM_MODEL_EXP", line 303
ORA-06512: at line 1
EXP-00083: The previous problem occurred when calling DMSYS.DBMS_DM_MODEL_EXP.schema_info_exp
. exporting foreign function library names for user LEOPARDSNOW
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user LEOPARDSNOW
About to export LEOPARDSNOW's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export LEOPARDSNOW's tables via Conventional Path ...
. . exporting table AAA 14 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table AAAA 35 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table AA_COMPANY 200 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table AA_EMPLOYEE 16546 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table ACCESSORY_QUOTATION_EMPLOYEE 77582 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table ACCUMULATION_FUND 14305 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table ACCUMULATION_FUND_ADJUST 0 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table ACCUMULATION_FUND_MAKEUP 151 rows expo

EXP常用选项
1.FULL，这个用于导出整个数据库，在ROWS=N一起使用时，可以导出整个数据库的结构。例如：
exp userid=test/test file=./db_str.dmp log=./db_str.log full=y rows=n compress=y direct=y
2. OWNER和TABLE，这两个选项用于定义EXP的对象。OWNER定义导出指定用户的对象；TABLE指定EXP的table名称，例如：
exp userid=test/test file=./db_str.dmp log=./db_str.log owner=duanl
exp userid=test/test file=./db_str.dmp log=./db_str.log table=nc_data,fi_arap
3.BUFFER和FEEDBACK，，在导出比较多的数据时，我会考虑设置这两个参数。例如：
exp userid=test/test file=yw97_2003.dmp log=yw97_2003_3.log feedback=10000 buffer=100000000 tables=WO4,OK_YT
4.FILE和LOG，这两个参数分别指定备份的DMP名称和LOG名称，包括文件名和目录，例子见上面。
5.COMPRESS参数不压缩导出数据的内容。用来控制导出对象的storage语句如何产生。默认值为Y，使用默认值，对象的存储语句的init extent等于当前导出对象的extent的总和。推荐使用COMPRESS＝N。
6. FILESIZE该选项在8i中可用。如果导出的dmp文件过大时，最好使用FILESIZE参数，限制文件大小不要超过2G。如：
exp userid=duanl/duanl file=f1,f2,f3,f4,f5 filesize=2G owner=scott
这样将创建f1.dmp, f2.dmp等一系列文件，每个大小都为2G，如果导出的总量小于10G
EXP不必创建f5.bmp.

IMP常用选项
1、FROMUSER和TOUSER，使用它们实现将数据从一个SCHEMA中导入到另外一个SCHEMA中。例如：假设我们做exp时导出的为test的对象,现在我们想把对象导入用户：
imp userid=test1/test1 file=expdat.dmp fromuser=test1 touser=test1
2、IGNORE、GRANTS和INDEXES，其中IGNORE参数将忽略表的存在，继续导入，这个对于需要调整表的存储参数时很有用，我们可以先根据实际情况用合理的存储参数建好表，然后直接导入数据。而GRANTS和INDEXES则表示是否导入授权和索引，如果想使用新的存储参数重建索引，或者为了加快到入速度，我们可以考虑将INDEXES设为N，而GRANTS一般都是Y。例如：imp userid=test1/test1 file=expdat.dmp fromuser=test1 touser=test1 indexes=N