Oracle学习基本知识点总结

以下是我总结的ocp教程的知识点，以备参考之用！

 

1, what’s oracle server?

· it’s a database management system that provides an open, comprehensive, integrated approach to information management.

· consists of an oracle instance and an oracle database

2,what is oracle flashback technology?

· oracle flashback query.

· oracle flashback versions query.

· oracle flashback transaction query.

· oracle flashback table.

· oracle flashback drop.

· oracle flashback database.

· oracle flashback restore points.

3,oracle enterprise manager grid control (oemgc) is a html-based user interface supporting the administrator. includes:

· oracle management service (oms).

· oracle management agents.

· oracle management repository.

4,oracle database 10g with rac(real application cluster)

· multiple oracle instances running on multiple computer

· oracle clusterware

· shared-everything database

5,oracle database 10g with data guard

· production (primary) database

· standby database: a copy of production database

· physical standby database

· logical standby database

· switch role of databases in case of failure

6, the difference between physical and logical standby database.

· datafiles

· oracle redo logs

· physical standby database:directly applies redo to its datafiles

· logical standby database: convert dml and ddl operations to sql statements

7, overview of oracle primary components

 

8,oracle instance

· is a means to access an oracle database

· always opens one and only one database

· consists of memory(sga) and background process

9,connect to an oracle instance

· establishing a user connection

· creating a session (through server process)

10,oracle database

physical structure:

· mainly: consists of three file types: data files, redo log files, control files

· others: parameter file, password file, archived log files

memory structure:

oracle’s memory structure consists of two memory areas known as：

· system global area (sga) ：allocated at instance startup, and is a fundamental component of oracle instance

· program global area (pga) ：allocated when the server process is started

11,sga:system global area

the sga consists of serveral memory structures：

· shared pool

· database buffer cache

· redo log buffer

· other structures（for example, lock and latch management, statistical data）

there are two additional memory structures that can be configured within the sga：

· large pool

· java pool

· stream pool (10g)

when the size of sga is less than 128 ,the granule is 4m, otherwise the granule is 16m. sga_max_size controls the size of sga.

12,shared pool

used to store：

· most recently executed sql statements

· most recently used data definitions

it consists of two key performance-related memories: library cache and data dictionary cache sized by the parameter: shared_pool_size

how to change the size: alter system set shared_pool_size = 64m;

13,library cache(库高速缓存 )

? stores information about the most recently used sql and plsql statements

? enables the sharing of commonly used statements

? is managed by a least recently used (lru) algorithm

? consists of two structures：

– shared sql area

– shared pl/sql area

? size determined by the shared pool sizing

14,data dictionary cache(数据字典高速缓存 )

? a collection of most recently used definitions in database

? includes information about database files, tables,indexes, columns, users, privileges, and other database objects

? during the parse phase, the server process looks at the data dictionary for information to resolve object names and validate access

? caching data dictionary information into memory improves response time on queries and dml

? size determined by the shared pool sizing

15,database buffer cache(缓冲区高速缓存 )

? stores copies of data blocks that have been retrieved from the data files

? enables great performance gains when you obtain and update data

? managed through an lru algorithm

? db_block_size determines primary block size

如果未在数据库缓冲区高速缓存中找到这个块，服务器进程就从数据文件读取这个块，并在数据库缓冲区高速缓存中放置一个副本.

if the data blocks are not found in the database buffer cache, the server processes are responsible to read the needed blocks from the data files, and then save a copy of them in the database buffer cache for the future used.

缓冲区的dirty data size由参数 fast_start_mttr_target 决定的（如果已指定）。缺省值为零。

?consists of independent subcaches：

–db_cache_size

–db_keep_cache_size

–db_recycle_cache_size

?can be dynamically resized

?alter system set db_cache_size = 96m;

?db_cache_advice set to gather statistics for predicting different cache size behavior

?statistics displayed by v$db_cache_advice

–db_cache_size：只调整缺省缓冲区高速缓存的大小，这个参数始终存在且不能设置为零 ?????

–db_keep_cache_size：调整保留缓冲区高速缓存的大小，用于保留内存中很可能会重新使用的块

–db_recycle_cache_size：调整循环缓冲区高速缓存的大小，用于删除内存中重新使用的可能性很小的块

16,redo log buffer

· records all changes made to the database data blocks

· primary purpose is recovery

· changes recorded within are called redo entries

· redo entries contain information to reconstruct or redo changes

· sized defined by log_buffer

17,large pool

· an optional area of memory in the sga

· relieves the burden placed on the shared pool

· used for：

session memory (uga) for the shared server

i/o server processes

backup and restore operation or rman

parallel execution message buffers

parallel_automatic_tuning设置为 true

· does not use an lru list

· sized by large_pool_size

· can be dynamically resized

· 备份和恢复：

如果设置了 backup_disk_io= n和 backup_tape_io_slave = true 参数，则恢复管理器 (rman)将使用大型共享池。如果大型共享池已经配置，但不够大，则在大型共享池中分配内存就会失败。rman将错误消息写入警报日志文件，而且不使用 i/o 操作进行备份或恢复操作。

· 并行执行：

如果将 parallel_automatic_tuning设置为 true，将使用大型共享池。否则，将把这些缓冲区分配至共享池。

18,java pool

· an optional area of memory in the sga

· services parsing requirements for java commands

· requied if installing and using java

· sized by java_pool_size parameter

19,pga: program global area

· memory reserved for each user process connecting to an oracle database

· allocated when a process is created

· deallocated when the process is terminated

· used by only one process

20,process structure

oracle takes advantage of various types of processes：

· user process: started at the time a database user requests connection to the oracle server

· server process: connects to the oracle instance and is started when user establishes a session

· background processes: started when an oracle instance is started

21,user process

· a program that requests interaction with the oracle server

· must first establish a connection

· does not interact directly with oracle server

22, server process (服务器进程)

· a program that directly interacts with the oracle server.

· fulfill(执行) calls generated and returns results

· can be dedicated server or shared server

· 用户建立连接后，服务器进程便会启动，以处理用户进程的请求。服务器进程可以是专用服务器进程或共享服务器进程。在专用服务器环境中，服务器进程只处理一个用户进程的请求。用户进程断开连接后，服务器进程就会终止。在共享服务器环境中，服务器进程将处理多个用户进程的请求。服务器进程可以通过 oracle程序接口 (opi) 与 oracle服务器进行通信。

23,background process

· it maintains and enforces relationships between physical and memory structures:

· mandatory background processes：dbwn(0-9,a-j) , pmon,ckpt,lgwr,smon

optional background processes： arcn,lmdn, reco,cjq0,lmon,snnn,dnnn,pnnn,

lckn,qmnn

reco：恢复程序

qmnn：高级排队

arcn：归档程序

lckn： rac锁管理器 – 例程锁

lmon：rac dlm监控程序 – 全局锁

lmdn：rac dlm监控程序 – 远程锁

cjq0：协调程序作业队列后台进程

dnnn：调度程序

snnn：共享服务器

pnnn：并行查询从属

24,database writer (dbwn)

it’s responsible for writing the dirty data from database buffer cache back to disk data files.writes when：

· checkpoint occurs

· dirty buffers reach threshold

· there are no free buffers

· timeout occurs

· tablespace offline

· tablespace read only

· table drop or truncate

· tablespace begin backup

· rac/ops ping request is made

25,log write (lgwr)

it’s responsible for writing the entry data from redo log buffer to online redo log files.

lgwr writes：

· at commit

· when one-third full

· when there is 1 mb of redo

· every three seconds

· before dbwn writes

26,system monitor (smon)

responsibilities：

· instance recovery:

1,rolls forward changes in online redo log files

2,opens database for user access

3,rolls back uncommited transactions(data in undo tbs?)

· coalesces free space

· deallocates temporary segments

27,process monitor (pmon)(过程监视器)

cleans up after failed processes by：

· rolling back the transaction

