index
1.b-tree(balance)平衡树,从根节点到任何叶子节点之间的深度都相同,都是那么多层。索引的叶子节点里面就是值和rowid的关联信息。
2.bitmap
create index idx_tt on tt(col1) tablespace
test;--创建索引的事后指定表空间
select object_id from dba_objects where
object_name='IDX_TT';--oracle每个对象都有一个对象id
alter session set events 'immediate tracename treedump level
12197(索引idx_tt的object_id)';--在trace文件中生成一个文件,里面包含了索引平衡树的结构。
branch<==>leaf:根和叶子,16进制显示了该索引的平衡树
select to_number('24000cd','xxxxxxxxxxxxx') from
dual;--16进制的0x24000cd转化为10进制的数,xxxxxx相当于占位符,没意义的
select to_char(37748941,'xxxxxxxxxxx') from
dual;--10进制转化为16进制
select
dbms_utility.data_block_address_file(37748941);--要写入10进制的地址,分解出这个10进制的objectid对应的文件号
select
dbms_utility.data_block_address_block(37748941);--要写入10进制的地址,分解出这个10进制的objectid对应的文件的第几个块
select * from dba_extents where
segment_name='IDX_TT';--从段中查看这个索引的信息(索引也是段),文件号(file_id),块的起始id(block_id),连续的块的个数(blocks)
dump
9号文件的205块,讲内存或磁盘上的某个文件的某个数据块转储出来到一个trc文件中,可以看出当前块存储的数据行以及列长列值信息
alter system dump datafile 9 block 205;
select dump(2,16) from
dual;--显示出2在oracle中存储的形式,trc文件中的数值类型都是这样显示的 2=c1 03
select dbms_rowid.rowid_object('AAAC+kAAJAAAABMAAt')
bno,dbms_rowid.rowid_relative_fno('AAAC+kAAJAAAABMAAt')
fno,dbms_rowid.rowid_block_number('AAAC+kAAJAAAABMAAt')
bno,dbms_rowid.rowid_row_number('AAAC+kAAJAAAABMAAt') rno from
dual;
--分别显示出来这个row的对象id,文件id,块id,行id。
**set autotrace
traceonly;--sql语句执行时,会生成执行计划,如分几步执行,但与分析出来的步骤序号无关。而是根据缩进的深度,越深先执行。
**索引列有表达式时,索引失效
INDEX RANGE SCAN:扫描索引
TABLE ACCESS BY INDEX ROWID:根据索引rowid处理数据
TABLE ACCESS FULL:全表扫描
select * from tt where object_id=5;--执行按索引查询(执行索引的表,索引名)
并非任何时间使用索引都可以提高效率,当查询的数据超过了全表数据的30%时,会适得其反。
plsql:procedure language
sql:structure query language
plsql结构:匿名block,不是一个对象,不能保存在数据库中
/*******
declare
v_i number :=0;
v_i2 number :=&a;
v_name varchar2(10) default 'a'; --赋值可以用:=
也可以用default
v_name2 varchar2(10) default &b;
begin
insert into t values(v_i,v_name);
insert into t values(v_i2,v_name2);
commit;
end;
/
/*******
支持的条件语句
1.if ... then
...;
end if;
2.if ... then
...;
else
...;
end if;
3.if ... then
...;
elsif ... then
...;
elsif ... then
...;
else
...;
end if;
4.case --类似于switch
when
... then ...;
when
... then ...;
end case;
支持的循环语句
exit;表示退出循环,可以直接写exit;也可以exit when
v_id>100;
1.loop
...;
end loop;
2.while ... loop --满足条件循环
...;
end loop;
3.for i in 1 .. 100 loop --循环1-100,i不需要定义随便起名,两个点不能有空格,for循环和游标结合的时候最实用
...;
end loop;
dbms_output.put_line(v_i); --输出某个值,在sqlplus中输出需要设置,set serveroutput
on;(当前会话有效)
10.2.0/db_1/sqlplus/admin/glogin.sql
--这个是初始化文件,可以各种修改。文件末尾添加set serveroutput on (不需要分号)重新登录则永远有效
将匿名块起个名字以对象的方式保存在数据库里就是过程了。
create or replace procedure proc_test1
is
--declare --这里is代替了declare
... --定义变量
begin
...
end proc_test1; --结束时过程名可以省掉
/
--执行过程
begin
proc_test1; --Call the
procedure
end;
/
或
execute proc_test1; --执行过程
含参数的过程
create or replace procedure proc_test2(p_i in int,p_name out
varchar2) --无参数时不要加括号,字符类型不要加宽度,in为默认的,可以不写
is
begin
select name into p_name from tt where id=p_id;
insert into t values(p_i,p_name);
commit;
end proc_test2;
--执行
declare
v_name varchar2(10);
begin
proc_test2(p_id=>1,p_name=>v_name);
或者 pro_test2(1,v_name); --缺省的是第一个参数,但是可以用前面的方法指定参数
dbms_output.put_line(v_name);
--v_name接收到了p_name的值,上面不能用v_name=proc_test2,因为这不是函数没有返回值
end;
/
参数类型 in out
in 输入参数,值在过程中只读不能够修改/赋值:=。
out 输出参数值在过程内部可以改变,可以在外面接收
in out 既可以输入又可以输出
plsql中select出来的任何结果都只能放在变量里面 select ... into ...
如果是select出来结果集了,定义游标变量接收
to_number('') --将字符变数值,如果不强制转换利用系统默认转化,会不走索引的
函数比过程多一个返回值,可以在外用表面接收
create or replace function fun_test(p_a in number,p_b in
number)
return number;
is
begin
return v_r:=(p_a/p_b);
--oracle好多地方可以添加括号,但基本都不佳,括号的作用就是用来改变优先级的
end fun_test;
/
--执行
select fun_test(10,5) as a from dual;
或
declare
v_r number;
begin
v_r:=fun_test(10,5);
dbms_output.put_line(v_r);
end;
/
Package
有了包,可以将包里面所有的块的权限一次授出等,很方便
--包头
create or replace package pack_test --包头中定义的东西是公有的
is
type ref_type is ref
cursor; --自定义类型,引用类型的游标类型,用来接收结果集
v_pi constant :=3.14;
--constant说明是常量
v_r
number :=2; --定义常量
procedure proc_test;
procedure
proc_test2;
function fun_test(p_a in
number,p_b in number) return number;
function fun_recycle
return number;
procedure
proc_result(p_result out ref_type);
--参数为自定义的类型
end pack_test;
--包体
create or replace package body pack_test --包体中定义的东西是私有的
is
--各种定义(过程,函数)
procedure proc_test2(p_i in int,p_name out varchar2)
--无参数时不要加括号,字符类型不要加宽度,in为默认的,可以不写
is
begin
select name into p_name from tt where id=p_id;
insert into t values(p_i,p_name);
commit;
end proc_test2;
----
function fun_test(p_a in number,p_b in number)
return number;
is
begin
return v_r:=(p_a/p_b);
--oracle好多地方可以添加括号,但基本都不佳,括号的作用就是用来改变优先级的
end fun_test;
----
function fun_recycle
return number;
is
begin
return 2*v_r*v_pi;
end fun_recycle;
----
procedure proc_result(p_result out ref_type)
--自定义类型的实现
is
begin
open p_result for select * from emp;
---讲结果集放在游标变量里面
end proc_result;
----
begin
--这里放初始化语句。。。但是通常连这个begin都拿掉了
end pack_test;
--执行自定义函数
begin
:result:=pack_test.fun_recycle;
end
--执行游标过程
begin
pack_test.proc_result(p_result=>:p_result);
end;
触发器 trigger
不能建在sys用户下,在触发器中不能加入commit等事务控制的语句。
1.dml(insert,update,delete) 针对dml操作的触发器,好处是能讲修改前后的值保存下来
2.system(database,ddl) 针对数据库和ddl操作的触发器,dba常用
3.instead-of(view)
/**********DML触发器
create or replace trigger user1.tri_test
after insert on t
--可以为:after insert or update
or delete on t
for each row
--批量操作,每一行触发一次,若没有的话,操作一次触发一次,但:new和:old只能和这个结合起来用在行级触发上
declare ---这个地方又出现了这个东西,过程函数中是is。。。
begin
if inserting then
insert into t_trace
values(sys.login_user,syscontext('userenv','ip_address'),syscontext('userenv','terminal'),sysdate,'INSERT',:new.id,:new.name);
--sys.login_user
登录用户
elsif updating
then
insert into t_trace
values(sys.login_user,syscontext('userenv','ip_address'),syscontext('userenv','terminal'),sysdate,'INSERT',:old.id,:old.name);
--sys.login_user
登录用户
else
insert into t_trace
values(sys.login_user,syscontext('userenv','ip_address'),syscontext('userenv','terminal'),sysdate,'INSERT',:old.id,:old.name);
--sys.login_user
登录用户
end tri_test;
/**********
inserting/updating/deleting 代表3个操作的动作判断
:new 代表即将插入的一条数据,只能用在触发器里面
:old 相反,代表之前的数据
select syscontext('userenv','ip_address') from
dual;--取客户端ip,需要登陆用@sid登录,不然取到为空
select syscontext('userenv','terminal') from dual; --终端
主机名
/**********SYSTEM触发器_DDL
create or replace trigger tri_test1
after ddl on
user1.schema --对user1下所有的对象进行ddl操作会触发
declare ---这个地方又出现了这个东西,过程函数中是is。。。
begin
insert into t_trace
values(sys.login_user,syscontext('userenv','ip_address'),syscontext('userenv','terminal'),sysdate,sys.sysevent,null,null);
--sys.login_user
登录用户
end tri_test;
/**********
sys.sysevent --得到ddl的事件alter create drop truncate
****触发器无法捕捉sql语句,审计可以
/**********SYSTEM触发器_DB
create or replace trigger tri_test2
after logon on database
--登录数据库触发,before logout on database 退出数据库之前
declare ---这个地方又出现了这个东西,过程函数中是is。。。
begin
insert into t_trace
values(sys.login_user,syscontext('userenv','ip_address'),syscontext('userenv','terminal'),sysdate,sys.sysevent,null,null);
--sys.login_user
登录用户
end tri_test;
/**********
****想将修改前后的值保存下来只能通过触发器,审计也不行
pl/sql配置选项
PLSQL_CODE_TYPE=NATIVE 编译过的plsql能够以解释性字节码(INTERPRETED
BYTECODE)和机器码(NATIVE)两种方式存储,缺省的是字节码。开发时建议字节码,执行后建议机器码。
PLSQL_DEBUG=FALSE 为TRUE时,开发环境条数有帮助,强制存储为字节码,不顾上一个参数的设置,默认为false
PLSQL_OPTIMIZE_MODE=2 缺省1,提供了理想的编译性能。代码改变不频繁的,并且大量使用pl/sql时改为1。为2时,提高了运行性能,但降低了编译性能。
PLSQL_WARNING=DISABLE:ALL
两个参数,第一个禁止显示编译警告信息,缺省disable。第二个参数屏蔽了所有的警告信息。可选为:all/server/informational/performance,生产环境disable:all最好。
Oracle database Security
1.给用户最少够用的权限
2.缺省的那些用户(安装时带的),尽量删除锁定或修改密码
3.口令安全特征 执行这个脚本utlpwdmg.sql会创建一个函数并修改default
profile文件,增加安全性
4.public关键字
scott用户在db_1/RDBMS/ADMIN/scott.sql中创建
----这句话亮了,授权时候直接创建用户
grant CONNECT,RESOURCE,UNLIMITED TABLESPACE to scott
identified by tiger;
///****按照自然月分段,25以后算下一月
select
to_char(applytime,'yyyyMMDD'),decode(sign(to_number(to_char(applytime,'dd'))-25),0,to_number(to_char(applytime,'MM')),1,to_number(to_char(applytime,'MM'))+1,to_number(to_char(applytime,'MM')))
from YX_OPLIST_HIS
///****
grant all on table1 to
public;--public关键字代表所有用户的集合。相当于一个组,当想让一个权限给所用的用户,就可以将用户权限授予public
revoke all on t from public;
UTL_SMTP/UTL_TCP/UTL_HTTP/UTL_FILE/DBMS_OBFUSCATION_TOOLKIT这5个包的执行权限默认属于public,需要收回
revoke execute on util_file from public;--全部以此收回
audit审计,一旦打开对性能影响较大。相当于监控用户操作行为或者对某对象的某种操作
由参数AUDIT_TRAIL决定,值为NONE/OS/DB等
alter system set audit_trail=true
[scope=spfile];--=true相当于=db,true和以前的版本兼容,打开审计,静态参数需要加上scope来修改到文件里面,然后重启
alter system set audit_trail=db_extended
scope=spfile;--=db_extended的时候,才会捕捉sql语句
=os审计信息放在auidt_file_dest代表的目录下(unix/linux),win系统则在计算机管理应用程序事件查看器下面。
=db审计信息
1.privilege(system privilege) 系统权限审计
audit create session [by user1];
--审计所有用户(指定用户)的登录,监控某系统动作
noaudit create session;
audit create table by
user1;--审计user1的创建表操作。走网络(本地也用@sid登录)才能正常审计
select * from dba_priv_audit_opts;--查看对哪些权限进行审计了
select * from
dba_audit_trail;--审计结果放在这里面,其中sql_text会显示捕捉到sql语句,失败时会记录错误代码
select * from aud$;--同上 action#列标识审计的动作(数字)
select * from audit_actions;--从这里面根据动作查询动作的内容
2.object(object privilege) 对象审计,监控某对象的某种被操作
audit select on tt [by
access/session];--审计关于表tt的select操作,缺省是session,一次会话中,相同的sql操作只记录一次
select * from dba_obj_audit_opts;--查看哪些对象都进行了哪种审计
3.statement(sql) 对sql语句审计
select * from dba_stmt_audit_opts;--查看对哪些对象进行了语句审计
oracle 的各种审计都很交叉,所以前两种审计的操作也可能记录到这个里面
noaudit table by test;
noaudit create session by test;
noaudit create table by test;--关掉审计
purge recyclebin;--清空回收站,无法闪回了。
****审计记载的东西相当多,一般自己都写的触发器来进行
--系统预置的一些操作
audit all by test; --打开所有语句审计和权限审计。
noaudit all by test;
audit all on default;--审计所有对象,all审计只对其后创建的对象起作用
noaudit all on default;--不审计也是针对其后创建的对象起作用,需要另写命令
noaudit all on table1;--具体针对某个对象取消all审计
select * from all_def_audit_opts;--所有all审计对应的内容
--以上3种全部是标准审计(数据库审计),通过修改audit_trail来决定,能捕捉sql语句但不能获取修改前后的值
select * from dba_common_audit_trail;--同时包含了精细审计和常规审计的结果
Fine-Grained Auditing(精细粒度审计)dbms_fga利用这个包的审计value-based
auditing 基于值的审计,借助触发器实现,能捕获修改前后的值:new :old
可以具体对某一表的满足条件的某一些值进行监控,有固定的语法,添加审计的策略,用的很少因为太细了。
execute dbms_fga.add_policy
(
object_schema=>'user1', --schema名字
object_name=> 'table1',
--对象名
policy_name=>
'audit_table1_col1',
--策略名
audit_condition=> 'col2=10', --满足审计的条件
audit_column=>'col1', --审计的列
enable=>true,
statement_types=>'select'
); --执行时,这段代码保证在一行。执行后就开始了对某一列满足某一条件是进行查询是操作的监控。
select * from dba_audit_policies;
--所有定义的审计策略的信息
select * from dba_audit_policy_columns;
--定义的审计策略针对的列的信息
select * from dba_fga_audit_trail; --精细审计的结果
net service/listener
tnsping abcd;--pin服务名,看是否连通。
通常可以一个listener监听多个数据库,连接以后即使停止了监听也没事,只有在连接的一刹那需要用到监听
ABCD = --服务命名
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
--这里给服务命名abcd添加了两个地址,当用abcd连接数据库时,一个连不上了会切换到另一个,这个就是connect
time failover
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.19.3)(PORT =
1521))
)
(CONNECT_DATA =
(SERVICE_NAME = abcd)
)
)
--以上在客户端配置,连接服务器端
sqlplus test/test@192.168.1.21:1522/abcd --这样的连接方法,不需要配置服务命名,如果是1521端口可以缺省,ip替换为机器名
所谓服务命名就是给服务起一个别名,在客户端使用
连接模式
专用,一session一后台进程对应,绝大多数用
共享,支持专用,连接池的原理
shared_servers=0
启动的共享进程的数目,=0此参数不支持共享,=几就等于启动了几个服务器进程来支持共享
select sid from v$mystat where rownum=1;
sid=153
select server from v$session where
sid=153;--server=DEDICATED/SHARED 专用/共享
select server,paddr(服务器进程地址),saddr(session进程地址) from v$session
where sid=153; --padder=33A4CBFC 就是专用模式下session对应的服务器进程地址
select pid,spid from v$process wehre addr='33A4CBFC';
--pid=16,spid=2036 pid是oracle进程id,spid是操作系统进程id
根据地址查询当前服务器上某个进程的信息
ORACLE.EXE(SHAD) shad:shadow 影子进程(专用服务器进程别名)
ORACLE.EXE(S001) 这些是共享服务器的进程
alter system dump datafile 9 block 205;
--将块信息dump到跟踪文件(10.2.0/admin/sid/udump),共享服务器dump出来的跟踪文件当作后台进程放在bdump目录下,坑爹啊
sid_ora_spid.trc
--进程(服务器进程,如上面的2036)产生的跟踪文件的命名规律
动态参数改完了不用重启
alter system set
dispatchers='(PROTOCOL=TCP)(dispatchers=5)';想增加共享模式下调度分发进程(ORACLE.EXE(D001))的数量可以修改参数dispatchers
select * from v$shared_server; --共享模式下,各服务器进程的状态
select * from
v$circuit;--进程回路(sessiondispatcherserver)的信息,显示了服务器进程地址SERVER,分发进程地址DISPATCHER,session地址SADDR
要想是共享服务器模式连接,必须走的动态监听提供的服务名(创建服务命名时要输入的服务名)。也就是lsnrctl>status中显示为ready的,显示unknow的为动态监听