oracle简单运维
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;
oracle 创建索引 create index online
普通创建索引的命令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
Oracle sum字段报错 ORA-01722: invalid number
有一个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会报错
oracle 分区表
分区表的概念，简单来说就是：原来一个用户下表的数据是放在一个表空间里，使用了分区表后，表的数据是放在多个表空间中。
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函数
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清理数据问题
一、删除一个用户下所有数据库对象
一种方法是删除这个用户然后重建，需要管理员操作：
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高水位线会下降（释放表空间）
PL/SQL基础学习（4）
--1、select * into 必须只满足一条记录 没有记录或有多条记录会报异常
--只能取出一条 要取多条要用游标
--2、update和delete语句没有记录或多条记录满足都不会出现异常
3、
--select * into 如果没找到记录会出现异常
--/
DECLARE
v_emp emp%rowtype;
BEGIN
select * into v_emp from emp where empno = 9999;
dbms_output.put_line(v_emp.ename);
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('未找到记录');
END;
/
4、
--select * into 如果有多行记录满足也会出现异常
--/
DECLARE
v_emp emp%rowtype;
BEGIN
select * into v_emp from emp where job = 'MANAGER';
dbms_output.put_line(v_emp.ename);
EXCEPTION
WHEN TOO_MANY_ROWS THEN
dbms_output.put_line('超过一行返回');
END;
/
oracle数据库对象-游标
游标分为隐式游标和显示游标。
一、隐式游标
对于select * into和DML操作（update、delete、insert）都附带有一个隐式游标。
隐式游标的属性
SQL%ROWCOUNT 整型 代表DML语句成功执行的数据行数 SQL%FOUND 布尔型 值为TRUE代表插入、删除、更新或单行查询操作成功 SQL%NOTFOUND 布尔型 与SQL%FOUND属性返回值相反 SQL%ISOPEN 布尔型 DML执行过程中为真，结束后为假
当系统使用一个隐式游标时，可以通过隐式游标的属性来了解操作的状态和结果，进而控制程序的流程。隐式游标可以使用名字SQL来访问，但要注意，通过SQL游标名总是只能访问前一个DML操作或单行SELECT操作的游标属性。所以通常在刚刚执行完操作之后，立即使用SQL游标名来访问属性。
例子：
--/
DECLARE
v_emp emp%rowtype;
BEGIN
delete from emp where empno = 9999;
IF SQL%notfound THEN
dbms_output.put_line('隐式游标 属性为notfound');
END IF;
dbms_output.put_line('delete无记录不会出现异常');
END;
/
PL/SQL基础学习（3）
1、--helloworld程序
--/
BEGIN
DBMS_OUTPUT.PUT_LINE('Hello World!!!');
END;
/
2、--定义变量
--变量名 类型
--/
DECLARE
A NUMBER(5, 2) := -1;
B NUMBER(5, 2) := 3;
C NUMBER(5, 2);
BEGIN
C := A * B / (A + B);
DBMS_OUTPUT.PUT_LINE('c=' || C);
EXCEPTION
WHEN ZERO_DIVIDE THEN
DBMS_OUTPUT.PUT_LINE('除数不能为0！');
END;
/
3、--定义常量
--/ DECLARE a CONSTANT NUMBER := 100; BEGIN DBMS_OUTPUT.PUT_LINE(a); END; /
oracle数据库对象-包
程序包是ORACLE PL/SQL的一个特性，它就像是一个容器或者说是一个命名空间，可以将各种逻辑相关的类型、常量、变量、异常和子程序结合在一起。为开发人员编写大型复杂应用程序时，提供了一个良好的组织单元。
当定义好了程序包之后，应用程序就可以通过包来访问各种不同的功能单元，而不用担心过多零散的子程序导致程序代码的松散。
一个PL/SQL程序包包含如下两个部分组成：
·包规范：主要是包的一些定义信息，不包含具体的代码实现部分。
·包体：包体是对包规范中声明的子程序的实现部分，包体的内容对于外部应用程序来说是不可见的，包体就像是一个黑匣子一样，是对包规范的实现。
1、包规范基本语法
CREATE [OR REPLACE] PACKAGE PACKAGE_NAME
{IS | AS}
PACKAGE_SPECIFICATION
END PACKAGE_NAME;
例子：
--/
CREATE OR REPLACE PACKAGE pack_fun IS
--定义一个函数返回两个数的和
FUNCTION fun_add(num1 IN NUMBER, num2 IN OUT NUMBER) RETURN NUMBER;
--定义一个函数返回两个数的差
FUNCTION fun_sub(num1 IN NUMBER, num2 IN OUT NUMBER) RETURN NUMBER;
--定义一个存储过程显示九九乘法表
PROCEDURE proc_1;
END;
/