oracle 12c数据库分区
访问量:1699
创建时间:2023-03-14
分区的原因
某些表中存放的业务日志相关的数据量越来越大，delete删除历史数据后表空间不释放，需要不断添加表空间文件，缩表的操作相对效率低，不降低水位线，缩表的有些操作还会锁表;数据库大了后，rman备份非常尴尬，没有空间。从数据量大查询日志速度非常慢。
分区的好处，可以按照分区导出数据，清空分区降低对表空间的占用。rman可以再次派上用场；查询速度会提高；(如果有需要删除历史数据可以按照分区导出单独备份，如果需要查询，可以再导入回去,因为业务系统不是公司自研发的，所以不太方便部署一套系统用于历史数据查询)
[oracle@abcdprdpdb expdbdir]$ df -h
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/centos-root 80G 38G 43G 48% /
devtmpfs 32G 0 32G 0% /dev
tmpfs 32G 16G 16G 50% /dev/shm
tmpfs 32G 74M 32G 1% /run
tmpfs 32G 0 32G 0% /sys/fs/cgroup
/dev/sda1 497M 159M 339M 32% /boot
/dev/mapper/data-oracle 1.4T 1.1T 332G 77% /oracle
tmpfs 6.3G 44K 6.3G 1% /run/user/0
准备工作
1、增加undo表空间到64G,(2个表空间文件，方式导出数据时报错，过程省略) 2、设置环境变量 字符集 export NLS_LANG=AMERICAN_AMERICA.AL32UTF8 3、设置DUMP_DIR ，检查select * from dba_directories where DIRECTORY_NAME='DUMP_DIR'; (4、检查分区select table_name,partition_name from user_tab_partitions where table_name='MY_LOG_KEY';)
脚本准备
分区过程：
1、导出3个月的数据 2、TRUNCATE TABLE 并按照时间分区 3、导入数据
导出数据脚本
[oracle@abcdprdpdb expdbdir]$ cat MY_LOG_KEY20230101-20230401.txt
directory=dump_dir
dumpfile=MY_LOG_KEY20230101-20230401.dmp
tables=MY_LOG_KEY
query=MY_LOG_KEY:"where CREATION_DATE>=to_date('2023-01-01','yyyy-mm-dd') and CREATION_DATE<=to_date('2023-04-01','yyyy-mm-dd')"
[oracle@abcdprdpdb expdbdir]$ cat MY_LOG_KEY_expdp.sh
expdp qwer/qwer@192.168.0.99:1521/abcdprdpdb parfile=MY_LOG_KEY20230101-20230401.txt
truncate表并分区的脚本
[oracle@abcdprdpdb expdbdir]$ cat MY_LOG_KEY_split_parition.sql
TRUNCATE TABLE LGDS.MY_LOG_KEY;
ALTER TABLE LGDS.MY_LOG_KEY SPLIT PARTITION LOGINITED AT (TO_DATE(' 2023-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')) INTO (PARTITION LOGINITED20230401,PARTITION LOGINITED);
ALTER TABLE LGDS.MY_LOG_KEY SPLIT PARTITION LOGINITED AT (TO_DATE(' 2023-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')) INTO (PARTITION LOGINITED20230801,PARTITION LOGINITED);
ALTER TABLE LGDS.MY_LOG_KEY SPLIT PARTITION LOGINITED AT (TO_DATE(' 2023-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')) INTO (PARTITION LOGINITED20231201,PARTITION LOGINITED);
ALTER TABLE LGDS.MY_LOG_KEY SPLIT PARTITION LOGINITED AT (TO_DATE(' 2024-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')) INTO (PARTITION LOGINITED20240401,PARTITION LOGINITED);
ALTER TABLE LGDS.MY_LOG_KEY SPLIT PARTITION LOGINITED AT (TO_DATE(' 2024-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')) INTO (PARTITION LOGINITED20240801,PARTITION LOGINITED);
ALTER TABLE LGDS.MY_LOG_KEY SPLIT PARTITION LOGINITED AT (TO_DATE(' 2024-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')) INTO (PARTITION LOGINITED20241201,PARTITION LOGINITED);
ALTER TABLE LGDS.MY_LOG_KEY SPLIT PARTITION LOGINITED AT (TO_DATE(' 2025-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')) INTO (PARTITION LOGINITED20250401,PARTITION LOGINITED);
ALTER TABLE LGDS.MY_LOG_KEY SPLIT PARTITION LOGINITED AT (TO_DATE(' 2025-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')) INTO (PARTITION LOGINITED20250801,PARTITION LOGINITED);
ALTER TABLE LGDS.MY_LOG_KEY SPLIT PARTITION LOGINITED AT (TO_DATE(' 2025-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')) INTO (PARTITION LOGINITED20251201,PARTITION LOGINITED);
ALTER TABLE LGDS.MY_LOG_KEY SPLIT PARTITION LOGINITED AT (TO_DATE(' 2026-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')) INTO (PARTITION LOGINITED20260401,PARTITION LOGINITED);
ALTER TABLE LGDS.MY_LOG_KEY SPLIT PARTITION LOGINITED AT (TO_DATE(' 2026-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')) INTO (PARTITION LOGINITED20260801,PARTITION LOGINITED);
ALTER TABLE LGDS.MY_LOG_KEY SPLIT PARTITION LOGINITED AT (TO_DATE(' 2026-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')) INTO (PARTITION LOGINITED20261201,PARTITION LOGINITED);
ALTER TABLE LGDS.MY_LOG_KEY SPLIT PARTITION LOGINITED AT (TO_DATE(' 2027-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')) INTO (PARTITION LOGINITED20270401,PARTITION LOGINITED);
ALTER TABLE LGDS.MY_LOG_KEY SPLIT PARTITION LOGINITED AT (TO_DATE(' 2027-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')) INTO (PARTITION LOGINITED20270801,PARTITION LOGINITED);
ALTER TABLE LGDS.MY_LOG_KEY SPLIT PARTITION LOGINITED AT (TO_DATE(' 2027-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')) INTO (PARTITION LOGINITED20271201,PARTITION LOGINITED);
ALTER TABLE LGDS.MY_LOG_KEY SPLIT PARTITION LOGINITED AT (TO_DATE(' 2028-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')) INTO (PARTITION LOGINITED20280401,PARTITION LOGINITED);
ALTER TABLE LGDS.MY_LOG_KEY SPLIT PARTITION LOGINITED AT (TO_DATE(' 2028-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')) INTO (PARTITION LOGINITED20280801,PARTITION LOGINITED);
ALTER TABLE LGDS.MY_LOG_KEY SPLIT PARTITION LOGINITED AT (TO_DATE(' 2028-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')) INTO (PARTITION LOGINITED20281201,PARTITION LOGINITED);
ALTER TABLE LGDS.MY_LOG_KEY SPLIT PARTITION LOGINITED AT (TO_DATE(' 2029-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')) INTO (PARTITION LOGINITED20290401,PARTITION LOGINITED);
ALTER TABLE LGDS.MY_LOG_KEY SPLIT PARTITION LOGINITED AT (TO_DATE(' 2029-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')) INTO (PARTITION LOGINITED20290801,PARTITION LOGINITED);
ALTER TABLE LGDS.MY_LOG_KEY SPLIT PARTITION LOGINITED AT (TO_DATE(' 2029-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')) INTO (PARTITION LOGINITED20291201,PARTITION LOGINITED);
ALTER TABLE LGDS.MY_LOG_KEY SPLIT PARTITION LOGINITED AT (TO_DATE(' 2030-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')) INTO (PARTITION LOGINITED20300401,PARTITION LOGINITED);
ALTER TABLE LGDS.MY_LOG_KEY SPLIT PARTITION LOGINITED AT (TO_DATE(' 2030-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')) INTO (PARTITION LOGINITED20300801,PARTITION LOGINITED);
ALTER TABLE LGDS.MY_LOG_KEY SPLIT PARTITION LOGINITED AT (TO_DATE(' 2030-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')) INTO (PARTITION LOGINITED20301201,PARTITION LOGINITED);
commit;
导入数据脚本
[oracle@abcdprdpdb expdbdir]$ cat MY_LOG_KEY_impdp.sh
echo $NLS_LANG
export NLS_LANG=american_america.AL32UTF8
echo $NLS_LANG
impdp qwer/qwer@192.168.0.99:1521/abcdprdpdb directory=dump_dir dumpfile=MY_LOG_KEY20230101-20230401.dmp TABLE_EXISTS_ACTION=TRUNCATE
登陆评论:
使用GITHUB登陆