oracle数据库迁移

导出数据

首先测试exp命令能否使用，在cmd里输入exp，如果不报错说明可用，报错先百度解决报错！

查询环境变量

select * from nls_database_parameters t where t.parameter='NLS_CHARACTERSET';  

EXP-00091报错需要修改环境变量:

NLS_LANG = AMERICAN_AMERICA.AL32UTF8

默认：SIMPLIFIED CHINESE_CHINA.ZHS16GBK

修改环境变量为查出的一样： AMERICAN_AMERICA.AL32UTF8

执行导出命令

cmd进入存放备份文件的目录

exp STD06010000/XFP@10.20.0.12:1521/ELAN11GR2 file=STD06010000.DMP owner=(STD06010000) log=STD06010000.log
exp ARCHXXXXTST/XFP@10.20.0.12:1521/ELAN11GR2 file=ARCHXXXXTST.DMP owner=(ARCHXXXXTST) log=ARCHXXXXTST.log
exp EXPXXXXTST/XFP@10.20.0.12:1521/ELAN11GR2 file=EXPXXXXTST.DMP owner=(EXPXXXXTST) log=EXPXXXXTST.log
exp LIVEXXXXTST/XFP@10.20.0.12:1521/ELAN11GR2 file=LIVEXXXXTST.DMP owner=(LIVEXXXXTST) log=LIVEXXXXTST.log
exp XSIXXXXTST/XFP@10.20.0.12:1521/ELAN11GR2 file=XSIXXXXTST.DMP owner=(XSIXXXXTST) log=XSIXXXXTST.log

完整导出（数据量小使用）：

exp SYSTEM/XFP@10.20.0.12:1521/ELAN11GR2 file=STD06010000.DMP FULL=Y log=STD06010000.log

导入数据

创建用户：

create user STD06010000 identified by XFP;
create user LIVEXXXXTST identified by XFP;
create user XSIXXXXTST identified by XFP;
create user EXPXXXXTST identified by XFP;
create user ARCHXXXXTST identified by XFP;

授权表空间：
grant resource to STD06010000 ;
grant resource to LIVEXXXXTST ;
grant resource to XSIXXXXTST ;
grant resource to EXPXXXXTST ;
grant resource to ARCHXXXXTST ;

导入数据：

imp system/oracle@IP:PORT/orcl file=STD06010000.DMP log=IMP_STD06010000.log fromuser=STD06010000 touser=STD06010000

imp system/oracle@IP:PORT/orcl file=LIVEXXXXTST.DMP log=LIVEXXXXTST.log fromuser=LIVEXXXXTST touser=LIVEXXXXTST
imp system/oracle@IP:PORT/orcl file=ARCHXXXXTST.DMP log=IMP_ARCHXXXXTST.log fromuser=ARCHXXXXTST touser=ARCHXXXXTST
imp system/oracle@IP:PORT/orcl file=XSIXXXXTST.DMP log=IMP_XSIXXXXTST.log fromuser=XSIXXXXTST touser=XSIXXXXTST
imp system/oracle@IP:PORT/orcl file=EXPXXXXTST.DMP log=IMP_EXPXXXXTST.log fromuser=EXPXXXXTST touser=EXPXXXXTST

导入数据后可能需要重新编译视图

创建授权查询视图：

