ORACLE中查看执行计划plan一：执行形式
通常我们在sql*plus中就可以执行了。在形式上，如果按照输出结果方式主要有两个不同，按照执行方式也有两个不同。
至于如何使用dbms_xplan包裹，不在此详述，我自己一般也不用。
1)执行方式1 －－ set autotrace traceonly..
sql>set serveroutput on
sql>set autotrace traceonly
完整格式是：
SET AUTOT[RACE] {ON | OFF | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
然后运行即可直接的查看结果，如例子（已经手工删除一些空白）：
SQL> select * from tab;
已选择442行。
执行计划
----------------------------------------------------------
Plan hash value: 457676135
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1066 | 90610 | 204 (4)| 00:00:03 |
| 1 | NESTED LOOPS OUTER | | 1066 | 90610 | 204 (4)| 00:00:03 |
|* 2 | TABLE ACCESS FULL | OBJ$ | 1066 | 83148 | 152 (5)| 00:00:02 |
| 3 | TABLE ACCESS CLUSTER| TAB$ | 1 | 7 | 1 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("O"."TYPE#"<=5 AND "O"."OWNER#"=USERENV('SCHEMAID') AND
"O"."TYPE#">=2 AND "O"."LINKNAME" IS NULL)
4 - access("O"."OBJ#"="T"."OBJ#"(+))
统计信息
----------------------------------------------------------
8 recursive calls
0 db block gets
1684 consistent gets
0 physical reads
0 redo size
11810 bytes sent via SQL*Net to client
704 bytes received via SQL*Net from client
31 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
442 rows processed
（注：墨绿色部分是EXPLAIN PLAN输出所不具有的)
该命令参考见<<sql * plus user's guide and reference release 10.2>> B14357-01.
如果想不起来，可以用sql> help set来查看可用SET命令。
2)EXPLAIN PLAIN FOR
直接执行EXPLAIN PLAN FOR SELECT * FROM TAB;
SQL> set autotrace off;
SQL> explain plan for select * from tab;
已解释。
SQL> select * from table(dbms_xplan.display);
具体结果略。
该命令必须参考<<Oracle Database SQL Reference 10g Release 2 (10.2) >>B14200-02
其它的可以参考视图：V$SQL_WORKAREA，V$SQL_PLAN，V$SQL_PLAN_STATISTICS，V$SQL_PLAN_STATISTICS_ALL。
可以参考的其它书籍是： Oracle Database Performance Tuning Guide （有关explain输出),Oracle Database Reference(前面提到的动态性能视图,那几个v$开头的）.
 3)两种方式的比较
a) 前面一种方式更加简单，而且只有一次设置，次次有效（SQL*PLUS环境下),输出的结果也更详细，缺点
是输出结果必须用spool才可以保存。
b)后面一种方式稍微麻烦一些，需要为每个独立的SQL执行，但优点是输出的结果可以存储到表格中，因为
dbms_xplan.display是一个管道表函数，输出的每一行都是varchar2类型。
综合而言，我还是更喜欢用set的方式。
它们的共同点在于，都需要用到表格plan_table ,有关plan_Table的脚本执行脚本ORACLE_HOME\RDBMS\ADMIN\UTLXPLAN.SQL
二：可用的参考书籍
1)sql * plus user's guide and reference release 10.2
2)Oracle Database SQL Reference 10g Release 2 (10.2)
3)Oracle Database Performance Tuning Guide
4)Oracle Database Reference

ORA-12518: TNS:listener could not hand off client connection
2008-06-23 08:57
ORA-12518: TNS:listener could not hand off client connection
Cause: The process of handing off a client connection to another process failed.
Action: Turn on listener tracing and re-execute the operation. Verify that the listener and database instance are properly configured for direct handoff. If problem persists, call Oracle Support.

先试试 connect / as sysdba,看行不行，如果行的话，
再试试connect / @ as sysdba看看监听配置是否有问题。
如果这一步有问题，说明监听这块有问题。

1.cmd
2.sqlpls /nolog
3.connect sys/test as sysdba
没有问题
1.cmd
2.sqlplus /nolog
3.connect sys/test@test as sysdba
ORA-12518: TNS:listener could not hand off client connection
说明监听有问题