· releasing locks

· releasing other resource

· restarting dead dispatchers

进程失败后，后台进程 pmon通过下面的方法进行清理：

回退用户的当前事务处理

释放当前保留的所有表锁或行锁

释放用户当前保留的其它资源

重新启动已失效的调度程序

28,checkpoint (ckpt)

responsible for：

· signaling dbwn at checkpoint

· updating datafile headers with checkpoint information

· updating control files with checkpoint information

· 由 ckpt写入的检查点信息包括检查点位置、更改号、重做日志中恢复操作的起始位置以及有关日志的信息等等。

· 注：ckpt并不将数据块写入磁盘，或将重做块写入联机重做日志。

· 每隔三秒，ckpt进程就会向控制文件存储数据，以标识重做日志文件中恢复操作的起始位置，该操作称作检查点.

29,archiver (arcn)

· optional background process

· automatically archives online redo log files when archivelog mode is set

· preserves the record of all changes made to the database

30, oracle database logical structure

tablespace <------segment <----extent <---block

表空间：

· oracle数据库内的数据存储在表空间内。

· oracle数据库可以从逻辑上分组到称为表空间的更小的逻辑空间区。

· 一个表空间在某一时刻只能属于一个数据库。

· 每个表空间由一个或多个称为数据文件的操作系统文件组成。

· 表空间可能包含一个或多个段。

· 表空间可以在数据库运行时联机。

· 除了 system表空间或者有活动还原段的表空间，可将其它表空间置于脱机状态而不会影响数据库运行。

· 表空间可以在可读写和只读状态之间切换。

数据文件（不是逻辑结构）：

· oracle数据库内的每个表空间由一个或者多个称为数据文件的文件组成。这些物理结构与在其上运行 oracle服务器的操作系统是一致的。

· 一个数据文件只能属于一个表空间。

· 通过分配指定数量的磁盘空间加上少量的开销，oracle服务器创建表空间数据文件。

· 数据文件创建后，数据库管理员可以更改其大小或者指定数据文件应随着表空间内对象的增长而动态增长。

段：

· 段是在表空间中为特定逻辑存储结构分配的空间。

· 表空间可以由一个或多个段组成。

· 段无法跨越表空间；但是段可以跨越属于同一表空间的多个数据文件。

· 每个段由一个或多个区组成。

区：

· 按区向段分配空间。

· 一个或多个区组成一个段。

· 当段创建后，它至少由一个区组成。

· 随着段的增长，需要向该段添加区。

· dba可以手动向段添加区。

· 一个区就是一组连续的 oracle块。

· 区无法跨越数据文件，所以，它必须存在于一个数据文件内。

数据块：

oracle 服务器以 oracle 块或者数据块为单位来管理数据文件中的存储空间。

· oracle数据库内的数据存储在数据块内，数据块为最精细的粒度等级。

· oracle数据块是 oracle 服务器能够分配、读或写的最小存储单元。

· 一个数据块对应一个或多个从现有数据文件中分配的操作系统块。

· 每个 oracle数据库的标准数据块大小是在创建数据库时由初始化参数 db_block_size 指定的。

· 数据块大小应当是操作系统块大小的整数倍以避免不必要的 i/o。

· 数据块大小最大值取决于操作系统。

31,processing sql statements(处理 sql语句 )

处理查询：

? 语法分析：

– 搜索同一语句

– 检查语法、对象名和权限

– 锁定语法分析过程中使用的对象

– 创建和存储执行计划

? 绑定：获取变量值

? 执行：处理语句

? 提取：将结果行返回用户进程

处理 dml 语句：

· 语法分析：与处理查询时的语法分析阶段相同。

· 绑定：与处理查询时的绑定阶段相同。

· 执行：

· 如果数据库缓冲区高速缓存中不存在某些数据块和还原块，服务器进程就从数据文件将它们读入数据库缓冲区高速缓存。

· 服务器进程锁定要进行修改的行。还原块用于存储数据的前像，以便在需要时回退 dml语句。

· 数据块记录数据的新值。

· 服务器进程将数据的前像记录到回退块中，并更新数据块。这两种更改都 是在数据库缓冲区高速缓存中进行的。数据库缓冲区高速缓存中所有已更改的块都标记为灰数据缓冲区，即与磁盘中相应的块不同的缓冲区。

· delete或 insert 命令的处理使用类似的步骤。delete命令的前像包含已
删除行中的列值，而 insert命令的前像中包含行的位置信息。

处理 ddl 语句：

ddl（数据定义语言）语句的执行与 dml（数据操纵语言）语句和查询的执行不尽相同，因为成功执行 ddl语句需要对数据字典具有写权限。对于这些语句，语法分析阶段实际上包括分析、数据字典查找和执行。事务处理管理 sql语句、会话管理 sql 语句和系统管理 sql语句在语法分析和执行阶段处理。要重新执行这些语句，再次进入执行阶段即可。

32，oracle enterprise manager — architecture

 

oracle enterprise manager体系结构

oracle enterprise manager使用三层体系结构，其中包括：

第一层：控制台客户机和集成工具，为管理员提供图形界面。

第二层：oracle management servers和数据库资料档案库，为处理系统管理任务提供可伸缩的中层结构。

第三层：安装在每个节点上的智能代理，监视 oracle enterprise manager服务并执行 management server 上的任务。

33，the sequence of oracle database

spfilesid.ora ---control file---data files

nomount---mount---open

34,pfile - initsid.ora

· text file

· modified with an operating system editor

· modifications made manually

· changes take effect on the next start up

· only opened during instance start up

· default locations is $oracle_home/dbs

35,pfile format

· 以这样的格式指定值：keyword=value（关键字 =值）。

· 服务器为每个参数都设置了缺省值。根据参数的不同，缺省值可能与操作系统相关。

· 可以按任意顺序指定参数，但也存在例外。

· 注释行以 #符号开头。

· 参数中如果包括字符文字，可将参数用双引号括起。

· 可以使用关键字 ifile使参数中包括其它文件。

· 如果使用的操作系统区分大小写，那么文件名也区分大小写。

· 如果有多个值，应该用圆括号将它们括起来，用逗号隔开。

注：请为参数的列出顺序指定一个标准：按字母顺序列出或按功能进行分组。pfile根据例程的不同而变化，不一定与上例相同。

36,spfile(spfilesid.ora)

· binary file

· maintained by the oracle server

· always resides on the server side

· ability to make changes persistent across shutdown and startup

· can self-tune parameter values

· can have recovery manager support backing up to the initialization parameter file

37,startup database phase

nomount :

· 仅在创建数据库或重新创建控制文件过程中，例程才会在 nomount阶段启动。

· 启动例程包括下列任务：

· 按以下顺序从 $oracle_home/dbs读取初始化文件：

· 首先读取 spfilesid.ora

· 如果找不到，则读取 spfile.ora

· 如果仍然找不到，则读取 initsid.ora

· 使用 startup指定 pfile 参数以覆盖缺省行为。

· 分配 sga

· 启动后台进程

· 打开 alertsid.log文件和跟踪文件

mount:

若要执行特定的维护操作，可启动例程并加载数据库，但不要打开数据库。

例如，在以下任务中必须加载数据库但不要打开数据库：

· 重命名数据文件

· 启用和禁用重做日志归档选项

· 执行完全数据库恢复

加载数据库包括以下任务：

· 使数据库与以前启动的例程关联

· 定位并打开参数文件中指定的控制文件

· 读取控制文件以获取数据文件和重做日志文件的名称和状态。但是，在此时
不进行数据文件和联机重做日志文件是否存在的检查。

open:

正常的数据库操作指启动例程、加载数据库和打开数据库。通过正常的数据库操作，任何有效用户都可以连接到数据库并执行一般的数据访问操作。

打开数据库包括以下任务：

打开联机数据文件

打开联机重做日志文件

