Oracle数据管理实用脚本整理

蜗牛去旅行吧

已于 2023-05-23 12:17:18 修改

阅读量137

点赞数

分类专栏：数据库管理文章标签： oracle 数据库 sql

于 2023-05-23 12:14:35 首次发布

本文链接：https://blog.csdn.net/weixin_54104864/article/details/130824499

版权

数据库管理专栏收录该内容

9 篇文章 0 订阅

订阅专栏

数据导出:

sql
exp C20201231/C20201231# file=/dbbackup/data/everymonth/C20201231.dmp  
log=/tmp/C20201231.log   
owner=C20181231 buffer=1228800  
statistics=none

file参数指定导出文件的路径和文件名
log参数指定日志文件路径
owner参数指定导出对象的所有者
buffer参数指定缓冲区大小
statistics=none参数关闭统计信息收集以提高性能
数据导入:
sql

imp salaryuser/salary 
file=salary_test.dmp  
log=/tmp/clspuser_test.log
fromuser=clspuser 
touser=clspuser  
buffer=1228800  
statistics=none

file参数指定导入的备份文件路径
log参数指定日志文件路径
fromuser参数指定导出时使用的用户名
touser参数指定导入时使用的用户名
其他参数同导出脚本
数据备份:
sql

expdp C20201231/C20201231#  
full=y
directory=backup_dir 
dumpfile=full%U.dmp

full=y参数指定执行全备,备份整个数据库
directory参数指定备份文件的目录
dumpfile参数指定备份文件的命名模式
常规维护:

truncate table emp;   # 清空emp表数据
alter table emp enable row movement;   
insert into emp (select * from emp_bak);   # 从emp_bak表恢复emp表数据

alter table emp add constraint emp_pk primary key(empno);   # 添加emp表主键

create index emp_ename_i on emp(ename);   # 创建emp表ename字段索引

alter database  
datafile 'D:\DATA\users.dbf' resize 100M;  # 修改users表空间大小为100M

示例：

exp C20201231/C20201231# file=/nfs/data/everymonth/C20201231.dmp  log=/tmp/C20201231.log   owner=C20181231 buffer=1228800  statistics=none   
imp sauser/sa file=sa_test.dmp  log=/tmp/sauser_test.log fromuser=sauser touser=sauser  buffer=1228800  statistics=none

#!/bin/bash
export DISPLAY=:0

if [ -f ~/.bash_profile ];
then
  . ~/.bash_profile
fi

sh /home/oracle/.bash_profile

BACKUP_DAILY_DIR=/dbbackup/backup_daily
Sql_Directory=/home/oracle/scripts

DATE=`date -d '-1 day' +%Y%m%d`
cd ${BACKUP_DAILY_DIR}

sqlplus -s system/dba@AUDITDW @${Sql_Directory}/create_user.sql
FILE_PREFIX=dbbackfile_tabs
USERNAME=isp${DATE}
EXP_FILENAME=${FILE_PREFIX}_${USERNAME}.dmp
EXP_FILELOG=${FILE_PREFIX}_${USERNAME}.log

impdp system/dba@AUDITDW directory=BACKUP_DAILY_DIR remap_schema=ispuser:${USERNAME} dumpfile=${EXP_FILENAME} transform=segment_attributes:n logfile=${EXP_FILELOG} parallel=2 

mv ${BACKUP_DAILY_DIR}/${EXP_FILENAME}  ../history_bak/
mv ${BACKUP_DAILY_DIR}/${EXP_FILELOG}  ../history_bak/

脚本说明:

配置环境变量和bash profiles
定义备份目录路径常量
获取昨天的日期,并作为新用户的后缀
连接数据库并执行创建新用户的SQL脚本
定义数据快照的前缀、新用户名称和导出文件名
使用impdp工具导入数据快照到新用户,remap_schema参数将ispuser用户映射到新用户
将导入生成的导出文件和日志文件移动到历史备份目录
通过cron在指定时间执行该脚本,实现自动定期数据导入
该脚本实现了将ispuser用户的数据导入到按日新建的用户中,可以用于数据的导入和审计

create_user.sql

set serveroutput on
declare 
v_date varchar2(8);
v_sql varchar2(2000);
v_username varchar2(20);
v_userpasswd  varchar2(20);
v_gsql varchar2(200);
begin
 select to_char(sysdate-1,'yyyymmdd') into v_date from dual;
 v_username := 'isp'||v_date;
 v_userpasswd := 'isp'||v_date||'#';
 v_sql := 'create user '||v_username||' identified by '|| v_userpasswd || ' default tablespace ispSP';
 v_gsql := 'grant dba to ' || v_username;
 dbms_output.put_line(v_sql);
 execute immediate v_sql;
 dbms_output.put_line(v_gsql);
 execute immediate v_gsql;
end;
/
exit;

脚本说明:

声明变量并开启输出
获取昨天的日期,并拼接成新用户名和密码
构建创建新用户和授权的SQL语句
打印即将执行的SQL语句
执行创建新用户的语句
打印授权语句
执行授权语句
退出SQL*Plus
该脚本用于创建数据导入的目标用户,并将DBA权限授予该用户。和impdp_isp_data.sh脚本配合使用,实现自动用户创建和数据导入。