ORACLE基础学习(创建)

2 创建
所有参见内容都在附件01_install_02_create_03_init/下。
以oracle用户进行操作，设定数据库实例名为oradb（长度建议不要超过8个字符）。
2.1 Oracle 8 & 8i
2.1.1 工具创建
Oracle 8
运行$ORACLE_HOME/bin/orainst（安装数据库时必须选中oracle installer），选择create database object，安装界面中选Oracle Enterprise Server(RDBMS)
mount point暂为$ORACLE_BASE，字符集为ZHS16CGB231280或ZHS16GBK，调整system,tools,users,rbs,temp,redolog等尺寸。
创建过程中会提示输入osdba,osoper的UNIX组，这是向instance表明此组的成员享有角色sysdba或sysoper的权限，从而用connect / as sysdba替换掉connect internal
Oracle 8i
进入X WINDOW，运行dbassist
2.1.2 手工创建
任何工具都有其局限性，熟练的数据库管理员可采用手工方法创建数据库，以增加对系统的灵活控制。
对于手工建库Oracle 8与Oracle 8i的区别主要是建立的数据字典和存储过程有些不同，Oracle8i的dbassistant可以生成建库脚本供以后使用。
取得/8i/initoradb.ora，编辑如db_name,control_file,dump_dest等参数，以符合实际情况。如不需要生成remote_login_passwordfile，可在initoradb.ora中设remote_login_passwordfile=none；如需要，在initoradb.ora中设remote_login_passwordfile=exclusive，运行orapwd file= password= 必须创建新生成文件所要用到的目录，如在配置文件中指定的bdump,cdump,udump等目录，以及数据文件存储目录。
将initoradb.ora转移到$ORACLE_BASE/admin/oradb/pfile/，并连接到$ORACLE_HOME/dbs/initoradb.ora。
ln -s $ORACLE_BASE/admin/oradb/pfile/initoradb.ora $ORACLE_HOME/dbs/initoradb.ora
取得8i/createdb.sh，编辑如pfile,数据文件目录等参数，以符合实际情况，并转移到$ORACLE_BASE/admin/oradb/create/下，执行。
相关系统表：
v$database
v$datafile(file#,ts#,name)
v$tablespace(ts#,name)
v$parameter（SQL>;show parameter）
v$sga（SQL>;show sga）
2.1.3 MTS（multi-threaded server）
Oracle8使用两种配置模式：dedicated server（专用模式）和shared server（即multi-threaded server共享模式），缺省使用专用模式。在连接数不很大且保持长期连接的情况下，专用模式为每个连接设立一个专用oracle服务进程，以保持较高的性能和稳定性。而当连接数上升到非常高的数目且不保持长期连接时，数据库管理开销增大，并且占用大量系统资源，给操作系统形成带来极大的压力。在这种情况下，共享模式更为有利，它通过缓冲池和预先设定数目的server提供服务，每个连接不再有专用的oracle服务进程，每次SQL操作由分配器（dispatcher）确定oracle服务进程。
multi-thread仅表示分配器展开的多个服务流程，并非操作系统意义上的多线程
配置：
￠ initoradb.ora
加入
mts_dispatchers = "(address=(protocol=TCP))(dispatchers=10)" #初始分配器数量
mts_max_dispatchers = 15 #最大分配器数量
mts_servers = 50 #初始服务进程数量
mts_max_servers = 80 #最大服务进程数量
mts_service = oradb3 #MTS方式下对外提供的数据库服务，非service_name
表明instance能够提供MTS服务，不意味着取消dedicated方式
￠ listener.ora
应删除所有SID_LIST，SID_LIST的存在决定LISTENER以dedicated还是shared方式启动oracle连接。如SID_LIST存在，LISTENER不再接受instance的登记，以dedicated方式启动oracle连接； 如SID_LIST不存在，LISTENER启动时不为任何instance服务，由instance来登记MTS service，以shared方式启动oracle连接
￠ client
MTS在client端配置颇为怪诞，在tnsnames.ora中的host一定要写数据库server的名字，而且必须作全名解析，似乎server端接收到client端请求后会将主机字符串返回，应此client端必须能够解析，否则会报出诸如"database service not exist"的错误
tnsnames.ora
dbserver.soar.com =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS=(PROTOCOL=TCP)(HOST= dbserver)(PORT = 1521))
)
(CONNECT_DATA =(SERVICE_NAME = oradb))
)
/etc/hosts
10.0.0.1 dbserver.soar.com dbserver
启动：先起LISTENER，后起instance
以下步骤均在数据库open状态下，由system用户完成
2.1.4 调整临时表空间
alter tablespace temp temporary; #Oracle8的orainst没有将temp的缺省值permanent改为temporary，这样用户在temp上暂存的数据均为永久对象，很快将temp空间耗完。Oracle8i已修正。
SQL>;alter tablespace temp default storage (initial 128k next 128k maxextents 5000 pctincrease 0)；
SQL查询操作如group by,order by,distinct，join等需要在临时段上展开数据，须充分考虑临时段的大小。
如果实例启动参数指定hash_join_enabled=true（缺省为true），当oracle选择以hash join方式进行表与表的联接，oracle根据查询操作的实际情况计算出hash_multiblock_io_count，此参数从属于session，平时显示为0，即hash join一次I/O读写需要的连续数据空间。这样当此参数大于临时段的next扩展块时，hash join操作会中断。如果预知联接表的规模比较巨大，可使用alter tablespace temp default storage(next …)将next值设为较大值，待全部操作完成后，再恢复正常。
2.1.5 调整回滚表空间
先将建库工具缺省设定的若干个回滚段删除
SQL>;alter rollback segment r01 offline；
SQL>;drop rollback segment r01；
根据实际需要创建回滚段（如r01-r10），供联机处理和批处理使用
SQL>;create rollback segment r01 storage（initial 128k next 128k maxextents 5000 optimal 5M） tablespace rbs；
SQL>;alter rollback segment r01 online；
注意修改$ORACLE_HOME/dbs/initoradb.ora中的激活回滚段段名
另创建一个尺寸无限制的回滚段（r99），供特殊用途
SQL>;create rollback segment r99 storage（initial 128k next 128k maxextents 5000） tablespace rbs；
如果在创建回滚段时使用create public rollback segment，则不需要在$ORACLE_HOME/dbs/initoradb.ora中用rollback_segment=(…)选项激活，推荐使用public方式
相关系统表：
SQL>;select segment_name, initial_extent, next_extent, max_extents, extents,bytes from dba_segments where segment_type='ROLLBACK'; #回滚段占用空间状况
SQL>;select segment_name, status from dba_rollback_segs; #回滚段状态
2.1.6 调整日志
建立日志组
SQL>;alter database add logfile group x('log1a','log1b') size 10M;
增加日志组成员
SQL>;alter database add logfile member 'log1c' to group x；
删除日志
数据库实例至少需要2个日志组，只有状态为inactive的日志组才能被删除，而当前日志组状态为current，上一个切换的日志组状态为active，这就意味着至少存在3个日志组才能删除其中的一个，如果要更新全部日志组，只能删除一个，再创建一个，直至全部被更新。
SQL>;alter database drop logfile group x；
如果要删除的日志组是当前日志组，必须先将其切换至状态为inactive，再删除。
SQL>;alter system switch logfile；
删除日志组成员
SQL>;alter database drop logfile member 'log1c';
相关系统表
v$log ＃日志组状态、占用空间、顺序号等
v$logfile ＃日志组文件
2.1.7 调整用户表空间
创建表空间
假定表数据在ts_data，索引在ts_index
SQL>;create tablespace ts_data default storage（initial 10M next 10M maxextents 5000 pctincrease 0） datafile 'path/data_01.dbf' size 500M;
SQL>;create tablespace ts_index default storage（initial 5M next 5M maxextents 5000 pctincrease 0） datafile 'path/index_01.dbf' size 500M;
参考命令：删除表空间
SQL>;drop tablespace data including contents; ＃删除表空间及其包含的所有数据对象
相关系统表：
user(dba)_tablespaces
增加表空间尺寸
假定表空间ts_data由path/data_01.dbf和path/data_02.dbf（500M）组成
增加一个数据文件：
SQL>;alter tablespace ts_data add datafile 'path/data_03.dbf' size 500M;
扩大原有文件大小：
SQL>;alter database datafile 'path/data_01.dbf' resize 1000M;
移动表空间数据文件
假如要求为：将path1下data_01.dbf移至path2下，并把文件名改为data01.dbf
实例处于关闭状态
sqlplus "/ as sysdba"
SQL>;startup mount
回到shell环境下
$ mv path1/data_01.dbf path2/data01.dbf
$ mv path1/data_02.dbf path2/data02.dbf
再到sqlplus环境中
SQL>;alter database rename file 'path1/data_01.dbf' to 'path2/data01.dbf';
或
SQL>;alter tablespace tbsdata rename datafile 'path/data_01.dbf' to 'path2/data01.dbf';
SQL>;alter database open;
查看剩余空间
SQL>;select tablespace_name,sum(bytes),max(bytes) from dba_free_space group by tablespace_name;
注意:空闲数据块总和sum(bytes)够用并不意味每个空闲块都满足分配需要，所以当表空间不够分配扩展块的时候，还要查看最大空闲数据块max(bytes)的大小。
合并空闲块
如果表空间上的数据对象经常发生类似drop-create的变动，加之未采用统一的扩展块尺寸，使那些采用较大扩展块的数据对象不能利用较小的空间碎片，造成空间浪费。可通过将较小的空闲块合并成较大的空闲块的方法，减少空间浪费。
SQL>;alter tablespace tbsdata coalesce;
2.1.8 创建用户
SQL>;create user dbuser identified by oracle default tablespace data temporary tablespace temp quota unlimited on data quota 0 on system quota 0 on tools quota 0 on users；
SQL>;grant connect to dbuser；
SQL>;grant create procedure to dbuser； ＃这些权限足够用于开发及生产环境
SQL>;grant select on dba_pending_transactions to dbuser; ＃二阶段提交过程中类似Tuxedo的软件需要检索挂起交易的状态，所以必须得到对此视图的select权限，以sys用户身份赋予
修改用户可使用alter user dbuser ...
参考命令：
drop user dbuser cascade; ＃删除用户及其所有的数据对象
revoke connect from dbuser; ＃取消用户角色权限
相关系统表：
user(dba)_users
user(dba)_role_privs 角色权限
user(dba)_sys_privs 系统权限
user(dba)_tab_privs 对其他用户表操作的权限
user_ts_quotas 表空间限额
2.1.9 创建数据对象
相关系统表：
user_catalog(cat)
user_objects(obj)
表和索引建立在表空间上，如果不指定表空间，使用本用户的缺省表空间（default tablespace）；如果不指定本对象的存储参数，使用建于其上的表空间的缺省存储参数（default storage）。
表（table）
建表脚本通常是以下形式：
create table emp （no number(12), name char(20), …,constraint emp_x00 primary key(no)） storage（initial 100M next 100M pctincrease 0 maxextents 5000） pctused 70 pctfree 10 tablespace tbs_data enable primary key using index tablespace tbs_index;
然而从简化数据对象配置、减少表空间碎片的角度考虑，不推荐为每张表单独指定storage选项，存储参数使用建于其上的表空间的缺省存储参数。不同表对扩展块大小的要求，可以通过分析归类，建立相应具有不同缺省存储参数的表空间的方法解决。这样数据库设计就能变得简洁明了。
命令简化为：
create table emp （no number(12), name char(20), …, constraint emp_x00 primary key(no)）pctused 70 pctfree 10 tablespace tbs_data enable primary key using index tablespace tbs_index;
primary key关键字建立同名的primary key constraint和unique index，表的每个域都有自身的constraint。
相关系统表：
user_tables(tabs)，dba_tables #表属性
user_tab_columns(cols),dba_tab_columns #表各列属性
索引（index）
create index emp_x01 on emp(name) storage(initial 10M next 10M pctincrease 0 maxextents 5000) pctfree 10 tablespace tbs_index;
可参照表对storage的处理方式。
create index emp_x01 on emp(name) pctfree 10 tablespace tbs_index;
相关系统表：
user_indexes(ind),dba_indexes #索引属性
user_ind_columns,dba_ind_columns #索引各列属性，以index_position为顺序
序列（sequence）
create sequence emp_seq increment by 1 start with 1 nomaxvalue nocycle;
相关系统表：
user(dba)_sequences(seq) 序列属性
视图（view）
create emp_depart_view as select emp.name,emp_duty.name from emp,emp_duty where emp.duty=emp_duty.duty;
相关系统表：
user(dba)_views 视图属性
Oracle将view,sequence，用户参数等定义均存放于系统表空间，而用户创建的表空间仅存放table,index实体，因此可以大胆删除用户表空间，再用备份重新恢复，不必担心view,sequence等会被一并删去。
2.1.10 创建只读用户
假定数据库用户dbbrsr需要对dbuser的表emp拥有select权力
connect dbuser
grant select on emp to dbbrsr
connect dbbrsr
create synonym emp for dbuser.emp;
这样，dbbrsr就能象使用自己的表一样对dbuser的表执行select操作
2.1.11 启动及关闭数据库实例
oracle用户，dbstart和dbshut启动及关闭/var/opt/oracle/oratab或/etc/oratab中设定的数据库实例，dbstart采用normal方式，dbshut采用immediate方式。
或者使用手工方式
sqlplus "/ as sysdba"
启动
normal
SQL>;startup
mount
SQL>;startup mount; #启动实例进程，载入数据库文件，允许DBA权限的某些操作，但禁止对数据库文件的一般性操作
SQL>;完成某些操作
SQL>;alter database open;
nomount
SQL>;startup nomount; #启动实例进程，但不允许访问数据库，常用于创建数据库、介质恢复或创建controlfile
SQL>;完成某些操作
SQL>;alter database open;
关闭
normal
SQL>;shutdown或SQL>;shutdown transactional; #等待每个连接交易完成后，切断连接，再关闭数据库
immediate
SQL>;shutdown immediate; #立刻中止每个连接，交易回滚
abort
SQL>;shutdown abort; #立刻关闭数据库，不保证交易完整性，在下一次启动打开数据库文件时会进行介质恢复
2.1.12 网络配置
假定某一台机器为client，ORACLE_SID为oraclient，数据库用户为dbclient；另一台机器为server，ORACLE_SID为oraserver，数据库用户为dbserver在server上$ORACLE_HOME/dbs/initoraserver.ora中有以下设定：
db_name = oraserver
instance_name = oraserver
Oracle 8i
service_names=oraserver
2.1.12.1 TNS
Client端配置
修改$ORACLE_HOME/network/admin/tnsnames.ora，增加一条PROTOCOL=TCP的记录。
Oracle8
db_server
(DESCRIPTION =
(ADDRESS = (PROTOCOL= TCP)(Host= server)(Port= 1521))
(CONNECT_DATA = (SID = oraserver))
)
Oracle8i
db_server
(DESCRIPTION =
(ADDRESS = (PROTOCOL= TCP)(HOST= server)(PORT= 1521))
(CONNECT_DATA = (SERVICE_NAME=oraserver
)
HOST可在/etc/hosts或DNS中配置，或直接写上IP地址
sqlplus dbserver/passwd@db_server
Server端配置
修改$ORACLE_HOME/network/admin/listener.ora
在LISTENER中增加ADDRESS的记录
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
(ADDRESS = (PROTOCOL = TCP)(HOST = server)(PORT = 1521))
)
)
)
在SID_LIST_LISTENER中增加SID_DESC记录
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /opt/oracle/app/oracle/product/8.1.5)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = oraserver)
(ORACLE_HOME = /opt/oracle/app/oracle/product/8.1.7)
(SID_NAME = oraserver)
)
)
HOST可在/etc/hosts或DNS中配置，或直接写上IP地址
注意：LISTENER和SID_LIST_LISTENER是成对出现的，可配置多个监听服务进程和相应的SID_LIST，如LISTENER_1和SID_LIST_LISTENER_1
2.1.12.2 DB Link
如果client的instance需要在访问本地数据对象同时访问server中的数据对象，可在instance中创建对server的数据库连接，实现间接访问
在tnsnames.ora中建立"db_server"配置
sqlplus dbclient/passwd1
SQL>;create database link server_link connect to dbserver identified by passwd using 'db_server';
使用emp@server_link访问server上的emp，如同访问本地instance中的数据对象一样。为了更方便的使用，可建立synonym
2.2 Oracle 9i
Oracle 9i相较于Oracle 8&8i，在兼容Oracle 8&8i的基础上，回滚和临时表空间配置发生比较大的变化，导致建库操作出现一些不同。在数据库配置文件initoradb.ora中有关于回滚表空间的选项，详细情况在"数据库配置"中解释。而且Oracle 9i简化了表空间的创建。所以此小节主要描述Oracle 9i相对于Oracle 8i的差异，其它相同的操作可参考Oracle 8i。
2.2.1 手工创建
由于在Oracle 9i中工具dbassist的使用方法与在Oracle 8i中类似，因此工具建库过程省略，只记录手工建库过程
Oracle 9i中的建库过程已经变得极为简洁，大致如下：
create database ${ORACLE_SID}
user sys identified by sys
user system identified by system
logfile group 1 ('${ORACLE_BASE}/oradata/${ORACLE_SID}/redo01.log') size 10M,
group 2 ('${ORACLE_BASE}/oradata/${ORACLE_SID}/redo02.log') size 10M,
group 3 ('${ORACLE_BASE}/oradata/${ORACLE_SID}/redo03.log') size 10M
maxlogfiles 5
maxlogmembers 5
maxloghistory 1
maxdatafiles 254
maxinstances 1
archivelog
character set ZHS32GB18030
national character set AL16UTF16
datafile '${ORACLE_BASE}/oradata/${ORACLE_SID}/system01.dbf' size 300M
default temporary tablespace tbstemp tempfile '${ORACLE_BASE}/oradata/${ORACLE_SID}/temp01.dbf' size 500M
undo tablespace tbsundo datafile '${ORACLE_BASE}/oradata/${ORACLE_SID}/undo01.dbf' size 500M;
其特点为使用专用的回滚和临时表空间，而不象Oracle 8i中的那样，回滚和临时表空间与普通表空间没有差异，这样既简化了配置也有利于效能提高。要注意临时表空间的指定文件关键字是tempfile而不是通用的datafile，而且临时表空间的存储选项必须为uniform，由Oracle系统决定。同样回滚表空间也是由Oracle系统决定。不必人工干预。
Oracle 9i在$ORACLE_HOME/dbs下可使用二进制配置文件，缺省为spfile{实例名}.ora，如spfileoradb.ora，支持Oracle系统进程在不重启的情况下动态调整参数，这对要求不间断运行的系统是有利的。在建库阶段就可将此配置文件创建起来。
create spfile from pfile= '${ORACLE_BASE}/admin/${ORACLE_SID}/init${ORACLE_SID}.ora
完整步骤见/9i/createdb.sh，编辑如pfile,数据文件目录等参数，以符合实际情况，并转移到$ORACLE_BASE/admin/oradb/create/下，执行。
2.2.2 创建用户表空间
Oracle 9i对于表空间管理一个明显的变化是改数据字典管理（extent management dictionary）为表空间本地管理（extent management local），还可以根据建立的数据对象对空间的要求自动确定扩展块的大小（autoallocate），最小为64K，这两项都是创建表空间的缺省选项。
create tablespace tbsdata datafile '…' [ extent management local ] [ autoallocate ];
而对于指定每个扩展块大小的创建策略，设立了新选项：统一扩展块大小（uniform [size xxx[K|M]]），可覆盖autoallocate选项，如果不加上具体的size xxx[K|M]，缺省为1M，这样就不必考虑Oracle 8i中的如initial,next,pctincrease,maxextents等default storage参数应如何组合，事实上Oracle 8i的这些设置原本就没有什么意义。
不能够同时指定extent management local和default storage，换言之，default storage只能和extent management dictionary一起显式指定。
如果未指定extent management的类型，Oracle 9i缺省使用local方式，如果又同时使用default storage选项，就有以下的判断：
如果使用minimun extent，Oracle检查是否minumum extent=initial=next且pctincrease＝0,如是，Oracle使用uniform选项，size=initial；如不是，Oracle忽略指定选项，使用autoallocate。
如果未指定minimum extent，Oracle检查是否initial=next且pctincrease＝0,如是Oracle使用uniform选项，size=initial；如不是Oracle忽略指定选项，使用autoallocate。
为了避免与Oracle 8i的习惯做法混淆，建议只使用Oracle 9i较简洁的方法。
对于存储少量静态数据的表空间来说，如配置信息等，可简单地写为：
create tablespace tbsdata datafile '…';
对于必须关心其扩展块大小的表空间，如大批量的记录或索引，可简单地写为：
create tablespace tbsdata datafile '…' uniform size 10M;