如果在尝试打开数据库时有任何数据文件或联机重做日志文件不存在，oracle服务器将返回错误消息。

在这个最后阶段中，oracle服务器验证所有数据文件和联机重做日志文件是否可以打开，并检查数据库的一致性。如果需要，系统监视 (smon)后台进程将启动例程恢复操作。

38,shutdown

abort immediate transactional normal

 

39,diagnostic files

· alert_sid.log file

background trace files[sid_processname_pid.trc (db01_lgwr_23845.trc)]

· user trace files

alert_sid.log的存储位置由 background_dump_dest 初始化参数定义。

40,user trace files

· user trace files

produced by the user process

can be generated by a server process

contain statistics for traced sql statements

contain user error messages

· created when a user encounters user sessions errors

· location is defined by user_dump_dest

· size defined by max_dump_file_size

· 用户跟踪文件包含跟踪的 sql语句的统计信息，这对于sql 优化非常有用。此外，用户跟踪文件还包含用户错误消息。

· 用户跟踪文件的命名约定：sid_ora_pid.trc(db01_ora_23845.trc)。

· 其存储位置由 user_dump_dest初始化参数定义。

41, enable/disable user tracing

session level：

using the alter session command：

alter session set sql_trace = true

executing dbms procedure:

dbms_system.set_sql_trace_in_session

instance level:

setting the initialization parameter: sql_trace = true

注：在例程级别设置 sql_trace=true后将生成大量跟踪数据。因此，要谨慎使用此选项。

sql> show parameter sql_trace

name type value

------------------------------------ ----------- ------------------------------

sql_trace boolean false

42,authentication methods

os: sqlplus / as sysdba

password parameter file is used for remote log on database.

cd $oracle_home/dbs

orapwd file=orapwsid password=xxxxxx entries=5 force=y;

sqlplus sys/oracle@test as sysdba (need to start listener)

43,password file authentication

· create the password file using the password utility.

· orapwd file=orapwsid password=xxxxxx entries=5 force=y;

· setremote_login_passwordfile=exclusive in initialization parameter file

· add users to the password file

· assign appropriate privileges to each user.

· grant sysdba to hr;

其中entries的值表示，可以有多少个用户可以拥有sysdba or sysoper权限，数据不一定准确的。sql> select * from v$pwfile_users;通过它可以查看。

给用户授与sysdba权限的过程就是把密码从数据字典复制到了密码文件中的过程。

44，install oracle must pre-specify os environment

oracle_base eg:/u01/app/oracle

oracle_home eg:$oracle_base/product/release(10.2.0)

oracle_sid

path 定义操作系统查找可执行程序时要搜索的目录

ld_library_path 定义所需的库文件的存储目录

45,creating a db manually

· choose a unique instance and database name.

· choose a database character set

· set operating system variables

· create the initialization parameter file

· create server parameter file

· start the instance in nomount stage

· create and execute create database command

· open database (create database do it automatically)

· run scripts to generate the data dictionary and accomplish post-creation steps

· create addional tablespaces as need

46,choose a database administrator authentication method

you must be authenticated and granted appropriate system privileges in order to create a database. you can authenticate as an administrator with the required privileges in the following ways:

a,with a password file

b,with operating system authentication

in this step, you decide on an authentication method.if you decide to authenticate with a password file, create the password file as described in "creating and maintaining a password file" on page 1-21.if you decide to authenticate with operating system user group. on the unix and linux plateforms for example this is typically thedba user group. on the windows platform the user installing the oracle software is automatically placed in the required user group.

47,4:create the initialization parameter file

shared_pool_size=80m

sga_max_size=280m

启动的时候shared_pool_size需要设置的大一点一般要有80mb，可以吧sga_target设置到280mb，不然直接跑 create database prod 会导致

tue aug 16 01:54:01 2005

errors in file d:\oracle\product\10.2.0\db_1\rdbms\trace\prod_ora_1408.trc:

ora-01501: create database ??

ora-01519: ????? '%oracle_home%\rdbms\admin\sql.bsq' ???? 1413 ???

ora-00604: ?? sql ?? 1 ????

ora-04031: ???? 2208 ??????? ("shared pool","unknown object","kgls heap","kgls mem block")

导致sql.bsq无法顺利建立完毕，也就是数据字典表没办法建立完毕，最终导致连control file 都mount不上

48,create a server parameter file

sql>create spfile from pfile;

tip: the database must be restarted before the server parameter file takes effect.

49,data dictionary

· central to every oracle database

· describe the database and its objects

· contains read-only tables and views

· stored in the system tablespace

· owned by the user sys

· maintained by the oracle server

· accessed with select

只要执行数据定义语言 (ddl)命令，oracle服务器就会更新数据字典。

the data dictionary contains two parts:

base tables :

a,stores description of the database

b,created with create database

data dictionary views:

a,used to simplify the base table information

b,accessed through public synonyms

c,created with the catalog.sql script

50,create data dictionary

location: unix/linux：$oracle_home/rdbms/admin

51,data dictionary contents

the data dictionary provides information about:

· logical and physical database structures

· definitions and space allocations of objects

· integrity constraints

· users

· roles

· privileges

· auditing

52,how the dd is used

primary uses:

· oracle server uses it to find information about

a,users

b,schema objects

c,storage structures

· oracle server modifies it when a ddl statement is executed.

· users and dbas use it as a read-only reference for information about the database.

53,dd(data dictionary) view categories

three sets of static views

distinguished by their scope:

· dba:what is in all the schemas

· all:what the user can access

· user:what is in the user's schema

 

54,dynamic performance tables

· virtual tables

· record current database activity

· continually updated while the database is operational

· information is accessed from memory and control file, spfile

· used to monitor and tune the database

· owned by sys user

· synonyms begin with v$

· listed in v$fixed_table

sql> select * from v$fixed_table where name='v$instance';

55,admin script naming

 

56,control file

· a small binary file

· defines current state of physical database

· maintains integrity of database

· required:

a,at mount state during database startup

b,to operate the database

· linked to a single database

· size initially by create database

· loss may require recovery

57,the size of control file

the main determinants of the size of a control file are the values set for the maxdatafiles, maxlogfiles, maxlogmembers, maxloghistory, and maxinstances parameters in the create database statement that created the associated database. increasing the values of these parameters increases the size of a control file of the associated database.

58,control file contents

a control file contains the following entries:

· database name and identifier

· time stamp of database creation

· tablespace names

· names and locations of data files and online redo log files

· current online redo log file sequence number

· checkpoint information

· begin and end of undo segments

· redo log archive information

· backup information

59, tom says about control files

thecontrol file is a fairly small file (it can grow up to 64mb or so in extreme cases) that contains a directory of the other files oracle needs. the parameter file tells the instance where the control files are, and the control files tell the instance where the database and online redo log files are.

the control files also tell oracle other things, such as information about checkpoints that

have taken place, the name of the database (which should match the db_name parameter), the timestamp of the database as it was created, an archive redo log history (this can make a control file large in some cases), rman information, and so on.

control files should be multiplexed either by hardware (raid) or by oracle when raid or

mirroring is not available. more than one copy of them should exist, and they should be stored on separate disks, to avoid losing them in the event you have a disk failure. it is not fatal to lose your control files—it just makes recovery that much harder.

control files are something a developer will probably never have to actually deal with.

to a dba they are an important part of the database, but to a software developer they are not

extremely relevant.

60,multiplexing the control file

1, place on different disks, can up to eight copies.

61, the behavior of mutiplexed control files is:

· write to all filenames listed by control_files parameter

· only read the first file listed by control_files parameter

· if any of the control files become unavailable during operation, instance becomes inoperable and should be aborted.

62, using spfile to multiplex controlfile

1,alter spfile：

sql>alter system set control_files='$orcle_home/oradata/u01/ctrl01.ctl',

'$orcle_home/oradata/u02/ctrl02.ctl' scope=spfile;

2,shutdown database：

sql>shutdown immediate