查到了相关资料如下：
http://www.dba-oracle.com/sf_ora_12518_tns_listener_could_not_hand_off_client_connection.htm

The reason ORA-12518 is being throw may be because of DEDICATED connections because Oracle 10g has a value of PROCESSES is defaulted at 150 which can be lower than necessary in a production system. Also, in pre-9i MTS, ORA-12518 may be thrown in SHARED SERVER because the dispatcher may have reached the maximum connection value, so it denies all other.

There are two solutions for ORA-12518 depending on which symptom you may be experiencing.
a.For the DEDICATED occurrence of ORA-12518, you would need to try increasing the PROCESSES parameter so that it can handle the needed number of processes. You can ensure that you have the needed value by monitoring the listener log for ORA-12518. Also, note that because the PROCESSES parameter is static, the database will need to be bounced.
b.If you are experiencing ORA-12518 because of a shared server issue, you first would need to use the command below to shutdown the dispatcher:
SQL> alter system shutdown immediate 'D001';
Then, add on new dispatchers:
SQL> alter system set DISPATCHERS = '(protocol=tcp)(dispatchers=3)';

通过以上资料分析得出可能出现问题的原因有两种：一是processes值设的过小，二是需要增加参数DISPATCHERS。

考虑到之前经常遇到ora-00020超出最大进程数的问题在安装数据库的时候已经把processes设到800了，为保险起见再show parameter processes

SQL> show parameter process;

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----
aq_tm_processes                      integer     0
db_writer_processes                  integer     1
gcs_server_processes                 integer     0
job_queue_processes                  integer     10
log_archive_max_processes            integer     2
processes                            integer     1000

再查进程数

SQL> select count(*) from v$session;

  COUNT(*)
----------
       224

所以最后得出结论应该不是processes的问题，应该用b方法来解决
1.connect sys/test as sysdba
2.show parameters dispatchers;
NAME                TYPE             VALUE
dispatchers         string           (protocol=tcp)(service=oracle10xdb)
max_dispatchers      integer 
5.SQL>alter system set dispatchers = '(protocol=tcp)(dispatchers=3)(service=oracle10xdb)';
system altered
问题解决。
当然根据实际情况你也可以用a方法解决，

可以用show parameters processes;

命令查看processes的值，数据库在安装时默认为150，对于大型应用，32位的oracle一定要注意这个问题

sqlplus sys/system@server as sysdba;

archive log list;

shutdown immediate;

startup mount;

alter database noarchivelog;

alter database open;

archive log list;

--unable

　　发现ARCHIVELOG占近70％，BACKUPPIRCR占了30％，这样FLASH_RECOVERY_AREA空间的空间已经被完全占据了。

4、解决过程

　　根据数据库目前可用存储空间为200GB、FLASH_RECOVERY_AREA空间为2GB的实际情况，把FLASH_RECOVERY_AREA的空间修改为20GB。

一、主键和外键的关系
表A主键为name，表B外键为name。
在表A中为主键，在表B中为外键，那么A表是主表，B表是从表。
对表B的name字段进行修改或者添加记录，必须要参考表A的name列。假如插入jim值到表B的name字段，而表A的name列没有这个值，那么就会插入失败，这就是外键约束。如果表B的name列允许为空，那么可以插入空记录。因为可能这一行记录可以不关联表A的记录。
外键的值可以相同，因为从表有些行的记录可能都要关联父表某行的记录。

二、oracle远程连接
客户端
命令: sqlplus username/password@HOST:port/SERVICE_NAME [as sysdba]
用sysdba权限用户远程连就必须要加上as sysdba选项。

服务端
1.打开监听，命令为lsnrctl start.
2.查看$ORACLE_HOME/network/admin/下的sqlnet.ora和tnsname.ora
   [oracle@oracle admin]$ cat sqlnet.ora
# sqlnet.ora Network Configuration File: /xinying/oracle/app/oracle/product/11.1.0/db_1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
SQLNET.AUTHENTICATION_SERVICES = (ALL) #用户认证模式，操作系统认证和密码文件认证混合模式。

[oracle@oracle admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /xinying/oracle/app/oracle/product/11.1.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
#客户端的命令选项根据以下的参数来设置
ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.252)(PORT = 1521))#网络监听服务主机端口。
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)#网络服务名
    )
  )



