数据库的导出和导入是很常见的,下面介绍下在B server上搭建与A server相同的数据库(包含数据).
db_username: testuser
db_password: testpassword
1.导出A server数据库
(假设dumpdir是/data/oracle/oradata/,dmp文件存放位置)
oracle@Ahost:~>expdp testuser/testpassword direcroty=dumpdir dumpfile=db_20070101.dmp table=table_name
2.讲dump文件传输到B server上
oracle@Bhost:~>scp root@AhostIP:/data/oracle/oradata/db_20070101.dmp /data/oracle/ oradata/db_20070101.dmp
3.导入B server
a.创建数据库所使用的表空间
SQL>show user;
USER IS "SYS"
SQL>CREATE TABLESPACE db_space DATAFILE '/data/oracle/oradta/db/db_space.dbf' SIZE 1g AUTOEXTEND ON;
b.创建用户(和A server使用相同用户)
SQL>CREATE USER testuser IDENTIFIED BY testpassword
2>DEFAULT TABLESPACE db_space
3>QUOTA UNLIMITED ON db_space;
如果创建用户时没有指定配额,可使用下面命令修改
SQL>ALTER user testuser
2> QUOTA UNLIMITED ON db_space;
c.创建dump目录
SQL>CREATE DIRECTORY dumpdir AS '/data/oracle/oradata';
d.为用户分配权限
SQL>GRANT read,write ON DIRECTORY dumpdir TO testuser;
SQL>GRANT connect,resource TO testuser;
e.导入A server数据库到B server
oracle@Bhost:~>impdp testuser/testpassword directory=dumpdir dumpfile=db_20070101.dmp
不同用户
testbbb/bbbpassword
oracle@Bhost:~>impdp testbbb/bbbpassword directory=dumpdir dumpfile=db_20070101.dmp remap_userid=testuser:testbbb
[@more@]很多时候需要重新导库,比如干掉当前库,导入最新的,再如一次导入完全成功.
1.检查与当前库的会话,避免不必要的损失
SQL>show user;
USER IS "SYS"
SQL>SELECT sid,serial# FROM v$session WHERE username='TESTUSER';
2.通知相关人员,确认可以已关闭相关与数据库的连接,删除会话
SQL>ALTER system KILL SESSION 'sidvalule1,serialvalue1';
SQL>ALTER system KILL SESSION 'sidvalule2,serialvalue2';
SQL>ALTER system KILL SESSION 'sidvalule3,serialvalue3';
3.删除用户
SQL>DROP USER testuser cascade;
下面的工作同正文.
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/9183234/viewspace-911394/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/9183234/viewspace-911394/