Oracle数据库管理知识
一、数据库服务器组成：由实例和数据库组成，可以为一对一关系，也可以多对一关系（RAC集群）。
一）、实例（instance）--管理数据库，通过它进入和管理数据库，由系统全局区（SGA）和后台进程组成
$ipcs –m 查看SGA大小
1、系统全局区（内存结构）：
>show parameter sga sga_max_size
开启例程时自动分配，关闭时释放内存。每个后台进程都共享
1）共享池-shared_pool-存放最近的SQL语句和数据字典。由shared_pool_size定义
库缓存区-library cache-存放SQL、PL/SQL语句上下文，解释代码值和执行计划 (变量绑定可优化)
数据字典缓存区-dict cache-表、列的定义信息、用户、权限信息
动态改变共享池的大小
>alter system set shared_pool_size=60M;
2）数据高速缓存-database buffer cache
>show parameter db_ 标准数据块大小 8k
工作原理：通过使用LRU（Least Recently use）-最近最少使用，将不用的和少用缓冲区都移到队列最后，腾出空间。
3）重做日志高速缓存-redo log buffer
4) 大池-large pool--辅助I/O，主要在备份的时候用到
5)java池-java pool--提供java支持
6）流池-stream pool--将整个数据库复制到另一个主机上。应用很大，如一机器专门读，另一台专门写，读写分开提高性能
2、后台进程(必须有的5个，少一个实例都不能启动）ps-ef|grep ora
查看后台进程：
>desc v$bgprocess
>select name,paddr from v$bgprocess where paddr<>'00';
查看用户会话进程信息：
>desc v$session
>select username,sid,serial#,machine,program from v$session;
服务器进程：监控用户进程，负责为用户进程与实例，数据库打交道。
用户进程：启动一个会话 用户进程-服务器进程-实例-数据库
1）SMON-系统监视器--主要用于例程恢复，合并空间碎片，释放临时段。负责前滚和回滚。重新应用写入重做日志文件但没写入数据文件的数据和回滚未提交的数据。
2)PMON-进程监视器--监控服务器进程，并在服务器进程失败时清除该进程，回退用户的事务，释放服务器进程的锁和释放资源。
3)DBWR-数据写进程--将在数据缓存中的脏缓冲区数据写入数据文件中，最多可开10个进程在特定条件下才写进数据文件。
系统发出检查点（checkpoint）时-- 保持数据文件，控制文件，日志文件SCN的同步
服务器进程不能找到空闲缓冲区时
删除或截断表时
表空间只读时
开始备份表空间时-alter tablespace ...begin backup
使表空间正常脱机时
RAC中实例间的同步时
4)LGWR-日志写进程--将重做日志缓存区的更改信息全部写入重做日志文件中。写入的情况：
提交事务时
每隔3秒
在DBWR将脏数据写入数据文件前
缓冲区已有三分之一填满时
重做信息超过1M时
5)CKPT-检查点进程--发出检查点时，会同步数据文件和控制文件和重做日志文件，将SCN分别写入数据文件和控制文件的头部，同时促使DBWR将脏数据写入数据文件，LGWR也会将重做缓冲区信息写入重做日志文件。确保数据文件、控制文件、重做日志文件保持一致.开始工作的情况：
日志切换时
关闭例程时（除shutdown abort外）
手工发出—alter system checkpoint
修改参数强制发出时fast_start_mttr_target
二）、数据库（database）--存储物理数据文件，包括数据文件、控制文件、重做日志文件（必有），还有参数文件，口令文件，归档日志文件。
数据文件存放位置:
/u01/app/oracle/oradata/orcl/，存放数据文件、控制文件、重做日志文件。
$ORACLE_HOME/dbs：参数文件spfileorcl.ora，口令文件orapworcl
oracle中使用逻辑结构管理物理结构文件
必不可少的文件：
控制文件（control file）:二进制文件，存放所有物理结构信息，包括数据名、数据库标识，数据库创建时间，当前日志序列号和检查点信息，表空间的信息，数据文件信息，重做日志文件信息，备份信息等。
数据文件（datafiles）：存放数据文件
重做日志文件（redo log files）:录像带，记录数据库事务改变信息，主要用来数据库的恢复。每个状态改变都有一个SCN号（commit时改变），SCN是数据库里的时间戳。先写日志文件再写数据文件。
参数文件：启动数据库首先读取的文件，包括实例名，数据库名，诊断文件，数据块大小，还原表空间，数据高速缓冲区大小，控制文件位置，大池，java池大小，还原段保留时间
口令文件：主要是管理数据库管理员口令的，远程登陆数据库认证
归档日志文件：当数据库运行在归档模式下时，会将重做日志文件轮流进行副本备份存放，即归档日志文件。对于恢复很重要。
三）数据库逻辑结构：
database
tablespace datafile
segment
extent
block os block
1）数据逻辑上时存放在表空间里，物理上存放在数据文件里。
2）tablespace--表空间由段组成，段即是实际存放数据的结构，一般有表段，索引段，还原段，临时段。一个表空间可有多个段。一个表空间可以有多个数据文件。
3)datafile--存放在表空间里，由区分配组成。一个数据文件只能属于一个表空间。
4）segment--给段分配空间是按照区来分配的，由多个区组成。
5)extent--区有连续都块组成，空间分配的逻辑单元
6） block--块是数据执行I/O操作的最小单位，大小为OS BLOCK块大小的整数倍。允许不同表空间使用不同的块。
二、DBA TOOLS
安装工具：
软件安装
建库
一）OUI-ORACLE UNIVERSIAL INSTALLER
二）SILENT（静默模式安装方式）-非图形界面后台安装。
三）DBCA-DataBase Configuration Assistance --图形界面管理库和产生和管理库模板，建立、删除管理数据库
GUI建库
手工建库--通过命令create database建立。
四）OEM-Oracle Enterprise Manager(企业管理器)--可以作任何事，图形界面管理工具。DBA管理很有用。
五）sqlplus
六）net manager-网络配置管理工具，配置监听器等。
七）OFA-Optimal Flexible Architecture最优体系灵活结构--安装oracle规范化的目录设置标准。
提供两个目录
$oralce_base/admin-管理目录，admin/实例名/存放一些后台进程文件，用户跟踪文件等
$oracle_base/oradata-数据文件存放目录 oradata/实例名/存放数据文件、控制文件、重做日志文件。
远程登录口令验证：
#xhost +
#su - oracle
$netmgr 选local-listener-database service 改Global databa name:orcl sid:orcl
或者改：/u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora中
GLOBAL_DBNAME = orcl，SID_NAME = orcl
$lsnrctl stop
$rm /u01/app/oracle/product/10.2.0/db_1/dbs/orapworcl
$orapwd file=/u01/app/oracle/product/10.2.0/db_1/dbs/orapworcl password=david entries=5
$lsnrctl start
$sqlplus /nolog
>conn sys/ddd@orcl as sysdba --报错ORA-01017: invalid username/password; logon denied
>conn sys/david@orcl as sysdba
三、管理实例instance
一）参数文件：实例启动首先读取参数文件,读取顺序：spfile.ora--spfile.ora--init.ora—init.ora
1）pfile-文本参数文件：可编辑,init.ora; >create pfile= from spfile=;
>select status from v$instance;
更改参数值：
>alter system set parameter=value scope=both(default)|memory|spfile [SID]（RAC中使用）
both-当即生效且更改spfile中值。
memory-当即生效，实例重启后还原
spfile-实例重启后才生效，修改静态参数
>show parameter spfile --value有值表示实例是以spfile启动。
>startup pifle=$ORACLE_HOME/dbs/initorcl.ora --以pfile启动。
>create spfile from pfile; --以pfile启动只能生成spfile。
2）spfile-二进制文件,spfile.ora，不可手工编辑.
OMF oracle manage file--大型数据库一般不用。
二）启动和关闭实例
四、启动和关闭数据库
一）启动
open database
start mount
start nomount
shutdown
1）start nomount--a、读取参数文件，b、启动实例(分配SGA，启动后台进程)，c、打开跟踪文件和报警文件.可以产生数据库，建库;重建控制文件
2）start mount --读取控制文件，载入数据库结构。
可以对数据文件改名，移动，表空间备份恢复，将数据库从非归档模式归档模式。
alter database mount
select name from v$datafile;
select * from v$logfile;
3）open--做一致性检查，检查数据文件，控制文件头部的SCN号是否一致，如果一致就打开。
4）startup restrict 将数据库打开在受限方式下
或启动后
>startup
>alter system enable restricted session;
>alter system disable restricted session;将受限模式关掉。
二）、关闭数据库（结合商店关门，保安对待顾客方式理解）
shutdown normal 不允许新的会话接入，等待所有用户都退出会话后关闭。
transactional 等到所有事务执行完毕才关闭。
immediate 回滚当前事务，马上关闭
前三种属于正常关闭，不需要恢复
abort 等同于掉电，启动时需要实例恢复。
startup force=shutdown abort && startup
五、诊断文件和跟踪文件
>show parameter dump
$oracle_base/admin/orcl/
adump:审计跟踪文件
bdump:后台跟踪文件和警告文件：后台进程跟踪文件、报警文件alert_orcl.log
udump:用户跟踪文件：用户进程跟踪文件，SQL跟踪
> show parameter sql_
>alter session set sql_trace=true/false;启用/关闭会话一级SQL用户跟踪
$tkprof ****.trc aa（filename） 在udump下生成一个aa.prf文件。
多种安装oracle 及建库方法：
1.OUI install --OUI can install oracle,create database
also it can install other oracle software
root
1)#fdisk /dev/sda n +10g w
#partprobe
#mkdir -p /u01/app
#vim /etc/fstab
add:
/dev/sda6/ /u01/app ext3 defaults 0 0
#reboot
1)
binutils-2.15.92.0.2-13.EL4
compat-db-4.1.25-9
compat-libstdc++-296-2.96-132.7.2
control-center-2.8.0-12
gcc-3.4.3-22.1.EL4
gcc-c++-3.4.3-22.1.EL44
glibc-2.3.4-2.9
glibc-common-2.3.4-2.9
gnome-libs-1.4.1.2.90-44.1
libstdc++-3.4.3-22.1
libstdc++-devel-3.4.3-22.1
make-3.80-5
pdksh-5.2.14-30
sysstat-5.0.5-1
xscreensaver-4.18-5.rhel4.2
setarch-1.6-1
libaio-0.3.103-3
2)
vi /etc/sysctl.conf
kernel.shmall = 2097152
kernel.shmmax = 194304000 （bytes） --usually half of physical memory
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default=262144
net.core.rmem_max=262144
net.core.wmem_default=262144
net.core.wmem_max=262144
/sbin/sysctl –p --take into effect without reboot
3)
vi /etc/security/limits.conf
oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536
vi /etc/pam.d/login
session required pam_limits.so
4)
mkdir /u01/app/oracle
5)
#groupadd dba
#groupadd oinstall
#useradd -g oinstall -G dba -m oracle
#passwd oracle
6)chown -R oracle.oinstall /u01/app/oracle
chmod -R 775 /u01/app/oracle
su - oracle
1)
vi .bash_profile
TMP=/tmp;export TMP #TMP directory is used to store the oralce software
TMPDIR=$TMP;export TMPDIR
ORACLE_BASE=/u01/app/oracle;export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1;export ORACLE_HOME
ORACLE_SID=orcl;export ORACLE_SID --多个例程情况下不用设置
LID_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
PATH=$ORACLE_HOME/bin:$PATH; export PATH
source .bash_profile
2)
unzip 10201_database_linux32.zip -d /tmp/oracle
root
#xhost + 给非根用户或远程登陆用户开启软件图形界面。
3)
install software
$./runInstaller
choose oinstall
2 silent-静默安装方式 install oracle software
1)
rm -rf $oracle_base/*
rm -rf /etc/oratab
/oraInc
rm -rf /usr/local/bin/*
2)#tar zxvf /opt/database10G.tar.gz -C /u01/app（/home/oracle）
#chown - R /u01
$su - oracle
$cp /u01/app/database/response/enterprise.rsp ../
$vim enterprise.rsp
UNIX_GROUP_NAME="oinstall"
FROM_LOCATION="/u01/app/database/stage/products.xml"
ORACLE_HOME="/u01/app/oracle/product/10.2.0/db_1"
ORACLE_HOME_NAME="oracle"
n_configurationOption=3 --only install oracle software
3)#su - oracle
$cd database/
$./runInstaller -responsefile /u01/app/database/enterprise.rsp -silent
$exit
#cd /u01/app/oracle/product/10.2.0/db_1/
#./root.sh
#su - oracle
$dbca --DBCA create database
tick:Create database creation scripts: /u01/app/oracle/admin/orcl/scripts
3 手工建库：manual create database
1)#su - oracle
$sqlplus / as sysdba
>shutdown immediate
>quit
$cd $ORACLE_BASE
$rm -rf oradata/orcl/*
$cp $ORACLE_BASE/admin/scripts/init.ora $ORACLE_HOME/dbs/
$mv $ORACLE_HOME/dbs/init.ora $ORACLE_HOME/dbs/initorcl.ora
$orapwd file=$ORACLE_HOME/dbs/orapworcl password=oracle entries=5
$splplus / as sysdba
>startup nomount
>@CreatDB.sql --即是数据库建库命令脚本 建库的时候根据参数文件建立控制文件
同时会运行sql.bsd创建数据字典基表
>alter database open
>@$ORACLE_HOME/rdbms/admin/catalog.sql -- 创建数据字典视图
>@$ORACLE_HOME/rdbms/admin/catproc.sql -- make PL/SQL package
>conn system/manager
>@$ORACLE_HOME/sqlplus/admin/pupbld.sql –
安装product_user_profile控制产品安全表
>@scott.sql --生成scott用户
动态注册，可选
2)config listener prepared for installing OEM
$cp $ORACLE_HOME/network/admin/sample/listener.ora $ORACLE_HOME/network/admin/
$vim $ORACLE_HOME/network/admin/listener.ora
modify following:
# LISTENER =
# (ADDRESS_LIST=
# (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))
# (ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY)))
# SID_LIST_
# List of services the listener knows about and can connect
# clients to. There is no default. See the Net8 Administrator's
# Guide for more information.
#
# SID_LIST_LISTENER=
# (SID_LIST=
# (SID_DESC=
# #BEQUEATH CONFIG
# (GLOBAL_DBNAME=salesdb.mycompany)
# (SID_NAME=sid1)
# (ORACLE_HOME=/private/app/oracle/product/8.0.3)
# #PRESPAWN CONFIG
# (PRESPAWN_MAX=20)
# (PRESPAWN_LIST=
# (PRESPAWN_DESC=(PROTOCOL=tcp)(POOL_SIZE=2)(TIMEOUT=1))
# )
# )
# )
# PASSWORDS_
AS
LISTENER =
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))
(ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY)))
# SID_LIST_
# List of services the listener knows about and can connect
# clients to. There is no default. See the Net8 Administrator's
# Guide for more information.
#
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
# #BEQUEATH CONFIG
(GLOBAL_DBNAME=orcl)
(SID_NAME=orcl)
(ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1)
# #PRESPAWN CONFIG
(PRESPAWN_MAX=20)
(PRESPAWN_LIST=
(PRESPAWN_DESC=(PROTOCOL=tcp)(POOL_SIZE=2)(TIMEOUT=1))
)
)
)
3)OEM install
$sqlplus / as sysdba
>select comp_name,version from dba_registry; --查看组件安装
$lsnrctl start
$emca -repos create (emca -h help) --to create repository which store OEM
Database SID: orcl
Listener port number: 1521
Password for SYS user:oracle
Password for SYSMAN user:oracle
Password for SYSMAN user:oralce
$emca -config dbcontrol db -- to create dbconfig file
Database SID: orcl
Listener port number: 1521
Password for SYS user:oracle
Password for SYSMAN user:oracle
Password for SYSMAN user:oralce
$emctl start dbconsole
$emctl status dbconsole
http://192.168.0.28:1158/em sys oracle sysdba
4)See also: how to remove OEM
$sqlplus / as sysdba
>select comp_name,version from dba_registry;
$emca -deconfig dbcontrol db -- to remove dbconfig file first
Database SID: orcl
Listener port number: 1521
Password for SYS user:oracle
Password for SYSMAN user:oracle
Password for SYSMAN user:oralce
$emca -repos drop --to remove repositery which store OEM
Database SID: orcl
Listener port number: 1521
Password for SYS user:oracle
Password for SYSMAN user:oracle
Password for SYSMAN user:oralce
6.13
一、搭建oracle服务器，规划
www.tpc.org --对每台主机硬件测试，提供oracle服务器搭建技术支持
用户数量
考虑容量：软件系统放哪，用户数据文件、索引文件如何放，备份存贮位置和方式
数据库字符集
建库 临时表空间......
控制文件，日志文件存放策略。
数据库名字的规划。与客户沟通
关键要跟应用开发人员沟通，决定那些需要安装。
二、数据字典--属于sys用户，管理所有数据库的所有信息。对DBA非常重要非常有用，
通过它可以获取数据库所有信息。不需要强行记，多做，记住主要的、经常用的。
通过DDL语句修改数据字典信息.
dictionary--包含了所有数据字典
两大类：
1、数据字典表--只是一个视图，是基于基表创建的视图，数据字典基表在创建数据库时创建
DBA_ 查看所有对象，只有具有DBA身份才能查看
ALL_ 查看用户所拥有的schema对象
USER_ 只能查看用户自身具有对象信息。
desc dict(dictionary)
>select table_name from dict where table_name like 'DBA_CON%';模糊查询约束。
比较重要的数据字典表：dba_tablespaces,dba_data_files,dba_segments
2、动态性能表（Gv$（多实例用） v$开头）--动态变动，表示数据库所有的活动，数据源：内存（实例和参数文件）和控制文件。
分为累计和瞬间状态。主要用来性能调优多。
v$fixed_table-包含了所有的动态性能视图。
>desc v$fixed_table
>select * from v$fixed_table where name like 'V$%'; --X打头的表示基表。
经常用到的： v$instance
v$database 每个数据库都有一个唯一的DBID，CURRENT_SCN，FLACHBACK_ON（10G开始才有）
v$controlfile,v$datafile,
>select * from v$instance;
>select dbid,log_mode,open_mode,database_role,current_scn,flashback_on,db_unique_name from v$database;
三、控制文件--数据库里很小的二进制文件,记录和维护数据库的物理结构。
在mount状态下读入。只能跟一个数据库有关联。位置和名字由参数文件决定。
创建数据库时根据参数文件创建。
文件是一模一样的。一般冗余放置在不同磁盘上，以恢复使用。
包含的内容：数据库名称，数据文件名称和位置,重做日志文件名称和位置，表空间名称，当前日志序列号，检查点信息SCN,RMAN记录信息
>alter session set events 'immediate trace name controlf level 12'; 跟踪控制文件
>alter database backup controlfile to trace; udump--orcl_ora_5650.trc
>select p.spid from v$session s,v$process p where s.paddr=p.addr and s.username='SYS';
spid-系统进程号5650
>show parameter control --通过参数文件信息读取控制文件相关信息。
如何移动恢复控制文件。读取参数文件顺序：spfileSID.ora-spfile.ora-initSID.ora-init.ora
若前三个参数文件都丢失，可到 $oracle_base/admin/SID/pfile/init.ora.******，将其复制到$ORACLE_HOME/dbs/
>create pfile from spfile; 备份spfile参数文件
>alter system set control_files='/u01/app/oracle/oradata/orcl/control01.ctl' scope=spfile;
>shutdown immediate
$rm -rf
/u01/app/oracle/oradata/orcl/control02.ctl /u01/app/oracle/oradata/orcl/control03.ctl
>startup
>shutdown immediate
$cp control01.ctl control02.ctl control03.ctl
$rm spfileorcl.ora
>startup --此步数据库将以参数文件PFILE（initorcl.ora）启动例程。
相关动态性能视图：v$controlfile v$controlfile_record_section
从控制文件里读数据的动态性能视图：v$database v$datafile v$tempfile v$tablespace v$log V$logfile
v$loglist v$backup v$archived_log
四、联机重做日志文件--redo log file :唯一用途-恢复，以事务的条目记录数据库所有的改变。恢复时重演。
至少要有两个日志组，每个组最少1个成员。
最大日志组数16,每个组最多有3个成员。
>select * from v$log; --查看日志组信息。
>select * from v$logfile; --查看日志成员的信息。
日志切换：sequence#序号，每个日志组有相同的的成员（组冗余），写完一组日志切换写另一组，在非归档模式下轮流覆盖写入。
归档日志文件：对日志文件做副本
写进程：LGWR 将相同的日志写入日志成员。
手动切换：
>alter system switch logfile; current -当前使用 active --还有脏数据还没写入数据文件，inactive
>alter system checkpoint; -转换日志状态，将active改为inactive,执行这步会将脏数据写入数据文件。
添加日志组：
>alter database add logfile group 4
>'/u01/app/oracle/oradata/orcl/redo04.log' size 100M;
>select * from v$logfile;
添加日志成员：组成员最好放不同磁盘上，方便恢复。
>alter database add logfile member
>'/u01/app/oracle/oradata/orcl/redo01a.log' to group 1;
删掉日志组：
>alter database drop logfile group 2; --注意删除非活动日志组，但物理日志成员文件没删，需要手动删除。
可以在mount的状态下删除。
删除日志成员：
>alter database drop logfile member '/u01/app/oracle/oradata/orcl/redo03.log';
对日志文件进行初始化：（日志已坏也可用）
>alter database clear logfile '/u01/app/oracle/oradata/orcl/redo03.log';(非活动成员)
>alter database clear logfile group 2;
alter database clear unarchived logfile ***
五、datafile和tablespace
数据文件和表空间紧密相关。数据文件的创建依附表空间的创建而创建。
一个表空间可以有多个数据文件组成
一个数据文件只能属于一个表空间
表空间按照段来分配，段按照区分配，而不用块分配，这样性能会比较好。块是I/O的最小单位。
表空间创建：
>create tablespace test datafile
>'/u01/app/oracle/oradata/orcl/test01.dbf" size 5m;
>select * from v$tablespace;
看数据文件和表空间关系：
>desc dba_data_files
>select file_name,tablespace_name from dba_data_files;
空间管理：
1）数据字典管理（extent management dictionary）：
通过数据字典管理（8I以前的方式），引起争用，会经常改变数据字典表，要跟踪合并空间碎片。
2）本地管理(extent management local)：在每个数据文件头，用位图来管理空间，维护空闲区的信息。
在表空间为用户分配空间. 9I后缺省管理方式
分配空间的方式：根据对象相应的分配大小。
1）自动分配：autoallocate，oracle自己分配。第一次分64K，分16次左右，16以后1M..8M..64M...64M
2) 统一分配：uniform,给定一个特定的值。
>select tablespace_name,extent_management，allocate_type from dba_tablespaces;
>create tablespace test datafile
>'/u01/app/oracle/oradata/orcl/test01.dbf" size 5m
>extent management local --指定本地管理
(>autoallocate) --自动分配
>uniform. size 1m ; --统一指定分配区大小,当创建一张表时首先分配1M大小。
表空间分类：
1）系统表空间：system,sysaux（10G前没有，主要把SYSTEM表空间一些功能东西迁移到此，比如OEM的数据仓库，awr快照信息便于管理）
2）非系统表空间：
用户自定义的表空间
UNDO表空间：存放前映像数据，用来回滚。oracle特有
TEMP临时表空间：排序，内存空间不够到此排序。
>select tablespace_name,extent_management，allocate_type,contents from dba_tablespace;
创建UNDO表空间
>create undo tablespace undo2
>datafile '/u01/app/oracle/oradata/orcl/undo2.dbf‘ size 10m;
创建临时表空间：
>create temporary tablespace temp02
>tempfile '/u01/app/oracle/oradata/orcl/temp02.dbf"size 10m;
看系统临时表空间：
>desc database_properties;
>select property_name,property_value from database_properties;
改变缺省临时表空间
>alter database default temporay tablespace temp2;
改变表空间的状态：
>alter tablespace test offline; --使表空间脱机，维护表空间。
>alter tablespace test online;
>alter tablespace test read only; --只读，可以删除（drop...purge）对象。删的是数据字典里内容
>alter tablespace test read write;
删除表空间：
>drop tablespace test; --物理文件仍然存在
>desc user_tablespaces
>select table_name,tablespace_name from user_tablespaces where table_name like ..
>drop tablespace test including contents and datefiles [cascade constraints];--连同物理文件和约束删除。
改变表空间大小：
自动扩展
增加数据文件个数
增大数据文件大小
>create tablespace tt datafile 'u01/app/oracle/oradata/orcl/t01.dbf' size 2m;
>alter tablespace tt add datafile 'u01/app/oracle/oradata/orcl/t02.dbf' size 2m;--添加数据文件
>alter database tt datafile 'u01/app/oracle/oradata/orcl/t01.dbf' resize 4m;-改变数据文件大小
开启自动扩展：
>desc dba_data_files; autoextensible 最好设置个最大值，一般不建议打开。
>select file_name,bytes,autoextensible from dba_data_file;
>alter database datafile 'u01/app/oracle/oradata/orcl/t01.dbf' autoextend on next 10m maxsize 1G;
一、移动数据文件方法：
1)通过表空间移动：（数据库打开状态下）
alter tablespace ts_name RENAME DATAFILE ..........TO ...........;
>alter tablespace users offline;
>host
$mv $ORACLE_BASE/oradata/users01.dbf $ORACLE_BASE
>alter tablespace users rename datafile '$ORACLE_BASE/oradata/users01.dbf'
> to '$ORACLE_BASE/users01.dbf';
>alter tablespace users online;
2）通过命令：ALTER DATABASE RENMAE FILE '......' to '.........';(mount状态)
>shutdown immediate
>host mv $ORACLE_BASE/oradata/users01.dbf $ORACLE_BASE/users01.dbf 或
>host
$mv $ORACLE_BASE/oradata/users01.dbf $ORACLE_BASE/users01.dbf
>startup mount
>alter database rename file '$ORACLE_BASE/oradata/users01.dbf'
> to '$ORACLE_BASE/users01.dbf'; (改变控制文件指向数据文件的指针)
>alter database open;
二、
大文件与表空间是一对一关系，即是大文件表空间一般只有一个文件。小文件表空间中一个表空间可以有多个文件。
>desc database_properties
>select property_name,property_value from database_properties;
查看缺省临时表空间，缺省永久表空间，缺省表空间类型（大文件还是小文件），字符集等等
创建大文件表空间：
>create bigfile tablespace bigfile_ts datafile
>'/u01/app/oradata/bigfile_ts.dbf' size 10m;
查看
>select tablespace_name,bigfile from dba_tablespaces;
改变数据库缺省表空间
>create tablespace t datefile '/u01/app/oradata/t01.dbf' size 10m;
>alter database default tablespace t;
>alter tablespace t add datafile '/u01/app/oradata/t02.dbf' size 10m;
>alter tablespace t drop datafile '/u01/app/oradata/t02.dbf';删除表空间里的空数据文件
表空间改名：
>alter tablespace t RENAME TO tt;
使用缺省表空间的原因：创建用户默认缺省，不用手工指定。
>create user test identified by test;
>desc dba_users;
>select username,default_tablespace from dba_users;
查看表空间的信息：dba_tablespaces,v$tablespace
查看数据文件信息：dba_data_files,v$datafile,v$tempfile
三、段和数据块
1、段-segment 一般跟创建的对象联系在一起，一般跟对象名字一样。
1）有表段：堆表-无序的，只放在一个表空间里
分区表（partition table）-一个表可以放在多个表空间，按照时间或范围存在同一个表空间。
簇表（cluster table）--在做连接的时候，把连接表的连接数据放在一个块里，提高性能。
2）索引段 3）undo段 4）临时段
5）索引组织表（IOT）--索引跟表组织在一起，用索引组织一张表。6）嵌套表 7）启动表
>desc dba_segments (课后实践，段在表空间时怎么存放的)
>select segment_name,segment_type,tablespace_name,bytes,blocks,extents from dba_segments;
>desc dba_extents 对建立有索引的表，表和索引的块存放是不连续的，索引-表-索引-表。一个区有8个块。16个区后
一个区有128个块
>select segment_name,tablespace_name,extent_id,file_id,block_id,blocks
>from dba_extents where tablespace_name='..'
2、数据块-ORALCE最小I/O单元。是OS块的整数倍，设置表空间的时候生成（blocksize定义表空间的块）。
db_block_size-设置数据库标准块大小，总共有5个块尺寸2、4、8、16、32,8K以外的给非标准块使用
块是怎么组织的：
块头-（从上往下写），记录块的物理地址，表的目录，一般情况都是只有一张表，行目录，事务草（t_slot），大小为84-107字节。
数据（从下往上写），与块头之间时空的
1)pctfree（percent free）--留空用来数据更新，防止行迁移，10%
>conn scott/tiger
>create table t(c char(1)) pctfree 0;
>begin
for i in 1..5000 loop
insert into t values('a');
end loop;
commit;
end;
>select dbms_rowid.rowid_block_number(rowid) b,count(dbms_rowid.rowid_row_number(rowid))
from t group by dbms_rowid.rowid_block_number(rowid);
一个块最多可以放733行。行结构：行地址、列长度、列值
2)pctused--当数据低于此值时，数据块又可以重新插入数据 （一般为40%）
设置并发：
3)initrans(1)--
4)maxtrans(255)--
段的空间如何管理：两种方式
bitmap:在段头上记载块的使用情况 在创建表空间的时候可指定segment space management auto
free list(以前方式):手工设定pctfree pctused
查看逻辑结构信息：dba_extents dba_free_space(表空间还有多少空闲区)，
dba_segments（查看表空间有哪些表段）,dba_tablespaces
>select tablespace_name,segment_space_management from dba_tablespaces;
创建非标准块表空间：
改SGA
>select * from v$sgainfo; SGA分配给内存使用是以区组（granule）分配的
>alter system set sga_max_size=172m scope=spfile; 改静态参数，改变oracle启动时从物理内存取的内存大小。
sga_target实际用的内存
必须首先打开db_16k_cache_size
>alter system set db_16k_cache_size=8m scope=spfile; 在内存中划8M缓存给16K的块大小使用
>startup force
>create tablespace t16 datafile '/u01/app/oracle/oradata/orcl/t1601.dbf' size 10m;
>select block_size,tablespace_name from dba_tablespaces;
四、undo--存放修改数据的旧数据。作用：1）回滚撤销使用 2）读一致性（一个用户在修改，另一个用户读旧数据）
3）恢复 4）闪回（flashback）,根据之前操作做相反操作
以前没有undo表空间管理回滚段的。
一个数据库可以有多个UNDO表空间，但同一时间只能有一个在用。
如何配置回滚段：
>show parameter undo --undo_management(auto|manual),undo_retention(回滚段保留时间),
undo_tablespace(设置当前使用表空间)
undo_retention的设置，大量回滚段时，会出现报错：ora-01555快照太老，会将旧回滚段覆盖，解决办法将UNDO表空间增大。
>select tablespace_name,contents from dba_tablespaces;
>alter system set undo_tablespace='undo2'; 动态参数，可马上生效。
>select * from v$transaction;
>commit; v$transaction表为空。
删除UNDO表空间
>alter system set undo_tablespace='UNDOTBS1';将在用UNDO表空间设为不用
>drop tablespace undo2; 表空间与数据库文件脱离关系
>host rm $ORACLE_BASE/oradata/orcl/undo201.dbf;
查看UNDO信息:dba_rollback_segs v$rollname v$session v$undostat v$transaction
五、表 与段对应，表逻辑存储在段里。1）用户定义表 2）内置表
1）ROWID:伪列，行的绝对地址。扩展rowid和有限rowid
扩展rowid的结构：对象号（6）文件号（3）块号（6）行号（3）--OOOOOOFFFBBBBBBRRR基于64位。
如何转换rowid为可读懂格式
>desc dbms_rowid 查看包的结构
>select dname,dbms_rowid.rowid_object(rowid)object_no,dbms_rowid.rowid_relative_fno(rowid)file_no,
dbms_rowid.rowid_block_number(rowid)block_no,
dbms_rowid.rowid_row_number(rowid)row_no from dept;dbms_rowid包名，接过程名
select file_id,file_name from dba_data_files --文件号
select object_name from dba_objects where object_name='DEPT'; --对象号
select file_id,segment_name from dba_extents where segment_name='DEPT'; --块号
2）行的结构：行头 列长度 列值
行头：记录多少列以及锁信息，链接信息。
移动表：
>alter table d move tablespace te;
HWM-high water mark 高水位标记，oraclE全表扫描忽略空直接读到高水位，这时就要考虑空间碎片整理。
>conn scott/tiger
>desc user_tables
>create table e as select * from emp;
>insert into e select * from e; --多次执行产生大量数据
>analyze table t compute statistics;分析表，
>select table_name,blocks,empty_blocks from user_tables;看有没有空块
BLOCKS显示的是高水位，empty_blocks显示的是空余块。
>delete e where deptno=30; 删除大量数据
>analyze table t compute statistics;数据改变后再次分析
>select table_name,blocks,empty_blocks from user_tables; empty_blocks没变化
>alter table t enable row movement; --设置允许行迁移 10G开始有
>alter table t shrink space; --移动高水位标记,即是回收不用空间（缩水）,回收一个区（8块）。
>analyze table t compute statistics;
>select table_name,blocks,empty_block from user_table;发生变化，空间回收
delete 并不能改变高水位。
要会建表空间和添加数据文件
一、外部表
1)建立一个文件 ext.txt
vim ext.txt 添加
50,XXXX,shanghai
60,YYYY,beijing
2)
>create diretory ext_dir as '/tmp/oracle'; --/tmp/oracle目录需要首先建立
>grant read,write on directory ext_dir to scott;
>conn scott/tiger
>create table ext_dept(num number(4),dname varchar(20),loc varchar(20))
organization external(type oracle_loader default directory ext_dir
access parameters
(records delimited by newline
field terminated by ',')
location('ext.txt')
);
>select * from ext_dept;
现在外面多为应用集成，数据集成，通过 oracle将平面文件、外部数据（sqlserver db,sybase...）集成进来。--高级应用
CLOB--针对字符大文件
BLOB--针对二进制大文件，图片音视频等
二、索引
逻辑分类：单列索引 多列索引（组合索引），次序很重要，因为索引经常出现在where条件里。
唯一索引（建立了主键后oracle自动为其建立一唯一索引） 非唯一索引（索引里不能包含空值）
基于函数的索引
基于域的索引 反键索引-（7396 6937）
物理分类：B-TREE索引-用得最多。
位图索引
分区索引(partition)
B-TREE索引：二叉树，根-分支-叶-条目（header:length:value:rowid)，叶之间是双向连接的。
header-包含行的数目,锁信息
length-列长度
索引要看高度，跟I/O有关。
B-TREE索引 对select最有意义，对INSERT影响不大，影响最大的是update，
位图索引：也是一个二叉树，但内容与B-TREE内容时不同的，基数-一列当中不相同的值的个数。FMFMFMMMF（男女）,建立位图索引后将是
1F-101010001 2M-010101110
重建索引（B-tree）：（在表更新或表更改表空间后，整理索引碎片）
>create table e as select * from emp;
>insert into e select * from e;(多次，插入大量数据)
>create index e_deptno_in on e(deptno);
>desc user_indexes
>select index_name,table_name,blevel,leaf_blocks from user_indexes where table_name='E';
>delete e where deptno=30;
>commit;
>select index_name,table_name,blevel,leaf_blocks from user_indexes where table_name='E';
>alter index e_deptno_in rebuild (online 可联机使用);(alter index e_deptno_in coalesce)；
>select index_name,table_name,blevel,leaf_blocks from user_indexes where table_name='E';
在同一张表不同列上建立位图索引和B-tree索引，比较
>conn / as sysdba
>alter database datafile '/u01/app/oracle/oradata/orcl/users01.dbf' resize 50m;
>conn scott/tiger
>create table m(no number,oe varchar2(1));
>begin
for i in 1..100000 loop
if mod(i,2)=1 then
insert into m values(i,'o');
else
insert into m valuse(i,'e');
end if;
end loop;
commit;
end;
/
>create index no_indx on m(no);
>create bitmap index oe_index on m(oe);
>select index_name,index_type,blevel,leaf_blocks from user_indexes where table_name='M';
说明基数越小，适合建位图索引，基数偏大，建B-tree索引
开启/关闭监控索引过程
>alter index in_name monitoring/nomonitoring usage;
查看索引有没有用到：v$object_usage
>create index emp_ename_idx on emp(ename);
>desc v$object_usage
>select * from v$object_usage;
>alter index emp_ename_idx monitoring usage; --开始监控
>select * from v$object_usage;
>select * from emp where ename='SCOTT';
>select * from v$object_usage; --used 为YES 说明查询走索引。
>alter index emp_ename_idx nomonitoring usage;
查看索引是否有效
>select index_name,status from user_indexes;(select object_name,status from user_objects where object_name='INDEXNAME';)
表移动需要重建索引使之有效。索引无效会导致性能下降
>alter table emp move;
>select index_name,status from user_indexes;
>alter index emp_ename_idx rebuild;
三、数据完整性约束
主键可以实现表每一行唯一非空。建议一张表要建立主键。外面碰到最大的问题是主外键。
四种情况：约束两种状态：enable disable -是否检查新进来数据。
对旧数据是否有效性检查：validate novalidate
enable validate(缺省)
enable novalidate --大量导入数据时，最好设置为novalidate
disable validate
disable novalidate
>conn scott/tiger
>create table e tablespace te as select * from emp;
>create table d tablespace te as select * from dept;
>alter table e add constraint e_empno_pk primary key(empno) using
index tablespace t16;将索引和表分开存放。
>conn / as sysdba
>select segment_name from dba_segment where tablespace_name='T16';
>alter table d add constraint d_empno_pk primary key(deptno)
using index tablespace t16;
>insert into e select * from emp where deptno=10; 违反主键约束，无法插入。
>alter table e disable constraint e_empno_pk;
>select constraint_name,status,deferred,validated from user_constraints; deferred用得不多.
>insert into e select * from emp where deptno=10; 插入成功
>alter table e enable validate constraint e_empno_pk; 报错，表已经有重复记录
>@$ORACLE_HOME/rdbms/admin/utlexcpt; 生成exceptions表，存放违反约束的数据rowid
>alter table e enable validate constraint e_empno_pk exceptions into exceptions;
将违反约束的数据存放到exceptions
>select ename,rowid from e where rowid in (select row_id from exceptions) for update;
Select语句中加入for update能锁定表以便更新
>update e set ename='' where rowid='';
>alter table e enable validate constraint e_empno_pk; 执行成功
四、使用概要文件（profile）管理口令和资源。
>select username,profile from dba_users;
>desc dba_profiles
>select profile,resouce_name,resource_type,limit from dba_profiles;
>alter profile default limit
failed_login_attempts=3;
>alter system set resource_limit=true;
>alter profile default limit
sessions_per_user=2;
>alter user scott profile scott_profile; --更改用户概要文件
>alter user scott profile default; --将概要文件改为默认。
>create profile scott.pro limit
idle_time 2 ;
>alter user scott profile scott.pro;
>select profile,resource_name,resource_type from dba_profiles where profile='SCOTT';
一、user-schema(对象的集合),创建用户后当一创建对象后马上创建schema
用户安全机制：
认证机制：oracle内部认证,数据字典授权，操作系统授权（conn / as），第三方认证
账户锁（accout locking）
资源限制(resource limit)：
缺省表空间(default tablespace)：
临时表空间(temporary tablespace,排序）
表空间配额(quotas)
权限(direct privilege)
角色(role privilege)
create user u_name identified [by password|externally(操作系统认证)]
default tablespace ts_name
temporary tablespace temp
quota [0|K|m|g|unlimited] on ts_name
password expire(用户第一次登录要改密码)
account [lock|unlock]
profile prof_name;
创建系统认证用户
>show parameter os; os_authent_prefix ops$向前兼容
>alter system set os_authent_prefix='' scope=spfile; --静态参数需要重启数据库
>start force
>create user app identified externally;
>select username,password from dba_users;
>grant create session to app; --授权，建议权利最小化
#useradd app
>create user user01 identified by user01
default tablespace te
temporary tablespace temp
quoto 2M on users
password expire;
>grant create session,create table to user01;
>alter user user01 quota 0 on users; --回收配额，但是对于已经创建的表可以插入数据。
查看用户占据表空间配额信息：dba_ts_quotas;
>alter user scott quota 0 on users; --限制scott用户在users表空间配额为0
>conn scott/tiger
>create table t(id,number); --还能创建
原因：
>select * from dba_sys_privs where grantee='SCOTT'; privilege为unlimited tablespace
回收：
>revoke unlimited tablespace from scott;
>create table t(id,number); --报错
二、权限
系统权限--用户能在数据库里能干什么（有一百多种）
对象权限--对对象具有什么操作权限（只有八九种）
sysoper --startup shutdown alter database open|mount recover database
alter database archievelog alter database backup controlfile to
restricted session
sysdba --privilege of sysoper with admin option
create database
alter tablespace begin/end backup
不完全恢复
系统权限：
>show parameter o7_dictionary_accessibility; --此参数设置sys用户连接登录时是否要指明as sysdba，true不用接。
授权时带with admin option --被授权也有权利授权给其他用户。
>grant create any index to scott with admin option; --权限传递
>conn scott/tiger
>grant create any index to hr;
>conn scott/tiger
>select * from v$session_privs; --查看当前用户拥有的权限。
>revoke create any index from scott; --hr没有回收，with admin option回收不级联。
对象权限：alter delete exec index insert references select update
授权时带 with grant option --被授权者也有权利授予其他用户，回收权限时级联。
>grant select on scott.emp to hr with grant option;
>grant select on scott.emp to user01;
>desc dba_tab_privs; grantee grantor
>conn user01/user01
>select * from scott.emp;
查看权限信息数据字典视图：dba_sys_privs session_privs --系统权限
dba_tab_privs dba_col_privs(列) user_tab_privs --对象权限
三、audit-审计，监控和记载用户对数据库所作的操作，并把审计信息存入数据库和操作系统文件。
>show parameter audit; audit_trail db-将审计信息存入审计跟踪记录$oracle_base/admin/adump中
os-写入操作系统文件 none-关掉。
>alter system set audit_trail=db scope=spfile; 打开审计
>startup force
>audit select on scott.emp; 打开对表的查询审计
>desc aud$; 查看审计信息视图。
>conn scott/tiger
>select * from emp;
>conn / as sysdba
>select * from aud$;
>select username,audid from v$sesson;
>noaudit select on scott.emp; 关闭对表的查询审计
四、role，角色--权限的集合（系统权限和对象权限）,角色分配原则：用户-应用角色-权限-应用角色-用户。
三层构架（client-(web-app)-db）下不好创建分配。
系统预定义角色：
DBA
CONNECT
RESOURCE
session_roles;--当前用户激活的角色。
用户缺省角色 --default角色，用户登录时自动激活默认缺省角色。
>alter user scott default role connect;
>conn scott/tiger
>select * from session_roles;
>set role resource[,..|all|all except ..]; --激活角色
创建角色
>create role hr_role;
如何修改缺省角色：
>alter user u_name default role [roles_list |all except roles|none];
查看角色信息：dba_roles,dba_role_privs
role_sys_privs,role_role_privs,role_tab_privs
五、全球化支持：主要时字符集支持，文字排序，货币，日期等等
oracle有它自己的字符集，自带字符集，独立与操作系统字符集。
字符集分两类：单字节7位，2的7次 128
多字节：固定长度的 AL16UTF16（unicode） 2位
可变长的 1-4个字节AL16UTF-8编码的。 多选ZHS16GBK,客户遍布全世界选AL32UF8
字符集同时能给数据库带来性能的问题。
查看oracle支持的字符集信息：v$nls_valid_valus --语言、地区、字符集、排序
字符集设置：
数据库：
安装字符集
国家字符集
实例：覆盖数据库
client 环境：nls_language nls_lang=语言_地区.字符集（环境变量bash_profile修改）（AMERICAN_AMERICA....）
会话：alter session
语句：带nls参数
>desc nls_database_parameters;
>desc nls_instance_parameters; 注意nls_length_semantics
>desc nls_session_parameters; 优先级中会话的最高。
往往实例的字符集影响客户端登录。字符集设置在数据导入导出是很重要
出现乱码的关键问题在：db-client(包括app)间字符集、语言环境、时区是不是兼容
首先看db的：nls_database_parameter
再看client:nls_language
一般改客户端适应db
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/27064837/viewspace-732990/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/27064837/viewspace-732990/
扫一扫
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
