数据库exp和imp实验
实验目的：实验目的：在Linux操作系统中，将A用户的一张表通过EXP命令导出，再通过IMP命令导入到B用户 下。实验步骤：实验步骤：1、登录到scott用户sqlplus /nologconn / as sysdbaexp Test1/Test1passwd owner=Test1 file=D:filesTest1.dmp2、在A机器上查看用户默认表空间，以便导入时创建一样的表空间 SQL> select username,default_tablespace from dba_users where username = 'TEST1'USERNAME DEFAULT_TABLESPACE- -TEST1 CMIS3、查看用户使用的表空间 SQL> select DISTINCT owner ,tablespace_name from dba_extents where own er like 'TEST1'OWNER TABLESPACE_NAME- -TEST1 XSLTEST1 CMIS4、检查B机器中数据库的表空间，看是否存在CMIS，XSLSQL>select name from v$tablespace where name in ('XSL','CMIS'); 查找不到，说明没有这个两个表空间，需要创建。5、B机器数据库中没有CMIS，XSL表空间，创建它们SQL>create tablespace xsl logging datafile '/opt/oracle/product/10.2.0/oradata/xsl.dbf' size 15000M extent management local;SQL>create tablespace cmis logging datafile '/opt/oracle/product/10.2.0/oradata/cmis.dbf' size 37000M extent management local;6、在服务器B上查找用户是否已经存在SQL> select username from dba_users where username='TEST2'【一】用户不存在SQL>create user Test2 identified by Test2passwd default tablespace cmis temporary tablespace temp profile default;【二】用户存在SQL>drop user Test2 cascade; （删除用户及其拥有的所有对象）#此时如果这个用户在连接，drop会出错，必须先杀掉用户的session，然后再drop userSQL>SELECT 'alter system kill session '''|SID|','|SERIAL#|''' immediate;' FROM V$SESSION WHERE USERNAME='TEST2'#（如果用户正在连接，构建命令并杀掉）#（上面的语句是构建出杀掉Test2用户session的语句）相当于：'ALTERSYSTEMKILLSESSION'''|SID|','|SERIAL#|'''IMMEDIATE;'alter system kill session '129,3570' immediate;alter system kill session '131,2' immediate;alter system kill session '133,572' immediate;alter system kill session '135,1456' immediate;alter system kill session '136,487' immediate;alter system kill session '138,302' immediate;alter system kill session '139,366' immediate;再复制这些语句，粘贴到sqlplus中执行，来杀掉Test2的session。SQL>create user Test2 identified by Test2passwd default tablespace cmis temporary tablespace temp profile default;（创建用户）SQL>grant connect,resource to Test2; （授权）7、把文件从A机器上拷贝到B机器上。假如拷贝过来放到tmp目录下/tmp/Test1. dmp8、最后在、最后在B机器上按用户导入数据机器上按用户导入数据#一定注意执行imp时要退出sqlplus，在linux的shell下执行imporacletest2 $ imp Test2/Test2passwd fromuser =Test1 touser =test2 file=D:xsldb.DMP log =app/oracle/file/log/DEV_PMODOC.log;create user oracle default tablespace users identified by “oracle“;grant create table,connect to oracle;alter user oracle quota unlimited on users;create table test1(id number,name varchar2(10);insert into test1 values(1,'linux');commit;create user hr default tablespace users identified by hr;grant create table,connect to hr;alter user hr quota unlimited on users;exp oracle/oracledbsdv1 file=d:test1.dump tables=(test1)imp system/oracledbsdv1 file=d:test1.dump fromuser=oracle touser=jian
