分类目录归档：Oracle

1、inset into select

ALTER TABLE EMP NOLOGGING;--为了使插入时少产生log记录，要修改表，不能直接加nologging

1.1、单线程插入

INSERT /*+ APPEND */ INTO TEST
SELECT * FROM B.TEST;
--加上/*+ APPEND */要比不加这个速度快的多，因为这个直接导入到高水位之后，而且是直接块对块的插入，不用经过复杂的sql转换

1.2、多线程插入

--创建分区表
 create table emp
 (empno number,
 ename varchar2(20))
 partition by hash(empno)
 (partition part1,
 partition part2);
--执行多线程插入
alter session enable parallel dml;
insert /*+ PARALLEL(emp,2) */ into emp
select  OBJECT_ID,TIMESTAMP from all_objects;

2、sql loader
上次服务器被攻击，我导入日志文件使用的是c#程序导入到oracle数据库中
这次使用sql loadr实现
control文件为

load data
infile data.log "str '\n'"
into table gj2
replace
fields terminated by ' '
TRAILING NULLCOLS
(a1,a2,a3,a4,a5,a6,a7,a8,a9,a10,a11,a12,a13,a14)

创建对应表

create table gj2(a1 varchar(10),a2 varchar2(10),
a3 varchar2(100),a4 varchar2(100),a5 varchar2(100),a6 varchar2(100),
a7 varchar2(200),a8 varchar2(100),a9 varchar2(100),a10 varchar2(100),
a11 varchar2(200),a12 varchar2(100),a13 varchar2(100), a14 varchar2(100));

执行sql loader

sqlldr b/b control=c:\abc.txt log=log.tx

这个非常方便，很快就把几十万条记录导入到数据库中了，而且直接提出a1和a2列进行分析就可以得到上次通过程序导入到数据库，然后再通过orcale的正则表达式功能选择出时间和ip地址，相比之下这个简单多了，很好用。
原始数据：

2010-07-02 10:55:02 W3SVC689347672 192.168.10.222 HEAD /index.shtml - 80 - 221.232.180.193 - 403 1 0
2010-07-02 10:55:04 W3SVC689347672 192.168.10.222 HEAD /index.shtml - 80 - 121.35.96.46 - 403 1 0
2010-07-02 10:55:07 W3SVC689347672 192.168.10.222 HEAD /index.shtml - 80 - 119.178.8.242 - 403 1 0
2010-07-02 10:55:13 W3SVC689347672 192.168.10.222 HEAD /index.shtml - 80 - 124.236.72.63 - 403 1 0
2010-07-02 10:55:13 W3SVC689347672 192.168.10.222 HEAD /index.shtml - 80 - 58.60.253.104 - 403 1 0
2010-07-02 10:55:14 W3SVC689347672 192.168.10.222 HEAD /index.shtml - 80 - 120.10.3.64 - 403 1 0
2010-07-02 10:55:18 W3SVC689347672 192.168.10.222 HEAD /index.shtml - 80 - 221.196.165.225 - 403 1 0
2010-07-02 10:55:21 W3SVC689347672 192.168.10.222 GET /index.shtml - 80 - 116.16.159.224 Mozilla/4.0+(compatible;+MSIE+7.0;+Windows+NT+5.1;+SV1) 200 0 64
2010-07-02 10:55:21 W3SVC689347672 192.168.10.222 GET /anlewwwroot.zip - 80 - 120.84.23.2 Mozilla/4.0 404 0 64
2010-07-02 10:55:22 W3SVC689347672 192.168.10.222 GET /index.shtml - 80 - 122.226.148.190 Mozilla/4.0+(compatible;+MSIE+7.0;+Windows+NT+5.1;+SV1) 200 0 64
2010-07-02 10:55:22 W3SVC689347672 192.168.10.222 GET /index.shtml - 80 - 122.226.148.190 Mozilla/4.0+(compatible;+MSIE+7.0;+Windows+NT+5.1;+SV1) 200 0 64
2010-07-02 10:55:22 W3SVC689347672 192.168.10.222 GET /index.shtml - 80 - 121.204.33.120 Mozilla/4.0+(compatible;+MSIE+7.0;+Windows+NT+5.1;+SV1) 200 0 64
2010-07-02 10:55:22 W3SVC689347672 192.168.10.222 GET /index.shtml - 80 - 202.30.113.101 Mozilla/4.0+(compatible;+MSIE+7.0;+Windows+NT+5.1;+SV1) 200 0 64
2010-07-02 10:55:22 W3SVC689347672 192.168.10.222 GET /index.shtml - 80 - 58.101.51.214 Mozilla/4.0+(compatible;+MSIE+7.0;+Windows+NT+5.1;+SV1) 200 0 64
2010-07-02 10:55:22 W3SVC689347672 192.168.10.222 GET /index.shtml - 80 - 114.41.216.107 Mozilla/4.0+(compatible;+MSIE+7.0;+Windows+NT+5.1;+SV1) 200 0 64
2010-07-02 10:55:22 W3SVC689347672 192.168.10.222 GET /index.shtml - 80 - 58.101.51.214 Mozilla/4.0+(compatible;+MSIE+7.0;+Windows+NT+5.1;+SV1) 200 0 64
2010-07-02 10:55:22 W3SVC689347672 192.168.10.222 GET /index.shtml - 80 - 114.41.216.107 Mozilla/4.0+(compatible;+MSIE+7.0;+Windows+NT+5.1;+SV1) 200 0 64