3,create additional control files：

$cp $orcle_home/oradata/u01/ctrl01.ctl $orcle_home/oradata/u02/ctrl02.ctl

4,start the database：

sql>startup

63,get control file information

information about control file status and locations can be retrieved by querying the following:

· v$controlfile: list the name and status of all control files associated with the instance

· v$parameter: lists status and location of all parameters

· v$controlfile_record_section: provides information about the control file record sections

· show parameter control_files: lists the name, status, and location of the control files

sql> select type, record_size, records_total, records_used from v$controlfile_record_section where type='datafile';

type record_size records_total records_used

---------------------------- ----------- ------------- ------------

datafile 428 100 8

此例表示数据文件的最大数为 100，这个数字由 create database 命令中的 maxdatafiles参数确定。

64，features of redo log files：

· record all changes made to data

· apply recovery mechanism

· can be organized into groups

· at least two groups required

sql> show parameter log_buffer

all the changes are put into the log buffer. lgwr will write the entries from log buffer to online redo log. the data will be written to online redo log when trigger commit operation.

so actually, no data will be lost.

65, the structure of redo log files

· a set of identical copies of online redo log files is called an online redo log file group.

· the lgwr background process concurrently writes the same information to all online redo log files in a group.

· the oracle server needs a minimun of two online redo log file groups for the normal operation of a database.

66,how redo file work

· online redo log files are used in a cyclic fashion.

· when a online redo log file is full, lgwr will move to the next log group.

a, called a log switch

b, checkpoint operation also occurs

c, information written to the control file

· the act of switching from one log file group to the other is called a log switch.

· a checkpoint is the writing of dirty (modified) blocks from the buffer cache to disk.

67,ls & lsn

ls:log switch

lsn: log sequence number

alter system switch logfile;

alter system checkpoint;

fast_start_mttr_target = 600; 600秒必须做一次ckpt

68，adding redo files groups

you can query the v$logfile and v$log to specify the number of redo log groups and the size of each member.

sql>alter database add logfile group 4 ‘/u01/app/oracle/oradata/test/redo04.log’ size 50m;

69, adding redo file members

alter database add logfile member

'/u01/app/oracle/oradata/test/redo01a.log' to group 1,

'/u01/app/oracle/oradata/test/redo02a.log' to group 2,

'/u01/app/oracle/oradata/test/redo03a.log' to group 3,

'/u01/app/oracle/oradata/test/redo04a.log' to group 4;

70,drop redo file group

sql> alter database drop logfile group 4;

限制：

一个例程至少需要两组联机重做日志文件。

无法删除活动组或者当前组。

删除联机重做日志文件组时并不删除操作系统文件。

71,drop redo log file member

sql>alter database drop logfile member '/u01/app/oracle/oradata/test/redo03a.log';

限制：

l 如果要删除的是组内的最后一个有效成员，那么您不能删除该成员。

l 如果该组是当前组，那么必须先强制执行日志文件切换，然后才能删除该成员。

l 如果数据库正运行在 archivelog模式下并且未将该成员所属日志文件组归档，那么您无法删除该成员。

l 删除联机重做日志文件成员时，如果没有使用 omf功能，则不会删除操作系统文件。

72,relocate & rename

relocate or rename online redo log files in one of the two following ways:

1,alter database clear logfile group n;

a,shut down the database

b,copy the online redo log files to the new location.

c,place the database in mount mode.

e,execute the command:

alter database rename file '/u01/app/oracle/oradata/prod/redo01.log' to '/u01/app/oracle/oradata/prod/redo011.log';

f,open database for normal operation.

sql>alter database open;

2,add new members and drop old members

alter database add logfile group 4 '/u01/app/oracle/oradata/prod/redo01.log' size 50m;

sql> alter database drop logfile group 1;

73,redo file configuration

put the online redo log file and archived log file on the different disks.

74,group & member information

information about group and its members can be obtained by querying the following views：

l v$log from controlfile

l v$logfile identify redo log group status

l v$log_history obtain log history information

75,在oracle10g中，log_archive_start参数已经被废弃，只要启动数据库的归档模式，oracle就会启用自动归档，从而避免了10g以前由于用户疏忽所带来的一系列问题。

76,tablespaces & data files

oracle stores data logically in tablespaces and physically in data files.

tablespaces:

l can belong to only one database at a time

l consist of one or more data files

l are further pided into logical units of storage

data files:

l can belong to only one tablespace and one database

l are a repository for schema object data

77,storage hierarchy summary

l a database is mode up of one or more tablespaces.

l a tablespace is mode up of one or more data files. these files might be cooked files in a file system, raw partitions, asm managed database files, or a file on a clustered file system. a tablespace contains segments.

l a segment (table,inex, and so on) is made up of one or more extents. a segment exists in a tablespace, but may have data in many data files within that tablespace.

l an extent is a logically contiguous set of blocks on disk. an extent is in a single tablespace and, furthermore,is alwys in a single file within that tablespace.

l a block is the smallest unit of allocation in the database. a block is the smallest unit of i/o used by database.

78,types of tablespaces

a, system tablespace

b, non-system tablespace

79,space mgmt in tablespaces(表空间以区为单位分配空间)

locally managed tablespace:(after 9i version default)

l free extents are managed in the tablespace.

l bitmap is used to record free extents.

l each bit corresponds to a block or group of blocks.

l bit value indicates free or used.

dictionary-manage tablespace:

l free extents are managed by the data dictionary.

l appropriate tables are upated when extents are allocated or deallocated.

80,locally managed tablespace

l reduced contention on data dictionary tables

l no undo generated when space allocation or deallocation occurs

l no coalescing required

create tablespace userdata datafile '/u01/oradata/userdata01.dbf' size 500m

extent management local uniform size 128k;

the default value of extent is 1m.

81,what does tom say

with a locally-managed tablespace, a bitmap stored in each data file is used to manage the extents. now to get an extent, all the system needs to do is set a bit to 1 in the bitmap.to free space, the system sets a bit back to 0. compared to using dictionary-managed tablespaces, this is incredibly fast. we no longer serialize for a long-running operation at the database level for a very fast operation. locally-managed tablespaces have other nice attributes as well, such as the enforcement of a uniform extent size, but that is starting to get heavily into the role of the dba.

going forward, the only storage management method you should be using is a locally-managed tablespace. in fact, in oracle9i and above, if you create a database using the dbca, it will create system as lmt and if system is locally managed, all other tablespaces in that database will be locally managed as well, and the legacy dictionary-managed method will not work. it is not that dictionary-managed tablespaces are not supported in a database where system is locally managed, it is that they simply cannot be created.

82,migrating a dm system ts

migrate a dictionary managed system tablespace to locally managed:

dbms_space_admin.tablespace_migrate_to_local(‘system’);

83,undo tablespace

l used to store undo segments

l cannot contain any other objects

l extents are locally managed

l can only use the datafile and extent management clauses

create undo tablespace undo1 datafile '/u01/oradata/undo01.dbf' size 40m;

84,temporary tablespaces

l used for sort operations

l can be shared by multiple users

l cannot contain any permanent objects

l locally managed extents recommended

create temporary tablespace temp

tempfile '/u01/oradata/temp01.dbf' size 500m

extent management local uniform size 4m;

when the system tablespace is locally managed, you must define at least one default temporary tablespace when creating a database. a locally managed system tablespace cannot be used for default temporary storage.

if system is dictionary managed and if you do not define a default temporary tablespace when creating the database, then system is still used for default temporary storage. however, you will receive a warning in alert.log saying that a default temporary tablespace is recommended and will be necessary in future releases.

本地管理的临时表空间具有临时数据文件 (tempfile)，它与普通数据文件很相似，只有
以下几点不同：

? tempfile始终设为 nologging模式。

? 无法将 tempfile设置为只读。

? 无法重命名 tempfile。

? 无法通过 alter database命令创建 tempfile。

