oracle数据库迁移相关SQL语句
数据库迁移常见的一种方式是使用命令将某个用户的所有数据导出,再将其导入新用户中。
创建用户需要DBA权限。
sqlplus /nologconn /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