Oracle常用命令

朱梦君

已于 2024-04-08 14:41:04 修改

阅读量6.3k

点赞数 2

分类专栏： Oracle 文章标签：数据库

于 2020-01-21 16:45:16 首次发布

本文链接：https://blog.csdn.net/qq_30426943/article/details/104063318

版权

Oracle 专栏收录该内容

5 篇文章 2 订阅

订阅专栏

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;

持续更新中......