? tempfile对于只读数据库是必需的。

? 介质恢复不恢复 tempfile。

若要优化临时表空间内的排序性能，可将 uniform size设置为 sort_area_size参数的整数倍。

85,default temporary ts

l specifies a database-wide default temporary tablespace

l eliminates using system tablespace for storing temporary data

l can be created by using：

a,create database

b,alter database

alter database default temporary tablespace temp;

86,restrictions of temp ts

default temporary tablespace cannot be：

l dropped until after a new default is made available

l taken offline

l altered to a permanent tablespace

87,read-only tablespaces

use the following command to place a tablespace in read-only mode:

alter tablespace userdata read only;

l causes a checkpoint

l data available only for read operations

l objects can be dropped from tablespace(because drop only update data dictionary)

88.taking a tablespace offline

not available for data access

1,tablespaces that cannot be taken offline：

l system tablespace

l tablespaces with active undo segments

2,default temporary tablespace

to take a tablespace offline：

alter tablespace userdata offline;

to bring a tablespace online：

alter tablespace userdata online;

alter tablespace tablespace

{online |offline [normal|temporary|immediate|for recover]}

其中：

normal：将该表空间中所有数据文件内的所有块从 sga中清空。这是缺省设置。在使该表空间重新联机之前，您无须对其执行介质恢复。尽可能使用 normal子句。

temporary：对表空间内的所有联机数据文件执行检查点操作，即使某些文件无法写入。所有脱机文件可能都需要进行介质恢复。

immediate：不保证表空间文件可用，而且不执行检查点操作。在使表空间重新联机前，您必须对其执行介质恢复操作。

for recover：使表空间脱机以进行表空间时间点恢复。

89, changing storage settings

a,using alter tablespace command to change storage settings(dictionary management tbs only):

l alter tablespace userdata minimum extent 2m;

l alter tablespace userdata default storage (initial 2m next 2m maxextents 999);

b,storage settings for locally managed tablespaces cannot be altered.

90,resizing a tablespace

a tablespace can be resized by:

a,changing the size of a data file:

l automatically using autoextentd

l manually using alter database

b,adding a data file using alter tablespace

91,enable automatic extension

a,can be resized automatically with the following command:

l create database

l create tablespace

l alter tablespace … add datafile

b,example：

l create tablespace user_data datafile '/u01/oradata/userdata01.dbf' size 200m autoextend on next 10m maxsize 500m;

c,query the dba_data_files view to determine whether autoextend is enable.

为新数据文件指定 autoextend（续）

使用 alter database命令可修改数据文件并启用自动扩展：

alter database datafile filespec [autoextend_clause]

autoextend_clause:== [ autoextend { off|on[next integer[k|m]] [maxsize unlimited | integer[k|m]] } ]

其中：

autoextend off：禁用数据文件的自动扩展

autoextend on：启用数据文件的自动扩展

next：指定在需要更多区时分配给数据文件的磁盘空间

maxsize：指定允许分配给该数据文件的最大磁盘空间

unlimited：将分配给数据文件的磁盘空间设为不受限

sql> alter database datafile '/u01/app/oracle/oradata/prod/corrupt01.dbf' autoextend on next 10m maxsize 500m;

92, manually resizing data file

l manually increase or decrease a data file size using alter database

l resizing a data file adds more space without adding more data files.

l manual resizing of a data file reclaims unused space in database.

l example：

alter database datafile '/u03/oradata/userdata02.dbf' resize 200m;

93,adding data files to a ts

l increases the space allocated to a tablespace by adding additional data files

l add datafile clause is used to add a data file

l example：

sql> alter tablespace corrupt_tbs add datafile '/u01/app/oracle/oradata/prod/corrupt02.dbf' size 200m;

94,method for moving data file

1,alter tablespace(它仅适用于不含活动还原段或临时段的非 system表空间中的数据文件)

· tablespace must be offline

· target data files must exist

the step of renaming data file：

· make the tablespace offline

alter tablespace example offline;

· use os command copy or move data files

· execute alter tablespace rename datafile

alter tablespace example rename datafile ‘/u01/app/oracle/oradata/test/example01.dbf’ to ‘/u01/app/oracle/oradata/test/example02.dbf’;

· make the tablespace online

sql> recover datafile 5;需要做recover

media recovery complete.

sql> alter tablespace example online;

tablespace altered.

· use os command delete data file if it’s needed

2, alter database

database must be mounted

target data file must exist

alter database命令

alter database命令可用来移动任意类型的数据文件：

alter database [database]

rename file 'filename'[, 'filename']...

to 'filename'[, 'filename']...

因为 system表空间无法脱机，您必须使用该方法移动 system表空间内的数据文件。

使用如下进程重命名无法脱机的表空间内的文件：

1. 关闭数据库。

2. 使用操作系统命令移动文件。

3. 装载数据库。

4. 执行 alter database rename file命令。

sql> alter database rename file '/u01/app/oracle/oradata/test/system01.dbf' to '/u01/app/oracle/oradata/test/system02.dbf';

5. 打开数据库。

95，dropping tablespaces

you cannot drop a tablespace if it：

system tablespace

has active segments

you can with the following optional:

including contents drop the segments

including contents and datafiles deletes data files

cascade constraints drops all referential integrity constraints

sql>drop tablespace example including contents and datafiles cascade constraints;

96，get tablespace information

tablespace information：dba_tablespaces v$tablespace

data file information：dba_data_files v$datafile

temp file information：dba_temp_files v$tempfile

97，type of segments

table segment, index segment, undo segment, lob segment, cluster segment, temp segment, partition segment.

lob 段：

表中的一列或者多列可以用来存储大型对象 (lob)，如文本文档、图像或者视频。如果列很大，oracle服务器将把这些值存储在独立的段（称为 “lob段”）中。表中只包含一个定位器或者指针，指向对应的 lob数据所在的位置。

98,extent alloc & dealloc

an extent is a chunk of space used by segment within a tablespace

an extent is allocated when the segment is:

· created

· extented

· altered

an extent is deallocated when segment is：

· dropped

· altered

· truncated

99,database block

· minimum unit of i/o

· consists of one or more operating system blocks

· set at tablespace creation

· db_block_size is the default block size

100,multiple block size support

a database can be created with a standard block size and up to four nonstandard block sizes.

block sizes can have any power-of-two value between 2kb and 32kb.

101,standard block size

a,set at database creation using the db_block_size parameter;

· cannot be changed without re-creating the database

· used for system and temporary tablespaces

b,db_cache_size specifies the size of default buffer cache for standard block size：

· minimum = one granule（4 mb or 16 mb）

· default = 48 mb

102,create non-std block size ts

sql> create tablespace tbs_1 datafile '/u01/app/oracle/oradata/test/tbs_101.dbf' size 10m blocksize 4k;

create tablespace tbs_1 datafile '/u01/app/oracle/oradata/test/tbs_101.dbf' size 10m blocksize 4k

*

error at line 1:

ora-29339: tablespace block size 4096 does not match configured block sizes

you need to pre-configure a non-std block size cache as following:

sql> alter system set db_4k_cache_size=4k;

sql> create tablespace tbs_1 datafile '/u01/app/oracle/oradata/test/tbs_101.dbf' size 10m blocksize 4k;

sql> select block_size ,tablespace_name from dba_tablespaces;

103,multiple block size rules

· all partitions of a partitioned object must reside in tablespaces of the same block size.

· all temporary tablespaces, including the permanent ones that are being used as default temporary tablespaces, must be of standard block size.

· index-organized table overflow and out-of-line lob segments can be stored in a tablespace with a block size different from the base table.

104,database block contents

block space util parameters:

initrans

maxtrans

pctfree (default 10%)

pctused( default 40%)

105,data block management

two methods are available for managing data blocks：

· automatic segment-space management

· manual management

106, the advantages of automatic segment-space management

a,it is a method of managing free space inside database segments.

