案例1： 误删除user及所有的对象，恢复

1）创建用户，并分配权限

06:14:01 SQL> create user scott identified by tiger default tablespace users ;

User created.

06:15:55 SQL> grant connect ,resource to scott;

Grant succeeded.

06:16:06 SQL>


2）导入备份

[oracle@work dat]$ imp system/oracle file=scott.dmp ignore=y fromuser=scott touser=scott;

Import: Release 10.2.0.1.0 - Production on Fri Aug 19 06:16:36 2011

Copyright （c） 1982, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path

Warning: the objects were exported by SCOTT, not by you

import done in US7ASCII character set and AL16UTF16 NCHAR character set

import server uses WE8ISO8859P1 character set （possible charset conversion）

. importing SCOTT's objects into SCOTT

. . importing table                        "BONUS"          0 rows imported

. . importing table                         "DEPT"          4 rows imported

. . importing table                          "EMP"         14 rows imported

. . importing table                         "EMP1"        512 rows imported

. . importing table                         "LX01"          9 rows imported

. . importing table                     "SALGRADE"          5 rows imported

. . importing table                         "TEST"         10 rows imported

About to enable constraints…

Import terminated successfully without warnings.


3）验证

06:17:11 SQL> conn scott/tiger

Connected.

06:17:16 SQL>

06:17:16 SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID

------------------------------ ------- ----------

BONUS                          TABLE

DEPT                           TABLE

EMP                            TABLE

EMP1                           TABLE

LX01                           TABLE

SALGRADE                       TABLE

SYS_TEMP_FBT                   TABLE

TEST                           TABLE

8 rows selected.



案例2：利用exp/imp 传输表空间（要保证两个库的字符集一致）

前提条件：如果两个库在同一个操作系统，就要保证两个库的字符集一致；

如果两个库不在同一个操作系统，就要检查对应操作系统的参数是否一致。


1、建立表空间

17:08:41 SQL> col file_name for a50

17:08:48 SQL> select file_id,file_name,tablespace_name from dba_data_files

17:08:57   2  ;

FILE_ID FILE_NAME                                          TABLESPACE_NAME

---------- -------------------------------------------------- ------------------------------

4 /u01/app/oracle/oradata/ocmtest1/users01.dbf       USERS

3 /u01/app/oracle/oradata/ocmtest1/sysaux01.dbf      SYSAUX

2 /u01/app/oracle/oradata/ocmtest1/undotbs01.dbf     UNDOTBS1

1 /u01/app/oracle/oradata/ocmtest1/system01.dbf      SYSTEM

5 /u01/app/oracle/oradata/ocmtest1/example01.dbf     EXAMPLE

17:08:58 SQL> create tablespace test

17:09:05   2   datafile '/u01/app/oracle/oradata/ocmtest1/test01.dbf' size 100m;

Tablespace created.

17:09:18 SQL> conn scott/tiger

Connected.

17:09:23 SQL>

17:09:29 SQL> create table t01 （id int） tablespace test;

Table created.

17:09:33 SQL> insert into t01 values （1）；

1 row created.

17:09:38 SQL> insert into t01 values （2）；

1 row created.

17:09:39 SQL> insert into t01 values （3）；

1 row created.

17:09:40 SQL> commit;

Commit complete.

17:09:41 SQL> conn /as sysdba

Connected.

17:10:12 SQL>

17:10:12 SQL> alter tablespace test read only;

Tablespace altered.


2、查看表空间是否自包含【看表空间上的对象是不是都属于同一个表空间】

17:10:20 SQL> exec sys.dbms_tts.transport_set_check（'test',true）；

PL/SQL procedure successfully completed.

17:11:37 SQL> select * from sys.transport_set_violations; ——结果为空则为自包含，反之为非自包含

no rows selected

17:11:43 SQL>

——将传输的tablespace 设置成 read  only 模式

15:37:37 SQL> alter tablespace test read only;

Tablespace altered.


3、导出可传输表空间（必须以sysdba权限）

[oracle@ocmtest1 ~]$ exp userid=\'sys/oracle as sysdba\' file=test.dmp log=test.log transport_tablespace=y tablespaces=test

Export: Release 10.2.0.1.0 - Production on Mon Aug 15 17:13:24 2011

Copyright （c） 1982, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

Export done in US7ASCII character set and AL16UTF16 NCHAR character set

server uses WE8ISO8859P1 character set （possible charset conversion）

Note: table data （rows） will not be exported

About to export transportable tablespace metadata…

For tablespace TEST …

. exporting cluster definitions

. exporting table definitions

. . exporting table                            T01

. exporting referential integrity constraints

. exporting triggers

. end transportable tablespace metadata export——只传输数据结构

Export terminated successfully without warnings.

[oracle@ocmtest1 ~]$


4、启动另外一个数据库

[oracle@ocmtest1 ~]$ export ORACLE_SID=catdb

[oracle@ocmtest1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Aug 15 17:15:25 2011

Copyright （c） 1982, 2005, Oracle.  All rights reserved.

Connected to an idle instance.

17:15:25 SQL>

17:15:25 SQL> startup

ORACLE instance started.

Total System Global Area  167772160 bytes

Fixed Size                  1218316 bytes

Variable Size              75499764 bytes

Database Buffers           88080384 bytes

Redo Buffers                2973696 bytes

Database mounted.

Database opened.

17:15:41 SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME

------------------------------

SYSTEM

UNDOTBS1

SYSAUX

TEMP

USERS

EXAMPLE

CATTBS

7 rows selected.


5、在目标数据库下做imp操作，导入表空间

[oracle@ocmtest1 ~]$ cp /u01/app/oracle/oradata/ocmtest1/test01.dbf /u01/app/oracle/oradata/catdb/

——将表空间数据文件从源数据库拷贝到目标数据库下

[oracle@ocmtest1 ~]$ imp userid=\'sys/oracle as sysdba\' tablespaces=test     file=test.dmp transport_tablespace=y

datafiles=/u01/app/oracle/oradata/catdb/test01.dbf

Import: Release 10.2.0.1.0 - Production on Mon Aug 15 17:17:23 2011

Copyright （c） 1982, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path

About to import transportable tablespace（s） metadata…

import done in US7ASCII character set and AL16UTF16 NCHAR character set

import server uses WE8ISO8859P1 character set （possible charset conversion）

. importing SYS's objects into SYS

. importing SYS's objects into SYS

. importing SCOTT's objects into SCOTT

. . importing table                          "T01"

. importing SYS's objects into SYS

Import terminated successfully without warnings.


6、验证

[oracle@ocmtest1 ~]$

17:16:53 SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME

------------------------------

SYSTEM

UNDOTBS1

SYSAUX

TEMP

USERS

EXAMPLE

CATTBS

TEST

8 rows selected.

17:17:28 SQL> select * from scott.t01;

ID

----------

1

2

3

17:17:33 SQL>

17:28:03 SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME                STATUS

------------------------------ ---------

SYSTEM                         ONLINE

UNDOTBS1                       ONLINE

SYSAUX                         ONLINE

TEMP                           ONLINE

USERS                          ONLINE

EXAMPLE                        ONLINE

CATTBS                         ONLINE

TEST                           READ ONLY

8 rows selected.

17:28:12 SQL> alter tablespace test read write;

Tablespace altered.



oracle视频教程请关注:http://u.youku.com/user_video/id_UMzAzMjkxMjE2.html