目录
一，Oracle数据泵导入导出dmp
（一）设置目录和操作权限
（二）导入
（三）导出
二，案例-Oracle 数据清理
（一）为什么大数据量不能用delete删除
（二）选择使用rename重建表方式
1，综上所述选择rename方式把，历史数据进行备份；
2，删除索引（会与原历史表的重复）；
3，重新创建历史表，并按月添加分区；
3.1，oracle按时间戳（TIMESTAMP）类型按月自动分区
3.2，oracle按时间DATE类型按月自动分区
3.3，oracle按字符VARCHAR2类型字段按月自动分区
3.4 查看分区数据
（三）sh脚本
三，删除历史表
（一）先truncate 后drop删除历史表
（二）分步分阶段大表删除
（三）观察表空间大小
一，Oracle数据泵导入导出dmp
（一）设置目录和操作权限
1.登录root权限，然后切换到Oracle用户下； su - oracle; 2.登录dba权限数据库； sqlplus sys/123456 as sysdba; 2.1 检查目录是否存在 #select * from dba_directories where DIRECTORY_NAME='EXPDP_PATH'； 3.连接成功后。创建目录、后面引号部分是你的dmp文件所在的路径。必须放到Oracle文件下面。路径不要有中文； #应programmer_ada同学提议修改 #expdp目录需额外创建，根据自身业务调整 create directory EXPDP_PATH as '/home/oracle/expdp'; 4.授权成功后。把读写IMPDMP目录权限赋予给cim 数据库用户； grant read,write on directory IMPDMP to cim; 5.退出数据库 exit; 6.如果提示没有directory权限 grant create any directory to oracle; grant drop any dirctory to oracle; 7.如果该目录不使用，建议删除 drop directory EXPDP_PATH; 或者覆盖 create or replace针对2.1 ，3步骤的优化
2.1 检查目录是否存在 #select * from dba_directories where DIRECTORY_NAME='IMPDMP'； 3.对导出的.dmp做目录目录上的调整，不建议直接使用：标准目录 #应programmer_ada同学提议修改 #expdp目录需额外创建，根据自身业务调整 create directory EXPDP_PATH as '/home/oracle/expdp'; 如果该目录不使用，建议删除 drop directory EXPDP_PATH;（二）导入
username 用户名，password 密码 database 数据库名称
#导入整个用户的表 impdp username/password@database directory=IMPDMP dumpfile=dumpName.dmp;（三）导出
同样需要文件授权 ; username 用户名，password 密码 database 数据库名称
expdp username/password@databasedumpfile=22.dmp logfile=tables.log directory=IMPDMP; #按表，有条件导出 expdp username/password@database directory=IMPDMP dumpfile=22.dmp logfile=22.log table=tableName query=\"t where t.id=1\";二，案例-Oracle 数据清理
（一）为什么大数据量不能用delete删除
delete删除是逐条，需要进行事务提交，数据量大的时候，耗时会非常长，还会造成undo表空间不足。
Oracle undo表空间
当我们对数据库进行DML操作的时候，Oracle能通过undo为我们提供保存修改前数据的镜像。
1、提供读一致性
例如用户A修改表TB1，在他进行操作前Oracle将TB1存放于undo中，在用户A修改的同时用户B若访问TB1，则访问的是undo中的TB1，其他访问TB1的用户也是如此，因此在用户A修改TB1的时候，undo保证了其他用户对TB1的读一致性。
2、为事务提供回滚
通过保留在undo中的镜像来恢复用户未提交的数据。当用户未提交数据，而此时数据库发生异常关闭，undo中保留的镜像会自动将数据回滚。
3、提供闪回操作
保留在undo中的镜像在事务结束后并未马上删除，而是保留了一段时间，在这段时间内用户即便已经提交了事务，仍可以通过 flashback 技术查询。
（二）选择使用rename重建表方式
把原有表改为历史表，然后把表中对应索引也删除重建；
对原有表,索引重建，并添加分区。
1，综上所述选择rename方式把，历史数据进行备份；
rename table_his to h_table_his;2，删除索引（会与原历史表的重复）；
主建索引和唯一索引无法通过rename方式修改。
重命名索引 唯一索引不能重命名，所以主键需要删除并重新创建 alter index index_sno rename to bitmap_index; 索引删除 --查询主外键，表名一定要大写 select constraint_name from user_constraints where table_name = 'TB_SCENERY'; select constraint_name from user_constraints where table_name = 'TB_SCENERY_TICKETS'; --删除主外键，注意大小写 alter table TB_SCENERY drop constraint SCENERY_PK_ID; alter table TB_SCENERY_TICKETS drop constraint SCENERY_ID; --增加主键(增加主键时会自动建立关于主键的索引) alter table TB_SCENERY add constraint SCENERY_PK_ID primary key (ID) ; --增加外键 alter table TB_SCENERY_TICKETS add constraint SCENERY_PK_ID foreign key (SCENERY_ID) references TB_SCENERY (ID); --查询表的相关索引 select index_name,index_type,table_name from user_indexes where table_name='TB_SCENERY'; --删除索引[强制] DROP INDEX SCENERY_PK_ID [FORCE]; --查询哪些表没有建立索引 SELECT table_name FROM User_tables t WHERE NOT EXISTS (SELECT table_name FROM User_constraints c WHERE constraint_type = 'P' AND t.table_name=c.table_name)查看oracle中表的索引
oracle中表的索引信息存在 user_indexes 和 user_ind_columns 两张表里面，
其中
user_indexes 系统视图存放是索引的名称以及该索引是否是唯一索引等信息，
user_ind_columns 统视图存放的是索引名称，对应的表和列等
select* from all_indexes where table_name='ACM_NETWORK_OPERATION'; select * from user_ind_columns where table_name='ACM_NETWORK_OPERATION';3，重新创建历史表，并按月添加分区；
3.1，oracle按时间戳（TIMESTAMP）类型按月自动分区
参考：oracle按时间戳创建分区_ma_anjun的博客-CSDN博客_oracle创建时间分区
CREATE TABLE TEST1 ( ID NUMBER (20) NOT NULL, CREATEDATE TIMESTAMP (6) ) PARTITION BY RANGE (CREATEDATE) INTERVAL (numtoyminterval(1, 'month')) ( PARTITION P2020028213030 VALUES LESS THAN ( to_TIMESTAMP('2020-02-28 21:30:30','yyyy-mm-dd hh24:mi:ss') ) );3.2，oracle按时间DATE类型按月自动分区
create test2( ID NUMBER(20) not null, create_time DATE ) PARTITIONBY RANGE (CREATE_TIME) INTERVAL (numtoyminterval(1, 'month')) (partition part_t01values less than(to_date('2018-11-01', 'yyyy-mm-dd')));3.3，oracle按字符VARCHAR2类型字段按月自动分区
不应将日期或时间值存储为字符串，请始终使用正确的DATE或TIMESTAMP数据类型。
作为解决方法，您可以使用如下虚拟列：
CREATE TABLE TEST3( ADD_DATE VARCHAR2(6), ADD_DATE_TIMESTAMP TIMESTAMP(0) GENERATED ALWAYS AS (TO_TIMESTAMP(ADD_DATE, 'YYYYMM')), ) PARTITION BY RANGE (ADD_DATE_TIMESTAMP ) INTERVAL (NUMTOYMINTERVAL(1, 'MONTH')) (PARTITION P_MAY2021 VALUES LESS THAN (TIMESTAMP '2022-10-10 00:00:00'));3.4 查看分区数据
select * from test partition(p1)（三）sh脚本
test.sql 与该脚本放在同一目录下
#!/bin/bash # sh 脚本名称.sh 20221017 20221018 '账号/密码' startT=$1 endT=$2 userPass=$3 directoryName=EXPDP_PATH echo "expdp start " tableName=test dumapFile="`date +%Y-%m-%d`_"$tableName".dmp" logFile ="`date +%Y-%m-%d`_"$tableName"_expdp.log" #特殊符号加\转义 expdp $userPass@数据库实例 directory=$directoryName dumpfile=$dumapFile logfile=$logFiletables=$tableName query=\"where to_char\(add_time,\'yyyymmdd\'\)\>\'$startT\' and to_char\(add_time,\'yyyymmdd\'\)\<=\'$endT\'\" echo "expdp end " #sql sqlplus $3<<EOF @test.sql EOF #impdp echo "impdp start " logFileImpdp="`date +%Y-%m-%d`_"$tableName"_impdp.log" impdp $userPass@数据库实例 directory=$directoryName dumpfile=$dumapFile logfile=$logFile ignore=y echo "impdp end "三，删除历史表
（一）先truncate 后drop删除历史表
先清空，在drop，减少日志，增加效率
truncate table tableName; drop table tableName;（二）分步分阶段大表删除
总的指导原则是先删除数据，不影响数据一致性的要求，之后分阶段分步骤的进行空间回收。避免一次性drop对系统造成过大的压力。
步骤：
1、首先使用带reuse storage子句的truncate table，将数据删除。
reuse storage子句的作用是单纯降低数据段data segment的高水位线，对分配的空间不进行回收。这样truncate操作不涉及到空间回收，速度是可以接受的。
2、分若干次数，使用deallocate unused keep XXX的方法，将分配的空间回收。
因为keep后面可以加入维持空间数量，所以可以分若干个窗口期进行回收。
3，drop
备注：
（二）分步分阶段大表删除转自：
https://www.cnblogs.com/lcword/p/11553125.html
但我经过实测，发现并没有太大的差距，最后选择了先truncate 后drop的方式
1，用个用reuse storege无明显区别；（不释放空间，不删除表的情况下，之前表占用的空间只能供该表使用）
2，reuse storege之后不deallocete对drop的影响，不释放空间，drop时间反而变长。
（三）观察表空间大小
select t.tablespace_name,x.contents tablespace_type,t.SUM_SPACE_mb,t.SUM_BLOCKS,t.USED_SPACE_mb,t.USED_RATE_percent,t.FREE_SPACE_mb from (SELECT D.TABLESPACE_NAME,SPACE SUM_SPACE_mb,BLOCKS SUM_BLOCKS,SPACE - NVL(FREE_SPACE, 0) USED_SPACE_mb,ROUND((1 - NVL(FREE_SPACE, 0) / SPACE) * 100, 2) USED_RATE_percent,FREE_SPACE FREE_SPACE_mb FROM (SELECT TABLESPACE_NAME,ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE,SUM(BLOCKS) BLOCKS FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) D,(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES) / (1024 * 1024), 2) FREE_SPACE FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+) UNION ALL SELECT D.TABLESPACE_NAME,SPACE SUM_SPACE_mb,BLOCKS SUM_BLOCKS,USED_SPACE USED_SPACE_mb,ROUND(NVL(USED_SPACE, 0) / SPACE * 100, 2) USED_RATE_percent,SPACE - USED_SPACE FREE_SPACE_mb FROM (SELECT TABLESPACE_NAME,ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE,SUM(BLOCKS) BLOCKS FROM DBA_TEMP_FILES GROUP BY TABLESPACE_NAME) D,(SELECT TABLESPACE,ROUND(SUM(BLOCKS * 8192) / (1024 * 1024), 2) USED_SPACE FROM V$tempseg_USAGE GROUP BY TABLESPACE) F WHERE D.TABLESPACE_NAME = F.TABLESPACE(+)) t,dba_tablespaces x where t.tablespace_name=x.tablespace_name order by USED_RATE_percent desc
