oracle数据库迁移相关SQL语句

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

    创建用户需要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;

数据库迁移步骤，需要准备导入的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;
     
     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;
     
     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、触发器，最后是表序列，因为在迁移数据库的过程中，旧数据库的序列有可能还在新增，最后才迁移序列可以避免新数据的序列冲突。