Oracle数据库操作

Oracle文档

导入导出

Oracle数据库备份操作建议使用数据泵方式(expdp/impdp),效率高,并且可选参数丰富

expdp/impdp

数据泵导入导出方式有directory的概念，select * from dba_directories;dmp存放位置需要配置directory,
另外直接放到oracle安装目录的默认导入导出目录下则不需要配置。路径为oracle安装目录/admin/${sid}/dpdump/

• expdp

  • expdp user/passwd@localhost:1521/orcl dumpfile=exp.dmp logfile=exp.log --导出user用户下对象
  • DATA_ONLY=y 只导入/导出表数据
  • METADATA_ONLY=y 只导入/导出对象定义
  • full=y 全库导出，需要拥有dba或者exp_full_database导出权限
  • schemas=user 导出一个或多个对象
  • Tablespaces=tab1 导出一个或多个表空间
  • tables=tab1,tab2 导出指定表
  • query=\"where id < 100000 \" 使用query导出部分数据，id小于100000
  • Sample=10 导出10%数据(近似参考值)
  • EXCLUDE/INCLUDE 注意：在windows使用时双引号需要转义,Linux中单双引号和括号都需要转义，并且所有数据需要大写 expdp fms_cloud/fms_cloud INCLUDE=TABLE:\"='T8_BOND_DEAL_INFO'\" dumpfile=include.dmp --windows
    expdp fms_cloud/fms_cloud INCLUDE=TABLE:\"=\'T8_BOND_DEAL_INFO\'\" --Linux
  • EXCLUDE=SEQUENCE,VIEW --过滤所有的SEQUENCE,VIEW
  • EXCLUDE=TABLE:"IN ('EMP','DEPT')" --过滤表对象EMP,DEPT
  • EXCLUDE=SEQUENCE,VIEW,TABLE:"IN ('EMP','DEPT')" --过滤所有的SEQUENCE,VIEW以及表对象EMP,DEPT
  • EXCLUDE=INDEX:"= 'INDX_NAME'" --过滤指定的索引对象INDX_NAME
  • INCLUDE=PROCEDURE:"LIKE 'PROC_U%'" --包含以PROC_U开头的所有存储过程(_ 符号代表任意单个字符)
  • INCLUDE=TABLE:"> 'E' " --包含大于字符E的所有表对象

• impdp

  • impdp user/passwd@localhost:1521/orcl dumpfile=exp.dmp logfile=imp.log --将exp.dmp导入到user用户下
  • Table_exists_action=(SKIP/APPEND/TRUNCADE/REPLACE) 导入表存在处理(跳过/追加数据/清空并追加/替换表)
  • REMAP_SCHEMA=fromuser1:touser,fromuser2,touser 导入切换用户,从多个用户切换到touser
  • REMAP_TABLESPACE=fromtabspace1:totabspace,fromtabspace2:totabspace 导入切换表空间,多个表空间切换
  • 导入部分表替换已存在表并切换用户和表空间 例：impdp fms_jc/fms_jc dumpfile=fmsdev20200429.dmp REMAP_SCHEMA=fmsdev:fms_jc REMAP_TABLESPACE=fmsdev_data:fms_jc TABLES=fmsdev.T8_SYS_EXPRESSION,fmsdev.T8_SUBJECT_ACCRULE,fmsdev.T8_SUBJECT_POOL,fmsdev.T8_SUBJECT_CTRL Table_exists_action=REPLACE

exp/imp

• exp username/password@localhsot/orcl file='/user/oracle/tmp.dmp' --导出用户数据表结构
• exp fms_cloud/fms_cloud@oracle-dev.zcgl.kkws.cn/xe file='D:\KKWS脚本\dmp\exp.dmp' TABLES=(ACT_WORKFLOW_FORM)QUERY=\"WHERE rownum<10000\" --导出用户指定表数据到指定位置
• imp username/password@localhost/orcl file='/usr/oracle/tmp.dmp' full=y ignore=y log=imp.log --将tmp.dmp文件导入到用户中，full=y表示全部导入 ignore=y表示忽略创建错误继续执行
  • data_only=y 只导数据
  • rows=n 不导入数据
  • fromuser=user1 touser=user2 导入时切换用户(在ignore=y不生效时可使用)
  • tables=(tab1,tab2) 导出指定表
  • QUERY=\"WHERE rownum<11\" 在tables()后面跟上导出部分数据