使用sql loader处理后结果：

以前使用c#导入，oracle正则表达式处理日志为：
服务器遭攻击，oracle分析日志

一、profile管理
1、profile创建

create profile fei_profile limit
 sessions_per_user 1
 cpu_per_session 30000
 cpu_per_call 600
 connect_time 300
 idle_time 60
 logical_reads_per_session 300
 logical_reads_per_call 100
 failed_login_attempts 2
 password_life_time 1
 password_reuse_time 3
 password_reuse_max 10
 password_lock_time 1;

2、修改profile

alter profile fei_profile limit
connect_time 400
idle_time 200;

3、删除profile

drop profile fei_profile;

二、创建用户
1、准备工作

--查看表空间
select tablespace_name,contents from dba_tablespaces;
--查看默认临时表空间
select * from database_properties where property_name like '%TEMP_TABLE%';

2、创建用户
2.1数据库认证

create user abc
identified by abc
default tablespace abc
temporary tablespace temp
quota 10m on abc
quota 5m on users
password expire
account lock;

2.2、操作系统认证

--查看os_authent_prefix（前缀+操作系统用户名=数据库登陆名）
show parameter os_aut--查看结果为：OPS$
--创建用户
create user OPS$ff identified externally default tablespace abc;

三、修改用户

alter user abc account unlock;

四、删除用户

drop user abc cascade;--表示删除该用户下的所有objects

五、授权和回收
5.1、system级别权限

grant create session,create table to abc with admin option;--可以传递该授权（续传的权限不会被回收）
revoke create session,create table from abc;
grant create session,create table to fei identified by fei;
--用户不存在直接创建该用户，存在则修改用户密码

5.1.1权限类型

