ITPub博客

首页 > Linux操作系统 > Linux操作系统 > exp/expdp

exp/expdp

原创 Linux操作系统 作者:carlos。zh 时间:2011-06-03 11:09:05 0 删除 编辑

A  --->  B
在將數據導入的的時候要求按照標的行數  來決定表所要放置的表空間,

則可以將該schema下的所有表的scripts導出,然後將scripts中的所有表及索引對應的空間首先設置成隨便的 base_data  base_idx 然後再在B庫中執行該scripts

然後使用以下語句將表及索引按照  要求  修改存儲的表空間

B庫的表空間設計

CREATE TABLESPACE SN_DATA DATAFILE
  '/data/mrp/sn_data01.dbf' SIZE 4000M AUTOEXTEND ON NEXT 1000M MAXSIZE 4000M,
  '/data/mrp/sn_data02.dbf' SIZE 6000M AUTOEXTEND ON NEXT 1000M MAXSIZE 8192M
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL UNIFORM. SIZE 20M      ------------->存儲很的大的表 一般設置為20M   一般1000W以上    表的next  大小 一般設置為20M

BLOCKSIZE 16K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

 

CREATE TABLESPACE WIP_DATA DATAFILE
  '/data/mrp/wip_data01.dbf' SIZE 4000M AUTOEXTEND ON NEXT 1000M MAXSIZE 8192M
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL UNIFORM. SIZE 10M    --------------> 存儲較大的表  設置為10M     幾百萬到1000W左右    表的next  大小 一般設置為10M

BLOCKSIZE 16K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

CREATE TABLESPACE LOG_DATA DATAFILE
  '/data/mrp/log_data01.dbf' SIZE 2000M AUTOEXTEND OFF,
  '/data/mrp/log_data02.dbf' SIZE 2000M AUTOEXTEND ON NEXT 1000M MAXSIZE 4000M
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL UNIFORM. SIZE 1M     -------------->  存儲普通表   設置1M     10W以上 100W以下    表的next  大小 一般設置為5M

BLOCKSIZE 16K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;


CREATE TABLESPACE BASE_DATA DATAFILE
  '/data/mrp/base_data01.dbf' SIZE 2000M AUTOEXTEND OFF,
  '/data/mrp/base_data02.dbf' SIZE 2000M AUTOEXTEND ON NEXT 1000M MAXSIZE 4000M
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL UNIFORM. SIZE 128K   ----------------> 存儲小表   10W以下           表的next  大小 一般設置為1M
BLOCKSIZE 16K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

將a庫中的某個schema的結構導出,toad即可 --》然後導入到B

根據table的行數 來規劃相應的存儲表空間 (a庫執行,得出的結果在B庫執行)

SELECT      ' alter table  DT2GLOBAL_AD.'
         || object_name
         || '  move tablespace BASE_DATA ; '
  FROM   dba_objects
 WHERE   object_type = 'TABLE' AND wner = 'DT2GLOBAL_AD'
         AND object_name IN
                  (SELECT   table_name
                     FROM   dba_tables
                    WHERE   wner = 'DT2GLOBAL_AD'
                            AND num_rows <1000
                    union
                    SELECT   table_name
                     FROM   dba_tables
                    WHERE   wner = 'DT2GLOBAL_AD'
                            AND num_rows is null)

union

/* Formatted on 2011/2/18 下午 04:54:27 (QP5 v5.115.810.9015) */
SELECT      ' alter index  DT2GLOBAL_AD.'
         || index_name
         || '  rebuild  tablespace BASE_IDX ;'
  FROM   dba_indexes
 WHERE   table_owner = 'DT2GLOBAL_AD' AND wner = 'DT2GLOBAL_AD'
         AND table_name IN
                  (SELECT   table_name
                     FROM   dba_tables
                    WHERE   wner = 'DT2GLOBAL_AD'
                            AND num_rows <1000
                    union
                    SELECT   table_name
                     FROM   dba_tables
                    WHERE   wner = 'DT2GLOBAL_AD'
                            AND num_rows is null)


union

