oracel相关问题

1.oracle锁表问题解决

首先PL/SQL要以管理员的账号（system/admin等）登录

–查看被锁表信息

select sess.sid,
       sess.serial#,
       lo.oracle_username,
       lo.os_user_name,
       ao.object_name,
       lo.locked_mode
  from v$locked_object lo, dba_objects ao, v$session sess
 where ao.object_id = lo.object_id
   and lo.session_id = sess.sid;

–杀掉锁表进程

alter system kill session '68,51'; --分别为SID和SERIAL#号

–查看数据库引起锁表的SQL语句

SELECT A.USERNAME,
       A.MACHINE,
       A.PROGRAM,
       A.SID,
       A.SERIAL#,
       A.STATUS,
       C.PIECE,
       C.SQL_TEXT
  FROM V$SESSION A, V$SQLTEXT C
 WHERE A.SID IN (SELECT DISTINCT T2.SID
                   FROM V$LOCKED_OBJECT T1, V$SESSION T2
                  WHERE T1.SESSION_ID = T2.SID)
   AND A.SQL_ADDRESS = C.ADDRESS(+)
 ORDER BY C.PIECE;

2.oracel创建表空间、用户、授权

select * from sys.dba_tablespaces;

--创建表空间
CREATE TABLESPACE user_data DATAFILE 'E:\work\SBKDATA.DBF' SIZE 50m AUTOEXTEND ON NEXT 50m MAXSIZE 10240m EXTENT MANAGEMENT LOCAL; 

--创建用户
CREATE USER sbk IDENTIFIED BY sbk DEFAULT TABLESPACE user_data;	

--用户授权
GRANT CONNECT,RESOURCE,DBA TO sbk;

select * from sys.dba_tablespaces;	--查看表空间
select * from dba_data_files; 		--查找表空间（工作空间）的路径
select * from dba_users;	--查看用户

2.1 创建用户时的默认表空间 default tablespace 用途：

用户的默认表空间主要是在用户创建数据库对象时如果不显示的指定创建的对象存储的表空间时，会被默认放置的表空间。而用户是可以在任何允许访问的表空间上创建数据库对象的。 
用户以后创建的数据库对象将存放在此表空间内,创建用户的时候还必须使用quota子句为用户在默认表空间中分配空间配额，如果不指定默认表空间，ORACLE将会把SYSTEM表空间做为用户的默认表空间，这种情况应该避免. 

2.2 创建用户时的临时表空间 emporary tablespace 用途

当用户所执行的SQL语句需要进行排序操作是，会要求获取一定的临时空间。这时，oracle将在用户的临时表空间中创建临时段，该临时段属于sys用户，而不是属于用户 
用户的临时表空主要是用户在执行大型的sql语句时所示用的临时排序空间，如果临时表空间不足在排序时则会使用硬盘进行排序，这会导致大量的磁盘读写操作，从而影响sql的执行性能。

临时表空间用来管理数据库排序操作以及用于存储临时表、中间排序结果等临时对象,当ORACLE里需要用到SORT的时候，并且当PGA中sort_area_size大小不够时，将会把数据放入临时表空间里进行排序。像数据库中一些操作： CREATE INDEX、 ANALYZE、SELECT DISTINCT、ORDER BY、GROUP BY、 UNION ALL、 INTERSECT、MINUS、SORT-MERGE JOINS、HASH JOIN等都可能会用到临时表空间。当操作完成后，系统会自动清理临时表空间中的临时对象，自动释放临时段。这里的释放只是标记为空闲、可以重用，其实实质占用的磁盘空间并没有真正释放。这也是临时表空间有时会不断增大的原因。

2.3 删除用户及其表 用 system 用户登录

select * from dba_users;	--查找用户
drop user sbk2 cascade;		--删除用户 sbk2
drop tablespace 表空间名称 including contents and datafiles cascade constraint;	--删除表空间
如果提示无法删除当前连接的用户
select username, sid, serial# from v$session where username='SBK2';
alter system kill session '**,**';杀掉进程资源 'sid,serial#' 如 '4,105'

例如：删除用户名成为LYK，表空间名称为LYK
drop user LYK cascade;	--删除用户，及级联关系也删除掉
drop tablespace LYK including contents and datafiles cascade constraint;--删除表空间及对应的表空间文件

oracle 表结构及数据复制

create table ab as select * from b -- 相同用户下表复制，也叫表的备份  

