Tham số SAMPLE trong expdp chỉ định tỷ lệ phần trăm các hàng dữ liệu được lấy mẫu và xuất từ cơ sở dữ liệu nguồn. Điều này là cần thiết khi bạn muốn thực hiện kiểm thử với ít dữ liệu hơn trong môi trường thử nghiệm, nơi việc kiểm thử không khả thi với khối lượng dữ liệu lớn từ môi trường sản xuất.
Tỷ lệ phần trăm mẫu chỉ cho biết xác suất một hàng được chọn làm mẫu. Điều đó không có nghĩa là cơ sở dữ liệu sẽ truy xuất chính xác số lượng hàng đó từ bảng.
SAMPLE= <schema_name>.<table_name>:<sample_percent>
OR
SAMPLE= <sample_percent>
Giá trị phần trăm mẫu có thể nằm trong khoảng từ 0.000001 đến 100 (nhưng không bao gồm 100). Nếu bạn nhập 100%, bạn sẽ nhận được thông báo lỗi. Ngoài ra, lưu ý rằng tham số SAMPLE không hợp lệ đối với xuất dữ liệu qua mạng.
Hãy cùng xem xét các ví dụ dưới đây:
Bảng test.emp chứa 5 hàng.
SQL> select * from test.emp;
ID NAME ADDRESS SALARY
---- ------------ ------------ ----------
1 Rupesh Navi Mumbai 5000
2 Jaideep Navi Mumbai 6000
3 Prashant Mumbai 7000
4 Sagar Mumbai 8000
5 Jigun Mumbai 9000
#Export the table without sample parameter.
C:\Windows\System32>expdp directory=DIR_TEST tables=test.emp dumpfile=table.dmp logfile=table.log
Export: Release 19.0.0.0.0 - Production on Sat Apr 13 07:08:04 2024
Version 19.16.0.0.0
Copyright (c) 1982, 2022, Oracle and/or its affiliates. All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "SYS"."SYS_EXPORT_TABLE_01": /******** AS SYSDBA directory=DIR_TEST tables=test.emp dumpfile=table.dmp logfile=table.log
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
. . exported "TEST"."EMP" 6.531 KB 5 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
C:\BACKUP\TABLE.DMP
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Sat Apr 13 07:08:24 2024 elapsed 0 00:00:17
#Export the table with sample percentage 10. You can see that zero% data rows exported as 10% does not satisfy any data.
C:\Windows\System32>expdp directory=DIR_TEST tables=test.emp dumpfile=table.dmp logfile=table.log sample=10
Export: Release 19.0.0.0.0 - Production on Sat Apr 13 07:09:21 2024
Version 19.16.0.0.0
Copyright (c) 1982, 2022, Oracle and/or its affiliates. All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "SYS"."SYS_EXPORT_TABLE_01": /******** AS SYSDBA directory=DIR_TEST tables=test.emp dumpfile=table.dmp logfile=table.log sample=10
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
. . exported "TEST"."EMP" 6.398 KB 0 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
C:\BACKUP\TABLE.DMP
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Sat Apr 13 07:09:40 2024 elapsed 0 00:00:16
#Export the table with sample percentage 20. Here, only one row exported.
C:\Windows\System32>expdp directory=DIR_TEST tables=test.emp dumpfile=table.dmp logfile=table.log sample=20
Export: Release 19.0.0.0.0 - Production on Sat Apr 13 07:08:45 2024
Version 19.16.0.0.0
Copyright (c) 1982, 2022, Oracle and/or its affiliates. All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "SYS"."SYS_EXPORT_TABLE_01": /******** AS SYSDBA directory=DIR_TEST tables=test.emp dumpfile=table.dmp logfile=table.log sample=20
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
. . exported "TEST"."EMP" 6.429 KB 1 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
C:\BACKUP\TABLE.DMP
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Sat Apr 13 07:09:04 2024 elapsed 0 00:00:16
#Export the table with sample percentage 30. Here, again only 1 row exported with 30%.
C:\Windows\System32>expdp directory=DIR_TEST tables=test.emp dumpfile=table.dmp logfile=table.log sample=30
Export: Release 19.0.0.0.0 - Production on Sat Apr 13 07:15:52 2024
Version 19.16.0.0.0
Copyright (c) 1982, 2022, Oracle and/or its affiliates. All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "SYS"."SYS_EXPORT_TABLE_01": /******** AS SYSDBA directory=DIR_TEST tables=test.emp dumpfile=table.dmp logfile=table.log sample=30
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
. . exported "TEST"."EMP" 6.429 KB 1 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
C:\BACKUP\TABLE.DMP
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Sat Apr 13 07:16:11 2024 elapsed 0 00:00:16
#Export the table with sample percentage 40.
C:\Windows\System32>expdp directory=DIR_TEST tables=test.emp dumpfile=table.dmp logfile=table.log sample=40
Export: Release 19.0.0.0.0 - Production on Sat Apr 13 07:15:18 2024
Version 19.16.0.0.0
Copyright (c) 1982, 2022, Oracle and/or its affiliates. All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "SYS"."SYS_EXPORT_TABLE_01": /******** AS SYSDBA directory=DIR_TEST tables=test.emp dumpfile=table.dmp logfile=table.log sample=40
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
. . exported "TEST"."EMP" 6.453 KB 2 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
C:\BACKUP\TABLE.DMP
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Sat Apr 13 07:15:37 2024 elapsed 0 00:00:16
#Export the table with sample percentage 50.
C:\Windows\System32>expdp directory=DIR_TEST tables=test.emp dumpfile=table.dmp logfile=table.log sample=50
Export: Release 19.0.0.0.0 - Production on Sat Apr 13 07:11:32 2024
Version 19.16.0.0.0
Copyright (c) 1982, 2022, Oracle and/or its affiliates. All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "SYS"."SYS_EXPORT_TABLE_01": /******** AS SYSDBA directory=DIR_TEST tables=test.emp dumpfile=table.dmp logfile=table.log sample=50
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
. . exported "TEST"."EMP" 6.507 KB 4 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
C:\BACKUP\TABLE.DMP
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Sat Apr 13 07:11:51 2024 elapsed 0 00:00:16
#Export the table with sample percentage 60.
C:\Windows\System32>expdp directory=DIR_TEST tables=test.emp dumpfile=table.dmp logfile=table.log sample=60
Export: Release 19.0.0.0.0 - Production on Sat Apr 13 07:21:22 2024
Version 19.16.0.0.0
Copyright (c) 1982, 2022, Oracle and/or its affiliates. All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "SYS"."SYS_EXPORT_TABLE_01": /******** AS SYSDBA directory=DIR_TEST tables=test.emp dumpfile=table.dmp logfile=table.log sample=60
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
. . exported "TEST"."EMP" 6.453 KB 2 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
C:\BACKUP\TABLE.DMP
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Sat Apr 13 07:21:41 2024 elapsed 0 00:00:16
#Export the table with sample percentage 80. Here, only 3 rows exported though the sample percentage is 80%.
C:\Windows\System32>expdp directory=DIR_TEST tables=test.emp dumpfile=table.dmp logfile=table.log sample=80
Export: Release 19.0.0.0.0 - Production on Sat Apr 13 07:12:45 2024
Version 19.16.0.0.0
Copyright (c) 1982, 2022, Oracle and/or its affiliates. All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "SYS"."SYS_EXPORT_TABLE_01": /******** AS SYSDBA directory=DIR_TEST tables=test.emp dumpfile=table.dmp logfile=table.log sample=80
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
. . exported "TEST"."EMP" 6.476 KB 3 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
C:\BACKUP\TABLE.DMP
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Sat Apr 13 07:13:05 2024 elapsed 0 00:00:16
#Export the table with sample percentage 90. Here, only 3 rows exported though the sample percentage is 90%.
C:\Windows\System32>expdp directory=DIR_TEST tables=test.emp dumpfile=table.dmp logfile=table.log sample=90
Export: Release 19.0.0.0.0 - Production on Sat Apr 13 07:13:47 2024
Version 19.16.0.0.0
Copyright (c) 1982, 2022, Oracle and/or its affiliates. All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "SYS"."SYS_EXPORT_TABLE_01": /******** AS SYSDBA directory=DIR_TEST tables=test.emp dumpfile=table.dmp logfile=table.log sample=90
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
. . exported "TEST"."EMP" 6.484 KB 3 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
C:\BACKUP\TABLE.DMP
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Sat Apr 13 07:14:07 2024 elapsed 0 00:00:16
#Export the table with sample percentage 100. Please note that 100% will not accept the value. You will face below error message if you give 100% in sample.
C:\Windows\System32>expdp directory=DIR_TEST tables=test.emp dumpfile=table.dmp logfile=table.log sample=100
Export: Release 19.0.0.0.0 - Production on Sat Apr 13 07:13:25 2024
Version 19.16.0.0.0
Copyright (c) 1982, 2022, Oracle and/or its affiliates. All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
ORA-39001: invalid argument value
ORA-39163: A sample size of 100 is invalid.
#Export the schema with sample percentage 40 for specific table. You can give sample percentage for specific table within the schema while taking schema export.
C:\Windows\System32>expdp directory=DIR_TEST schemas=test dumpfile=SCHEMA.dmp logfile=SCHEMA.log SAMPLE="TEST"."EMP":40
Export: Release 19.0.0.0.0 - Production on Sat Apr 13 07:23:11 2024
Version 19.16.0.0.0
Copyright (c) 1982, 2022, Oracle and/or its affiliates. All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "SYS"."SYS_EXPORT_SCHEMA_01": /******** AS SYSDBA directory=DIR_TEST schemas=test dumpfile=SCHEMA.dmp logfile=SCHEMA.log SAMPLE=TEST.EMP:40
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
. . exported "TEST"."DEPT" 6.531 KB 5 rows
. . exported "TEST"."CUSTOMERS" 0 KB 0 rows
. . exported "TEST"."PROMOTIONS" 0 KB 0 rows
. . exported "TEST"."EMP" 6.421 KB 1 rows
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
C:\BACKUP\SCHEMA.DMP
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Sat Apr 13 07:23:51 2024 elapsed 0 00:00:36
TƯ VẤN: Click hoặc Hotline/Zalo 090.29.12.888
=============================
Website không chứa bất kỳ quảng cáo nào, mọi đóng góp để duy trì phát triển cho website (donation) xin vui lòng gửi về STK 90.2142.8888 - Ngân hàng Vietcombank Thăng Long - TRAN VAN BINH
=============================
Nếu bạn không muốn bị AI thay thế và tiết kiệm 3-5 NĂM trên con đường trở thành DBA chuyên nghiệp hay làm chủ Database thì hãy đăng ký ngay KHOÁ HỌC ORACLE DATABASE A-Z ENTERPRISE, được Coaching trực tiếp từ tôi với toàn bộ bí kíp thực chiến, thủ tục, quy trình của gần 20 năm kinh nghiệm (mà bạn sẽ KHÔNG THỂ tìm kiếm trên Internet/Google) từ đó giúp bạn dễ dàng quản trị mọi hệ thống Core tại Việt Nam và trên thế giới, đỗ OCP.
- CÁCH ĐĂNG KÝ: Gõ (.) hoặc để lại số điện thoại hoặc inbox https://m.me/tranvanbinh.vn hoặc Hotline/Zalo 090.29.12.888
- Chi tiết tham khảo:
https://bit.ly/oaz_w
=============================
2 khóa học online qua video giúp bạn nhanh chóng có những kiến thức nền tảng về Linux, Oracle, học mọi nơi, chỉ cần có Internet/4G:
- Oracle cơ bản: https://bit.ly/admin_1200
- Linux: https://bit.ly/linux_1200
=============================
KẾT NỐI VỚI CHUYÊN GIA TRẦN VĂN BÌNH:
📧 Mail: binhoracle@gmail.com
☎️ Mobile/Zalo: 0902912888
👨 Facebook: https://www.facebook.com/BinhOracleMaster
👨 Inbox Messenger: https://m.me/101036604657441 (profile)
👨 Fanpage: https://www.facebook.com/tranvanbinh.vn
👨 Inbox Fanpage: https://m.me/tranvanbinh.vn
👨👩 Group FB: https://www.facebook.com/groups/DBAVietNam
👨 Website: https://www.tranvanbinh.vn
👨 Blogger: https://tranvanbinhmaster.blogspot.com
🎬 Youtube: https://www.youtube.com/@binhguru
👨 Tiktok: https://www.tiktok.com/@binhguru
👨 Linkin: https://www.linkedin.com/in/binhoracle
👨 Twitter: https://twitter.com/binhguru
👨 Podcast: https://www.podbean.com/pu/pbblog-eskre-5f82d6
👨 Địa chỉ: Tòa nhà Sun Square - 21 Lê Đức Thọ - Phường Mỹ Đình 1 - Quận Nam Từ Liêm - TP.Hà Nội
=============================
cơ sở dữ liệu, cơ sở dữ liệu quốc gia, database, AI, trí tuệ nhân tạo, artificial intelligence, machine learning, deep learning, LLM, ChatGPT, DeepSeek, Grok, oracle tutorial, học oracle database, Tự học Oracle, Tài liệu Oracle 12c tiếng Việt, Hướng dẫn sử dụng Oracle Database, Oracle SQL cơ bản, Oracle SQL là gì, Khóa học Oracle Hà Nội, Học chứng chỉ Oracle ở đầu, Khóa học Oracle online,sql tutorial, khóa học pl/sql tutorial, học dba, học dba ở việt nam, khóa học dba, khóa học dba sql, tài liệu học dba oracle, Khóa học Oracle online, học oracle sql, học oracle ở đâu tphcm, học oracle bắt đầu từ đâu, học oracle ở hà nội, oracle database tutorial, oracle database 12c, oracle database là gì, oracle database 11g, oracle download, oracle database 19c/21c/23c/23ai, oracle dba tutorial, oracle tunning, sql tunning , oracle 12c, oracle multitenant, Container Databases (CDB), Pluggable Databases (PDB), oracle cloud, oracle security, oracle fga, audit_trail,oracle RAC, ASM, oracle dataguard, oracle goldengate, mview, oracle exadata, oracle oca, oracle ocp, oracle ocm , oracle weblogic, postgresql tutorial, mysql tutorial, mariadb tutorial, ms sql server tutorial, nosql, mongodb tutorial, oci, cloud, middleware tutorial, docker, k8s, micro service, hoc solaris tutorial, hoc linux tutorial, hoc aix tutorial, unix tutorial, securecrt, xshell, mobaxterm, putty
