使用imp和exp前,需要先执行下面的命令设置编码格式
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
exp <user_id>/<password> file=<file_path> #如果目标用户的默认表空间与表的默认表空间不同,imp前需要手动修改dmp文件中的表空间 #imp前先确认该用户的所有表、约束、触发器、存储过程、视图、序列等都已经删除 imp <user_id>/<password> fromuser=<origin_username> touser=<target_username> file=<dmp_file_path>
打开sqlplus,连接后执行@<file_name_with_file_path>
sqlplus /nolog # 最好在sqlplus中先执行spool <filename>以将输出重定向到文件,执行完sql后执行spool off关闭重定向 sql>@<file_name_with_path>
参考:expdp impdp 数据库导入导出命令详解-石头博客-51CTO博客
# 导出 expdp <username>/<password>@<host>:<port>/<sid> schemas=<username> dumpfile=expdp.dmp DIRECTORY=DATA_PUMP_DIR; # 导入 impdp <username>/<password>@<host>:<port>/<sid> DIRECTORY=DATA_PUMP_DIR dumpfile=expdp.dmp schemas=<username>;
参考How to Create a User and Grant Permissions in Oracle
CREATE USER <user_name> IDENTIFIED BY <password> DEFAULT tablespace <tablespace_name>; --创建新用户,并指定默认表空间(可以不指定),password含有@符号时会报错,如果希望包含@符号,不清楚如何实现 GRANT CONNECT, resource, dba TO <user_name>; --为用户授予权限
DROP USER <user_name>; --禁止删除sys用户和system用户
CREATE tablespace <tablespace_name> datafile '<file_path>' SIZE <size_in_m_or_g> autoextend ON; --创建自动扩展的表空间
详见:oracle11g - How to move table from one tablespace to another in oracle 11g - Stack Overflow
ALTER TABLE <TABLE NAME TO be moved> MOVE TABLESPACE <destination TABLESPACE NAME>; --移动到新的表空间 ALTER INDEX <owner>."<index_name>" rebuild; --移动表空间会导致索引失效,因此需要重建索引
su oracle --登录 sqlplus /nolog --进入SQLPLUS
CONNECT / AS sysdba --切换管理员登录 shutdown --关闭 startup --启动
--数据库对象解锁 SELECT B.Owner, B.Object_Name, A.Oracle_Username, A.OS_User_Name FROM V$Locked_Object A, All_Objects B WHERE A.Object_ID = B.Object_ID; SELECT SID, SERIAL# FROM V$SESSION WHERE SID IN ( SELECT SESSION_ID FROM DBA_DML_LOCKS WHERE NAME = '<Object_Name>' ); ALTER SYSTEM KILL SESSION '<session>, <session>';
BEGIN FOR cur_rec IN (SELECT object_name, object_type FROM user_objects WHERE object_type IN ('TABLE', 'VIEW', 'PACKAGE', 'PROCEDURE', 'FUNCTION', 'SEQUENCE' )) LOOP BEGIN IF cur_rec.object_type = 'TABLE' THEN EXECUTE IMMEDIATE 'DROP ' || cur_rec.object_type || ' "' || cur_rec.object_name || '" CASCADE CONSTRAINTS'; ELSE EXECUTE IMMEDIATE 'DROP ' || cur_rec.object_type || ' "' || cur_rec.object_name || '"'; END IF; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.put_line ( 'FAILED: DROP ' || cur_rec.object_type || ' "' || cur_rec.object_name || '"' ); END; END LOOP; END;
--解决数据库系统序列值比最大id小导致无法插入数据的问题 DECLARE v_rlt NUMBER(8):=1; BEGIN <<fst_loop>> LOOP SELECT "TQYDZY"."ISEQ$$_92176".nextval INTO v_rlt FROM dual; dbms_output.put_line('v_rlt = '||v_rlt); exit fst_loop WHEN v_rlt > 5242;--序列的期望值 END loop; dbms_output.put_line('LOOP循环已经结束!'); END;
自Oracle11g R2开始,插入第一条数据才分配空间,所以没有数据的表默认没有分配空间,使用exp命令无法导出这些表。
解决办法是执行下面的sql得到修改表的空间分配方案的sql命令,再执行这些sql(点击查看更多信息):
BEGIN FOR i IN (SELECT TABLE_NAME FROM user_tables WHERE segment_created = 'NO') LOOP EXECUTE IMMEDIATE 'ALTER TABLE ' || i.table_name || ' ALLOCATE EXTENT '; END LOOP; END;