create table user.orders_back as select * from scott.orders;
不同用户之间的表复制需要在有dba权限的用户下才能实现，user 用户中创建表orders_back，来源system用户

oracle 删除表空间

删除表数据操作，清空所有表记录

TRUNCATE TABLE your_table_name;

释放表空间
存放大数据量的表，其表空间占用也比较大，删除数据后不会自动释放这些记录占用的表空间，所以，即便表里面数据量很少，查询效率依旧很慢，所以需要释放表空间。

-- 查询数据表占用的表空间大小，--注意，表名必须大写
SELECT sum(bytes)/(1024*1024) FROM user_segments WHERE segment_name='YOUR_TABLE_NAME'; 
说明：sum(bytes)/(1024*1024) 数据统计单位由 Byte 转为 GB

--整理碎片，释放已删除记录占用的表空间
ALTER TABLE your_table_name MOVE;

oracle 百万数据导出导入 （.dmp 文件）

如果数据在测试和生产环境迁移，且有 blob 字段，可以用 dmp，blob 导出 sql 文件无效，blob 可存二进制数据等如图片
exp.exe 导出
imp.exe 导入
这两个exe文件在 plsql 中都有

步骤参考 https://blog.csdn.net/GRAY_KEY/article/details/81072345

imp 导入默认是增量的，不会删除原表数据，有主键冲突时可能会报错（如果要删表数据和导入一体，试下加 destory=y 参数）
plsql imp 导入时，会校验 dmp 文件文件中的版本号，不一致会报标头验证失败，版本一致再校验是否是 dba 用户，只要 dba 用户才能导入dba导出的文件，做好 linux root 或者 oracle 用户的申请，可以临时赋权给应用用户去imp 再回收权限

su oracle   切换到 oracle 后，用户的根目录和 PATH 仍然是原先用户的
su - oracle 相当于重新登陆,此时用户的根目录和PATH等信息会发生改变，不加"-"，用的是切换前用户的环境变量，所以会报 command not found

su - oracle       #注意 - 和空格
sqlplus / as sysdba  # dba 作为dba登录  sqlplus /nolog 该命令是只连接未登录，后续会要输入账号密码（删除用 delete，退出用 exit 或 quit）
cd /home/oracle   # 我们的 oracel 用户只能在 oracle 目录下操作
grant dba to mercury;   # 如果提示只有dba用户才能导，就授权 
imp mercury/passwd file=/home/oracle/test.dmp full=y ignore=y    # full=y 导入全部文件  ignore=y 忽略创建错误，dmp 文件中有 create 表操作
revoke dba from mercury 	# 操作完回收 dab 权限

一、数据库安装及建库、导表
--创建表空间（D:\app\jun\oradata\为数据库安装路径）
create tablespace cixi_ehr_data  datafile 'D:\app\jun\oradata\cixi_ehr\ehr01.dbf'
size 32M autoextend on next 32M maxsize 5120M extent management local;

create tablespace cixi_ehr_idata  datafile 'D:\app\jun\oradata\cixi_ehr\idata01.dbf'
size 32M autoextend on next 32M maxsize 5120M extent management local;

--创建数据库用户及口令
create user cixiehr identified by cixiehr default tablespace cixi_ehr_data ;
create user cixiehridata identified by cixiehridata default tablespace cixi_ehr_idata;


--用户授权
grant dba to cixiehr;
grant dba to cixiehridata;

--删除用户
--drop user ehr cascade;


----------------------------------------------------------------------------------------------
导入数据库

1、创建逻辑目录，该命令不会在操作系统创建真正的目录，最好以system等管理员创建。

--创建或者替换目录路径 在oracle里面执行(默认：DATA_PUMP_DIR)
create or replace directory dump_dir as 'd:\test\dump';

2、查询oracle配置路径（同时查看操作系统是否存在，因为Oracle并不关心该目录是否存在，如果不存在，则出错）

select * from dba_directories;

3、给hradmin用户赋予在指定目录的操作权限，最好以system等管理员赋予。

grant read, write on directory dump_test to hradmin; 

4、导出
expdp cixiehr/cixiehr@orcl schemas=ehr DUMPFILE=CIXIEHR20161219.dmp DIRECTORY=data_pump_dir