SQL语法

DML数据操作语言

基本功能

增删改查

• insert into table_name (column1,column2,column3) values (1,2,3); --插入数据
• select column_name from table_name; --查询表数据
• update table_name set column_1 = newdata where id = 1; --修改表数据
• delete from table_name where id = 1; --删除表数据

插入数据字段拼接单引号

• insert into table_name(column1,column2) values ('测试1','测试2');
• insert into table_name(column1,column2) values ('''' || '测试1' || '''','''' || '测试2' || '''');

表备份,恢复

• create table user_bak as select * from user --表备份
• truncate table user --清空表
• insert into user select * from user_bak --插入备份数据
• drop table user_bak --删除备份表

辅助功能

数据库相关

• select 'create or replace synonym ' || table_name || ' for FMS_CZ_MD.' || table_name || ';' from user_tables;--同义词创建
• select userenv('language') from dual; --查看字符集编码
• select * from all_users; --查询所有用户
• select * from dba_users;
• select user from dual; --查询当前登录用户
• select name from v$database; --查询当前实例
• select * from dba_sys_privs;
• select * from user_sys_privs; (查看当前用户所拥有的权限)
• select tablespace_name from sys.dba_tablespaces;--查询所有表空间
• select osuser, a.username, cpu_time/executions/1000000||'s', b.sql_text, machine
• from v$session a, v$sqlarea b
• where a.sql_address =b.address
• order by cpu_time/executions desc; --查看当前有哪些用户正在使用数据库
• Oracle的连接数(sessions)与其参数文件中的进程数(process)有关。sessions=(1.1*process+5)
• select count(*) from v$session; -- 查询当前的session会话连接数
• select count(*) from v$process ; --查看当前数据库进程连接数
• select value from v$parameter where name ='processes'; --查询数据库允许的最大进程连接数
• select count(*) from v$session where status='ACTIVE'; --查询数据库的并发连接数
• alter system kill session 'sid, serial#'; --杀死某个连接session会话
• alter system set sessions=1105 scope=spfile; -- 修改允许最大会话连接,需要重启数据库
• alter system set processes = 1000 scope = spfile; --修改数据库最大连接数,需要重启数据库
• shutdown immediate; --关闭数据库
• startup; --重启数据库

v$process：

• 这个视图提供的信息，都是oracle服务进程的信息，没有客户端程序相关的信息

• 服务进程分两类，一是后台的，一是dedicate/shared server

• pid, serial# 这是oracle分配的PID

• spid 这才是操作系统的pid

• program 这是服务进程对应的操作系统进程名

   select object_name,machine,s.sid,s.serial#
   	from v$locked_object l,dba_objects o ,v$session s
   	where l.object_id = o.object_id and l.session_id=s.sid; --查看数据库表锁死
   
   查询锁进程全部SQL
   select l.session_id sid, s.serial#, l.locked_mode, l.oracle_username,s.user#, l.os_user_name, 
      s.machine, s.terminal, a.sql_textfrom v$sqltext a, v$session s, v$locked_object l 
   	where l.session_id = s.sid and s.prev_sql_addr = a.address order by sid, s.serial#;
   
   select /*+ ORDERED */ sql_text from v$sqltext a where (a.hash_value,a.ADDRESS) in
   	( select decode(sql_hash_value,0,PREV_HASH_VALUE,sql_hash_value),
   	decode(sql_hash_value,0,PREV_SQL_ADDR,SQL_ADDRESS) from v$session b
   	where b.sid=&SID)
   	order by piece asc
   
   性能监控表
   	v$sql
   	v$sqlarea
   	v$sqltext
   	v$session
   
   Oracle数据库表或数据删除解决
   	https://blog.51cto.com/1197822/2157204
  

​

数据相关

--删除指定用户所有表的方法
select 'Drop table '||table_name||';'from all_tables where owner='要删除的用户名(注意要大写)'; 
--查询表和表字段描述
select t.* from user_col_comments t where t.table_name = 'T8_ALGORITHM_LOG';
--查询表字段说明
select t.* from user_tab_columns t where t.table_name = 'T8_ALGORITHM_LOG'
--查询表索引说明
select * from user_indexes t where t.table_name='T8_ALGORITHM_LOG';
--查询表索引属于那张表
select * from user_ind_columns t where t.table_name = 'T8_ALGORITHM_LOG';
--查询表状态
select * from user_tables t where t.table_name = 'T8_ALGORITHM_LOG';

DDL数据定义语言

表相关

• --创建表
  create table TABLENAME (
  ID number not null ,
  DEALNO varchar2(50) ,
  contact_name varchar2(50) ,
  CONSTRAINT PK_TABLENAME_ID primary key(ID), --主键
  CONSTRAINT PK_TABLENAME_DEALNO UNIQUE (DEALNO)--创建表时创建唯一性约束
  );

  --删除表
  drop table TABLENAME;
  --清空表
  truncat table TABLENAME;--DDL操作，不产生rollback,速度块
  delete from TABLENAME;

  --修改表名
  alter table t8_forex_exchange rename to t8_forex_ex_deal_info
  --修改表字段类型
  alter table t8_forex_exchange modify (MSG_OPERATE_TYPE VARCHAR2(10))
  --修改字段长度时不生效可使用dba用户修改
  UPDATE USER_TAB_COLUMNS SET DATA_TYPE='VARCHAR2', DATA_LENGTH=100 WHERE TABLE_NAME='T8_SYS_EXPRESSION';

  --增加表字段
  alter table t8_forex_exchange add (ftool_code VARCHAR2(32))
  --修改字段名
  alter table t8_forex_exchange rename column id to ids;

索引相关

--在字段上建立索引
create index INDEX_TABLENAME on TABLENAME (COLUMN1,COLUMN2);
--增加主键索引
alter table T8_FOREX_EXCHANGE add constraint T8_FOREX_EXCHANGE_PK primary key (ID);
--增加唯一索引
alter table T8_FOREX_EXCHANGE add constraint T8_FOREX_EXCHANGE_DEALNO unique (DEALNO);
--查询表索引
select * from user_indexes t where t.table_name='TABLENAME';
--查询表索引详细信息
select * from user_ind_columns t where t.TABLENAME = 'T8_ALGORITHM_LOG';
--删除索引
drop index index_name;
--修改索引名称
ALTER TABLE TABLENAME RENAME CONSTRAINT SYS_C00421221 TO PK_PC59;
ALTER INDEX SYS_C00421221 RENAME TO PK_PC59;

创建用户表空间

--创建表空间、用户并赋予用户权限
create tablespace tablespace 
	datafile '/u01/app/oracle/oradata/tablespace.dbf'
	size 200M reuse autoextend on next 16K maxsize unlimited extent management local 		autoallocate;

create user username
 	identified by "username"
  	default tablespace tablespace
  	temporary tablespace TEMP
  	profile DEFAULT;  

grant create user,drop user,alter user,create any view,
        select any table,delete any table,
        insert any table,update any table,
        drop any view ,exp_full_database,imp_full_database,
	create database link,
        connect,resource,create session to username;

--如果是12c数据库还需要加上一条
alter user username QUOTA UNLIMITED ON tablespace

删除用户表空间

drop user username cascade; --删除用户以及关联对象
drop tablespace tablespacename (including contents); --删除表空间(非空表空间)

修改用户密码

alter user system identified by 123456; --修改用户密码

创建/删除DB_LINK

CREATE PUBLIC DATABASE LINK "MARKETDATA" CONNECT TO username IDENTIFIED BY "pwd"
	USING '(DESCRIPTION =(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))) 
	(CONNECT_DATA =(SERVICE_NAME = ORCL)))'; --创建DB_LINK