2.设置初始化参数 REMOTE_LOGIN_PASSWORDFILE ：
    在 Oracle 数据库实例的初始化参数文件中，此参数控制着密码文件的使用及其状态。它可以有以下几个选项：

NONE ：指示 Oracle 系统不使用密码文件，特权用户的登录通过操作系统进行身份验证；

EXCLUSIVE ：指示只有一个数据库实例可以使用此密码文件。只有在此设置下的密码文件可以包含有除 INTERNAL ／ SYS 以外的用户信息，即允许将系统权限 SYSOPER ／ SYSDBA 授予除 INTERNAL ／ SYS 以外的其他用户。

SHARED ：指示可有多个数据库实例可以使用此密码文件。在此设置下只有 INTERNAL ／ SYS 帐号能被密码文件识别，即使文件中存有其他用户的信息，也不允许他们以 SYSOPER ／ SYSDBA 的权限登录。此设置为缺省值。

SQL> show parameter remote_login_passwordfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------
remote_login_passwordfile            string      EXCLUSIVE

3.创建密码文件，文件路径在$ORACLE_HOME/dbs/,文件名为orapwORACLE_SID.
  命令：[oracle@oracle admin]$ orapwd --help
Usage: orapwd file=<fname> password=<password> entries=<users> force=<y/n> ignorecase=<y/n> nosysdba=<y/n>

  where
    file - name of password file (required), 创建文件的路径
    password - password for SYS (optional),  密码
    entries - maximum number of distinct DBA (required),允许多少人远程访问
    force - whether to overwrite existing file (optional), 覆盖当前的密码文件
    ignorecase - passwords are case-insensitive (optional),
    nosysdba - whether to shut out the SYSDBA logon (optional Database Vault only).

  There must be no spaces around the equal-to (=) character.

orapwd file="/xinying/oracle/app/oracle/product/11.1.0/db_1/dbs/orapwORACLE_SID" password=test entries=10 force=y

4.查看sysdba权限远程连接的用户
SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS                            TRUE  TRUE  FALSE


三、修改oracle本地监听端口
编辑$ORACLE_HOME/network/admin/下的listener.ora。

　oracle的同义词总结：

　　从字面上理解就是别名的意思，和试图的功能类似。就是一种映射关系。

　　1.创建同义词语句：

　　create public synonym table_name for user.table_name;

　　其中第一个user_table和第二个user_table可以不一样。

　　此外如果要创建一个远程的数据库上的某张表的同义词，需要先创建一个Database Link(数据库连接)来扩展访问，然后在使用如下语句创建数据库同义词：create synonym table_name for table_name@DB_Link;

　　当然，你可能需要在user用户中给当前用户(user2)授权： grant select/delete/update on user2

　　2.删除同义词：

　　drop public synonym table_name;

　　3.查看所有同义词：

　　select * from dba_synonyms

　　同义词拥有如下好处：节省大量的数据库空间，对不同用户的操作同一张表没有多少差别;扩展的数据库的使用范围，能够在不同的数据库用户之间实现无缝交互;同义词可以创建在不同一个数据库服务器上，通过网络实现连接。

经常使用oracle10g，我们可以发现以前删除的表在数据库中出现了特别多的垃圾表，如下例：

BINjR8PK5HhrrgMK8KmgQ9nw==

这一类的表通常无法删除，并且无法用"delete"删除,这种情况的出现，

一般不会影响正常的使用，但是有遇到以下几种情况时则必须删掉它。

◆1.这些表占用空间

◆2.如果使用Middlegen-Hibernate-r5的朋友会发现一些问题，不你象以前用oracle9时那么顺的生成hibernate配置文件,原因就在这里将它删除就没有问题了.

BINjR8PK5HhrrgMK8KmgQ9nw==

这一类的表是是10g 的新特性。

Drop Table 后，没有真正的删除表，而是在“垃圾站”中了。可以通过下面看到。

SQL> SHOW RECYCLEBIN

如果要彻底删除，可以使用：

SQL> PURGE TABLE "BINjR8PK5HhrrgMK8KmgQ9nw==";

删除这一个或删除全部。

SQL> PURGE RECYCLEBIN;

