oracle导入导出个人实践

导出前数据库状况：
------------------------------------------------------------------------------------------------------------------
1、表空间：study   大小 200M,有两个文件
/oracle/oradata/study/studydata1.bdf
/oracle/oradata/study/studydata2.bdf

2、用户 test/test
权限：
SQL>select * from user_sys_privs;
TEST                           CREATE TABLE                             NO
TEST                           CREATE SESSION                           NO
TEST                           UNLIMITED TABLESPACE                     NO
或者
SQL> select * from session_privs;

PRIVILEGE
----------------------------------------
CREATE SESSION
UNLIMITED TABLESPACE
CREATE TABLE

角色：
SQL> select * from user_role_privs;

USERNAME                       GRANTED_ROLE                   ADM DEF OS_
------------------------------ ------------------------------ --- --- ---
TEST                           CONNECT                        NO  YES NO

默认表空间：study
SQL>select username,default_tablespace from user_users;  //dba_users 适用于DBA 用户，查看任何用户的默认表空间                            
USERNAME                       DEFAULT_TABLESPACE
------------------------------ ------------------------------
TEST                           STUDY

所拥有的对象
SQL> select * from user_objects;  //dba_objects 适用于dba 用户查询
OBJECT_NAME              OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE         CREATED   LAST_DDL_ TIMESTAMP           STATUS  T G S
---------------------------------------------------------------------------------------------------------
STUDENT                    51255           51255 TABLE               28-NOV-09 28-NOV-09 2009-11-28:06:36:20 VALID   N N N
SYS_C005250                51256           51256 INDEX               28-NOV-09 28-NOV-09 2009-11-28:06:36:21 VALID   N Y N
TEST                       51257           51257 TABLE               28-NOV-09 28-NOV-09 2009-11-28:06:36:22 VALID   N N N
TESTTABLE                  51258           51258 TABLE               28-NOV-09 28-NOV-09 2009-11-28:06:36:22 VALID   N N N

--------------------------------------------------------------------------------------------------------------
对象在表空间中的分布：（方便确认）
SQL> select table_name,tablespace_name from user_tables;  

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
STUDENT                        USERS
TEST                           STUDY
TESTTABLE                      STUDY

SQL> select index_name,table_name,table_owner from user_indexes;   //dba_indexes 使用于dba 用户查看所有用户

INDEX_NAME                     TABLE_NAME                     TABLE_OWNER
------------------------------ ------------------------------ ------------------------------
SYS_C005250                    STUDENT                        TE


数据库导出实例
-----------------------------------------------------------------------------------------------------------------
1、全库导出导入实例
SQL> create user backup identified by backup;  
SQL>GRANT CREATE USER,DROP USER,ALTER USER ,CREATE ANY VIEW ,
    DROP ANY VIEW,EXP_FULL_DATABASE,IMP_FULL_DATABASE,
    DBA,CONNECT,RESOURCE,CREATE SESSION  TO backup;
$  exp backup/backup file=backup.dump full=yes  compress=n
$  imp backup/backup file=backup.dump full=yes  commit=yes ignore=yes     //注意原来的表空间文件已经存在时，要删除，否则创建表空间失败，致使导入失败

$  exp system/oracle file=full.dump full=yes  compress=n                       
$  imp system/oracle file=full.dump full=yes   ignore=yes                 //注意原来的表空间文件已经存在时，要删除，否则创建表空间失败，致使导入失败


$  exp \'sys/oracle as sysdba\' file=fullsys.dump full=yes  compress=n
$  imp \'sys/oracle as sysdba\' file=fullsys.dump full=yes  ignore=yes    //注意原来的表空间文件已经存在时，要删除，否则创建表空间失败，致使导入失败

数据库破坏处理
SQL> drop user test cascade;
SQL> drop tablespace study including contents;
#rm -f  /oracle/oradata/study/studydata*  //删除原来表空间的数据文件。


2、按用户导出导入实
-----------------------------------------------------------------------------------------------------------------
$  exp test/test file=test.dump compress=no
$  imp test/test file=test.dump ignore=yes

导入数据库前。新建test 用户
SQL > create user test identified by test;
SQL > grant create session,create table,unlimited tablespace to test;  //确保该用户有连接权限，创建表格权限，对表空间的写权限。

数据库破坏处理：
SQL> drop user test cascade;


3、按表空间导出导入实例
----------------------------------------------------------------------------------------------------------------
1、表空间在数据库之间移动：
SQL> alter tablespace study read only;
   $ exp \'sys/oracle as sysdba\'  transport_tablespace=y tablespaces=study  file=study.dmp
 
 进入目标机器上，创建对应表空间的用户，不必指定缺省表空间。
把study.dmp，及该表空间的数据文件/studydata1.bdf，/studydata2.bdf  --  ftp 或cp 到别的机器。

SQL> create user test identified by test;
$ imp file=study.dmp userid=\'sys/oracle as sysdba\' transport_tablespace=y datafiles='/oracle/oradata/study/studydata1.bdf','/oracle/oradata/study/studydata1.bdf' ignore=yes

数据库破坏处理：
SQL> drop tablespace study including contents;


4、数据库文件损坏处理实例
----------------------------------------------------------------------------------------------------------------
误删除一下文件：
#rm -f  /oracle/oradata/study/studydata*  //删除原来表空间的数据文件。
SQL > shutdown immediate;
SQL > startup mount;
SQL > alter database datafile '/oracle/oradata/study/studydata1.bdf' offline drop;
SQL > alter database datafile '/oracle/oradata/study/studydata2.bdf' offline drop;
SQL> alter database open;
Database altered.

SQL> drop tablespace study;
Tablespace dropped.

进行全库导入恢复处理：
$ imp system/oracle file=full.dump full=yes   ignore=yes                 //注意原来的表空间文件已经存在时，要删除，否则创建表空间失败，致使导入失败