drop public database link "MARKETDATABAK"; --删除DB_LINK

存储

索引

优化

in和exists

in 会先查询子 再查询外   exist 会先查外 再查子
select * fromm t8_bond_deal_info where id in (select ID from t8_bond_deal_info where id >1);
select * from t8_bond_deal_info bond where exists (select ID from t8_bond_deal_info where id >1 and id = bond.id);

SQL执行计划

explain plan for
	select * from sys_dict_item where dict like '%bond_market%'
select * from table(dbms_xplan.display)

数据库SQL查询统计

--查询数据库中执行最慢的50条SQL
select *
from (select sa.SQL_TEXT,
    sa.SQL_FULLTEXT,
    sa.EXECUTIONS "执行次数",
    round(sa.ELAPSED_TIME / 1000000, 2) "总执行时间",
    round(sa.ELAPSED_TIME / 1000000 / sa.EXECUTIONS, 2) "平均执行时间",
    sa.COMMAND_TYPE,
    sa.PARSING_USER_ID "用户ID",
    u.username "用户名",
    sa.HASH_VALUE
 from v$sqlarea sa
 left join all_users u
  on sa.PARSING_USER_ID = u.user_id
 where sa.EXECUTIONS > 0
 order by (sa.ELAPSED_TIME / sa.EXECUTIONS) desc)