5、导入数据库更换用户名和表空间 remap_schema（旧用户ehr换成ehruat）和 remap_tablespace（旧表空间CIITEHR_DATA、USERS换成EHR_DATA）
impdp cixiehr/cixiehr@orcl DIRECTORY=data_pump_dir DUMPFILE=CIXIEHR20161219.DMP remap_schema=ehruat:cixiehr remap_tablespace=cixi_ehr_data:EHR_DATA logfile=ehr.log

oracle索引

多个字段需要建索引时，不要建成一个索引，最好分开建不然没啥效果

oracle组织机构表同步问题（外键的启停）

select * from user_constraints  # 查看所有外键
alter table table_name enable constraint constraint_name   # 启用某表的外键约束
alter table table_name disable constraint constraint_name  # 禁用某表的外键约束

查出数据库中所以外键的约束名：
select 'alter table '||table_name||' enable constraint '||constraint_name||';' from user_constraints where constraint_type='R'
select 'alter table '||table_name||' disable constraint '||constraint_name||';' from user_constraints where constraint_type='R'

1.删除所有外键约束 
select 'alter table '||table_name||' drop constraint '||constraint_name||';' from user_constraints where constraint_type='R' 

2.禁用所有外键约束
select 'alter table '||table_name||' disable constraint '||constraint_name||';' from user_constraints where constraint_type='R' 

3.启用所有外键约束
select 'alter table '||table_name||' enable constraint '||constraint_name||';' from user_constraints where constraint_type='R'

oracle 分区函数 partition

分数表中张三考了 2 次，取出最高的分数
SELECT * FROM 
(select sno,cno,degree,row_number()over(partition by cno order by degree desc) mm from score) 
where mm = 1;

先根据 cno 分组，再根据 degree 倒序排，再根据排序的别名 mm 取出第一个

rank() 与 row_number() 区别：在求第一名成绩的时候，不能用row_number()，因为如果同班有两个并列第一，row_number()只返回一个结果

oracle 表结构转 mysql 结构

参考 https://blog.csdn.net/qq_28194001/article/details/79124090

1、安装 powerdesign （数据库设计工具）
2、导出 ORACLE 表结构（.sql文件）
3、把 oracle 表结构导入 powerdesign，File->Reverse Engineer->Database，设置物理模型的名称及所使用数据库类型，选择 Oracle version 11g，然后点击 Using script files 框里的 Add Files 按钮，选择已经导出的 Oracle 表结构 sql 文件
4、改变数据库类型，Database->Change Current DBMS，Current DBMS 显示当前的数据库类型 Oracle version 11g，New DBMS 中选择 MySQL 5.0  （如果上方菜单没找到 Database->Change Current DBMS ，可能需要打开视图，点击表）
5、导出 sql 文件（mysql语法） Database->Generete Database

oracle 行转列

行列值分组的结果作为新列
group by 和聚合函数要么都出现，要么都不出现。

|chat_id | falg |
|–|–|-|
| 1| +|
| 1| +|
| 1| -|
| 2| + |
| 2| - |
转成
|chat_id | in| out|
|–|–|-|-|
| 1| 2|1|
| 2| 1|1|

select chat_id sum(case when flag='+' then 1 else 0 end) as in,sum(case when flag='-' then 1 else 0 end) as out from test where group by chat_id

mysql 行转列

参考 https://www.cnblogs.com/oktokeep/p/15821792.html

--方式 1
SELECT cust_id,
SUM(CASE `prod_id` WHEN '001' THEN 1 ELSE 0 END) AS '001',
SUM(CASE `prod_id` WHEN '002' THEN 1 ELSE 0 END) AS '002'
FROM cust_txn_info 
GROUP BY cust_id;

--方式 2
SELECT cust_id,
SUM(IF(`prod_id`='001',1,0)) AS '001',
SUM(IF(`prod_id`='002',1,0)) AS '002'
FROM cust_txn_info 
GROUP BY cust_id

oracle 存储过程编写及调试

1.创建存储过程

注意：当存储过程中当前用户需要用到别的用户表时，需要先赋权！可能直接 select 别的用户正常，但是存储过程中，如果没赋权会报表不存在

grant select on shx.comm_employee to mercury

CREATE OR REPLACE PROCEDURE pro(myin IN varchar2, myout out varchar2) AS
BEGIN
  myout := '执行完成'; --in out 模式参数的值可以修改
  dbms_output.put_line('输入参数：' || myin);
exception
  when others then
    myout := '执行异常';
