第一步：创建临时的表空间
create temporary tablespace user_temp
tempfile 'f:oracle\user_temp.dbf'
size 50m
autoextend on
next 50m maxsize 2048m
extent management local
/
第二步：创建数据表空间
create tablespace user_data
logging
datafile 'f:oracle\user_data.dbf'
size 50m
autoextend on
next 50m maxsize 2048m
extent management local
/
第三步：创建用户并指定默认的表空间
create user username identified by password
default tablespace user_data
temporary tablespace user_temp;
第四步：给用户授予权限
grant connect,resource,dba to username;
一、数据库
数据库顾名思义是数据的集合，而Oracle则是管理这些数据集合的软件系统，它是一个对象关系型的数据库管理系统。
二、表空间
表空间是Oracle对物理数据库上相关数据的逻辑映射。
一个数据库在逻辑上被划分成一到若干个表空间，每个表空间包含了在逻辑上相关联的一组结构。
每个数据库至少有一个表空间(称之为system表空间)。
每个表空间由同一磁盘上的一个或多个文件组成，这些文件叫数据文件(datafile)。
一个数据文件只能属于一个表空间。
三、实例
每一个运行中的数据库都对应着一个Oracle实例。
当服务器上的Oracle数据库启动时，Oracle首先会在内存中分配一片区域－称之为System Global Area(SGA)，然后启动一个或多个的Oracle进程。
SGA和这些进程合起来就称为一个Oracle的实例。实例中的内存和进程管理器用来高效地访问数据库的数据，同时为单个或多个的用户提供服务。
一个数据库可以由多个实例打开，但任何时刻一个实例只能打开一个数据库。多个实例可以同时运行在同一个机器上，它们彼此访问各自独立的物理数据库。
sga详解：
1 概述
Oracle SGA区的监控和管理，是数据库日常维护的重要内容。本文详细介绍SGA的基本概念，SGA运行情况检查，以及SGA的参数设置原则。
--------------------------------------------------------------------------------
2 SGA的基本概念
当启动Oracle数据库时，系统会先在内存内规划一个固定区域，用来储存用户需要的数据，以及Oracle运行时必备的系统信息。我们称此区域为系统全局区(System Global Area)，简称SGA。
SGA 包含数个重要区域，分别是：
■ Database Buffer Cache (数据库缓冲区)
■ Redo Log Buffer (重做日志缓冲区)
■ Shared Pool (共享区)
■ 其它，如Java pool， Large pool
2.1 Database Buffer Cache (数据库缓冲区)
数据库缓冲区的作用主要是在内存中缓存从数据库中读取的数据块。数据库缓冲区越大，为用户已经在内存里的共享数据提供的内存就越大，这样可以减少所需要的磁盘物理读。
在9i以前数据库缓冲区的大小是由db_block_buffers*db_block_size 来决定大小的。db_block_size参数是在创建数据库时设置的，OLTP系统的db_block_size一般设置为8k。
在9i中，数据库缓冲区的大小由db_cache_size决定，8i中的db_block_buffers被取消。db_cache_size的单位是字节，它直接决定了数据库缓冲区的大小，而不再是块的数量。
2.2 Shared Pool (共享区)
共享区由三部分组成，分别是Dictionary cache(包括数据字典的定义，如表结构、权限等)，Library cache(包括共享的sql游标，sql原代码以及执行计划、存储过程和会话信息)和Control structure。它的大小由初始化参数shared_pool_size控制，它的作用是缓存已经被解析过的SQL，使其能被重用，不用再解析。 SQL的解析非常消耗CPU的资源，如果一条SQL在Shared pool中已经存在，则进行的仅是软解析(在Shared pool中寻找相同SQL)，这将大大提高数据库的运行效率。当然，这部分内存也并非越大越好，太大的Shared pool，oracle为了维护共享结构，将付出更大的管理开销。建议在150M-500M之间。如果系统内存为1G，该值可设为150M-200M;如果为2G，该值设为250M-300M;每增加1G内存，该值增加100M;但该值最大不应超过500M。
2.3 Redo Log Buffer (重做日志缓冲区)
Log_buffer是重做日志缓冲区，对数据库的任何修改都按顺序被记录在该缓冲，然后由LGWR 进程将它写入磁盘。LGWR的写入条件是：用户提交、有1/3 重做日志缓冲区未被写入磁盘、有大于1M 重做日志缓冲区未被写入磁盘、超时、DBWR需要写入的数据的SCN 号大于LGWR 记录的SCN 号，DBWR 触发LGWR写入。
从中可以看出，大于1M的log buffer值意义并不大。重做日志缓冲区的大小由初始化参数log_buffer设定。
2.4 Large pool和Java pool
Large pool：用于MTS、并行查询和RMAN。如果使用了MTS或RMAN，large_pool特别有用，它可以降低用户对share pool的争用。
如果应用系统不使用MTS，也不使用RMAN，large_pool_size的大小可设为1M。
Java pool：Oracle8I以后，oracle数据库内置了对java的支持，如果数据库安装时选择了JServer组件，则这个值可以设置为20M- 30M之间。可以查询v$option动态视图，如果java那一项值为true，则表示安装了JServer，为false，表示未安装。
如果应用系统不使用PORTAL，APPS，也不使用RMAN，Java pool值的大小可设为1M。如果系统需要安装补丁，建议先把Java pool值的大小临时增加到50-150M。
查看sga：
SQL> show sga;
Total System Global Area 535662592 bytes
Fixed Size 1334380 bytes
Variable Size 184550292 bytes
Database Buffers 343932928 bytes
Redo Buffers 5844992 bytes
SQL> show user;
USER 为 "SYSTEM"
或者
SQL> show user;
USER 为 "SYSTEM"
SQL> select * from v$sga;
NAME VALUE
-------------------- ----------
Fixed Size 1334380
Variable Size 184550292
Database Buffers 343932928
Redo Buffers 5844992
Show sga的结果和SGA的初始化参数配置并不是完全一一对应的，下面是每一个部分的具体解释：
1>Fixed Size
oracle 的不同平台和不同版本下可能不一样，但对于确定环境是一个固定的值，里面存储了SGA各部分组件的信息，可以看作引导建立SGA的区域。
2>Variable Size
包含了shared_pool_size、java_pool_size、large_pool_size等内存设置和用于管理数据缓冲区等内存结构的hash table、块头信息等。
3>Database Buffers
指数据缓冲区，在8i中包含default pool、buffer_pool_keep、buffer_pool_recycle三部分内存。在9i中包含db_cache_size、 db_keep_cache_size、db_recycle_cache_size、db_nk_cache_size。这里要注意在8i中三部分内存总和为db_block_buffers*db_block_size。
4>Redo Buffers
指重做日志缓冲区的实际大小，和log_buffer的值可能稍有不同。
3.2 Data buffer 命中率检查
检查语句：
select 1 - (phy.value / (cur.value + con.value)) "HIT RATIO"
from v$sysstat cur， v$sysstat con， v$sysstat phy where cur.name = 'db block gets'
and con.name = 'consistent gets'
and phy.name = 'physical reads'
/
命中率不应该低于90%.
如果该值过低，应该调高初始化参数。8i调整db_block_buffer，9i调整db_cache_size。
SQL> ED
已写入 file afiedt.buf
1 select 1 - (phy.value / (cur.value + con.value)) "HIT RATIO"
2 from v$sysstat cur， v$sysstat con， v$sysstat phy where cur.name = 'db
block gets'
3 and con.name = 'consistent gets'
4* and phy.name = 'physical reads'
SQL> /
HIT RATIO
----------
.977971699
3.3 Dictionary cache命中率检查
检查语句：
- SQL>SELECT
1 - (SUM(getmisses)/SUM(gets)) "Data Dictionary Hit Ratio" FROM v$rowcache;
调整原则：
命中率不应该低于95%.
如果该值过低，应调高shared_pool_size的大小。
SQL> SELECT 1 - (SUM(getmisses)/SUM(gets)) "Data Dictionary Hit Ratio" FROM v$ro
wcache;
Data Dictionary Hit Ratio
-------------------------
.96951522
3.4 Library cache命中率检查
检查语句：
- SQL>SELECT
1-(SUM(reloads)/SUM(pins)) "Library cache Hit Ratio" FROM v$librarycache;
命中率不应该低于99%.
如果该值过低，应调高shared_pool_size的大小。
SQL> SELECT 1-(SUM(reloads)/SUM(pins)) "Library cache Hit Ratio" FROM v$libraryc
ache;
Library cache Hit Ratio
-----------------------
.995374557
3.5 Log buffer命中率检查
检查语句：
SQL> ED
已写入 file afiedt.buf
1 select (req.value*5000)/entries.value "Ratio"
2 from v$sysstat req， v$sysstat entries
3 where req.name = 'redo log space requests'
4* and entries.name = 'redo entries'
SQL> /
Ratio
----------
0
调整原则：
命中率不应该高于1.
如果该值过高，应调高log_buffer的大小。
4 、SGA 的参数设置原则
在 Oracle8i 时，SGA 的大小是由初始化参数文件内的某些参数所设定，最麻烦的是每次调整参数之后必须等重新激活数据库才生效。
从Oracle9i 开始，DBA 可以动态配置内存的大小;这样的技术我们称为「dynamicSGA」。有了dynamic SGA ，SGA的各组成区域都可以动态地进行规划与调整，而不需先关闭数据库。在Oracle9i中，SGA_MAX_SIZE可设定 SGA 所占用的最大内存空间。需要注意的是：SGA_MAX_SIZE 尽量不要超过物理内存大小，否则将会使用到硬盘上的虚拟内存，反而导致性能低下。如果 SGA_MAX_SIZE 之设定值小于其它 SGA 相关参数设定值的总和，或是小于各相关参数默认值的总和，则 SGA_MAX_SIZE 之设定值无效。
在SGA的基本概念部分，已经介绍了各个部分大小设置的原则，这里再用一句话来总结：
1)数据库缓冲区建议占物理内存的20%-30%。物理内存在1G以内，可以占20%，1G-2G之间可占25%，物理内存在2G以上，可以占30%，甚至更高。
2)共享区建议在150M-500M之间。如果系统内存为1G，该值可设为150M-200M;如果为2G，该值设为250M-300M;每增加1G内存，该值增加100M;但该值最大不应超过500M。
3)重做日志缓冲区建议设为1M。
4)如果应用系统不使用MTS，也不使用RMAN，Large pool的大小可设为1M。否则设为16M。
5)如果应用系统不使用RMAN，Java pool值的大小可设为1M。否则设为50M。如果系统需要安装补丁，建议先把Java pool值的大小临时增加到50-150M。
调整完上述参数后，一定要计算SGA的尺寸，总原则是SGA的尺寸应小于物理内存的一半。SGA的计算方法如下：
SGA=DB_BLOCK_BUFFERS*DB_BLOCK_SIZE+SHARED_POOL_SIZE+LOG_BUFFER。同时还要保证(SGA+sort_area_size*sesson数量)不大于物理内存的70%。
Oracle 调整SGA、PGA大小 :
SQL> show parameter sga;
SQL> alter system set sga_max_size=4096M scope=spfile;
System altered.
SQL> alter system set sga_target=4096M scope=spfile;
System altered.
如果是RAC环境，需要这样增加sid='*'：
alter system set sga_target=20G scope=spfile sid='*';
重启使更改生效：
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 4294967296 bytes
Fixed Size 2026296 cbytes
Variable Size 805307592 bytes
Database Buffers 3472883712 bytes
Redo Buffers 14749696 bytes
Database mounted.
Database opened.
SQL> show parameter sga;
NAME TYPE VALUE
----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 4G
sga_target big integer 4GSQL> show parameter pga；
不用重启DB，直接在线修改。
SQL> alter system set workarea_size_policy=auto scope=both;
System altered.SQL> alter system set pga_aggregate_target=512m scope=both;
System altered.
SQL> show parameter workarea
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
workarea_size_policy string AUTO --这个设置成AUTO
SQL> show parameter pga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 536870912SELECT * FROM V$PGASTAT;
aggregate PGA auto target 477379584 bytes --当前可用于自动分配了的PGA大小，应该比PGA_AGGREGATE_TARGET 小
over allocation count 1118 --实例启动后，发生的分配次数，如果这个值大于0，就要考虑增加pga的值
1 查看 SGA大小 show parameter sga_max_size db_cache_size的尺寸受 SGA的影响为能大于SGA
2 查看 show parameter shared_pool_size 尺寸
一般来说 shared_pool_size+db_cache_size=SGA_MAX_SIZE*70%左右
经过计算再修改db_cache_size
sql>alter system set db_cache_size= 大小M scope=spfile sid='数据库SID';
sql>shutdown immediate
sql>startup
sql>show parameter db_cache_size
就可以看到修改后的效果了
扫一扫
48万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
