select a.owner,a.table_name,a.SEGMENT_NAME,a.COLUMN_NAME,sum(b.bytes)/1024/1024/1024  SIZE_GB from dba_lobs a,dba_segments b
where a.segment_name=b.segment_name
and a.owner=b.owner
and a.owner not in (SYS,SYSTEM,MDSYS,XDB,APEX_040200)
group by a.owner,a.table_name,a.SEGMENT_NAME,a.COLUMN_NAME order by 5;

select owner, tablespace_name,segment_type, segment_name,PARTITION_NAME, sum(bytes) / 1024 / 1024 size_m
from dba_segments
where segment_type = LOBSEGMENT and owner not in (SYS,SYSTEM,XDB,APEX_040200,MDSYS) AND tablespace_name not in (SYSAUX,SYSTEM)
group by owner, tablespace_name,segment_type, segment_name,PARTITION_NAME
order by 6 desc ;

cat > exp_gg_big_XXOWNER_XXTAB_XXSEQ.mod_par
USERID=*****/*******
##FLASHBACK_SCN=15696871445525
(如需要導(dǎo)出相應(yīng)時刻的表數(shù)據(jù)請加上)
DIRECTORY=邏輯目錄
CONTENT=DATA_ONLY
DUMPFILE=exp_gg_bcvbig_XXOWNER_XXTAB_XXSEQ.dmp
LOGFILE=exp_gg_bcvbig_XXOWNER_XXTAB_XXSEQ.log
TABLES=AAAAAA.BBBBBB
QUERY=AAAAAA.BBBBBB:"WHERE rowid IN (SELECT sou_rowid FROM system.seq_test_XXTAB where sb_seq=XXSEQ)" 

cat > create_exp_gg_bcvbig1.sh
. /home/oracle/.profile
#Used by ogg.AUTO EXP WITH SCN
Usage()
{
echo "------------------------------------------------------------"
echo " Usage"
echo " ksh create_exp_gg_bcvbig.sh -o OWNER -t OR_PRTCNTT_202205 &"
echo " Sample:"
echo " ksh create_exp_gg_bcvbig.sh -o DBORDERADM -t OR_PRTCNTT_202205"
echo "------------------------------------------------------------"
}
# Main
if [ $# -ne 4 ]
then
Usage
exit 1
fi
while getopts :o:t: OPTION
do
case $OPTION in
o) V_OWNER=$OPTARG;;
t) V_TABLE_NAME=$OPTARG;;
?) Usage
exit 1;;
esac
done
SQL_FILE=create_seq_test_$V_TABLE_NAME".sql"
cat /dev/null>$SQL_FILE
echo "set time on timing on">>$SQL_FILE
echo "ALTER SESSION FORCE PARALLEL QUERY PARALLEL 8;">>$SQL_FILE
echo "ALTER SESSION FORCE PARALLEL DDL PARALLEL 8;">>$SQL_FILE
echo "ALTER SESSION FORCE PARALLEL DML PARALLEL 8;">>$SQL_FILE
echo "alter session enable parallel ddl;">>$SQL_FILE
echo "alter session enable parallel dml;">>$SQL_FILE
echo "create table system.seq_test_"$V_TABLE_NAME" tablespace 表空間 as select mod(rownum,5#將表切割為多少個#) sb_seq,rowid sou_rowid from "$V_OWNER"."$V_TABLE_NAME" ##as of scn 15696871445525##;">>$SQL_FILE
echo "alter session disable parallel ddl;">>$SQL_FILE
echo "alter table system.seq_test_"$V_TABLE_NAME" noparallel;">>$SQL_FILE
echo "exit">>$SQL_FILE
echo "cat exp_gg_big_XXOWNER_XXTAB_XXSEQ.mod_par|sed s/XXDIR/SOU_EXPA/g|sed s/XXSEQ/00/g|sed s/XXOWNER/"$V_OWNER"/g|sed s/XXTAB/"$V_TABLE_NAME"/g > exp_gg_bcvbig_"$V_TABLE_NAME"_00.par"|sh
echo "cat exp_gg_big_XXOWNER_XXTAB_XXSEQ.mod_par|sed s/XXDIR/SOU_EXPA/g|sed s/XXSEQ/01/g|sed s/XXOWNER/"$V_OWNER"/g|sed s/XXTAB/"$V_TABLE_NAME"/g > exp_gg_bcvbig_"$V_TABLE_NAME"_01.par"|sh
echo "cat exp_gg_big_XXOWNER_XXTAB_XXSEQ.mod_par|sed s/XXDIR/SOU_EXPA/g|sed s/XXSEQ/02/g|sed s/XXOWNER/"$V_OWNER"/g|sed s/XXTAB/"$V_TABLE_NAME"/g > exp_gg_bcvbig_"$V_TABLE_NAME"_02.par"|sh
echo "cat exp_gg_big_XXOWNER_XXTAB_XXSEQ.mod_par|sed s/XXDIR/SOU_EXPA/g|sed s/XXSEQ/03/g|sed s/XXOWNER/"$V_OWNER"/g|sed s/XXTAB/"$V_TABLE_NAME"/g > exp_gg_bcvbig_"$V_TABLE_NAME"_03.par"|sh
echo "cat exp_gg_big_XXOWNER_XXTAB_XXSEQ.mod_par|sed s/XXDIR/SOU_EXPA/g|sed s/XXSEQ/04/g|sed s/XXOWNER/"$V_OWNER"/g|sed s/XXTAB/"$V_TABLE_NAME"/g > exp_gg_bcvbig_"$V_TABLE_NAME"_04.par"|sh

ksh create_exp_gg_bcvbig1.sh -o AAAAAA -t BBBBBB

exp_gg_big_AAAAAA_BBBBBB_00.par
USERID=*****/*******
##FLASHBACK_SCN=15696871445525
(如需要導(dǎo)出相應(yīng)時刻的表數(shù)據(jù)請加上)
DIRECTORY=邏輯目錄
CONTENT=DATA_ONLY
DUMPFILE=exp_gg_big_AAAAAA_BBBBBB_00.dmp
LOGFILE=exp_gg_big_AAAAAA_BBBBBB_00.log
TABLES=AAAAAA.BBBBBB
QUERY=AAAAAA.BBBBBB:"WHERE rowid IN (SELECT sou_rowid FROM ogg.seq_test_BBBBBB where sb_seq=00)" 

nohup expdp parfile=exp_gg_big_AAAAAA_BBBBBB_00.par &（一共5個）