数据迁移要求:
表结构要同步,存储过程、函数、同义词是、视图要、序列更要同步
1同步数据结构
2预先导入大表数据
3导入其他数据(注意序列、触发器)
----------------------------------------------------------------------------------------------------------------------
迁移过程:
1.在本地库创建用dawn 与值对应的表空间;
2.用dawn登陆,创建相关的表,主外键约束,索引,存储过程,函数,同义词,
视图,序列,触发器;
3.将dawn用户的数据结构和数据分别导出;
4.将dump文件导入到RAC实例;
5. 事后处理[主要包括索引,约束,触发器的启用--无效的procedure,function,package
的重新编译]
----------------------------------------------------------------------------------------------------------------------
DB script:
创建用户和表空间:
SQL> select file_name from dba_data_files;
FILE_NAME
----------------------------------------------------
E:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS01.DBF
E:\APP\ADMINISTRATOR\ORADATA\ORCL\UNDOTBS01.DBF
E:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSAUX01.DBF
E:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSTEM01.DBF
SQL> create tablespace dawn datafile
2 'E:\APP\ADMINISTRATOR\ORADATA\ORCL\dawn.dbf' size 50m;
Tablespace created
SQL> create user dawn identified by dawn default tablespace dawn temporary tablespace temp;
SQL> grant dba to dawn;
SQL> grant connect to dawn;
SQL> grant resource to dawn;
使用dawn登录创建相关数据结构:
SQL> conn dawn/dawn;
--学生表
create table T_student
(
stu_id number not null,
stu_name VARCHAR2(9) not null,
subject_id NUMBER not null
)
alter table t_student add constraint stu_pk primary key(stu_id);
--创建唯一索引
create unique index ind_stu_name on T_student(stu_name); --唯一索引不能插入相同的数据
--课程表
create table t_subject(
subject_id number not null,
subject_name varchar2(50) not null,
subject_teacher varchar2(50)
)
alter table t_subject add constraint sub_pk primary key(subject_id);
--创建序列
create sequence stu_seq increment by 1 start with 1 MAXVALUE 999999 NOCYCLE NOCACHE;
--外键约束,级联删除
alter table T_student add constraint stu_fk foreign key (subject_id) references t_subject(subject_id) ON DELETE CASCADE;
--创建试图--学生课程视图
create view v_stu_sub as select st.stu_id,st.stu_name,sj.subject_name,sj.subject_teacher from T_student st,t_subject sj
where st.subject_id=sj.subject_id;
--创建函数
CREATE OR REPLACE
FUNCTION F_add (num1 IN NUMBER, num2 IN NUMBER)
RETURN NUMBER
AS
num3 number;
BEGIN
num3 := num1 + num2;
RETURN num3;
--创建重建索引procedure
create or replace procedure BATCH_REBUILD_INDEX is
S_SQL VARCHAR2(500);
ACCOUNT_normal NUMBER := 0;
--normal index rebuild
begin
FOR LINE2 IN (select t4.index_name,t4.tablespace_name from user_indexes t4 where t4.status='UNUSABLE')LOOP
S_SQL := 'alter index ' || LINE2.INDEX_NAME || ' rebuild tablespace '||LINE2.TABLESPACE_NAME||' pararllel 4 nologging';
DBMS_OUTPUT.PUT_LINE(S_SQL);
ACCOUNT_normal := ACCOUNT_normal + 1;
EXECUTE IMMEDIATE S_SQL;
END LOOP;
DBMS_OUTPUT.PUT_LINE('normal index rebuild num