b,tracking in-segment free and used space is done using bitmaps as opposed to using free lists.

this method provides:

· ease of management

· better space utilization

· better performance for concurrent insert operations

c,bitmap segments contain a bitmap that describes the status of each block in the segment d,with respect to its avaiable space.

e,the map is contained in a separate set of blocks referred to as bitmapped blocks(bmbs).

f,when inserting a new row, the server searches the map for a block with sufficient space.

g,as the amount of space available in a block changes, its new state is reflected in the bitmap.

107,configuring assm(automatic segment space management)

· automatic segment-space management can be enabled at the tablespace level only, for locally managed tablespaces.

· after a tablespace is created, the specifications apply to all segments created in the tablespace.

example:

create tablespace data02

datafile '/u01/app/oracle/oradata/test/data02.dbf' size 5m extent management local uniform size 64k

segment space management auto;

108,getting storage information

· dba_extents

· dba_segments

· dba_tablespaces

· dba_data_files

· dba_free_space

· 查询 dba_segments视图以获得分配给某个段的区和块的数目。

sql>select segment_name,tablespace_name,extents,blocks from dba_segments where owner = 'hr';

· 使用 dba_extents视图以检查给定段的区。

sql>select extent_id,file_id,block_id,blocks from dba_extents where owner='hr' and segment_name='employees';

109,managing undo data

there are two methods for managing undo data：

· automatic undo management

· manual undo management

the term undo was known as rollback in previous versions.

110,undo segment：purpose

transaction recovery

read consistency

transaction rollback

111,types of undo segments(system还原段 ,非system还原段 ,延迟还原段 )

a,system: used for objects in the system tablespace

b,non-system：used for objects in other tablespaces:

· auto mode: requires an undo tablespace

· manual mode：

· private: acquired by a single instance

· public:acquired by any instance

c,deferred: used when tablespaces are taken offline immediate, temporary, or for recovery

112,auto undo mgmt: concepts

· undo data is managed using an undo tablespace.

· you allocate one undo tablespace per instance with enought space for the workload of the instance.

· the oracle server automatically mainstains undo data within the undo tablespace.

113,auto undo mgmt: config

a,configure two parameter in the initialization file：

· undo_management

· undo_tablespace

b,create at least one undo tablespace

undo_management: specifies wheter the system should use auto or manual mode

undo_tablespace: specifies a particular undo tablespace to used

they are both need to be specified in pfile|spfile

sql> alter system set undo_tablespace = undotbs;

114,how to create undo tablespace

a, create the undo tablespace with the database by adding a clause in the create database command:

create database db01

. . .

undo tablespace undo1

datafile '/u01/oradata/undoldb01.dbf' size 20m

autoextend on

b,create it later by using the create undo tablespace command:

sql> create undo tablespace undo1 datafile '/u01/app/oracle/oradata/test/undo1db01.dbf' size 20m;

notes:自动还原管理需要一个 undo表空间。数据库中可能有多个 undo 表空间，但只能有一个 undo表空间处于活动状态。

115,switching uno ts

· you can switch from using one undo tablespace to another.

· only one undo tablespace can be in assigned to a database at a time.

· more than one undo tablespace may exist within an instance, but only one can be active.

· use the alter system command for dynamic switching between undo tablespaces.

sql>alter system set undo_tablespace=undo1;

116,dropping an undo ts

a,the drop tablespace command drops an undo tablespace.

· drop tablespace undotbs2;

b,an undo tablespace can only be dropped if it is currently not in use by any instance.

c,to drop an active undo tablespace:

· switch to a new undo tablespace.

· drop the tablespace after all current transactions are complete.

您可以在表空间 undotbs内的所有事务处理都已完成后删除它。要确定是否存在任何一个活动的事务处理，请使用以下查询：

