oracle有用资料.doc

l 第一章:日志管理1.forcing log switchessql alter system switch logfile;2.forcing checkpointssql alter system checkpoint;3.adding online redo log groupssql alter database add logfile group 4sql (/disk3/log4a.rdo,/disk4/log4b.rdo) size 1m;4.adding online redo log memberssql alter database add logfile membersql /disk3/log1b.rdo to group 1,sql /disk4/log2b.rdo to group 2;5.changes the name of the online redo logfilesql alter database rename file c:/oracle/oradata/oradb/redo01.logsql to c:/oracle/oradata/redo01.log;6.drop online redo log groupssql alter database drop logfile group 3;7.drop online redo log memberssql alter database drop logfile member c:/oracle/oradata/redo01.log;8.clearing online redo log filessql alter database clear unarchived logfile c:/oracle/log2a.rdo;9.using logminer analyzing redo logfilesa. in the init.ora specify utl_file_dir = b. sql execute dbms_logmnr_d.build(oradb.ora,c:oracleoradblog);c. sql execute dbms_logmnr_add_logfile(c:oracleoradataoradbredo01.log,sql dbms_logmnr.new);d. sql execute dbms_logmnr.add_logfile(c:oracleoradataoradbredo02.log,sql dbms_logmnr.addfile);e. sql execute dbms_logmnr.start_logmnr(dictfilename=c:oracleoradblogoradb.ora);f. sql select * from v$logmnr_contents(v$logmnr_dictionary,v$logmnr_parameterssql v$logmnr_logs);g. sql execute dbms_logmnr.end_logmnr;l 第二章:表空间管理1.create tablespacessql create tablespace tablespace_name datafile c:oracleoradatafile1.dbf size 100m,sql c:oracleoradatafile2.dbf size 100m minimum extent 550k logging/nologgingsql default storage (initial 500k next 500k maxextents 500 pctinccease 0)sql online/offline permanent/temporary extent_management_clause2.locally managed tablespacesql create tablespace user_data datafile c:oracleoradatauser_data01.dbfsql size 500m extent management local uniform size 10m;3.temporary tablespacesql create temporary tablespace temp tempfile c:oracleoradatatemp01.dbfsql size 500m extent management local uniform size 10m;4.change the storage settingsql 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 onlinesql alter tablespace app_data offline;sql alter tablespace app_data online;6.read_only tablespacesql alter tablespace app_data read only|write;7.droping tablespacesql drop tablespace app_data including contents;8.enableing automatic extension of data filessql alter tablespace app_data add datafile c:oracleoradataapp_data01.dbf size 200msql autoextend on next 10m maxsize 500m;9.change the size fo data files manuallysql alter database datafile c:oracleoradataapp_data.dbf resize 200m;10.Moving data files: alter tablespacesql alter tablespace app_data rename datafile c:oracleoradataapp_data.dbfsql to c:oracleapp_data.dbf;11.moving data files:alter databasesql alter database rename file c:oracleoradataapp_data.dbfsql to c:oracleapp_data.dbf;l 第三章:表1.create a tablesql create table table_name (column datatype,column datatype.)sql tablespace tablespace_name pctfree integer pctused integersql initrans integer maxtrans integersql storage(initial 200k next 200k pctincrease 0 maxextents 50)sql logging|nologging cache|nocache2.copy an existing tablesql create table table_name logging|nologging as subquery3.create temporary tablesql create global temporary table xay_temp as select * from xay;on commit preserve rows/on commit delete rows4.pctfree = (average row size - initial row size) *100 /average row sizepctused = 100-pctfree- (average row size*100/available data space)5.change storage and block utilization parametersql alter table table_name pctfree=30 pctused=50 storage(next 500ksql minextents 2 maxextents 100);6.manually allocating extentssql alter table table_name allocate extent(size 500k datafile c:/oracle/data.dbf);7.move tablespacesql alter table employee move tablespace users;8.deallocate of unused spacesql alter table table_name deallocate unused keep integer9.truncate a tablesql truncate table table_name;10.drop a tablesql drop table table_name cascade constraints;11.drop a columnsql alter table table_name drop column comments cascade constraints checkpoint 1000;alter table table_name drop columns continue;12.mark a column as unusedsql 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 1000data_dictionary : dba_unused_col_tabsl 第四章:索引1.creating function-based indexessql create index summit.item_quantity on summit.item(quantity-quantity_shipped);2.create a B-tree indexsql create unique index index_name on table_name(column,. asc/desc) tablespacesql tablespace_name pctfree integer initrans integer maxtrans integersql logging | nologging nosort storage(initial 200k next 200k pctincrease 0sql maxextents 50);3.pctfree(index)=(maximum number of rows-initial number of rows)*100/maximum number of rows4.creating reverse key indexessql create unique index xay_id on xay(a) reverse pctfree 30 storage(initial 200ksql next 200k pctincrease 0 maxextents 50) tablespace indx;5.create bitmap indexsql create bitmap index xay_id on xay(a) pctfree 30 storage( initial 200k next 200ksql pctincrease 0 maxextents 50) tablespace indx;6.change storage parameter of indexsql alter index xay_id storage (next 400k maxextents 100);7.allocating index spacesql alter index xay_id allocate extent(size 200k datafile c:/oracle/index.dbf);8.alter index xay_id deallocate unused;l 第五章:约束1.define constraints as immediate or deferredsql alter session set constraints = immediate/deferred/default;set constraints constraint_name/all immediate/deferred;2. sql drop table table_name cascade constraintssql drop tablespace tablespace_name including contents cascade constraints3. define constraints while create a tablesql create table xay(id number(7) constraint xay_id primary key deferrablesql using index storage(initial 100k next 100k) tablespace indx);primary key/unique/references table(column)/check4.enable constraintssql alter table xay enable novalidate constraint xay_id;5.enable constraintssql alter table xay enable validate constraint xay_id;l 第六章:LOAD数据1.loading data using direct_load insertsql insert /*+append */ into emp nologgingsql select * from emp_old;2.parallel direct-load insertsql alter session enable parallel dml;sql insert /*+parallel(emp,2) */ into emp nologgingsql select * from emp_old;3.using sql*loadersql sqlldr scott/tiger sql control = ulcase6.ctl sql log = ulcase6.log direct=truel 第七章:数据整理1.using expoty$exp scott/tiger tables(dept,emp) file=c:emp.dmp log=exp.log compress=n direct=y2.using import$imp scott/tiger tables(dept,emp) file=emp.dmp log=imp.log ignore=y3.transporting a tablespacesqlalter tablespace sales_ts read only;$exp sys/. file=xay.dmp transport_tablespace=y tablespace=sales_tstriggers=n constraints=n$copy datafile$imp sys/. file=xay.dmp transport_tablespace=y datafiles=(/disk1/sles01.dbf,/disk2/sles02.dbf)sql alter tablespace sales_ts read write;4.checking transport setsql DBMS_tts.transport_set_check(ts_list =sales_ts .,incl_constraints=true);在表transport_set_violations 中查看sql dbms_tts.isselfcontained 为true 是， 表示自包含一、ORACLE的启动和关闭1、在单机环境下要想启动或关闭ORACLE系统必须首先切换到ORACLE用户，如下su-oraclea、启动ORACLE系统oraclesvrmgrlSVRMGRconnectinternalSVRMGRstartupSVRMGRquitb、关闭ORACLE系统oraclesvrmgrlSVRMGRconnectinternalSVRMGRshutdownSVRMGRquit启动oracle9i数据库命令：$sqlplus/nologSQL*Plus:Release9.2.0.1.0-ProductiononFriOct3113:53:532003Copyright(c)1982,2002,OracleCorporation.Allrightsreserved.SQLconnect/assysdbaConnectedtoanidleinstance.SQLstartupCSQLstartupORACLEinstancestarted.2、在双机环境下要想启动或关闭ORACLE系统必须首先切换到root用户，如下suroota、启动ORACLE系统haregyoracleb、关闭ORACLE系统haregnoracleOracle数据库有哪几种启动方式说明：有以下几种启动方式：1、startupnomount非安装启动，这种方式启动下可执行：重建控制文件、重建数据库读取init.ora文件，启动instance，即启动SGA和后台进程，这种启动只需要init.ora文件。2、startupmountdbname安装启动，这种方式启动下可执行：数据库日志归档、数据库介质恢复、使数据文件联机或脱机，重新定位数据文件、重做日志文件。执行“nomount”，然后打开控制文件，确认数据文件和联机日志文件的位置，但此时不对数据文件和日志文件进行校验检查。3、startupopendbname先执行“nomount”，然后执行“mount”，再打开包括Redolog文件在内的所有数据库文件，这种方式下可访问数据库中的数据。4、startup，等于以下三个命令startupnomountalterdatabasemountalterdatabaseopen5、startuprestrict约束方式启动这种方式能够启动数据库，但只允许具有一定特权的用户访问非特权用户访问时，会出现以下提示：ERROR：ORA-01035:ORACLE只允许具有RESTRICTEDSESSION权限的用户使用6、startupforce强制启动方式当不能关闭数据库时，可以用startupforce来完成数据库的关闭先关闭数据库，再执行正常启动数据库命令7、startuppfile=参数文件名带初始化参数文件的启动方式先读取参数文件，再按参数文件中的设置启动数据库例：startuppfile=E:Oracleadminoradbpfileinit.ora8、startupEXCLUSIVE二、用户如何有效地利用数据字典ORACLE的数据字典是数据库的重要组成部分之一，它随着数据库的产生而产生,随着数据库的变化而变化,体现为sys用户下的一些表和视图。数据字典名称是大写的英文字符。数据字典里存有用户信息、用户的权限信息、所有数据对象信息、表的约束条件、统计分析数据库的视图等。我们不能手工修改数据字典里的信息。很多时候，一般的ORACLE用户不知道如何有效地利用它。dictionary全部数据字典表的名称和解释，它有一个同义词dictdict_column全部数据字典表里字段名称和解释如果我们想查询跟索引有关的数据字典时，可以用下面这条SQL语句:SQLselect*fromdictionarywhereinstr(comments,index)0;如果我们想知道user_indexes表各字段名称的详细含义，可以用下面这条SQL语句:SQLselectcolumn_name,commentsfromdict_columnswheretable_name=USER_INDEXES;依此类推，就可以轻松知道数据字典的详细名称和解释，不用查看ORACLE的其它文档资料了。下面按类别列出一些ORACLE用户常用数据字典的查询使用方法。1、用户查看当前用户的缺省表空间SQLselectusername,default_tablespacefromuser_users;查看当前用户的角色SQLselect*fromuser_role_privs;查看当前用户的系统权限和表级权限SQLselect*fromuser_sys_privs;SQLselect*fromuser_tab_privs;2、表查看用户下所有的表SQLselect*fromuser_tables;查看名称包含log字符的表SQLselectobject_name,object_idfromuser_objectswhereinstr(object_name,LOG)0;查看某表的创建时间SQLselectobject_name,createdfromuser_objectswhereobject_name=upper(&table_name);查看某表的大小SQLselectsum(bytes)/(1024*1024)assize(M)fromuser_segmentswheresegment_name=upper(&table_name);查看放在ORACLE的内存区里的表SQLselecttable_name,cachefromuser_tableswhereinstr(cache,Y)0;3、索引查看索引个数和类别SQLselectindex_name,index_type,table_namefromuser_indexesorderbytable_name;查看索引被索引的字段SQLselect*fromuser_ind_columnswhereindex_name=upper(&index_name);查看索引的大小SQLselectsum(bytes)/(1024*1024)assize(M)fromuser_segmentswheresegment_name=upper(&index_name);4、序列号查看序列号，last_number是当前值SQLselect*fromuser_sequences;5、视图查看视图的名称SQLselectview_namefromuser_views;查看创建视图的select语句SQLsetview_name,text_lengthfromuser_views;SQLsetlong2000;说明：可以根据视图的text_length值设定setlong的大小SQLselecttextfromuser_viewswhereview_name=upper(&view_name);6、同义词查看同义词的名称SQLselect*fromuser_synonyms;7、约束条件查看某表的约束条件SQLselectconstraint_name,constraint_type,search_condition,r_constraint_namefromuser_constraintswheretable_name=upper(&table_name);SQLselectc.constraint_name,c.constraint_type,cc.column_namefromuser_constraintsc,user_cons_columnsccwherec.owner=upper(&table_owner)andc.table_name=upper(&table_name)andc.owner=cc.ownerandc.constraint_name=cc.constraint_nameorderbycc.position;8、存储函数和过程查看函数和过程的状态SQLselectobject_name,statusfromuser_objectswhereobject_type=FUNCTION;SQLselectobject_name,statusfromuser_objectswhereobject_type=PROCEDURE;查看函数和过程的源代码SQLselecttextfromall_sourcewhereowner=userandname=upper(&plsql_name);三、查看数据库的SQL1、查看表空间的名称及大小selectt.tablespace_name,round(sum(bytes/(1024*1024),0)ts_sizefromdba_tablespacest,dba_data_filesdwheret.tablespace_name=d.tablespace_namegroupbyt.tablespace_name;2、查看表空间物理文件的名称及大小selecttablespace_name,file_id,file_name,round(bytes/(1024*1024),0)total_spacefromdba_data_filesorderbytablespace_name;3、查看回滚段名称及大小selectsegment_name,tablespace_name,r.status,(initial_extent/1024)InitialExtent,(next_extent/1024)NextExtent,max_extents,v.curextCurExtentFromdba_rollback_segsr,v$rollstatvWherer.segment_id=v.usn(+)orderbysegment_name;4、查看控制文件selectnamefromv$controlfile;5、查看日志文件selectmemberfromv$logfile;6、查看表空间的使用情况selectsum(bytes)/(1024*1024)asfree_space,tablespace_namefromdba_free_spacegroupbytablespace_name;SELECTA.TABLESPACE_NAME,A.BYTESTOTAL,B.BYTESUSED,C.BYTESFREE,(B.BYTES*100)/A.BYTES%USED,(C.BYTES*100)/A.BYTES%FREEFROMSYS.SM$TS_AVAILA,SYS.SM$TS_USEDB,SYS.SM$TS_FREECWHEREA.TABLESPACE_NAME=B.TABLESPACE_NAMEANDA.TABLESPACE_NAME=C.TABLESPACE_NAME;7、查看数据库库对象selectowner,object_type,status,count(*)count#fromall_objectsgroupbyowner,object_type,status;8、查看数据库的版本SelectversionFROMProduct_component_versionWhereSUBSTR(PRODUCT,1,6)=Oracle;9、查看数据库的创建日期和归档方式SelectCreated,Log_Mode,Log_ModeFromV$Database;四、ORACLE用户连接的管理用系统管理员，查看当前数据库有几个用户连接：SQLselectusername,sid,serial#fromv$session;如果要停某个连接用SQLaltersystemkillsessionsid,serial#;如果这命令不行,找它UNIX的进程数SQLselectpro.spidfromv$sessionses,v$processprowhereses.sid=21andses.paddr=pro.addr;说明：21是某个连接的sid数然后用kill命令杀此进程号。五、SQL*PLUS使用a、近入SQL*Plus$sqlplus用户名/密码退出SQL*PlusSQLexitb、在sqlplus下得到帮助信息列出全部SQL命令和SQL*Plus命令SQLhelp列出某个特定的命令的信息SQLhelp命令名c、显示表结构命令DESCRIBESQLDESC表名d、SQL*Plus中的编辑命令显示SQL缓冲区命令SQLL修改SQL命令首先要将待改正行变为当前行SQLn用CHANGE命令修改内容SQLc/旧/新重新确认是否已正确SQLL使用INPUT命令可以在SQL缓冲区中增加一行或多行SQLiSQL输入内容e、调用外部系统编辑器SQLedit文件名可以使用DEFINE命令设置系统变量EDITOR来改变文本编辑器的类型，在login.sql文件中定义如下一行DEFINE_EDITOR=vif、运行命令文件SQLSTARTtestSQLtest常用SQL*Plus语句a、表的创建、修改、删除创建表的命令格式如下：createtable表名（列说明列表）；为基表增加新列命令如下：ALTERTABLE表名ADD（列说明列表）例：为test表增加一列Age，用来存放年龄sqlaltertabletestadd（Agenumber(3)）；修改基表列定义命令如下：ALTERTABLE表名MODIFY（列名数据类型）例：将test表中的Count列宽度加长为10个字符sqlalteratbletestmodify（Countychar(10)）；b、将一张表删除语句的格式如下：DORPTABLE表名；例：表删除将同时删除表的数据和表的定义sqldroptabletestc、表空间的创建、删除六、ORACLE逻辑备份的SH文件完全备份的SH文件：exp_comp.shrq=date+%m%dsu-oracle-cexpsystem/managerfull=yinctype=completefile=/oracle/export/db_comp$rq.dmp累计备份的SH文件：exp_cumu.shrq=date+%m%dsu-oracle-cexpsystem/managerfull=yinctype=cumulativefile=/oracle/export/db_cumu$rq.dmp增量备份的SH文件:exp_incr.shrq=date+%m%dsu-oracle-cexpsystem/managerfull=yinctype=incrementalfile=/oracle/export/db_incr$rq.dmproot用户crontab文件/var/spool/cron/crontabs/root增加以下内容021*/oracle/exp_comp.sh302*0-5/oracle/exp_incr.sh452*6/oracle/exp_cumu.sh当然这个时间表可以根据不同的需求来改变的，这只是一个例子。七、ORACLE常用的SQL语法和数据对象一.数据控制语句(DML)部分1.INSERT(往数据表里插入记录的语句)INSERTINTO表名(字段名1,字段名2,)VALUES(值1,值2,);INSERTINTO表名(字段名1,字段名2,)SELECT(字段名1,字段名2,)FROM另外的表名;字符串类型的字段值必须用单引号括起来,例如:GOODDAY如果字段值里包含单引号需要进行字符串转换,我们把它替换成两个单引号.字符串类型的字段值超过定义的长度会出错,最好在插入前进行长度校验.日期字段的字段值可以用当前数据库的系统时间SYSDATE,精确到秒或者用字符串转换成日期型函数TO_DATE(2001-08-01,YYYY-MM-DD)TO_DATE()还有很多种日期格式,可以参看ORACLEDOC.年-月-日小时:分钟:秒的格式YYYY-MM-DDHH24:MI:SSINSERT时最大可操作的字符串长度小于等于4000个单字节,如果要插入更长的字符串,请考虑字段用CLOB类型,方法借用ORACLE里自带的DBMS_LOB程序包.INSERT时如果要用到从1开始自动增长的序列号,应该先建立一个序列号CREATESEQUENCE序列号的名称(最好是表名+序列号标记)INCREMENTBY1STARTWITH1MAXVALUE99999CYCLENOCACHE;其中最大的值按字段的长度来定,如果定义的自动增长的序列号NUMBER(6),最大值为999999INSERT语句插入这个字段值为:序列号的名称.NEXTVAL2.DELETE(删除数据表里记录的语句)DELETEFROM表名WHERE条件;注意：删除记录并不能释放ORACLE里被占用的数据块表空间.它只把那些被删除的数据块标成unused.如果确实要删除一个大表里的全部记录,可以用TRUNCATE命令,它可以释放占用的数据块表空间TRUNCATETABLE表名;此操作不可回退.3.UPDATE(修改数据表里记录的语句)UPDATE表名SET字段名1=值1,字段名2=值2,WHERE条件;如果修改的值N没有赋值或定义时,将把原来的记录内容清为NULL,最好在修改前进行非空校验;值N超过定义的长度会出错,最好在插入前进行长度校验.注意事项:A.以上SQL语句对表都加上了行级锁,确认完成后,必须加上事物处理结束的命令COMMIT才能正式生效,否则改变不一定写入数据库里.如果想撤回这些操作,可以用命令ROLLBACK复原.B.在运行INSERT,DELETE和UPDATE语句前最好估算一下可能操作的记录范围,应该把它限定在较小(一万条记录)范围内,.否则ORACLE处理这个事物用到很大的回退段.程序响应慢甚至失去响应.如果记录数上十万以上这些操作,可以把这些SQL语句分段分次完成,其间加上COMMIT确认事物处理.二.数据定义(DDL)部分1.CREATE(创建表,索引,视图,同义词,过程,函数,数据库链接等)ORACLE常用的字段类型有CHAR固定长度的字符串VARCHAR2可变长度的字符串NUMBER(M,N)数字型M是位数总长度,N是小数的长度DATE日期类型创建表时要把较小的不为空的字段放在前面,可能为空的字段放在后面创建表时可以用中文的字段名,但最好还是用英文的字段名创建表时可以给字段加上默认值,例如DEFAULTSYSDATE这样每次插入和修改时,不用程序操作这个