Oracle操作

用户操作

新建用户

create user abc identified by 123456;

授权

--授权当前的用户
grant connect,resource,dba to abc;

--授权所有权限给另外一个用户
select 'Grant all on '||table_name||'to BIDATA ;' from all_tables where owner = upper('test');

--赋权A到B：先进入到A中 connect A/A的密码;
grant all privileges to B with admin option;

删除

drop user ASGARD cascade;
select saddr,sid,serial#,paddr,username,status from v$session where username is not null;
alter system kill session '6,267';
alter system kill session '161,2443';

SQLPLUS

sqlplus /nolog
conn ba/aa@172.16.0.6:1521/orcl

sqlplus ba/aa@//172.16.0.6:1521/orcl

导入导出

impdp expdp

--创建路径
CREATE OR REPLACE DIRECTORY expdir AS 'd:';
--授权
GRANT read,write ON DIRECTORY expdir TO public;
--导出
expdp BIDATA/BIDATA@BIDATA DIRECTORY=expdir DUMPFILE=BIDATA.dmp；

--导入
impdp BIDATA/BIDATA@BIDATA DUMPFILE=BIDATA.dmp
impdp BIDATA/BIDATA@BIDATA REMAP_SCHEMA=BIDATA:BIDATA DUMPFILE=BIDATA.dmp

--导入导出具体的表
expdp JAVAHIS/JAVAHIS@ORCL DUMPFILE=TEMP2.dmp tables=JAVAHIS.SYS_PATINFO
impdp ASGARD/Ab123456@ORCL REMAP_SCHEMA=JAVAHIS:ASGARD DUMPFILE=TEMP2.dmp tables=JAVAHIS.SYS_PATINFO REMAP_TABLESPACE=TBS_DIC_DAT:USERS

Linux 操作

导出导入：https://www.cnblogs.com/yanch/p/8676445.html

-- 导出
su - oracle
exp asgard/Ab123456@ORCL file=/home/oracle/CDR20200113.dmp
-- linux 导入
 $ imp usrname/password file = t.dmp full=y
-- windows导入
imp asgard/Ab123456@ORCL file=D:\Doc\DBBack\CDR20200113.dmp full=y ignore=y statistics=none

imp exp

--导出库
exp BIDATA/BIDATA@1.1.1.1/BIDATA file=d:\BIDATA.dmp LOG=d:\BIDATA.LOG indexes=n statistics=none

--导入库
imp BIDATA/BIDATA@1.1.1.1/BIDATA file=D:\BIDATA.dmp full=y ignore=y statistics=none

--数据库导入导出表
exp BIDATA/BIDATA@10.1.1.1/BIDATA file=d:\TEMP.dmp tables=(门诊预约记录)
imp BIDATA/BIDATA@127.0.0.1/BIDATA file=D:\TEMP.dmp tables=(门诊预约记录)

--多表一起操作
exp JAVAHIS/JAVAHIS@10.1.1.1/ORCL file=d:\TEMP.dmp tables=(ADM_INP,INP_CONS)
imp ASGARD/Ab123456@10.1.1.1/ORCL file=D:\TEMP.dmp tables=(ADM_INP,INP_CONS)

数据库查询

--查询数据库的实例名称
select instance_name from v$instance

--查看服务器的字符集
select userenv('language') from dual;
SIMPLIFIED CHINESE_CHINA.ZHS16GBK
SIMPLIFIED CHINESE_CHINA.WE8MSWIN1252

SQL备份

-- 1随机返回5条记录

Select * from (select ename,job from emp order by dbms_random.value()) where rownum<=5

-- 2处理空值排序
select * from emp order by comm desc nulls last(first);

-- 3查询跳过表中的偶数行

select ename from (select row_number() over (order by ename) rn,ename from emp) x where mod(rn,2)=1;

-- 4查询员工信息与其中工资最高最低员工

select ename,sal,max(sal) over(), min(sal) over() from emp;

-- 5连续求和

select ename,sal,sum(sal) over(), sum(sal) over(order by ename) from emp;
sum(sal) over(order by ename)指的是连续求和.是以ename来排序的。若有两个这样的窗口函数，以后面的排序为主。

-- 6分部门连续求和

select deptno,sal ,sum(sal) over (partition by deptno order by ename) as s from emp;分部门连续求和

sum(sal) over (partition by deptno) 分部门求和

-- 7得到当前行上一行或者下一行的数据

select ename,sal,lead(sal) over(order by sal) aaa ,lag(sal) over(order by sal) bbb from emp;
month person income 月份 人员 收入 要求用一个SQL语句，统计每个月及上月和下月的总收入 要求列表输出为 月份 当月收入 上月收入 下月收入
select month,sum(income),lead(sum(income)) over(order by sum(income)) from table group by month;