/* Formatted on 2011/2/18 下午 04:54:19 (QP5 v5.115.810.9015) */
SELECT      ' alter table  DT2GLOBAL_AD.'
         || object_name
         || '  move tablespace LOG_DATA ; '
  FROM   dba_objects
 WHERE   object_type = 'TABLE' AND wner = 'DT2GLOBAL_AD'
         AND object_name IN
                  (SELECT   table_name
                     FROM   dba_tables
                    WHERE   wner = 'DT2GLOBAL_AD'
                            AND num_rows >= 1000 and num_rows < 100000)
union

/* Formatted on 2011/2/18 下午 04:54:27 (QP5 v5.115.810.9015) */
SELECT      ' alter index  DT2GLOBAL_AD.'
         || index_name
         || '  rebuild  tablespace LOG_IDX ;'
  FROM   dba_indexes
 WHERE   table_owner = 'DT2GLOBAL_AD' AND wner = 'DT2GLOBAL_AD'
         AND table_name IN
                  (SELECT   table_name
                     FROM   dba_tables
                    WHERE   wner = 'DT2GLOBAL_AD'
                            AND num_rows >= 1000 and num_rows < 100000)


union

/* Formatted on 2011/2/18 下午 04:54:19 (QP5 v5.115.810.9015) */
SELECT      ' alter table  DT2GLOBAL_AD.'
         || object_name
         || '  move tablespace WIP_DATA ; '
  FROM   dba_objects
 WHERE   object_type = 'TABLE' AND wner = 'DT2GLOBAL_AD'
         AND object_name IN
                  (SELECT   table_name
                     FROM   dba_tables
                    WHERE   wner = 'DT2GLOBAL_AD'
                            AND num_rows >= 100000 and num_rows < 10000000)
union

/* Formatted on 2011/2/18 下午 04:54:27 (QP5 v5.115.810.9015) */
SELECT      ' alter index  DT2GLOBAL_AD.'
         || index_name
         || '  rebuild  tablespace WIP_IDX ;'
  FROM   dba_indexes
 WHERE   table_owner = 'DT2GLOBAL_AD' AND wner = 'DT2GLOBAL_AD'
         AND table_name IN
                  (SELECT   table_name
                     FROM   dba_tables
                    WHERE   wner = 'DT2GLOBAL_AD'
                            AND num_rows >= 100000 and num_rows < 10000000)


union

/* Formatted on 2011/2/18 下午 04:54:19 (QP5 v5.115.810.9015) */
SELECT      ' alter table  DT2GLOBAL_AD.'
         || object_name
         || '  move tablespace SN_DATA ; '
  FROM   dba_objects
 WHERE   object_type = 'TABLE' AND wner = 'DT2GLOBAL_AD'
         AND object_name IN
                  (SELECT   table_name
                     FROM   dba_tables
                    WHERE   wner = 'DT2GLOBAL_AD'
                            AND num_rows >= 10000000)
union

/* Formatted on 2011/2/18 下午 04:54:27 (QP5 v5.115.810.9015) */
SELECT      ' alter index  DT2GLOBAL_AD.'
         || index_name
         || '  rebuild  tablespace SN_IDX ;'
  FROM   dba_indexes
 WHERE   table_owner = 'DT2GLOBAL_AD' AND wner = 'DT2GLOBAL_AD'
         AND table_name IN
                  (SELECT   table_name
                     FROM   dba_tables
                    WHERE   wner = 'DT2GLOBAL_AD'
                            AND num_rows >= 10000000);
修改存儲

