oracle数据库之数据库迁库操作

1、查询用户使用的表空间及临时表空间

--tab=使用的表空间

select a.tablespace_name from dba_segments a where a.owner='KOBE' group by a.tablespace_name;

--tab=使用的临时表空间

select username,default_tablespace,temporary_tablespace from dba_users a where a.username='KOBE';

2、目标库创建同样的用户和表空间

--创建临时表空间

create temporary tablespace test_temp

tempfile 'E:\oracle\product\10.2.0\oradata\testserver\test_temp01.dbf'

size 32m

autoextend on

next 32m maxsize 20480m

extent management local;

--创建数据表空间

create tablespace test_data

nologging /*logging 是对象的属性，创建数据库对象时，oracle 将日志信息记录到联机重做日志文件中。代表空间类型为永久型 */

datafile 'E:\oracle\product\10.2.0\oradata\testserver\test_data01.dbf',

'E:\oracle\product\10.2.0\oradata\testserver\test_data02.dbf',

'E:\oracle\product\10.2.0\oradata\testserver\test_data03.dbf'

size 32m

autoextend on /*autoextend on 表空间大小不够用时自动扩展*/

next 32m maxsize 2048m|unlimited /*next 50m 自动扩展增量为50MB */

extent management local; /*extent management local 代表管理方式为本地*/

在表空间中增加数据文件：

alter tablespace test_data

add datafile 'E:\oracle\product\10.2.0\oradata\testserver\test_data04.dbf' size 64m autoextend on next 32m maxsize unlimited,

'E:\oracle\product\10.2.0\oradata\testserver\test_data05.dbf' size 64m autoextend on next 32m maxsize unlimited,

'E:\oracle\product\10.2.0\oradata\testserver\test_data06.dbf' size 64m autoextend on next 32m maxsize unlimited

extent management local;

--创建用户

create user kobe identified by kobe123

default tablespace test_data

temporary tablespace test_temp;

--给用户授予权限

grant connect,resource,dba to testserver_user; (dba：指定所有权限)

create user kobe  --用户名必须，不区分大小写
identified by kobe123 --指定口令，除口令验证之外，还会有其他身份验证方式，如我们常用的操作系统身份验证：conn / as sysdba; 或者通过外部口令文件验证，或是委托给外部身份验证
default tablespace test_data --为用户指定一个表空间，非必需，如果不指定，则为数据库默认表空间
temporary tablespace test_temp --为用户指定临时表空间，非必需
quota 100m on users --指定配额，意味着该账户可在users表空间上使用的空间为100m
profile my_profile --为该账户指定一个配置文件，配置文件的作用会在后面提到
password expire ;--强制用户立即更改口令

3、导出用户数据。

注：1、可单独导出大表数据或者表中包含大表字段的。

select * from user_segments where bytes>=500*1024*1024;/*查找大于500M的对象*/

2、然后再删除大表数据。

3、然后再导出全部数据。

在新库建立好owner和表空间，停老库的应用，在老库执行：

$ exp user/pwd owner=XXX file=exp_xxx.dmp log=exp_xxx.log buffer=6000000;

4、导入用户数据至目标库。

导入dmp文件到新库，在新库执行如下命令：

$ imp user/pwd fromuser=XXX touser=XXX file=exp_xxx.dmp log=imp_xxx.log ignore=y;