資料泵匯出索引資料和統計資訊嗎
關於在資料泵問題:
(1)索引資料匯出嗎?
(2)源表統計資料匯出/匯入嗎?
1建立測試表
drop table test;
create table test
as
select dbms_random.string('x',50) as x from dual
connect by level<=1e6;
create index idx_test_x on test(x);
begin
dbms_stats.gather_table_stats(user,'TEST');
end;
/
select table_name,num_rows,blocks,to_char(last_analyzed,'YYYYMMDD hh24miss') as l_anay from user_tables where table_name='TEST';
TABLE_NAME NUM_ROWS BLOCKS L_ANAY
---------- ---------- ---------- --------------------
TEST 997971 7884 20140315 153237
select index_name,leaf_blocks,to_char(last_analyzed,'YYYYMMDD hh24miss') as l_anay from user_indexes where table_name='TEST';
INDEX_NAME LEAF_BLOCKS L_ANAY
--------------- ----------- --------------------
IDX_TEST_X 8467 20140315 153238
col segment_name for a20
select segment_name,bytes/1024/1024 from user_segments where segment_name in('TEST','IDX_TEST_X');
SEGMENT_NAME BYTES/1024/1024
-------------------- ---------------
TEST 62
IDX_TEST_X 72
2修改統計資料
begin
dbms_stats.set_table_stats(ownname => user,tabname => 'TEST',numrows => 1000,numblks => 100);
end;
/
begin
dbms_stats.set_index_stats(ownname => user,indname => 'IDX_TEST_X',numlblks => 100);
end;
/
select table_name,num_rows,blocks,to_char(last_analyzed,'YYYYMMDD hh24miss') as l_anay from user_tables where table_name='TEST';
TABLE_NAME NUM_ROWS BLOCKS L_ANAY
---------- ---------- ---------- --------------------
TEST 1000 100 20140315 153457
select index_name,leaf_blocks,to_char(last_analyzed,'YYYYMMDD hh24miss') as l_anay from user_indexes where table_name='TEST';
INDEX_NAME LEAF_BLOCKS L_ANAY
--------------- ----------- --------------------
IDX_TEST_X 100 20140315 153457
3包含/不包含索引匯出
(1)不包含索引匯出
mah]$ expdp \'sys/sys as sysdba\' directory='DMP_DIR' tables=MH.TEST dumpfile=test.dmp exclude=INDEX estimate=statistics
Export: Release 10.2.0.1.0 - Production on Saturday, 15 March, 2014 15:36:19
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "SYS"."SYS_EXPORT_TABLE_01": 'sys/******** AS SYSDBA' directory=DMP_DIR tables=MH.TEST dumpfile=test.dmp exclude=INDEX estimate=statistics
Estimate in progress using STATISTICS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. estimated "MH"."TEST" 54.86 KB ==>estimate=blocks時,估計為62 MB
Total estimation using STATISTICS method: 54.86 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "MH"."TEST" 52.45 MB 1000000 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
/home/oracle/mah/test.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at 15:36:26
(2)包含索引匯出
[oracle@rhel5 mah]$ expdp \'sys/sys as sysdba\' directory='DMP_DIR' tables=MH.TEST dumpfile=test2.dmp estimate=statistics
Export: Release 10.2.0.1.0 - Production on Saturday, 15 March, 2014 15:37:22
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "SYS"."SYS_EXPORT_TABLE_01": 'sys/******** AS SYSDBA' directory=DMP_DIR tables=MH.TEST dumpfile=test2.dmp estimate=statistics
Estimate in progress using STATISTICS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. estimated "MH"."TEST" 54.86 KB
Total estimation using STATISTICS method: 54.86 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "MH"."TEST" 52.45 MB 1000000 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
/home/oracle/mah/test2.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at 15:37:30
匯出檔案比較:
[oracle@rhel5 mah]$ ls -lrt
-rw-r----- 1 oracle oinstall 55070720 Mar 15 15:36 test.dmp
-rw-r----- 1 oracle oinstall 55078912 Mar 15 15:37 test2.dmp ==>含索引
==>兩個檔案大小差異極小(8192bytes),因TEST表上索引列較大(50bytes*1000000rows),不可能含索引資料;多出來的資料塊僅包含索引結構.
4匯入
(1)刪除表MH.TEST後:
impdp \'sys/sys as sysdba\' directory='DMP_DIR' tables=MH.TEST dumpfile=test.dmp
SQL> select table_name,num_rows,blocks,to_char(last_analyzed,'YYYYMMDD hh24miss') as l_anay from user_tables where table_name='TEST';
TABLE_NAME NUM_ROWS BLOCKS L_ANAY
---------- ---------- ---------- --------------------
TEST 1000 100 20140315 153457
SQL> select index_name,leaf_blocks,to_char(last_analyzed,'YYYYMMDD hh24miss') as l_anay from user_indexes where table_name='TEST';
no rows selected
(2)刪除表MH.TEST後:
impdp \'sys/sys as sysdba\' directory='DMP_DIR' tables=MH.TEST dumpfile=test2.dmp
SQL> select table_name,num_rows,blocks,to_char(last_analyzed,'YYYYMMDD hh24miss') as l_anay from user_tables where table_name='TEST';
TABLE_NAME NUM_ROWS BLOCKS L_ANAY
---------- ---------- ---------- --------------------
TEST 1000 100 20140315 153457
SQL> select index_name,leaf_blocks,to_char(last_analyzed,'YYYYMMDD hh24miss') as l_anay from user_indexes where table_name='TEST';
INDEX_NAME LEAF_BLOCKS L_ANAY
--------------- ----------- --------------------
IDX_TEST_X 100 20140315 153457
==>行數和資料塊是匯出時設定的統計資訊,而不是資料本身真實分佈資訊,包括索引和表上的上次分析時間
5結論
(1)expdp不匯出索引資料,僅匯出索引DDL
(2)expdp/impdp分別匯出/匯入統計資料
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/18922393/viewspace-719227/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 資料泵匯出匯入
- Oracle資料泵的匯入和匯出Oracle
- Oracle資料泵匯出匯入(expdp/impdp)Oracle
- Oracle使用資料泵expdp,impdp進行資料匯出匯入Oracle
- Oracle expdp資料泵遠端匯出Oracle
- 細緻入微:如何使用資料泵匯出表的部分列資料
- 資料泵匯出匯入物化檢視(ORA-39083)
- plsql developmer 匯出資料和生成資料SQLdev
- 【資料泵】EXPDP匯出表結構(真實案例)
- 資料庫 MySQL 資料匯入匯出資料庫MySql
- 匯入和匯出AWR的資料
- ORACLE表統計資訊與列統計資訊、索引統計資訊Oracle索引
- 資料搬運元件:基於Sqoop管理資料匯入和匯出元件OOP
- [重慶思莊每日技術分享]-資料泵匯出資料包39006是什麼原因
- 資料字典和固定表統計資訊更新
- SQL資料庫的匯入和匯出SQL資料庫
- MySQL入門--匯出和匯入資料MySql
- ClickHouse 資料表匯出和匯入(qbit)
- 資料庫升級之-資料泵資料庫
- 將資料庫中資料匯入至solr索引庫資料庫Solr索引
- mysql匯出資料MySql
- sqoop資料匯入匯出OOP
- Oracle 資料匯入匯出Oracle
- Oracle資料匯入匯出Oracle
- phpMyAdmin匯入/匯出資料PHP
- 殺停資料泵
- Oracle用資料泵匯入資料包12899的錯誤碼解決方法Oracle
- SQL優化之統計資訊和索引SQL優化索引
- 運維中資料泵匯出常用的細節小功能兩例運維
- 資料泵expdp匯出遇到ORA-01555和ORA-22924問題的分析和處理
- 【STATS】Oracle匯入匯出優化器統計資訊Oracle優化
- PHP大資料xlswriter匯入匯出(最優資料化)PHP大資料
- oracle 備份資料庫,匯出資料庫Oracle資料庫
- 匯出百萬級資料
- MySQL資料的匯出MySql
- layui 資料表格匯出UI
- 6 收集資料庫統計資訊資料庫
- 使用Dbeaver 進行資料的匯入和匯出
- Oracle資料庫(資料泵)遷移方案(上)Oracle資料庫