oracle

ecoolper

已于 2024-01-18 21:23:15 修改

阅读量54

点赞数 1

文章标签： oracle 数据库

于 2023-10-13 11:28:43 首次发布

本文链接：https://blog.csdn.net/weixin_49032281/article/details/133807247

版权

本文介绍了如何在Oracle数据库中执行备份操作，包括使用exp工具备份多表数据，以及执行多表关联更新(MERGE)和根据分组规则获取最新数据的SQL查询。同时提及了时间点查询，如to_date函数和闪回查询功能。

摘要由CSDN通过智能技术生成

exp

exp username/password@localhost:1521/x5 tables=(sa_task) recordlength=65535 buffer=104857600 file=D:\oracle\sa_task_20231011.dmp log=d:\oracle\sa_task_20231011.log

多表关联更新

MERGE INTO a a
	USING b b ON
(b.code = a.code )
WHEN MATCHED THEN 
	UPDATE
SET a.name = b.name
WHERE b.fid IS NOT NULL;

根据分组规则获取最新数据

SELECT a.* FROM table a
	WHERE 0=(SELECT count(1) from table a1 WHERE a1.parentId=a.parentId and a1.startDate >a.startDate);

exp

rem oracle数据库备份

echo off
set user=
set pwd=
set homeDir=%~dp0
set currentDateTime=%date:~0,4%%date:~5,2%%date:~8,2%-%time:~0,2%%time:~3,2%%time:~6,2%
set currentDateTime=%currentDateTime: =0%

set tables=SA_CHATPROMISSION,SA_CODE,SA_DOCAUTH,SA_DOCLINK,SA_DOCLINKDEFINE,SA_DOCLOG,SA_DOCNAMESPACE ^
,SA_DOCNODE,SA_FILTERPATTERN,SA_KVSEQUENCE,SA_MESSAGEPARAMETER,SA_OPAGENT,SA_OPAUTHORIZE ^
,SA_OPCUSTOMGROUP,SA_OPDEPT,SA_OPMANAGEMENT,SA_OPMANAGETYPE,SA_OPORG,SA_OPPARENTROLE,SA_OPPERMISSION ^
,SA_OPPERSON,SA_OPROLE,SA_OPROLEMANAGEMENT,SA_PORTAL2PROFILEMANAGER,SA_PORTAL2PROFILES,SA_PORTALPROFILES ^
,SA_PROCESSCONTROL,SA_PROCESSTEMPLATE,SA_QUICKSTART,SA_REMIND,SA_REMINDACTIONPARAMETER,SA_REMINDCONFIG ^
,SA_REMINDER,SA_RESOURCECONTROL,SA_RUNTIME,SA_SHORTURL,SA_SOCIAL,SA_TASKRELATION,SA_TASKTYPE ^
,SA_TERMINAL,SA_THEMEDEFINE,SA_THEMEMANAGER,SA_TOKENINSTANCE,SA_TASK


exp %user%/%pwd%@127.0.0.1:1521/mis ^
tables=(%tables%) ^
file=%homeDir%sa_%currentDateTime%.dmp ^
log=%homeDir%sa_%currentDateTime%.log ^
buffer=65535

to_date

select to_date('2023-11-15 17:45:37','yyyy-mm-dd hh24:mi:ss') from dual

flashback

select * from 表名 as of timestamp to_timestamp('2023-12-12 10:46:00', 'yyyy-mm-dd hh24:mi:ss'); select count(*) from fad01 as of timestamp (systimestamp - interval '1'minute);