i setup a vista environment with oracle 10gr2 client, it made me terribly defeated a whole day. in the end i understood that install oracle 10g2 client under Vista environment must be close vista UAC, than installed by default mode.

and the following is another ways to install seccessfully:

首先、确保你有该文件夹的完全控制权。（修改：文件夹点右键 属性-〉安全-〉高级-〉所有者-〉改为自己。接着编辑自己的权限为完全控制）
其次、将setup.exe的兼容性改为windows server 2003(server pack 1)。就是右键属性-〉兼容性-〉兼容模式中。
接着、右键，以管理员身份运行。
最后、会出现兼容性问题的提示框。选择运行程序。

ORA-28547的解决之道

ORA-12514: TNS: 监听程序当前无法识别连接描述符中请求的服务　问题解决
 

    近日在配置Oracle 10G的流复制环境时，遇到一个问题，关闭数据库（shutdown immediate）后，通过SQL Plus连接数据库:conn sys/his@orc0 as sysdba，出现如下错误：

    ORA-12514: TNS: 监听程序当前无法识别连接描述符中请求的服务

通过重启服务的方式启动数据库，再次连接却能成功登录，也就是说在关闭数据库状态下无法连接服务器。

    开始以为是系统环境变量Oracle_SID的配置问题，因为机器有多个实例，一阵折腾后还是不能连接。后来查资料得知：

    Oracle9i以后，后台进程PMON自动在监听器中注册在系统参数SERVICE_NAMES中定义的服务名，SERVICE_NAMES默认为DB_NAME+DOMAIN_NAME。监听配置文件listener.ora中可以不必指定监听的服务名。但是，当数据库处于关闭状态下PMON进程没有启动，也就不会自动注册监听的实例名，所以使用sqlplus sys/his@orc0 as sysdba 会出现ORA-12514错误。

如果在listener.ora文件中指定监听的实例名，则即使数据库处于关闭状态，仍然可以连接。

listener.ora

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (SID_NAME = PLSExtProc)

      (ORACLE_HOME = G:\oracle\product\10.2.0\db_1)

      (PROGRAM = extproc)

    )

   (SID_DESC =

    (GLOBAL_DBNAME = ORCL)

    (ORACLE_HOME = G:\oracle\product\10.2.0\db_1)  

    (SID_NAME = ORCL)

    )

  )

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = zyk)(PORT = 1521))

    )

  )

以上粗体部件为增加的内容，修改后重启监听服务后即可。

附：通过在lsnrctl中输入set display verbose，然后再通过命令service查看，服务状态为READY表示PMON自动注册的服务名，而UNKNOWN则表示该服务是手工在LISTENER.ORA中配置的数据库服务。

第一章：日志管理

1.forcing log switches
sql> alter system switch logfile;

2.forcing checkpoints
sql> alter system checkpoint;

3.adding online redo log groups
sql> alter database add logfile [group 4]
sql> ('/disk3/log4a.rdo','/disk4/log4b.rdo') size 1m;

4.adding online redo log members
sql> alter database add logfile member
sql> '/disk3/log1b.rdo' to group 1,
sql> '/disk4/log2b.rdo' to group 2;

5.changes the name of the online redo logfile
sql> alter database rename file 'c:/oracle/oradata/oradb/redo01.log'
sql> to 'c:/oracle/oradata/redo01.log';

6.drop online redo log groups
sql> alter database drop logfile group 3;

7.drop online redo log members
sql> alter database drop logfile member 'c:/oracle/oradata/redo01.log';

8.clearing online redo log files
sql> alter database clear [unarchived] logfile 'c:/oracle/log2a.rdo';

9.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;

第二章：表空间管理
1.create tablespaces
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';

第三章：表

1.create a table
sql> 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 table
sql> create table table_name [logging|nologging] as subquery

3.create temporary table
sql> create global temporary table xay_temp as select * from xay;
on commit preserve rows/on commit delete rows

4.pctfree = (average row size - initial row size) *100 /average row size
pctused = 100-pctfree- (average row size*100/available data space)

5.change storage and block utilization parameter
sql> alter table table_name pctfree=30 pctused=50 storage(next 500k
sql> minextents 2 maxextents 100);

6.manually allocating extents
sql> alter table table_name allocate extent(size 500k datafile 'c:/oracle/data.dbf');

