oracle
kong-kong
记录流水账
展开
-
oracle表空间大小
今天报这个错了:Cause: java.sql.SQLException: ORA-01653: unable to extend table YTT.S_PRODUCT by 128 in tablespace USERS查了下是表空间不足了# 查看表空间及最大可用大小SELECT file_name,tablespace_name,bytes / 1024 / 1024 "bytes MB",maxbytes / 1024 / 1024 "maxbytes MB"FROM dba.原创 2020-06-24 09:50:59 · 364 阅读 · 0 评论 -
oracle connect by 取树的叶子节点、根节点
往上取到根节点(往上)select i.inventorycategoryid,i.fflbs,level,i.flbm from s_inventory_category i start with i.inventorycategoryid='EFA0A37F07AA4300B7D87598C7C91C36' connect by ...原创 2020-03-18 19:30:13 · 1680 阅读 · 0 评论 -
oracle锁表
#查看锁表select a.object_name,b.session_id,c.serial#,c.program,c.username,c.command,c.machine,c.lockwaitfrom all_objects a,v$locked_object b,v$session c where a.object_id=b.object_id and c.sid=b...原创 2019-04-09 15:39:52 · 299 阅读 · 0 评论 -
plssql 数组
declare TYPE id_array_type IS TABLE OF VARCHAR2(32) INDEX BY BINARY_INTEGER; inventoryIds id_array_type;begin -- 菜品1 id inventoryIds(1) := 'a1'; -- 菜品2 id inventoryIds(2) := '...原创 2019-04-28 09:46:24 · 352 阅读 · 0 评论 -
oracle 循环
declare cursor cur is select * from s_user where rownum=1; urow s_user%rowtype; begin dbms_output.enable(1000000); for urow in cur loop dbms_ou...原创 2019-04-25 10:07:42 · 203 阅读 · 0 评论 -
oracle plsql template
declare cursor cur is SELECT * FROM ( SELECT A.*, ROWNUM RN FROM ( select d.tbbm,i.inventoryid,i.chmc,i.jgbs,i.zhbs,i.chlx,i.createtime ...原创 2019-04-25 14:02:19 · 611 阅读 · 0 评论 -
oracle 函数
# 年月日to_char(xdsj,'yyyy-MM-dd')#时分to_char(xdsj,'hh24:mi')原创 2019-05-06 15:48:25 · 198 阅读 · 0 评论 -
oracle查看表结构信息
1. 查看吧表结构字段select * from user_tab_columns where table_name='S_PRODUCT_PLAN_SALE_TYPE_POINT';原创 2019-06-21 10:36:42 · 4110 阅读 · 0 评论 -
oracle导入导出
# 导出exp shr/shr@orcl file=C:\backup\0820\shr0820.dmp owner=(shr) buffer=4096 log=C:\backup\0820\shr0820.log# 导入imp kq/kq@orcl buffer=4096 file=C:\backup\0820\shr0820.dmp FROMUSER=shr TOU...原创 2019-08-20 15:19:32 · 241 阅读 · 0 评论 -
oracle游标
declare cursor cursor_order is select * from s_sale_order o where o.xsqd=6 and o.xslb is null; --定义rowtype cur_order s_sale_order%rowtype; begin open cursor_order; --loop循环 ...原创 2019-08-20 18:49:56 · 231 阅读 · 0 评论 -
oracle plsql变量定义
# 常量c_defaultSaleType constant int:=1;# 定义rowtype cur_order s_sale_order%rowtype; # string变量v_message varchar2(256);# 引用 tablename.column%typev_site_name s_merchant_site.zdmc%type;...原创 2019-08-21 10:21:19 · 1108 阅读 · 0 评论 -
oracle自定义类型
TYPE siteType is record ( id varchar(32), name s_merchant_site.zdmc%type, zdbh s_merchant_site.zdbh%type ); site siteType; select id,name,zdbh into site from s_product_plan_...原创 2019-08-21 18:00:45 · 612 阅读 · 0 评论 -
druid连接池参数
https://github.com/alibaba/druid/wiki/DruidDataSource%E9%85%8D%E7%BD%AE# 在生产环境一般是不开启的,主要是性能考虑# 指明是否在从池中取出连接前进行检验,如果检验失败, 则从池中去除连接并尝试取出另一个# 注意: 设置为true后如果要生效,validationQuery参数必须设置为非空字符串testOn...原创 2019-08-21 22:23:35 · 1204 阅读 · 0 评论 -
oracle listagg等函数
函数: listagg某个字段多条数据合起来,类似StringUtils.join()用法:select listagg (列名, ',') WITHIN GROUP (ORDER BY 列名) as 别名 from 表名 where 条件表达式;示例:select listagg(ms.zdmc,'-') WITHIN GROUP (ORDER BY zdbh)fro...原创 2019-08-22 14:37:30 · 571 阅读 · 0 评论 -
oracle存储过程出参
outcreate or replace procedure GET_POINT_ID_BY_NAME_TMP(p_name varchar,p_point_id out varchar)原创 2019-08-26 19:50:09 · 1677 阅读 · 0 评论 -
oracle plsql while循环
whiledeclare v_size int; v_isLast int; v_index int;begin -- 每一次处理10条 v_size :=10; -- 结束标志符 -1退出 v_isLast :=1; -- 处理次数 v_index :=0; while v_isLast!=-1 loop v_index ...原创 2019-08-29 18:18:35 · 813 阅读 · 0 评论 -
oracle单引号转义
# ''yyyy-MM-dd''declare num number;begin select count(1) into num from user_indexes where table_name=upper('S_SALE_ORDER') and index_name=upper('S_SALE_ORDER_USER_INDEX'); if num =0 then ...原创 2019-09-11 18:28:47 · 568 阅读 · 0 评论 -
oracle空间统计
# 某个表空间大小select sum(bytes)/(1024*1024) as "size(M)" from user_segments where segment_name=upper('tablename');select * from user_segments where segment_name=upper('tablename');# 库空间占用大...原创 2019-03-26 14:20:59 · 420 阅读 · 0 评论 -
oracle登录/创建用户/删除用户
windows 启动cmd --> sqlplus /nologcmd --> set ORACLE_SID=RESTAURANTsql --> connect / as sysdbasql --> startup启动startup pfile=C:\app\Administrator\admin\RESTAURANT\pfile\init.or...原创 2018-11-12 17:06:35 · 370 阅读 · 0 评论 -
oracle中如何创建/删除数据库的实例
创建/删除数据库的实例 开始 --> 程序 --> OraDb11g --> 配置和移植工具 --> Database Configuration Assist原创 2018-12-06 12:37:28 · 2011 阅读 · 0 评论 -
oracle lsnrctl
日志目录: $ORACLE_BASE\diag\tnslsnr\<hostname>\listener\trace\<listener_name>.log命令行输入 lsnrctl status#查看日志状态show log_status#关闭日志set log_status off#打开日志set log_status on...原创 2018-12-06 13:37:02 · 372 阅读 · 0 评论 -
ORA-12526:TNS:监听程序:所有适用例程都处于受限模式
连接oracle数据库出现 oracle ORA-12526:TNS:监听程序:所有适用例程都处于受限模式今天连接oracle数据库出现 oracle ORA-12526: TNS: 监听程序: 所有适用例程都处于受限模式。查了下原来之前改字符集时执行了: ALTER SYSTEM ENABLE RESTRICTED SESSION;解决办法:ALTER SYSTEM DISABLE R...转载 2018-12-06 13:22:57 · 5329 阅读 · 0 评论 -
TNS-12541: TNS: 无监听程序 TNS-12560: TNS: 协议适配器错误 TNS-00511: 无监听程序
C:\>lsnrctl statusLSNRCTL for 64-bit Windows: Version 11.1.0.7.0 - Production on 12-11月-2012 18:18:32Copyright (c) 1991, 2008, Oracle. All rights reserved.正在连接到 (DESCRIPTION=(ADDRESS=(PR...转载 2018-12-06 13:36:21 · 4273 阅读 · 1 评论 -
Oralce服务器 Plsql不能选择数据库示例问题
#服务器D:\app\Administrator\product\11.2.0\dbhome_1\NETWORK\ADMIN#clientD:\app\Administrator\product\11.2.0\client_1\network\admin\tnsnames.oraRESTAURANT = (DESCRIPTION = (ADDRESS = ...原创 2018-12-06 16:59:38 · 895 阅读 · 0 评论 -
Oracle创建directory
1. 新建directory的语法CREATE [OR REPLACE] DIRECTORY directory AS 'pathname';#需手工创建database目录create or replace directory dump_dir as 'D:\database'create or replace directory是在oracle中创建1个directory对象,...原创 2018-12-06 17:37:16 · 16071 阅读 · 0 评论 -
oracle 导入导出
导入:#从shr实例导入到kq实例impdp USERID='kq/kq@RESTAURANT as sysdba' remap_schema=shr:kq directory=dump_dir dumpfile=shr1.DMP logfile=test.log # 将源表空间的所有对象导入到目标表空间中# remap_tablespace=ts_test1_prod:t...原创 2018-12-07 15:22:10 · 200 阅读 · 0 评论 -
oracle 函数查询
#正则匹配select * from t_authority where regexp_like(code, '^[a-z].*$');原创 2018-12-14 11:06:07 · 1342 阅读 · 0 评论 -
oracle索引操作
#创建索引create index sale_order_xdsj_index on t_sale_order(to_char(xdsj,'yyyy-MM-dd'),orgid,xsqd)#创建唯一索引create index unique index_name on table(column_name1,column_name2);#查看某个表的索引select * f...原创 2018-12-10 16:32:14 · 289 阅读 · 0 评论 -
oracle脚本维护
declare num number;begin select count(1) into num from user_indexes where table_name=upper('t_sale_order') and index_name=upper('sale_order_xdsj_index'); if num =0 then execute immediat...原创 2018-12-10 17:50:23 · 304 阅读 · 0 评论 -
plsql 条件判断语句
-- begin exception endbegin dbms_output.put_line('');exception when no_data_found then dbms_output.put_line('');end; -- IFIF condition THEN {...sta...原创 2019-02-19 13:37:59 · 4310 阅读 · 0 评论 -
plsql 笔记
----- 添加20条数据----------------------------------------------------------------------------- fordeclare V_START NUMBER := 1; begin FOR V_START IN 1..20 LOOP INSERT INTO BON...原创 2019-02-19 13:48:41 · 322 阅读 · 0 评论 -
oracle sql
1. rownumoracle前10条select * from accounts t where rownum<11 分页 取 10 到20条 :select * from (select t.*,rownum r from accounts t) a where a.r>=10 and a.r<=20; 分页:select * ...原创 2019-02-19 13:51:13 · 214 阅读 · 0 评论 -
oracle分页
SELECT * FROM ( SELECT A.*, ROWNUM RN FROM (select * from s_sale_order) A WHERE ROWNUM <= 20 ) WHERE RN >= 0原创 2019-03-15 09:28:45 · 201 阅读 · 0 评论 -
java.sql.SQLRecoverableException: 关闭的语句
https://github.com/alibaba/druid/issues/1708https://github.com/alibaba/druid/issues/1614 1.0.28关闭pscache,或者升级1.0.29当关闭poolPreparedStatements配置,就没有关闭的语句这个问题 https://github.com/alibaba/druid...原创 2018-11-08 02:10:16 · 12730 阅读 · 1 评论