select * from session_privs;--查看本用户所具有的权限
ALTER SYSTEM
AUDIT SYSTEM
CREATE SESSION
ALTER SESSION
RESTRICTED SESSION
CREATE TABLESPACE
ALTER TABLESPACE
MANAGE TABLESPACE
DROP TABLESPACE
UNLIMITED TABLESPACE
CREATE USER
BECOME USER
ALTER USER
DROP USER
CREATE ROLLBACK SEGMENT
ALTER ROLLBACK SEGMENT
DROP ROLLBACK SEGMENT
CREATE TABLE
CREATE ANY TABLE
ALTER ANY TABLE
BACKUP ANY TABLE
DROP ANY TABLE
LOCK ANY TABLE
COMMENT ANY TABLE
SELECT ANY TABLE
INSERT ANY TABLE
UPDATE ANY TABLE
DELETE ANY TABLE
CREATE CLUSTER
CREATE ANY CLUSTER
ALTER ANY CLUSTER
DROP ANY CLUSTER
CREATE ANY INDEX
ALTER ANY INDEX
DROP ANY INDEX
CREATE SYNONYM
CREATE ANY SYNONYM
DROP ANY SYNONYM
SYSDBA
SYSOPER
CREATE PUBLIC SYNONYM
DROP PUBLIC SYNONYM
CREATE VIEW
CREATE ANY VIEW
DROP ANY VIEW
CREATE SEQUENCE
CREATE ANY SEQUENCE
ALTER ANY SEQUENCE
DROP ANY SEQUENCE
SELECT ANY SEQUENCE
CREATE DATABASE LINK
CREATE PUBLIC DATABASE LINK
DROP PUBLIC DATABASE LINK
CREATE ROLE
DROP ANY ROLE
GRANT ANY ROLE
ALTER ANY ROLE
AUDIT ANY
ALTER DATABASE
FORCE TRANSACTION
FORCE ANY TRANSACTION
CREATE PROCEDURE
CREATE ANY PROCEDURE
ALTER ANY PROCEDURE
DROP ANY PROCEDURE
EXECUTE ANY PROCEDURE
CREATE TRIGGER
CREATE ANY TRIGGER
ALTER ANY TRIGGER
DROP ANY TRIGGER
CREATE PROFILE
ALTER PROFILE
DROP PROFILE
ALTER RESOURCE COST
ANALYZE ANY
GRANT ANY PRIVILEGE
CREATE MATERIALIZED VIEW
CREATE ANY MATERIALIZED VIEW
ALTER ANY MATERIALIZED VIEW
DROP ANY MATERIALIZED VIEW
CREATE ANY DIRECTORY
DROP ANY DIRECTORY
CREATE TYPE
CREATE ANY TYPE
ALTER ANY TYPE
DROP ANY TYPE
EXECUTE ANY TYPE
UNDER ANY TYPE
CREATE LIBRARY
CREATE ANY LIBRARY
ALTER ANY LIBRARY
DROP ANY LIBRARY
EXECUTE ANY LIBRARY
CREATE OPERATOR
CREATE ANY OPERATOR
ALTER ANY OPERATOR
DROP ANY OPERATOR
EXECUTE ANY OPERATOR
CREATE INDEXTYPE
CREATE ANY INDEXTYPE
ALTER ANY INDEXTYPE
DROP ANY INDEXTYPE
UNDER ANY VIEW
QUERY REWRITE
GLOBAL QUERY REWRITE
EXECUTE ANY INDEXTYPE
UNDER ANY TABLE
CREATE DIMENSION
CREATE ANY DIMENSION
ALTER ANY DIMENSION
DROP ANY DIMENSION
MANAGE ANY QUEUE
ENQUEUE ANY QUEUE
DEQUEUE ANY QUEUE
CREATE ANY CONTEXT
DROP ANY CONTEXT
CREATE ANY OUTLINE
ALTER ANY OUTLINE
DROP ANY OUTLINE
ADMINISTER RESOURCE MANAGER
ADMINISTER DATABASE TRIGGER
MERGE ANY VIEW
ON COMMIT REFRESH
EXEMPT ACCESS POLICY
RESUMABLE
SELECT ANY DICTIONARY
DEBUG CONNECT SESSION
DEBUG ANY PROCEDURE
FLASHBACK ANY TABLE
GRANT ANY OBJECT PRIVILEGE
CREATE EVALUATION CONTEXT
CREATE ANY EVALUATION CONTEXT
ALTER ANY EVALUATION CONTEXT
DROP ANY EVALUATION CONTEXT
EXECUTE ANY EVALUATION CONTEXT
CREATE RULE SET
CREATE ANY RULE SET
ALTER ANY RULE SET
DROP ANY RULE SET
EXECUTE ANY RULE SET
EXPORT FULL DATABASE
IMPORT FULL DATABASE
CREATE RULE
CREATE ANY RULE
ALTER ANY RULE
DROP ANY RULE
EXECUTE ANY RULE
ANALYZE ANY DICTIONARY
ADVISOR
CREATE JOB
CREATE ANY JOB
EXECUTE ANY PROGRAM
EXECUTE ANY CLASS
MANAGE SCHEDULER
SELECT ANY TRANSACTION
DROP ANY SQL PROFILE
ALTER ANY SQL PROFILE
ADMINISTER SQL TUNING SET
ADMINISTER ANY SQL TUNING SET
CREATE ANY SQL PROFILE
EXEMPT IDENTITY POLICY
MANAGE FILE GROUP
MANAGE ANY FILE GROUP
READ ANY FILE GROUP
CHANGE NOTIFICATION
CREATE EXTERNAL JOB
--note：如果授权select any table 在默认情况下不能访问数据字典（O7_DICTIONARY_ACCESSIBILITY=false）
--如果修改O7_DICTIONARY_ACCESSIBILITY=true则可以访问数据字典

5.2、object级别授权与回收

grant select,update,delete on abc.abc_a to abc with grant option;--权限可以传递（如果被回收，续传下去的权限一并回收）
grant all on fei_a to abc;--把fei_a的所有相关操作授权给abc
revoke  select,update,delete on abc.abc_a from abc;
--note：update，insert可以指定表的列，而select不行，只能通过view实现限制功能

