oracle相关知识
1 导入和导出
1.1 导出expdp
expdp 用户名/密码 schemas=username1,username2 directory=MYDATA dumpfile=expdp%U_yf.dmp logfile=expdp_yf.log filesize=20g parallel=8 cluster=no #按照用户名导出
expdp 用户名/密码 schemas=laity directory=MYDATA dumpfile=expdp%U_laity_yf.dmp logfile=expdp_laity_yf.log filesize=20g （排除这些表用exclude）EXCLUDE=TABLE:\"IN\(\'tablename1\'\,\'tablename2\'\,\'tablename3\'\)\" parallel=8 cluster=no #按照用户导出排除不需要的表
exp 用户名/密码 tables=(TableA,TableB) file=/export/home/billing/a.dmp log=/export/home/billing/a.log #按照表名导出
参数：
content={all|data_only|metadata_only}当设置content=all时,将导出对象定义及其所有数据;当content=data_only时,只导出对象数据;当content=metadata_only时,只导出对象定义;
dumpfile #指定转储文件的名称
directory #指定转储文件的目录 select * from dba_directories;
estimate_only #用于指定是否估算导出作业所占的磁盘空间，默认值为N estimate_only={Y|N}
EXCLUDE #用于指定导出时的排出对象类型或相关对象
INCLUDE #用于包含导出时的对象类型或相关对象
filesize #导出文件的大小默认0，无限制
full #导出数据库默认N full={y|n}
logfile #指定导出日志的名称
parallel #操作并行进程数 一般是cpu的2倍，可以被文件个数整除
remap_tablespace #更改表空间名称
1.2 导入impdp
impdp 用户名/密码 schemas=username directory=expbak dumpfile=expdp%U_laity_yf.dmp logfile=impdp_laity_yf.log REMAP_TABLESPACE=tablespace_name1:tablespace_name2 parallel=8
1.3 expdp导出需注意
expdp导出时需注意两边数据库版本
（1）低版本（oracle 10g 10.2.0.1.0）-->高版本（oracle 11g 11.2.0.1.0）
可以
（2）高版本（oracle 11g 11.2.0.1.0）-->低版本（oracle 10g 10.2.0.1.0）
可以，不过需要添加条件，在导出时需要使用指定版本参数version=10.2.0.1.0
1.4 终止导出进程
select job_name,state from dba_datapump_jobs; --查询存在的进程，如果任务运行状态为state=”executing“,则表示正在执行
-->expdp 用户名/密码 ATTACH=SYS_EXPORT_SCHEMA_16 --SYS_EXPORT_SCHEMA_16 代表的是 state=”executing“ 的job_name
-->stop_job=immediate
-->yes
2 表空间和数据文件相关
2.1 创建临时表空间
create temporary tablespace user_temp
tempfile 'C:\app\Administrator\oradata\orcl\kc_temp.dbf'
size 50m
autoextend on
next 50m maxsize 20480m
extent management local;
2.2 查询表空间使用情况
SELECT Upper(F.TABLESPACE_NAME) "表空间名",
D.TOT_GROOTTE_MB "表空间大小(M)",
D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
To_char(Round(( D.TOT_GROOTTE_MB - F.TOTAL_BYTES ) / D.TOT_GROOTTE_MB * 100, 2), '990.99')
|| '%' "使用比",
F.TOTAL_BYTES "空闲空间(M)",
F.MAX_BYTES "最大块(M)"
FROM (SELECT TABLESPACE_NAME,
Round(Sum(BYTES) / ( 1024 * 1024 ), 2) TOTAL_BYTES,
Round(Max(BYTES) / ( 1024 * 1024 ), 2) MAX_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
Round(Sum(DD.BYTES) / ( 1024 * 1024 ), 2) TOT_GROOTTE_MB
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME;
2.3 数据文件增加
第一种方式：
alter tablespace tablespacename add datafile '/laity/app/oracle/datafile/tablespacename15.dbf' size 2g;
第二种方式：表空间增加数据文件，设置自增长，限制最大值
alter tablespace tablespacename add datafile '/laity/app/oracle/datafile/tablespacename1515.dbf' size 500M autoextend on maxsize 3072M;
2.4 更改数据文件大小（已存在表空间数据文件重新设置大小）
alter dataspace datafile '/laity/app/oracle/datafile/tablespacename15.dbf' resize 3072M;
3 txt文本导入oracle数据库
创建 sql2.ctl
load data
infile 'cc.txt' --cc.txt为要导入的txt文本
replace into table user.ZT_AAA2021 --user.zt_aaa2021为要导入的数据库表
(id char terminated by ',',
name char terminated by ',',
sex char terminated by whitespace) --id,name,sex为字段
进入到放置*.ctl的目录，执行以下命令：
sqlldr userid=用户名/密码@数据库连接串 control=sql2.ctl log=sqlldr2.log
4 oracle函数
4.1 sysdate函数
select sysdate,add_months(sysdate,-12) from dual; --减1年
select sysdate,add_months(sysdate,-1) from dual; --减1月
select sysdate,sysdate-7 from dual; --减1星期
select sysdate,sysdate-1 from dual; --减1天
select sysdate,sysdate-1/24 from dual; --减1小时
select sysdate,sysdate-1/24/60 from dual; --减1分钟
select sysdate,sysdate-1/24/60/60 from dual; --减1秒钟
4.2 exists函数
exists用户检查子查询是否返回至少一行数据，该子查询实际上并不会返回任何数据，而是返回true和false
4.3 union与union all函数
union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；
union all：对两个结果集进行并集操作，包括重复行，不进行排序；
4.4 interval函数
（1）、INTERVAL YEAR TO MONTH数据类型
Oracle语法:
INTERVAL 'integer [- integer]' {YEAR | MONTH} [(precision)][TO {YEAR | MONTH}]
该数据类型常用来表示一段时间差, 注意时间差只精确到年和月. precision为年或月的精确域, 有效范围是0到9, 默认值为2.
eg:
INTERVAL '123-2' YEAR(3) TO MONTH
表示: 123年2个月, "YEAR(3)" 表示年的精度为3, 可见"123"刚好为3为有效数值, 如果该处YEAR(n), n<3就会出错, 注意默认是2.
INTERVAL '123' YEAR(3)
表示: 123年0个月
INTERVAL '300' MONTH(3)
表示: 300个月, 注意该处MONTH的精度是3啊.
INTERVAL '4' YEAR
表示: 4年, 同 INTERVAL '4-0' YEAR TO MONTH 是一样的
INTERVAL '50' MONTH
表示: 50个月, 同 INTERVAL '4-2' YEAR TO MONTH 是一样
INTERVAL '123' YEAR
表示: 该处表示有错误, 123精度是3了, 但系统默认是2, 所以该处应该写成 INTERVAL '123' YEAR(3) 或"3"改成大于3小于等于9的数值都可以的
（2）、INTERVAL DAY TO SECOND数据类型
Oracle语法:
INTERVAL '{ integer | integer time_expr | time_expr }'
{ { DAY | HOUR | MINUTE } [ ( leading_precision ) ]
| SECOND [ ( leading_precision [, fractional_seconds_precision ] ) ] }
[ TO { DAY | HOUR | MINUTE | SECOND [ (fractional_seconds_precision) ] } ]
leading_precision值的范围是0到9, 默认是2. time_expr的格式为:HH[:MI[:SS[.n]]] or MI[:SS[.n]] or SS[.n], n表示微秒.
范围值:
HOUR: 0 to 23
MINUTE: 0 to 59
SECOND: 0 to 59.999999999
eg:
INTERVAL '4 5:12:10.222' DAY TO SECOND(3)
表示: 4天5小时12分10.222秒
INTERVAL '4 5:12' DAY TO MINUTE
表示: 4天5小时12分
INTERVAL '400 5' DAY(3) TO HOUR
表示: 400天5小时, 400为3为精度,所以"DAY(3)", 注意默认值为2.
INTERVAL '400' DAY(3)
表示: 400天
INTERVAL '11:12:10.2222222' HOUR TO SECOND(7)
表示: 11小时12分10.2222222秒
INTERVAL '11:20' HOUR TO MINUTE
表示: 11小时20分
INTERVAL '10' HOUR
表示: 10小时
INTERVAL '10:22' MINUTE TO SECOND
表示: 10分22秒
INTERVAL '10' MINUTE
表示: 10分
INTERVAL '4' DAY
表示: 4天
INTERVAL '25' HOUR
表示: 25小时
4.5 字符串函数
4.5.1 trim函数
trim一般都是删除字符串两边的空格；也可以用来删除字符串两边的字符(指定删除的字符串只能是一个字符)
4.5.1.1 去除字符串两边空格
select trim (' DWEYE ') from dual;
select trim (both from ' DWEYE ') from dual; --两者效果一样
4.5.1.2 单独去除字符串左右两边空格
select trim (trailing from ' DWEYE ') from dual; --去除后边空格
select trim (leading from ' DWEYE ') from dual; --去除前边空格
4.5.1.3 去除字符串两边的字符
select trim ('x' from 'xxxDWEYExxx') from dual;
select trim (both 'x' from 'xxxDWEYExxx') from dual;
4.5.1.4 单独去除字符串两边的字符
select trim (trailing 'x' from 'xxxDWEYExxx') from dual;
select trim (leading 'x' from 'xxxDWEYExxx') from dual;
4.5.1.5 去除字符串两边的多个字符--拓展
select rtrim('xyxDWEYExyx','xy') from dual; --需要注意，这里去除xy，不是'xy'连接一起，而是只要是x,y；都会去除
select ltrim('xyxDWEYExyx','xy') from dual;
select ltrim(rtrim('xyxDWEYExyx','xy'),'xy') from dual;
4.5.2 translate函数
translate函数将字符串转化成需要的文本；translate(字符串,'要转换的字符串','转换成什么')
4.5.2.1 正常转换
select translate('abcdef','ab','**') from dual;
4.5.2.2 当“要转换的字符串”不在对象（列/字符串）里面时，不会报错，原封不动输出
select translate('abcdef','gh','**') from dual;
4.5.2.3 当“要转换的字符串”多于“转换成什么”时，多出来的部分是没有替换效果的
select translate('abcdef','abc','*') from dual;
4.5.2.4 当“要转换的字符串”少于“转换成什么”时，少的部分不会自动填补
select translate('abcdef','abc','*****') from dual;
其他
描述 INTERVAL参数值
每天午夜12点 TRUNC(SYSDATE + 1)
每天早上8点30分 TRUNC(SYSDATE + 1) +（8*60+30）/(24*60)
每星期二中午12点 NEXT_DAY(TRUNC(SYSDATE ),''TUESDAY'' ) + 12/24
每个月第一天的午夜12点 TRUNC(LAST_DAY(SYSDATE ) + 1)
每个季度最后一天的晚上11点 TRUNC(ADD_MONTHS(SYSDATE + 2/24, 3 ), 'Q') -1/24
每星期六和日早上6点10分 TRUNC(LEAST(NEXT_DAY(SYSDATE,''SATURDAY"), NEXT_DAY(SYSDATE, "SUNDAY"))) + （6×60+10）/（24×60）
每秒钟执行次 Interval => sysdate+ 1/(24 * 60 * 60)
如果改成sysdate + 10/(24 *60 * 60)就是10秒钟执行次
每分钟执行
Interval =>TRUNC(sysdate,'mi') + 1/ (24*60)
如果改成TRUNC(sysdate,'mi')+ 10/ (24*60) 就是每10分钟执行次
每天定时执行
例如：每天的凌晨1点执行
Interval =>TRUNC(sysdate) + 1 +1/ (24)
每周定时执行
例如：每周一凌晨1点执行
Interval =>TRUNC(next_day(sysdate,'星期一'))+1/24
每月定时执行
例如：每月1日凌晨1点执行
Interval=>TRUNC(LAST_DAY(SYSDATE))+1+1/24
每季度定时执行
例如每季度的第一天凌晨1点执行
Interval =>TRUNC(ADD_MONTHS(SYSDATE,3),'Q') + 1/24
每半年定时执行
例如：每年7月1日和1月1日凌晨1点
Interval =>ADD_MONTHS(trunc(sysdate,'yyyy'),6)+1/24
每年定时执行
例如：每年1月1日凌晨1点执行
Interval=>ADD_MONTHS(trunc(sysdate,'yyyy'),12)+1/24
5 oracle基本语法
5.1 增加
alter table tablename add old_user_pwd VARCHAR2(64); --增加一行表结构
alter table tablename add (a1 int,a2 int); --增加多行表结构
create or replace directory test_dir as '/home/oracle/database/utl_file'; --创建远程目录，test_dir为自定义目录名称，as后面为目录在磁盘上的路径，需要是已经存在的目录或提前创建好，否则创建directory会失败
alter table tablename add constraint pk_name primary key (USER_ID); --添加主键
--表中有数据时，是无法新添加新字段并且是not null的，需要先添加该字段，然后变更为null，最后变更为not null;
alter table tablename add forcing_flag char(1);
update tablename set forcing_flag = '0' where forcing_flag is null;
alter table tablename modify forcing_flag char(1) not null;
5.2 删除
alter table student drop column num1; --删除一个字段
alter table student drop(a1,a2); --删除多行字段
drop directory TEST_DIR; --删除远程目录
alter table tablename drop constraint XXXXXXX cascade; --删除主键
5.3 修改
alter table student rename to student1; --修改表名
alter table student modify (a1 varchar2(6),a2 varchar2(10)); --修改表中数据类型
5.4 查询
select a.bytes/1024/1024 表大小 from user_segments a where a.segment_type = 'TABLE' and a.segment_name = 'tablename';
select * from all_all_tables where owner='USER' and table_name like '%1027%'; --查询表名
select inst_id,sql_id,count(*) from gv$session where status='ACTIVE' group by inst_id,sql_id; --查询会话
select * from gv$sql a where a.sql_id = 'buxfs4hs7s003'; --根据sql_id查询对应sql
5.5 左右外连接
简单的理解为:
(+)在右，是左外连接。左表的数据全展示
(+)在左，是右外连接。右表的数据全展示
示例中t_A看成左表,t_B看成右表
左外连接：t_A的数据都显示,加上t_A和t_B匹配后的数据,t_B表不足的地方均为NULL。
select a.*,b.* from t_A a,t_B b where a.a_id=b.b_id(+) order by a.a_id;
select a.*,b.* from t_A a left join t_B b on a.A_id = b.b_id order by a.a_id;
结果：
右外连接，t_B的数据都显示,加上t_A和t_B匹配后的数据。 t_A表不足的地方均为NULL。
Select a.*,b.* from t_A a,t_B b where a.a_id(+)=b.b_id order by a.a_id;
select a.*,b.* from t_A a right join t_B b on a.a_id = b.b_id order by a.a_id;
结果：
6 其他
6.1 权限
grant read ,write on directory MYDATA to bill;
revoke read,write on directory MYDATA from bill;
7 job
7.1 job相关视图
可以根据视图查询到job对应的job进程号
select * from dba_jobs;
select * from all_jobs;
select * from user_jobs;
-- 查询字段描述
/*
字段（列） 类型 描述
JOB NUMBER 任务的唯一标示号
LOG_USER VARCHAR2(30) 提交任务的用户
PRIV_USER VARCHAR2(30) 赋予任务权限的用户
SCHEMA_USER VARCHAR2(30) 对任务作语法分析的用户模式
LAST_DATE DATE 最后一次成功运行任务的时间
LAST_SEC VARCHAR2(8) 如HH24:MM:SS格式的last_date日期的小时，分钟和秒
THIS_DATE DATE 正在运行任务的开始时间，如果没有运行任务则为null
THIS_SEC VARCHAR2(8) 如HH24:MM:SS格式的this_date日期的小时，分钟和秒
NEXT_DATE DATE 下一次定时运行任务的时间
NEXT_SEC VARCHAR2(8) 如HH24:MM:SS格式的next_date日期的小时，分钟和秒
TOTAL_TIME NUMBER 该任务运行所需要的总时间，单位为秒
BROKEN VARCHAR2(1) 标志参数，Y标示任务中断，以后不会运行
INTERVAL VARCHAR2(200) 用于计算下一运行时间的表达式
FAILURES NUMBER 任务运行连续没有成功的次数
WHAT VARCHAR2(2000) 执行任务的PL/SQL块
CURRENT_SESSION_LABELRAW MLSLABEL 该任务的信任Oracle会话符
CLEARANCE_HI RAW MLSLABEL 该任务可信任的Oracle最大间隙
CLEARANCE_LO RAW MLSLABEL 该任务可信任的Oracle最小间隙
NLS_ENV VARCHAR2(2000) 任务运行的NLS会话设置
MISC_ENV RAW(32) 任务运行的其他一些会话参数
*/
7.2 创建job
declare
variable job number;
begin
sys.dbms_job.submit(job => :job, --job是输出参数
what => 'prc_name;', --what指的是：执行的存储过程，PL/SQL代码块
next_date => to_date('22-11-201309:09:41', 'dd-mm-yyyy hh24:mi:ss'), --next_date 何时将执行这个工作
interval =>'sysdate+1/86400'); --interval 何时这个工作将重新执行
commit;
end;
实例:
declare jobno number;
begin
dbms_job.submit(jobno,'tydic.p_stop_danger_duanka(3);', sysdate, 'TRUNC(sysdate,''hh24'') + 1/ (24)');
commit;
end;
7.3 启动job
begin
dbms_job.run(:job) --该job为submit过程提交时返回的jobnumber或是去dba_jobs去查找对应job的编号
end;
示例：
begin
dbms_job.run(1221);
commit;
end;
7.4 删除job
begin
dbms_job.remove(1221);
commit;
end;
7.5 停止job
select * from dba_jobs_running; --查询正在运行的job
--停止job（该方法不会立即停止job，如果需要立马停止，需要用下面的kill掉）
begin
dbms_job.broken(1221, true);
commit;
end;
如果上面这种方法停止不了，可以直接kill掉
select sid,serial# from v$session a where a.sid = '&sid';
alter system kill session '&sid,&serial';
7.6 更改job
begin
SYS.DBMS_JOB.CHANGE(
2 ,
'zxjsy.PROC_INIT_SJTBKZ(sysdate);',
to_date('19/05/2018 23:00:00','dd/mm/yyyy hh24:mi:ss'),
'TRUNC(LAST_DAY(SYSDATE))+19+23/24'
);
commit;
end;
7.7 正在运行job
select * from dba_jobs_running;
