Oracle数据迁移手册
Oracle
数据库支持组
目 录
1 配置登录
表
关于同志近三年现实表现材料材料类招标技术评分表图表与交易pdf视力表打印pdf用图表说话 pdf
............................................................................................................................... 1
2 对象依赖性 ............................................................................................................................... 1
3 其他数据特征 ........................................................................................................................... 1 4 数据导出 ................................................................................................................................... 1
5 数据导入 ................................................................................................................................... 2
5.1 创建表空间 ................................................................................................................... 2
5.2 创建用户 ....................................................................................................................... 2
5.3 数据导入 ....................................................................................................................... 2 6 数据库验证 ............................................................................................................................... 2
6.1 数据对象验证 ............................................................................................................... 2
6.1.1 参数文件 ........................................................................................................... 2 7 常用SQL .................................................................................................................................. 2
7.1 数据库版本 ................................................................................................................... 2
7.2 数据库实例名 ............................................................................................................... 2
7.3 依赖于其他帐号的对象信息 ....................................................................................... 2
7.4 其他帐号依赖的对象信息 ........................................................................................... 2
7.5 无效的对象 ................................................................................................................... 2
7.6 数据表空间的最小值 ................................................................................................... 3
7.7 用户创建的数据库表空间及数据文件名 ................................................................... 3
7.8 新建帐号及特征 ........................................................................................................... 3
7.9 数据库字符集 ............................................................................................................... 3
7.10 用户的权限 ............................................................................................................... 3
7.11 用户的表权限 ............................................................................................................... 3
7.12 对象未存在于默认表空间 ....................................................................................... 4
7.13 设置客户端字符集 ................................................................................................... 4
7.14 数据导出脚本 ........................................................................................................... 4
7.15 数据导入脚本 ........................................................................................................... 4
1
1 配置登录表
源服务器 目标服务器 客户端
基本描述
注释
硬件
地点 机型 处理器 内存 存贮设备 网络状况
系统
操作系统 网络配置
数据库
数据库 实例名 字符集 表空间 帐号、表空间 2 对象依赖性
3 其他数据特征
4 数据导出
exp
1
5 数据导入
5.1 创建表空间
5.2 创建用户
5.3 数据导入
6 数据库验证
6.1 数据对象验证
6.1.1 参数文件
7 常用SQL
7.1 数据库版本
SELECT * FROM v$version
7.2 数据库实例名
SELECT * FROM global_name 7.3 依赖于其他帐号的对象信息
DEFINE owner='TOAD'
SELECT TYPE, NAME, referenced_owner, referenced_name, referenced_type
FROM dba_dependencies
WHERE owner = UPPER ('&owner')
AND owner <> referenced_owner
AND referenced_owner <> 'SYS' 7.4 其他帐号依赖的对象信息
DEFINE owner='TOAD'
SELECT owner, TYPE, NAME, referenced_type, referenced_name
FROM dba_dependencies
WHERE owner <> referenced_owner AND referenced_owner = UPPER ('&owner')
7.5 无效的对象
DEFINE owner='TOAD'
SELECT object_type, object_name , last_ddl_time
FROM dba_objects
2
WHERE status = 'INVALID' AND owner = UPPER ('&owner') 7.6 数据表空间的最小值
SELECT tablespace_name, ROUND (SUM (BYTES) / 1024 / 1024, 2) || 'M' minsize
FROM dba_extents
WHERE tablespace_name IN (
SELECT t.NAME
FROM v$datafile d, v$tablespace t
WHERE t.ts# = d.ts#
AND creation_time > (SELECT MIN (creation_time) + 1 / 24
FROM v$datafile))
GROUP BY tablespace_name
7.7 用户创建的数据库表空间及数据文件名
SELECT t.NAME, d.NAME
FROM v$datafile d, v$tablespace t
WHERE t.ts# = d.ts#
AND creation_time > (SELECT MIN (creation_time) + 1 / 24
FROM v$datafile)
ORDER BY creation_time
7.8 新建帐号及特征
SELECT username, default_tablespace, temporary_tablespace, created
FROM dba_users
WHERE created > (SELECT MIN (created) + 1 / 24
FROM dba_users)
ORDER BY created
7.9 数据库字符集
SELECT *
FROM nls_database_parameters
WHERE parameter IN ('NLS_CHARACTERSET', 'NLS_LANGUAGE','NLS_TERRITORY');
7.10 用户的权限
DEFINE owner='TOAD'
SELECT 'SYS', PRIVILEGE
FROM dba_sys_privs
WHERE grantee = UPPER ('&owner')
UNION
SELECT 'ROLE', granted_role
FROM dba_role_privs
WHERE grantee = UPPER ('&owner')
7.11 用户的表权限
DEFINE owner='TOAD'
3
SELECT owner, table_name, PRIVILEGE
FROM dba_tab_privs
WHERE grantee = UPPER ('&owner')
7.12 对象未存在于默认表空间
DEFINE owner='TOAD'
SELECT '1.TABLE' obj_type, table_name object_name, tablespace_name
FROM dba_tables
WHERE owner = UPPER ('&owner')
AND tablespace_name NOT IN (SELECT default_tablespace
FROM dba_users
WHERE username = UPPER ('&owner'))
UNION
SELECT DECODE (index_type, 'NORMAL', '2.INDEX', index_type),
index_name object_name, tablespace_name
FROM dba_indexes
WHERE owner = UPPER ('&owner')
AND tablespace_name NOT IN (SELECT default_tablespace
FROM dba_users
WHERE username = UPPER ('&owner'))
ORDER BY 1
7.13 设置客户端字符集
set NLS_LANG=
_. SET NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
7.14 数据导出脚本
exp
userid
file
grants
indexes
direct
log
rows
owner
triggers
constraints
filesize
7.15 数据导入脚本
imp
userid
file
4
show
ignore
grants
indexes
rows
log
fromuser
touser
tables
constraints
indexfile
5