7.move tablespace
sql> alter table employee move tablespace users;

8.deallocate of unused space
sql> alter table table_name deallocate unused [keep integer]

9.truncate a table
sql> truncate table table_name;

10.drop a table
sql> drop table table_name [cascade constraints];

11.drop a column
sql> alter table table_name drop column comments cascade constraints checkpoint 1000;
alter table table_name drop columns continue;

12.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

第四章：索引

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;

第五章：约束

1.define constraints as immediate or deferred
sql> alter session set constraint[s] = immediate/deferred/default;
set constraint[s] constraint_name/all immediate/deferred;

2. sql> drop table table_name cascade constraints
sql> drop tablespace tablespace_name including contents cascade constraints

3. define constraints while create a table
sql> create table xay(id number(7) constraint xay_id primary key deferrable
sql> using index storage(initial 100k next 100k) tablespace indx);
primary key/unique/references table(column)/check

4.enable constraints
sql> alter table xay enable novalidate constraint xay_id;

5.enable constraints
sql> alter table xay enable validate constraint xay_id;

第六章：LOAD数据

1.loading data using direct_load insert
sql> insert /*+append */ into emp nologging
sql> select * from emp_old;

2.parallel direct-load insert
sql> alter session enable parallel dml;
sql> insert /*+parallel(emp,2) */ into emp nologging
sql> select * from emp_old;

3.using sql*loader
sql> sqlldr scott/tiger \
sql> control = ulcase6.ctl \
sql> log = ulcase6.log direct=true

第七章：reorganizing data

1.using expoty
$exp scott/tiger tables(dept,emp) file=c:\emp.dmp log=exp.log compress=n direct=y

2.using import
$imp scott/tiger tables(dept,emp) file=emp.dmp log=imp.log ignore=y

3.transporting a tablespace
sql>alter tablespace sales_ts read only;
$exp sys/.. file=xay.dmp transport_tablespace=y tablespace=sales_ts
triggers=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 set
sql> DBMS_tts.transport_set_check(ts_list =>'sales_ts' ..,incl_constraints=>true);
在表transport_set_violations 中查看
sql> dbms_tts.isselfcontained 为true 是， 表示自包含

第八章： 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

3.create a profile : password setting
sql> create profile grace_5 limit failed_login_attempts 3
sql> password_lock_time unlimited password_life_time 30
sql>password_reuse_time 30 password_verify_function verify_function
sql> password_grace_time 5;

4.altering a profile
sql> alter profile default failed_login_attempts 3
sql> password_life_time 60 password_grace_time 10;

5.drop a profile
sql> drop profile grace_5 [cascade];

6.create a profile : resource limit
sql> 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 cost
dba_Users,dba_profiles

8. enable resource limits
sql> alter system set resource_limit=true;

第九章：Managing users

1.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];

2.change user quota on tablespace
sql> alter user juncky quota 0 on users;

3.drop a user
sql> drop user juncky [cascade];

4. monitor user
view: dba_users , dba_ts_quotas

第十章：managing privileges

1.system privileges: view => system_privilege_map ,dba_sys_privs,session_privs

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;

8.display object privilege : view => dba_tab_privs, dba_col_privs

9.revoke object privilege
sql> revoke execute on dbms_pipe from scott [cascade constraints];

10.audit record view :=> sys.aud$

11. protecting the audit trail
sql> audit delete on sys.aud$ by access;

12.statement auditing
sql> audit user;

13.privilege auditing
sql> audit select any table by summit by access;

14.schema object auditing
sql> 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_opts

16.view audit result: view=> dba_audit_trail,dba_audit_exists,dba_audit_object,dba_audit_session,dba_audit_statement

第十一章： manager role

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

第十二章: BACKUP and RECOVERY

1. v$sga,v$instance,v$process,v$bgprocess,v$database,v$datafile,v$sgastat

2. Rman need set dbwr_IO_slaves or backup_tape_IO_slaves and large_pool_size

3. Monitoring Parallel Rollback
> v$fast_start_servers , v$fast_start_transactions

4.perform. a closed database backup (noarchivelog)
> shutdown immediate
> cp files /backup/
> startup

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
5.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
> startup
8.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';