CREATE OR REPLACE VIEW VW_USER_PRIVS_LHR AS SELECT D.GRANTEE, CASE WHEN D.ADMIN_OPTION = 'YES' THEN 'GRANT ' || D.PRIVILEGE || ' TO ' || D.GRANTEE || ' WITH GRANT OPTION ;' ELSE 'GRANT ' || D.PRIVILEGE || ' TO ' || D.GRANTEE || ';' END PRIV, 'SYSTEM_GRANT' TYPE, 'DBA_SYS_PRIVS' FROM_VIEW FROM DBA_SYS_PRIVS D UNION ALL SELECT D.GRANTEE, CASE WHEN D.ADMIN_OPTION = 'YES' THEN 'GRANT ' || D.GRANTED_ROLE || ' TO ' || D.GRANTEE || ' WITH GRANT OPTION;' ELSE 'GRANT ' || D.GRANTED_ROLE || ' TO ' || D.GRANTEE || ';' END PRIV, 'SYSTEM_GRANT' TYPE, 'DBA_SYS_PRIVS' FROM_VIEW FROM DBA_ROLE_PRIVS D UNION ALL SELECT D.GRANTEE, CASE WHEN D.GRANTABLE = 'YES' THEN 'GRANT ' || D.PRIVILEGE || ' ON ' || D.OWNER || '.' || D.TABLE_NAME || ' TO ' || D.GRANTEE || ' WITH GRANT OPTION ;' ELSE 'GRANT ' || D.PRIVILEGE || ' ON ' || D.OWNER || '.' || D.TABLE_NAME || ' TO ' || D.GRANTEE || ';' END PRIV, 'SYSTEM_GRANT' TYPE, 'DBA_SYS_PRIVS' FROM_VIEW FROM DBA_TAB_PRIVS D where D.TABLE_NAME not like 'BIN%' UNION ALL SELECT D.GRANTEE, CASE WHEN D.GRANTABLE = 'YES' THEN 'GRANT ' || D.PRIVILEGE || ' (' || D.COLUMN_NAME || ') ON ' || D.OWNER || '.' || D.TABLE_NAME || ' TO ' || D.GRANTEE || ' WITH GRANT OPTION ;' ELSE 'GRANT ' || D.PRIVILEGE || ' (' || D.COLUMN_NAME || ') ON ' || D.OWNER || '.' || D.TABLE_NAME || ' TO ' || D.GRANTEE || ';' END PRIV, 'COL_GRANT' TYPE, 'DBA_COL_PRIVS' FROM_VIEW FROM DBA_COL_PRIVS D where D.TABLE_NAME not like 'BIN%';   

查询用户的授权：
SELECT * FROM VW_USER_PRIVS_LHR D WHERE D.GRANTEE = ‘STD06010000’;
SELECT * FROM VW_USER_PRIVS_LHR D WHERE D.GRANTEE = ‘LIVEXXXXTST’;
SELECT * FROM VW_USER_PRIVS_LHR D WHERE D.GRANTEE = ‘XSIXXXXTST’;
SELECT * FROM VW_USER_PRIVS_LHR D WHERE D.GRANTEE = ‘EXPXXXXTST’;
SELECT * FROM VW_USER_PRIVS_LHR D WHERE D.GRANTEE = ‘ARCHXXXXTST’;

需要将授权全部在新库执行：

复制授权语句，执行！

其他常用命令

删除用户：
drop user LIVEXXXXTST cascade

连接数据库：
sqlplus /nolog
conn system/oracle@IP:PORT/orcl

sqlplus system/oracle@IP:PORT/orcl as sysdba

sqlplus sys/oracle@IP:PORT/orcl as sysdba

“ORACLE_HOME”=”E:\oracle\product\10g\db_1”
“ORACLE_SID”=”oratest”
“ORACLE_BASE”=”E:\oracle\product\10g”

show parameter service_name

SELECT --B.file_name "文件名",
 A.TABLESPACE_NAME "表空间名",
 TOTAL "表空间大小",
 FREE "表空间剩余大小",
 (TOTAL - FREE) "表空间使用大小",
 TOTAL / (1024 * 1024 * 1024) "表空间大小(G)",
 FREE / (1024 * 1024 * 1024) "表空间剩余大小(G)",
 (TOTAL - FREE) / (1024 * 1024 * 1024) "表空间使用大小(G)",
 ROUND((TOTAL - FREE) / TOTAL, 4) * 100 "使用率 %"
FROM (SELECT TABLESPACE_NAME,
 SUM(BYTES) FREE
 FROM DBA_FREE_SPACE
 GROUP BY TABLESPACE_NAME) A,
 (SELECT TABLESPACE_NAME,
 -- file_name,
 SUM(BYTES) TOTAL
 FROM DBA_DATA_FILES
 GROUP BY TABLESPACE_NAME/*,file_name*/) B
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME;



select tablespace_name,file_name,autoextensible from dba_data_files ;