where rownum <= 50;

--查询数据库中查询次数最多的SQL
select *
from (select s.SQL_TEXT,
    s.EXECUTIONS "执行次数",
    u.username "用户名",
    rank() over(order by EXECUTIONS desc) EXEC_RANK
 from v$sql s
 left join all_users u
  on u.USER_ID = s.PARSING_USER_ID
  where u.USERNAME = 'FMS_CLOUD'
  ) t
where exec_rank <= 100;

索引重建

锁

版本区别

Oracle11gR2和Oracle12c不支持wm_concat解决

解决办法：
1. 使用listagg
select listagg(ftool_code,',') within group (order by ftool_code) from t8_bond_deal_info group by 		ftool_code
select wm_concat(ftool_code) from t8_bond_deal_info group by ftool_code

2.
CREATE OR REPLACE TYPE WM_CONCAT_IMPL AS OBJECT
(
CURR_STR VARCHAR2(32767), 
STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT WM_CONCAT_IMPL) RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT WM_CONCAT_IMPL,
P1 IN VARCHAR2) RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN WM_CONCAT_IMPL,
RETURNVALUE OUT VARCHAR2,
FLAGS IN NUMBER)
RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT WM_CONCAT_IMPL,
SCTX2 IN WM_CONCAT_IMPL) RETURN NUMBER
);

CREATE OR REPLACE TYPE BODY WM_CONCAT_IMPL
IS
STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT WM_CONCAT_IMPL)
RETURN NUMBER
IS
BEGIN
SCTX := WM_CONCAT_IMPL(NULL) ;
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT WM_CONCAT_IMPL,
P1 IN VARCHAR2)
RETURN NUMBER
IS
BEGIN
IF(CURR_STR IS NOT NULL) THEN
CURR_STR := CURR_STR || ',' || P1;
ELSE
CURR_STR := P1;
END IF;
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN WM_CONCAT_IMPL,
RETURNVALUE OUT VARCHAR2,
FLAGS IN NUMBER)
RETURN NUMBER
IS
BEGIN
RETURNVALUE := CURR_STR ;
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT WM_CONCAT_IMPL,
SCTX2 IN WM_CONCAT_IMPL)
RETURN NUMBER
IS
BEGIN
IF(SCTX2.CURR_STR IS NOT NULL) THEN
SELF.CURR_STR := SELF.CURR_STR || ',' || SCTX2.CURR_STR ;
END IF;
RETURN ODCICONST.SUCCESS;
END;
END;
	
CREATE OR REPLACE FUNCTION wm_concat(P1 VARCHAR2) RETURN VARCHAR2 AGGREGATE USING WM_CONCAT_IMPL ;	

create public synonym WM_CONCAT_IMPL for sys.WM_CONCAT_IMPL;
create public synonym wm_concat for sys.wm_concat;
grant execute on WM_CONCAT_IMPL to public;
grant execute on wm_concat to public;

使用问题

docker容器部署Oracle报错sqlplus、impdp等操作未定义

export ORACLE_HOME=/u01/app/oracle-product/12.1.0/xe  
export PATH=$PATH:$ORACLE_HOME/bin  
export ORACLE_SID=xe

