点击上方“IT那活儿”公众号,关注后了解更多内容,不管IT什么活儿,干就完了!!!
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
(如需要导出相应时刻的表数据请加上)
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
--o为属主,t为表名。
exp_gg_big_AAAAAA_BBBBBB_00.par
USERID=*****/*******
##FLASHBACK_SCN=15696871445525
(如需要导出相应时刻的表数据请加上)
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个)
注意:生成的5个脚本需要依次执行不能同时执行。
文章版权归作者所有,未经允许请勿转载,若此文章存在违规行为,您可以联系管理员删除。
转载请注明本文地址:https://www.ucloud.cn/yun/129370.html
摘要:数据库多模是指同一个数据库支持多个存储引擎,可以同时满足应用程序对于结构化半结构化非结构化数据的统一管理需求。多模式数据管理能力,使得数据库能够进行跨部门跨业务的数据统一存储与管理,实现多业务数据融合,支撑多样化的应用服务。 如今,随着业务互联网化和智能化的发展以及架构 微服务和云化的发展,应用系统对数据的存储管理提出了新的标准和要求,数据的多样性成为了数据库平台面临的一大挑战,数据库...
摘要:图元数据与数据文件结构映射在建立集合的过程当中,大对象存储必须依附于普通集合存在,一个集合中的大对象仅归属于该集合,不能被另外一个集合管理。 前言 企业内容管理(Enterprise Content Management,ECM)系统是一种管理非结构化内容的系统,传统代表为EMC Documentum或IBM Filenet等ECM解决方案。随着大数据技术的越发普及,越来越多的客户开始...
阅读 1347·2023-01-11 13:20
阅读 1685·2023-01-11 13:20
阅读 1133·2023-01-11 13:20
阅读 1860·2023-01-11 13:20
阅读 4101·2023-01-11 13:20
阅读 2705·2023-01-11 13:20
阅读 1386·2023-01-11 13:20
阅读 3598·2023-01-11 13:20