将大批量数据迁移到新建数据库

刚建的数据库数据库后大批量数据迁移

1、创建表空间

CREATE TABLESPACE LOG_DATA DATAFILE

'/home/oracle/u01/product/oradata/typzpt/log_data01.dbf' SIZE 2147483648,

'/home/oracle/u01/product/oradata/typzpt/log_data02.dbf' SIZE 2147483648,

'/home/oracle/u01/product/oradata/typzpt/log_data03.dbf' SIZE 2147483648,

'/home/oracle/u01/product/oradata/typzpt/log_data04.dbf' SIZE 2147483648,

'/home/oracle/u01/product/oradata/typzpt/log_data05.dbf' SIZE 2147483648,

'/home/oracle/u01/product/oradata/typzpt/log_data06.dbf' SIZE 2147483648

LOGGING ONLINE PERMANENT BLOCKSIZE 8192

EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO;

2、创建用户

查看所有用户

select * from dba_users;

创建表空间

--CREATE TABLESPACE LOG_DATA DATAFILE

-- '/home/oracle/u01/product/oradata/typzpt/log_data01.dbf' SIZE 2147483648,

--'/home/oracle/u01/product/oradata/typzpt/log_data02.dbf' SIZE 2147483648,

--'/home/oracle/u01/product/oradata/typzpt/log_data03.dbf' SIZE 2147483648,

--'/home/oracle/u01/product/oradata/typzpt/log_data04.dbf' SIZE 2147483648,

--'/home/oracle/u01/product/oradata/typzpt/log_data05.dbf' SIZE 2147483648,

--'/home/oracle/u01/product/oradata/typzpt/log_data06.dbf' SIZE 2147483648

--LOGGING ONLINE PERMANENT BLOCKSIZE 8192

--EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO;

创建用户

--create user YTORA2WDEBJ

--identified by YTORA2WDEBJ

--default tablespace users

--temporary tablespace temp;

 

--create user TEST

--identified by TEST

--default tablespace users

--temporary tablespace temp;

 

--create user USO

--identified by USO

--default tablespace users

--temporary tablespace temp;

 

--create user typzpt2

--identified by typzpt2int

--default tablespace LOG_DATA

--temporary tablespace temp;

赋权

--grant connect,resource,dba to TEST;

--grant connect,resource,dba to YTORA2WDEBJ ;

--grant connect,resource,dba to USO;

-- GRANT CONNECT, RESOURCE,DBA TO TYPZPT2;

-- GRANT CREATE JOB TO TYPZPT2;

-- GRANT DEBUG ANY PROCEDURE TO TYPZPT2;

-- GRANT DEBUG CONNECT SESSION TO TYPZPT2;

-- GRANT ALTER ANY TRIGGER TO TYPZPT2;

-- GRANT CREATE TRIGGER TO TYPZPT2;

-- GRANT CREATE VIEW TO TYPZPT2;

-- grant create table to TYPZPT2;

-- grant create sequence to TYPZPT2;

-- ALTER USER TYPZPT2 QUOTA UNLIMITED ON LOG_DATA;

-- ALTER USER TYPZPT2 QUOTA UNLIMITED ON LOG_INDEX;

-- ALTER SESSION SET CURRENT_SCHEMA=typzpt2;

3、导入数据

imp ty****/ty****@ty**** file = /home/oracle/data/typzpt_all_tables ignore=y full=y statistics=none

4、删除用户下的所有表和触发器

如果表导入中出错需要删除该用户下所有数据可用此命令进行删除语句的拼接

--select 'drop table '||table_name||' cascade constraints;' from user_tables;

--select 'drop trigger ' || trigger_name || ';' from user_triggers;

--select 'drop sequence ' || sequence_name || ';' from user_sequences;

   select 'drop view ' || view_name||';'||chrstatic(10) from user_views;

 

5、修改数据文件大小

如果数据文件大小不足时可以添加数据文件的分配的空间，具体命令如下

alter tablespace ty**** add datafile '/home/oracle/u01/product/oradata/ty****/log_data01.dbf' 40960 M;

6、查询表空间使用情况

上一步提到表空间不足可以通过这个命令查询

select Upper(f.tablespace_name) "表空间名",

d.tot_grootte_mb "表空间大小(M)",

　 　d.tot_grootte_mb - f.total_bytes "已使用空间(M)",

to_char(round((d.tot_grootte_mb - f.total_bytes) /

d.tot_grootte_mb * 100, 2),'990.99') || '%' "使用比",

　 f.total_bytes "空闲空间(M)",

　　 f.max_bytes "最大块(M)"

from (select tablespace_name,

round(sum(bytes) / (1024 * 1024), 2) total_bytes,

round(max(bytes) / (1024 * 1024), 2) max_bytes

from sys.dba_free_space

group by tablespace_name) f,

(select dd.tablespace_name,

round(sum(dd.bytes) / (1024 * 1024), 2) tot_grootte_mb

from sys.dba_data_files dd

group by dd.tablespace_name) d

where d.tablespace_name = f.tablespace_name

order by f.tablespace_name;

查看数据库下的触发器

select * from user_procedures;

SELECT trigger_name,table_owner,table_name FROM user_triggers;

SELECT trigger_name,table_owner,table_name FROM all_triggers;

禁止触发器：alter trigger trigger_name disable;

激活触发器：alter trigger trigger_name enable;

转载于:https://my.oschina.net/BigDataInterchange/blog/897836