select a.name,b.status from v$rollname a, v$rollstat b where a.name in ( select segment_name from dba_segments where tablespace_name = 'undotbs‘ ) and a.usn = b.usn;

117,sizing an undo ts

determining a size for the undo tablespace requires three pieces of information:

· (ur) undo_retention in seconds

· (ups) number of undo data blocks generated per second

· (dbs) overhead varies based on extent and file size (db_block_size)

· undospace = [ur * (ups * dbs)] + (dbs * 24)

sql> select (sum(undoblks) / sum((end_time - begin_time) * 86400)) as ups from v$untat;

select (ur * (ups * dbs)) + (dbs * 24) as "bytes" from (select value as ur

from v$parameter where name = 'undo_retention'),

(select (sum(undoblks)/sum(((end_time -begin_time)*86400))) as ups

from v$undostat),(select value as dbs from v$parameter where name = 'db_block_size');

为了获得最佳结果，应该在一天中数据库负载最繁重的时候进行计算。

118,get undo segment info

information about undo segments can be obtained by querying the following views:

dba_rollback_segs

dynamic performance views

v$rollname

v$rollstat (rollback segment detail)

v$undostat (undo data detail made by transaction)

v$session

v$transaction

sql>select segment_name,owner,tablespace_name,status from dba_rollback_segs;

owner 列指定还原段的类型：

sys：指专用还原段

public：指公用还原段

119,rowid format

restricted rowid: only for oracle 7 or before version

because a segment can only reside in one tablespace, by using the data objec number, the oracle server can determine the tablespace that contains a row.

the relative file number within the tablespace is used to locate the file, the block number is used to locate block containing the row, and the row number is used to locate the row directory for the row.

the row directory entry can be used to locate the beginning of the row.

thus, rowid can be used to locate any row within a database

180,structure of a row

row header--column length--column value

row data is stored in database blocks as variable-length records. columns for a row are generally stored in the order in which they are defined and any trailing null columns are not stored.

note: a single byte for column length is required for non trailing null columns.

row header: used to store the number of columns in the row, the chaining information, and the row lock status.

row data: for each column, the oracle server stores the column length and value (one byte is needed to store the column length if the column will require more than 250 bytes of storage in which case three bytes will be used for column length. the column value is stored immediately following the column length bytes.adjacent rows do not need any space between them. each row in the block has a slot in the row directory. the directory slot points to the beginning of the row.

181,creating a table

assm: automatic segment space management (pctfree, initrans)

mssm: manual segment space management (freelists, pctfree, pctused, initrans)

182, oracletemporary tables

create global temporary table hr.emp_temp on commit delete rows as select * from hr.employees;

此时只会创建一个临时表结构，不会有数据信息。

insert into emp_temp select * from employees;

此时数据顺利的插入进emp_temp临时表，是可以select * from emp_temp查看到记录的

commit;

事务提交后此时再select * from emp_temp就看不到记录了

create global temporary table hr.emp_temp on commit preserve rows as select * from hr.employees;

同理：此时只会创建一个临时表结构，不会有数据信息。

insert into emp_temp select * from employees;

此时数据顺利的插入进emp_temp临时表，是可以select * from emp_temp查看到记录的

commit;

事务提交后还是可以看到select * from emp_temp里面有记录了。如果我退出sql*plus重新登录，再select * from emp_temp就没有记录了，这就是会话期间都保存数据信息

· tables retain data only for the duration of a transaction or session.

· dml locks are not acquired on the data.

· you can create indexes, views, and triggers on temporary tables

183,row migration & chaining

row migration: if pctfree is set to a low value, there may be insufficinet space in a block to accommodate a row that grows as a result of an update. when this happens, the oracle server will move the entire row to a new block and leave a pointer from the original block to the new location. this process is referred to as row mirgration. when a row is migrated, i/o performance associated with this row decreases because the oracle server must scan two data blocks to retrieve the data.

row chaining: row chaining occurs when a row is too large to fit into any block. this might occur when the row contains columns that are very long. in this case, the oracle server pides the row into smaller chunks called row pieces. each row piece is stored in a block along with the necessary pointers to retrieve and assemble the entire row. row chaining can be minimized by choosing a higher block size or by splitting the table into multiple tables with fewer columns, if possible.

184,manually allocating extents

alter table hr.employees allocate extent(size 500k datafile '/u01/app/oracle/oradata/prod/users01.dbf');

185,nonpartitioned table reorg

alter table hr.employees move tablespace data1;

when a nonpartitioned table is reorganized, its structure is kept, but not its contents. (rowid会变) .

it is used to move a table to a different tablespace or reorganize extents.

a nonpartitioned table can be moved without having to run the export or import utility. in addition, it allows the storage parameters to be changed. this is useful when:

moving a table from one tablespace to another

reorganizing the table to eliminate row migration

186,truncating table

truncating a table deletes all rows in a table and releases used space.

corresponding indexes are truncated

187,dropping a column

sql>alter table hr.employees drop column comments cascade constraints checkpoint 1000;

removes the column length and data from each row, freeing space in the data block.

dropping a column in a large table tables a considerable amount of time.

删除列时使用检查点：

删除列可能需要很长时间，并且且需要大量的还原空间。从大型表中删除列时，可以指定检查点来尽量减少还原空间的使用。在幻灯片上的示例中，每 1,000行出现一个检查点。在操作运行完成前，该表一直被标记为 invalid。如果操作过程中例程失败，则该表在启动后仍将处于 invalid状态，因此该操作必须完成。

使用下面的语句可恢复中断的删除操作：

sql> alter table hr.employees drop columns continue;

188,renaming a column

alter table hr.tt1 rename column old_name to new_name

189, using the unused option

alter table hr.employees set unused column comments cascade constraints;

alter table hr.employees drop unused columns checkpoint 1000;

alter table hr.employees drop columns continue checkpoint 1000;

190,getting table information

dba_tables

dba_objects

191, b- tree index

192,creating indexes: guideline

l balance query and dml needs

l place in separate tablespace.

l use uniform extent sizes: multiples of five blocks or minimum extent size for tablespace.

l consider nologging for large inexes.

l initrans should generally be higher on indexes than on the corresponding tables.

193,create bitmap indexes

create bitmap index orders_region_id_idx

on orders(region_id)

pctfree 30

storage(initial 200k next 200k pctincrease 0 maxextents 50)

tablespace indx;

create_bitmap_area_size参数：

初始化参数 create_bitmap_area_size决定了内存中用于存储位图段的空间量。缺省值为 8 mb。使用较大的值，可提高索引创建的速度。如果基数很小，可将该值设置为一个较小值。例如，如果基数仅为 2，则该值可以为千字节数量级而非兆字节数量级。一般来讲，基数越大，则获取最佳性能所需的内存越多。

194,storage params for indexes

alter index emp_emp_id_pk storage(next 400k maxextents 100);

有可能修改不成功。

195,alloc & dealloc index space

alter index orders_region_id_idx allocate extent (size 200k datafile '/disk6/indx01.dbf');

alter index orders_id_idx deallocate unused;

196,rebuilding indexes

l move an index to a different tablespace

l improve space utilization by removing deleted entries

alter index orders_region_id_idx rebuild tablespace indx02;

alter index ...rebuild命令不能用于将位图索引更改为 b 树索引，反之亦然。
只能为 b树索引指定 reverse 或 noreverse关键字。

197,rebuilding indexes online

indexes can be rebuilt with minimal table locking.

sql>alter index orders_id_idx rebuild online;

some restrictions still apply.

联机重建索引

建立或重建索引是一项费时的任务，尤其当表非常大时更是如此。在 oracle8i之前，建立或重建索引都需要锁定表，并要防止并发的 dml 操作。 oracle9i允许在基表上进行并发操作的同时建立或重建索引，但不建议在此过程中执行大量的 dml操作。

注：仍存在 dml锁，这意味着在联机索引建立期间不能执行其它 ddl 操作。

限制：

l 不能在临时表中重建索引

l 不能重建整个分区索引。必须分别重建每个分区或子分区。

l 也不能回收未用空间。

l 不能整个更改索引的 pctfree参数值。

198,coalescing indexes

sql> alter index hr.employees_idx coalesce;

199,checking index validity

analyze index emp_emp_id_pk validate structure;

运行此命令后，查询 index_stats以获取索引的有关信息，如下例所示：

sql> select blocks, pct_used, distinct_keys lf_rows, del_lf_rows from index_stats;

blocks pct_used lf_rows del_lf_rows

---------- ---------- ---------- -----------

8 19 107 0

如果索引中已删除行的比例很高，请重新组织该索引。例如：当 del_lf_rows占 lf_rows 的比率超过 30%时。

200,dropping indexes

l drop and re-create an index before bulk loads.

l drop indexes that are infrequently needed, and build indexes when necessary.

l drop and re-create invalid indexes.

sql>drop index hr.deptartments_name_idx;

201,identifying unused indexes

to start monitoring the usage of an index:

alter index hr. emp_emp_id_pk monitoring usage;

to stop monitoring the usage of an index:

alter index hr. emp_emp_id_pk nomonitoring usage;

从 oracle9i开始，可以在 v$object_usage 中收集和显示有关索引使用的统计信息。如果收集的信息表明索引从未使用过，则删除该索引。此外，删除未用索引还可减少 oracle服务器用于 dml 操作的开销，从而改善了性能。每次指定 monitoring usage子句时，将对指定的索引重置 v$object_usage。以前的信息被清除或重置，并记录新的开始时间。

v$object_usage列

index_name：索引名

table_name：对应的表

monitoring：指示监视是 on还是 off

used：指示 yes或 no，即在监视时间内是否使用了索引

start_monitoring：索引监视的开始时间

end_monitoring：索引监视的结束时间

202,getting index information

dba_indexes

dba_ind_columns

v$object_usage

203,the type of constraints

l not null

l unique(可以有null值)

l primary key(不可以有null值)

l check

l foreign key

204, the integrate of data

application

trigger

constraints

205,the status of constraints

· disable novalidate

· disable validate

· enable novalidate

· enable validate

alter session set constraint[s] ={immediate|deferred|default}

206, foreign key

涉及父表的 ddl：

在删除父表之前，必须先删除外键。可以使用以下一条语句同时执行这两个操作：

drop table table cascade constraints

在未删除或禁用外键之前无法截断父表。

在删除包含父表的表空间之前，必须先删除外键。可使用下列命令完成该操作：

drop tablespace tablespace including contents cascade constraints

207, using constraints

a,enable novalidate

没有表锁定

主键和唯一键必须使用非唯一索引

alter table hr.departments enable novalidate constraint dept_pk;

b, enable validate

锁定表

可以使用唯一或非
唯一的索引

需要有效的表数据

alter table hr.employee enable validate constraint emp_dept_fk;

208,创建表时定义约束

sql> create tablespace indx datafile '/u01/app/oracle/oradata/test/indx01.dbf' size 100m;

sql>create table hr.employee(id number(7) constraint employee_id_pk primary key deferrable

using index storage(initial 100k next 100k) tablespace indx,

last_name varchar2(25) constraint employee_last_name_nn not null,

dept_id number(7)) tablespace users;

209, using exceptions table

· 通过运行 utlexcpt.sql脚本来创建 exceptions 表。

sql> @?/rdbms/admin/utlexcpt

sql>desc exceptions

· 执行带有 exceptions选项的 alter table 语句。

alter table hr.employee enable validate constraint employee_dept_id_fk exceptions into hr.exceptions;

· 使用 exceptions上的子查询定位包含无效数据的行。

select * from hr.employee where rowid in (select row_id from exceptions) for update;

· 纠正错误。

· 重新执行 alter table以启用约束。

210, get the constraints information

dba_constraints

dba_cons_columns

211,enabling password mgmt

te enable password management, run the utlpwdmg.sql script as user sys

sql>@?/rdbms/admin/utlpwdmg.sql

这个时候系统的密码就安装严格的格式校验，如果是个人测试系统，很麻烦，索引需要取消校验如下：

alter profile default limit password_verify_function verify_function;(启用校验)

alter profile default limit password_verify_function null;(取消校验)

212,database schema

· a schema is a named collection of objects.

· a user is created, and a corresponding schema is created.

· a user can be associated with only one schema.

· username and schema are often used interchangeably.

213, database authentication

sql> create user watson identified by watson default tablespace users temporary tablespace temp quota 15m on users quota 10m on example password expire;

214,getting user information

dba_users

dba_ts_quotas

215,managing privileges

there are two types of oracle user privileges:

· system priviledges: enables users to perform particular actions in the database

· object priviledges: enables users to access and manipulate a specific object

216,who can grant or revoke?

only two types of users can grant system privileges to other users or revoke those privileges form them:

users who were granted a specific system privilege with the admin option

users with the system privilege grant any privilege

for this reason, only grant these privileges to trusted users.

217,the public

public is a user group defined in the database; it is not a database user or a role. every user in the database belongs to this group. therefore, if you grant privileges topublic, they are avaiable to all users of the database.

218,granting system privileges

use the grant command to grant system privileges.

the grantee can further grant the system privilege with theadmin option.

grant create session to emi;

grant create session to emi with admin option;

219,revokoing system privileges

· use the revoke command to remove a system privilege from a user.

· users with admin option for system privilege can revoke system privileges.

· only privileges granted with a grant command can be revoked.

sql>revoke create table from emi;

不能级联撤销系统权限

220，granting object privileges

· use the grant command to grant object privileges.

· grant must be in grantor's schema or grantor must havegrant option.

grant execute on dbms_output to jeff;

grant update on emi.customers to jeff withgrant option;

221，revoking object privileges

use the revoke command to revoke object privileges

user revoking the privilege must be the original grantor of the object privilege being revoked.

revoke select on emi.orders from jeff;

可以级联revoke object priviledges

222，getting privileges inform

· dba_sys_privs

· session_privs

· dba_tab_privs

· dba_col_privs

223，roles

· roles can be grant to and revoked from users with the same commands that are used to grant and revoke system privileges.

· roles can be granted to any user or role. however, a role cannot be granted to itself and cannot be granted circularly.

· a role can consist of both system and object privileges.

· a role can be enabled or disabled for each user who is granted the role.

· a role can require a password to be enabled.

· each role name must be unique among existing usernames and role names.

· roles are not owned by anyone; and they are not in any schema.

· roles have their descriptions stored in the data dictionary.

224，predefined roles

· connect privilege to connect to the database;

· resource privilege to create a cluster, a table, and a sequence, and to create programmatic objects such as procedures, functions, packages, indextypes, types, triggers, and operators.

· dba all system privileges with the admin option, so the system privileges can be granted to other users of the database or to roles.

· select_catalog_role ability to query the dictionary views and tables.

· execute_catalog_role privilege to execute the dictionary packages (sys-owned packages).

· delete_catalog_role ability to drop or re-create the dictionary packages.

· also, when you run the catproc.sql script as part of the database creation, the script executes catexp.sql, with creates two more roles:

· exp_full_database ability to make full and incremental exports of the database using the export utility.

· imp_full_database ability to perform full database imports using the import utility, this is a very powerful role.

225，getting role informatin

information about roles can be obtained by querying the following views:

· dba_roles： all roles that exist in the database.

· dba_roles_privs：roles granted to users and roles

· role_role_privs： roles that are granted to roles

· dba_sys_privs： system privileges granted to users and roles

· role_sys_privs： system privileges granted to roles

· role_tab_privs： object privileges granted to roles

· session_roles： roles that the user currently has enabled

226，data loading methods

direct load:

将数据从外部文件加载到 oracle数据库表中。sql*loader 包含一个功能强大的数据分析引擎，该引擎对数据文件中数据的格式几乎没有限制

sql*loader(直接加载):

直接加载插入可用于在同一数据库中从一个表向另一个表复制数据。此方法绕过数据库缓冲区高速缓存直接将数据写入数据文件，从而加快了插入操作的速度

227,direct load

直接加载插入（串行或并行）只能支持 insert语句的 insert ... select 语法而
无法支持其 insert ... values语法。insert ... select 的并行性是由并行
提示或并行表定义决定的。

可使用 append提示调用直接加载插入，如以下的命令所示：

insert /*+append */ into [ schema. ] table [ [no]logging ] sub-query;

228,serial direct load

insert /*+ append */ into my_objects nologging select * from dba_objects;

commit;(如果不提交，则没有办法做dml,或者query my_objects对象)

数据时直接插入到hwm之后的。不走

sql> select force_logging from v$database;

note: if the database or tablespace is in force logging mode, then direct path insert always logs, regardless of the logging setting.

229，sql*loader control file

the loader control file tells sql*loader:

where to find the load data

the data format

configuration details:

· memory management

· record rejection

· interrupted load handing details

how to manipulate the data

230，编码方案

oracle 提供不同类别的编码方案：

单字节

宽度可变

宽度固定

unicode

231:字符集

character set (数据库字符集)

national character set (国家字符集):oracle只有两种：al16utf16 | utf8

national language support(nls)国家语言支持

232,编码方案(推荐用unicode)
oracle 支持不同类别的字符编码方案：

· 单字节字符集(including: 7位 and 8 位)

单字节方案示例：

7 位字符集：美国 7 位 ascii码 (us7ascii)

8 位字符集：

? 西欧 iso 8859-1码 (we8iso8859p1)

? 西欧 8位 ebcdic 代码页 500码 (we8ebcdic500)

? 西欧 8位 dec 码 (we8dec)

· 宽度可变的多字节字符集

宽度可变的多字节方案示例：

? 日文扩展 unix代码 (jeuc)

? 中文 gb2312-80 (cgb2312-80)

? al32utf8 (utf-8)

· 宽度固定的多字节字符集

除了每个字符采用字节数固定的格式外，宽度固定的多字节字符集同宽度 可变的多字节字符集提供的支持类似。

这提供了每个字符具有统一字节长度表示法的好处。

宽度固定的多字节字符集示例：

al16utf16、16位 unicode（宽度固定的双字节 unicode）

· unicode (al32uft8, al16utf16, utf8)

unicode是一种全球字符编码标准，可以表示计算机中使用的所有字符，包 括技术符号和出版用的字符。unicode标准 3.0 版包含 49,149个字符，容量 超过一百多万个字符。

oracle 提供al32utf8、utf8和utfe作为数据库字符集，同时提供 al16utf16 和utf8作为国家字符集。

233,数据库字符集和国家字符集

数据库字符集:存储类型为 char、 varchar2、 clob、 long 的数据列

国家字符集 :存储类型为 nchar、nvarchar2、nclob的数据列

234,national language support(nls)国家语言支持

235，指定会话的语言相关行为

alter session set nls_date_format=‘dd.mm.yyyy’;

dbms_session.set_nls(‘nls_date_format’,’’’dd.mm.yyyy’’’) ;

236，nls排序

sql> alter session set nls_sort = binary;

237，在 sql函数中使用 nls参数

select to_char(hire_date,'dd.mon.yyyy', 'nls_date_language=chinese') from employees;

238，使用 nls导入和加载数据

· 在导入过程中，数据将从导出文件字符集转换成数据库字符集。

sql*loader:

· 常规路径：将数据转换为 nls_lang指定的会话字符集。

· 直接路径：数据直接转换为数据库字符集。

239，获取字符集信息

sql>select parameter, value from nls_database_parameters where parameter like '%characterset%';

240，获取 nls设置信息

以下视图仅显示出已在 init<sid>.ora文件中显式设置的参数的值。

sql> select * from nls_instance_parameters;

以下视图显示会话参数。

sql> select * from nls_session_parameters;

列出 nls 参数的所有有效值。

sql>select * from v$nls_valid_values;

显示 nls 参数的当前值。

sql> select * from v$nls_parameters;