六、role的管理
6.1、role的创建

create role r_a;
create role r_b identified by r_b;

6.2/授权

grant create session to r_a;
grant create table to r_b;
grant r_a,r_b to b;--b为用户，把role授权给用户

6.3、查看role相关信息

select * from role_tab_privs where  ROLE LIKE 'R_%';
SELECT * FROM ROLE_SYS_PRIVS WHERE ROLE LIKE 'R_%';
SELECT * FROM ROLE_ROLE_privs where role like 'R_%';

6.4、设置default role 和enabl/disable role

alter user b default role r_a;
set role r_a,r_b identified by r_b;
--使用b用户登录，然后enable role r_a,r_b(注意密码)
--没有在set中写出来的role，表似乎disable

一、not null

--创建表是直接指定
 create table orders(
 order_num number(4) constraint nn_orader_num not null,
 order_date date not null,
 product_id number);
--创建表后修改
alter table orders modify product_id not null;

二、check

--创建表时直接指定
 create table bouns(
 emp_id number not null,
 salary number(9,2) check(salary>500),
 bonus number(9,2),
 constraint ck_bonus check(bonus>100));
--创建表后修改
alter table bouns add constraint ck_bonus2 check(bonus<salary);
alter table bouns drop constraint ck_bonus2;--删除约束条件

三、unique

--最简单的创建
create table f3(id number unique);
--多列的索引
create table f4(id number,
name varchar2(20),
pwd varchar2(20),
constraint unique_f4 unique(id,pwd));
--添加unique和指定index相关信息
alter table fei
add constraint uq_fei_1np unique(id,pwd)
using index tablespace users
storage(initial 32k next 32k pctincrease 0) nologging;
--添加一列，包括unique
alter table fei add abc varchar2(11) constraint un_abc unique;
--删除unique
alter table d drop unique(id) cascade;--cascade可选（外键时强制删除）

四、primar key

--最基本的primary key
create table f5(id number primary key);
--主键为多列的情况
create table f6(id number,name varchar2(22),
constraint pk_f6 primary key(id,name));
--直接index的相关信息
create table f7(id number,name varchar2(20),
pwd varchar2(20),constraint pk_f7 primary key(id,pwd)
using index tablespace users
storage(initial 64k next 64k) nologging)
tablespace abc;
alter table a drop primary key cascade;--删除主键（外键时强制删除）

五、foreign key

--最简单的foreign key
create table a(id number primary key,name varchar2(10));
 create table b(id number references a(id));
--修改表的方式添加foreign key
create table c(id number,name varchar2(10));
alter table c add constraint fk_c foreign key(id) references a(id) on delete cascade;
--note：外键的三种删除方式：delete no action（default，如果外键中存在，主键中不能被删除）
--delete cascade（如果删除主键，强制删除外键中的内容）
--delete set null（删除主键，外键中内容变为null）
alter table c drop constraint fk_c;--删除外键约束

六、约束条件disable

alter table c add constraint pk_c  primary key(id) disable;
--noet：在创建条件后面加上disable
alter table c enable constraint pk_c;--启用约束性关系

七、(disable/enable)与（validate/novalidate）组成的四种状态

alter table a enable validate primary key;--（new、old数据都要检查）
alter table a enable novalidate primary key;--（new数据都要检查）
alter table a disable validate primary key;--（只读状态）
alter table a disable novalidate primary key;--（new、old数据都不检查）
select constraint_name,status,validated from user_constraints;--查询所属状态
--note：从disable novalidate状态转换为其他状态时，要考虑隐藏的unique index的存在

八、deferred延迟检查

alter table fei add constraint pk_fei primary key(id) deferrable;
set constraint pk_fei deferred;--或者set constraints all deferred;
--note：1）设置为deferrable时，如果建立隐形index则为NONUNIQUE
--2）建立constraint时，默认情况下是立即检查，而不是等到commit时检查
--3）如果没有set设置，默认还是为immediate（立即执行）

九、oracle约束状态转换冲突数据统计

--建立统计数据表
create table exceptions(row_id urowid,
 owner varchar2(30),
 table_name varchar2(30),
 constraint varchar2(30));
--创建表
create table t(id number primary key disable,name char(20));
--转换状态语句后面加exceptions into exceptions
alter table t enable validate primary key exceptions into exceptions;
--查看冲突数据
select rowid,id,name from t where rowid in (select row_id from exceptions);