1. 进入控制台
# 用户oracle登录(有些不是这样的可能是因为做了别名)
su - oracle
--如果没有给sys设置密码,先无密码登录
sqlplus / as sysdba
--相当于
sqlplus /nolog
conn / as sysdba
--然后可以给sys或其他用户修改密码
alter user sys identified by sys;
--此时就可以连接sys并以管理员权限登录
conn sys/sys as sysdba
--普通登录: sqlplus 用户/密码@IP/实例名
sqlplus test2023112801/test2023112801@10.120.21.84/orcl
2. 用户和表空间
2.1 创建临时表空间
--1.创建临时表空间,比如命名tempspace01
--1024m是表空间初始大小,
--100m是表空间自动增长大小,
--10240m是表空间最大的大小
create temporary tablespace tempspace01 tempfile '/data/oracle/tampspace01.dbf' size 1024m autoextend on next 100m maxsize 10240m UNLIMITED extent management local;
--2.查看临时表空间信息
SELECT * FROM dba_tablespaces WHERE contents = 'TEMPORARY';
2.2 创建表空间
--1.创建表空间
create tablespace dataspace01 datafile '/data/oracle/dataspace01.dbf' size 1024m autoextend on next 100m maxsize 10240m extent management local;
--2.查看表空间信息
select * from v$tablespace;
2.3 创建用户并关联表空间
--4.创建用户并指定表空间(分开两步的写法)
create user zmj identified by 123456;
alter user zmj default tablespace dataspace01 temporary tablespace tampspace01;
--4.创建用户并指定表空间(合成一句SQL)
create user zmj identified by 123456 default tablespace dataspace01 temporary tablespace tampspace01;
--7.查看用户使用的表空间和临时表空间信息
select username,default_tablespace,temporary_tablespace from dba_users where username = 'ZMJ';
2.4 给用户赋权
--5.给用户授予权限
grant create session,create table,create view,create sequence,unlimited tablespace,connect,resource,dba to zmj;
--6.查询当前用户所具有的权限
select *from session_privs;
2.5 删除用户
--8.删除用户及其相关对象
drop user zmj cascade;
2.6 扩展表空间
---查询表空间
select file_name,tablespace_name,bytes from dba_data_files;
--FILE_NAME
--------------------------------------------------------------------------------
--TABLESPACE_NAME BYTES
------------------------------------------------------------ ----------
--/data/oracle/dataspace01.dbf
--DATASPACE01 1073741824
---增加表空间关联文件
ALTER TABLESPACE dataspace01 ADD DATAFILE '/data/oracle/dataspace01_2.dbf' SIZE
1024m autoextend ON NEXT 1024m maxsize UNLIMITED;
2.7 删除表空间
--删除永久表空间 不删除数据文件
drop tablespace test_tablespace;
--删除永久表空间 包含删除数据文件
drop tablespace test_tablespace including contents and datafiles;
2.8 扩展临时表空间
---根据用户名查询临时表空间名称
select u.temporary_tablespace from dba_users u
where u.username = 'HSF_NEW1216_0321_18';
--TEST_TEMP
--- 增加临时表空间关联文件
ALTER TABLESPACE test_temp ADD tempfile '/oradata/ORCL/test_temp2.dbf' SIZE 1024
m autoextend ON NEXT 1024m maxsize UNLIMITED;
2.9 删除临时表空间
drop tablespace test_temp;
2.10 解决用户账户被锁
su - oracle
sqlplus / as sysdba
alter user yourusername account unlock;
2.11 用户修改密码
--给sys或其他用户修改密码
alter user sys identified by 123456;
2.12 解决用户密码过期限制
-- 查看某个用户下,这个密码是否有限制,LIMIT对应的就是天数限制
select * from dba_profiles where profile='DEFAULT' and resource_name='PASSWORD_LIFE_TIME';
-- PROFILE, RESOURCE_NAME, RESOURCE_TYPE, LIMIT, COMMON, INHERITED, IMPLICIT
-- 1 DEFAULT PASSWORD_LIFE_TIME PASSWORD 180 NO NO NO
-- ACCOUNT_STATUS如果是EXPIRED,就是过期了
select * FROM dba_users where username='用户名大写';
-- 将用户的密码设置为不受限制
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
--如果还是不行,并且还没有生效,或者报新的错误
--那就重新修改下密码,密码还可以是原来的值
alter user 用户名 identified by <原来的密码> account unlock;
3. DMP文件操作
3.1. 创建DMP文件存储目录
su - oracle
cd /home/oracle
mkdir zmjdmpdir
# 赋权限
cd /home/oracle/zmjdmpdir
chmod 755 *.dmp
sqlplus / as sysdba
-- 创建文件导出目录
create or replace directory zmjdmpdir as '/home/oracle/zmjdmpdir';
exit
3.2. 导出dmp数据
--查看oracle存储目录
select * from dba_directories;
--owner directory_name directory_path
--SYS DATA_PUMP_DIR /home/app/oracle/admin/orcl/dpdump/
-- 查看Oracle版本
select * from v$version;
-- 功能:根据用户导出整个数据库
-- schemas 导出操作的用户名
-- directory 导出生成的文件要存放的目录
-- dumpfile 导出生成的文件名称
-- logfile 导出的日志名称
-- version:导出文件的版本号
-- EXCLUDE 排除某些表,注意exclude中接的用户或者表名必须大写
expdp test0320/123456@10.16.210.22:1521/orcl schemas=test0320 directory=DATA_PUMP_DIR
dumpfile=test0320-20230921.dmp logfile=test0320.log version=11.2
EXCLUDE=TABLE:\"IN \(\'MY_TABLE\'\)\"
# 导出某个用户下全部
exp chalco1230/chalco1230 file=/upload/manual_chalco1230.dmp
# 将数据库TEST完全导出,用户名system 密码manager 导出到D:\daochu.dmp中
exp system/manager@TEST file=d:\daochu.dmp full=y
# 将数据库中system用户与sys用户的表导出
exp system/manager@TEST file=d:\daochu.dmp owner=(system,sys)
# 将数据库中的表table1 、table2导出
exp system/manager@TEST file=d:\daochu.dmp tables=(table1,table2)
# 将数据库中的表table1中的字段filed1以"00"打头的数据导出
exp system/manager@TEST file=d:\daochu.dmp tables=(table1) query=\" where filed1 like '00%'\"
# 按表空间导出
expdp system/manager@orcl directory=dump_dir dumpfile=tablespace.dmp tablespaces=temp,example logfile=expdp.log
3.3 导入dmp文件
# 如果是expdb导出的文件
impdp sxy/123456@127.0.0.1:1521/orcl directory=zmjdmpdir dumpfile=z_ssm20220225_.dmp
logfile=impdb.log full=y remap_schema=zmj:sxy remap_tablespace=user:user
exclude=TABLE:\" IN \(\'EMP_TEST\'\)\"
impdp test0802/123456 directory=DATA_PUMP_DIR dumpfile=test0620v3.4.1d20230803.dmp
remap_schema=test0620:idmstest0802 logfile=test0802.log transform=segment_attributes:n
# 如果是exp的
imp sxy/123456@127.0.0.1/orcl directory=zmjdmpdir dumpfile=z_ssm20220225_.dmp full=y
#如果判断用imp还是impdp呢?
# 如果真的不清楚,那么就首先使用impdp
# 如果报错ORA-39143: 转储文件 “.dmp” 可能是原始的导出转储文件,则证明是impdp无法识别exp命令
# 导出的文件,需要使用imp命令进行导入
4. 操作表/序列
4.1 创建表
-- 直接创建一个新表
create table stu (
id number(6) primary key,
name varchar2(20) not null,
gender number(1),
age number(3),
email varchar2(50) unique
);
-- 根据已经存在的某个表创建一个新表,且复制数据
create table SETT_FUNDS_TRANSOPENFIXEDDEPOSIT_COPY as
select * from SETT_FUNDS_TRANSOPENFIXEDDEPOSIT
-- 根据已经存在的某个表创建一个新表,且不复制数据
create table SETT_FUNDS_TRANSOPENFIXEDDEPOSIT_COPY as
select * from SETT_FUNDS_TRANSOPENFIXEDDEPOSIT where 1 = 2;
4.2 创建序列
-- seq_stu 从1开始,每次增加1,无缓存
create sequence seq_stu start with 1 increment by 1 nocache;
4.3 插入表数据
4.3.1 单个插入
-- 单个插入 stu ID根据序列自动增长,唯一性
insert into stu values (seq_stu.nextval,'张三',1,22,'abc@126.com');
4.3.2 批量插入
-- 批量插入
INSERT ALL
INTO stu(id,name,gender,age,email) VALUES (seq_stu.nextval,'张三',1,22,'zhs@126.com')
INTO stu(id,name,gender,age,email) VALUES (seq_stu.nextval + 1,'李四',1,22,'ls@126.com')
INTO stu(id,name,gender,age,email) VALUES (seq_stu.nextval + 2,'赵武',1,22,'zhw@126.com')
SELECT 1 FROM DUAL;
4.3.3 将备份表的数据重新导入到原表
delete from SETT_FUNDS_TRANSOPENFIXEDDEPOSIT;
insert into SETT_FUNDS_TRANSOPENFIXEDDEPOSIT select * from
SETT_FUNDS_TRANSOPENFIXEDDEPOSIT_COPY
4.3.4 将本表的数据抽取一部分再插入,只改id和某个特定字段的值
这里举例为ncurrencyid=1的数据copy出来再次插入时ncurrencyid =18
-- 先copy出来创建一个新表
create table Sett_Glsubjectdefinition_2 as
select * from Sett_Glsubjectdefinition
where nofficeid = 1 and ncurrencyid = 1;
-- 查询原表的最大ID,比如为34512
select max(id) from Sett_Glsubjectdefinition;
-- 创建序列,从34513开始,每次自增1;如果之前已经有创建好的可以直接用原来的
create sequence seq_mytest start with 34513 increment by 1 nocache;
-- 将临时表数据插入原表中
insert into Sett_Glsubjectdefinition (id,Ncurrencyid,Ssegmentname)
select seq_mytest.nextval,18,Ssegmentname
from Sett_Glsubjectdefinition_2;
-- 删除临时序列和临时表
DROP SEQUENCE seq_mytest;
DROP TABLE Sett_Glsubjectdefinition_2;
4.4 添加/删除表字段
-- 添加表字段和注释
alter table loan_AheadRepayForm add isSettAuto number(4);
comment on column loan_AheadRepayForm.isSettAuto is '哈哈哈';
-- 删除表字段
ALTER TABLE tableName DROP COLUMN tableColumnName;
4.5 约束
4.5.1 创建唯一约束
--创建sett_glsetting表的唯一约束,名称为un_abc;对应的表字段为abc
--创建唯一约束会自动创建一个同名的唯一索引
alter table sett_glsetting add constraints un_abc UNIQUE (abc);
4.5.2 删除唯一约束
---删除sett_glsetting表的唯一约束,名称为un_abc;对应的表字段为abc
---删除唯一约束会自动删除一个同名的唯一索引
alter table sett_glsetting drop constraints un_abc;
4.6 索引
4.6.1 创建普通索引
-- 可以单独创建索引 sett_glsetting表的索引,名称为idx_abc;对应的表字段为abc
create index idx_abc on sett_glsetting(abc);
4.6.2 删除索引
--索引是全局的,可以直接指定删除
drop index idx_glvoucheridrule;
4.7. 移除表或序列
declare
num number;
begin
select count(1) into num from user_tables where table_name = upper('sett_TradeAcceptDraftPay');
if num > 0 then
execute immediate 'drop table sett_TradeAcceptDraftPay';
end if;
select count(1) into num from user_sequences where sequence_name = upper('seq_sett_TradeAcceptDraftPay');
if num > 0 then
execute immediate 'drop sequence seq_sett_TradeAcceptDraftPay';
end if;
end;
4.8.更改序列初始值
--- 查询下个序列是34513
select seq_mytest.nextval from dual;---34513
--- 比如,先将每次增加按照+100000
alter sequence seq_mytest increment by 100000;
--- 目前已经变成了你想要的134513
select seq_mytest.nextval from dual;---134513
---再改成原来的每次增加按照+1
alter sequence seq_mytest increment by 1;
--- 目前已经变成了你想要的134514
select seq_mytest.nextval from dual;---134514
5. 视图操作
视图最好设置为只读
1. 在多个人不同权限查看相同的一个或多个表需要显示不同内容时
2. 多个地方用到同样的查询结果,但是很复杂,那么单独做个视图,然后跟其他关联
create or replace view view_sett_glsetting_for_hr as
(
select
id, --员工ID
officeid --办公ID
from sett_glsetting)
with read only;
6. 常用函数
6.1 decode
--decode 相当于if else if else if else
select name,decode(gender,1,'男',2,'女','妖')as gender from stu;
6.2 nvl
-- 如果这个字段为空,则设置一个默认值;nvl2 如果不为空,则为第一个默认值,否则为第二个默认值
select name,nvl(EMAIL,'未填写邮箱')as email from stu;
select name,nvl2(email,'已设置邮箱','未设置邮箱') from stu;
6.3 to_date 字符串转成日期
select to_date('2020-01-01','yyyy-mm-dd') from dual;
---按照日历,查看某一天,在某月的第几周
---比如---在2000-02-01到2055-05-01的区间内 2025-01-28是第5周
with t as
(select rownum-1 rn from dual connect by rownum<=100000),
tt as
(select dateTime-6 as startDate,dateTime endDate,
to_number(TO_CHAR(dateTime,'W')) as whichWeek,rowNum id from
(
select to_date('2000-02-01','yyyy-mm-dd')+rn-7 as dateTime from t
where to_date('2000-02-01','yyyy-mm-dd')+rn-7<=to_date('2055-05-01','yyyy-mm-dd')+7
) b where to_char(b.dateTime, 'day') = '星期日'
)
select
case
when tt.whichWeek=1 and to_number(TO_CHAR(to_date('2025-01-28','yyyy-mm-dd'),'DD'))>22
then (select whichWeek+1 from tt ttt where ttt.id = tt.id-1)
else tt.whichWeek
end as whichWeek
from tt where to_date('2025-01-28','yyyy-mm-dd') >= tt.startDate
and to_date('2025-01-28','yyyy-mm-dd') <= tt.endDate
6.4 to_char 转字符串
--查询当前时间并转为字符串类型
select to_char(sysdate,'yyyy-mm-dd') as nowDate from dual;
--查询时间并转为字符串类型,然后再转为日期类型,目的是去掉时间部分,只留日期部分
--比如时间是2023/8/10 12:23:15; 更新后的结果是2023/8/10
select to_date(to_char(predrawdate,'yyyy-mm-dd'),'yyyy-mm-dd') as nowTime from dual;
update TRANSDIS_BILLAMORTIZATION set predrawdate = to_date(to_char(predrawdate,'yyyy-mm-dd'),'yyyy-mm-dd');
6.5 rownum
当需取前几位的数据,如果其他字段用不上时;但rownum只能是<或=
--查询前十条里面的后9条
select * from
(select stu.*,rownum as sturownum
from stu where rownum <= 10) a
where a.sturownum >= 2;
6.6 With语句查询
--With语句查询,相当于创建一个虚拟的临时表
with
temp_userinfo as (select id,sloginno,spassword from userinfo ),
temp_userinfo2 as (select id,sloginno,spassword from ob_user )
select * from temp_userinfo a,temp_userinfo2 b where a.id = b.id;
6.7 拼接语句 ||
-- 查询所有表字段注释并拼接为可执行sql
select 'comment on column '|| t.table_name || '.' || t.column_name || ' is ''' || t.comments || ''';' from user_col_comments t;
-- 查询所有表注释并拼接为可执行sql
select 'comment on table '|| t.table_name ||' is '''|| t.comments || ''';' from user_tab_comments t;
6.8 按照某字段累计
-- 按照t.ename分组 按照t.deptno,ename排序累计
select t.deptno,
t.ename,
t.sal,
sum(t.sal-t.sal2) over (partition by t.ename order by t.deptno,ename) AccuSal
from emp t
6.9 替换字段对应数据字符串中的某部分
update IDMS_SYS_MENU_RESOURCE set url =
replace(url,'http://172.16.35.88:36871','http://172.16.35.123:31015');
update IDMS_SYS_MENU_RESOURCE set url =
replace(url,'http://172.16.35.132:29130','http://172.16.35.123:29130');
UPDATE its_param SET PARAMVALUE =
replace(PARAMVALUE,'http://172.16.35.132:29130','http://172.16.35.123:29130');
UPDATE its_param p SET p.PARAMVALUE =
replace(PARAMVALUE,'http://172.16.35.88:36871','http://172.16.35.123:31015');
7. PLSQL过程化语言 Oracle自己的编程语言
7.1 plsql变量常量的使用
declare
--变量声明,包含静态赋值 / 动态赋值 / into指定
v_abc varchar2(20) := 'xiaoming';
v_bcd number(3) := &请输入年龄;
v_def varchar2(30);
--变量可以动态绑定某个表的字段
v_name stu.name%type;
--变量还可以动态绑定整个表的字段
v_stu stu%rowtype;
-- 常量声明
v_country constant varchar2(10) := 'China';
begin
--业务逻辑
dbms_output.put_line(v_abc || '----' || v_bcd);
select email into v_def from stu where id = 2;
dbms_output.put_line(v_def);
select name into v_name from stu where id = 2;
dbms_output.put_line(v_name);
select * into v_stu from stu where id = 2;
dbms_output.put_line(v_stu.name || '--' || v_stu.age || '--' || v_stu.email);
dbms_output.put_line(v_country);
exception
when NO_DATA_FOUND then
dbms_output.put_line('数据没有找到!');
end;
7.2 plsql中的流程控制
declare
v_stu stu%rowtype;
v_sql varchar2(100);
v_id stu.id%type := &请输入学生ID;
exception_age_invalid exception; --自定义异常
begin
v_sql := 'select * from stu where id=:id';
execute immediate v_sql
into v_stu
using v_id;
-- if else语句
if v_stu.age > 180 then
raise exception_age_invalid;
elsif v_stu.age > 60 then
dbms_output.put_line(v_stu.name || '--' || v_stu.age || ',该退休了!');
elsif v_stu.age > 18 then
dbms_output.put_line(v_stu.name || '--' || v_stu.age || ',可以工作!');
else
dbms_output.put_line(v_stu.name || '--' || v_stu.age || ',未成年!');
end if;
--case when 语句
case v_stu.gender
when 1 then
dbms_output.put_line(v_stu.name || '是男生!');
when 2 then
dbms_output.put_line(v_stu.name || '是女生!');
else
dbms_output.put_line(v_stu.name || '是男生!');
end case;
--异常处理 包括自定义异常和预定义异常
exception
when exception_age_invalid then
dbms_output.put_line('年龄无效!');
when NO_DATA_FOUND then
dbms_output.put_line('数据没有找到!');
end;
7.3 游标 cursor
declare
v_name stu.name%type := '&请输入学生姓名:';
v_stu stu%rowtype;
cursor cursor_query_all(stu_name varchar2) is select * from stu where name = stu_name;
begin
open cursor_query_all(v_name);
loop
fetch cursor_query_all into v_stu;
if cursor_query_all%found then
dbms_output.put_line(v_stu.name || ',年龄是' || v_stu.age);
else
exit;
end if;
end loop;
close cursor_query_all;
-- 如果是 for循环,会自动开启关闭游标,相对简单些
for v_stu in cursor_query_all(v_name) loop
dbms_output.put_line(v_stu.name || ',年龄是' || v_stu.age);
end loop;
end;
7.4 存储过程 procedure
7.4.1 创建一个存储过程
create or replace procedure pro_test111(
-- IN是输入参数 OUT是输出参数
p_name IN varchar2,
p_age IN number,
p_result IN OUT varchar2
) is
begin
dbms_output.put_line(p_name || p_age);
if 5 > 3 then
p_result := 'success';
end if;
end;
8.表的常用操作
8.1 表被误Drop掉进行恢复
--模拟误操作Drop,删除了一张表test2
drop table test2;
--查询test2表在回收站的对象
SELECT OBJECT_NAME,ORIGINAL_NAME,TYPE,CREATETIME,DROPTIME FROM RECYCLEBIN A
WHERE A.ORIGINAL_NAME = 'TEST2';
--BIN$+bwZwQTUAIzgU6wQCzIAjA==$0 TEST2 TABLE 2023-02-21:14:54:16 2023-04-20:10:18:54
--闪回到Drop之前
flashback table "BIN$+bwZwQTTAIzgU6wQCzIAjA==$0" to before drop;
8.2 遇到死锁
select * from v$session t1, v$locked_object t2 where t1.sid = t2.SESSION_ID;
--SID:9
--SERIAL#:56110
alter system kill session '9,56110';
8.3 表数据被误Delete掉进行恢复
--根据关键字查询到删除时的时间点
select SQL_TEXT,Last_active_time from v$sql where sql_text like '%模糊查询的关键字%';
--select SQL_TEXT,Last_active_time from v$sql where sql_text like '%10202211170200008%';
--Result:
--SQL_TEXT delete from fe_transforeignexchange where stransno = '10202211170200008'
--LAST_ACTIVE_TIME 2023/8/22 10:21:26
--根据时间点恢复,比如2023-08-22 10:21:26 表名是fe_transforeignexchange
insert into fe_transforeignexchange (select * from fe_transforeignexchange as of timestamp to_timestamp('2023-08-22 10:21:26','yyyy-mm-dd hh24:mi:ss'));
10.常用查询
10.1 查询某个表的所有表字段名称或字段个数
---查询表字段的个数
select count(column_name) from user_tab_columns where TABLE_NAME = '表的名字';
---查询表字段的所有字段,并显示为小写
select lower(column_name) from user_tab_columns where TABLE_NAME = '表的名字';
10.2 查看占用表空间最大的表
---通过此SQL可以看到占用表空间最大的表
select t.owner,t.segment_name,t.tablespace_name,
bytes/1024/1024/1024 sizeGB,q.num_rows
from dba_segments t
left join dba_tables q
on t.segment_name=q.table_name
and t.owner=q.owner
where t.segment_type='TABLE'
--and t.tablespace_name='TS_SPACE' --需要查看的表空间
order by 4 desc;
-- HGJX1221 BS_SYSTEMLOG ITREASURY 14.79296875 89053581
-- SYS WRH$_SYSMETRIC_HISTORY SYSAUX 2.375 65380
-- HGJX1221 BS_ACCTHISTRANSINFO ITREASURY 0.9453125 4579695
-- SYS WRH$_SQL_PLAN SYSAUX 0.515625 71902
10.3 查询某个用户下所有表占用大小
SELECT OWNER as "用户名", sum(BYTES) / 1024 / 1024 as "所有表的大小(MB)"
FROM DBA_SEGMENTS
WHERE
OWNER = 'HGJXTEST230416' ----用户名称
and SEGMENT_NAME in (select t2.OBJECT_NAME
from dba_objects t2
where t2.OBJECT_TYPE = 'TABLE')
group by OWNER order by 2 desc;
10.4 查询每个表空间占用情况
SELECT D.tablespace_name,
SPACE
|| 'M' "SUM_SPACE(M)",
blocks "SUM_BLOCKS",
SPACE - Nvl(free_space, 0)
|| 'M' "USED_SPACE(M)",
Round(( 1 - Nvl(free_space, 0) / SPACE ) * 100, 2)
|| '%' "USED_RATE(%)",
free_space
|| 'M' "FREE_SPACE(M)"
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(+);
10.5 查询每个临时表空间占用情况
SELECT D.tablespace_name,
SPACE
|| 'M' "SUM_SPACE(M)",
blocks SUM_BLOCKS,
used_space
|| 'M' "USED_SPACE(M)",
Round(Nvl(used_space, 0) / SPACE * 100, 2)
|| '%' "USED_RATE(%)",
Nvl(free_space, 0)
|| 'M' "FREE_SPACE(M)"
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_name,
Round(SUM(bytes_used) / ( 1024 * 1024 ), 2) USED_SPACE,
Round(SUM(bytes_free) / ( 1024 * 1024 ), 2) FREE_SPACE
FROM v$temp_space_header
GROUP BY tablespace_name) F
WHERE D.tablespace_name = F.tablespace_name(+)
ORDER BY 1;
11.通过定时任务+dblink+存储过程传数据到中间库
11.1 确定临时表目录
# 1.先看看有没有 放临时表 的目录,没有创建下:或者改放其他目录
cd /usr/local/oracle/oradata
# 2.将目录所有者换成oracle
chown -R oracle /usr/local/oracle/
# 3.切换oracle用户,并管理员身份进入
su - oracle
sqlplus / as sysdba
11.2 创建用户表空间授权
--- 4.创建用户
create user middle_user identified by middle_user;
--- 5.创建表空间
create tablespace ts_middle_user datafile '/usr/local/oracle/oradata/middle_user_data.dbf' size 6000M autoextend on;
--- 6.用户关联表空间
alter user middle_user default tablespace ts_middle_user;
--- 7.给用户访问授权
grant create session,create table,create view,create sequence,unlimited tablespace,connect,resource,dba to middle_user;
11.3 创建monitor目录
# 8.用Oracle用户创建对应 monitor目录,存放每次执行完毕的结果文件,验证每次导入是否正确
cd /usr/local/oracle
mkdir monitor
# 9.如果monitor目录不属于以下用户,请执行
# select * from all_directories
chown -R oracle:oinstall monitor/
-- 10.登录middle_user用户
sqlplus middle_user/middle_user
-- 11.登录middle_user用户后,创建目录MONITOR
create or replace directory monitor as '/usr/local/oracle/monitor';
--12. 在中间库执行建立dblink的脚本,远程链接(用户、密码、IP、端口、实例 以实际为准)
CREATE DATABASE LINK dblink_finance
CONNECT TO 用户 IDENTIFIED BY 密码
USING '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = IP地址)(PORT = 端口号))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = 实例名称)
)
)';
-- 13.验证是否已经关联
select * from userinfo@dblink_finance;
-- 14.在中间库创建表结构(以实际为准)
create table F_CREDIT_AMOUNTSETUP as
select *
from CREDIT_AMOUNTSETUP@dblink_finance;
comment on table F_CREDIT_AMOUNTSETUP
is 'aaaa详情表';
comment on column F_CREDIT_AMOUNTSETUP.id
is '主键';
comment on column F_CREDIT_AMOUNTSETUP.creditcode
is 'aaaa编号';
create table F_LOAN_DISCOUNTCONTRACTBILL
as select * from LOAN_DISCOUNTCONTRACTBILL@dblink_finance;
-- Add comments to the table
comment on table F_LOAN_DISCOUNTCONTRACTBILL
is 'bbbb表';
comment on column F_LOAN_DISCOUNTCONTRACTBILL.nofficeid
is '办事处';
---15. 将指定数据的增量从主库推送到中间库
CREATE OR REPLACE PROCEDURE PROCEDURE_FINANCE_TO_MIDDLE
is ---将指定数据的增量从主库推送到中间库
v_day_7 date;
v_day_30 date;
v_now varchar(30);
V_FILE UTL_FILE.FILE_TYPE;
V_INPUT CLOB;
BEGIN
select to_char(sysdate, 'yyyy-MM-dd_HH24:mi:ss') into v_now from dual;
V_FILE := UTL_FILE.FOPEN('MONITOR',
'FINANCE_TO_MIDDLE_' || v_now || '.txt',
'W');
select (sysdate - 7) into v_day_7 from dual;
select (sysdate - 30) into v_day_30 from dual;
delete from F_CREDIT_AMOUNTSETUP;
insert into F_CREDIT_AMOUNTSETUP
select * from CREDIT_AMOUNTSETUP@dblink_finance;
commit;
delete from F_LOAN_DISCOUNTCONTRACTBILL where DTEND > v_day_30;
insert into F_LOAN_DISCOUNTCONTRACTBILL
select *
FROM LOAN_DISCOUNTCONTRACTBILL@dblink_finance a
where not exists
(select id from F_LOAN_DISCOUNTCONTRACTBILL b where a.id = b.id);
commit;
---- 这里是对结果进行校验,如果输出的表对应值为0,则正常,否则数据丢失
FOR I IN (
select diff || ' ' || 'F_CREDIT_AMOUNTSETUP' result
from (select ((select count(*) as count1 from F_CREDIT_AMOUNTSETUP) -
(select count(*) as count2
from CREDIT_AMOUNTSETUP@dblink_finance)) as diff
from dual) a
union all
select diff || ' ' || 'F_LOAN_DISCOUNTCONTRACTBILL' result
from (select ((select count(*) as count1
from F_LOAN_DISCOUNTCONTRACTBILL) -
(select count(*) as count2
from LOAN_DISCOUNTCONTRACTBILL@dblink_finance)) as diff
from dual) a
) LOOP
UTL_FILE.PUT_LINE(V_FILE, I.RESULT);
END LOOP;
UTL_FILE.FCLOSE(V_FILE);
EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Exception happened,all data was rollback');
ROLLBACK;
END;
---16. 首先先执行一次,看看存储过程写的是否正确
--- 如果执行成功,会有success,并且会生成日志文件
exec PROCEDURE_FINANCE_TO_MIDDLE;
--17.先生成定时任务
declare
JOB_FINANCE_TO_MIDDLE number; /*JOB_FINANCIAL_TO_MIDDLE:定时器名称*/
BEGIN
DBMS_JOB.SUBMIT(
JOB => JOB_FINANCE_TO_MIDDLE, /*自动生成JOB_ID*/
WHAT => 'PROCEDURE_FINANCE_TO_MIDDLE;', /*需要执行的存储过程名称或SQL语句*/
--NEXT_DATE => sysdate+3/(24*60), /*初次执行时间-下一个3分钟*/
--INTERVAL => 'trunc(sysdate,''mi'')+100/(24*60)' /*每隔100分钟执行一次*/
INTERVAL => 'TRUNC(SYSDATE + 1) + (20*60+10)/(24*60)' /*每天晚上8点10分*/
);
commit;
end;
---18. 查询定时ID,比如找到对应的ID=23
SELECT * FROM user_jobs;
-- 19. 开启定时
begin
dbms_job.run(23); --开启
commit;
end;
---20. 如果遇到数据没有增量更新,也就是定时任务没有运行
select value from v$parameter where name like '%job_queue_processes%';
---21. 如果小的话,改成100,很可能是因为这个job值太小造成定时任务不运行
alter system set job_queue_processes =100;
持续更新中......