下载文档原格式
best practice用法"Best practice" 是一个常用的商业术语，用于描述在特定领域中被认为是最有效、最可行的做法或方法。
这个术语通常用于企业管理、信息技术、项目管理等领域。
下面我将从不同角度来解释"best practice" 的用法。
首先，"best practice" 可以用于指代一种通用的标准或方法，这种标准或方法被认为是在特定领域中最有效的。
比如，在软件开发领域，"best practice" 可能是指一种被认可为在特定情况下最有效的编码或测试方法。
其次，"best practice" 也可以用于指代一种被广泛认可的行业标准或规范。
这意味着在特定行业中，某种做法被认为是最为有效和可行的，因此被推荐给其他从业者。
比如，在医疗行业，"best practice" 可能是指一种特定的手术方法或治疗方案。
此外，"best practice" 还可以用于描述公司内部的最佳工作方式或流程。
这意味着公司内部认可的、被证明是最有效的工作方式或流程。
比如，一个公司可能会制定一套"best practice" 来规范员工的日常工作，以确保工作的高效进行。
总的来说，"best practice" 是一个用于描述在特定领域中被认为是最有效、最可行的做法或方法的术语。
它可以指通用的标准或方法、行业标准或规范，以及公司内部的最佳工作方式或流程。
在实际应用中，人们应该根据具体情况和实际需求来理解和应用"best practice"，并不是所有的"best practice" 都适用于所有的情况。
plsql高级应用技巧PL/SQL Advanced Application TechniquesPL/SQL is a very powerful tool for developers to work with Oracle databases. It allows for the creation of stored procedures, functions, triggers, and packages, and provides the ability to write complex business logic within the database itself. This can greatly improve the performance and maintainability of an application, as well as provide better security by keeping sensitive data close to the source.PL/SQL高级应用技巧对于数据库开发人员来说是非常重要的。
通过PL/SQL，可以创建存储过程、函数、触发器和包，并在数据库中编写复杂的业务逻辑。
这可以极大地提高应用程序的性能和可维护性，同时通过将敏感数据保持在源头附近，提供更好的安全性。
One of the key benefits of using PL/SQL is the ability to encapsulate complex logic into reusable components. By creating functions and procedures, developers can abstract away the details of the implementation and expose only the necessary interface to other parts of the application. This not only makes the code easier to readand understand but also reduces the chances of bugs and inconsistencies in the logic.使用PL/SQL的一个关键好处是能够将复杂逻辑封装为可重用的组件。
经典sql练习题Introduction:SQL (Structured Query Language) is a programming language used for managing and manipulating relational databases. In this article, we will explore some classic SQL exercise questions to enhance our understanding of SQL concepts and improve our problem-solving skills. Each exercise will be accompanied by a description, SQL query, and explanation of the expected results.Exercise 1: Retrieving Data from a Single TableDescription:Retrieving specific columns from a table.SQL Query:SELECT column1, column2, ...FROM table_name;Explanation:Replace "column1, column2, ..." with the desired column names, and "table_name" with the appropriate table name. This query will return the specified columns from the table.Exercise 2: Filtering Data from a Single TableDescription:Filtering data based on specific conditions.SQL Query:SELECT *FROM table_nameWHERE condition;Explanation:Replace "table_name" with the table name you want to query. In the "WHERE" clause, specify the condition that needs to be met to retrieve the data.Exercise 3: Sorting Data from a Single TableDescription:Sorting data in ascending or descending order.SQL Query:SELECT *FROM table_nameORDER BY column1 [ASC|DESC];Explanation:Replace "table_name" with the desired table name and "column1" with the column you want to use for sorting. Use "ASC" for ascending order or "DESC" for descending order.Exercise 4: Joining Data from Multiple TablesDescription:Joining data from multiple tables based on a shared key.SQL Query:SELECT *FROM table1JOIN table2 ON table1.key = table2.key;Explanation:Replace "table1" and "table2" with the appropriate table names. "key" represents the shared key column between the two tables. This query will return the combined data from both tables based on the matching key values.Exercise 5: Aggregating DataDescription:Performing aggregate functions on a set of data.SQL Query:SELECT aggregate_function(column)FROM table_nameWHERE conditionGROUP BY column;Explanation:Replace "aggregate_function" with a specific function like SUM, AVG, MAX, MIN, etc. Adjust the "column" and "table_name" accordingly. The "WHERE" clause allows for filtering, and the "GROUP BY" clause groups the data based on a specific column.Exercise 6: Updating Data in a TableDescription:Modifying existing data within a table.SQL Query:UPDATE table_nameSET column = new_valueWHERE condition;Explanation:Replace "table_name" with the table you want to update, "column" with the specific column to modify, and "new_value" with the desired value. The "WHERE" clause specifies the condition or filter for updating specific rows.Exercise 7: Deleting Data from a TableDescription:Deleting specific data from a table.SQL Query:DELETE FROM table_nameWHERE condition;Explanation:Replace "table_name" with the table you want to delete data from. In the "WHERE" clause, specify the condition for deleting specific rows.Exercise 8: Creating a New TableDescription:Creating a new table in the database.SQL Query:CREATE TABLE table_name (column1 datatype constraint,column2 datatype constraint,...);Explanation:Replace "table_name" with the desired name for the new table. Specify the column names, data types, and any constraints required.Conclusion:These classic SQL exercise questions cover various aspects of SQL, such as retrieving data, filtering, sorting, joining multiple tables, aggregating data, updating, deleting, and creating tables. By practicing these exercises,you can strengthen your SQL skills and become more proficient in database management. Remember to adapt the queries to suit your specific scenario and always apply best practices when working with SQL.。
PLSQL程序优化和性能分析方法沈阳东软软件股份有限公司文件编号：TD文档编号版本号1．0分册名称第册/共册总页数正文附录编制张悦审批生效日期沈阳东软软件股份有限公司1.前言 (6)1.1目的 (6)1.2文档说明 (6)1.3词汇表 (6)1.4参考资料 (6)2. PLSQL程序优化原则 (7)2.1导致性能问题的内在原因 (7)2.2PLSQL优化的核心思想 (7)2.3ORACLE优化器 (7)2.4PLSQL优化 (8)2.4.1选择最有效率的表名顺序 (8)2.4.2 WHERE子句中的连接顺序 (9)2.4.3 SELECT子句中避免使用‘ * ‘ (9)2.4.4用EXISTS替代IN (9)2.4.5用NOT EXISTS替代NOT IN (10)2.4.6用表连接替换EXISTS (10)2.4.7用EXISTS替换DISTINCT (11)2.4.8减少对表的查询 (11)2.4.9避免循环（游标）里面嵌查询 (12)2.4.10尽量用union all替换union (14)2.4.11使用DECODE函数来减少处理时间 (14)2.4.12 group by优化 (14)2.4.13尽量避免用order by (15)2.4.14用Where子句替换HAVING子句 (15)2.4.15使用表的别名(Alias) (15)2.4.16删除重复记录 (15)2.4.17 COMMIT使用 (16)2.4.18减少多表关联 (16)2.4.19批量数据插入 (16)2.5索引使用优化 (17)2.5.1避免在索引列上使用函数或运算 (17)2.5.2避免改变索引列的类型. (18)2.5.3避免在索引列上使用NOT (18)2.5.4用>=替代> (19)2.5.5避免在索引列上使用IS NULL和IS NOT NULL (19)2.5.6带通配符（%）的like语句 (19)2.5.7总是使用索引的第一个列 (20)2.5.8多个平等的索引 (20)2.5.9不明确的索引等级 (20)2.5.10自动选择索引 (20)2.5.11使用提示(Hints) (20)2.5.12表上存在过旧的分析 (21)2.5.13表上存在并行 (22)2.5.14关于索引建立 (22)3. PLSQL程序性能问题测试方法 (22)3.1性能问题分析 (22)3.2E XPAIN P LAN分析索引使用 (23)3.3TOPSQL分析 (25)3.4针对性语句搜索 (29)3.5后台存储过程跟踪 (30)3.6性能监控 ............................................................................................................... 错误!未定义书签。
七、PLSQL存储函数和存储过程及打断点如何调试1、存储过程和函数的概念：ORACLE 提供可以把PL/SQL 程序存储在数据库中，并可以在任何地⽅来运⾏它。
这样就叫存储过程或函数。
过程和函数统称为PL/SQL⼦程序，他们是被命名的PL/SQL块，均存储在数据库中，并通过输⼊、输出参数或输⼊/输出参数与其调⽤者交换信息。
过程和函数的唯⼀区别是函数总向调⽤者返回数据，⽽过程则不返回数据常见的单词：pragma 编译指⽰instantiable 实例化overriding 覆盖重写static member 静态成员delimited 划定…的界限identifier 标识符reverse 反向2、存储函数的格式|存储函数格式|create or replace function func_name(dept_id number,salary number)return varchar2is-- 函数使⽤过程中，需要声明的变量，记录类型，cursorbegin--函数的执⾏体，如果有返回值需要returnreturn 'helloworld'|| v_logo;--commit;如果此处是进⾏insert、delete、update操作，可以通过提交进⾏更改，⽆需再调⽤存储函数后再进⾏提交exception--处理函数执⾏过程中的异常end;详解：1）因为函数需要返回⼀个值, 所以RETURN 包含返回结果的数据类型.2）函数名后⾯是⼀个可选的参数列表, 其中包含IN, OUT 或IN OUT 标记. 参数之间⽤逗号隔开IN 参数标记表⽰传递给函数的值在该函数执⾏中不改变;OUT 标记表⽰⼀个值在函数中进⾏计算并通过该参数传递给调⽤语句;IN OUT 标记表⽰传递给函数的值可以变化并传递给调⽤语句. 若省略标记, 则参数隐含为IN3）A、存储函数：有返回值，创建完成后，如何调⽤：⽅法⼀select function() from dual;⽅法⼆：set serveroutput on;var aaa varchar2(10);call hello_world() into :aaa;⽅法三：begindbms_output.put_line(hello_world());end;B、存储过程：由于没有返回值，创建完成后，不能使⽤select语句，只能使⽤pl/sql块执⾏|实例⼀：创建⼀个⽆参函数|create or replace function hello_worldreturn varchar2isbeginreturn 'hello world';end;如何调⽤：⽅法⼀select hello_world() from dual;⽅法⼆set serveroutput on;var aaa varchar2(10);call hello_world() into :aaa;⽅法三：begindbms_output.put_line(hello_world());end;⽅法四：declarev_bianling varchar2(40);beginv_bianling := hello_world;dbms_output.put_line(v_bianling);end;|实例⼆：创建⼀个有参函数（传进去的参数v_classid的值是不变的）|求⼀个班学⽣的总薪⽔create or replace function get_sal(v_classid number)return numberisv_sumsal number(10):=0;cursor sal_cursor is select sal from student where classid =v_classid; beginfor c in sal_cursor loopv_sumsal:=v_sumsal+c.sal;end loop;return v_sumsal;end;|实例三：创建⼀个有参函数（传进去的参数v_classid的值是不变的）|OUT型参数 对于实例⼆中的传进去的参数⼀般是不变的In是输⼊类型,⽽out是输⼊输出类型的如果⼀个形参⽤out修饰 那么它就会在函数中被赋值，并且可以当成⼀个数传出去（结合例⼦理解）create or replace function get_sal1(v_classid number,total_sal out number)return numberisv_sumsal number(10):=0;cursor sal_cursor is select sal from student where classid =v_classid;begintotal_sal:=0;for c in sal_cursor loopv_sumsal:=v_sumsal+c.sal;total_sal:=total_sal +1;end loop;return v_sumsal;end;如何调⽤：declarev_total_sal number(5);begindbms_output.put_line(get_sal1(1, v_total_sal));dbms_output.put_line(v_total_sal);end;⽆返回值 但是可以定义⼀个 out型参数把 值传出来的例⼦如下：create or replace function get_sal2(v_classid number,total_sal out number)return numberiscursor sal_cursor is select sal from student where classid =v_classid;begintotal_sal:=0;for c in sal_cursor looptotal_sal:= total_sal+c.sal;end loop;return total_sal;end;如何调⽤：declarev_classid number(5):=1;v_total_sal number(10):=0;begindbms_output.put_line(get_sal2(v_classid,v_total_sal));dbms_output.put_line(v_total_sal);end;3.plsqldep存储过程如何打断点进⾏调试1）edit pkg_regulatory_tax.;在相应的代码处添加断点、点击执⾏按钮进⾏编译2）test pkg_regulatory_tax.p_load_auto;输⼊传参信息，点击上⾯的执⾏按钮进⾏调试。
PL/SQL循序渐进全面学习教程（1）发布时间：2005.07.21 12:43来源：作者：prewind 课程一 PL/SQL 基本查询与排序本课重点：1、写SELECT语句进行数据库查询2、进行数学运算3、处理空值4、使用别名ALIASES5、连接列6、在SQL PLUS中编辑缓冲，修改SQL SCRIPTS7、ORDER BY进行排序输出。
8、使用WHERE 字段。
一、写SQL命令：不区分大小写。
SQL语句用数字分行，在SQL PLUS中被称为缓冲区。
最后以；或 / 结束语句。
也可以用RUN来执行语句二、举例例1：例2：对于数值或日期型的字段，可以进行相应的四则运算，优先级与标准的高级语言相同。
三、列的别名ALIASES：计算的时候特别有用；紧跟着列名，或在列名与别名之间加“AS”；如果别名中含有SPACE，特殊字符，或大小写，要用双引号引起。
例：四、连接符号：||连接不同的列或连接字符串使结果成为一个有意义的短语：五、管理NULL值：此函数使NULL转化为有意义的一个值，相当于替换NULL。
六、SQL PLUS的基本内容，请参考《SQL PLUS 简单实用精髓篇》七、ORDER BY 操作：与其他SQL92标准数据库相似，排序如：从Oracle7 release 7.0.16开始，ORDER BY 可以用别名。
另：通过位置判断排序：这样就避免了再写一次很长的表达式。
另：多列排序：八、限制选取行：例1：例2：例3：如果有列为NULL优先级：Order Evaluated Operator1 All comparison operators (=, <>, >, >=, <, <=, IN, LIKE, IS NULL, BETWEEN)2 AND3 OR总结：我们今天主要学习了如何进行查询SELECT操作，具体的组合查询与子查询将在以后的课堂中学习，同时希望大家可以工作、学习中多多摸索，实践!123456789101112下一页>>PL/SQL循序渐进全面学习教程（2）发布时间：2005.07.21 12:43来源：作者：prewind 课程二 PL/SQL查询行函数本课重点：1、掌握各种在PL/SQL中可用的ROW函数2、使用这些函数的基本概念3、SELECT语句中使用函数4、使用转换函数注意：以下实例中标点均为英文半角一、FUNCTION的作用：进行数据计算，修改独立的数据，处理一组记录的输出，不同日期显示格式，进行数据类型转换函数分为：单独函数(ROW)和分组函数注意：可以嵌套、可以在SELECT, WHERE, 和 ORDER BY中出现。
Oracle专题——PL/SQL调试一般方法在开发PL/SQL程序时，每个程序错误都有其特殊之处，这就使得程序的调试和测试技术面临挑战。
虽然在开发过程中可以借助于测试来减少程序错误的数量，但是，如果能有效的调试PL/SQL程序，可以较大幅度提高开发效率及质量。
以下为PL/SQL调试的一些方法：（1）捕捉违例通过两个内建的函数sqlcode 和sqlerrm 来找出发生了哪类错误并获得详细的错误信息，在内部违例发生时，sqlcode返回从-1至－20000之间的一个错误号，但有一个例外，仅当内部违例no_data_found 发生时，才会返回一个正数 100。
当用户自定义的违例发生时，sqlcode返回＋1，除非用户使用 pragma exception_init 将自定义违例绑定一个自定义的错误号。
当没有违例抛出时，sqlcode返回0。
下面是个简单的捕捉违例的例子：CREATE OR REPLACE PROCEDURE PROC_GETEXCEPTIONINFO(I_TMPVAR INNUMBER,O_RETCODE OUT VARCHAR2,O_ERRMSG OUT VARCHAR2) ISV_TMPVAR NUMBER;BEGINSELECT I_TMPVAR / 0 INTO V_TMPVAR FROM DUAL;EXCEPTIONWHEN OTHERS THENO_RETCODE := SQLCODE;O_ERRMSG := SQLERRM;END PROC_GETEXCEPTIONINFO;运行结果为：输入：I_TMPVAR 10输出：O_RETCODE -1476O_ERRMSG ORA-01476: 除数为 0（2）将结果打印到屏幕通过DBMS_OUTPUT包将PLSQL中的变量值打印到屏幕上，在程序运行过程中，可以跟踪变量的变化轨迹。
DECLAREBEGINDBMS_OUTPUT.PUT_LINE('Before loop......');FOR V_COUNTER IN 1 .. 5 LOOPDBMS_OUTPUT.PUT_LINE('Inside loop. counter=' || V_COUNTER);END LOOP;DBMS_OUTPUT.PUT_LINE('After loop.');END;运行结果为：Before loop......Inside loop. counter=1Inside loop. counter=2Inside loop. counter=3Inside loop. counter=4Inside loop. counter=5After loop.（3）使用调试表把变量的值插入到程序维持的临时表中，当该程序运行结束时，查询该临时表中的变量数据值。
ORACLEPLSQL命令大全ORACLE—PL/SQL命令大全第一章：日志管理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 4]sql> ('/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.log'sql> 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';/doc/b8157a83d4d8d15abe234e1f.ht ml ing 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:oracleoradblogora db.ora');f. sql> select * from v$logmnr_contents(v$logmnr_dictionary,v$logmnr_parameterssql> v$logmnr_logs);g. sql> execute dbms_logmnr.end_logmnr;第二章：表空间管理1.create tablespacessql> create tablespace ts_name datafile 'c:oracleoradatafile1.dbf' size 100m ,sql>'c:oracleoradatafile2.dbf' size 100m minimum extent550k [logging/nologging]sql> default storage (initial 500k next 500k maxextents 500 pctinccease 0)sql> [online/offline] [permanent/temporary] [extent_management_clause]2.locally managed tablespacesql> create tablespace user_data datafile 'c:oracleoradatauser_data01.dbf'sql> size 500m extent management local uniform size 10m;3.temporary tablespacesql> create temporary tablespace temp tempfile 'c:oracleoradatatemp01.dbf'sql> 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> alterdatabase 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.dbf' to 'c:oracleapp_data.dbf';第三章：表1.create a tablesql> create table table_name (column datatype,column datatype]....)sql> tablespace tablespace_name [pctfree integer] [pctused integer]sql> [initrans integer] [maxtrans integer]sql> storage(initial 200k next 200k pctincrease 0 maxextents 50)sql> [logging|nologging] [cache|nocache]2.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 500k 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 integer]9.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_tabs第四章：索引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 integer]sql> [logging | nologging] [nosort] storage(initial 200k next 200k pctincrease 0 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 200k 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 200k 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;第五章：约束1.define constraints as immediate or deferredsql> alter session set constraint[s] = immediate/deferred/default;set constraint[s] 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;第六章：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;/doc/b8157a83d4d8d15abe234e1f.ht ml ing sql*loadersql> sqlldr scott/tigersql> control = ulcase6.ctlsql> log = ulcase6.log direct=true第七章：reorganizing data/doc/b8157a83d4d8d15abe234e1f.ht ml ing expoty$exp scott/tiger tables(dept,emp) file=c:emp.dmp log=exp.log compress=n direct=y/doc/b8157a83d4d8d15abe234e1f.ht ml ing import$imp scott/tiger tables(dept,emp) file=emp.dmp log=imp.log ignore=y3.transporting a tablespacesql>alter 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 是，表示自包含第八章： managing password security and resources1.controlling account lock and passwordsql> alter user juncky identified by oracle account unlock;/doc/b8157a83d4d8d15abe234e1f.ht ml er_provided password functionsql> function_name(userid in varchar2(30),password in varchar2(30),old_password in varchar2(30)) return boolean3.create a profile : password settingsql> create profile grace_5 limit failed_login_attempts 3sql> password_lock_time unlimited password_life_time 30sql>password_reuse_time 30 password_verify_function verify_functionsql> password_grace_time 5;4.altering a profilesql> alter profile default limitsql> failed_login_attempts 3sql> password_life_time 60;5.drop a profilesql> drop profile grace_5 [cascade];6.create a profile : resource limitsql> create profile developer_prof limit sessions_per_user 2 sql> cpu_per_session 10000 idle_time 60 connect_time 480;7. view => resource_cost : alter resource costdba_Users,dba_profiles8. enable resource limitssql> alter system set resource_limit=true;第九章：Managing users1.create a user: database authenticationsql> create user juncky identified by oracle default tablespace users temporary tablespace temp quota 10m/unlimited on data password expire [account lock|unlock] [profile profilename|default];2.change user quota on tablespacesql> alter user juncky quota 0 on users;3.drop a usersql> drop user juncky [cascade];4. monitor userview: dba_users , dba_ts_quotas第十章：managing privileges1.system privileges: view => system_privilege_map ,dba_sys_privs,session_privs2.grant system privilegesql> 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,alterdatabase backup controlfile,alter tablespace begin/end backup,recover databasealter database archivelog,restricted sessionsysdba: sysoper privileges with admin option,create database,recover database until4.password file members: view:=> v$pwfile_users5.O7_dictionary_accessibility =true restriction access to view or tables in other schema6.revoke system privilegesql> revoke create table from karen;sql> revoke create session from scott;7.grant object privilegesql> grant execute on dbms_pipe to public;sql> grant update(first_name,salary) on employee to karen with grant option;8.display objectprivilege : view => dba_tab_privs, dba_col_privs9.revoke object privilegesql> revoke execute on dbms_pipe from scott [cascade constraints];10.audit record view :=> sys.aud$11. protecting the audit trailsql> audit delete on sys.aud$ by access;12.statement auditingsql> audit user;13.privilege auditingsql> audit select any table by summit by access;14.schema object auditingsql> audit lock on summit.employee by access whenever successful;15.view audit option : view=> all_def_audit_opts,dba_stmt_audit_opts,dba_priv_audit_opts,dba _obj_audit_opts16.view audit result: view=> dba_audit_trail,dba_audit_exists,dba_audit_object,dba_audit_ses sion,dba_audit_statement第十一章： manager role1.create rolessql> create role sales_clerk;sql> create role hr_clerk identified by bonus;sql> create role hr_manager identified externally;2.modify rolesql> alter role sales_clerk identified by commission;sql> alter role hr_clerk identified externally;sql> alter role hr_manager notidentified;3.assigning rolessql> grant sales_clerk to scott;sql> grant hr_clerk to hr_manager;sql> grant hr_manager to scott with admin option;4.establish default rolesql> 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 rolessql> 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 usersql> revoke sales_clerk from scott;sql> revoke hr_manager from public;7.remove rolesql> drop role hr_manager;8.display role informationview:=>dba_roles,dba_role_privs,role_role_privs,dba_sys_privs,role_sy s_privs,role_tab_privs,session_roles第十二章: BACKUP and RECOVERY1.v$sga,v$instance,v$process,v$bgprocess,v$database,v$datafile,v $sgastat2. Rman need set dbwr_IO_slaves or backup_tape_IO_slaves and large_pool_size3. Monitoring Parallel Rollback> v$fast_start_servers , v$fast_start_transactions4.perform a closed database backup (noarchivelog)> shutdown immediate> cp files /backup/> startup5.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 databaserecover 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;/doc/b8157a83d4d8d15abe234e1f.ht ml plete 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 online5.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;6.backup a control file> alter database backup controlfile to 'control1.bkp';> alter database backup controlfile to trace;7.recovery (noarchivelog mode)> shutdown abort> cp files> startup8.recovery of file in backup mode>alter database datafile 2 end backup;9.clearing redo log file>alter database clear unarchived logfile group 1;>alter database clear unarchived logfile group 1 unrecoverable datafile;10.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';。
