oracle计算hash值
1、dbms_utility.get_hash_value(
name VARCHAR2,
base NUMBER,
hash_size NUMBER
)

函数说明
name：输入值
base：返回hash value的起始值（hash bucket最小值）
hash_size：返回hash表的期望大小（hash bucket最大值）

这个函数用于计算并返回落在给定范围内的hash值

2、ora_hash(
expr VARCHAR2,
max_bucket NUMBER,
seed_value NUMBER
)

函数说明
expr：输入值
max_bucket：函数返回的最大值（范围是0到4294967295）
seed_value：随机数（默认为0），可以对同一个输入值返回不同的结果

这个函数用于计算带随机数的hash值

3、例子
1）ora_hash和dbms_utility.get_hash_value返回值不同
select ora_hash(‘CHL20190710T25180748988’, 99999) as “hash1”, dbms_utility.get_hash_value(‘CHL20190710T25180748988’, 0, 99999) as “hash2” from dual;

 hash1    hash2

 78481    15674

2）seed_value不同，ora_hash返回值也不同
select ora_hash(‘CHL20190710T25180748988’, 99999, 0) from dual;
结果：78481
select ora_hash(‘CHL20190710T25180748988’, 99999, 1) from dual;
结果：57583
select ora_hash(‘CHL20190710T25180748988’, 99999, 2) from dual;
结果：97214

参考资料：
https://docs.oracle.com/cd/E11882_01/server.112/e41084/functions124.htm#SQLRF06313
https://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_util.htm#ARPLS73195
https://blog.csdn.net/zutsoft/article/details/44203027

oracle 10g没有continue功能，在11g才加入。如果要在10g实现continue，可以用goto代替。

--/
begin
  for i in 1 .. 20
  loop
    if i < 10 then
      GOTO print_now;
    end if;
    dbms_output.put_line(i);
    <<print_now>>
    NULL;
  end loop;
  dbms_output.put_line('111');
end;
/

oracle的ASCII、ASCIISTR函数

1、ASCII函数
作用是将字符转换为ascii码值，中文没有ascii码值，应该是转成某一编码再取编码的ascii值。

2、ASCIISTR函数
ASCIISTR函数，参数是一个字符串，如果这个字符在ASCII码表中有，则转成ASCII表中的字符。
如果没有，则转成\xxxx格式，xxxx是UTF-16的编码。
如果表中只有中文和英文、数字等字符，则可以用\来判断是否带有中文。
举例：
select * from bacs.tcim_per_account where ASCIISTR(USER_ID) LIKE ‘%\%’;

3、对比区别

select ASCIISTR('A'), ASCIISTR('b'), ASCIISTR('才'), ASCII('A'), ASCII('b'), ASCII('才') from dual;
ASCIISTR('A') ASCIISTR('B') ASCIISTR('才') ASCII('A') ASCII('B') ASCII('才')
------------- ------------- ------------- ---------- ---------- ----------
A             b             \624D                 65         98   15108493

一、oracle截取字符串
1、substr
SUBSTR(“字符串”, 起始位, 截取长度)
从起始位置截取一定长度的字符串，起始位置从1开始
SUBSTR(“字符串”,起始位)
从起始位置截取到结尾的字符串

2、substrb
substrb格式和substr一致，substr()是按照字符来算的，而substrb()是按照字节来算的，对中文的计算不一致
在substr里一个中文算1个长度，在substrb里一个中文算2个长度

二、oracle查找字符位置
1、instr
INSTR(‘源字符串’,’要查找的字符串’,查找的开始位置,需要定位到第几次出现的地方)
返回字符串出现的位置

2、例子
select instr(‘12345678@qq.com’, ‘@’, 1) from dual;
结果：9
select substr(‘12345678@qq.com’, instr(‘12345678@qq.com’, ‘@’, 1)) from dual;
结果：@qq.com

oracle数据库磁盘满了，常用的处理方法：
1）清空无用的大表
2）删除日志
3）转移表空间文件

一、准备工作
0、切换到oracle用户

su - oracle

1、查看oracle安装目录

echo $ORACLE_HOME
/u01/oracle/product/10.2.0/db_1

2、查看实例名称

echo $ORACLE_SID
pbcssit

二、清空无用的大表
1、查看数据占用大小

select * from dba_segments where owner = 'NPBCS';

plsql在BYTES列，右键 – Column Totals – Sum

可以看到数据占用了6G多的空间

建议：按照BYTES大小降序排列，查看是否有历史表、临时表可以清空

2、查看表空间文件

select * from dba_data_files;

查看表空间文件占用磁盘大小，关闭自增功能，修改AUTOEXTENSIBLE为NO
因为有些表自动扩展是无限制的，会把磁盘都占满，测试环境可以把表空间初始值设置的大一点，如果满了手工添加

3、查看表空间剩余大小

select * from dba_free_space;

三、删除日志
1、cd到oracle安装目录下

du -sh *
4.8M	adump
5.5G	bdump
73M	cdump
8.0K	dpdump
8.0K	pfile
3.0G	udump

删除365天前的文件：

find . -name "*" -mtime +365 -exec rm -rf {} \;

四、转移表空间文件
通过软链接把表空间文件移动到有空闲空间的磁盘。

五、oracle状态查询
1、查看oracle自有进程

ps -ef | grep ora_

2、查看oracle SID

echo $ORACLE_SID

3、查看oracle安装目录

echo $ORACLE_HOME