SELECT 'ALTER TABLE MRP.'||TABLE_NAME||' STORAGE ( NEXT 1M ) ;' from dba_tables where tablespace_name='BASE_DATA' and wner='MRP'
UNION
SELECT 'ALTER INDEX MRP.'||INDEX_NAME||' STORAGE ( NEXT 1M ) ;' from dba_indexes where tablespace_name='BASE_IDX' and wner='MRP'
UNION
SELECT 'ALTER TABLE MRP.'||TABLE_NAME||' STORAGE ( NEXT 5M ) ;' from dba_tables where tablespace_name='LOG_DATA' and wner='MRP'
UNION
SELECT 'ALTER INDEX MRP.'||INDEX_NAME||' STORAGE ( NEXT 5M ) ;' from dba_indexes where tablespace_name='LOG_IDX' and wner='MRP'
UNION
SELECT 'ALTER TABLE MRP.'||TABLE_NAME||' STORAGE ( NEXT 10M ) ;' from dba_tables where tablespace_name='WIP_DATA' and wner='MRP'
UNION
SELECT 'ALTER INDEX MRP.'||INDEX_NAME||' STORAGE ( NEXT 10M ) ;' from dba_indexes where tablespace_name='WIP_IDX' and wner='MRP'
UNION
SELECT 'ALTER TABLE MRP.'||TABLE_NAME||' STORAGE ( NEXT 20M ) ;' from dba_tables where tablespace_name='SN_DATA' and wner='MRP'
UNION
SELECT 'ALTER INDEX MRP.'||INDEX_NAME||' STORAGE ( NEXT 20M ) ;' from dba_indexes where tablespace_name='SN_IDX' and wner='MRP';

8i的字符集設置

NLS_LANG=American_america.ZHT16BIG5;export NLS_LANG

 windows下,

set NLS_LANG=American_america.AL32UTF8
exp system/"""system@dbsec""" wner=gwdms_lh file=e:\backup\gwdms_lh.dmp log=e:\backup\gwdms_lh.txt

在Linux下,

NLS_LANG=American_america.AL32UTF8;export NLS_LANG

exp 'system/"system$test"'@csdqc70 wner=tms file='/u01/backup/tms.dmp' log=/u01/backup/tms.log'

發現漏導了一個schema的數據  但結構都已經導入了

set NLS_LANG=AMERICAN_AMERICA.AL32UTF8

exp system/"""system@dbsec""" file=e:\dt2global_ad.dmp log=E:\dt2global_ad.log wner=dt2global_ad

imp system/system#test file=dt2global_ad.dmp fromuser=dt2global_ad touser=dt2global_ad rows=y ignore=y log=imp_dt2global_ad.log constraint=n
------->注意 由於結構已經導入了,所以我們只需要導入數據,constraint不需要導..rows=y  constraint=n

檢查導入后index建是否合理(因為前庫可能有的index不在她的schema下)

背景:  A全庫導出   -- 需要全庫遷移到 ---B庫
 當A庫中的sfis1 導入時,報錯 --需要sfism4上的表,則導的時不管他,這是因為A庫在建sfism4索引的時候不合理造成的
 逐個schame導完后
 select 'drop index SFIS1.'||index_name||';' ,table_owner from dba_indexes where owner != table_owner
 得出drop index語句,直接執行
 然後在原庫中找出該index語句,注意在語句中修改owner使索引創建正確。

導完對象后比對

/* Formatted on 2011/4/12 09:45:48 (QP5 v5.115.810.9015) */
  SELECT   OWNER, OBJECT_TYPE, COUNT (OWNER) OBJECT_COUNT
    FROM   SYS.DBA_OBJECTS
   WHERE   wner = 'GWDMS_WH'
GROUP BY   OWNER, OBJECT_TYPE
ORDER BY   OBJECT_TYPE

簡介exp、imp

將A庫中的dfms導入到B庫中:

cd /data/dump_dir
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
expdp system/'system$oracle' directory=DUMP_DIR dumpfile=dfms20110414.dmp schemas=dfms logfile=dfms20110414.log

cd /data/dump_dir
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
impdp system/'system$test'@dell10qc directory=DUMP_DIR dumpfile=dfms20110414.dmp schemas=dfms logfile=dfms20110414.log remap_schema=dfms:dfms

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25072296/viewspace-697049/,如需转载,请注明出处,否则将追究法律责任。

上一篇: dg standby lgwr
下一篇: linux 基礎
请登录后发表评论 登录
全部评论

注册时间:2010-12-12

  • 博文量
    21
  • 访问量
    24537