END;

2.调用存储过程

begin
  -- sql 窗口调用存储过程
  pro(myin => :myin,
      myout => :myout);
end;

3.编译及调试存储过程

齿轮按钮：编译并保存存储过程
recompile： 只能编译，不能保存修改后的存储过程，
test ：调试debugger 存储过程，点击后进入下图，1为开始调试，2为执行结束，3为单步执行

mybatis-plus 注解方式调用存储过程

	/**
     * 注解形式调用存储过程 test ，key1为入参，key2为出参，
     * @param map
     */
    @Select("call test(#{map.key1,mode=IN,jdbcType=INTEGER},#{map.key2,mode=OUT,jdbcType=VARCHAR})")
    @Options(statementType=StatementType.CALLABLE)
    void callProcedure(@Param("map")Map map);

linux 环境下启停oracle服务

1、先登录服务器后，切换到 oracle 用户状态下： su - oracle
2、检查 oracle 监听器运行状态：lsnrctl status 命令查看。
3、执行启动监听，通过命令：lsnrctl start （lsnrctl stop 关闭监听）看到success表示端口已启动成功。
4、如果启动报错，listener.ora 文件打开，调整 HOST IP
5、进入sqlplus，命令：sqlplus /nolog
6、管理员 sysdba 权限登陆 oracle：conn /as sysdba
7、启动服务（实例）：startup
8、关闭服务（实例）：shutdown immediate;
	
SQL> 下退出编辑按键 Ctrl + D 或 SQL>exit

oracel 表空间使用情况查询

参考 https://www.cnblogs.com/xwdreamer/p/3511047.html

--查询表空间使用情况
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
ORDER  BY 1

数据库字段

参考 https://www.cnblogs.com/kliine/p/10018607.html

类型	含义	存储描述	备注
CHAR	固定长度字符串	最大长度2000bytes
VARCHAR2	可变长度的字符串,	最大长度4000bytes	可做索引的最大长度 749
NCHAR	根据字符集而定的固定长度字符串	最大长度2000bytes
NVARCHAR2	根据字符集而定的可变长度字符串	最大长度4000bytes
DATE	日期（日-月-年）	DD-MM-YY(HH-MI-SS)，无千虫问题
TIMESTAMP	日期（日-月-年）	DD-MM-YY(HH-MI-SS:FF3)，无千虫问题	与 DATE 比，TIMESTAMP 有小数位秒信息
LONG	超长字符串	最大长度2G,足够存储大部头著作
RAW	固定长度的二进制数据	最大长度2000bytes	可存放多媒体图象声音等
LONG RAW	可变长度的二进制数据	最大长度2G	可存放多媒体图象声音等
BLOB	二进制数据	最大长度4G
CLOB	字符数据	最大长度4G
NCLOB	根据字符集而定的字符数据	最大长度4G
BFILE	存放在数据库外的二进制数据	最大长度4G
ROWID	数据表中记录的唯一行号	10bytes	**.*.*格式，*为0或1
NROWID	二进制数据表中记录的唯一行号	最大长度4000bytes
NUMBER(P,S)	数字类型	P为整数位，S为小数位
DECIMAL(P,S)	数字类型	P为整数位，S为小数位
INTEGER	整数类型	小的整数
FLOAT	浮点数类型	NUMBER(38)，双精度
REAL	实数类型	NUMBER(63)，精度更高

网络 IP 字段 建议用 varchar2

datetime：时间字段（年月日时分秒）
date：年月日（生日）
BLOB（存图片、文件、音乐等文件的二进制）
CLOB（存文章或较长文字）

BLOB 二进制大对象（Binary Large Object)。存储数据库中的大型二进制对象。最大存储4G字节

CLOB 字符大型对象（Character Large Object)。与 LONG 数据类型类似， CLOB 用于存储数据库中的大型单字节字符数据块，不支持宽度不等的字符集。最大存储4G字节

blob 在跨数据库拷贝时，一般要用 exp.exe 导出 dmp 包（imp.exe 导入） ，参考上面百万数据导出

表字段应尽可能显示设置默认值。建议数值型的默认值为数值0，布尔型的默认值为数值1（通常情况下，系统中所有逻辑型中数值0表示为“真”、“正常的”；数值1表示为“假”、“异常的”，这种编码后面还会有介绍），datetime、smalldatetime类型的字段没有默认值，必须为NULL