空表exp未导出

1、 deferred_segment_creation：是否延迟segment的创建，11g默认为true，即空表不创建segment，所以导出dmp没有空表的信息。  
2、查看现有的状态：show parameter deferred_segment_creation;  
3、修改状态为false：alter system set  deferred_segment_creation=false;  
4、经过上面的步骤说明：以后建的空表都会创建segment，但是之前创建的空表不起作用，还是导不出信息。  
5、查询用户下所有的空表：  
    a、`select t.table_name from user_tables t where t.num_rows=0 or t.num_rows is null;`  
    b、`select t.table_name from all_tables t where t.owner='' and (t.num_rows =0 or t.num_rows is null);`  
6、给所有的空表分配segment：（分配空间)  
`select 'alter table ' || t.table_name || ' allocate extent' ||';' from user_tables t where t.num_rows=0 or t.num_rows is null; ` 
7、执行查询结果。  

Oracle修改字符集编码

shutdown immediate;  
startup mount;  
alter system enable restricted session;  
alter system set job_queue_processes=0;  
alter system set aq_tm_processes=0;  
alter database open;  
alter database character set internal_use ZHS16GBK;--AL32UTF8  
shutdown immediate;  
startup; 

imp ignore和fromuser/touser不生效

最终解决办法是先创建需要的表空间,再调整表空间

数据库切换表空间

--表
select 'alter table ' ||table_name|| ' move tablespace FMS_QHD_DATA;' table_name from dba_tables where owner='FMS_QHD'; 

--索引lob字段
select 'alter table ' ||ind.table_name|| ' move lob('||ind.index_name||') store as ( tablespace FMS_QHD_DATA);' from dba_indexes ind where ind.index_type = 'LOB' and owner = 'MFS_QHD'
或者
select 'alter table ' ||ind.table_name|| ' move lob('||ind.index_name||') store as ( tablespace FMS_QHD_DATA);' from dba_indexes ind
    left join user_tab_cols col on ind.TABLE_NAME=col.table_name where col.data_type like '%LOB%' and owner='FMS_QHD' ;

--索引表空间
select 'alter index ' ||index_name|| ' rebuild tablespace FMS_QHD_DATA;' index_name from dba_indexes where owner='FMS_QHD';

Oracle not available解决

sqlplus / as sysdba;
startup;

错误

ORA-28040:没有匹配的验证协议

• 原因： oracle驱动不兼容，

• 解决办法： 修改ojdbc.jar。使用jdk1.8连接oracle12c需要ojdbc8.jar。注意classes12.jar也是oracle的低版本jdk1.1/jdk1.2的驱动，依赖需要去掉这个jar。也可以配置Oracle允许低版本访问，在$ORACLE_HOME/${sid}/network/admin/sqlnet.ora中加入下面两行,Oracle不需要重启，但是重新连接会报错用户名密码不对，还需要再次修改密码

  SQLNET.ALLOWED_LOGON_VERSION_SERVER=8
SQLNET.ALLOWED_LOGON_VERSION_CLIENT=8

ORA-00060:等待资源时检测到死锁

• 原因：主外键关联表设置级联删除，子表外键未加索引，并发情况删除造成
• 解决：1.子表外键加锁(没有死锁情况也需要，否则会存在全表扫描) 2.删除语句上加类锁,在代码处控制

Cannot get a connection, pool error Timeout waiting for idle object

• 原因：数据库连接满了
• 解决：修改数据库允许的最大连接数

ORA-02019: connection description for remote database not found

• 原因：dblink使用名错误
• 解决：修改使用正确的dblink

ORA-00917: missing comma

• 原因：sql语句错误,缺少逗号
• 解决：

ORA-00924: 缺失 BY 关键字

• 原因：排序字段写道order by中间去了

IMPDP报错

ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31640: unable to open dump file "/u01/app/oracle/admin/xe/dpdump/t8_fund_info.dmp" for read
ORA-27041: unable to open file
Linux-x86_64 Error: 13: Permission denied
Additional information: 9
原因：dmp文件权限问题
解决：chown -R oracle:dba /u01/app/oracle/admin/xe/dpdump/expdat.dmp