--======================================================================================================== --==================================== 一.sql语句方面 ==================================================== --======================================================================================================== 1.增加主键 alter table TABLE_NAME add constraint KEY_NAME primary key (TABLE_COLUMN); 指定表空间 alter table TABLE_NAME add constraint KEY_NAME primary key (TABLE_COLUMN) using index tablespace TABLE_SPACE_NAME; 2.增加外键 alter table TABLE_NAME add constraint FK_NAME foreign key (TABLE_COLUMN) references KEY_TABLE_NAME; 3.使主键或外键失效、生效 alter table TABLE_NAME disable(enable) constraint KEY_NAME; 4、查看各种约束 select constraint_name,table_name,constraint_type,status from user_constraints; select constraint_name, constraint_type,search_condition, r_constraint_name from user_constraints where table_name = upper('&table_name')
select c.constraint_name,c.constraint_type,cc.column_name from user_constraints c,user_cons_columns cc where c.owner = upper('&table_owner') and c.table_name = upper('&table_name') and c.owner = cc.owner and c.constraint_name = cc.constraint_name order by cc.position;
5、删除主键或外键 alter table TABLE_NAME drop constraint KEY_NAME; 6、建外键 单字段时:create table 表名 (col1 char(8), cno char(4) REFERENCE course); 多个字段时,在最后加上 Foreign Key (字段名) REFERENCE 表名(字段) 连带删除选项 (on delete cascade 当指定时,如果父表中的记录被删除,则依赖于父表的记录也被删除 REFERENCE 表名() on delete cascade; 7、删除带约束的表 Drop table 表名 cascade constraints;
8:索引管理 <1>.creating function-based indexes sql> create index summit.item_quantity on summit.item(quantity-quantity_shipped);
<2>.create a B-tree index sql> create [unique] index index_name on table_name(column,.. asc/desc) tablespace sql> tablespace_name [pctfree integer] [initrans integer] [maxtrans integer] sql> [logging | nologging] [nosort] storage(initial 200k next 200k pctincrease 0 sql> maxextents 50);
<3>.pctfree(index)=(maximum number of rows-initial number of rows)*100/maximum number of rows
<4>.creating reverse key indexes sql> create unique index xay_id on xay(a) reverse pctfree 30 storage(initial 200k sql> next 200k pctincrease 0 maxextents 50) tablespace indx;
<5>.create bitmap index sql> create bitmap index xay_id on xay(a) pctfree 30 storage( initial 200k next 200k sql> pctincrease 0 maxextents 50) tablespace indx;
<6>.change storage parameter of index sql> alter index xay_id storage (next 400k maxextents 100);
7.allocating index space sql> alter index xay_id allocate extent(size 200k datafile 'c:/oracle/index.dbf');
<8>.alter index xay_id deallocate unused;
<9>、查看索引 SQL>select index_name,index_type,table_name from user_indexes order by table_name; <10>、查看索引被索引的字段 SQL>select * from user_ind_columns where index_name=upper('&index_name');
11、创建序列 select * from user_sequences; create sequence SEQ_NAME start with 1000 maxvalue 1000 increment by 1; alter sequence SEQ_NAME minvalue 50 maxvalue 100; 12、删除重复行 update a set aa=null where aa is not null;
delete from a where rowid!= (select max(rowid) from a b where a.aa=b.aa); 13、删除同其他表相同的行 delete from a where exits (select 'X' from b where b.no=a.no); 或 delete from a where no in (select no from b); 14、查询从多少行到多少行的记录(可以用在web开发中的分页显示)
select * from ( select rownum row_id,b.* from (select a.* from sys_oper a) b ) where row_id between 15 and 20
15、对公共授予访问权 grant select on 表名 to public; create public synonym 同义词名 for 表名; 16、填加注释 comment on table 表名 is '注释'; comment on column 表名.列名 is '注释'; 17、分布式数据库,创建数据库链路 create [public] database link LINKNAME [connect to USERNAME identified by PASSWORD] [using 'CONNECT_STRING'] 可以在服务器端,也可以在客户端建立,但必须注意,两台服务器之间 数据库必须可以互访,必须各有各自的别名数据库 18、查看数据库链路 select * from all_db_links; select * from user_db_links; 查询 select * from TABLENAME@DBLNKNAME; 创建远程数据库同义词 create synonym for TABLENAME@DBLNKNAME; 操纵远程数据库记录 insert into TABLENAME@DBLNKNAME (a,b) values (va,vb); update TABLENAME@DBLNKNAME set a='this'; delete from TABLENAME@DBLNKNAME; 怎样执行远程的内嵌过程 begin otherdbpro@to_html(参数); end;
19、数据库链路用户密码有特殊字符的时候,可以用双引号把密码引起来 create public database link dblink1 connect to db1 identified by "123*456" using 'db11'
20.oracle8中扩充了group by rollup和cube的操作。有时候省了你好多功夫的。 <1>下面的语句可以进行总计 select region_code,count(*) from aicbs.acc_woff_notify group by rollup(region_code); <2> 对第1个字段小计,最后合计 select region_code,write_status,count(*) from aicbs.acc_woff_notify group by rollup(region_code,write_status); ---------------------- 570 0 3 570 1 2 570 5 --此处小计了570的记录 571 0 10 571 1 2 571 12 --此处小计了571的记录 ..... 100 --此处有总计 <3> 复合rollup表达式,只做总计 select region_code,write_status,count(*) from aicbs.acc_woff_notify group by rollup(region_code,write_status);
<4> 对第1个字段小计,再对第2个字段小计,最后合计 select region_code,write_status,count(*) from aicbs.acc_woff_notify group by cube(region_code,write_status); ---------------------- 100 --此处有总计 0 60 --对write_status=0的小计 1 39 --对write_status=1的小计 3 1 --对write_status=3的小计 570 5 --此处小计了570的记录 570 0 3 570 1 2 571 12 --此处小计了571的记录 571 0 10 571 1 2 .... <3> 复合cube表达式,只做总计 select region_code,write_status,count(*) from aicbs.acc_woff_notify group by cube(region_code,write_status);
<4>下面的语句可以按照rollup不同的字段进行小计 select region_code,write_status,count(*) from aicbs.acc_woff_notify group by region_code,rollup(write_status); 21.查询view的创建语句 sql>set long 1000 sql>select * from user_views where view_name='MY_VIEW_NAME'; or sql>select * from all_views where view_name='MY_VIEW_NAME';
insert into zj values(1,'aaa',0) insert into zj values(11,'aaa1',1) insert into zj values(12,'aaa2',1) insert into zj values(111,'aaa11',11) insert into zj values(112,'aaa12',11) insert into zj values(113,'aaa13',11) insert into zj values(121,'aaa21',12) insert into zj values(122,'aaa22',12) insert into zj values(123,'aaa23',12) -- select bm,bmmc,sjbm,level from zj start with sjbm=0 connect by prior bm = sjbm 或者
select bm,bmmc,sjbm,level from zj start with sjbm=0 connect by sjbm = prior bm
25、快照 create snapshot SNAPSHOT_NAME [storage (storage parameter)] [tablespace TABLESPACE_NAME] [refresh [fast\complete\force] [start with START_DATE next NEXT_DATE] as QUERY;
create snapshot snapshot_to_study as select * from TABLE_NAME@to_study; 创建角色 create role aa identified by aaa; 授权 grant create snapshot,alter snapshot to aaa; grant aaa to emp; create snapshot SNAPSHOT_TO_HTML refresh complete start with sysdate next sysdate+5/(24*60*60) as select * from a@to_html; 删除 drop snapshot snap_to_html 手工刷新快照,(调用DBMS_SNAPSHOT包中的refresh过程)DBMS_SNAPSHOT.refresh(snapshot_name,refresh_type); begin DBMS_SNAPSHOT.REFRESH('snap_to_html','c'); end; 对所有快照进行刷新 begin DBMS_SNAPSHOT.REFRESH_ALL; end; 怎样执行远程的内嵌过程 begin otherdbpro@to_html(参数); end; 26、用户管理 create a user: database authentication sql> create user juncky identified by oracle default tablespace users sql> temporary tablespace temp quota 10m on data password expire sql> [account lock|unlock] [profile profilename|default];
<1>.查看当前用户的缺省表空间 SQL>select username,default_tablespace from user_users; <2>生成用户时指定缺省表空间 create user 用户名 identified by 口令 default tablespace 表空间名;
<3>重新指定用户的缺省表空间 alter user 用户名 default tablespace 表空间名 <4>查看当前用户的角色 SQL>select * from user_role_privs; <5>查看当前用户的系统权限和表级权限 SQL>select * from user_sys_privs; SQL>select * from user_tab_privs; <6>查看用户下所有的表 SQL>select * from user_tables; <7> alter user语句的quota子句限制用户的磁盘空间 如:alter user jf quota 10M on system;
27、查看放在ORACLE的内存区里的表 SQL>select table_name,cache from user_tables where instr(cache,'Y')>0;
29、查看创建视图的select语句 SQL>set view_name,text_length from user_views; SQL>set long 2000; 说明:可以根据视图的text_length值设定set long 的大小 SQL>select text from user_views where view_name=upper('&view_name');
30、查看同义词的名称 SQL>select * from user_synonyms;
31、用Sql语句实现查找一列中第N大值 select * from (select t.*,dense_rank() over (order by sal) rank from employee) where rank = N;
<2>.copy an existing table sql> create table table_name [logging|nologging] as subquery
<3> create table ... as 方式建表的时候,指定表参数 create table a storage( initial 1M /*第一次创建时分配空间*/ next 1M /*第一次分配的存储空间用完时在分配*/ ) as select * from b; <4>.创建临时表 sql> create global temporary table xay_temp as select * from xay; on commit preserve rows/on commit delete rows 在Oracle中,可以创建以下两种临时表: a 会话特有的临时表: create global temporary table () on commit preserve rows; 会话指定,当中断会话时ORACLE将截断表
b 事务特有的临时表: create global temporary table () on commit delete rows; 事务指定,每次提交后ORACLE将截断表(删除全部行) c 说明 临时表只在当前连接内有效 临时表不建立索引,所以如果数据量比较大或进行多次查询时,不推荐使用 数据处理比较复杂的时候时表快,反之视图快点 在仅仅查询数据的时候建议用游标: open cursor for 'sql clause'; <5> pctfree = (average row size - initial row size) *100 /average row size pctused = 100-pctfree- (average row size*100/available data space) <6>.change storage and block utilization parameter sql> alter table table_name pctfree=30 pctused=50 storage(next 500k sql> minextents 2 maxextents 100); <7>.manually allocating extents sql> alter table table_name allocate extent(size 500k datafile 'c:/oracle/data.dbf'); <8>.move tablespace sql> alter table employee move tablespace users; <9>.deallocate of unused space sql> alter table table_name deallocate unused [keep integer] <10>.drop a column sql> alter table table_name drop column comments cascade constraints checkpoint 1000; alter table table_name drop columns continue; <11>.mark a column as unused sql> alter table table_name set unused column comments cascade constraints; alter table table_name drop unused columns checkpoint 1000; alter table orders drop columns continue checkpoint 1000 data_dictionary : dba_unused_col_tabs
40. 在Oracle服务器上通过SQLPLUS查看本机IP地址 ? select sys_context('userenv','ip_address') from dual; 如果是登陆本机数据库,只能返回127.0.0.1
41. 在ORACLE中取毫秒? 9i之前不支持,9i开始有timestamp. 9i可以用select systimestamp from dual;
42. 将N秒转换为时分秒格式? set serverout on declare N number := 1000000; ret varchar2(100); begin ret := trunc(n/3600) || '小时' || to_char(to_date(mod(n,3600),'sssss'),'fmmi"分 "ss"秒"') ; dbms_output.put_line(ret); end;
43、在某个用户下找所有的索引 select user_indexes.table_name, user_indexes.index_name,uniqueness, column_name from user_ind_columns, user_indexes where user_ind_columns.index_name = user_indexes.index_name and user_ind_columns.table_name = user_indexes.table_name order by user_indexes.table_type, user_indexes.table_name, user_indexes.index_name, column_position;
44. not in的替代。 一般not in的效率比较低。特别是数据量大的时候,几乎不能执行。 用下面几种方式可以替换写法 比如要查询在fee_rev_info表中已经销户的用户(不在cm_user中的)(不过下面的例子不是很好,因为bill_id是cm_user的唯一索引) select * from fee_rev_info where bill_id not in (select bill_id from cm_user) <1> 用not exists select * from fee_rev_info a where not exists (select 'p' from cm_user b where b.bill_id = a.bill_id) <2> 用外连接(+) select a.* from fee_rev_info a,cm_user b where a.bill_id = b.bill_id (+) and b.bill_id is null <3> 用hash_aj select /*+HASH_AJ*/* from fee_rev_info where bill_id not in (select bill_id from cm_user)
45.怎么样查询特殊字符,如通配符%与_ 假如数据库中有表 STATIONTYPE,STATION_571 STATION_572 ... select * from tab where tname like 'STATION_%' 会显示 STATIONTYPE,STATION_571 ... 可以用下面的语句 select * from tab where tname like 'STATION\_%' escape'\'
46.如果存在就更新,不存在就插入可以用一个语句实现吗 9i已经支持了,是Merge,但是只支持select子查询, 如果是单条数据记录,可以写作select .... from dual的子查询。 语法为: MERGE INTO table USING data_source ON (condition) WHEN MATCHED THEN update_clause WHEN NOT MATCHED THEN insert_clause;
如 MERGE INTO cm_user_credit USING (select * from dual) ON (user_id =1302514690 ) when MATCHED then update set credit_value = 1000 when NOT MATCHED then insert (user_id,acc_id,bill_id,plan_id,region_code,credit_value) values(1302514690,1305032158,'13857141218',10070247,'571',1000);
47.怎么实现一条记录根据条件多表插入 9i以上可以通过Insert all语句完成,仅仅是一个语句,如: INSERT ALL WHEN (id=1) THEN INTO table_1 (id, name) values(id,name) WHEN (id=2) THEN INTO table_2 (id, name) values(id,name) ELSE INTO table_other (id, name) values(id, name) SELECT id,name FROM a;
如果没有条件的话,则完成每个表的插入,如 INSERT ALL INTO table_1 (id, name) values(id,name) INTO table_2 (id, name) values(id,name) INTO table_other (id, name) values(id, name) SELECT id,name FROM a;
这一类型的转换必须借助于PL/SQL来完成,这里给一个例子 CREATE OR REPLACE FUNCTION get_c2(tmp_c1 NUMBER) RETURN VARCHAR2 IS Col_c2 VARCHAR2(4000); BEGIN FOR cur IN (SELECT c2 FROM t WHERE c1=tmp_c1) LOOP Col_c2 := Col_c2||cur.c2; END LOOP; Col_c2 := rtrim(Col_c2,1); RETURN Col_c2; END; / SQL> select distinct c1 ,get_c2(c1) cc2 from table;即可
方式一: create or replace function get_col( p_userId number, p_col number ) return varchar as v_tmp varchar2(255); begin select user_number||chr(9)||user_num into v_tmp from (select user_number,user_num,rownum row_id from okcai_1 where user_id = p_userId) a where row_id = p_col; return ltrim(v_tmp); --return v_tmp; end;
然后 select distinct user_id,get_col(user_id,1),get_col(user_id,2),get_col(user_id,3) .... from okcai_1
方式二: create or replace function get_col( p_userId number, p_col number ) return varchar as v_tmp varchar2(255); begin select user_number||chr(9)||user_num into v_tmp from (select user_number,user_num,rownum row_id from okcai_1 where user_id = p_userId) a where row_id = p_col; return ltrim(v_tmp); --return v_tmp; end; select distinct user_id,get_col_new(user_id) from okcai_1;
49.怎么设置存储过程的调用者权限 普通存储过程都是所有者权限,如果想设置调用者权限,请参考如下语句 create or replace procedure ...() AUTHID CURRENT_USER As begin ... end;
50.Oracle有哪些常见关键字 详细信息可以查看v$reserved_words视图
51.怎么查看数据库参数 <1> show parameter 参数名 如通过show parameter spfile可以查看9i是否使用spfile文件 其中参数名是可以匹配的。 比如show parameter cursor ,则会显示跟cursor相关的参数 <2> select * from v$parameter <3>
除了这部分参数,Oracle还有大量隐含参数,可以通过如下语句查看: SELECT NAME ,VALUE ,decode(isdefault, 'TRUE','Y','N') as "Default" ,decode(ISEM,'TRUE','Y','N') as SesMod ,decode(ISYM,'IMMEDIATE', 'I', 'DEFERRED', 'D', 'FALSE', 'N') as SysMod ,decode(IMOD,'MODIFIED','U', 'SYS_MODIFIED','S','N') as Modified ,decode(IADJ,'TRUE','Y','N') as Adjusted ,description FROM ( --GV$SYSTEM_PARAMETER SELECT x.inst_id as instance ,x.indx+1 ,ksppinm as NAME ,ksppity ,ksppstvl as VALUE ,ksppstdf as isdefault ,decode(bitand(ksppiflg/256,1),1,'TRUE','FALSE') as ISEM ,decode(bitand(ksppiflg/65536,3), 1,'IMMEDIATE',2,'DEFERRED','FALSE') as ISYM ,decode(bitand(ksppstvf,7),1,'MODIFIED','FALSE') as IMOD ,decode(bitand(ksppstvf,2),2,'TRUE','FALSE') as IADJ ,ksppdesc as DESCRIPTION FROM x$ksppi x ,x$ksppsv y WHERE x.indx = y.indx AND substr(ksppinm,1,1) = '_' AND x.inst_id = USERENV('Instance') ) ORDER BY NAME
52.怎样建立基于函数索引 8i以上版本,确保 Query_rewrite_enabled=true Query_rewrite_integrity=trusted Compatible=8.1.0以上 Create index indexname on table (function(field));
53.怎么样移动表或表分区 [A]移动表的语法 Alter table tablename move [Tablespace new_name Storage(initial 50M next 50M pctincrease 0 pctfree 10 pctused 50 initrans 2) nologging] 移动分区的语法 alter table tablename move (partition partname) [update global indexes] 之后之后必须重建索引 Alter index indexname rebuild 如果表有Lob段,那么正常的Alter不能移动Lob段到别的表空间,而仅仅是移动了表段,可以采用如下的方法移动Lob段 alter table tablename move lob(lobsegname) store as (tablespace newts);
54.怎么样修改表的列名 [A]9i以上版本可以采用rname命令 ALTER TABLE UserName.TabName RENAME COLUMN SourceColumn TO DestColumn 9i以下版本可以采用create table …… as select * from SourceTable的方式。 另外,8i以上可以支持删除列了 ALTER TABLE UserName.TabName SET UNUSED (ColumnName) CASCADE CONSTRAINTS ALTER TABLE UserName.TabName DROP (ColumnName) CASCADE CONSTRAINTS
55.case的用法 在sql语句中 CASE test_value WHEN expression1 THEN value1 [[WHEN expression2 THEN value2] [...]] [ELSE default_value] END
比如1 SELECT last_name, job_id, salary CASE job_id WHEN 'IT_PROG' THEN 1.10*salary WHEN 'ST_CLERK' THEN 1.15*salary WHEN 'SA_REP' THEN 1.20*salary ELSE salary END "REVISED_SALARY" FROM employees
比如2 select case when real_charge>=20000 and real_charge<30000 then 5000 when real_charge>=30000 and real_charge<40000 then 9000 when real_charge>=40000 and real_charge<50000 then 10000 when real_charge>=50000 and real_charge<60000 then 14000 when real_charge>=60000 and real_charge<70000 then 18000 when real_charge>=70000 and real_charge<80000 then 19000 when real_charge>=80000 and real_charge<90000 then 24000 when real_charge>=90000 and real_charge<100000 then 27000 when real_charge>=100000 and real_charge<110000 then 27000 when real_charge>=110000 and real_charge<120000 then 29000 when real_charge>=120000 then 36000 else 0 end ,acc_id,user_id,real_charge from okcai_jh_charge_200505
在存储过程中 case v_strGroupClassCode when '1' then v_nAttrNum := v_nAttrNum + 300; v_strAttrFlag := '1'||substr(v_strAttrFlag,2,7); when '2' then v_nAttrNum := v_nAttrNum + 200; v_strAttrFlag := '2'||substr(v_strAttrFlag,2,7); else NULL; end case; 注意的是存储过程和sql语句有的细微差别是用end case,而不是end。语句后面跟";" --======================================================================================================== --======================================= 二.pl/sql 编程方面 ============================================= --========================================================================================================
2、包 包说明(package specification),包头,存放关于包的内容的信息,定义包的用户可见的过程、 函数,数据类型和变量 create or replace package tt_aa as v1 varchar2(10); v2 varchar2(10); v3 number; v4 boolean; procedure proc1(x number); procedure proc2(y varchar2); procedure proc3(z number); function my_add(x number,y number) return number; end; 包主体(package body)是可选的 create or replace package body tt_aa as procedure proc1(x number) as begin v1:=to_char(x); end; procedure proc2(y varchar2) as begin v2:=y; end; procedure proc3(z number) as begin v1:=z; end; procedure proc4(x number,y number) return number as begin return x+y; end; end;
调用 begin tt_aa.proc1(6); dbms_output.put_line(to_char(tt_aa.my_add(1,3)); end;
3、动态sql(使用dbms_sql) create or replace procedure my_execute(sql_string in varchar2) as v_cursor number; v_numrows interger; begin v_cursor:=dbms_sql.open_cursor; dbms_sql.parse(v_cursor,sql_string,dbms_sql.v7); v_numrows:=dbms_sql.execute(v_cursor); dbms_sql.close_cursor(v_cursor); end;
则可以 sql>exec my_execute('select * from tab'); sql>exec my_execute('insert into test value'||'('||'''ddd'''||')'); sql>exec my_execute('commit');
对于查询方面的可以如下方式: 比如想用游标查询一个表,但是这个表是分月的,每个月可能表名都会改变。 create or replace procedure proc_test as v_curid integer; v_result integer; v_strSql varchar2(255); v_userid okcai.userid%type; v_username okcai.username%type; begin v_strSql := 'select * from okcai_'||to_char(sysdate,'yyyymm'); v_curid := dbms_sql.open_cursor; dbms_sql.parse(v_curid,v_strSql,dbms_sql.v7); dbms_sql.define_column(v_curid,1,v_userid); dbms_sql.define_column(v_curid,2,v_username,10); --必须指定大小 v_result := dbms_sql.execute(v_curid); loop if dbms_sql.fetch_rows(v_curid) = 0 then exit; --没有了 ,退出循环 end if; dbms_sql.column_value(v_curid,1,v_userid); dbms_sql.column_value(v_curid,2,v_username); dbms_output.put_line(v_userid); dbms_output.put_line(v_username); end loop; dbms_sql.close(v_curid); end;
4、用EXECUTE IMMEDIATE <1>. 在PL/SQL运行DDL语句 begin execute immediate 'set role all'; end; <2>. 给动态语句传值(USING 子句) declare l_depnam varchar2(20) := 'testing'; l_loc varchar2(10) := 'Dubai'; begin execute immediate 'insert into dept values (:1, :2, :3)' using 50, l_depnam, l_loc; commit; end; <3>. 从动态语句检索值(INTO子句) declare l_cnt varchar2(20); begin execute immediate 'select count(1) from emp' into l_cnt; dbms_output.put_line(l_cnt); end; <4>. 动态调用例程.例程中用到的绑定变量参数必须指定参数类型.黓认为IN类型,其它类型必须显式指定 declare l_routin varchar2(100) := 'gen2161.get_rowcnt'; l_tblnam varchar2(20) := 'emp'; l_cnt number; l_status varchar2(200); begin execute immediate 'begin ' || l_routin || '(:2, :3, :4); end;' using in l_tblnam, out l_cnt, in out l_status;
if l_status != 'OK' then dbms_output.put_line('error'); end if; end; <5>. 将返回值传递到PL/SQL记录类型;同样也可用%rowtype变量 declare type empdtlrec is record (empno number(4), ename varchar2(20), deptno number(2)); empdtl empdtlrec; begin execute immediate 'select empno, ename, deptno ' || 'from emp where empno = 7934' into empdtl; end; <6>. 传递并检索值.INTO子句用在USING子句前 declare l_dept pls_integer := 20; l_nam varchar2(20); l_loc varchar2(20); begin execute immediate 'select dname, loc from dept where deptno = :1' into l_nam, l_loc using l_dept ; end; <7>. 多行查询选项.对此选项用insert语句填充临时表,用临时表进行进一步的处理,也可以用REF cursors纠正此缺憾. declare l_sal pls_integer := 2000; begin execute immediate 'insert into temp(empno, ename) ' || ' select empno, ename from emp ' || ' where sal > :1' using l_sal; commit; end; <8>. 完成update的returning功能 update可以用returning返回修改以后的值。比如: UPDATE employees SET job_id =’SA_MAN’, salary = salary + 1000, department_id = 140 WHERE last_name = ’Jones’ RETURNING salary*0.25, last_name, department_id INTO :bnd1, :bnd2, :bnd3; 用execute immediate来完成的时候,可以用 declare l_sal pls_integer; begin execute immediate 'update employees SET salary = salary + 1000 where last_name=''okcai'' RETURNING INTO :1' returning into v_sql; commit; end;
5、用ref cursor来完成动态游标的功能 declare type ct is ref cursor; cc ct; v_notify acc_woff_notify%rowtype; begin open cc for 'select * from acc_woff_notify'; loop fetch cc into v_notify; exit when cc%notfound; dbms_output.put_line(v_notify.done_code); end loop; close cc; end;
7.存储过程使用table类型 <1>.字符串数组 declare type regionType is table of varchar2(3) index by binary_integer; v_listRegion regionType; i number(2):=0; begin v_listRegion(1):='571'; v_listRegion(2):='572'; v_listRegion(3):='573'; v_listRegion(4):='574'; v_listRegion(5):='575'; v_listRegion(6):='576'; v_listRegion(7):=null; i := 1; while i<= v_listRegion.last loop dbms_output.put_line( v_listRegion(i) ); i := v_listRegion.next(i); end loop; end;
<2>.rowtype数组 declare type CmUserType is table of cm_user%rowtype index by binary_integer; v_listUser CmUserType; i number(5):=0; r_user cm_user%rowtype; begin
i := 1; for r_user in (select * from cm_user where rownum<=5) loop v_listUser(i):= r_user; i := i + 1; end loop;
i := 1; while i<= v_listUser.last loop dbms_output.put_line( v_listUser(i).bill_id ); i := v_listUser.next(i); end loop; end;
<3>. record数组 declare type recCmUserType is record (bill_id cm_user.bill_id%type,cust_name varchar2(25)); type CmUserType is table of recCmUserType index by binary_integer; v_listUser CmUserType; i number(5):=0; r_user cm_user%rowtype; begin
i := 1; for r_user in (select * from cm_user where rownum<=5) loop v_listUser(i).bill_id:= r_user.bill_id; v_listUser(i).cust_name:= '客户'||i; i := i + 1; end loop;
i := 1; while i<= v_listUser.last loop dbms_output.put_line( v_listUser(i).bill_id ); dbms_output.put_line( v_listUser(i).cust_name ); i := v_listUser.next(i); end loop; end;
8、存储函数和过程
查看函数和过程的状态 SQL>select object_name,status from user_objects where object_type='FUNCTION'; SQL>select object_name,status from user_objects where object_type='PROCEDURE';
查看函数和过程的源代码 SQL>set long 1000 SQL>set pagesize 0 SQL>set trimspool on SQL>select text from all_source where owner=user and name=upper('&plsql_name');
9、触发器
查看触发器
set long 50000; set heading off; set pagesize 2000;
10. 加密ORACLE的存储过程 用wrap命令,如: 下列存储过程内容放在AA.SQL文件中 create or replace procedure testCCB(i in number) as begin dbms_output.put_line('输入参数是'||to_char(i)); end;
SQL>wrap iname=a.sql; PL/SQL Wrapper: Release 8.1.7.0.0 - Production on Tue Nov 27 22:26:48 2001 Copyright (c) Oracle Corporation 1993, 2000. All Rights Reserved. Processing a.sql to a.plb 提示a.sql转换为a.plb,这就是加密了的脚本,执行a.plb即可生成加密了的存储过程 运行a.plb SQL> @a.plb ;
11.怎么样利用游标更新数据 cursor c1 is select * from tablename where name is null for update [of column] ... update tablename set column = ... where current of c1;
14.用java写存储过程包 <1> create or replace and compile java source named "CHelloWorld" as public class HelloWorld { public static String print() { return System.out.println("Hello,World"); } }; /
<2> create or replace function my_helloWorld return varchar2 as language java name 'HelloWorld.print() return java.lang.String'; /
<3> select my_helloWorld from dual;
--======================================================================================================== --==================================== 三.系统函数和包使用方面 =========================================== --======================================================================================================== 1.十进制和十六进制转换 (oracle 8i以后) select to_char(125,'XXXXX') from dual ----------- 7D select to_char(125,'xxxxx') from dual ----------- 7d
select to_number('7D','XXXXX') from dual ----------- 125
2. ORACLE产生随机函数 DBMS_RANDOM.RANDOM
3、调度程序 DBMS_JOB
broken 中止一个任务调度 change 修改任务的属性 internal 改变间隔 submit 任务发送到任务队列中去 next_date 改变任务的运行时间 remove 删除一个任务 run 立即执行一个任务 submit 提交一个任务 user_export 任务说明 what 改变任务运行的程序 查询 select * from user_job; 建立一存储过程 create or replace procedure log_proc as begin insert into test(aa) values(sysdate); commit; end; 提交一个任务 declare job_num number; begin dbms_job.submit(job_num,'log_proc;',sysdate,sysdate+5/(24*60*60),false); dbms_output.put_line('Job numer='||to_char(job_num)); end; 1> 上面程序从当前开始,间隔5秒执行一次。 2> 如果每天几点执行,可以写为(比如从2004-09-13开始执行,每天7点执行) next_date => to_date('13-09-2004 07:00:00', 'dd-mm-yyyy hh24:mi:ss'), interval => 'trunc(sysdate)+(7+24)/24') 3> 如果是每个月几号开始执行。比如每月2号21点执行。 add_months(trunc(sysdate,'MONTH'),1) + 2-1 + 21/24
移走任务 begin dbms_job.remove(1); end; 中止任务 begin dbms_job.broken(1,true); end; 查询正在执行的job select * from dba_jobs_running 如果运行比较慢,加 select /*+ rule */* from dba_jobs_running 4.UTL_FILE包 在PL/SQL 3.3以上的版本中,UTL_FILE包允许用户通过PL/SQL读写操作系统文件。如下:
DECALRE FILE_HANDLE UTL_FILE.FILE_TYPE; BEGIN FILE_HANDLE:=UTL_FILE.FOPEN('C:\','TEST.TXT','A'); UTL_FILE.PUT_LINE(FILE_HANDLE,'HELLO,IT iS A TEST TXT FILE'); UTL_FILE.FCLOSE(FILE_HANDLE); END;
比如:怎么样在Oracle中写操作系统文件,如写日志 可以利用utl_file包,但是,在此之前,要注意设置好Utl_file_dir初始化参数 /************************************************************************** parameter:textContext in varchar2 日志内容 desc: ·写日志,把内容记到服务器指定目录下 ·必须配置Utl_file_dir初始化参数,并保证日志路径与Utl_file_dir路径一致或者是其中一个 ****************************************************************************/ CREATE OR REPLACE PROCEDURE sp_Write_log(text_context VARCHAR2) IS file_handle utl_file.file_type; Write_content VARCHAR2(1024); Write_file_name VARCHAR2(50); BEGIN --open file write_file_name := 'db_alert.log'; file_handle := utl_file.fopen('/u01/logs',write_file_name,'a'); write_content := to_char(SYSDATE,'yyyy-mm-dd hh24:mi:ss')||'||'||text_context; --write file IF utl_file.is_open(file_handle) THEN utl_file.put_line(file_handle,write_content); END IF; --close file utl_file.fclose(file_handle); EXCEPTION WHEN OTHERS THEN BEGIN IF utl_file.is_open(file_handle) THEN utl_file.fclose(file_handle); END IF; EXCEPTION WHEN OTHERS THEN NULL; END; END sp_Write_log;
7.怎么在Oracle中发邮件 可以利用utl_smtp包发邮件,以下是一个发送简单邮件的例子程序 /**************************************************************************** parameter: Rcpter in varchar2 接收者邮箱 Mail_Content in Varchar2 邮件内容 desc: ·发送邮件到指定邮箱 ·只能指定一个邮箱,如果需要发送到多个邮箱,需要另外的辅助程序 ****************************************************************************/ CREATE OR REPLACE PROCEDURE sp_send_mail( rcpter IN VARCHAR2, mail_content IN VARCHAR2) IS conn utl_smtp.connection;
--write title PROCEDURE send_header(NAME IN VARCHAR2, HEADER IN VARCHAR2) AS BEGIN utl_smtp.write_data(conn, NAME||': '|| HEADER||utl_tcp.CRLF); END;
BEGIN --opne connect conn := utl_smtp.open_connection('smtp.com'); utl_smtp.helo(conn, 'oracle'); utl_smtp.mail(conn, 'oracle info'); utl_smtp.rcpt(conn, Rcpter); utl_smtp.open_data(conn); --write title send_header('From', 'Oracle Database'); send_header('To', '"Recipient" '); send_header('Subject', 'DB Info'); --write mail content utl_smtp.write_data(conn, utl_tcp.crlf || mail_content); --close connect utl_smtp.close_data(conn); utl_smtp.quit(conn); EXCEPTION WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN BEGIN utl_smtp.quit(conn); EXCEPTION WHEN OTHERS THEN NULL; END; WHEN OTHERS THEN NULL; END sp_send_mail;
8.怎么样获取对象的DDL语句 第三方工具就不说了主要说一下9i以上版本的dbms_metadata <1>获得单个对象的DDL语句 set heading off set echo off set feedback off set pages off set long 90000 select dbms_metadata.get_ddl('TABLE','TABLE_NAME','SCAME') from dual; 比如 select dbms_metadata.get_ddl('TABLE','CM_USER','AICBS') from dual; <2>.如果获取整个用户的脚本,可以用如下语句 select dbms_metadata.get_ddl('TABLE',u.table_name) from user_tables u; 当然,如果是索引,则需要修改相关table到index <3>.还有 dbms_metadata.get_xml()
5、查看修改数据库的字符集 <1>数据库服务器字符集 在表props$中 update props$ set value$='ZHS16CGB231280' where name ='NLS_CHARACTERSET' 然后重新启动数据库,而不需要重新安装
8i以上版本可以通过alter database来修改字符集,但也只限于子集到超集,不建议修改props$表,将可能导致严重错误。 Startup nomount; Alter database mount exclusive; Alter system enable restricted session; Alter system set job_queue_process=0; Alter database open; Alter database character set zhs16gbk;
sql> show parameter NLS
查看数据库字符集: SELECT * FROM NLS_DATABASE_PARAMETERS; SELECT * FROM V$NLS_PARAMETERS; <2> 客户端字符集环境select * from nls_instance_parameters,其来源于v$parameter, 表示客户端的字符集的设置,可能是参数文件,环境变量或者是注册表 会话字符集环境 select * from nls_session_parameters,其来源于v$nls_parameters,表示会话自己的设置,可能是会话的环境变量或者是alter session完成,如果会话没有特殊的设置,将与nls_instance_parameters一致。 客户端的字符集要求与服务器一致,才能正确显示数据库的非Ascii字符。如果多个设置存在的时候,alter session>环境变量>注册表>参数文件 字符集要求一致,但是语言设置却可以不同,语言设置建议用英文。如字符集是zhs16gbk,则nls_lang可以是American_America.zhs16gbk。
9. 怎样查得数据库的SID ? select name from v$database; 也可以直接查看 init.ora文件
10、管理回滚段: 存放事务的恢复信息 建立回滚段 create public rollback segment SEG_NAME tabelspace TABLESPACE_NAME; alter rollback segment SEG_NAME online; 删除回滚段 首先改变为offline状态 直接使用回滚段 sql>set transaction use rollback segment SEG_NAME;
11. 计算一个表占用的空间的大小 select owner,table_name, NUM_ROWS, BLOCKS*AAA/1024/1024 "Size M", EMPTY_BLOCKS, LAST_ANALYZED from dba_tables where table_name='XXX';
Here: AAA is the value of db_block_size ; XXX is the table name you want to check
12. 表在表空间中的存储情况 select segment_name,sum(bytes),count(*) ext_quan from dba_extents where tablespace_name='&tablespace_name' and segment_type='TABLE' group by tablespace_name,segment_name;
13. 索引在表空间中的存储情况 select segment_name,count(*) from dba_extents where segment_type='INDEX' and owner='&owner' group by segment_name;
14.查看某表/索引的大小 表 SQL>select sum(bytes)/(1024*1024) as "size(M)" from user_segments where segment_name=upper('&table_name'); 索引 SQL>select sum(bytes)/(1024*1024) as "size(M)" from user_segments where segment_name=upper('&index_name');
15、确定可用空间 select tablespace_name,sum(blocks),sum(bytes) from sys.dba_free_space group by tablespace_name;
16、程序中报错:maxinum cursor exceed! <1> 查看当前的open cursor参数 sql> show parameter open_cursors <2> 如果确实很小,应该调整数据库初始化文件 加如一项 open_cursors=200 <3> 如果很大,则 select sid,sql_text,count(*) from v$open_cursor group by sid,sql_text having count(*) > 200 其中200是随便写一个比较大的值。查询得到打开太多的cursor.
17、查看数据库的版本信息 SQL> select * from v$version; 包含版本信息,核心版本信息,位数信息(32位或64位)等 至于位数信息,在linux/unix平台上,可以通过file查看,如 file $ORACLE_HOME/bin/oracle
18. 查看最大会话数 SELECT * FROM V$PARAMETER WHERE NAME LIKE 'proc%'; SQL> SQL> show parameter processes NAME TYPE VALUE ------------------------------------ ------- ------------------------------ aq_tm_processes integer 1 db_writer_processes integer 1 job_queue_processes integer 4 log_archive_max_processes integer 1 processes integer 200
这里为200个用户。
select * from v$license; 其中sessions_highwater纪录曾经到达的最大会话数
22. 如何查询SERVER是不是OPS? SELECT * FROM V$OPTION; 如果PARALLEL SERVER=TRUE则有OPS能
23. 查询每个用户的权限 SELECT * FROM DBA_SYS_PRIVS;
24.将表/索引移动表空间 ALTER TABLE TABLE_NAME MOVE TABLESPACE_NAME; ALTER INDEX INDEX_NAME REBUILD TABLESPACE TABLESPACE_NAME;
25.在LINUX,UNIX下启动DBA STUDIO? OEMAPP DBASTUDIO
26.LINUX下查询磁盘竞争状况命令? Sar -d
27.LINUX下查询磁盘CPU竞争状况命令? sar -r
28. 查询表空间信息? SELECT * FROM DBA_DATA_FILES;
29. 看各个表空间占用磁盘情况:
SQL> col tablespace format a20 SQL> select b.file_id 文件ID号, b.tablespace_name 表空间名, b.bytes 字节数, (b.bytes-sum(nvl(a.bytes,0))) 已使用, sum(nvl(a.bytes,0)) 剩余空间, sum(nvl(a.bytes,0))/(b.bytes)*100 剩余百分比 from dba_free_space a,dba_data_files b where a.file_id=b.file_id group by b.tablespace_name,b.file_id,b.bytes order by b.file_id
SQL> connect / as sysdba SQL> select * from v$version; SQL> create pfile from spfile; SQL> CREATE SPFILE FROM PFILE='E:\ora9i\admin\eygle\pfile\init.ora';
文件已创建。 SQL> CREATE SPFILE='E:\ora9i\database\SPFILEEYGLE.ORA' FROM PFILE='E:\ora9i\admin\eygle\pfile\init.ora'; 文件已创建。
45. 查询做比较大的排序的进程? <1> SELECT b.tablespace, b.segfile#, b.segblk#, b.blocks, a.sid, a.serial#, a.username, a.osuser, a.status FROM v$session a,v$sort_usage b WHERE a.saddr = b.session_addr ORDER BY b.tablespace, b.segfile#, b.segblk#, b.blocks ;
<2>. 查询做比较大的排序的进程的SQL语句 select /*+ ORDERED */ sql_text from v$sqltext a where a.hash_value = ( select sql_hash_value from v$session b where b.sid = &sid and b.serial# = &serial) order by piece asc ;
46. ORA-01555 SNAPSHOT TOO OLD的解决办法 增加MINEXTENTS的值,增加区的大小,设置一个高的OPTIMAL值。 如果是执行大的事务,报此错误,说明oracle给此事务随机分配的回滚段太小了,这时可以为它指定一个足够大的回滚段,以确保这个事务的成功执行.例如
set transaction use rollback segment roll_abc; delete from table_name where ... commit; 回滚段roll_abc被指定给这个delete事务,commit命令则在事务结束之后取消了回滚段的指定.
48. 监控事例的等待 select event,sum(decode(wait_Time,0,0,1)) "Prev", sum(decode(wait_Time,0,1,0)) "Curr",count(*) "Tot" from v$session_Wait group by event order by 4;
49. 回滚段的争用情况 select name, waits, gets, waits/gets "Ratio" from v$rollstat C, v$rollname D where C.usn = D.usn;
50 监控表空间的 I/O 比例 select B.tablespace_name name,B.file_name "file",A.phyrds pyr, A.phyblkrd pbr,A.phywrts pyw, A.phyblkwrt pbw from v$filestat A, dba_data_files B where A.file# = B.file_id order by B.tablespace_name;
51、监控文件系统的 I/O 比例 select substr(C.file#,1,2) "#", substr(C.name,1,30) "Name", C.status, C.bytes, D.phyrds, D.phywrts from v$datafile C, v$filestat D where C.file# = D.file#;
52、监控 SGA 的命中率 select a.value + b.value "logical_reads", c.value "phys_reads", round(100 * ((a.value+b.value)-c.value) / (a.value+b.value)) "BUFFER HIT RATIO" from v$sysstat a, v$sysstat b, v$sysstat c where a.statistic# = 38 and b.statistic# = 39 and c.statistic# = 40;
53、监控 SGA 中字典缓冲区的命中率 select parameter, gets,Getmisses , getmisses/(gets+getmisses)*100 "miss ratio", (1-(sum(getmisses)/ (sum(gets)+sum(getmisses))))*100 "Hit ratio" from v$rowcache where gets+getmisses <>0 group by parameter, gets, getmisses;
select sum(pinhits-reloads)/sum(pins) "hit radio",sum(reloads)/sum(pins) "reload percent" from v$librarycache;
55、显示所有数据库对象的类别和大小 select count(name) num_instances ,type ,sum(source_size) source_size , sum(parsed_size) parsed_size ,sum(code_size) code_size ,sum(error_size) error_size, sum(source_size) +sum(parsed_size) +sum(code_size) +sum(error_size) size_required from dba_object_size group by type order by 2;
56、监控 SGA 中重做日志缓存区的命中率,应该小于1% SELECT name, gets, misses, immediate_gets, immediate_misses, Decode(gets,0,0,misses/gets*100) ratio1, Decode(immediate_gets+immediate_misses,0,0, immediate_misses/(immediate_gets+immediate_misses)*100) ratio2 FROM v$latch WHERE name IN ('redo allocation', 'redo copy');
57、监控内存和硬盘的排序比率,最好使它小于 .10,增加 sort_area_size SELECT name, value FROM v$sysstat WHERE name IN ('sorts (memory)', 'sorts (disk)');
58、监控当前数据库谁在运行什么SQL语句? SELECT osuser, username, sql_text from v$session a, v$sqltext b where a.sql_address =b.address order by address, piece;
59、监控字典缓冲区? SELECT (SUM(PINS - RELOADS)) / SUM(PINS) "LIB CACHE" FROM V$LIBRARYCACHE; SELECT (SUM(GETS - GETMISSES - USAGE - FIXED)) / SUM(GETS) "ROW CACHE" FROM V$ROWCACHE; SELECT SUM(PINS) "EXECUTIONS", SUM(RELOADS) "CACHE MISSES WHILE EXECUTING" FROM V$LIBRARYCACHE; 后者除以前者,此比率小于1%,接近0%为好。
SELECT SUM(GETS) "DICTIONARY GETS",SUM(GETMISSES) "DICTIONARY CACHE GET MISSES" FROM V$ROWCACHE
60、监控 MTS select busy/(busy+idle) "shared servers busy" from v$dispatcher; 此值大于0.5时,参数需加大 select sum(wait)/sum(totalq) "dispatcher waits" from v$queue where type='dispatcher'; select count(*) from v$dispatcher; select servers_highwater from v$mts; servers_highwater接近mts_max_servers时,参数需加大
61、查看碎片程度高的表? SELECT segment_name table_name , COUNT(*) extents FROM dba_segments WHERE owner NOT IN ('SYS', 'SYSTEM') GROUP BY segment_name HAVING COUNT(*) = (SELECT MAX( COUNT(*) ) FROM dba_segments GROUP BY segment_name);
62、如何知道使用CPU多的用户session? 11是cpu used by this session
select a.sid,spid,status,substr(a.program,1,40) prog,a.terminal,osuser,value/60/100 value from v$session a,v$process b,v$sesstat c where c.statistic#=11 and c.sid=a.sid and a.paddr=b.addr order by value desc;
65、查找前十条性能差的sql. SELECT * FROM ( SELECT PARSING_USER_ID EXECUTIONS, SORTS, COMMAND_TYPE, DISK_READS, sql_text FROM v$sqlarea ORDER BY disk_reads DESC ) WHERE ROWNUM<10 ;
66、查看占io较大的正在运行的session SELECT se.sid, se.serial#, pr.SPID, se.username, se.status, se.terminal, se.program, se.MODULE, se.sql_address, st.event, st.p1text, si.physical_reads, si.block_changes FROM v$session se, v$session_wait st, v$sess_io si, v$process pr WHERE st.sid=se.sid AND st.sid=si.sid AND se.PADDR=pr.ADDR AND se.sid>6 AND st.wait_time=0 AND st.event NOT LIKE '%SQL%' ORDER BY physical_reads DESC 对检索出的结果的几点说明: <1> 我是按每个正在等待的session已经发生的物理读排的序,因为它与实际的IO相关。 <2> 你可以看一下这些等待的进程都在忙什么,语句是否合理? Select sql_address from v$session where sid=; Select * from v$sqltext where address=; 执行以上两个语句便可以得到这个session的语句。 你也以用alter system kill session 'sid,serial#';把这个session杀掉。 <3> 应观注一下event这列,这是我们调优的关键一列,下面对常出现的event做以简要的说明: a、buffer busy waits,free buffer waits这两个参数所标识是dbwr是否够用的问题,与IO很大相关的,当v$session_wait中的free buffer wait的条目很小或没有的时侯,说明你的系统的dbwr进程决对够用,不用调整;free buffer wait的条目很多,你的系统感觉起来一定很慢,这时说明你的dbwr已经不够用了,它产生的wio已经成为你的数据库性能的瓶颈,这时的解决办法如下: a.1增加写进程,同时要调整db_block_lru_latches参数 示例:修改或添加如下两个参数 db_writer_processes=4 db_block_lru_latches=8 a.2开异步IO,IBM这方面简单得多,hp则麻烦一些,可以与Hp工程师联系。 b、db file sequential read,指的是顺序读,即全表扫描,这也是我们应该尽量减少的部分,解决方法就是使用索引、sql调优,同时可以增大db_file_multiblock_read_count这个参数。 c、db file scattered read,这个参数指的是通过索引来读取,同样可以通过增加db_file_multiblock_read_count这个参数来提高性能。 d、latch free,与栓相关的了,需要专门调节。 e、其他参数可以不特别观注。
71、日志管理 <1>建立日志组 sql>select * from v$logfile; sql>alter database add logfile group 3 ('f:\orant\database\log1_g3.ora' 'f:\orant\database\log2_g3.ora') size 100k; sql>select * from v$logfile;
---- sql> alter database add logfile group 4 ('/disk3/log4a.rdo','/disk4/log4b.rdo') size 1m;
<2>删除日志组 alter database drop logfile group 1; 但是其物理文件并没有被删除掉 系统至少需要2个日志组,如果只有2个,就不能删除 不能删除正活动的日志组 <3>手工归档 通过alter system 的archive log 子句来实现 archive log [thread 整数] [seq 整数][change 整数][current][group 整数] [logfile '文件名'][next][all][start][to '位置'] <4> 强制日志切换 sql> alter system switch logfile; <5> 强制checkpoints sql> alter system checkpoint; <6> adding online redo log members sql>alter database add logfile member '/disk3/log1b.rdo' to group 1, '/disk4/log2b.rdo' to group 2; <7>.changes the name of the online redo logfile sql> alter database rename file 'c:/oracle/oradata/oradb/redo01.log' to 'c:/oracle/oradata/redo01.log'; <8> drop online redo log members sql> alter database drop logfile member 'c:/oracle/oradata/redo01.log'; <9>.clearing online redo log files sql> alter database clear [unarchived] logfile 'c:/oracle/log2a.rdo'; <10>.using logminer analyzing redo logfiles a. in the init.ora specify utl_file_dir = ' ' b. sql> execute dbms_logmnr_d.build('oradb.ora','c:\oracle\oradb\log'); c. sql> execute dbms_logmnr_add_logfile('c:\oracle\oradata\oradb\redo01.log', sql> dbms_logmnr.new); d. sql> execute dbms_logmnr.add_logfile('c:\oracle\oradata\oradb\redo02.log', sql> dbms_logmnr.addfile); e. sql> execute dbms_logmnr.start_logmnr(dictfilename=>'c:\oracle\oradb\log\oradb.ora'); f. sql> select * from v$logmnr_contents(v$logmnr_dictionary,v$logmnr_parameters sql> v$logmnr_logs); g. sql> execute dbms_logmnr.end_logmnr;
72 系统控制 alter system ...
alter system enable restricted session; 只允许具有restricted系统特权的用户登录 alter system flush shared_pool 清除共享池 alter system checkpoint 执行一 个检查点 alter system set license_max_session=64,license_session_warning=54 会话限制为64,会话的警界为54 alter system set license_max_session=0 会话数为无限制 alter system set license_max_users=300 用户限制为300个 alter system switch logfile 强制执行日志转换 73 会话控制 alter session
alter session set sql_trace=true 当前会话具有sql跟踪功能 alter session set NLS_language=French 出错信息设为法语 alter session set NLS_date_format='YYYY MM DD HH24:MI:SS';缺省日期格式 alter session set optimizier_goal=first_row改变优化方法为基于开销方法,具有快速响应速度 update student@teach set sold=sold+1 where sno='98010'; commit; alter session close database link teach; 关闭远程链路 74、封锁机制 数据封锁:保护表数据,在多个用户并行存取数据时候,保证数据的完整性。 DML操作又在两个级别获取数据封锁:指定记录封锁和表封锁 表封锁可以有下列方式:行共享(RS),行排他(RX),共享封锁(S),共享行排他(SPX)和排他 封锁(X) 行共享表封锁(RS),允许其他事务并行查询、插入,修改和删除及再行封锁 select ...from 表名 ... for update of ...; lock table 表名 in row share mode; 行排他表封锁(RX) 对该行有独占权利 insert into 表名 ...; update 表名 ...; delete from 表名 ...; lock table 表名 in row exclusive mode; 允许并行查询、插入、删除或封锁其他行,但禁止其他事务使用下列命令进行并发封锁: lock table 表名 in share mode; lock table 表名 in share exclusive mode; lock table 表名 in exclusive mode; 共享表封锁(S) lock table 表名 in share mode; 允许其他事务可在该表上做查询和再实现共享表操作,但不能修改该表,同时也不能做如下封锁: lock table 表名 in share row exclusive mode; lock table 表名 in exclusive mode; lock table 表名 in row exclusive mode; 共享排他表封锁(SRX) lock table 表名 in share row exclusive mode; 排他表封锁(SRX) lock table 表名 in exclusive mode;
75、设置事务 set transaction [read only][read write][use rollback segment 回滚段名]
76.如果希望用aimtzmcc用户连接数据库,访问aicbs用户的表,不在表名前缀"aicbs.",可以在建立数据库连接后发下面的命令 alter session set current_schema = aicbs;
77、表空间管理 <1> 创建表空间 sql> create tablespace tablespace_name datafile 'c:\oracle\oradata\file1.dbf' size 100m, sql> 'c:\oracle\oradata\file2.dbf' size 100m minimum extent 550k [logging/nologging] sql> default storage (initial 500k next 500k maxextents 500 pctinccease 0) sql> [online/offline] [permanent/temporary] [extent_management_clause] <2>.locally managed tablespace sql> create tablespace user_data datafile 'c:\oracle\oradata\user_data01.dbf' sql> size 500m extent management local uniform size 10m; <3>.temporary tablespace sql> create temporary tablespace temp tempfile 'c:\oracle\oradata\temp01.dbf' sql> size 500m extent management local uniform size 10m; <4>.change the storage setting sql> alter tablespace app_data minimum extent 2m; sql> alter tablespace app_data default storage(initial 2m next 2m maxextents 999); <5>.taking tablespace offline or online sql> alter tablespace app_data offline; sql> alter tablespace app_data online; <6>.read_only tablespace sql> alter tablespace app_data read only|write; <7>.droping tablespace sql> drop tablespace app_data including contents; <8>.enableing automatic extension of data files sql> alter tablespace app_data add datafile 'c:\oracle\oradata\app_data01.dbf' size 200m sql> autoextend on next 10m maxsize 500m; <9>.change the size fo data files manually sql> alter database datafile 'c:\oracle\oradata\app_data.dbf' resize 200m; <10>.Moving data files: alter tablespace sql> alter tablespace app_data rename datafile 'c:\oracle\oradata\app_data.dbf' sql> to 'c:\oracle\app_data.dbf'; <11>.moving data files:alter database sql> alter database rename file 'c:\oracle\oradata\app_data.dbf' sql> to 'c:\oracle\app_data.dbf';
<5>.restore to a different location connect system/manager as sysdba startup mount alter database rename file '/disk1/../user.dbf' to '/disk2/../user.dbf'; alter database open;
<6>.recover syntax --recover a mounted database recover database; recover datafile '/disk1/data/df2.dbf'; alter database recover database; --recover an opened database recover tablespace user_data; recover datafile 2; alter database recover datafile 2;
<7>.how to apply redo log files automatically set autorecovery on recover automatic datafile 4;
<8>.complete recovery: --method 1(mounted databae) copy c:\backup\user.dbf c:\oradata\user.dbf startup mount recover datafile 'c:\oradata\user.dbf; alter database open; --method 2(opened database,initially opened,not system or rollback datafile) copy c:\backup\user.dbf c:\oradata\user.dbf (alter tablespace offline) recover datafile 'c:\oradata\user.dbf' or recover tablespace user_data; alter database datafile 'c:\oradata\user.dbf' online or alter tablespace user_data online; --method 3(opened database,initially closed not system or rollback datafile) startup mount alter database datafile 'c:\oradata\user.dbf' offline; alter database open copy c:\backup\user.dbf d:\oradata\user.dbf alter database rename file 'c:\oradata\user.dbf' to 'd:\oradata\user.dbf' recover datafile 'e:\oradata\user.dbf' or recover tablespace user_data; alter tablespace user_data online; --method 4(loss of data file with no backup and have all archive log) alter tablespace user_data offline immediate; alter database create datafile 'd:\oradata\user.dbf' as 'c:\oradata\user.dbf'' recover tablespace user_data; alter tablespace user_data online <9>.perform an open database backup alter tablespace user_data begin backup; copy files /backup/ alter database datafile '/c:/../data.dbf' end backup; alter system switch logfile; <10>.backup a control file alter database backup controlfile to 'control1.bkp'; alter database backup controlfile to trace; <11>.recovery (noarchivelog mode) shutdown abort cp files startup <12>.recovery of file in backup mode alter database datafile 2 end backup;
<13>.clearing redo log file alter database clear unarchived logfile group 1; alter database clear unarchived logfile group 1 unrecoverable datafile;
<14>.redo log recovery alter database add logfile group 3 'c:\oradata\redo03.log' size 1000k; alter database drop logfile group 1; alter database open; or >cp c:\oradata\redo02.log' c:\oradata\redo01.log alter database clear logfile 'c:\oradata\log01.log';
79 managing password security and resources
<1>.controlling account lock and password sql> alter user juncky identified by oracle account unlock;
<2>.user_provided password function sql> function_name(userid in varchar2(30),password in varchar2(30), old_password in varchar2(30)) return boolean
<2>.grant system privilege sql> grant create session,create table to managers; sql> grant create session to scott with admin option; with admin option can grant or revoke privilege from any user or role;
<3>.sysdba and sysoper privileges: sysoper: startup,shutdown,alter database open|mount,alter database backup controlfile, alter tablespace begin/end backup,recover database alter database archivelog,restricted session sysdba: sysoper privileges with admin option,create database,recover database until
<4>.password file members: view:=> v$pwfile_users
<5>.O7_dictionary_accessibility =true restriction access to view or tables in other schema
<6>.revoke system privilege sql> revoke create table from karen; sql> revoke create session from scott;
<7>.grant object privilege sql> grant execute on dbms_pipe to public; sql> grant update(first_name,salary) on employee to karen with grant option;
<1>.create roles sql> create role sales_clerk; sql> create role hr_clerk identified by bonus; sql> create role hr_manager identified externally;
<2>.modify role sql> alter role sales_clerk identified by commission; sql> alter role hr_clerk identified externally; sql> alter role hr_manager not identified;
<3>.assigning roles sql> grant sales_clerk to scott; sql> grant hr_clerk to hr_manager; sql> grant hr_manager to scott with admin option;
<4>.establish default role sql> alter user scott default role hr_clerk,sales_clerk; sql> alter user scott default role all; sql> alter user scott default role all except hr_clerk; sql> alter user scott default role none;
<5>.enable and disable roles sql> set role hr_clerk; sql> set role sales_clerk identified by commission; sql> set role all except sales_clerk; sql> set role none;
<6>.remove role from user sql> revoke sales_clerk from scott; sql> revoke hr_manager from public;
<7>.remove role sql> drop role hr_manager;
<8>.display role information view: =>dba_roles,dba_role_privs,role_role_privs,dba_sys_privs,role_sys_privs,role_tab_privs,session_roles
81.查询当前正在执行的job的情况 有时候对于需要执行的job查询执行情况,比如正在执行那条语句,或者想把job停下来等。一般不知道怎么查询到 job执行的session的sid. 方法一: select * from dba_jobs_running 如果运行比较慢,加 select /*+ rule */* from dba_jobs_running 方法二: <1>首先得到job号,从user_jobs或者dba_jobs select * from user_jobs where upper(what) like '%2004PRESENT%' <2> 根据job号查询sid号 select * from v$lock where id2 = 3361910 and type ='JQ' 就可以查询到sid了 比如查询当前的执行什么语句 select sql_text from v$sqlarea a,v$lock b,v$session c,user_jobs d where d.upper(what) like '%2004PRESENT%' and d.job = b.id2 and b.type='JQ' and b.sid = c.sid and a.hash_value = c.sql_hash_value and a.address = c.sql_address
83.如何移动数据文件 <1>、关闭数据库,利用os拷贝 a.shutdown immediate关闭数据库 b.在os下拷贝数据文件到新的地点 c.Startup mount 启动到mount下 d.Alter database rename datafile '老文件' to '新文件'; e.Alter database open; 打开数据库 <2>、利用Rman联机操作 RMAN> sql "alter database datafile ''file name'' offline"; RMAN> run { 2> copy datafile 'old file location' 3> to 'new file location'; 4> switch datafile ' old file location' 5> to datafilecopy ' new file location'; 6> } RMAN> sql "alter database datafile ''file name'' online"; 说明:利用OS拷贝也可以联机操作,不关闭数据库,与rman的步骤一样,利用rman与利用os拷贝的原理一样,在rman中copy是拷贝数据文件,相当于OS的cp,而switch则相当于alter database rename,用来更新控制文件。
84.如何管理联机日志组与成员 以下是常见操作,如果在OPA/RAC下注意线程号 增加一个日志文件组 Alter database add logfile [group n] '文件全名' size 10M; 在这个组上增加一个成员 Alter database add logfile member '文件全名' to group n; 在这个组上删除一个日志成员 Alter database drop logfile member '文件全名'; 删除整个日志组 Alter database drop logfile group n;
85.怎么样计算REDO BLOCK的大小 [A]计算方法为(redo size + redo wastage) / redo blocks written + 16 具体见如下例子 SQL> select name ,value from v$sysstat where name like '%redo%'; NAME VALUE ---------------------------------------------------------------- ---------- redo synch writes 2 redo synch time 0 redo entries 76 redo size 19412 redo buffer allocation retries 0 redo wastage 5884 redo writer latching time 0 redo writes 22 redo blocks written 51 redo write time 0 redo log space requests 0 redo log space wait time 0 redo log switch interrupts 0 redo ordering marks 0 SQL> select (19412+5884)/51 + 16 '"Redo black(byte)" from dual; Redo black(byte) ------------------ 512
86.如果发现表中有坏块,如何检索其它未坏的数据 [A]首先需要找到坏块的ID(可以运行dbverify实现),假设为,假定文件编码为。运行下面的查询查找段名: SELECT segment_name,segment_type,extent_id,block_id, blocks from dba_extents t where file_id = AND between block_id and (block_id + blocks - 1) 一旦找到坏段名称,若段是一个表,则最好建立一个临时表,存放好的数据。若段是索引,则删除它,再重建。 create table good_table as select from bad_table where rowid not in (select rowid from bad_table where substr(rowid,10,6) = ) 在这里要注意8以前的受限ROWID与现在ROWID的差别。 还可以使用诊断事件10231 SQL> ALTER SYSTEM SET EVENTS '10231 trace name context forever,level 10'; 创建一个临时表good_table的表中除坏块的数据都检索出来 SQL>CREATE TABLE good_table as select * from bad_table; 最后关闭诊断事件 SQL> ALTER SYSTEM SET EVENTS '10231 trace name context off '; 关于ROWID的结构,还可以参考dbms_rowid.rowid_create函数
87.怎么样备份控制文件 在线备份为一个二进制的文件 alter database backup controlfile to '$BACKUP_DEPT/controlfile.000' [reuse]; 备份为文本文件方式 alter database backup controlfile to trace [resetlogs|noresetlogs];
89.怎么样热备份一个表空间 <1>Alter tablespace 名称 begin backup; host cp 这个表空间的数据文件 目的地; Alter tablespace 名称 end backup; 如果是备份多个表空间或整个数据库,只需要一个一个表空间的操作下来就可以了。
90.怎么快速得到整个数据库的热备脚本 <1>可以写一段类似的脚本 SQL>set serveroutput on begin dbms_output.enable(10000); for bk_ts in (select distinct t.ts#,t.name from v$tablespace t,v$datafile d where t.ts#=d.ts#) loop dbms_output.put_line('--'||bk_ts.name); dbms_output.put_line('alter tablespace '||bk_ts.name||' begin backup;'); for bk_file in (select file#,name from v$datafile where ts#=bk_ts.ts#) loop dbms_output.put_line('host cp '||bk_file.name||' $BACKUP_DEPT/'); end loop; dbms_output.put_line('alter tablespace '||bk_ts.name||' end backup;'); end loop; end; /
92.丢失一个数据文件,没有备份但是有该数据文件创建以来的归档怎么恢复 保证如下条件 a. 不能是系统数据文件 b. 不能丢失控制文件 如果满足以上条件,则 SQL>startup mount SQL>Alter database create datafile 'file name' as 'file name' size ... reuse; SQL>recover datafile n; -文件号 或者 SQL>recover datafile 'file name'; 或者 SQL>recover database; SQL>Alter database open;
93.联机日志损坏如何恢复 <1>、如果是非当前日志而且归档,可以使用 Alter database clear logfile group n来创建一个新的日志文件 如果该日志还没有归档,则需要用 Alter database clear unarchived logfile group n <2>、如果是当前日志损坏,一般不能clear,则可能意味着丢失数据 如果有备份,可以采用备份进行不完全恢复 如果没有备份,可能只能用_allow_resetlogs_corruption=true来进行强制恢复了,但是,这样的方法是不建议的,最好在有Oracle support的指导下进行。
94.怎么样创建RMAN恢复目录 首先,创建一个数据库用户,一般都是RMAN,并给予recovery_catalog_owner角色权限 sqlplus sys SQL> create user rman identified by rman; SQL> alter user rman default tablespace tools temporary tablespace temp; SQL> alter user rman quota unlimited on tools; SQL> grant connect, resource, recovery_catalog_owner to rman; SQL> exit; 然后,用这个用户登录,创建恢复目录 rman catalog rman/rman RMAN> create catalog tablespace tools; RMAN> exit; 最后,你可以在恢复目录注册目标数据库了 rman catalog rman/rman target backdba/backdba RMAN> register database;
95.怎么样在恢复的时候移动数据文件,恢复到别的地点 给一个RMAN的例子 run { set until time 'Jul 01 1999 00:05:00'; allocate channel d1 type disk; set newname for datafile '/u04/oracle/prod/sys1prod.dbf' to '/u02/oracle/prod/sys1prod.dbf'; set newname for datafile '/u04/oracle/prod/usr1prod.dbf' to '/u02/oracle/prod/usr1prod.dbf'; set newname for datafile '/u04/oracle/prod/tmp1prod.dbf' to '/u02/oracle/prod/tmp1prod.dbf'; restore controlfile to '/u02/oracle/prod/ctl1prod.ora'; replicate controlfile from '/u02/oracle/prod/ctl1prod.ora'; restore database; sql "alter database mount"; switch datafile all; recover database; sql "alter database open resetlogs"; release channel d1; } 96.怎么从备份片(backuppiece)中恢复(restore)控制文件与数据文件 可以使用如下方法,在RMAN中恢复备份片的控制文件 restore controlfile from backuppiecefile; 如果是9i的自动备份,可以采用如下的方法 restore controlfile from autobackup; 但是,如果控制文件全部丢失,需要指定DBID,如SET DBID=? 自动备份控制文件的默认格式是%F,这个格式的形式为 c-IIIIIIIIII-YYYYMMDD-QQ,其中IIIIIIIIII就是DBID 至于恢复(restore)数据文件,oracle 816开始有个包dbms_backup_restore 在 nomount 状态下就可以执行,可以读 815甚至之前的备份片,读出来的文件用于恢复 可以在SQLPLUS中运行,如下 SQL>startup nomount SQL> DECLARE 2 devtype varchar2(256); 3 done boolean; 4 BEGIN 5 devtype := dbms_backup_restore.deviceallocate('', params=>''); 6 dbms_backup_restore.restoresetdatafile; 7 dbms_backup_restore.restorecontrolfileto('E:\Oracle\oradata\penny\control01.ctl'); 8 dbms_backup_restore.restoreDataFileto(1,'E:\Oracle\oradata\penny\system01.dbf'); 9 dbms_backup_restore.restoreDataFileto(2,'E:\Oracle\oradata\penny\UNDOTBS01.DBF'); 10 dbms_backup_restore.restoreDataFileto(3,'E:\ORACLE\ORADATA\PENNY\USERS01.DBF'); 11 dbms_backup_restore.restorebackuppiece('D:\orabak\BACKUP_1_4_04F4IAJT.PENNY',done=>done); 12 END; 13 / PL/SQL 过程已成功完成。 SQL> alter database mount; [Q]Rman的format格式中的%s类似的东西代表什么意义 [A]可以参考如下 %c 备份片的拷贝数 %d 数据库名称 %D 位于该月中的第几天 (DD) %M 位于该年中的第几月 (MM) %F 一个基于DBID唯一的名称,这个格式的形式为c-IIIIIIIIII-YYYYMMDD-QQ,其中IIIIIIIIII为该数据库的DBID,YYYYMMDD为日期,QQ是一个1-256的序列 %n 数据库名称,向右填补到最大八个字符 %u 一个八个字符的名称代表备份集与创建时间 %p 该备份集中的备份片号,从1开始到创建的文件数 %U 一个唯一的文件名,代表%u_%p_%c %s 备份集的号 %t 备份集时间戳 %T 年月日格式(YYYYMMDD)
97.执行exec dbms_logmnr_d.build('Logminer.ora','file directory'),提示下标超界,怎么办 完整错误信息如下, SQL> exec dbms_logmnr_d.build('Logminer.ora','file directory') BEGIN dbms_logmnr_d.build('Logminer.ora','file directory'); END; * ERROR 位于第 1 行: ORA-06532: 下标超出限制 ORA-06512: 在"SYS.DBMS_LOGMNR_D", line 793 ORA-06512: 在line 1 解决办法为: <1>.编辑位于"$ORACLE_HOME/rdbms/admin"目录下的文件"dbmslmd.sql" 改变行: TYPE col_desc_array IS VARRAY(513) OF col_description; 为 TYPE col_desc_array IS VARRAY(700) OF col_description; 并保存文件 <2>. 运行改变后的脚本 SQLPLUS> Connect internal SQLPLUS> @$ORACLE_HOME/rdbms/admin/dbmslmd.sql <3>.重新编译该包 SQLPLUS> alter package DBMS_LOGMNR_D compile body;
98.执行execute dbms_logmnr.start_logmnr(DictFileName=>'DictFileName')提示ORA-01843:无效的月份,这个是什么原因 我们分析start_logmnr包 PROCEDURE start_logmnr( startScn IN NUMBER default 0 , endScn IN NUMBER default 0, startTime IN DATE default TO_DATE('01-jan-1988','DD-MON-YYYY'), endTime IN DATE default TO_DATE('01-jan-2988','DD-MON-YYYY'), DictFileName IN VARCHAR2 default '', Options IN BINARY_INTEGER default 0 ); 可以知道,如果TO_DATE('01-jan-1988','DD-MON-YYYY')失败,将导致以上错误 所以解决办法可以为 <1>、Alter session set NLS_LANGUAGE=American <2>、用类似如下的方法执行 execute dbms_logmnr.start_logmnr (DictFileName=> 'f:\temp2\TESTDICT.ora', starttime => TO_DATE( '01-01-1988','DD-MM-YYYY'), endTime=>TO_DATE('01-01-2988','DD-MM-YYYY'));
9.在sql*plus中可以用&替代sql语句的变量,同样,也可以替换实体名称。比如表名。 目前有acc_bill_570,acc_bill_571,....acc_bill_580 等11张表。如果根据情况查询不同的表,可以如下执行。 SQL> select * from acc_bill_®ion_code where rownum<=10;
或者 SQL> define region_code=579 SQL> select * from acc_bill_®ion_code where rownum<=10;
--======================================================================================================== --========================================== 六.oracle优化方面 ====================================== --======================================================================================================== 1. 查询正在执行语句的执行计划(也就是实际语句执行计划) select * from v$sql_plan where hash_value = (select sql_hash_value from v$session where sid = 1111); 其中id和parent_id表示了执行数的结构,数值最大的为最先执行 比如 ID PARENT_ID ------------- 0 1 0 2 1 3 2 4 3 5 4 6 3 ------------则执行计划树为 0 1 2 3 6 4 5
2.如何设置自动跟踪 用system登录 执行$ORACLE_HOME/rdbms/admin/utlxplan.sql创建计划表 执行$ORACLE_HOME/sqlplus/admin/plustrce.sql创建plustrace角色 如果想计划表让每个用户都能使用,则 SQL>create public synonym plan_table for plan_table; SQL> grant all on plan_table to public; 如果想让自动跟踪的角色让每个用户都能使用,则 SQL> grant plustrace to public; 通过如下语句开启/停止跟踪 SET AUTOTRACE ON |OFF | ON EXPLAIN | ON STATISTICS | TRACEONLY | TRACEONLY EXPLAIN
3.如何跟踪自己的会话或者是别人的会话 跟踪自己的会话很简单 Alter session set sql_trace true|false Or Exec dbms_session.set_sql_trace(TRUE); 如果跟踪别人的会话,需要调用一个包 exec dbms_system.set_sql_trace_in_session(sid,serial#,true|false) 跟踪的信息在user_dump_dest 目录下可以找到或通过如下脚本获得文件名称(适用于Win环境,如果是unix需要做一定修改) SELECT p1.value||'\'||p2.value||'_ora_'||p.spid||'.ora' filename FROM v$process p, v$session s, v$parameter p1, v$parameter p2 WHERE p1.name = 'user_dump_dest' AND p2.name = 'db_name' AND p.addr = s.paddr AND s.audsid = USERENV ('SESSIONID') 最后,可以通过Tkprof来解析跟踪文件,如 Tkprof 原文件 目标文件 sys=n
4.怎么设置整个数据库系统跟踪 其实文档上的alter system set sql_trace=true是不成功的 但是可以通过设置事件来完成这个工作,作用相等 alter system set events '10046 trace name context forever,level 1'; 如果关闭跟踪,可以用如下语句 alter system set events '10046 trace name context off'; 其中的level 1与上面的8都是跟踪级别 level 1:跟踪SQL语句,等于sql_trace=true level 4:包括变量的详细信息 level 8:包括等待事件 level 12:包括绑定变量与等待事件
5.怎么样根据OS进程快速获得DB进程信息与正在执行的语句 有些时候,我们在OS上操作,象TOP之后我们得到的OS进程,怎么快速根据OS信息获得DB信息呢? 我们可以编写如下脚本: $more whoit.sh #!/bin/sh sqlplus /nolog 100,cascade=> TRUE); dbms_stats.gather_table_stats(User,TableName,degree => 4,cascade => true); 这是对命令与工具包的一些总结 <1>、对于分区表,建议使用DBMS_STATS,而不是使用Analyze语句。 a) 可以并行进行,对多个用户,多个Table b) 可以得到整个分区表的数据和单个分区的数据。 c) 可以在不同级别上Compute Statistics:单个分区,子分区,全表,所有分区 d) 可以倒出统计信息 e) 可以用户自动收集统计信息 <2>、DBMS_STATS的缺点 a) 不能Validate Structure b) 不能收集CHAINED ROWS, 不能收集CLUSTER TABLE的信息,这两个仍旧需要使用Analyze语句。 c) DBMS_STATS 默认不对索引进行Analyze,因为默认Cascade是False,需要手工指定为True <3>、对于oracle 9里面的External Table,Analyze不能使用,只能使用DBMS_STATS来收集信息。
6.怎么样快速重整索引 通过rebuild语句,可以快速重整或移动索引到别的表空间 rebuild有重建整个索引数的功能,可以在不删除原始索引的情况下改变索引的存储参数 语法为 alter index index_name rebuild tablespace ts_name storage(......); 如果要快速重建整个用户下的索引,可以用如下脚本,当然,需要根据你自己的情况做相应修改 SQL> set heading off SQL> set feedback off SQL> spool d:\index.sql SQL> SELECT 'alter index ' || index_name || ' rebuild ' ||'tablespace INDEXES storage(initial 256K next 256K pctincrease 0);' FROM all_indexes WHERE ( tablespace_name != 'INDEXES' OR next_extent != ( 256 * 1024 ) ) AND owner = USER SQL>spool off 另外一个合并索引的语句是 alter index index_name coalesce,这个语句仅仅是合并索引中同一级的leaf block 消耗不大,对于有些索引中存在大量空间浪费的情况下,有一些作用。
8.怎么样快速复制表或者是插入数据 快速复制表可以指定Nologging选项 如:Create table t1 nologging as select * from t2; 快速插入数据可以指定append提示,但是需要注意 noarchivelog模式下,默认用了append就是nologging模式的。 在archivelog下,需要把表设置程Nologging模式。 如insert /*+ append */ into t1 select * from t2 注意:如果在9i环境中并设置了FORCE LOGGING,则以上操作是无效的,并不会加快,当然,可以通过如下语句设置为NO FORCE LOGGING。 Alter database no force logging; 是否开启了FORCE LOGGING,可以用如下语句查看 SQL> select force_logging from v$database;
9.怎么避免使用特定索引 在很多时候,Oracle会错误的使用索引而导致效率的明显下降,我们可以使用一点点技巧而避免使用不该使用的索引,如: 表test,有字段a,b,c,d,在a,b,c上建立联合索引inx_a(a,b,c),在b上单独建立了一个索引Inx_b(b)。 在正常情况下,where a=? and b=? and c=?会用到索引inx_a, where b=?会用到索引inx_b 但是,where a=? and b=? and c=? group by b会用到哪个索引呢?在分析数据不正确(很长时间没有分析)或根本没有分析数据的情况下,oracle往往会使用索引inx_b。通过执行计划的分析,这个索引的使用,将大大耗费查询时间。 当然,我们可以通过如下的技巧避免使用inx_b,而使用inx_a。 where a=? and b=? and c=? group by b||'' --如果b是字符 where a=? and b=? and c=? group by b+0 --如果b是数字 通过这样简单的改变,往往可以是查询时间提交很多倍 当然,我们也可以使用no_index提示,相信很多人没有用过,也是一个不错的方法: select /*+ no_index(t,inx_b) */ * from test t where a=? and b=? and c=? group by b
举例: 本来在CM_USER上有索引IDX_CM_USER4(ACC_ID)和IDX_CM_USER8(BILL_ID),可是执行如下语句的时候很慢。 select * from CM_USER where acc_id =1200007175 and user_status>0 and bill_id like '13%' order by acc_id,bill_id 用explain分析,发现执行计划是用IDX_CM_USER8.如下查询 select * from user_indexes where table_name ='CM_USER' 发现IDX_CM_USER8没有分析过。 用下面语句执行计划改变 select /*+INDEX(CM_USER IDX_CM_USER4)*/* from CM_USER where acc_id =1200007175 and user_status>0 and bill_id like '13%' order by acc_id,bill_id
10.Oracle什么时候会使用跳跃式索引扫描 这是9i的一个新特性跳跃式索引扫描(Index Skip Scan). 例如表有索引index(a,b,c),当查询条件为 where b=?的时候,可能会使用到索引index(a,b,c) 如,执行计划中出现如下计划: INDEX (SKIP SCAN) OF 'TEST_IDX' (NON-UNIQUE) Oracle的优化器(这里指的是CBO)能对查询应用Index Skip Scans至少要有几个条件: <1> 优化器认为是合适的。 <2> 索引中的前导列的唯一值的数量能满足一定的条件(如重复值很多)。 <3> 优化器要知道前导列的值分布(通过分析/统计表得到)。 <4> 合适的SQL语句 等。
11.怎么样创建使用虚拟索引 可以使用nosegment选项,如 create index virtual_index_name on table_name(col_name) nosegment; 如果在哪个session需要测试虚拟索引,可以利用隐含参数来处理 alter session set "_use_nosegment_indexes" = true; 就可以利用explain plan for select ……来看虚拟索引的效果 利用@$ORACLE_HOME/rdbms/admin/utlxpls查看执行计划 最后,根据需要,我们可以删除虚拟索引,如普通索引一样 drop index virtual_index_name; 注意:虚拟索引并不是物理存在的,所以虚拟索引并不等同于物理索引,不要用自动跟踪去测试虚拟索引,因为那是实际执行的效果,是用不到虚拟索引的。
12.怎样监控无用的索引 Oracle 9i以上,可以监控索引的使用情况,如果一段时间内没有使用的索引,一般就是无用的索引 语法为: 开始监控:alter index index_name monitoring usage; 检查使用状态:select * from v$object_usage; 停止监控:alter index index_name nomonitoring usage; 当然,如果想监控整个用户下的索引,可以采用如下的脚本: set heading off set echo off set feedback off set pages 10000 spool start_index_monitor.sql SELECT 'alter index '||owner||'.'||index_name||' monitoring usage;' FROM dba_indexes WHERE owner = USER; spool off set heading on set echo on set feedback on ------------------------------------------------ set heading off set echo off set feedback off set pages 10000 spool stop_index_monitor.sql SELECT 'alter index '||owner||'.'||index_name||' nomonitoring usage;' FROM dba_indexes WHERE owner = USER; spool off set heading on set echo on set feedback on
13.怎么样能固定我的执行计划 可以使用OUTLINE来固定SQL语句的执行计划 用如下语句可以创建一个OUTLINE Create oe replace outline OutLn_Name on Select Col1,Col2 from Table where ....... 如果要删除Outline,可以采用 Drop Outline OutLn_Name; 对于已经创建了的OutLine,存放在OUTLN用户的OL$HINTS表下面 对于有些语句,你可以使用update outln.ol$hints来更新outline 如update outln.ol$hints(ol_name,'TEST1','TEST2','TEST2','TEST1) where ol_name in ('TEST1','TEST2'); 这样,你就把Test1 OUTLINE与Test2 OUTLINE互换了 如果想利用已经存在的OUTLINE,需要设置以下参数 Alter system/session set Query_rewrite_enabled = true Alter system/session set use_stored_outlines = true
15.怎么杀掉特定的数据库会话 Alter system kill session 'sid,serial#'; 或者 alter system disconnect session 'sid,serial#' immediate; 在win上,还可以采用oracle提供的orakill杀掉一个线程(其实就是一个Oracle进程) 在Linux/Unix上,可以直接利用kill杀掉数据库进程对应的OS进程
16.怎么快速查找锁与锁等待 数据库的锁是比较耗费资源的,特别是发生锁等待的时候,我们必须找到发生等待的锁,有可能的话,杀掉该进程。 这个语句将查找到数据库中所有的DML语句产生的锁,还可以发现,任何DML语句其实产生了两个锁,一个是表锁,一个是行锁。 可以通过alter system kill session ‘sid,serial#’来杀掉会话 SELECT /*+ rule */ s.username, decode(l.type,'TM','TABLE LOCK', 'TX','ROW LOCK', NULL) LOCK_LEVEL, o.owner,o.object_name,o.object_type, s.sid,s.serial#,s.terminal,s.machine,s.program,s.osuser FROM v$session s,v$lock l,dba_objects o WHERE l.sid = s.sid AND l.id1 = o.object_id(+) AND s.username is NOT NULL 如果发生了锁等待,我们可能更想知道是谁锁了表而引起谁的等待 以下的语句可以查询到谁锁了表,而谁在等待。 SELECT /*+ rule */ lpad(' ',decode(l.xidusn ,0,3,0))||l.oracle_username User_name, o.owner,o.object_name,o.object_type,s.sid,s.serial# FROM v$locked_object l,dba_objects o,v$session s WHERE l.object_id=o.object_id AND l.session_id=s.sid ORDER BY o.object_id,xidusn DESC 以上查询结果是一个树状结构,如果有子节点,则表示有等待发生。如果想知道锁用了哪个回滚段,还可以关联到V$rollname,其中xidusn就是回滚段的USN [Q] 如何有效的删除一个大表(extent数很多的表) [A] 一个有很多(100k)extent的表,如果只是简单地用drop table的话,会很大量消耗CPU(Oracle要对fet$、uet$数据字典进行操作),可能会用上几天的时间,较好的方法是分多次删除extent,以减轻这种消耗: 1. truncate table big-table reuse storage; 2. alter table big-table deallocate unused keep 2000m ( 原来大小的n-1/n); 3. alter table big-table deallocate unused keep 1500m ; .... 4. drop table big-table;
17.如何收缩临时数据文件的大小 9i以下版本采用 ALTER DATABASE DATAFILE 'file name' RESIZE 100M类似的语句 9i以上版本采用 ALTER DATABASE TEMPFILE 'file name' RESIZE 100M 注意,临时数据文件在使用时,一般不能收缩,除非关闭数据库或断开所有会话,停止对临时数据文件的使用。
18.怎么清理临时段 可以使用如下办法 <1>、 使用如下语句查看一下认谁在用临时段 SELECT username,sid,serial#,sql_address,machine,program, tablespace,segtype, contents FROM v$session se,v$sort_usage su WHERE se.saddr=su.session_addr <2>、 那些正在使用临时段的进程 SQL>Alter system kill session 'sid,serial#'; <3>、把TEMP表空间回缩一下 SQL>Alter tablespace TEMP coalesce; 还可以使用诊断事件 <1>、 确定TEMP表空间的ts# SQL> select ts#, name FROM v$tablespace; TS# NAME ----------------------- 0 SYSYEM 1 RBS 2 USERS 3* TEMP ... <2>、 执行清理操作 alter session set events 'immediate trace name DROP_SEGMENTS level TS#+1' 说明: temp表空间的TS# 为 3*, So TS#+ 1= 4 如果想清除所有表空间的临时段,则 TS# = 2147483647
19.怎么样dump数据库内部结构,如上面显示的控制文件的结构 [A]常见的有 1、分析数据文件块,转储数据文件n的块m alter system dump datafile n block m 2、分析日志文件 alter system dump logfile logfilename; 3、分析控制文件的内容 alter session set events 'immediate trace name CONTROLF level 10' 4、分析所有数据文件头 alter session set events 'immediate trace name FILE_HDRS level 10' 5、分析日志文件头 alter session set events 'immediate trace name REDOHDR level 10' 6、分析系统状态,最好每10分钟一次,做三次对比 alter session set events 'immediate trace name SYSTEMSTATE level 10' 7、分析进程状态 alter session set events 'immediate trace name PROCESSSTATE level 10' 8、分析Library Cache的详细情况 alter session set events 'immediate trace name library_cache level 10'
20.如何获得所有的事件代码 事件代码范围一般从10000 to 10999,以下列出了这个范围的事件代码与信息 SET SERVEROUTPUT ON DECLARE err_msg VARCHAR2(120); BEGIN dbms_output.enable (1000000); FOR err_num IN 10000..10999 LOOP err_msg := SQLERRM (-err_num); IF err_msg NOT LIKE '%Message '||err_num||' not found%' THEN dbms_output.put_line (err_msg); END IF; END LOOP; END; / 在Unix系统上,事件信息放在一个文本文件里 $ORACLE_HOME/rdbms/mesg/oraus.msg 可以用如下脚本查看事件信息 event=10000 while [ $event -ne 10999 ] do event=`expr $event + 1` oerr ora $event done 对于已经确保的/正在跟踪的事件,可以用如下脚本获得 SET SERVEROUTPUT ON DECLARE l_level NUMBER; BEGIN FOR l_event IN 10000..10999 LOOP dbms_system.read_ev (l_event,l_level); IF l_level > 0 THEN dbms_output.put_line ('Event '||TO_CHAR (l_event)|| ' is set at level '||TO_CHAR (l_level)); END IF; END LOOP; END; /
22. SQL语句的优化方法 <1> /*+ALL_ROWS*/ 表明对语句块选择基于开销的优化方法,并获得最佳吞吐量,使资源消耗最小化. 例如: SELECT /*+ALL+_ROWS*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='CCBZZP';
<2>. /*+FIRST_ROWS*/ 表明对语句块选择基于开销的优化方法,并获得最佳响应时间,使资源消耗最小化. 例如: SELECT /*+FIRST_ROWS*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='CCBZZP';
<3>. /*+CHOOSE*/ 表明如果数据字典中有访问表的统计信息,将基于开销的优化方法,并获得最佳的吞吐量; 表明如果数据字典中没有访问表的统计信息,将基于规则开销的优化方法; 例如: SELECT /*+CHOOSE*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='CCBZZP';
<4>. /*+RULE*/ 表明对语句块选择基于规则的优化方法. 例如: SELECT /*+ RULE */ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='CCBZZP';
<5>. /*+FULL(TABLE)*/ 表明对表选择全局扫描的方法. 例如: SELECT /*+FULL(A)*/ EMP_NO,EMP_NAM FROM BSEMPMS A WHERE EMP_NO='CCBZZP';
<6>. /*+ROWID(TABLE)*/ 提示明确表明对指定表根据ROWID进行访问. 例如: SELECT /*+ROWID(BSEMPMS)*/ * FROM BSEMPMS WHERE ROWID>='AAAAAAAAAAAAAA' AND EMP_NO='CCBZZP';
<7>. /*+CLUSTER(TABLE)*/ 提示明确表明对指定表选择簇扫描的访问方法,它只对簇对象有效. 例如: SELECT /*+CLUSTER */ BSEMPMS.EMP_NO,DPT_NO FROM BSEMPMS,BSDPTMS WHERE DPT_NO='TEC304' AND BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;
<8>. /*+INDEX(TABLE INDEX_NAME)*/ 表明对表选择索引的扫描方法. 例如: SELECT /*+INDEX(BSEMPMS SEX_INDEX) USE SEX_INDEX BECAUSE THERE ARE FEWMALE BSEMPMS */ FROM BSEMPMS WHERE SEX='M';
<9>. /*+INDEX_ASC(TABLE INDEX_NAME)*/ 表明对表选择索引升序的扫描方法. 例如: SELECT /*+INDEX_ASC(BSEMPMS PK_BSEMPMS) */ FROM BSEMPMS WHERE DPT_NO='CCBZZP';
<10>. /*+INDEX_COMBINE*/ 为指定表选择位图访问路经,如果INDEX_COMBINE中没有提供作为参数的索引,将选择出位图索引的 布尔组合方式. 例如: SELECT /*+INDEX_COMBINE(BSEMPMS SAL_BMI HIREDATE_BMI)*/ * FROM BSEMPMS WHERE SAL<5000000 AND HIREDATE <11>. /*+INDEX_JOIN(TABLE INDEX_NAME)*/ 提示明确命令优化器使用索引作为访问路径. 例如: SELECT /*+INDEX_JOIN(BSEMPMS SAL_HMI HIREDATE_BMI)*/ SAL,HIREDATE FROM BSEMPMS WHERE SAL<60000;
<12>. /*+INDEX_DESC(TABLE INDEX_NAME)*/ 表明对表选择索引降序的扫描方法. 例如: SELECT /*+INDEX_DESC(BSEMPMS PK_BSEMPMS) */ FROM BSEMPMS WHERE DPT_NO='CCBZZP';
<13>. /*+INDEX_FFS(TABLE INDEX_NAME)*/ 对指定的表执行快速全索引扫描,而不是全表扫描的办法. 例如: SELECT /*+INDEX_FFS(BSEMPMS IN_EMPNAM)*/ * FROM BSEMPMS WHERE DPT_NO='TEC305';
<14>. /*+ADD_EQUAL TABLE INDEX_NAM1,INDEX_NAM2,...*/ 提示明确进行执行规划的选择,将几个单列索引的扫描合起来. 例如: SELECT /*+INDEX_FFS(BSEMPMS IN_DPTNO,IN_EMPNO,IN_SEX)*/ * FROM BSEMPMS WHERE EMP_NO='CCBZZP' AND DPT_NO='TDC306';
<15>. /*+USE_CONCAT*/ 对查询中的WHERE后面的OR条件进行转换为UNION ALL的组合查询. 例如: SELECT /*+USE_CONCAT*/ * FROM BSEMPMS WHERE DPT_NO='TDC506' AND SEX='M';
<16>. /*+NO_EXPAND*/ 对于WHERE后面的OR 或者IN-LIST的查询语句,NO_EXPAND将阻止其基于优化器对其进行扩展. 例如: SELECT /*+NO_EXPAND*/ * FROM BSEMPMS WHERE DPT_NO='TDC506' AND SEX='M';
<17>. /*+NOWRITE*/ 禁止对查询块的查询重写操作.
<18>. /*+REWRITE*/ 可以将视图作为参数.
<19>. /*+MERGE(TABLE)*/ 能够对视图的各个查询进行相应的合并. 例如: SELECT /*+MERGE(V) */ A.EMP_NO,A.EMP_NAM,B.DPT_NO FROM BSEMPMS A (SELET DPT_NO ,AVG(SAL) AS AVG_SAL FROM BSEMPMS B GROUP BY DPT_NO) V WHERE A.DPT_NO=V.DPT_NO AND A.SAL>V.AVG_SAL;
<20>. /*+NO_MERGE(TABLE)*/ 对于有可合并的视图不再合并. 例如: SELECT /*+NO_MERGE(V) */ A.EMP_NO,A.EMP_NAM,B.DPT_NO FROM BSEMPMS A (SELET DPT_NO ,AVG(SAL) AS AVG_SAL FROM BSEMPMS B GROUP BY DPT_NO) V WHERE A.DPT_NO=V.DPT_NO AND A.SAL>V.AVG_SAL;
<21>. /*+ORDERED*/ 根据表出现在FROM中的顺序,ORDERED使ORACLE依此顺序对其连接. 例如: SELECT /*+ORDERED*/ A.COL1,B.COL2,C.COL3 FROM TABLE1 A,TABLE2 B,TABLE3 C WHERE A.COL1=B.COL1 AND B.COL1=C.COL1;
<22>. /*+USE_NL(TABLE)*/ 将指定表与嵌套的连接的行源进行连接,并把指定表作为内部表. 例如: SELECT /*+ORDERED USE_NL(BSEMPMS)*/ BSDPTMS.DPT_NO,BSEMPMS.EMP_NO,BSEMPMS.EMP_NAM FROM BSEMPMS,BSDPTMS WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;
<23>. /*+USE_MERGE(TABLE)*/ 将指定的表与其他行源通过合并排序连接方式连接起来. 例如: SELECT /*+USE_MERGE(BSEMPMS,BSDPTMS)*/ * FROM BSEMPMS,BSDPTMS WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;
<24>. /*+USE_HASH(TABLE)*/ 将指定的表与其他行源通过哈希连接方式连接起来. 例如: SELECT /*+USE_HASH(BSEMPMS,BSDPTMS)*/ * FROM BSEMPMS,BSDPTMS WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;
<25>. /*+DRIVING_SITE(TABLE)*/ 强制与ORACLE所选择的位置不同的表进行查询执行. 例如: SELECT /*+DRIVING_SITE(DEPT)*/ * FROM BSEMPMS,DEPT@BSDPTMS WHERE BSEMPMS.DPT_NO=DEPT.DPT_NO;
4.dbms_repcat_admin能带来什么安全隐患 如果一个用户能执行dbms_repcat_admin包,将获得极大的系统权限。 以下情况可能获得该包的执行权限: 1、在sys下grant execute on dbms_repcat_admin to public[|user_name] 2、用户拥有execute any procedure特权(仅限于9i以下,9i必须显示授权) 如果用户通过执行如下语句: exec sys.dbms_repcat_admin.grant_admin_any_schema('user_name'); 该用户将获得极大的系统特权 可以从user_sys_privs中获得详细信息
5.在不知道用户密码的时候,怎么样跳转到另外一个用户执行操作后并不影响该用户? 我们通过如下的方法,可以安全使用该用户,然后再跳转回来,在某些时候比较有用 需要Alter user权限或DBA权限: SQL> select password from dba_users where username='SCOTT'; PASSWORD ----------------------------- F894844C34402B67 SQL> alter user scott identified by lion; User altered. SQL> connect scott/lion Connected. REM Do whatever you like... SQL> connect system/manager Connected. SQL> alter user scott identified by values 'F894844C34402B67'; User altered. SQL> connect scott/tiger Connected.
9.怎么捕获用户登录信息,如SID,IP地址等 可以利用登录触发器,如 CREATE OR REPLACE TRIGGER tr_login_record AFTER logon ON DATABASE DECLARE miUserSid NUMBER; mtSession v$session%ROWTYPE; CURSOR cSession(iiUserSid IN NUMBER) IS SELECT * FROM v$session WHERE sid=iiUserSid; BEGIN SELECT sid INTO miUserSid FROM v$mystat WHERE rownum
10.程序报错 ORA-01555: snapshot too old: rollback segment number 148 with name "_SYSSMU148$" too small 可以查询错误原因 oerr ora 01555 得到如下信息 01555, 00000, "snapshot too old: rollback segment number %s with name \"%s\" too small" // *Cause: rollback records needed by a reader for consistent read are // overwritten by other writers // *Action: If in Automatic Undo Management mode, increase undo_retention // setting. Otherwise, use larger rollback segments
说明一个事务的执行时长超过了系统设定的最大值. SQL> show parameters undo 比如 NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_management string AUTO undo_retention integer 16200 undo_suppress_errors boolean FALSE undo_tablespace string UNDOTBS2
/L8"sql" Nocase Line Comment = -- Block Comment On = /* Block Comment Off = */ String Chars = ' File Extensions = SQL /Delimiters = ~!%@^&*()-+=|\/{}[]:;"'<> , .? /Function String = "%[A-Z _]*[~\s]+(*)" /C1 "Keywords" ABORT ACCEPT ACCESS ADD ALL ALTER AND ANY ARRAY ARRAYLEN AS ASC ASSERT ASSIGN AT AUDIT AUTHORIZATION AVG BASE_TABLE BEGIN BETWEEN BINARY_INTEGER BODY BOOLEAN BY CASE CHAR CHAR_BASE CHECK CLOSE CLUSTER CLUSTERS COLAUTH COLUMN COMMENT COMMIT COMPRESS CONNECT CONSTANT CRASH CREATE CURRENT CURRVAL CURSOR DATABASE DATA_BASE DATE DBA DEBUGOFF DEBUGON DECLARE DECIMAL DEFAULT DEFINITION DELAY DELETE DESC DIGITS DISPOSE DISTINCT DO DROP ELSE ELSIF END ENTRY EXCEPTION EXCEPTION_INIT EXCLUSIVE EXISTS EXIT FALSE FETCH FILE FLOAT FOR FORM FROM FUNCTION GENERIC GOTO GRANT GROUP HAVING IDENTIFIED IF IMMEDIATE IN INCREMENT INDEX INDEXES INDICATOR INITIAL INSERT INTEGER INTERFACE INTERSECT INTO IS LEVEL LIKE LIMITED LOCK LONG LOOP MAX MAXEXTENTS MIN MINEXTENTS MINUS MISLABEL MOD MODE NATURAL NATURALN NEW NEXTVAL NOAUDIT NOCOMPRESS NOLOGGING NOT NOWAIT NULL NUMBER NUMBER_BASE OF OFFLINE ON ONLINE OPEN OPTION OR ORDER OTHERS OUT PACKAGE PARTITION PCTFREE PCTUSED PLS_INTEGER POSITIVE POSITIVEN PRAGMA PRIOR PRIVATE PRIVILEGES PROCEDURE PUBLIC RAISE RANGE RAW REAL RECORD REF RELEASE REMR RENAME RESOURCE RETURN REVERSE REVOKE ROLLBACK ROW ROWID ROWLABEL ROWNUM ROWS ROWTYPE RUN SAVEPOINT SCHEMA SELECT SEPARATE SESSION SET SHARE SMALLINT SPACE SQL SQLCODE SQLERRM START STATEMENT STDDEV SUBTYPE SUCCESSFUL SUM SYNONYM SYSDATE TABAUTH TABLE TABLES TASK TERMINATE THEN TO TRIGGER TRUE TYPE UID UNION UNIQUE UPDATE USE USER VALIDATE VALUES VARCHAR VARCHAR2 VARIANCE VIEW VIEWS WHEN WHENEVER WHERE WHILE WITH WORK WRITE XOR /C2 "Packages" DBMS_OUTPUT DBMS_JOB DBMS_SQL /C3 "Package Methods" BIND_VARIABLE BIND_VARIABLE_CHAR BIND_VARIABLE_RAW BIND_VARIABLE_ROWID BROKEN CHANGE CLOSE_CURSOR COLUMN_VALUE COLUMN_VALUE_CHAR COLUMN_VALUE_RAW COLUMN_VALUE_ROWID DEFINE_COLUMN DEFINE_COLUMN_CHAR DEFINE_COLUMN_RAW DEFINE_COLUMN_ROWID DISABLE ENABLE EXECUTE EXECUTE_AND_FETCH FETCH_ROWS GET_LINE GET_LINES INTERVAL IS_OPEN LAST_ERROR_POSITION LAST_ROW_COUNT LAST_ROW_ID LAST_SQL_FUNCTION_CODE NEW_LINE NEXT_DATE OPEN_CURSOR PARSE PUT PUT_LINE REMOVE SUBMIT WHAT /C4 "SQL*Plus Commands" EXECUTE GRANT REPLACE RUN START /C5 "SQL*Plus Functions" ABS ADD_MONTHS ASCII CEIL CHR CONCAT CONVERT COS COSH DECODE EXP FLOOR GREATEST HEXTORAW INITCAP INSTR LAST_DAY LENGTH LN LOG LOWER LPAD LTRIM MOD MONTHS_BETWEEN NEXT_DAY NEW_TIME NLS_INITCAP NLS_LOWER NLS_UPPER NVL POWER RAWTOHEX REPLACE ROUND ROWIDTOCHAR RPAD RTRIM SIGN SIN SINH SOUNDEX SQRT SUBSTR SUBSTRB TAN TANH TO_CHAR TO_DATE TO_LABEL TO_MULTI_BYTE TO_NUMBER TO_SINGLE_TYPE TRANSLATE TRUNC UPPER USERENV VSIZE /C6 "Should Be Reserved" BEFORE EACH FOR KEY PRIMARY WHEN WHERE WHILE /C7 "CONST Var" DUP_VAL_INDEX TIMEOUT_ON_RESOURCE TRANSACTION_BACKED_OUT INVALID_CURSOR NOT_LOGED_ON LOGIN_DENIED NO_DATA_FOUND SYS_INVALID_ROWID TOO_MANY_ROWS ZERO_DIVIDE INVALID_NUMBER STORAGE_ERROR PROGRAM_ERROR VALUE_ERROR NOTFOUND FOUND
//创建临时表空间
create temporary tablespace zfmi_temp tempfile 'D:\oracle\oradata\zfmi\zfmi_temp.dbf' size 32m autoextend on next 32m maxsize 2048m extent management local;
//tempfile参数必须有
//创建数据表空间
create tablespace zfmi logging datafile 'D:\oracle\oradata\zfmi\zfmi.dbf' size 100m autoextend on next 32m maxsize 2048m extent management local;
select * from v$version
SELECT machine ,terminal FROM V$SESSION;
select * from all_tables where table_name like '%'
select * from all_tab_columns where table_name='??'
怎样计算一个表占用的空间的大小?
select owner,table_name,NUM_ROWS,BLOCKS*AAA/1024/1024 "Size M",EMPTY_BLOCKS,LAST_ANALYZED
from dba_tables
where table_name='XXX';
14. 如何查看系统被锁的事务时间?
select * from v$locked_object ;
16. 怎么获取有哪些用户在使用数据库?
select username from v$session;
怎样查得数据库的SID ?
select name from v$database;也可以直