exp/expdp

最新推荐文章于 2023-05-10 21:37:55 发布

ctkk9981

最新推荐文章于 2023-05-10 21:37:55 发布

阅读量82

点赞数

A ---&gt 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 -------------&gt存儲很的大的表一般設置為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 --------------&gt 存儲較大的表設置為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 --------------&gt 存儲普通表設置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 ----------------&gt 存儲小表 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
-------&gt注意由於結構已經導入了,所以我們只需要導入數據,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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25072296/viewspace-697049/

ctkk9981

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
exp/expdp

A ---&gt B在將數據導入的的時候要求按照標的行數來決定表所要放置的表空間,則可以將該schema下的所有表的scripts導出,然後將scripts中的所有表及索引對應的空間首先設置成隨便的 base...
复制链接

扫一扫