1 sql使用
1.1 Oracle +的使用
1.2 Oracle 查看字符编码
SQL> select * from V$NLS_PARAMETERS;
1.3 Oracle 判断字节长度函数lengthb
length求得是字符长度，lengthb求得是字节长度。
这是Oracle 自带函数，(case When lengthb(aaa)<1000 then substr )
1.4 Oracle fm函数使用
遇到一个SQL，记录一下
select to_char(参数,'FM990.00') from 表格
刚看到FM990.00确实不知道什么意思，通过网上资料，知道了
0表示：如果参数(double或者float类型)存在数字就显示数字，不存在数字就显示0
9表示：如果参数(double或者float类型)存在数字就显示数字，不存在数字就显示空格
FM表示：将9带来的空格删除
拓展：所以上面例子就表示左边最小为1位(个位)，最大3位(百位)，有两位小数，当然格式就不仅仅是FM990.00，格式就是无限个，可以为FM999999.99或者FM9999990.00等等
有的时候出现：XXX的情况
这时需要把to_char(参数，FM990.99) 中改为 to_char(参数*1.0，FM990.99)的情况。
原因无他，
在oracle11中to_char参数如果时小数点后三位如（0.0008）的话，没问题，
但是oracle12中o_char参数如果时小数点后三位如（0.0008）的话，结果会转成XXX的形式，因为是oracle处理的时候把参数部分当成了char去才处理，所以乘以1转成number之后再进行才处理就没有问题了。
1.5 Oracle 两张表结构相同的表对比找差异
--测试 --涉及表 table_name --备份表: create table table_name_bak as select * from table_name; 跑原来的批： --备份表: create table table_name_old as select * from table_name; --从 tablename_bak 恢复: drop table table_name; create table table_name as select * from table_name_bak; --跑新的批： select * from table_name_old minus select * from table_name;
select t1.* from (select * from tableA minus select * from tableB ) t1 union select t2.* from (select * from tableB minus select * from tableA ) t2
1.6 Oracle查询某张表的表结构
select * from USER_TAB_COLUMNS A WHERE A.TABLE_NAME = ’TABLE_NAME‘;
1.7 Oralce新增表字段
alter table T_BILL_TRANS_ORDER_TEMP add (NATION_FLAG varchar2(4) );
comment on column T_BILL_TRANS_ORDER_TEMP.NATION_FLAG is '国内国外标识';
1.8 Oracle计算金额之差
--记录金额之差
SELECT 2921025846.68 - 2920970987.52 FROM dual;
1.9 根据字段查找表名
SELECT table_name
FROM all_tab_columns
WHERE column_name = 'USER_PASSWORD'
1.A 根据表名查找是否包含
select * from user_tables where table_name like 'T_BGT_INCOME_DTL%';
1.Z 查询锁的信息
SELECT s.sid, s.serial#, o.object_name, l.locked_mode FROM v$locked_object l JOIN dba_objects o ON l.object_id = o.object_id JOIN v$session s ON l.session_id = s.sid;
l.session_id AS sid: 这将显示锁定对象的会话ID，并将其重命名为sid。 s.serial#: 这将显示会话的序列号，用于唯一标识会话。 o.object_name: 这将显示被锁定的对象名称。 l.locked_mode: 这将显示锁的模式，例如NULL表示无锁，X表示排他锁等。 注意事项 权限：确保你有足够的权限访问v$locked_object、dba_objects和v$session视图。通常，这些权限由DBA授予。 锁模式：Oracle中的锁模式有多种，例如： NULL：无锁。 X：排他锁（写锁）。 S：共享锁（读锁）。 RX：行排他锁。 RS：行共享锁。
2 存储过程，同义词，dblink
2.1 同义词
--0 同义词作用
-
简化SQL语句
当数据库对象的名称非常长，或者属于其他用户（Schema）时，使用同义词可以让你用更简短的名字来访问。- 场景： 原表名是
scott.department_locations。 - 作用： 你可以创建一个同义词
dept指向它。 - 效果： 以后查询只需要写
SELECT * FROM dept;，既快捷又不易出错。
- 场景： 原表名是
--1 查看同义词
SELECT * FROM dba_synonyms;
--2 创建同义词
CREATE [PUBLIC] [OR REPLACE] SYNONYM 同义词名 FOR [模式名.]对象名[@dblink]; -- 1. 删除指向 NCMIS 的旧同义词
DROP SYNONYM CCIS_HD.CCIS_HD_CTR_LOAN_EXTEND;
-- 2. 创建指向 CMIS_MV 的新同义词
CREATE SYNONYM CCIS_HD.CCIS_HD_CTR_LOAN_EXTEND FOR CMIS_MV.CTR_LOAN_EXTEND;
2.2 dblink
----0 作用
DBLink（数据库链接）就像一座桥梁，让你能在当前数据库中直接访问和操作另一个远程数据库里的数据，实现跨库查询与共享。
----1 查看dblink
-- 查看 B 库中所有的 DBLink
SELECT OWNER, DB_LINK, USERNAME, HOST, CREATED
FROM DBA_DB_LINKS
WHERE DB_LINK = 'LINK_TO_A'; -- 可以加上 WHERE 条件筛选特定的 dblink
----2 删除重建
DROP DATABASE LINK LINK_TO_A;
-- 如果是公有的，用 DROP PUBLIC DATABASE LINK LINK_TO_A;
CREATE DATABASE LINK LINK_TO_A
CONNECT TO ccis_hd IDENTIFIED BY "ccis_hd"
USING '(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 新IP地址)(PORT = 新端口))
(CONNECT_DATA = (SERVICE_NAME = ccis_hd))
)';
----3 验证 DBLink 是否成功
SELECT * FROM dual@LINK_TO_A;
2 Cmd常用功能
2.0 cmd 进入 sqlplus如何复制粘贴
右键cmd顶部标题栏--》属性--》勾选快速编辑模式，可以选中，右键复制
粘贴时直接Ctrl V即可
2.1 Oracle导入 sql文件
cmd 进入 命令窗口
输入：sqlplus 用户名/密码@ip地址:端口号/数据库名称（实例哈）
进入后
输入 ： @F:\R1TEST.sql(你的文件的位置)
直接回车 就ok了
2.2 Oracle导入导出 imp/exp导入导出数据库
imp wlj/wlj@orcl file=d:exportoracle.dmp full=y ignore=y
参考:使用imp命令和exp命令对oracle数据库进行导入导出操作 - 代码猫 - 博客园 (cnblogs.com)
exp username/password FILE=/ora_data1/20210527/demo_table.dmp log=/ora_data1/20210527/20210527.log tables=demo_table QUERY=\"WHERE id in \(\'1\'\,\'2\'\)\"
2.3 Oracle导入 数据泵导入
create directory data_pump_dir1 as 'E:\00\aa'; grant read,write on directory data_pump_dir1 to username; impdp username/password directory=data_pump_dir1 dumpfile=dmpname.dmp remap_schema=oldusername:username remap_tablespace=old_TBS:username_tbs,TEMP:username_tbs_tmp TABLE_EXISTS_ACTION=REPLACE,full = y
参考：数据泵导入文件 - 菜鸟乙 - 博客园 (cnblogs.com)
2.4 Oracle导入 数据泵停止
正在用数据泵impdp/expdp导入导出，如何停止数据泵的导出导入工作
#查看执行任务 以sysdba登录sqlplus select job_name,status from dba_datapump_jobs; #切回 cmd恢复导入任务 impdp/expdp username/password attach=job_name; stop_job=immediate;
2.5 Oracle导入 文件大批量
cmd 进入到xindai.ctl 文件所在路径
执行 sqlldr L7WD_20190624/meimima@192.156.36.63/ccms control=xindai.ctl log=222.log direct=y
xindai.ctl ：
load data
characterset ZHS16GBK
infile 'ECIF_M_CI_CUSTOMER_999009_20190430beifen.txt'
into table ECIF_LOAN_TEST
truncate
fields terminated by ","
optionally enclosed by '""'
trailing nullcols(
MFCUSTOMERID "trim(:MFCUSTOMERID)",
CORECUSTOMERID "trim(:CORECUSTOMERID)"
)
注意：要插的表中不能有主键，字段不能设置为null
2.6 Oracle cmd中startup显示运行不了显示“不是内部或外部命令
解决方案： 1 在C:\Windows\System32中检查cmd.exe是否存在（如果存在的话）（检查cmd.exe是否被误删）
2 在我的电脑——属性——环境变量——在系统变量找到Path编辑前面添加%systemroot%\system32;如果有站删除保存后再复制进去就好了
2.7 Oracle 忘记用户名密码
2.8 删除表空间
--数据文件不会删除
drop tablespace “tbs4”;
--数据文件会一起删除
drop tablespace tbs4 including contents and datafiles;
https://blog.csdn.net/qq_42774325/article/details/81913252
2.9 Oracle删除用户(级联关系也删除掉)
drop user username cascade;
2.A Oracle用户关联表空间
create user username identified by password default tablespace tablespacename ;
alter user username default tablespace tablespacename ；
参考：oracle用户关联表空间_You can shine.-CSDN博客
2.B Oracle查看当前数据库实例名
select instance_name from v$instance;
2.C 表空间不足解决方案：
-- 查看所有表空间使用 情况 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 4 desc; --查看表空间使用情况 select tablespace_name,sum(bytes)/1024/1024 from dba_data_files group by tablespace_name; --查看表空间空闲情况 select tablespace_name,sum(bytes)/1024/1024 from dba_free_space group by tablespace_name; -- 直接truncate或者drop 可以释放出表空间， delete 不可以释放表空间 -- truncate table table_name_20160328;
3 报错
3.1 oracle超出打开游标的最大数的原因和解决方案
1 检查rs是否全部关闭
2 检查数据库中的 OPEN_CURSORS 参数值。
Oracle 使用 init.ora 中的初始化参数 OPEN_CURSORS 指定一个会话一次最多可以拥有的游标数。缺省值为 50。要获得数据库中 OPEN_CURSORS 参数的值，可以使用以下查询：
SQL> show parameter open_cursors;
修改open_cursors
SQL> alter system set open_cursors=1000;
系统已更改。
SQL> commit;
提交完成。
3.2 Oracle提示TNS:无监听程序的解决办法
1。确认服务是否开启（实例服务和监听服务）
2.修改..\app\admin\product\11.2.0\dbhome_1\NETWORK\ADMIN\listener.ora文件
SID_NAME = CLRExtProc 改为 SID_NAME = orcl （orcl是实例名）
HOST = localhost 改为 HOST = 192.168.1.111（远程连接改为IP地址）
3.修改..\app\admin\product\11.2.0\dbhome_1\NETWORK\ADMIN\tnsnames.ora文件
两处 HOST = localhost 改为 HOST = 192.168.1.111 （远程连接为改为IP地址）
SID = CLRExtProc 改为 SID = orcl
4.好了,重启oracle的上边的两个服务就OK了。
原文链接：https://blog.csdn.net/qq_40110871/article/details/83444061
3.3 ORA-00972: identifier is too long
该错原因:查询的字段太长(通常超过29位，29位不会报错)。
3.4 ORA-01012:
Oracle安装目录的 sqlnet.ora配置了SQLNET.ALLOWED_LOGON_VERSION=X 导致
3.5 ORA-01109: 数据库未打开.
误删dbf文件造成
1.cmd-sqlplus /nolog-conn system/pwd as sysdba
2.shutdown immediate;
3.startup mount;
4.alter database datafile 'd:\*.dbf' offline drop;
alter database open;
http://hi.baidu.com/tned/blog/item/07ab8525ff3e026734a80f08.html
3.6 ORA-01658:（转自52斋347）
在oracle里创建表，报出错：ORA-01658: 无法为表空间space中的段创建 INITIAL 区；或者： ORA-01658: unable to create INITIAL extent for segment in tablespace string，查了些资料，原来是因为表空间不够用了，增加了表空间后，问题解决了。 查看表空间大小SQL: select SEGMENT_TYPE,owner,sum(bytes)/1024/1024 from dba_segments where tablespace_name='KMIS_DATA' group by segment_type,owner 更改表空间大小SQL: alter tablespace space add datafile 'D:\ORACLE\ORADATA\CJF\TEMP03.ora' size 10m;
3.7 ORA-01722:无效数字
遇到该问题，发现是小于1的(四位小数)数据导致的
只有高版本12版本或以上有这个问题，11版本没有
下面转载:
转自：http://blog.itpub.net/11320622/viewspace-700469/
oracle小于1的浮点转成字符时，丢掉前面0，变成点几的情况
解决方案:
select to_char(.516,'fm999999990.9999999') from dual t; -- 0.516
select to_char(.516,'999999990.9999999') from dual t; -- 0.5160000 前面有空格，fm的作用就是去掉前面空格和后面的占位0
select to_char(.516,'fm999999990.000000') from dual t; -- 0.516000
SELECT to_char(2,'fm00') FROM dual; -- 02
SELECT to_char(2,'fm9990.000000') FROM dual; -- 2.000000
SELECT to_char(2,'fm9990.999999') FROM dual; -- 2. fm去掉前面的空格，同时还去掉了小数位的占位0
SELECT to_char(2,'9990.999999') FROM dual; -- 2.000000
SELECT trim(to_char(2,'9990.999999')) FROM dual; -- 2.000000 trim可以去掉前面的空格，并且保持勒后面的占位，避免 2. 没有 小数位0的情况
3.8 ORA-02374 ORA-12899 ORA-02372 (impdp)
原因：原库字符集（16GDK）和目标数据库字符集(UTF-8)不同。
解决方案：删除当前实例，新建实例时选择16GDK字符集（删除新建实例参考3.8ORA-02374）
3.9 ORA-28040 plsql连接不上数据库
sqlnet.ora低版本改过
换驱动jar包改过
环境变量换过
各种方式都尝试了都不行，最后领导给了个三个文件，都放到oracle安装目录的network/admin文件路径下，就好了，三个文件路径
https://www.cnblogs.com/1446358788-qq/articles/14932908.html
3.A ORA-65096:共用用户名或角色无效
原因：使用的是可插拔数据库（12C的新特性）
解决方案：删除当前实例，并新建实例使用非插拔数据库
删除实例：win-->Oracle-->database configuration assistant-->删除数据库--》选定指定实例卸载即可
新建非插拔数据库：win-->Oracle-->database configuration assistant-->新建数据库--》下一步-->创建模式中 创建为容器数据库 不再勾选 即可。
参考：Oracle12c创建新用户提示公共用户名或角色无效 - Fangzhi杰 - 博客园 (cnblogs.com)
3.B java.sql.BatchUpdateException: ORA-01438: 值大于为此列指定的允许精度
原因：pl/sql number(n),插入的位数大于n
4 Oracle 安装应用
4.1 Oracle win10 64位安装数据库
转载网址:Oracle 12c Release 2 windows安装详解-百度经验 (baidu.com)
留下备忘网址：Oracle安装备忘 - 菜鸟乙 - 博客园 (cnblogs.com)
安装路径为：安装路径在G:\app\Administrator\product\11.2.0\dbhome_2\
安装完成后，查看对应目录G:\app\Administrator\product\11.2.0\dbhome_2\NETWORK\ADMIN 是否生成listener.ora和tnsnames.ora，如果没有，检查是否配置了环境变量.三个变量，分别是NLS_LANG、ORACLE_HOME、TNS_ADMIN。
ORACLE_HOME的值应为安装路径，即G:\app\Administrator\product\11.2.0\dbhome_2\ ，而TNS_ADMIN的路径应为G:\app\Administrator\product\11.2.0\dbhome_2\NETWORK\ADMIN，修改之后，再次配置监听程序和本地网络服务名，ADMIN目录下出现对应的文件。
参考网址：https://blog.csdn.net/wqztmx4/article/details/99954754
4.2 Oracle配置监听
windows-->Oracle--》Net Configuration Assistant--》监听程序配置--》下一步--》添加--》下一步--》LISTENER-->下一步--》TCPS/TCP-->下一步--》使用标准端口号1521--》下一步--》是否配置另一个监听程序选否--》下一步--》完成
参考：Oracle 11g使用Net Configuration Assistant工具配置监听程序_程序不是缘的博客-CSDN博客
4.3 Oracle卸载数据库(12C)
完全卸载Oracle方法(超详细)_Ninewind-CSDN博客_oracle卸载
4.4 Oracle 删除实例
windows-->oracle-->database configuration assistant-->删除数据库-
4.5 windows 查看Oracle安装路径
windows r-->services.msc-->OracleServiceORCL-->双击--》可执行文件的路径
4.6 Oracle 运行的linux空间 temp.dbf 文件过大 导致空间不足
第一步：删除文件
alter database tempfile 'F:\oracle\product\10.1.0\oradata\orcl\TEMP01.DBF' drop;
第二步：建立新文件
alter tablespace temp add tempfile
'F:\oracle\product\10.1.0\oradata\orcl\TEMP01.DBF'
size 2048M reuse autoextend on next 100M;
第三步：
select d.file_name, d.file_id, d.tablespace_name, d.bytes
from dba_temp_files d;
第四步：关闭自动增长
alter database tempfile 'F:\oracle\product\10.1.0\oradata\orcl\TEMP01.DBF' autoextend off;
转载自：原文链接：https://blog.csdn.net/qq493820798/article/details/83181010
5 PLSql应用
5.1 plsql 调试函数
右键函数 add debug information，右键函数 test即可
5.2 plsql 常用功能全库 搜索关键字（仅限表结构，不是表数据）
tools--find database objects -- text to find --search
5.3 plsql 常用功能大小注释写标记颜色
选中标注部分，右键--selection
5.4 plsql f8 执行所在行
tools --preference--window types--autoselect statement
5.5 plsql 自动补全
tools--preference--user interface--editor--autoreplace
5.6 Initialization error SQL*Net not properly installed
1 错误描述：
2、错误原因
由于我在win10 64位操作系统上安装了32位PL/SQL导致出错，出现兼容性连接问题
3、解决方法：
需要instantclient_11_2文件夹（32位）
将instantclient_11_2文件夹放置到plsql安装目录，打开plsql后（未登录状态）
工具==》首选项==》OCI库粘贴上 D:\java\data\oracle\PLSQL\PLSQL Developer\instantclient_11_2\oci.dll 即可（红色标注为指定的文件夹）
5.7 Initialization error Could not load ...oci.dll
toos-->preferences-->Oracle-->Connection-->OracleHome 和 OCILibrary分别设置即可
(注意通常设置32位Oracle客户端的，因为安装的是64位的服务端，plsql只有32位的，所以只能安装个32位Oracle客户端，plsql这部分设置的地址是连接32位客户端的 见5.7 Initialization error SQL*Net not properly installed)
参考：Initialization error Could not load ".../oci.dll"_"杰"出虚拟平台-数据运维~~-CSDN博客
6 概念
6.1 Oracle数据库、实例、用户、表空间、表之间的关系
https://www.cnblogs.com/zjhs/p/3147905.html
3.3 ORA-00972: identifier is too long
浙公网安备 33010602011771号