Mir Sayeed Hassan – Oracle Blog

How to perform the export a percentage (%) of data from the schema tables in Oracle 19C Database

Brief: Consider i have a requirement of the export of data in the form of percentage, Such as i need to export only 50% or 25% of data from my tables

The parameter: SAMPLE can be used in Export (expdp) only & cannot use it for Import (impdp)

Let us create a sample schema and few tables along with data & gather the statistics for accurate.

Check the status of database

SYS> select name, version, open_mode from V$database, v$instance;

NAME         VERSION          OPEN_MODE
----------------------------------------
ORA19CDB    19.0.0.0.0       READ WRITE

SYS> show pdbs

CON_ID     CON_NAME     OPEN MODE      RESTRICTED
--------------------------------------------------
2          PDB$SEED     READ ONLY        NO
4          PDB_TEST1    READ WRITE       NO

Connect to PDB Database

SYS> alter session set container=PDB_TEST1;
Session altered.

SYS> show pdbs

CON_ID    CON_NAME      OPEN MODE      RESTRICTED
--------------------------------------------------
4          PDB_TEST1    READ WRITE         NO

SQL> create user mir identified by mir123 default tablespace TESTING_TBS;
User created.

SQL> alter user mir quota unlimited on TESTING_TBS;
User altered.

SQL> grant connect, resource to mir;
Grant succeeded.

SQL> create table mir.test1 as select * from dba_objects;
Table created.

SQL> select count(*) from mir.test1;

COUNT(*)
----------
73301

SQL> create table mir.test2 as select * from user_objects;
Table created.

SQL> select count(*) from mir.test2;

COUNT(*)
----------
52802

Gather Statistics & Verify

SQL> BEGIN
DBMS_STATS.GATHER_SCHEMA_STATS(
ownname => 'MIR',
cascade => TRUE,
estimate_percent => NULL,
block_sample => FALSE,
degree 2 => 4,
no_invalidate => FALSE
);
END;
/

PL/SQL procedure successfully completed.

Verify

SQL> set linesize 300
SQL> select num_rows, table_name from dba_tab_statistics where owner='MIR';

NUM_ROWS    TABLE_NAME
-------------------------
73301       TEST1
52802       TEST2

Create a Parfile as shown below

[oracle@oracle19c-test dump]$ vi export_percentage_data.par
userid=system@test
directory=dump
dumpfile=mir_12feb25.dmp
logfile=mir_12feb25.log
schemas=mir
sample=50

Check the parfile

[oracle@oracle19c-test dump]$ cat export_percentage_data.par
userid=system@test
directory=dump
dumpfile=mir_12feb25.dmp
logfile=mir_12feb25.log
schemas=mir
sample=50

Start the expdp with 50% of data.

[oracle@oracle19c-test dump]$ expdp parfile=export_percentage_data.par

Export: Release 19.0.0.0.0 - Production on Wed Feb 12 15:57:47 2025
Version 19.25.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Password:

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/********@test parfile=export_percentage_data.par
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
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/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
. . exported "MIR"."TEST1" 4.801 MB 36355 rows
. . exported "MIR"."TEST2" 3.418 MB 26149 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/u01/dump/mir_12feb25.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Wed Feb 12 15:59:53 2025 elapsed 0 00:01:55

Hence as per the expected result are shows, it was exported 36355 rows of mir.test1 table (about 50% of 73301) and 26149 rows of mir.test2 table (about 50% of 52802)., Simillary you can try with any 50% as per your requirement.

=====Tested and Verified in our test env=====