oracle数据库迁移相关SQL语句

database

    数据库迁移常见的一种方式是使用命令将某个用户的所有数据导出,再将其导入新用户中。

    创建用户需要DBA权限。

sqlplus /nolog

conn /as sysdba

1、创建用户及授权,一般都是需要新建表空间和临时表空间

----删除用户

drop user sccsrm cascade;

----临时表空间

create temporary tablespace SCCSRM_TMP_TS

tempfile "/u01/app/oracle/oradata/SRM/datafile/SCCSRM_TMP_TS.dbf"

size 50m

autoextend on

next 50m maxsize 20480m

extent management local;

----数据表空间

create tablespace SCCSRM_DATA_TS

logging

datafile "/u01/app/oracle/oradata/SRM/datafile/SCCSRM_DATA_TS.dbf"

size 50m

autoextend on

next 50m maxsize 20480m

extent management local;

----创建用户sccsrm

create user sccsrm identified by sccsrm default tablespace SCCSRM_DATA_TS temporary tablespace SCCSRM_TMP_TS;

----表空间授权给sccsrm

alter user sccsrm quota unlimited on SCCSRM_DATA_TS;

----授权sccsrm

grant connect,resource to sccsrm;

---查看表空间的位置

select * from dba_data_files;

---修改表空间dbf文件,设置成默认每次增加50M,无限大——————当出现表空间不足时

alter database datafile "/u01/app/oracle/oradata/SRM/datafile/SCCSRM_DATA_TS.dbf" autoextend on next 50M maxsize unlimited;

2、数据库迁移步骤,需要准备导入的Excel的表清单

-----1、创建临时表的表sccsrm.TABLE_NAME_TEMP

create table sccsrm.TABLE_NAME_TEMP

(

table_name VARCHAR2(100)

)

tablespace SCCSRM_DATA_TS;

-----2、查询当前用户apps所有表名,将其导出为Excel,用于sccsrm用户的临时表sccsrm.table_name_temp的导入数据

select t.table_name from user_tables t order by t.TABLE_NAME;

-----将导出的表清单Excel导入sccsrm.table_name_temp

-----3、表sccsrm.TABLE_NAME_TEMP数据去空

update sccsrm.table_name_temp t

set t.table_name=trim(t.table_name);

-----4、在apps用户登录下,授权表的查询权限给sccsrm用户

-----授权限给sccsrm的查询权限

declare

cursor vdata is select t.table_name from sccsrm.table_name_temp t order by t.TABLE_NAME;

--声明变量,记录一行数

v vdata%rowtype;

v_sql varchar2(200);

begin

--打开游标

open vdata;

--循环遍历取数据

loop

fetch vdata into v;

exit when vdata%NOTFOUND;

v_sql := "grant select on apps."||v.table_name||" to sccsrm";

execute immediate v_sql;

end loop;

--关闭游标

close vdata;

end;

-----5、复制表数据——将apps用户下的表数据迁移至sccsrm用户下

declare

cursor vdata is select t.table_name from sccsrm.table_name_temp t where not exists(select tt.TABLE_NAME from user_tables tt where tt.TABLE_NAME=t.table_name) order by t.TABLE_NAME;

--声明变量,记录一行数

v vdata%rowtype;

v_sql varchar2(600);

n number;

begin

--打开游标

open vdata;

--循环遍历取数据

loop

fetch vdata into v;

exit when vdata%NOTFOUND;

/*dbms_output.put_line(v.table_name);*/

v_sql := "create table sccsrm."||v.table_name||" as select * from apps."||v.table_name;

execute immediate v_sql;

end loop;

--关闭游标

close vdata;

end;

-----6、查看该用户apps下的所有索引

select t.index_name,t.index_type,t.table_name from user_indexes t order by t.table_name;

-----查询出apps所有索引,将其导出为Excel,再将Excel数据复制到PLSQL,再将其进行修改,以便在sccsrm用户执行

SELECT dbms_lob.substr(dbms_metadata.get_ddl("INDEX", INDEX_NAME)) || ";" from dba_indexes where owner = "APPS" and index_type="NORMAL";

-----7、在apps用户登录下,回收给sccsrm的查询权限

declare

cursor vdata is select t.table_name from sccsrm.table_name_temp t order by t.TABLE_NAME;

--声明变量,记录一行数

v vdata%rowtype;

v_sql varchar2(200);

begin

--打开游标

open vdata;

--循环遍历取数据

loop

fetch vdata into v;

exit when vdata%NOTFOUND;

v_sql := "revoke select on apps."||v.table_name||" from sccsrm";

execute immediate v_sql;

end loop;

--关闭游标

close vdata;

end;

-----8、在apps用户登录下,查询出apps用户下所有序列

SELECT "create sequence sccsrm."||SEQUENCE_NAME||" minvalue "||MIN_VALUE||" maxvalue "||MAX_VALUE||

" start with "||LAST_NUMBER||" increment by "||INCREMENT_BY||" cache "||" 100;"

FROM DBA_SEQUENCES

WHERE SEQUENCE_OWNER = "APPS";

-----查看当前用户的所有序列

select * from user_sequences t;

-----9、在apps用户登录下,删除表及其数据——apps用户的旧数据,强烈建议进行数据备份再执行删除操作,避免误操作造成数据丢失现象

declare

cursor vdata is select t.table_name from sccsrm.table_name_temp t order by t.TABLE_NAME;

--声明变量,记录一行数

v vdata%rowtype;

v_sql varchar2(600);

begin

--打开游标

open vdata;

--循环遍历取数据

loop

fetch vdata into v;

exit when vdata%NOTFOUND;

/*dbms_output.put_line(v.table_name);*/

v_sql := "drop table apps."||v.table_name;

execute immediate v_sql;

end loop;

--关闭游标

close vdata;

end;

---查看空表

select "alter table "||table_name||" allocate extent;"from user_tables WHERE SEGMENT_CREATED="NO";

select * from user_indexes WHERE SEGMENT_CREATED="NO";

select * from user_lobs where segment_created="NO";

select count(*) from v$process --当前的连接数

select value from v$parameter where name = "processes" --数据库允许的最大连接数

---修改最大连接数:

alter system set processes = 300 scope = spfile;

---重启数据库:

shutdown immediate;

startup;

---查看当前有哪些用户正在使用数据

SELECT osuser, a.username,cpu_time/executions/1000000||"s", sql_fulltext,machine

from v$session a, v$sqlarea b

where a.sql_address =b.address order by cpu_time/executions desc;

---查看oracle的性能

show parameter sga;

注意事项:迁移数据时,需要先迁移表结构、表数据,表索引,function及view、触发器,最后是表序列,因为在迁移数据库的过程中,旧数据库的序列有可能还在新增,最后才迁移序列可以避免新数据的序列冲突。

以上是 oracle数据库迁移相关SQL语句 的全部内容, 来源链接: utcz.com/z/533395.html

回到顶部