整理项目资料时，看到了几年前数据库迁移时的备忘记录。迁移过程还是比较曲折的，现在想起来心里还有些抵触，这活简直太“脏”了，比较费神。
项目概要如下，迁移过程属于跨省数据迁移，之间通过运营商专线传输备份数据，数据量大概700G左右，说是专线，白天有时速度10几K，只能将任务放置到后台执行，这尼玛是毛专线。现在也没搞明白。
迁移过程使用oracle数据泵方式，只将表数据导出，传到对端后，再采用数据泵导入。schema信息单独导出，再进行导入。
现将oracle一些比较常用的命令记录如下：
1、查询字符集
# sqlplus / as sysdba 或者 SQL > conn sys as sysdba; 查询字符集 SQL> select userenv('language') from dual; USERENV('LANGUAGE') ---------------------------------------------------- AMERICAN_AMERICA.AL32UTF8 2、查询表注释
select COLUMN_NAME,COMMENTS from dba_col_comments where owner='user' and table_name='table'
3、查询表[TABLE]/分区表[TABLE PARTITION]/索引[INDEX]占用空间
select segment_name, sum(bytes)/1024/1024 Mbytese from user_segments where segment_type='TABLE' group by segment_name;
4、查询表结构
select table_name,tablespace_name,num_rows,status,partitioned from user_all_tables;
5、查询分区表各分区记录情况
SELECT partition_name,num_rows FROM user_tab_partitions WHERE table_name = UPPER('table_name') order by partition_name 6、查询表空间使用情况
SQL > set linesize 200 SQL > col tablespace for a25 SQL > SELECT c.tablespace_name "tablespace", ROUND(a.bytes / 1048576, 2) "total(MB)", ROUND(b.bytes / 1048576, 0) "free(MB)", ROUND((a.bytes - b.bytes) / 1048576, 0) "used(MB)", ROUND(b.bytes / a.bytes * 100, 0) "free(%)", ROUND((a.bytes - b.bytes) / a.bytes, 2) * 100 "used(%)" FROM (SELECT tablespace_name, SUM(a.bytes) bytes, MIN(a.bytes) minbytes, MAX(a.bytes) maxbytes FROM sys.DBA_DATA_FILES a GROUP BY tablespace_name) a, (SELECT a.tablespace_name, NVL(SUM(b.bytes), 0) bytes FROM sys.DBA_DATA_FILES a, sys.DBA_FREE_SPACE b WHERE a.tablespace_name = b.tablespace_name(+) AND a.file_id = b.file_id(+) GROUP BY a.tablespace_name) b, sys.DBA_TABLESPACES c WHERE a.tablespace_name = b.tablespace_name(+) AND a.tablespace_name = c.tablespace_name ORDER BY 5;
7、查询所有主键与索引
select index_name,table_name from user_indexes;
8、查询建表索引
SQL > set long 999999; SQL > set pagesize 0; SQL > SELECT DBMS_METADATA.GET_DDL('INDEX','INDEX_NAME') FROM DUAL; 9、查看索引是否失效
SELECT index_name, partition_name, status FROM user_ind_partitions WHERE index_name = 'indexname'
10、将主键失效
ALTER TABLE TABLENAME MODIFY PRIMARY KEY DISABLE;
11、删除索引
drop index INDEXNAME;
12、重建普通分区索引
ALTER INDEX INDEXNAME REBUILD PARTITION PARTITIONNAME
13、查询用户默认表空间
select username,default_tablespace from dba_users;
14、TRUNCATE指定分区数据
alter table TABLE_NAME truncate partition PARTITION_NAME;
15、后台执行SQL文件
# nohup sqlplus username/password @sqlname.sql &
16、查询当前共享池状态
SELECT free_space, avg_free_size, used_space, avg_used_size, request_failures, last_failure_size FROM v$shared_pool_reserved; SQL > show parameter pool;
数据库迁移时常用命令：
1、创建DIRECTORY
SQL > create directory dir_dmp as '/home/oracle/dir_dmp';
2、授权
SQL > grant read,write on directory dir_dmp to user;
3、只导出表数据
#expdp username/password DUMPFILE=dmpname.dmp DIRECTORY=dir_dmp LOGFILE=logname.log INCLUDE=TABLE_DATA TABLES=(table1,table2) / TABLES=(TABLENAME:PARTITION_NAME,TABLENAME:PARTITION_NAME)
4、导入表数据
# impdp username/password DUMPFILE=dmpname.dmp DIRECTORY=dir_dmp LOGFILE=logname.log
5、示例
导出schema
expdp user/password directory=dir_dmp dumpfile=schema.dmp schemas=user
导出数据
expdp user/password dumpfile=baseTbl.dmp DIRECTORY=dir_dmp LOGFILE=baseTbl.log SCHEMAS=user_SCHEMA INCLUDE=TABLE_DATA TABLES=(tbl1,tbl2)
导入数据
impdp user/password dumpfile=baseTbl.dmp DIRECTORY=dir_dmp LOGFILE=baseTbl.log tables=table—name REMAP_SCHEMA=from-user:to-user
备注：
sftp免密码登陆配置
http://bbs.chinaunix.net/archiver/tid-508290.html