目录一Oracle数据泵导入导出dmp一设置目录和操作权限二导入三导出二案例-Oracle 数据清理一为什么大数据量不能用delete删除二选择使用rename重建表方式1综上所述选择rename方式把历史数据进行备份2删除索引会与原历史表的重复3重新创建历史表并按月添加分区3.1oracle按时间戳TIMESTAMP类型按月自动分区3.2oracle按时间DATE类型按月自动分区3.3oracle按字符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_NAMEEXPDP_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_NAMEIMPDMP 3.对导出的.dmp做目录目录上的调整不建议直接使用标准目录 #应programmer_ada同学提议修改 #expdp目录需额外创建根据自身业务调整 create directory EXPDP_PATH as /home/oracle/expdp; 如果该目录不使用建议删除 drop directory EXPDP_PATH;二导入username 用户名password 密码 database 数据库名称#导入整个用户的表 impdp username/passworddatabase directoryIMPDMP dumpfiledumpName.dmp;三导出同样需要文件授权 ; username 用户名password 密码 database 数据库名称expdp username/passworddatabasedumpfile22.dmp logfiletables.log directoryIMPDMP; #按表有条件导出 expdp username/passworddatabase directoryIMPDMP dumpfile22.dmp logfile22.log tabletableName query\t where t.id1\;二案例-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_nameTB_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_namec.table_name)查看oracle中表的索引oracle中表的索引信息存在 user_indexes 和 user_ind_columns 两张表里面其中user_indexes 系统视图存放是索引的名称以及该索引是否是唯一索引等信息user_ind_columns 统视图存放的是索引名称对应的表和列等select* from all_indexes where table_nameACM_NETWORK_OPERATION; select * from user_ind_columns where table_nameACM_NETWORK_OPERATION;3重新创建历史表并按月添加分区3.1oracle按时间戳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.2oracle按时间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.3oracle按字符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 directoryNameEXPDP_PATH echo expdp start tableNametest dumapFiledate %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 $3EOF test.sql EOF #impdp echo impdp start logFileImpdpdate %Y-%m-%d_$tableName_impdp.log impdp $userPass数据库实例 directory$directoryName dumpfile$dumapFile logfile$logFile ignorey 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后面可以加入维持空间数量所以可以分若干个窗口期进行回收。3drop备注二分步分阶段大表删除转自https://www.cnblogs.com/lcword/p/11553125.html但我经过实测发现并没有太大的差距最后选择了先truncate 后drop的方式1用个用reuse storege无明显区别不释放空间不删除表的情况下之前表占用的空间只能供该表使用2reuse 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_namex.tablespace_name order by USED_RATE_percent desc