4、查看oracle连接数

ps -ef | grep oracle

看 (LOCAL=NO) 数量，表示不是本地连接，为外部连接进来的

5、查看系统session数量

select * from v$session;

6、查看参数文件配置

select * from v$parameter;


普通创建索引的命令create index会先锁表，然后再创建索引，如果表中数据量很大，会造成阻塞DML语句。

加了online后，可以以非独占的方式创建和删除索引。

但是加online字段有一些限制：
ONLINE
Specify ONLINE to indicate that DML operations on the table will be allowed during creation of the index.

Restrictions on Online Index Building
Parallel DML is not supported during online index building. If you specify ONLINE and then issue parallel DML statements, Oracle returns an error.
You cannot specify ONLINE for a bitmap index or a cluster index.
You cannot specify ONLINE for a conventional index on a UROWID column.
For a unique index on an index-organized table, the number of index key columns plus the number of primary key columns in the index-organized table cannot exceed 32.

参考资料：
http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_5012.htm

有一个sql要统计多个字段的sum值：

select card_accp_id c1,date_settlmt_8, sum(amt_trans), sum(mcht_fee), sum(host_trans_fee1)
from bth_new_gc_txn_succ where date_settlmt_8 > '20160101' group by card_accp_id,date_settlmt_8

执行报错：

14:14:36 [SELECT - 0 row(s), 0.000 secs] [Error Code: 1722, SQL State: 42000] ORA-01722: invalid number
... 1 statement(s) executed, 0 row(s) affected, exec/fetch time: 0.000/0.000 sec [0 successful, 0 warnings, 1 errors]

解决：
原因是sum字段有空格，在host_trans_fee1上加个trim()

结果：

select card_accp_id c1,date_settlmt_8, sum(amt_trans), sum(mcht_fee), sum(trim(host_trans_fee1))
from bth_new_gc_txn_succ where date_settlmt_8 > '20160101' group by card_accp_id,date_settlmt_8

sum字段如果遇到有记录为非number会报错

分区表的概念，简单来说就是：原来一个用户下表的数据是放在一个表空间里，使用了分区表后，表的数据是放在多个表空间中。

PS：表空间是包括一个用户下所有文件的逻辑概念。

如果一个数据库中，某几个表（特别是历史表）占了数据库50%以上的空间，或达到几百G的磁盘容量。这时就要考虑使用分区表了。

Oracle表分区分为四种：范围分区（Range分区），散列分区（Hash分区），列表分区（List分区）和复合分区（范围-散列分区range-hash，范围-列表分区range-list）

分区的好处，主要用到以下两点：
改善查询性能
维护备份数据方便

一、范围分区
范围分区就是根据表中某一字段的值的范围来分区，特别适用于时间日期、ID号来分区

1、建立测试用户

create user wangyi identified by wangyi;
grant dba to wangyi;
conn wangyi/wangyi;

2、建立测试表和数据
//策略是按月创建分区，保留一年数据，年末备份前年数据。

例子：

create table range_table (
inst_date date,
produce_id number,
amt varchar(12)
)
partition by range(inst_date)
(
partition p1 values less than(to_date('01/02/2016','DD/MM/YYYY')),
partition p2 values less than(to_date('01/03/2016','DD/MM/YYYY')),
partition p3 values less than(to_date('01/04/2016','DD/MM/YYYY')),
partition p4 values less than(to_date('01/05/2016','DD/MM/YYYY')),
partition p5 values less than(to_date('01/06/2016','DD/MM/YYYY'))
);

继续阅读oracle 分区表 →

一、interval函数
interval函数用于将一个整形值转换成时间值date类型的年月或时分秒。

oracle语法：

interval 'integer [- integer]' {YEAR | MONTH | DAY | HOUR | MINUTE | SECOND} [(precision)][TO {YEAR | MONTH | DAY | HOUR | MINUTE | SECOND}]

注：
1、年、月、日、时、分、秒可以单独使用。
2、{YEAR | MONTH} to {YEAR | MONTH}和
{DAY | HOUR | MINUTE | SECOND} to {DAY | HOUR | MINUTE | SECOND}是分别使用。
3、该函数常用来计算一段时间差。
4、precision为精度域，有效范围是0到9，默认值为2。这里精度是指显示结果的精度，如果结果超过两位长度要指定精度。

例子：
1、表示2个年

select interval '2' year from dual;

INTERVAL'2'YEAR
---------------------------------------------------------------------------
+02-00

2、表示1234个年

select interval '1234' year(4) from dual;

INTERVAL'1234'YEAR(4)
---------------------------------------------------------------------------
+1234-00

继续阅读oracle interval函数 →

一、删除一个用户下所有数据库对象
一种方法是删除这个用户然后重建，需要管理员操作：

drop user wangyi cascade; --删除用户
select * from dba_users where username = 'wangyi'; --查询默认表空间
drop tablespace WANGYI_DTBS including contents and datafiles; --删除表空间

重建用户：

create user wangyi identified by wangyi;
grant create session, create view, create any index, imp_full_database to wangyi;
alter user wangyi default tablespace wangyi temporary tablespace temp;
alter user wangyi quota unlimited on wangyi;

二、清除整个表的内容

delete from 表名;
truncate table 表名;

delete和truncate的区别：
delete删除的数据可以恢复，truncate不能恢复
delete高水位线不会下降，truncate高水位线会下降（释放表空间）
继续阅读oracle清理数据问题 →