[计算机]性能优化常用sql.doc

窗体顶端天天好心情留下我的路迹，偶或停下身来看看。挡档灰尘，啪啪土，明天在路途。 登录 注册 空间 博客 好友 相册 留言oracle 常用命令 收藏 1.增加主键 alter table TABLE_NAME add constraint KEY_NAME primary key (TABLE_COLUMN);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;5、删除主键或外键 alter table TABLE_NAME drop constraint KEY_NAME;6、在initorcl.ora中加入rollback_segments=(rb0,rb1,.) 其中rb0,rb1为自己定义的回滚段，可使这些回滚段在启动时生效7、svgmgrshow sga 显示全局区8、copy命令 格式: copy from to () using 其中:database string ；e.g:scott/tigeroracle9、客户端注册表修改 HKEY_LOCAL_MACHINE/SOFTWARE/ORACLE NLS_LANG_BAK -语言字符集 简体中文:SIMPLIFIED CHINESE_CHINA.ZHS16CGB231280 美国英文:AMERICA.WE8ISO8859P110、在win95/win98中在注册表中:HKEY_LOCAL_MACHINE/SOFTWARE/ORACLE填加一个字符串键值 local=oracle （可根据情况而定,指数据库别名) 即可以在sql*plus中不用输入连接串 或在autoexec.bat 中添加set local=alias_name11、在sqlplus快捷方式中，在属性窗口中，在目标中，在d:orawin95binplus33w.exe后面加空格和scott/tigeroracle即可快速进入12、修改数据库的字符集 在表props$中 update props$ set value$=ZHS16CGB23128013、oracle 安全与审计 user_sys_privs,user_tab_privs;配置文件 主要参数 session_per_user 每个用户可同时进行几个会话 cpu_per_session 每个用户可用多少个(cpu的)百分之一秒 cpu_per_call 语法分析、执行、取数可用多少个百分之一秒 connect_time 用户连接数据库的时间(分钟) idle_time 用户不调用数据库的时间(分钟) logical_reads_per_session 整个会话过程中用户访问oracle的块数 logical_reads_per_call 一次调用用户访问oracle的块数 private_SGA 一个用户进程所用SGA的内存数量 composite_limit 复合限制数 failed_login_attempts 连续多次注册失败引起一个帐户死锁 password_life_time 一个口令在其终止前可用的天数 password_reuse_time 一个口令在其n天才能重新使用 password_reuse_max 一个口令在重新使用之前必须改变多少次 password_lock_time 一个口令帐户被锁住的天数14、管理初始化文件 show parameters 经常修改的项目有 v$parameter shared_pool_size 分配给共享的字节数 rollback_segments 回滚段的个数 sessions 会话个数 processes 进程个数15、管理控制文件 控制文件保存文件有关数据库创建时间、数据库名以及数据库运行时使用的所有文件位置和名称。 增加控制文件，在initorcl.ora中，找到control_file项,增加一项即可 删除控制文件，在initorcl.ora中去掉，然后删除物理文件 建立新的控制文件 create controlfile reuse set database 数据库名 logfile group 整数 文件名 ,group 整数 文件名,. 对于现有的数据库，可以间接地通过 alter database backup controlfile to trace命令生成控制文件，即可在orantrmb7trace 下有ora00289.trc文件，其内容为文本16、管理回滚段: 存放事务的恢复信息 建立回滚段 create public rollback segment SEG_NAME tabelspace TABLESPACE_NAME; alter rollback segment SEG_NAME online; 删除回滚段 首先改变为offline状态 直接使用回滚段 sqlset transaction use rollback segment SEG_NAME;17、管理日志 建立日志组 sqlselect * from v$logfile; sqlalter database add logfile group 3 (f:orantdatabaselog1_g3.ora f:orantdatabaselog2_g3.ora) size 100k; sqlselect * from v$logfile; 删除日志组 alter database drop logfile group 1; 但是其物理文件并没有被删除掉 系统至少需要2个日志组，如果只有2个，就不能删除 不能删除正活动的日志组 手工归档 通过alter system 的archive log 子句来实现 archive log thread 整数 seq 整数change 整数currentgroup 整数 logfile 文件名nextallstartto 位置18 系统控制 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 强制执行日志转换19 会话控制 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 studentteach set sold=sold+1 where sno=98010; commit; alter session close database link teach; 关闭远程链路20、封锁机制 数据封锁:保护表数据，在多个用户并行存取数据时候，保证数据的完整性。 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; 21、通常oracle需要启动OracleServiceORCL,OracleStartORCL,OracleTNSListener 任务 在NT上至少要启动两个服务 oraclestartID和oracleserverID22、每个数据库都有一个系统标识符(SID)，典型安装的数据库使用的系统标识符是ORCL23、删除带约束的表 Drop table 表名 cascade constraints;24、设置事务 set transaction read onlyread writeuse rollback segment 回滚段名25、建外键 单字段时：create table 表名 (col1 char(8), cno char(4) REFERENCE course); 多个字段时,在最后加上 Foreign Key (字段名) REFERENCE 表名(字段) 连带删除选项 (on delete cascade 当指定时，如果父表中的记录被删除，则依赖于父表的记录也被删除 REFERENCE 表名（） on delete cascade;26、启动关闭数据库 关闭： svrmgrconnect internal/oracle shutdown -正常关闭数据库 svrmgrshutdown immediate -立即关闭数据库 svrmgrshutdown abort -一种最直接的关闭数据库的方式，执行之后，重新启动需要花6-8小时 启动： $svrmgrl svrmgrstartup -正常启动 -等价于:startup nomount; alter database mount; alter database open; svrmgrstartup mount; -安装启动：用于改变数据库的归档或执行恢复状态 svrmgrstartup nomount; -用于重建控制文件或重建数据库 svrmgrstartup restrict; -约束启动，能启动数据库，但只允许具有一定特权的用户访问 如果希望改变这种状态，连接成功后 alter system disable restricted session; svrmgrstartup force;当不能关闭数据库时，可采用强制启动数据库来完成数据库的关闭操作。 svrmgrstartup pfile=d:orantdatabaseinitorcl.ora -带初始化参数文件的启动27、缺省用户和密码 . Oracle安裝完成后的初始口令? internal/oracle sys/change_on_install system/manager scott/tiger sysman/oem_temp . ORACLE9IAS WEB CACHE的初始默认用户和密码？ administrator/administrator28、对公共授予访问权 grant select on 表名 to public; create public synonym 同义词名 for 表名;29、填加注释 comment on table 表名 is 注释; comment on column 表名.列名 is 注释;30 oracle loader 控制文件的格式 load data infile 数据文件名 into table 表名 (first_name position(01:14) char, surname position(15:28) char, clssn position(29:36) char, hire_data position(37:42) date YYMMDD) 31、程序中报错:maxinum cursor exceed! 应该调整数据库初始化文件 加如一项 open_cursors=20032、生成用户时指定缺省表空间 create user 用户名 identified by 口令 default tablespace 表空间名;33、重新指定用户的缺省表空间 alter user 用户名 default tablespace 表空间名34、约束条件 create table employee (empno number(10) primary key, name varchar2(40) not null, deptno number(2) default 10, salary number(7,2) check salaryexec my_execute(select * from tab); sqlexec my_execute(insert into test value|(|ddd|); sqlexec 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;46.1 用EXECUTE IMMEDIATE . 在PL/SQL运行DDL语句 begin execute immediate set role all; end; . 给动态语句传值(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; . 从动态语句检索值(INTO子句) declare l_cnt varchar2(20); begin execute immediate select count(1) from emp into l_cnt; dbms_output.put_line(l_cnt); end; . 动态调用例程.例程中用到的绑定变量参数必须指定参数类型.黓认为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;. 将返回值传递到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; . 传递并检索值.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; . 多行查询选项.对此选项用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; . 完成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; 47、用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; 48、重新编译 sqlexec dbms_pile_schema(schema); 如： sqlexec dbms_pile_schema(scott); 49、去除数据库中特殊字符 .字符串字段中含有,如果用来组合sql语句，会造成语句不准确。 比如：replace(f1,) .字符串字段中含有t n,如果用来在c或者c程序中输出到文件，格式无法保证。 比如：replace(f2,t,) .清除换行和回车 比如: replace(f2,chr(13)|chr(10),)50.如果希望用aimtzmcc用户连接数据库，访问aicbs用户的表，不在表名前缀aicbs.，可以在建立数据库连接后发下面的命令 alter session set current_schema = aicbs;51.查询锁的原因 如果进程被死锁，可以按下面方式查询 从v$session或者v$locked_object找到此session 如果有lockwait，查询v$lock, select * from v$lock where kaddr = C00000024AB87210 如果没有，根据sid select * from v$lock where sid = 438 查看v$lock lmode 0，表示已经得到此锁 request 0 表示正在请求此锁 根据id1和id2的值可以判断请求哪个锁的释放。 select * from v$lock where id1=134132 and id2 = 31431 52.oracle8中扩充了group by rollup和cube的操作。有时候省了你好多功夫的。 下面的语句可以进行总计 select region_code,count(*) from aicbs.acc_woff_notify group by rollup(region_code); 下面的语句可以按照rollup不同的字段进行小计 select region_code,write_status,count(*) from aicbs.acc_woff_notify group by region_code,rollup(write_status); select region_code,write_status,count(*) from aicbs.acc_woff_notify group